Full Backup using MySQL Enterprise Backup

As you might know, I am primarily an Oracle guy. This means that for all my backup needs, I am using Oracle Recovery Manager (RMAN). I recently had the task to implement MySQL Enterprise Backup for a MySQL database (mysql-advanced-5.6.15-linux-glibc2.5-x86_64). My most important resource for this task was the MySQL Enterprise Backup User’s Guide.

Install the software

Before we can configure backups and the like, you’ll need to install the MySQL Enterprise Backup software. Do so by following this guide:

$ tar xvzf meb-3.9.0-linux2.6-x86-64bit.tar.gz 
meb-3.9.0-linux2.6-x86-64bit/
meb-3.9.0-linux2.6-x86-64bit/bin/
meb-3.9.0-linux2.6-x86-64bit/bin/mysqlbackup
meb-3.9.0-linux2.6-x86-64bit/README.txt
meb-3.9.0-linux2.6-x86-64bit/LICENSE.mysql
meb-3.9.0-linux2.6-x86-64bit/manual.html
meb-3.9.0-linux2.6-x86-64bit/mysql-html.css

I then placed the mysqlbackup binary in my MySQL “bin” directory (typically /usr/local/mysql/bin if you installed MySQL as described in the documentation):

$ cp meb-3.9.0-linux2.6-x86-64bit/bin/mysqlbackup /usr/local/mysql/bin/
$ which mysqlbackup 
/usr/local/mysql/bin/mysqlbackup

Now we’ve installed the software, we can go on and prepare our database for backup.

Gather information, create backup user

Use this guide to review your configuration and figure out where you want to place your backup. In this example, I needed the following information:

• MySQL port (often “3306“)
• Backup user and password (“backup/mysupersecret” in this example)
• Location for backup data (“/var/backups/mysql/backups” in this example)

Since I did not yet have a backup user for the database, I had to create one. Log into the database as root and create the backup user (named backup in my case) and grant the necessary privileges:

$ mysql -u root -p
mysql> CREATE USER 'backup'@'localhost' IDENTIFIED BY 'mysupersecret';
mysql> GRANT RELOAD ON *.* TO 'backup'@'localhost';
mysql> GRANT CREATE, INSERT, DROP, UPDATE ON mysql.ibbackup_binlog_marker TO 'backup'@'localhost';
mysql> GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup_progress TO 'backup'@'localhost';
mysql> GRANT CREATE, INSERT, SELECT, DROP, UPDATE ON mysql.backup_history TO 'backup'@'localhost';
mysql> GRANT REPLICATION CLIENT ON *.* TO 'backup'@'localhost';
mysql> GRANT SUPER ON *.* TO 'backup'@'localhost';
mysql> GRANT CREATE TEMPORARY TABLES ON mysql.* TO 'backup'@'localhost';
mysql> FLUSH PRIVILEGES;

With these steps complete, we can now create a shell script to automate our backups.

Shell script for full backup

Based on your backup concept (you have one, right?), you might want to schedule the following shell script to run multiple times per day, daily or weekly. The most important command in the script is the following:

/usr/local/mysql/bin/mysqlbackup --port=3306 --protocol=tcp --user=$BACKUP_USER --password=$BACKUP_PASS --with-timestamp --backup-dir=$BACKUP_DIR backup-and-apply-log

This command calls the mysqlbackup binary with the following arguments (also see here):

Make sure to educate yourself on the backup operations supported by MySQL Enterprise Backup. The full script looks something like this:

#!/bin/bash

BACKUP_DIR=/var/backups/mysql/backups
BACKUP_PASS=mysupersecret
BACKUP_USER=backup

DATE_DAY=$(date +"%Y-%m-%d")
DATE_HOUR=$(date +"%H")

EMAIL_RECIPIENT=simon@krenger.ch

/usr/local/mysql/bin/mysqlbackup --port=3306 --protocol=tcp --user=$BACKUP_USER --password=$BACKUP_PASS --with-timestamp --backup-dir=$BACKUP_DIR backup-and-apply-log

NO_OF_COMPLETE_OK_MESSAGES=$(cat $BACKUP_DIR/${DATE_DAY}_${DATE_HOUR}*/meta/MEB_${DATE_DAY}.${DATE_HOUR}*.log | grep "mysqlbackup completed OK" | wc -l)

# Note that the string "mysqlbackup completed OK" must occur 2 times in the log in order for the backup to be OK
if [ $NO_OF_COMPLETE_OK_MESSAGES -eq 2 ]; then
        # Backup successful, find backup directory
        echo "Backup succeeded"
        exit 0
else
        echo "MySQL backup failed, see attached logfile" | mail -s "ERROR: MySQL Backup Failed!" ${EMAIL_RECIPIENT}
        exit 1
fi

You can then schedule it to run daily (crontab -e) at 04:00 in the morning for example:

0 4 * * * /var/backups/mysql/make-mysql-backup.sh