MySQL Enterprise Backup (MEB) 3.7 - Backup and Restore Examples

I have been working with MEB and put a few examples of working with MEB on-line.  It's not a comprehensive list of everything MEB does but hopefully will be enough to get things started.  For a more comprehensive information please see the documentation.
http://dev.mysql.com/doc/mysql-enterprise-backup/3.8/en/index.html
 The MySQL Enterprise Backup (MEB) product makes backing up an Innodb database more flexible.  With minimal locking (or as MySQL say a 'Hot non-blocking' backup) the ability to perform partial backups, use compression and rebuilding slave servers is useful.   I think it's a pretty good product for Innodb engine users.  For MyISAM it's fairly limited, locking tables is something to be aware of, as it will lock the tables until the backup has completed to ensure the database is in a consistent state.  Even if performing a partial database backup the entire database is still locked.  Depending on the size of the database and the speed of the backup this could take awhile.

A few facts

Download via Oracle e-delivery

Latest version is 3.8 (as of writing this)

Use mysqlbackup --help for a full list of options

Is a licensed product, someone will have to breakout the credit card to use it

The Admin Bit

Privileges Required for the user

mysql> GRANT RELOAD ON *.* TO 'dba'@'localhost';

mysql> GRANT CREATE, INSERT, DROP ON mysql.ibbackup_binlog_marker TO 'dba'@'localhost';

mysql> GRANT CREATE, INSERT, DROP ON mysql.backup_progress TO 'dba'@'localhost';

mysql> GRANT CREATE, INSERT, DROP ON mysql.backup_history TO 'dba'@'localhost';

mysql> GRANT REPLICATION CLIENT ON *.* TO 'dba'@'localhost';

mysql> GRANT SUPER ON *.* TO 'dba'@'localhost';

mysql> GRANT CREATE TEMPORARY TABLES ON mysql.* TO 'dba'@'localhost';

mysql> FLUSH PRIVILEGES;

My.cnf

This can be used for reading any parameters you want to set as default.  For example, in the my.cnf you can set the following:

[mysqlbackup]

with-timestamp=true

Then when you want to run a backup, it will automatically put the backup in a timestamped folder:

mysqlbackup -u root --backup-dir=/db/DB1/backup  backup-and-apply-log

Monitoring, use the views are backup_history, backup_progress in the mysql database.   I haven't done a lot with these but useful to know they are there.

A quick overview

The backup and restore involves three conceptual steps:

      (1) Backup operation. 

      (2) Apply log operation to prepare backup for restore.  This applies all the changes taken during the backup which brings the backup up to date.

      (3) Copy-back operation to restore the backup.

The apply option (2) can be done either as part of the backup or separately. It can be done as a standalone operation but must be done before the restore is performed.  The benefit to separating the backup and apply stages is if you want to reduce time of the backup or reduce the server load then separating out the apply stage is something to be considered.  To perform a restore use the copy-back option.  For incremental’s there are other steps to perform both for backup and recovery, see below for examples.

When running the apply-log separately you can use limit-memory.  Use the limit-memory=MB variable, which limits usage of memory for the apply-log.         Not used this yet but on the TODO list.

           

Full Backup - The basic full database backup syntax is

mysqlbackup -u root --backup-dir=/db/backup/db backup-and-apply-log

            A useful option here is --with-timestamp=true, this will create a directory with a timestamp to store the backup in the location specificied.

            [mysql@mysqldb DB1 /db/DB1/backup/2012-08-14_13-23-39/datadir ]$ ls -lrt /db/DB1/backup/

            total 694392

            drwx------ 4 mysql mysql      4096 Aug 14 13:23 2012-08-14_13-23-39

            drwx------ 4 mysql mysql      4096 Aug 14 15:30 2012-08-14_15-30-36

To perform the above in two steps:

            mysqlbackup -u root --backup-dir=/db/DB1/backup/backup1  backup

            then to apply all the changes:

            mysqlbackup -u root --backup-dir=/db/DB1/backup/backup1 apply-log

