使用xtrabackup进行个别数据库或表备份、恢复

2019独角兽企业重金招聘Python工程师标准>>>

前提：备份的表必须在单独的表空间，系统变量innodb_file_per_table设置为1；

可以通过xtrabackup进行数据备份，然后在新服务器通过导入表空间实现数据的快速迁移；

一、创建备份

有三种方法指定需要备份的数据表：

a、--include：正则表达式，完全匹配表名，包括数据库名，如databasename.tablename

该选项传递给xtrabackup --tables，匹配每个数据库的每一个表，没有备份的数据库也会创建一个备份目录。

innobackupex --include='^mydatabase[.]mytable' /path/to/backup

测试：

1. innobackupex --defaults-file=/opt/mariadb-10.0.12-linux-x86_64/my.cnf --user=backup --password=backup@123 --include='^test' /data/backup/physical/full/ 

实际执行的命令：

140810 11:33:58  innobackupex: Starting ibbackup with command: xtrabackup  --defaults-file="/opt/mariadb-10.0.12-linux-x86_64/my.cnf"  --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/data/backup/physical/full/2014-08-10_11-33-58 --tmpdir=/tmp --tables='^test' --extra-lsndir='/tmp'

查看备份后的文件：可以看到备份了共享表空间和test库，但是其他数据库也创建了空的目录

1. [root@myhost02 full]# ll 2014-08-10_11-33-58/
2. total 2113580
3. -rw-r--r-- 1 root root        370 Aug 10 11:33 backup-my.cnf
4. drwxr-xr-x 2 root root       4096 Aug 10 11:34 employees
5. -rw-r----- 1 root root 2147483648 Aug 10 11:34 ibdata1
6. -rw-r----- 1 root root   16777216 Aug 10 11:34 ibdata2
7. drwxr-xr-x 2 root root       4096 Aug 10 11:34 mysql
8. drwxr-xr-x 2 root root       4096 Aug 10 11:34 percona
9. drwxr-xr-x 2 root root       4096 Aug 10 11:34 performance_schema
10. drwxr-xr-x 2 root root       4096 Aug 10 11:34 sakila
11. drwx------ 2 root root       4096 Aug 10 11:34 test
12. -rw-r----- 1 root root         95 Aug 10 11:34 xtrabackup_checkpoints
13. -rw-r--r-- 1 root root        616 Aug 10 11:34 xtrabackup_info
14. -rw-r----- 1 root root       2560 Aug 10 11:34 xtrabackup_logfile
15. [root@myhost02 full]# ll 2014-08-10_11-33-58/employees/
16. total 0
17. [root@myhost02 full]# ll 2014-08-10_11-33-58/mysql/
18. total 0
19. [root@myhost02 full]# ll 2014-08-10_11-33-58/percona/
20. total 0
21. [root@myhost02 full]# ll 2014-08-10_11-33-58/performance_schema/
22. total 0
23. [root@myhost02 full]# ll 2014-08-10_11-33-58/sakila/
24. total 0
25. [root@myhost02 full]# ll 2014-08-10_11-33-58/test/
26. total 251992
27. -rw-r--r-- 1 root root      1834 Aug 10 11:34 sbtest1.frm
28. -rw-r----- 1 root root 255852544 Aug 10 11:34 sbtest1.ibd
29. -rw-r--r-- 1 root root       946 Aug 10 11:34 t1.frm
30. -rw-r----- 1 root root    655360 Aug 10 11:34 t1.ibd
31. -rw-r--r-- 1 root root       946 Aug 10 11:34 t2.frm
32. -rw-r----- 1 root root    655360 Aug 10 11:34 t2.ibd
33. -rw-r--r-- 1 root root       946 Aug 10 11:34 t3.frm
34. -rw-r----- 1 root root    655360 Aug 10 11:34 t3.ibd
35. -rw-r----- 1 root root       946 Aug 10 11:34 t4.frm
36. -rw-r----- 1 root root     98304 Aug 10 11:34 t4.ibd
37. -rw-r----- 1 root root       457 Aug 10 11:34 t5.frm
38. -rw-r----- 1 root root     98304 Aug 10 11:34 t5.ibd
39. [root@myhost02 full]# 

