backup mysql from Linux to Windows?

Hi

I've got a Linux server with a mysql database which is growing too big.
I want to back up my database, empty it before doing various changes and
then import some of the data again.

The smartest would probably be to export the data with mysqldump -
unfortunately my space left on the server is not enough, so I need to
export the data to another computer. Here the question is, can I export
it to my windows pc? It seems mysqldump can connect to a host (--host)
so would it work if I set up an ftp-server at my local pc? Another
possibilty might be, I have a ssh connection to the linux server, maybe
its possible to use this? Or can mysqldump pipe onto a shared windows
folder?

If you have experiences or ideas with the above I'd really like to hear
from you


Thanx

ps. I'm talking about 6GB of data - how mutch that will be on a dump I
don't know.

Ask Josephsen wrote:
> The smartest would probably be to export the data with mysqldump -
> unfortunately my space left on the server is not enough, so I need to
> export the data to another computer. Here the question is, can I export
> it to my windows pc? It seems mysqldump can connect to a host (--host)
> so would it work if I set up an ftp-server at my local pc?

No need to use an ftp server on the PC.

What I'd do is run the mysqldump utility on Windows, and specify the
hostname of your Linux server with the --host option. Then the output
of the mysqldump command can be written very easily on the Windows drive.

You might also reduce the volume of data going over the network, by
using mysqldump's --compress option.

> Another
> possibilty might be, I have a ssh connection to the linux server, maybe
> its possible to use this? Or can mysqldump pipe onto a shared windows
> folder?

You can use smbmount on the Linux box to mount a Windows share (similar
to what called "mapping a drive" on Windows). Then you could run
mysqldump on Linux and write its output to the Windows share via the
network.

But I'd still prefer the solution above, since SMB is a notoriously
inefficient protocol.

Regards,
Bill K.

You can install the client utilities on your Windows PC and run
mysqldump from there, writing to a local (PC) file. No FTP or file
shares are required. Just connect to the database host using the
--host option as you noted.

You might want to try in on a subset of your 6GB database to get a feel
for the volume.

Bob Lanteigne

On Mon, 10 Apr 2006 17:58:23 +0200, Ask Josephsen
<ask_rem@rem_minreklame.dk> wrote:

>The smartest would probably be to export the data with mysqldump -
>unfortunately my space left on the server is not enough, so I need to
>export the data to another computer. Here the question is, can I export
>it to my windows pc?

> It seems mysqldump can connect to a host (--host)

Yes, run mysqldump on your MSWindows PC and use the --host to
connect it to the database sever.
Its output can be piped into any local file on the MSWindows PC

>so would it work if I set up an ftp-server at my local pc?

Not needed.

>Another
>possibilty might be, I have a ssh connection to the linux server, maybe
>its possible to use this? Or can mysqldump pipe onto a shared windows
>folder?

Not impossible, but running mysqldump on your MSWindows PC is
much easier.

