Friday Jan 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 

About

Jeremy Smyth writes MySQL training courses, and likes exploring interesting questions that come up from novices and experts alike.

Connect

Search

Categories
Archives
January 2013 »
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
12
13
14
15
16
18
19
20
21
22
23
24
26
27
28
29
30
  
       
Today