Percona has developed some pretty amazing tools, and at the top of my list is their innobackupex script; a wrapper for the xtrabackup tool (also developed by Percona).

Innobackupex is a great tool that I have recently switched to use to create backups on my database, which is a mix of InnoDB and MyISAM storage engines. There is, however, one issue that innobackupex doesn’t appear to handle natively: I would like to exclude certain tables from a regular backup.

In my dataset, I have about 500 tables in 3 schemas. In the main schema, I have audit tables mixed with the main relational data. 90% of the backup size comes from these audit tables, so I would like to exclude them to be able to take full backups quickly and be able to restore even more quickly in the event of an emergency.

Innobackupex does provide the ability to create partial backups, which allows for including a list of tables or databases through three different methods:

• 1) a regex pattern passed to the ‘–include’ option
• 2) a list of tables in a file using the ‘–tables-file’ option
• 3) A list of databases and tables passed to the ‘–databases’ option

However, it does not appear that one can exclude a list of tables from the backup process. In my case, all of my audit tables are preceded with the ‘track_’ prefix. So naturally, I attempted to use the ‘include’ option to pass a regex to include everything except ‘track’:

innobackupex --include='[^track\_]' /path/to/backup

No dice. Looking at the other two methods, I developed an idea that does work. Using MySQL’s INFORMATION_SCHEMA, I am able to generate a list of tables to put into a tables file which will be included in the file.

$ mysql --skip-column-names -e "SELECT CONCAT(table_schema,'.',table_name) FROM information_schema.tables WHERE table_name NOT REGEXP '^track_';" > tables.txt
$ innobackupex --tables-file=tables.txt /path/to/backup

These partial backups will dramatically improve the speed to restore vital information that may have accidentally been deleted. But even though I do these partial backups, I also create daily full backups that does include the audit information.

It is worth noting that I found this old patch on innobackupex-1.5.1 that attempted to add an ‘–exclude’ option, but since it was discarded I did not attempt to use it.

Be Sociable, Share!

•