Automate MySQLdump to local computer (Windows)

Question

I'd like to automate a mysqldump to my computer from a remote host, and I cannot figure out how to do it.

I suppose that I should run a mysqldump through an SSH tunnel, but this becomes complicated by the fact that my local computer is a Windows XP machine. I'm using putty to open a tunnel like so:

putty -load "[my saved session]" -L [localport]:localhost:3306 -N

Note: I can't just connect to the mysql server remotely.

Answer 1

Building on what @Michael Berkowski gave me (and adapting to Linux, which is now all I use), I end up with two commands for tunneling to Server A to access a MySQL server on Server B:

ssh -f -L [localPort]:[serverB]:[serverB_mysqlPort] --port [serverA_sshPort] user@serverA -N
mysqldump -u[user] -p[password] -P[localPort] -h 127.0.0.1 [databaseName] > outfile

Answer 2

This works for me (one line code in myBackup.bat file):

mysqldump --result-file=C:\myProject\dbDump.dump --port=21

--host=localhost --user=dbUser --password=dbPassword dbName | plink -L 21:localhost:3306 -ssh [e-mail] -pw mysshPassword >

C:\myProject\dbDump.log

Answer 3

Instead of PuTTY, download the command line version plink.exe. Then using the same connection parameters you can run the mysqldump via plink and save the output locally. PLink is available from the same download page as PuTTY.

# setup the tunnel with plink
plink -load "[my saved session]" -L [localport]:localhost:3306
# Mysqldump your local port, redirected to outfile
mysqldump --port=[localport]  -h localhost -uuser -ppassword dbname > outfile

http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html

Or, another method would be to execute mysqldump in the same command line as plink, redirecting it to outfile on the local machine.

plink -load "[my saved session]" mysqldump -uuser -ppassword dbname > outfile

Answer 4

You can automate this process by setting up two things with the help of crons

1 You have to generate MYSQLDUMP on regular basis by creating a cron using below command on your remote machine.

MYSQLDUMP Command

MYSQLDUMP -uuser -p --all-databases > file_name.sql

2. You have to create a cron to transfer the file from remote server to your local machine using SCP Command that is mentioned below.

Linux SCP Command

scp user@remote_ip:~/mysql_dump_file_name.sql ./