MySQL and MySQL Community information
- January 11, 2013
Excluding a Table From Backup
Let's say you have a database that stores not only current transactional data, but also historic data that's unchanging. In particular, you have a large table containing hundreds of gigabytes worth of last year's data, and it won't change. Having backed it up already, you don't need to back it up every time. Is there any way to exclude this table from a backup?
For InnoDB tables with innodb-file-per-table enabled (the default as of MySQL 5.6), MySQL Enterprise Backup supports this feature in inverse. Specifically, you can choose to include specific innodb-file-per-table tables in addition to those stored in the system tablespace.
In order to exclude a specific table, you need to provide a regular expression to the --include option that includes every table except the one you want to exclude. For example, in my sakila database, I know that the payment table is the only one beginning with "p", so if I want to exclude it, I can do this:
# mysqlbackup --datadir=/var/lib/mysql --backup_dir=/backups \ > --include 'sakila\.[^p].*' backup-and-apply-log
The following link contains detailed information on the --include option:
http://dev.mysql.com/doc/mysql-enterprise-backup/3.7/en/only-innodb-usage.html
You can also specify filters for non-InnoDB tables using options such as --databases and --databases-list-file. The following link covers a variety of different partial backup techniques:
http://dev.mysql.com/doc/mysql-enterprise-backup/3.7/en/partial.html