Sometimes when performing a MySQL backup you do not need to back up all of your data – incremental or differential backups may also not be an option. In that case, you may turn your view towards partial backups.
Partial Backups in MySQL
Simply put, partial backups in MySQL mean that you only back up only specific tables or databases. In MySQL, there are various ways to create partial backups, we will discuss two:
- Making a partial backup using MySQL Enterprise Backup;
- Making a partial backup using Percona’s XtraBackup.
Partial Backups with MySQL Enterprise Backup
We will start off by using MySQL Enterprise Backup to perform partial backups. As you might remember (the tool is covered in-depth in this blog), MySQL Enterprise Backup can be invoked by typing mysqlbackup. Partial backups can be performed by utilizing one of many of its features:
--include-tablesoption specifies the tables that should be included in the backup. Other tables will be avoided. This option can be used together with –exclude-tables: in that case MySQL will first select all tables specified by –include-tables, then exclude tables specified by –
-exclude-tables. Should be used together with regular expressions.
--exclude-tablesoption specifies the tables that should be excluded from the backup. Should be used together with regular expressions.
--only-innodboption only backs up InnoDB data and log files.
--only-known-file-typesoption only backs up MySQL data files with .ARM, .ARZ, .CSM, .CSV, .ibd, .MRG, .MYD, .MYI and .opt extensions.
--use-ttsoption enables selective backup of InnoDB tables using transportable tablespaces (tablespaces can then be moved from one instance to another). Should be used in conjunction with the –include-tables and –exclude-tables options. This option can only back up tables that were created when the innodb_file_per_table option was enabled.
--renameoption renames a single table when the –include-tables and (or) –exclude-tables options are used. Should be used when data is being restored to a database from a backup which was created using the –use-tts option.
Here’s a sample of mysqlbackup being used to perform a partial backup:
$ mysqlbackup --defaults-file=/home/directory/my.cnf --only-innodb backup-to-imageThis command will back up all InnoDB tables excluding the .frm files.
Partial Backups with Percona XtraBackup
If the tables you want to back up reside in separate tablespaces (meaning they were created or altered after the file_per_table option was enabled), partial backups can also be created using Percona XtraBackup:
- Databases can be backed up by specifying the –databases option:
$ innobackupex --databases=”database.table” /backup/directory
Specify a space separated list of databases and tables to backup, or, if you want, you can also specify a file containing one element per one line. The command above will create a directory with the files that innobackupex creates, but the data files that are backed up will only include files related to the specified table in a specified database.
- Tables can be specified using a regex pattern:
$ innobackupex --include=’^database.table’ /backup/directory
The above command will create a directory with the files that
innobackupex creates, but the data files that are backed up will only include the files matching the regular expression. The regular expression will be matched against the database name and the table name (database[.]table).
- Tables can also be specified in a file using the –tables-file option:
$ innobackupex --tables-file=/tmp/tables.txt /backup/directory
The file should have one table name per one line. The command will create a directory with the files that innobackupex creates, but the data files that are backed up will only include the files related to the tables in the file.
Restoring Partial Backups with MySQL Enterprise Backup
To restore a partial backup using MySQL Enterprise Backup, you can use the –copy-back-and-apply-log command which extracts the backup from a given image and copies it to the data directory on the server, then performs an apply-log operation which brings the data into a consistent state. Here’s an example:
$ mysqlbackup --defaults-file=/etc/mysql/my.cnf -uroot --backup-image=/home/mysql_backups/my.mbi --backup-dir=/tmp/backups --datadir=/backups/restoredir --copy-back-and-apply-log
In this case,
--defaults-file defines the location of the
my.cnf file, –backup-image provides the path to a single-file backup, –backup-dir provides the location to an empty folder where some temporary files will be stored,
--datadir defines the location of the data directory for restoring the data and –
-copy-back-and-apply-log extracts the backup, copies it to a data directory and performs an apply-log operation. If you want to restore files from a directory backup, the
option can also be used, though note that before doing so you must make the backup consistent by using the
Restoring Partial Backups with Percona XtraBackup
Partial backups can also be restored using Percona XtraBackup. To restore your data, restore it as if you would be restoring specific tables:
- Use the –export option prepare the backup:
$ innobackupex --apply-log --export /home/directory/partialbackup
/home/directory/partialbackup with the path to your partial backup. After this operation is done, the target directory should contain a few files with the .exp, .ibd and .cfg extensions. These tables will be needed in order to restore the partial backup.
- To import a table into another server, create a new table with the exact same structure as the table being imported:
CREATE TABLE tablename (...) ENGINE = InnoDB;
Then discard its tablespace:
ALTER TABLE tablename DISCARD TABLESPACE;
After that, copy the
expfiles to the database. If you’re importing data into a database that runs MySQL 5.6, copy the .cfg file instead of the .exp file.
Finally, import the tablespace:
ALTER TABLE tablename IMPORT TABLESPACE;
In MySQL partial backups mean the restoration of specific databases or tables within them – partial backups can be performed by using MySQL Enterprise Backup or Percona XtraBackup. Each of those tools has their own distinct advantages and disadvantages (for example, MySQL Enterprise Backup costs $5,000 while Percona XtraBackup is free, but MySQL Enterprise Backup comes with a lot of extra features – see our previous post about the tool for more information), so choose wisely.