Normally the preferred way to transfer a MySQL database is to use mysqldump to create a database backup, possibly compress it with gzip, transfer that backup file to the new server, and then load it using the mysql command-line tool.
But what if you wanted to do it all in one step? Certainly it’s better to have the backup file around in case you need to repeat the process or in case something goes wrong. But as part of a recent job, I needed to do a transfer late at night that I knew based on previous experience would take about 6 hours to complete, and staying awake to babysit the process wasn’t a reasonable option.
So can you do it all with a single command? Certainly! Again, our old friend bash to the rescue. For reference, here’s the command to create the database backup:
mysqldump sourcedb > backup.sql
And here’s the command (on the destination server) to restore the backup:
mysql destdb < backup.sql
Obviously you can chain the two like this:
mysqldump sourcedb | mysql destdb
But that requires that they both be on the same server. Assuming one of the servers is listening on the Internet, you can connect to it remotely using the --host= parameter. But in this case we have to transfer over SSH. So lets take advantage of SSH’s stdin/stdout piping ability to make this easy. On the destination server, just do this:
mysql destdb < <(ssh sourceserver.example.com "mysqldump sourcedb")
A little confusing? Let’s break it down. First we run mysql on the destination server and select the database named “destdb”. Then we pipe to that program some input (the < operator). But instead of sending a file, we run a command and send its output. That’s what the notion <( command ) means. It’s a bit like the pipe (|) operator but slightly more flexible. The command we run is the ssh command, and we pass to it a string that we want to execute on the remote server; in this case mysqldump sourcedb.
The end result is that the destination server opens up an SSH connection to the source server, runs mysqldump sourcedb on it, and sends the result to the command mysql destdb on the destination server. Pretty slick, right? Could I have typed it instead like this?
ssh sourceserver.example.com "mysqldump sourcedb" | mysql destdb
Yes, probably. However, I with complex commands I have better luck with the < <( cmd ) syntax instead of using the | operator. The parsing and execution mechanics are slightly different between the two, and I find this preferred method more often works as expected.
Oh, but what about compression? We could certainly pipe the mysqldump output to gzip, and the restore input through gunzip, but that’s a little over-complicated. Instead I’m just going to ask SSH to handle compression transparently. The -C flag requests that compression be used (if it isn’t being used already). Also, I wanted to know how long it actually took, so I added the time command to the very beginning. Here’s the the final revised result, and the command I actually used for the job I mentioned:
time mysql destdb < <(ssh -C sourceserver.example.com "mysqldump sourcedb")
Note that MySQL authentication details are left out. You can easily make authentication implicit (and therefore leave them out completely from commands like this) by putting the details in a .my.cnf file in your home directory.