4

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.

JellicleCat
  • 28,480
  • 24
  • 109
  • 162
  • Possible duplicate of [PHP regular backup of mysql data](http://stackoverflow.com/questions/38916163/php-regular-backup-of-mysql-data) – e4c5 Aug 15 '16 at 00:46

4 Answers4

4

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
Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
  • I'm trying (and failing) right now.

    When I try the first approach, the dump doesn't appear to take place (as though the second command is waiting on the first to terminate). When I terminate the connection, the outfile is created (but empty)

    As for the second approach, could you explain the latter args? Is outfile just supposed to be filename (not path)? What's pscp? Why do I list the outfile for remote and local machines at the end? Is it redundant?

     – JellicleCat Jul 03 '11 at 19:19
  • @JellicleCat `pscp.exe` is a secure copy (`scp`) utility downloaded from the same place as putty & plink. Used for copying files via an ssh connection... – Michael Berkowski Jul 03 '11 at 19:33
  • @JellicleCat Regarding the outfile args... `outfile` can either be just the filename so the dump occurs in whatever directory you happen to be in (probably /home/username) or a full path to where you want it to end up. Then in the `pscp` command, the first arg is the ssh specification required to connect to the remote machine, followed after a colon by the path to the file. Second arg is where you want the file to end up locally. All of this also works with an SSH key if you have one setup. – Michael Berkowski Jul 03 '11 at 19:36
  • Ah. Okay, I've downloaded it (and now realised that option 2 was 2 commands, not 1 command with a line wrap--silly of me). Thanks so much. – JellicleCat Jul 03 '11 at 19:37
  • @JellicleCat for some plink examples, see http://the.earth.li/~sgtatham/putty/0.58/htmldoc/Chapter7.html – Michael Berkowski Jul 03 '11 at 19:38
  • Ah, sorry. Next time I'll put comment lines in between. – Michael Berkowski Jul 03 '11 at 19:38
  • I do not think the second solution does what a description says. The `>` is interpreted locally. So the `outfile` is created locally. No point/need downloading it with `pscp`. You have to wrap whole `mysqldump` command including the redirect to double-quotes to make it redirect to a remote file. See [my answer to similar (if not duplicate) question](http://stackoverflow.com/a/28494574/850848). – Martin Prikryl Feb 13 '15 at 09:11
  • @MartinPrikryl You're correct, I edited it according to your comment. – Michael Berkowski Feb 13 '15 at 14:58
1

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

Ankur Rastogi
  • 99
  • 3
0

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
JellicleCat
  • 28,480
  • 24
  • 109
  • 162
0

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 mysshUser@domain.com -pw mysshPassword >

C:\myProject\dbDump.log

Community
  • 1
  • 1
Arthur
  • 1
  • 1