b、--tables-file：包含表的文件，能包含多个表名，每行一个，格式如databasename.tablename

该选项传递给xtrabackup --tables-file，仅仅有选择表的数据库将创建备份目录。

$ echo "mydatabase.mytable" > /tmp/tables.txt

$ innobackupex --tables-file=/tmp/tables.txt /path/to/backup

测试：

将要备份的test.t1-t5表写到一个文件，每个表一行

1. # cat /tmp/tables 
2. test.t1
3. test.t2
4. test.t3
5. test.t4
6. test.t5

执行备份：

1. innobackupex --defaults-file=/opt/mariadb-10.0.12-linux-x86_64/my.cnf --user=backup --password=backup@123 --tables-file=/tmp/tables /data/backup/physical/full/

实际执行的xtrabackup命令：

140810 11:31:11  innobackupex: Starting ibbackup with command: xtrabackup  --defaults-file="/opt/mariadb-10.0.12-linux-x86_64/my.cnf"  --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/data/backup/physical/full/2014-08-10_11-31-11 --tmpdir=/tmp --extra-lsndir='/tmp' --tables_file='/tmp/tables'

查看备份后的文件：备份了共享表空间，和test下的t1-t5表，其他没有进行备份，也没有产生空目录

1. [root@myhost02 full]# ll 2014-08-10_11-31-11/
2. total 2113560
3. -rw-r--r-- 1 root root        370 Aug 10 11:31 backup-my.cnf
4. -rw-r----- 1 root root 2147483648 Aug 10 11:31 ibdata1
5. -rw-r----- 1 root root   16777216 Aug 10 11:31 ibdata2
6. drwx------ 2 root root       4096 Aug 10 11:31 test
7. -rw-r----- 1 root root         95 Aug 10 11:31 xtrabackup_checkpoints
8. -rw-r--r-- 1 root root        626 Aug 10 11:31 xtrabackup_info
9. -rw-r----- 1 root root       2560 Aug 10 11:31 xtrabackup_logfile
10. [root@myhost02 full]# ll 2014-08-10_11-31-11/test/
11. total 2132
12. -rw-r--r-- 1 root root    946 Aug 10 11:31 t1.frm
13. -rw-r----- 1 root root 655360 Aug 10 11:31 t1.ibd
14. -rw-r--r-- 1 root root    946 Aug 10 11:31 t2.frm
15. -rw-r----- 1 root root 655360 Aug 10 11:31 t2.ibd
16. -rw-r--r-- 1 root root    946 Aug 10 11:31 t3.frm
17. -rw-r----- 1 root root 655360 Aug 10 11:31 t3.ibd
18. -rw-r----- 1 root root    946 Aug 10 11:31 t4.frm
19. -rw-r----- 1 root root  98304 Aug 10 11:31 t4.ibd
20. -rw-r----- 1 root root    457 Aug 10 11:31 t5.frm
21. -rw-r----- 1 root root  98304 Aug 10 11:31 t5.ibd

c、--databases：提供数据库列表，可以是空格分隔的数据库和表，格式如databasename[.tablename]；或者每一行一个元素的文件

仅仅创建需要备份的相关目录；当前2.1.8版本，该选项只限制.frm和非Innodb表；

特别要注意使用--databases选项进行备份，将会备份数据库中所有表的idb文件(除非innodb的表不备份)，只备份--databases指定表或数据库中表的.frm文件

$ innobackupex --databases="mydatabase.mytable mysql" /path/to/backup

测试：

1. innobackupex --defaults-file=/opt/mariadb-10.0.12-linux-x86_64/my.cnf --user=backup --password=backup@123 --databases="test" /data/backup/physical/full/

140810 11:39:22  innobackupex: Starting ibbackup with command: xtrabackup  --defaults-file="/opt/mariadb-10.0.12-linux-x86_64/my.cnf"  --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/data/backup/physical/full/2014-08-10_11-39-22 --tmpdir=/tmp --extra-lsndir='/tmp'

查看备份后的文件：可以看到备份了所有.ibd文件，只有用--databases指定的库或表的frm表结构进行了备份，其他非innodb表没有进行备份

