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

Argument Description
--port Port used to connect to the database instance during backup operations.
--protocol Protocol used to connect to the database.
--user, --password ID and password of privileged MySQL user.
--with-timestamp Creates 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-dir The directory under which to store the backup data. This is a crucial parameter required for most kinds of backup operations.
backup-and-apply-log This 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:

#!/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, please check 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

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.

Comments

Oh no, new comments are currently disabled.
If you want to get in touch with me, please do so via e-mail:

Restore a MySQL Enterprise Backup – Simon Krenger, on 2017-04-30 09:20:46 (Website)

[…] another post I explained how to create a full backup using MySQL Enterprise Backup. Of course, the most […]

Simon Krenger, on 2015-12-17 11:17:33 (Website)

You can find an example of an incremental backup here: https://dev.mysql.com/doc/mysql-enterprise-backup/3.9/en/mysqlbackup.incremental.html

richmond, on 2015-12-13 20:44:52

Hi Simon, Please is there a way i can get an Incremental backups, Db Backups script on your MEB posting if you may have it.? Your full backup script works like a charm and was wondering if I can get the rest. Please advice, thanks. Please If you do, you can send it through my email richmondoprah@yahoo.com. Thanks.

richmod, on 2015-12-13 20:43:16

Hi Simon, Please is there a way i can get an Incremental backups, Db Backups script on your MEB posting if you may have it.? Your full backup script works like a charm and was wondering if I can get the rest. Please advice, thanks.

taj, on 2015-07-08 12:08:04

Hi Simon, Your full backup script is working fine. I used it in production server. Now i need to schedule incremental backup script. Please share the incremental backup script. I have schedule the full backup on monday, rest of the day i need incremental backup. Thanks in advance

Samrat, on 2015-03-17 13:19:04

Great work buddy. Can you show me a script which runs as scheduled task at everyday 11pm night --- one full backup (Monday) and tue,wed,thu,fri(Incremental backups). like one whole week can be covered. for mysql ENT backup on red hat linux 5 ? Thanks in advance.

Simon Krenger, on 2015-03-12 13:32:37 (Website)

Hello Samrat, I might consider to post a few tutorials concerning these scenarios. Thank you for your input.

Samrat, on 2015-02-26 13:22:25

Your article is great. it helps A LOT. i need more help on this. Please send me all the commands and shell scripts for scheduled backups --using MYSQL ENT BACKUP.I am new to this and need following things : how to do incremental backups -- commands how to verify backups ---commands how to restore --commands role of my.cnf file for connecting into the DB examples? how to use backup-my.cnf ? verification phase of backup-- any changes to be done in my.cnf ? need one scenraio showing backups (all types)/ verify/ restore --thru commands That will be a great help from your side. please send in my email --samrat101@gmail.com Thanks.

MariaDB Xtrabackup / MySQL enterprise backup | innotree, on 2014-06-05 06:28:09 (Website)

[…] enterprise backup http://www.krenger.ch/blog/full-backup-using-mysql-enterprise-backup/ Full Backup using MySQL Enterprise Backup http://dev.mysql.com/doc/mysql-enterprise-backup/3.10/en/ […]