CODEWITHSUNDEEP
windowssshmysqlputty
Jellicle
Jellicle

Reputation: 30206

Automate MySQLdump to local computer (Windows)

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.

Upvotes: 4

Views: 4616

Answers (4)

Ankur Rastogi
Ankur Rastogi

Reputation: 99

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 ./

Upvotes: 1

Michael Berkowski
Michael Berkowski

Reputation: 270609

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

Upvotes: 4

Jellicle
Jellicle

Reputation: 30206

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

Upvotes: 0

Arthur
Arthur

Reputation: 1

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 [email protected] -pw mysshPassword >

C:\myProject\dbDump.log

Upvotes: 0

Related Questions