1. [root@myhost02 full]# ll 2014-08-10_11-24-37/
2. total 2113576
3. -rw-r--r-- 1 root root        370 Aug 10 11:24 backup-my.cnf
4. drwx------ 2 root root       4096 Aug 10 11:25 employees
5. -rw-r----- 1 root root 2147483648 Aug 10 11:25 ibdata1
6. -rw-r----- 1 root root   16777216 Aug 10 11:25 ibdata2
7. drwx------ 2 root root       4096 Aug 10 11:25 mysql
8. drwx------ 2 root root       4096 Aug 10 11:25 percona
9. drwx------ 2 root root       4096 Aug 10 11:25 sakila
10. drwx------ 2 root root       4096 Aug 10 11:25 test
11. -rw-r----- 1 root root         95 Aug 10 11:25 xtrabackup_checkpoints
12. -rw-r--r-- 1 root root        617 Aug 10 11:25 xtrabackup_info
13. -rw-r----- 1 root root       2560 Aug 10 11:25 xtrabackup_logfile
14. [root@myhost02 full]# ll 2014-08-10_11-24-37/employees/
15. total 237808
16. -rw-r----- 1 root root    114688 Aug 10 11:25 departments.ibd
17. -rw-r----- 1 root root  31457280 Aug 10 11:25 dept_emp.ibd
18. -rw-r----- 1 root root    131072 Aug 10 11:25 dept_manager.ibd
19. -rw-r----- 1 root root  23068672 Aug 10 11:25 employees.ibd
20. -rw-r----- 1 root root 146800640 Aug 10 11:25 salaries.ibd
21. -rw-r----- 1 root root  41943040 Aug 10 11:25 titles.ibd
22. [root@myhost02 full]# ll 2014-08-10_11-24-37/mysql/
23. total 352
24. -rw-r----- 1 root root  98304 Aug 10 11:25 gtid_slave_pos.ibd
25. -rw-r----- 1 root root 163840 Aug 10 11:25 innodb_index_stats.ibd
26. -rw-r----- 1 root root  98304 Aug 10 11:25 innodb_table_stats.ibd
27. [root@myhost02 full]# ll 2014-08-10_11-24-37/sakila/
28. total 22864
29. -rw-r----- 1 root root   114688 Aug 10 11:25 actor.ibd
30. -rw-r----- 1 root root   163840 Aug 10 11:25 address.ibd
31. -rw-r----- 1 root root    98304 Aug 10 11:25 category.ibd
32. -rw-r----- 1 root root   114688 Aug 10 11:25 city.ibd
33. -rw-r----- 1 root root    98304 Aug 10 11:25 country.ibd
34. -rw-r----- 1 root root   196608 Aug 10 11:25 customer.ibd
35. -rw-r----- 1 root root   344064 Aug 10 11:25 film_actor.ibd
36. -rw-r----- 1 root root   147456 Aug 10 11:25 film_category.ibd
37. -rw-r----- 1 root root   344064 Aug 10 11:25 film.ibd
38. -rw-r----- 1 root root   442368 Aug 10 11:25 inventory.ibd
39. -rw-r----- 1 root root    98304 Aug 10 11:25 language.ibd
40. -rw-r----- 1 root root 10485760 Aug 10 11:25 payment.ibd
41. -rw-r----- 1 root root 10485760 Aug 10 11:25 rental.ibd
42. -rw-r----- 1 root root   147456 Aug 10 11:25 staff.ibd
43. -rw-r----- 1 root root   131072 Aug 10 11:25 store.ibd
44. [root@myhost02 full]# ll 2014-08-10_11-24-37/test/
45. total 251992
46. -rw-r--r-- 1 root root      1834 Aug 10 11:25 sbtest1.frm
47. -rw-r----- 1 root root 255852544 Aug 10 11:25 sbtest1.ibd
48. -rw-r--r-- 1 root root       946 Aug 10 11:25 t1.frm
49. -rw-r----- 1 root root    655360 Aug 10 11:25 t1.ibd
50. -rw-r--r-- 1 root root       946 Aug 10 11:25 t2.frm
51. -rw-r----- 1 root root    655360 Aug 10 11:25 t2.ibd
52. -rw-r--r-- 1 root root       946 Aug 10 11:25 t3.frm
53. -rw-r----- 1 root root    655360 Aug 10 11:25 t3.ibd
54. -rw-r----- 1 root root       946 Aug 10 11:25 t4.frm
55. -rw-r----- 1 root root     98304 Aug 10 11:25 t4.ibd
56. -rw-r----- 1 root root       457 Aug 10 11:25 t5.frm
57. -rw-r----- 1 root root     98304 Aug 10 11:25 t5.ibd

