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

前提:备份的表必须在单独的表空间,系统变量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)的截图:导入表空间没有报错,但是没有数据
10152939_crg7.png

以下为5.6.17 MySQL Community Server (GPL)版本的截图:导入表空间,查看数据完整;
10152939_2OvU.png

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

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

总结:
1、使用innobackupex进行个别数据库和表进行备份,有三个选项(--include,--tables-file,--databases),最好使用--tables-file选项,不要使用--databases选项;
2、对个别表的备份和恢复,可以利用5.6的新特性,在线实现个别表的恢复;






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

  • 1
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值