Remotely Backing Up a MySQL Database

I was recently asked about backing up a MySQL database for a remotely hosted Drupal site. Assuming you have SSH access to the hosting account, this is trivial to do from a Linux machine.

Assumptions:

  • No direct access to the MySQL server.
  • You do have SSH access to a server at the hosting provider that:
    • you have configured ~/.ssh/authorized_keys so that you can ssh in (securely!) without being prompted for a password.
    • that has mysqldump and can access the MySQL server

First test the remote access with ssh via “ssh username@remoteserver”. If it works correctly, you will have a command prompt on the remote (hosting) server. Then, ensure you can run ‘mysqldump’ — if you invoke it with no arguments, it should print out usage information. At this point, you can exit out to your original shell.

To do the backup, you’ll run ssh and give a command string (in single quotes) for mysqldump (which will be run on the remote machine). It would normally send the results to stdout, but you can redirect, of course. For example:

ssh -C user@host \
'mysqldump -u mysqluser -pmysqlpassword -h mysqlhost db_name' > database.dump

Note that there is no space between “-p” and the MySQL password. I’m also using the “-C” option to ssh to request data compression — this makes it run much faster.

To save space, you can run results through gzip locally:

ssh -C user@host \
'mysqldump -u mysqluser -pmysqlpassword -h mysqlhost db_name' \
| gzip -c > database.dump.gz

Or, you could run gzip remotely; something like:

ssh user@host \
'mysqldump -u mysqluser -pmysqlpassword -h mysqlhost db_name| gzip -c' \
> database.gz

In my case, I have it scheduled to run in cron every day and version my output files. The final command looks like:

ssh user@host \
'mysqldump -u mysqluser -pmysqlpassword -h mysqlhost db_name|gzip -c' \
 > database.`date +\%Y\%m\%d`.gz

Pretty straight-forward stuff, really.