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 

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 

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';

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):

--portPort used to connect to the database instance during backup operations.
--protocolProtocol used to connect to the database.
--user, --passwordID and password of privileged MySQL user.
--with-timestampCreates a subdirectory underneath the backup directory, with a name formed from the timestamp of the backup operation. Useful to maintain a single backup directory containing many backup snapshots.
--backup-dirThe directory under which to store the backup data. This is a crucial parameter required for most kinds of backup operations.
backup-and-apply-logThis option performs an extra stage after the initial backup, to bring all InnoDB tables up-to-date with any changes that occurred during the backup operation, so that the backup is immediately ready to be restored.

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



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


/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
        echo "MySQL backup failed, please check logfile" | mail -s "ERROR: MySQL Backup Failed!" ${EMAIL_RECIPIENT}
        exit 1

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

For more information on the topic of MySQL Enterprise Backup, I heavily recommend reading the documentation. If you want to know how to restore your backup, see my follow-up post.

Hello world

My name is Simon Krenger, I am a Technical Account Manager (TAM) at Red Hat. I advise our customers in using Kubernetes, Containers, Linux and Open Source.


  1. GitHub
  2. LinkedIn
  3. GitLab