Once the apply is complete the backup can be used to restore when needed.

Once the backup has run it creates the following directory structure:

total 12

-rw-r--r-- 1 mysql mysql  191 Sep  4 13:48 backup-my.cnf

drwx------ 8 mysql mysql 4096 Sep  4 13:49 datadir

drwx------ 2 mysql mysql 4096 Sep  4 13:49 meta

The datadir is holds the backup files, this includes the innodb logfiles and system tablespace, plus the data directory structure of the database.

The meta directory holds meta data about the backup in a few xml files.

Incremental Backups

The incremental backup type is dependent on what type of incremental you want to do.  The dictating factor is the --start-lsn option which must be used for an incremental backup (lsn is the log sequence number).  When you use mysqlbackup it creates a file called backup_variables.txt in the meta directory and in that file is a start_lsn and end_lsn.  From the last full backup, record the end_lsn and this becomes the start_lsn for the incremental.  If you need to do a differential always use the end-lsn from the full backup, this will capture all changes from the full but take longer.   If you want to do a cumulative use the end-lsn from the last increment backup.  This will capture less changes but make the recovery long.  There is no need to specify the apply-log for an incremental because the restore involves rolling the incremental(s|) into the base backup.

mysqlbackup -u root --incremental --start-lsn=<last full/incremental backup end lsn number> --incremental-backup-dir=/db/backup/db/increment1backup

Partial Database Backups

You can backup a single database or table.  I don't think this is straight forward and does backup innodb logfiles and the system tablespace when performing single table or partial backups. 

Here are the examples I have:

For all Innodb including and excluding the .frm file

mysqlbackup -u root --backup-dir=/db/DB1/backup --only-innodb-with-frm --verbose --with-timestamp=true backup-and-apply-log

mysqlbackup -u root --backup-dir=/db/DB1/backup --only-innodb --verbose --with-timestamp=true backup-and-apply-log

To backup a single table use the following:

mysqlbackup -u root --backup-dir=/db/DB1/backup/ --verbose --with-timestamp=true --include="testdb.smalltab" backup-and-apply-log

NOTE - Only applies to InnoDB tables created with the MySQL option innodb_file_per_table enabled, which are in separate files that can be included or excluded from the backup.

The --include allows you to be specific and specify a table.  For example to backup a table called table1 in a database called test. The syntax would be --include="test.table1.*" or to backup more than one database --include="test*.*".  If nothing matches the criteria then only the system tablespace and the innodb logfiles will be backed up.

A couple of points on single table backup:

The backup will include:

            system tablespace

            innodb files

            all databases and within each database the .frm files

            for the table specificied there will be a frm and the ibd file.

Backing up a Partitioning a Table
This did add another dimension to the issue of backing up and restoring a single table.  What if I want to backup a single partitioned table.  Partitioned tables in MySQL consist of the usual .frm file and an .ibd file for every partition you have.  It also consists of a .par file.  I used the --include="<database>.<table_name>.*", this will pickup all the .par, the .frm and all the .ibd files.  The issue is when you come to restore it.  Please see below.

MyISAM and MEB

The main drawback is if you are not using Innodb then locking maybe an issue for you.  In tests I have done even if you are doing a partial MyISAM backup then it will lock everything.  This is not ideal, but it does make sense as it probably needs to keep everything consistent.  If your database is large then be warned there could be a long locking wait.

Restore

Full restore example

            Shutdown the database instance

I tend to remove the old databases from datadir.   This will avoid issues when restoring databases if extra tables now exits post the backup.

Run the restore of the database.  The basic syntax is:

            mysqlbackup -u root --backup-dir=<path> copy-back

Note - The restored data includes the backup_history table, where MySQL Enterprise Backup records details of each backup. Restoring this table to its earlier state removes information about any subsequent backups that you did.  I normally truncate the backup tables after the restore.

