Backing Up MySQL Using a BASH Shell Script

Published November 5th, 2008

Problem Statement:

We have not found any good open source incremental backup solution for MySQL so far. Since disk space and bandwidth costs are becoming less and less by the day, they almost does not matter in deciding full or incremental backup decision. However, what does matter is time to create a backup. We assume that if we had a good incremental backup solution, it would take less time than making a full backup on a daily basis. But until we find such a solution, here is a simple shell script solution that will backup all your database on a daily basis and automatically keep last n days of backup.

Solution: Using mysql_backup.sh for daily backup of all your databases

The mysql_backup.sh is a simple Bash shell script that we developed to backup MySQL databases on a database server. The script is very easy to understand but here is how it works:

1. It connects to a remote MySQL database server and gets all the database names using SHOW DATABASES;
2. Next, it excludes all the databases listed in the file pointed by the $EXCLUDE_DB_FILE variable
3. Then it uses mysqldump to create dump of each table in each of the databases to be backed up and stores them in a new directory
4. It then compresses each of the tables
5. After processing each of the target databases it removes any old database backup that are older than $LAST_BACKUP_DAYS days

Step 1: Setting up mysql_backup.sh on a backup server

1. Download the latest version of the mysql_backup.sh from:
   http://mysqlhacker.com/downloads/backup/mysql_backup.tar.gz
2. Extract it in a directory. You should see a new sub-directory called mysql_backup
3. Edit the mysql_backup.sh script by following the embedded comments in the script. You need to replace:
   • $ROOT_DIR - set to the parent dir of the mysql_backup dir
   • $LOG_DIR - set to dir where you want to store the log files
   • $BACKUP_DIR - set to dir where you want to store the backups
   • $DB_USER - the database username (make sure this user can access all your databases)
   • $DB_PASS - the database password name
   • $DB_HOST - the database hostname
   • $EXCLUDE_DB_FILE - full path of the text file that lists databases which are to be excluded
   • $LAST_BACKUP_DAYS - how many days of old backup to keep
   • $ADMIN_EMAIL - email address which should receive failure notices
4. Once you have saved the edited version of the script, change permission to make the script executable by root user using:

   chown root:root mysql_backup.sh
   chmod 750 mysql_backup.sh comamnds

Step 2: Granting MySQL access to your backup server

Assuming that you are going to run the mysql_backup.sh on a different server than the MySQL database server, you need to grant SELECT privilege for the user you want to use in the script. For example, say you are going to run the mysql_backup.sh script on a backup server with IP address of 192.168.1.200 and you want to use a username/password pair:  backup_dba/tooMANYsecrets. In such a case, you need to run the following statements on the database server:

GRANT SELECT on *.* TO 'backup_dba'@'192.168.1.200' identified by 'tooManysecrets';
FLUSH PRIVILEGES;

Once you have run the GRANT and FLUSH statement with appropriate username/password pair and the appropriate IP address of the backup server, you can test the connection from the backup server by running the following command:

mysql -u backup_dba -ptooManysecrets -h your.db.hostname -A

If you are able to connect to the database server, run the following commands from the mysql prompt:

show databases;
use [db name];
show tables;
select count(*) from [db name].[table];

If all of the above works with appropriate replacements for [db name], and [table], you are set!

Step 3: Running for the first time

Now run the mysql_backup.sh script from the shell prompt as:

$ sh mysql_backup.sh

If you get an error, review the script and correct the error and rerun. If the script runs properly, you should see a new sub-directory  called $BACKUP_DIR/[year]/[month]/[month].[day].[year]/ in the $BACKUP_DIR directory . Below this new sub directory, you should see sub-directories for each of your databases. There should be compressed table dump files inside each of the database directories.

Step 4: Setting up a cron job to run it every night

Once you are sure that your mysql_backup.sh script is working fine, you should setup a nightly cron job to run the script every night. This can be done as simply as creating a symbolic link as follows:

ln -s /path/to/mysql_dump/mysql_backup.sh /etc/cron.daily/mysql_backup.sh

Make sure mysql_backup.sh is executable, otherwise it will not run when the cron daemon tries to run it.

Step 5: Checking the log

Next day, you should check the $LOG_FILE specified log and see if the cron job ran and also investigate the $BACKUP_DIR for backup of your databases.

Restoring a Table from Your Backup

We hope you never need to restore from your database backup but in case you do, here is how you can restore:

• Find the appropriate database backup directory in your $BACKUP_DIR directory
• Unzip each of the table that you want to restore using gzip -d [table file].gz
• Run: mysql -u [username] -p[password] -h [hostname] -D [database] [table fie].sql, which will drop your existing [table] and run CREATE TABLE statement to create the structure and insert all records stored in the backup dump file.
• Repeat this process for each of the tables that you want to restore

Restoring Entire Database from Your Backup

If you are planning to restore an entire database from your backup, do the following:

• Find the appropriate database backup directory in your $BACKUP_DIR directory
• From the mysql_backup tool directory, run: sh restore_db.sh [path to specific backup directory]. For example:

  sh restore_db.sh /backup/2008/10/10.01.2008/mydb

• Once the script is done, you should change your current directory to the specific backup directory
• Run mysql -u [user] -p[password] -h [host] -D [database] < [dbname].sql to restore your database

Category: Backup | Tags: