Partial backup 备份指定表/库

Partial Backups

XtraBackup支持partial backups,这意味着你可以只备份部分表或库.要备份的表必须是独立表空间,即innodb_file_per_table=1
有一点需要注意的是,部分备份不要copy back prepared backup.restore partial backups应该用导入,而不是–copy-back

创建partial backups

有三种方式可以创建partial backups:
1.–include 支持正则
2.–tables-file 读取一个文件,备份其中写明的所有表
3.–databases 列出库名

–include方式

innobackupex --defaults-file=/data/mysqldata/3306/my.cnf --user=backup --password='backup' --include='^test[.]t.*[0-9]$' /data/mysqldata/backup/partial/

此方法发现一个问题

[mysql@master backup]$ ls partial/2016-08-19_18-42-28/test/t1.frm  t2.MYD              test_null#P#p1.ibd  trb3.frm       trb3#P#p1.ibd  trb4.frm       trb4#P#p1.ibdt1.ibd  t2.MYI              test_null#P#p2.ibd  trb3.par       trb3#P#p2.ibd  trb4.par       trb4#P#p2.ibdt2.frm  test_null#P#p0.ibd  test_null#P#p3.ibd  trb3#P#p0.ibd  trb3#P#p3.ibd  trb4#P#p0.ibd  trb4#P#p3.ibd备份中包含了test_null表,而test_null表并不匹配我的正则表达式,但是为什么备份到了?(test_null为分区表)创建分区表thehe,分区名称p0 p1 p2 p3 p4CREATE TABLE thehe (id INT, name VARCHAR(50), purchased DATE)    PARTITION BY RANGE( YEAR(purchased) ) (        PARTITION p0 VALUES LESS THAN (1990),        PARTITION p1 VALUES LESS THAN (1995),        PARTITION p2 VALUES LESS THAN (2000),        PARTITION p3 VALUES LESS THAN (2005)    );创建分区表tpapa,分区名称 pa pb pc pdCREATE TABLE tpapa (id INT, name VARCHAR(50), purchased DATE)    PARTITION BY RANGE( YEAR(purchased) ) (        PARTITION pa VALUES LESS THAN (1990),        PARTITION pb VALUES LESS THAN (1995),        PARTITION pc VALUES LESS THAN (2000),        PARTITION pd VALUES LESS THAN (2005)    );再次备份innobackupex --defaults-file=/data/mysqldata/3306/my.cnf --user=backup --password='backup' --include='^test[.]t.*[0-9]$' /data/mysqldata/backup/partial3/[mysql@master backup]$ ls partial3/2016-08-19_18-49-31/test/t1.frm  t2.MYD          thehe#P#p1.ibd  trb3.frm       trb3#P#p1.ibd  trb4.frm       trb4#P#p1.ibdt1.ibd  t2.MYI          thehe#P#p2.ibd  trb3.par       trb3#P#p2.ibd  trb4.par       trb4#P#p2.ibdt2.frm  thehe#P#p0.ibd  thehe#P#p3.ibd  trb3#P#p0.ibd  trb3#P#p3.ibd  trb4#P#p0.ibd  trb4#P#p3.ibd只有thehe被备份了这说明,对于分区表,每个分区在这里被当做一个表,表名是 原table_name+分区名创建分区表tpapa,分区名称 p1 pb pc p4CREATE TABLE txxoo (id INT, name VARCHAR(50), purchased DATE)    PARTITION BY RANGE( YEAR(purchased) ) (        PARTITION pa VALUES LESS THAN (1990),        PARTITION pb VALUES LESS THAN (1995),        PARTITION pc VALUES LESS THAN (2000),        PARTITION pd VALUES LESS THAN (2005)    );再次备份innobackupex --defaults-file=/data/mysqldata/3306/my.cnf --user=backup --password='backup' --include='^test[.]t.*[0-9]$' /data/mysqldata/backup/partial4/[mysql@master backup]$ ls partial4/2016-08-19_18-58-39/test/t1.frm  t2.MYD          thehe#P#p1.ibd  trb3.frm       trb3#P#p1.ibd  trb4.frm       trb4#P#p1.ibdt1.ibd  t2.MYI          thehe#P#p2.ibd  trb3.par       trb3#P#p2.ibd  trb4.par       trb4#P#p2.ibdt2.frm  thehe#P#p0.ibd  thehe#P#p3.ibd  trb3#P#p0.ibd  trb3#P#p3.ibd  trb4#P#p0.ibd  trb4#P#p3.ibd没有备份txxoo,因为若备份p1 p4分区,整个表就是不完整的备份

–tables-file

–tables-file选项后面跟的是一个包含需要备份的表的列表文件.格式为每行一个表,表名以databasename.tablename为格式

cat >> backup_list.txt <<!fandb.depttest.app_phone_download_speed_detailtest.quarterly_report_status!innobackupex --defaults-file=/data/mysqldata/3306/my.cnf --user=backup --password='backup' --no-timestamp --tables-file=/data/mysqldata/backup/backup_list.txt /data/mysqldata/backup/list

