In this post I will walk you through the process of setting up a scheduled, automatic remote database backup on Linux.

If you haven't already done so, you'll need to set up passwordless SSH from the Server with the database(s) to the Server that will be storing the database backup files.

Create New MySQL User to Perform Backups

This step is to be performed on the server with the database(s).

To be on the safe side, it's a good idea not to use the root MySQL user to perform the database backups. So, let's create a new user with just enough privileges to create backups:

CREATE USER '__DB_USER__'@'localhost' IDENTIFIED BY  '__DB_PASSWORD__';

GRANT SELECT , 
RELOAD , 
FILE , 
SUPER , 
LOCK TABLES , 
SHOW VIEW ON *.* TO '__DB_USER__'@'localhost' IDENTIFIED BY  '__DB_PASSWORD__' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;

FLUSH PRIVILEGES;

Replace __DB_USER__ with the name for your new user.

Replace __DB_PASSWORD__ with a strong, unique password.

Create Bash Script

This step is to be performed on the server with the database(s).

Create a new file called backup.sh:

sudo vim /home/backup.sh

Add the contents of the following Gist to your new file:

mysql_backup.sh

Read the comments for instructions on how to use the script.

Set strictest possible permissions; so that the only thing that can be done is script execution by the file owner:

sudo chmod 0100 backup.sh

Create directory to store the database backups:

cd /home
sudo mkdir backups
cd backups
sudo mkdir db

Test the backup script:

sudo /home/backup.sh

If it worked, you should see a .sql.gz file for each of the databases you specified in the backup script.

Add Cron Task

This step is to be performed on the server with the database(s).

Finally, you'll need to add a cron task to execute the script on a set schedule. Edit crontab for current the user:

crontab -e

If you've never used crontab on the current server before, it will ask you which editor you would like to use. I prefer vim, but you can use whichever you wish.

Append the following to the end of the crontab file:

30 2 * * * sudo /home/backup.sh

This will run the backup script daily at 2:30 AM (server time). For more info on crontab: https://en.wikipedia.org/wiki/Crontab_(Unix_command.

It might be a good idea to verify your server time with the following command:

date

The output should be similar to the following:

Wed Jul 24 23:12:13 EDT 2013

On Ubuntu you can use the following to set the timezone temporarily and get instructions on how to set it permanently:

tzselect

If you need help setting the timezone on your linux distro:

https://duckduckgo.com/?q=how+to+set+timezone+on+linux+server

Additional Resources

These are not necessary to perform the remote, automatic backup, but you may find them useful.

prune_backups.sh - Automatically delete old backup files