MySQL Enterprise Backup

MySQL Enterprise Backup is a feature of MySQL Enterprise Edition

Backup

mysqlbackup  --user=mysqlbackup --backup-dir=/mysql/backup/FULL --socket=/mysql/data/mysql.sock --compress  backup

NOTE: When InnoDB tablespace files are compressed during backup, they receive the extension .ibz rather than the usual .ibd extension. NOTE: Compression saves storage space, but it requires extra CPU overhead during the backup itself. 

Example Backup Script

Securely store password...

mysql_config_editor set --login-path=mysqlbackup --host=localhost --user=mysqlbackup --password

Create Backup directory and create backup script...

mkdir -p /mysql/scripts/logs

mkdir /mysql/backup/FULL

vi /mysql/scripts/mysql-backup-full.sh

Contents of script could be similar to below...

# Set Variables


BDIR="/mysql/backup/FULL"                         # The Backup Directory

BBIN="/mysql/mysql-backup/bin/mysqlbackup"        # The location of the mysqlbackup executable

SOCK="/mysql/mysql.sock"                          # The MySQL Socket file


# Create a lockfile to prevent multiple instances of the script running


lockfile -r 0 /tmp/mysqlbackup.lock || exit 1


# Alternate lockfile method where "lockfile" not installed

# myLF=/var/lock/mysql-backup.lock

# trap " [ -d ${myLF} ] && /bin/rmdir ${myLF}" 0 1 2 3 13 15

# [ -d ${myLF} ] && exit

# mkdir ${myLF} || exit


# Retain old backup until new backup completes


mv ${BDIR} ${BDIR}_old


# Run Backup. Delete old backup if successful


${BBIN} --login-path=mysqlbackup --with-timestamp --backup-dir=${BDIR} --socket=${SOCK} --compress backup

if [ $? -eq 0 ]

then

   rm -rf ${BDIR}_old

fi


# Remove lockfile (remove line below if using alternate method)


rm -f /tmp/mysqlbackup.lock


# Backup config file


cp /mysql/my.cnf ${BDIR}/*/datadir


exit 0

If lockfile is not installed, see the cron page for alternate mutex techniques.

Add backup job to root crontab...

chmod 700 /mysql/scripts/mysql-backup-full.sh

crontab -e

20 15 * * * /mysql/scripts/mysql-backup-full.sh >/mysql/scripts/logs/mysql-backup-full.$(date +"\%Y\%m\%d\%H\%M\%S").log 2>&1

Caution

Refer to the Limitations of MySQL Enterprise Backup appendix in the documentation (see Bibliography at end of page). Some key points are called out below...

Since 8.0.19 there are some exceptions to this rule: https://dev.mysql.com/doc/mysql-enterprise-backup/8.0/en/bugs.backup.html 
In some circumstances this can cause any subsequent restore to fail
There are also some other encryption related restrictions
i.e. if you need to restore a backup taken with an older version of MEB then you will need to use an older version of MEB to restore it. Note that this may (in fact, almost certainly) mean you also need to revert to an older version of MySQL.

Check

This query is useful when you are backing up on one node of an InnoDB cluster...

SELECT backup_type, start_time, end_time, end_lsn, exit_state, member_host, member_role

  FROM mysql.backup_history

  JOIN performance_schema.replication_group_members ON member_id=server_uuid

Restore

Make backup usable:

/mysql/backup/bin/mysqlbackup --backup-dir=/mysql/backup/FULL --uncompress apply-log

(Optional) Preserve current database directory

mv /mysql/data /mysql/data_old 

Move backup copy to datadir 

cp -r /mysql/backup/FULL/ /mysql/data

chown -R mysql:mysql /mysql/data

Recovery

To roll forward to a point in time...

Check the latest backup log for information about binary log position during the backup...

mysqlbackup: INFO: MySQL binlog position: filename mysql-bin.000001, position 3232

To roll forward use...

cd /mysql/log

mysqlbinlog --start-position=3232 mysql-bin.000001 | mysql

In some scenarios datetime can be used instead of position

mysqlbinlog --start-datetime="2014-07-24  12:39:10" --stop-date="2014-07-24  12:39:11" mysql-bin.000001

Alternate syntax avoiding pipe character (sometimes an issue on Windows)...

mysqlbinlog --start-position=3232 mysql-bin.000001 --result-file=/tmp/bin.sql

mysql --execute "source /tmp/bin.sql"