在备份过程中,未包含在备份列表中的表会提示skipping
160822 14:33:16 [01] Skipping ./mysql/innodb_table_stats.ibd.

备份的结构,可见只包含了在备份列表中的表

[mysql@master backup]$ tree list/list/|-- backup-my.cnf|-- fandb|   |-- dept.frm|   `-- dept.ibd|-- ibdata1|-- test|   |-- app_phone_download_speed_detail.frm|   |-- app_phone_download_speed_detail.ibd|   |-- quarterly_report_status.frm|   |-- quarterly_report_status.par|   |-- quarterly_report_status#P#p0.ibd|   |-- quarterly_report_status#P#p1.ibd|   |-- quarterly_report_status#P#p2.ibd|   |-- quarterly_report_status#P#p3.ibd|   |-- quarterly_report_status#P#p4.ibd|   |-- quarterly_report_status#P#p5.ibd|   |-- quarterly_report_status#P#p6.ibd|   |-- quarterly_report_status#P#p7.ibd|   |-- quarterly_report_status#P#p8.ibd|   `-- quarterly_report_status#P#p9.ibd|-- xtrabackup_binlog_info|-- xtrabackup_checkpoints|-- xtrabackup_info`-- xtrabackup_logfile

–databases

–database选项可以直接接收需要备份的”表”和”库”名,或接收一个包含所需备份表名的列表文件

innobackupex --defaults-file=/data/mysqldata/3306/my.cnf --user=backup --password='backup' --no-timestamp --databases="test.app_phone_download_speed_detail fandb" /data/mysqldata/backup/databases上面的命令备份了单个test.app_phone_download_speed_detail和全部fandb库下的表[mysql@master backup]$ tree databases/databases/|-- backup-my.cnf|-- fandb|   |-- db.opt|   |-- dept.frm|   |-- dept.ibd|   |-- emp.frm|   |-- emp.ibd|   |-- incr.frm|   |-- incr.ibd|   |-- l_csv.CSM|   |-- l_csv.CSV|   |-- l_csv.frm|   |-- master2.frm|   |-- master2.ibd|   |-- salary.frm|   `-- salary.ibd|-- ibdata1|-- test|   |-- app_phone_download_speed_detail.frm|   `-- app_phone_download_speed_detail.ibd|-- xtrabackup_binlog_info|-- xtrabackup_checkpoints|-- xtrabackup_info`-- xtrabackup_logfile文件列表cat >> db_list.txt <<!fandbtest.quarterly_report_status!innobackupex --defaults-file=/data/mysqldata/3306/my.cnf --user=backup --password='backup' --no-timestamp --databases=/data/mysqldata/backup/db_list.txt /data/mysqldata/backup/db_list[mysql@master backup]$ tree db_listdb_list|-- backup-my.cnf|-- fandb|   |-- db.opt|   |-- dept.frm|   |-- dept.ibd|   |-- emp.frm|   |-- emp.ibd|   |-- incr.frm|   |-- incr.ibd|   |-- l_csv.CSM|   |-- l_csv.CSV|   |-- l_csv.frm|   |-- master2.frm|   |-- master2.ibd|   |-- salary.frm|   `-- salary.ibd|-- ibdata1|-- test|   |-- quarterly_report_status.frm|   |-- quarterly_report_status.par|   |-- quarterly_report_status#P#p0.ibd|   |-- quarterly_report_status#P#p1.ibd|   |-- quarterly_report_status#P#p2.ibd|   |-- quarterly_report_status#P#p3.ibd|   |-- quarterly_report_status#P#p4.ibd|   |-- quarterly_report_status#P#p5.ibd|   |-- quarterly_report_status#P#p6.ibd|   |-- quarterly_report_status#P#p7.ibd|   |-- quarterly_report_status#P#p8.ibd|   `-- quarterly_report_status#P#p9.ibd|-- xtrabackup_binlog_info|-- xtrabackup_checkpoints|-- xtrabackup_info`-- xtrabackup_logfile

Preparing Partial Backups

innobackupex --apply-log --export /path/to/partial/backup

在prepare过程中你会看到一些错误

[mysql@master backup]$ innobackupex --apply-log --export /data/mysqldata/backup/db_listInnoDB: xtrabackup: Last MySQL binlog file position 2519, file name mysql-bin.000035InnoDB: Failed to find tablespace for table `sakila`.`FTS_0000000000000203_00000000000001c5_INDEX_1` in the cache. Attempting to load the tablespace with space id 507InnoDB: Operating system error number 2 in a file operation.InnoDB: The error means the system cannot find the path specified.InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.InnoDB: Cannot open datafile for read-only: './sakila/FTS_0000000000000203_00000000000001c5_INDEX_1.ibd' OS error: 71InnoDB: Operating system error number 2 in a file operation.InnoDB: The error means the system cannot find the path specified.

只要最后出现 160822 21:52:11 completed OK! 就是成功的

官方文档中的解释

You may see warnings in the output about tables that don’t exist. This is because InnoDB -based engines stores its data dictionary inside the tablespace files besides the .frm files. innobackupex will use xtrabackup to remove the missing tables (those who weren’t selected in the partial backup) from the data dictionary in order to avoid future warnings or errors:

111225 0:54:06 InnoDB: Error: table ‘mydatabase/mytablenotincludedinpartialb’
InnoDB: in InnoDB data dictionary has tablespace id 6,
InnoDB: but tablespace with that id or name does not exist. It will be removed from data dictionary.

You should also see the notification of the creation of a file needed for importing (.exp file) for each table included in the partial backup:

xtrabackup: export option is specified.
xtrabackup: export metadata of table ‘employees/departments’ to file .//departments.exp (2 indexes
xtrabackup: name=PRIMARY, id.low=80, page=3
xtrabackup: name=dept_name, id.low=81, page=4

需要注意的是,你可以对一个已经prepare的备份再一次执行 –apply-log –export来生产.exp文件

Restoring Partial Backups

在5.6版本前,即便你的innodb表开启了innodb_file_per_table 独立表空间,你也无法通过直接cp数据文件 来在server间复制表
但是有了PerconaXtrabackup,你懂的

首先export表
innobackupex –apply-log –export /path/to/backup
和之前的prepare是一样的,这个过程中会为每个innodb表创建.exp文件

[mysql@master backup]$ find /data/mysqldata/backup/db_list/ -name dept.*/data/mysqldata/backup/db_list/fandb/dept.ibd/data/mysqldata/backup/db_list/fandb/dept.frm/data/mysqldata/backup/db_list/fandb/dept.cfg/data/mysqldata/backup/db_list/fandb/dept.exp

其中除了.frm文件,剩下的三个文件是import过程中需要的

导入表:
1.创建表create table ..
2.discard tablespace
3.cp .ibd .cfg .exp文件到相应的目录中
4.import tablespace

(mysql@localhost) [(none)]> use fandb;Database changed(mysql@localhost) [fandb]> select * from dept;+--------+------------+----------+| deptno | dname      | loc      |+--------+------------+----------+|     10 | ACCOUNTING | NEW YORK ||     20 | RESEARCH   | DALLAS   ||     30 | SALES      | CHICAGO  ||     40 | OPERATIONS | BOSTON   |+--------+------------+----------+4 rows in set (0.01 sec)(mysql@localhost) [fandb]> drop table dept;Query OK, 0 rows affected (0.01 sec)CREATE TABLE `dept` (  `deptno` int(11) NOT NULL,  `dname` varchar(14) DEFAULT NULL,  `loc` varchar(13) DEFAULT NULL,  PRIMARY KEY (`deptno`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;ALTER TABLE dept DISCARD TABLESPACE;cp /data/mysqldata/backup/db_list/fandb/dept.ibd /data/mysqldata/3306/data/fandb/cp /data/mysqldata/backup/db_list/fandb/dept.cfg /data/mysqldata/3306/data/fandb/cp /data/mysqldata/backup/db_list/fandb/dept.exp /data/mysqldata/3306/data/fandb/ALTER TABLE dept IMPORT TABLESPACE;(mysql@localhost) [fandb]> select * from dept;+--------+------------+----------+| deptno | dname      | loc      |+--------+------------+----------+|     10 | ACCOUNTING | NEW YORK ||     20 | RESEARCH   | DALLAS   ||     30 | SALES      | CHICAGO  ||     40 | OPERATIONS | BOSTON   |+--------+------------+----------+4 rows in set (0.00 sec)

导入与备份时不同的库也是可以的

(mysql@localhost) [test]> drop table dept;Query OK, 0 rows affected (0.01 sec)(mysql@localhost) [test]> CREATE TABLE `dept` (    ->   `deptno` int(11) NOT NULL,    ->   `dname` varchar(14) DEFAULT NULL,    ->   `loc` varchar(13) DEFAULT NULL,    ->   PRIMARY KEY (`deptno`)    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8    ->     -> ;Query OK, 0 rows affected (0.00 sec)(mysql@localhost) [test]> ALTER TABLE dept DISCARD TABLESPACE;Query OK, 0 rows affected (0.01 sec)cp /data/mysqldata/backup/db_list/fandb/dept.ibd /data/mysqldata/3306/data/test/cp /data/mysqldata/backup/db_list/fandb/dept.cfg /data/mysqldata/3306/data/test/cp /data/mysqldata/backup/db_list/fandb/dept.exp /data/mysqldata/3306/data/test/(mysql@localhost) [test]> ALTER TABLE dept IMPORT TABLESPACE;Query OK, 0 rows affected (0.01 sec)(mysql@localhost) [test]>  select * from dept;+--------+------------+----------+| deptno | dname      | loc      |+--------+------------+----------+|     10 | ACCOUNTING | NEW YORK ||     20 | RESEARCH   | DALLAS   ||     30 | SALES      | CHICAGO  ||     40 | OPERATIONS | BOSTON   |+--------+------------+----------+4 rows in set (0.00 sec)