Schedule MySQL Backup

Introduction

Here is a step-by-step tutorial for configuring a scheduled backup of MySQL databases.

In order to do a scheduled MySQL backup, I'm going to create a script that includes the MySQL

backup command (mysqldump) and the remote copy command (scp). Then, I use cron to execute the script

periodically.

Note: I assume that you have a basic knowledge of server management, UNIX systems, and database

servers.

Requirements

Two server machines on a network

A MySQL database server

A MySQL user with database permissions

My environment uses the following:

The Solaris 10 Operating System is running on the server where the backups are saved.

Linux openSUSE 10.2 X86_64 is the operating system running on the MySQL database server.

MySQL 5.0 runs on the database server.

Creating the Script

1. Create a text file using a text editor, such as vi, and for the first line, add #!/bin/sh.

>vi backupScript.sh

#!/bin/sh

2. Define the backup's file name:

#!/bin/sh

filename="databaseName_backup_"`eval date +m%d_%H_%M_%S`".sql"

The string databaseName_backup is the backup prefix. You can change it to whatever you want.

The string `eval date +m%d_%H_%M_%S` runs the date command with the following format:

%Y: Year

%m: Month

%d: Day of the month

%H: Hour

%M: Minutes

%S: Seconds

The string .sql is an extension for the file, which is optional, but I recommend you use it.

3. Add the backup command:

#!/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

The MySQL reference manual
provides the a lot of good information about the mysqldump command, its

origin and its uses.

The string --lock-tables (-l is also valid) locks all tables before starting the dump. The tables are locked

with READ LOCAL to allow concurrent inserts in the case of MyISAM tables. For transactional tables, such

as InnoDB and BDB, --single-transaction is a much better option, because it does not need to lock the

tables at all.

The database user name is specified by the following parameters:

-u indicates the user name of the database.

-p indicates the user password of the database; ensure there is no space between -p and the password

(i.e. -pUserPassword).

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 > $filename

scp $filename user@remoteServer:/path/to/backups

rm $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.