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