HTH
--
( Kees
)
c[_] THE BEST THINGS IN LIFE ARE FREE. (Lana) (#210)

Thanks

I't seems your advices should get me through. But connecting makes trouble.

Connecting by mysqldump from my windows pc to the linux gives me
following error:
C:\...\mysql\bin>mysqldump -h 192.168.123.123 -u=xxx -p datase1 table1 >
C:\...\test.sql
Enter password: ********
mysqldump: Got error: 2003: Can't connect to MySQL server on
'192.168.123.123' (10060) when trying to connect

I've tried to adding following rule on my firewall, but it does not seem
to matter. Also the router should not be the problem.
Action, Source, Dest, Protocol, Source, Dest
ACCEPT, Any, Any, Any 10060,3306

The second way, to mount my windows drive does not work either (my
computer has the fictive ip 192.168.111.111, I shared the temp-folder
and disabled my local firewall):

....$ mount //192.168.111.111/temp ~/mnt/ask_pc
Could not resolve mount point /root/mnt/ask_pc

same goes for smbmount:

....$ smbmount //192.168.1.126/temp ~/mnt/ask
Could not resolve mount point /home/god/mnt/ask

It seems it is the connection between my pc and the server that makes
the fuzz, but we do have samba running (and working) and use both ftp
and http services on the linux box - so notmally there is no problem
connecting.

I read somewhere mysql can be set to ignore network connections, how do
I find out if that is the problem - and how to solv it? It does,
however, work connecting to other mysql datases from the problematic
mysql server.

Hope you are still full of ideas


Thanks
Ask

Thanks

I't seems your advices should get me through. But connecting makes trouble.

Connecting by mysqldump from my windows pc to the linux gives me
following error:
C:\...\mysql\bin>mysqldump -h 192.168.123.123 -u=xxx -p datase1 table1 >
C:\...\test.sql
Enter password: ********
mysqldump: Got error: 2003: Can't connect to MySQL server on
'192.168.123.123' (10060) when trying to connect

I've tried to adding following rule on my firewall, but it does not seem
to matter. Also the router should not be the problem.
Action, Source, Dest, Protocol, Source, Dest
ACCEPT, Any, Any, Any 10060,3306

The second way, to mount my windows drive does not work either (my
computer has the fictive ip 192.168.111.111, I shared the temp-folder
and disabled my local firewall):

....$ mount //192.168.111.111/temp ~/mnt/ask_pc
Could not resolve mount point /root/mnt/ask_pc

same goes for smbmount:

....$ smbmount //192.168.1.126/temp ~/mnt/ask
Could not resolve mount point /home/god/mnt/ask

It seems it is the connection between my pc and the server that makes
the fuzz, but we do have samba running (and working) and use both ftp
and http services on the linux box - so notmally there is no problem
connecting.

I read somewhere mysql can be set to ignore network connections, how do
I find out if that is the problem - and how to solv it? It does,
however, work connecting to other mysql datases from the problematic
mysql server.

Hope you are still full of ideas


Thanks
Ask

Thanks

I't seems your advices should get me through. But connecting makes trouble.

Connecting by mysqldump from my windows pc to the linux gives me
following error:
C:\...\mysql\bin>mysqldump -h 192.168.123.123 -u=xxx -p datase1 table1 >
C:\...\test.sql
Enter password: ********
mysqldump: Got error: 2003: Can't connect to MySQL server on
'192.168.123.123' (10060) when trying to connect

I've tried to adding following rule on my firewall, but it does not seem
to matter. Also the router should not be the problem.
Action, Source, Dest, Protocol, Source, Dest
ACCEPT, Any, Any, Any 10060,3306

The second way, to mount my windows drive does not work either (my
computer has the fictive ip 192.168.111.111, I shared the temp-folder
and disabled my local firewall):

....$ mount //192.168.111.111/temp ~/mnt/ask_pc
Could not resolve mount point /root/mnt/ask_pc

same goes for smbmount:

....$ smbmount //192.168.1.126/temp ~/mnt/ask
Could not resolve mount point /home/god/mnt/ask

It seems it is the connection between my pc and the server that makes
the fuzz, but we do have samba running (and working) and use both ftp
and http services on the linux box - so notmally there is no problem
connecting.

I read somewhere mysql can be set to ignore network connections, how do
I find out if that is the problem - and how to solv it? It does,
however, work connecting to other mysql datases from the problematic
mysql server.

Hope you are still full of ideas


Thanks
Ask

Ask Josephsen wrote:
> Thanks
>
> I't seems your advices should get me through. But connecting makes trouble.
>
> Connecting by mysqldump from my windows pc to the linux gives me
> following error:
> C:\...\mysql\bin>mysqldump -h 192.168.123.123 -u=xxx -p datase1 table1 >
> C:\...\test.sql
> Enter password: ********
> mysqldump: Got error: 2003: Can't connect to MySQL server on
> '192.168.123.123' (10060) when trying to connect
>
> I've tried to adding following rule on my firewall, but it does not seem
> to matter. Also the router should not be the problem.
> Action, Source, Dest, Protocol, Source, Dest
> ACCEPT, Any, Any, Any 10060,3306

I wrote a long response but unfortunately, my newsreader decided to
discard it instead of posting. :-(

I'll summarize:

Test the connection from your Linux host and your Windows host. Run the
following command on both systems:

telnet 192.168.123.123 3306

You're just looking to see if it reaches MySQL; the response from MySQL
will not be human-readable.

If it works on Linux, but not on Windows, you still have a firewall
preventing access. Or perhaps mysqld is using the option
"bind-address=127.0.0.1".

If it works from neither local nor remote client, then perhaps
"skip-networking" is in effect. See below.

Another idea: do you have a local firewall like ZoneAlarm preventing
unauthorized outgoing connections on your Windows PC? This could allow
ftp/http connections, but prevent others.

> I read somewhere mysql can be set to ignore network connections, how do
> I find out if that is the problem - and how to solv it? It does,
> however, work connecting to other mysql datases from the problematic
> mysql server.

You can run this query in the mysql tool on Linux:

mysql> show variables like 'skip-networking';

If it shows "ON" then the msyqld is configured to prevent remote access
completely.

There may also be an option in effect "bind-address=127.0.0.1" that
permits network connections, but only from the localhost (this is
typically used by Java web apps). But I can't figure out how to query
for this. You may have to hunt for it in my.cnf files or even in the
/etc/init.d/mysql or mysqld_safe scripts.

Regards,
Bill K.

On Tue, 11 Apr 2006 12:47:02 +0200, Ask Josephsen
<ask_rem@rem_minreklame.dk> wrote:

>Thanks
>
>I't seems your advices should get me through. But connecting makes trouble.
>
>Connecting by mysqldump from my windows pc to the linux gives me
>following error:
>C:\...\mysql\bin>mysqldump -h 192.168.123.123 -u=xxx -p datase1 table1 >
>C:\...\test.sql
>Enter password: ********
>mysqldump: Got error: 2003: Can't connect to MySQL server on
>'192.168.123.123' (10060) when trying to connect

Ouch, probablyb 10060 is not a port number, but an error number
in this context!

>I've tried to adding following rule on my firewall, but it does not seem
>to matter. Also the router should not be the problem.
>Action, Source, Dest, Protocol, Source, Dest
>ACCEPT, Any, Any, Any 10060,3306

3306 is the default port MySQLd listens to.
Are you sure the server is on IP address 192.168.123.123 ?

Is your Linux server 192.168.123.123 on the same subnet as your
MSWindows PC?
If it is routed and you need NAT, NAT port 3306, not 10060.

Does user xxx with password datase1 really have access to MySQL?
Or is xxx only able to login from localhost/127.0.0.1 ?
(not very probable: if this were the problem, the error message
would complain about the userid@host / password combination).

>The second way, to mount my windows drive does not work either (my
>computer has the fictive ip 192.168.111.111, I shared the temp-folder
>and disabled my local firewall):
>...$ mount //192.168.111.111/temp ~/mnt/ask_pc
>Could not resolve mount point /root/mnt/ask_pc
>
>same goes for smbmount:
>
>...$ smbmount //192.168.1.126/temp ~/mnt/ask
>Could not resolve mount point /home/god/mnt/ask

I don't have any experience with that, so I won't comment on it.

>I read somewhere mysql can be set to ignore network connections, how do
>I find out if that is the problem - and how to solv it? It does,
>however, work connecting to other mysql datases from the problematic
>mysql server.

If thats is an option, it will be some definition in your mysql
my.ini file. It will be documented, try the MySQL site to get
search the docs.

>Hope you are still full of ideas

Not so sure
Try the NAT first 3306 -> IP of server with mysqld
HTH
--
( Kees
)
c[_] Experience is a good teacher but her fees are high... (#59)

Kees Nuyt skrev:
> On Tue, 11 Apr 2006 12:47:02 +0200, Ask Josephsen
> <ask_rem@rem_minreklame.dk> wrote:
>
>> Thanks
>>
>> I't seems your advices should get me through. But connecting makes trouble.
>>
>> Connecting by mysqldump from my windows pc to the linux gives me
>> following error:
>> C:\...\mysql\bin>mysqldump -h 192.168.123.123 -u=xxx -p datase1 table1 >
>> C:\...\test.sql
>> Enter password: ********
>> mysqldump: Got error: 2003: Can't connect to MySQL server on
>> '192.168.123.123' (10060) when trying to connect
>
> Ouch, probablyb 10060 is not a port number, but an error number
> in this context!
>
>> I've tried to adding following rule on my firewall, but it does not seem
>> to matter. Also the router should not be the problem.
>> Action, Source, Dest, Protocol, Source, Dest
>> ACCEPT, Any, Any, Any 10060,3306
>
> 3306 is the default port MySQLd listens to.
> Are you sure the server is on IP address 192.168.123.123 ?
>
> Is your Linux server 192.168.123.123 on the same subnet as your
> MSWindows PC?
> If it is routed and you need NAT, NAT port 3306, not 10060.
>
> Does user xxx with password datase1 really have access to MySQL?
> Or is xxx only able to login from localhost/127.0.0.1 ?
> (not very probable: if this were the problem, the error message
> would complain about the userid@host / password combination).
>
>> The second way, to mount my windows drive does not work either (my
>> computer has the fictive ip 192.168.111.111, I shared the temp-folder
>> and disabled my local firewall):
>> ...$ mount //192.168.111.111/temp ~/mnt/ask_pc
>> Could not resolve mount point /root/mnt/ask_pc
>>
>> same goes for smbmount:
>>
>> ...$ smbmount //192.168.1.126/temp ~/mnt/ask
>> Could not resolve mount point /home/god/mnt/ask
>
> I don't have any experience with that, so I won't comment on it.
>
>> I read somewhere mysql can be set to ignore network connections, how do
>> I find out if that is the problem - and how to solv it? It does,
>> however, work connecting to other mysql datases from the problematic
>> mysql server.
>
> If thats is an option, it will be some definition in your mysql
> my.ini file. It will be documented, try the MySQL site to get
> search the docs.
>
>> Hope you are still full of ideas
>
> Not so sure
> Try the NAT first 3306 -> IP of server with mysqld
> HTH

Thanks both of you. It seems either the firewall on the server or the
users on the mysql database are misconfigured. I could connect by telnet
to other servers. On the server with the problematic db I could not even
telnet to itself!

Anywayz a friend of mine is stopping by to help me out. Linux is not as
easy when you are used to windows

Thanks alot for your time, you got me quite far...

Ask