As far as MySQL backups are concerned, developers and DBAs have a few options: often, the problem can be easily solved by just using mysqldump which can be used to perform backups that reproduce data, but avoid reproducing physical files. In other scenarios though, you might have to look at different solutions – one of them is MySQL Enterprise Backup.
What is MySQL Enterprise Backup?
As the name suggests, MySQL Enterprise Backup is MySQL’s enterprise-grade backup and recovery tool. The tool has many unique features including:
- Hot backups – backups are performed without shutting down the database;
- Partial backups – backups can be performed only on specific tables or tablespaces;
- Incremental backups – backups can be performed by only backing up data that has changed.
The full list of MySQL Enterprise Backup features can be seen here. While the tool offers a lot of features, it is worth noting that the tool is an enterprise edition weapon meaning that it is not free – the standard version of the license will set you back $5,000.
Understanding MySQL Enterprise Backup
In a nutshell, MySQL Enterprise Backup:
- Is optimized to be used with InnoDB tables;
- Executes functions using the mysqlbackup client;
- Is able to back up different types of files;
- Enables users to choose between multiple backup scenarios;
- Offers backup encryption.
Of course, there are other scenarios where using MySQL Enterprise Backup can be very helpful (refer to the MySQL Enterprise Backup features link above), but we’ve chosen a few important ones. Now, we will dive deeper into what all of those points mean.
Can I only use the tool if my tables have the InnoDB storage engine?
Well, yes and no. You see, the tool can also be used if your database contains MyISAM or other storage engines, but you should keep in mind that in such a scenario tables will be backed up using the warm backup technique meaning that the backup will be taken while the database is still running, but you might experience some interruptions during the backup process. The MySQL server that is being backed up must also support InnoDB – for example, if the server was started with the –innodb=OFF option, the backup will fail. Your database must also contain at least one InnoDB table.
MySQL Enterprise Backup is optimized to be used with InnoDB tables because if your database is running InnoDB, it can back up:
- The ibdata* files – they make up the InnoDB system tablespace;
- The .ibd files – they represent InnoDB tablespaces created when the file_per_table setting was enabled;
- The data extracted from ib_logfile* files.
That’s not to say that the tool cannot be used to back up MyISAM table data though. If MyISAM tables are in use, it can back up the following files:
- The .MYD files – they store MyISAM table data;
- The .MYI files – they store MyISAM table indexes;
- The .frm files – they store metadata about the tables.
If you’re backing up MyISAM tables though, keep in mind that they will be backed up using the warm backup technique which may cause your database to become unresponsive for a while.
The mysqlbackup Client
All MySQL Enterprise Backup functions are executed through the mysqlbackup client. In general, there are five types of commands that can be executed:
- Backup operations – various kinds of backups can be taken by adding different options like –compress to compress backups or –incremental to take incremental backups;
- Single-file backup operations can also be taken by specifying the –backup-dir-to-image option. This option can be used to pack an existing backup directory into a single file;
- Update operations – the –apply-log option can be specified to make the InnoDB tables in a specified directory up to date and the apply-incremental-backup option can update data in one backup directory with data in an incremental backup directory;
- Restore operations – the copy-back option can be specified to restore backups from a directory backup into their original locations;
Validation operations – the validate option can help verify that the backup is not corrupted or damaged.
The tool can be used like this: $ mysqlbackup --user=root --password --port=3306 [OPTIONS] backup
Backing Up Different Types of Files
MySQL Enterprise Backup is also able to back up different types of files including, but not limited to (the full list of the types of files contained in a backup can be seen here):
- The ibdata* files containing several classes of information vital for InnoDB to function;
- The .ibd files representing InnoDB tables;
- The .ibz files – the compressed form of InnoDB data files;
- The *.opt files containing the database configuration information;
- The backup-my.cnf file which records the MySQL configuration parameters.
The tool also backs up the .MYD, .MYI and .sdi files associated with MyISAM tables. Once again, keep in mind that MyISAM tables cannot be backed up the same way InnoDB tables can: MyISAM tables are backed up using the warm backup technique, which may cause issues.
Different Backup Scenarios
MySQL Enterprise Backup can also be used for different backup scenarios – a full, partial, or an incremental backup.
- If you want to perform a full backup (a backup that includes all tables in each MySQL database and all databases inside a MySQL instance), you can use the following example:
$ ./mysqlbackup --user=root --password --host=127.0.0.1 --backup-image=/home/directory/backup.mbi --backup-dir=/home/backup-tmp backup-to-image
The –backup-image option specifies the destination file and the –backup-dir option specifies the backup directory. The backup-to-image option allows you to create a single-file backup.
- In order to perform a partial backup, you can specify what tables you want to back up using regular expressions, include or exclude tables by their names, back up all InnoDB tables etc. – here are a few examples:
To back up all InnoDB tables, but not .frm files use:
$ mysqlbackup --defaults-file=/home/directory/my.cnf --only-innodb backup-to-image
To back up all tables excluding tables from mysql and performance_schema databases:
$ mysqlbackup --host=localhost --user=root --protocol=TCP --port=3306 --backup-dir=/home/backup-tmp --backup-image=/home/directory/my.mbi --exclude-tables="^(mysql|performance_schema)\." backup-to-image
- MySQL Enterprise Backup has two ways to perform an incremental backup – you can either use the –incremental-base option or the –start-lsn option. When taking an incremental backup the –start-lsn option specifies the highest Log Sequence Number included in a previous backup – this value can be acquired through the output of a previous backup operation. When using the –incremental-base option, a LSN value isn’t required – you can specify the location of a previous directory backup. Examples:
--incremental-base example: $ mysqlbackup --defaults-file=/home/directory/.my.cnf --incremental --incremental-base=dir:/backups/monday --incremental-backup-dir=/backups/tuesday backup
LSN example (replace the 0000000000 with your LSN value): $ mysqlbackup --defaults-file=/dir/.my.cnf --incremental --start-lsn=0000000000 --with-timestamp --incremental-backup-dir=/backup backup
If you’re using MySQL Enterprise Backup, you can also benefit from backup encryption.
Data is encrypted with a key provided by the user. To generate a key, use the command below (the key will be displayed underneath):
$ echo "password" | shasum -a 256
To encrypt data:
$ mysqlbackup --backup-image=/backup/data.enc --encrypt --key=GeneratedKey --backup-dir=/backup backup-to-image
You can also use a key file by replacing the –key=GeneratedKey part with a –key-file parameter.
In order to decrypt a backup, use the –decrypt option and specify the extract keyword:
$ mysqlbackup --backup-image=/backup/data.enc --decrypt --key-file=/directory/key --backup-dir=/any/dir extract
MySQL Enterprise Backup is a tool that is capable of a lot of things. If you need your backup operations to be performant while dealing with complex backup operations (for example, taking incremental, partial, full, or encrypted backups), consider giving it a go. Before doing so though, weigh all your options and do note that this tool is not free.