4. Save and close the file (with vi, type ESC :wq).

5. Configure secure copy (scp without a password), in order to copy or move the database backup:

The scp command is used to copy files to remote machines, and it is easily configurable so you can copy to other servers without a password. In order to do that, I'm going to generate a public/private key pair on the backup machine (the MySQL server), copy the public key to the remote machine, and use the scp command without a key, as follows:

a. Generate a key:

ssh-keygen -t rsa

Here's the system response:

Generating public/private rsa key pair.
Enter file in which to save the key (/home/username/.ssh/id_rsa):
<Press Enter to use the default>
Enter passphrase (empty for no passphrase): <Enter, in my case>
Enter same passphrase again: <Enter again>
Your identification has been saved in /home/username/.ssh/id_rsa.
Your public key has been saved in /home/username/.ssh/id_rsa.pub.
The key fingerprint is:
e4:f1:b5:3d:57:2e:37:87:2f:55:91:a7:2a:f9:f4:1d username@servername

b. Copy the public key (which ends in .pub) to the remote machine (using scp, FTP, or whatever you want). For example, the following command copies the id_rsa.pub key to the home directory of the user on the remote server:

scp /home/username/.ssh/id_rsa.pub user@remoteServer:.

c. Save the key in the authorized_keys store.

To do this, you must log in to the remote server. You can use ssh (for example, ssh user@remoteServer). You must know the user password.

You must include the public key to /home/user/.ssh/authorized_keys (or /root/.ssh/authorized_keys, if you are the root), but be careful because this file can contain the keys of other machines. You can use the following commands to append the public key to authorized_keys or create a new file if it does not exist:

>data="`eval cat ./id_rsa.pub`"

(I'm assuming that the file is in the user's home directory.)

>echo $data >> ./.ssh/authorized_keys

(If the .ssh directory does not exist, you must create it.)

d. Log out of the remote server.

6. Test the previous configuration by copying a file from the backup server to the remote server using scp:

scp ./somefile user@remoteServer:.

If the prompt doesn't ask for a password, then congratulations. You have configured the key correctly.

If it asks for a password, you must perform step 5 again.

7. Include the scp command in the backup script:

#!/bin/sh
filename="databaseName_backup_"`eval date +m%d_%H_%M_%S`".sql"
mysqldump --lock-tables -u userName -pUserPassword databaseName > \
/path/to/dump/the/database/$filename
scp /path/to/dump/the/database/$filename \
user@remoteServer:/path/to/backups
rm /path/to/dump/the/database/$filename

The last line is optional if you don't want to save the backup on the MySQL server.

The /path/to/backups/ directory must be created, and the user must have write permissions.

8. Schedule the backup.

So far, we have completed the backup script. So, now we are going to schedule the script's execution with cron:

a. Open the crontab to add the backup script:

>crontab -e

b. Add a new line for the path of the backup script and the time of the execution:

min hour * * * /path/to/backupScript.sh

For example:

00 02 * * * /home/username/backupScript.sh

In this example, the backup script will be executed daily at 02 hours (24-hour format).

If you want to configure the backup in a different way, take into account the following parameter values:

• min: 0 - 59
• hour: 0 - 23
• First * from left: 1 - 31 (Day of the month)
• Second * from left: 1 - 12 (Month; 12 is December)
• Third * from left: 0 - 7 (Day of the week; Sunday is 0 or 7, Monday is 1, and so on)

9. Save the file (in vi, use ESC:wq).

Note: The backupScript file must be an executable file. To ensure that it is, you can use the chmod command as follows:

chmod +x /path/to/backupScript.sh

10. Enjoy.

We have completed all the steps to do a backup of a MySQL database periodically.

About the Author

My name is Edwin Miguel Triana Gómez. I am a Computer System Engineer at the National University of Colombia, where I work in the Telemedicine Center.

Unless otherwise licensed, code in all technical manuals herein (including articles, FAQs, samples) is provided under this License.