Incremental restore

•           Shutdown the database instance

•           Remove the old database from the datadir. 

•           Roll all the appropriate incremental backups into the full backup

Start with oldest incremental backup and apply one at a time. Perform this operation on all incremental backups until you get to the required backup.  For example, a full backup on Sunday and an incremental every day until the following Sunday.  First the Monday's night incremental would get rolled in and then the Tuesday until the require point is reached.

For example:

mysqlbackup -u root --incremental-backup-dir=<path of incremental backup> --backup-dir=<path of full backup> apply-incremental-backup

If the incremental backup has already been applied you will see:

mysqlbackup: ERROR: The apply-incremental-backup operation encountered an incremental backup, which has already been applied.  You can force the re-application with --force.

If you try to roll an incremental out of order then you will see:

mysqlbackup: ERROR: The apply-incremental-backup operation encountered an incremental start-LSN which is higher than the full backup end-LSN. This incremental backup cannot be applied on this full backup. One or more earlier incremental backups have to be applied first.

Once the rolling of the incremental backups is complete then the backup can be used to recover the database.

Run the restore of the full backup, with a copy-back command.

            mysqlbackup -u root --backup-dir=<path> copy-back

•           Start the mysql instance and the restore is complete, please check the mysqld.log file for any issues.

Point In Time Recovery (PITR)

NOTE - To perform a PITR using mysqlbackup the appropriate binary logs are need to be available. 

            Perform a normal restore of the database as required.  Once MEB has completed the database is then available for point in time recovery

•           Look in the mysqld.log for the appropriate line that gives the binary log number and location to start repeating the required transactions.

For example

InnoDB: Last MySQL binlog file position 0 2521, file name /db/DB1/logs/bin-logs/mysql-bin.000004

            Check are all the binary logs from mysql-bin.000004 available.

•           Use the mysqlbinlog program to create a sql file with all transactions from the appropriate binary log and location.

For example

mysqlbinlog --start-position=2521 /db/DB1/logs/bin-logs/mysql-bin.000004 > binlog.sql

Then perform the same for all logs required:

mysqlbinlog /db/DB1/logs/bin-logs/mysql-bin.000005 >> binlog.sql

mysqlbinlog /db/DB1/logs/bin-logs/mysql-bin.000006 >> binlog.sql

•           Replay the transactions into the database (this may take a while)

mysql –u <user> -p < binlog.sql

Please note this will create more binary logs, ensure you have enough filesystem space for this.

Partial restores are more complex.   There are some considerations for doing this. 

If you have not dropped or truncated the table a single table restore is possible.  This is because the tables internal id has changed and may cause an ID mismatch.

If you have not dropped or truncated the table.  The procedure is:

Lock the table to restore

            local tables <name> write;

Drop the ibd file from datadir

            alter table <name> discard tablespace;

Copy the ibd file from the backup to the datadir diectory

Import the tablespace

            alter table <name> import tablespace;

Unlock the table -

            unlock tables;

If you have dropped the table the you could try:

http://www.chriscalender.com/?p=28


Restoring a Partitioning Table
Once the .par, .ibd and .frm are safely backed up, how do you restore them?  In any version below 5.6 it does pose a problem and is something to think about.  If the table has not been dropped or truncated and is viable for restore the usual method doesn't seem to work on a partition storage engine:

DB1> lock table testpart write;
Query OK, 0 rows affected (0.00 sec)

DB1> alter table testpart discard tablespace;
ERROR 1031 (HY000): Table storage engine for 'testpart' doesn't have this option

It is something to be aware of and if you plan to restore a single table that is partitioned, maybe a test or two is the way to go.

Lastly when restoring a  backup if you run out of space on the filesystem you are using the backup will just hang.  If you are backing up MyISAM tables this will hold a lock (there is a feature in 3.8 which allows you not to hold a lock) until either more space is added or the backup is killed (if you kill the backup then check the database for any locks).