使用--databases只备份一些表：

1. innobackupex --defaults-file=/opt/mariadb-10.0.12-linux-x86_64/my.cnf --user=backup --password=backup@123 --databases="test.t4 test.t5" /data/backup/physical/full/

140810 11:42:24  innobackupex: Starting ibbackup with command: xtrabackup  --defaults-file="/opt/mariadb-10.0.12-linux-x86_64/my.cnf"  --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/data/backup/physical/full/2014-08-10_11-42-24 --tmpdir=/tmp --extra-lsndir='/tmp'

查看备份后的文件：备份了所有.ibd文件，同时只备份了--databases指定的表结构文件，其他非innodb表没有备份

1. [root@myhost02 full]# ll 2014-08-10_11-42-24/
2. total 2113576
3. -rw-r--r-- 1 root root        370 Aug 10 11:42 backup-my.cnf
4. drwx------ 2 root root       4096 Aug 10 11:42 employees
5. -rw-r----- 1 root root 2147483648 Aug 10 11:42 ibdata1
6. -rw-r----- 1 root root   16777216 Aug 10 11:42 ibdata2
7. drwx------ 2 root root       4096 Aug 10 11:42 mysql
8. drwx------ 2 root root       4096 Aug 10 11:42 percona
9. drwx------ 2 root root       4096 Aug 10 11:42 sakila
10. drwx------ 2 root root       4096 Aug 10 11:43 test
11. -rw-r----- 1 root root         95 Aug 10 11:43 xtrabackup_checkpoints
12. -rw-r--r-- 1 root root        628 Aug 10 11:43 xtrabackup_info
13. -rw-r----- 1 root root       2560 Aug 10 11:43 xtrabackup_logfile
14. [root@myhost02 full]# ll 2014-08-10_11-42-24/employees/
15. total 237808
16. -rw-r----- 1 root root    114688 Aug 10 11:42 departments.ibd
17. -rw-r----- 1 root root  31457280 Aug 10 11:42 dept_emp.ibd
18. -rw-r----- 1 root root    131072 Aug 10 11:42 dept_manager.ibd
19. -rw-r----- 1 root root  23068672 Aug 10 11:42 employees.ibd
20. -rw-r----- 1 root root 146800640 Aug 10 11:42 salaries.ibd
21. -rw-r----- 1 root root  41943040 Aug 10 11:42 titles.ibd
22. [root@myhost02 full]# ll 2014-08-10_11-42-24/mysql/
23. total 352
24. -rw-r----- 1 root root  98304 Aug 10 11:42 gtid_slave_pos.ibd
25. -rw-r----- 1 root root 163840 Aug 10 11:42 innodb_index_stats.ibd
26. -rw-r----- 1 root root  98304 Aug 10 11:42 innodb_table_stats.ibd
27. [root@myhost02 full]# ll 2014-08-10_11-42-24/percona/
28. total 160
29. -rw-r----- 1 root root 163840 Aug 10 11:42 checksums.ibd
30. [root@myhost02 full]# ll 2014-08-10_11-42-24/sakila/
31. total 22864
32. -rw-r----- 1 root root   114688 Aug 10 11:42 actor.ibd
33. -rw-r----- 1 root root   163840 Aug 10 11:42 address.ibd
34. -rw-r----- 1 root root    98304 Aug 10 11:42 category.ibd
35. -rw-r----- 1 root root   114688 Aug 10 11:42 city.ibd
36. -rw-r----- 1 root root    98304 Aug 10 11:42 country.ibd
37. -rw-r----- 1 root root   196608 Aug 10 11:42 customer.ibd
38. -rw-r----- 1 root root   344064 Aug 10 11:42 film_actor.ibd
39. -rw-r----- 1 root root   147456 Aug 10 11:42 film_category.ibd
40. -rw-r----- 1 root root   344064 Aug 10 11:42 film.ibd
41. -rw-r----- 1 root root   442368 Aug 10 11:42 inventory.ibd
42. -rw-r----- 1 root root    98304 Aug 10 11:42 language.ibd
43. -rw-r----- 1 root root 10485760 Aug 10 11:42 payment.ibd
44. -rw-r----- 1 root root 10485760 Aug 10 11:42 rental.ibd
45. -rw-r----- 1 root root   147456 Aug 10 11:42 staff.ibd
46. -rw-r----- 1 root root   131072 Aug 10 11:42 store.ibd
47. [root@myhost02 full]# ll 2014-08-10_11-42-24/test/
48. total 251976
49. -rw-r----- 1 root root 255852544 Aug 10 11:42 sbtest1.ibd
50. -rw-r----- 1 root root    655360 Aug 10 11:42 t1.ibd
51. -rw-r----- 1 root root    655360 Aug 10 11:42 t2.ibd
52. -rw-r----- 1 root root    655360 Aug 10 11:42 t3.ibd
53. -rw-r----- 1 root root       946 Aug 10 11:43 t4.frm
54. -rw-r----- 1 root root     98304 Aug 10 11:42 t4.ibd
55. -rw-r----- 1 root root       457 Aug 10 11:43 t5.frm
56. -rw-r----- 1 root root     98304 Aug 10 11:42 t5.ibd

二、准备备份

使用--export选项，以及应用日志；在这个过程中会从数据目录移除那么没有备份的丢失表信息(因基于innodb的表，数据字典是保存在表空间)，为每个表生成.exp文件；

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

三、还原备份

1、在导入服务器创建同样结构的新表

OTHERSERVER|mysql> CREATE TABLE mytable (...) ENGINE=InnoDB;

2、Discard表空间

OTHERSERVER|mysql> ALTER TABLE mydatabase.mytable DISCARD TABLESPACE;

3、拷贝*.idb和*.exp文件到数据库目录(如是导入到mysql5.6拷贝*.cfg，而不是*.exp)，然后导入表空间

OTHERSERVER|mysql> ALTER TABLE mydatabase.mytable IMPORT TABLESPACE;

如果没有开启innodb_file_per_table，丢弃表空间时，将报如下错误：

InnoDB: is in the system tablespace 0 which cannot be discarded

注意：

在5.6版本之前，不能拷贝表文件到其他服务器进行还原，只能导入到Percona Server的XtraDB和Mysql 5.6；备份文件不要求是XtraDB或Mysql 5.6；

验证测试：将5.5导出的表迁移到其他服务器(Percona XtraDB或5.6)

1、在一台mysql 5.5.36服务器创建test.t1表的备份：

innobackupex --tables-file=/tmp/tables.txt --socket=/tmp/mysql3307.sock --defaults-file=/usr/local/mysql-5.5.36-linux2.6-x86_64/my.cnf  /data/backup/

2、准备备份：

innobackupex --apply-log --export /data/backup/2014-05-09_16-43-21

3、在新服务器创建同样结构的表

CREATE TABLE `t1` (

  `id` int(11) DEFAULT NULL,

  `name` char(1) DEFAULT NULL

) ENGINE=InnoDB

4、discard表空间

 alter table t1 discard tablespace;

5、从远程拷贝t1.ibd和t1.cfg文件

scp t1.ibd t1.cfg root@10.0.60.38:/data/mysql56/test

然后在目标服务器的数据目录修改权限：

chown mysql.mysql test/*

6、导入表空间

alter table t1 import tablespace;

以下为5.5.36-log MySQL Community Server (GPL)的截图：导入表空间没有报错，但是没有数据；

以下为5.6.17 MySQL Community Server (GPL)版本的截图：导入表空间，查看数据完整；

.cfg是包含Innodb字典的特殊格式转储文件，不同于XtraDB使用的.exp文件；

使用--export时，使用--use-memory指定足够的buffer池，默认是100MB，以加快export的速度；

总结：

1、使用innobackupex进行个别数据库和表进行备份，有三个选项(--include，--tables-file，--databases)，最好使用--tables-file选项，不要使用--databases选项；

2、对个别表的备份和恢复，可以利用5.6的新特性，在线实现个别表的恢复；

来自为知笔记(Wiz)

转载于:https://my.oschina.net/anthonyyau/blog/262793