手机版
你好,游客 登录 注册
背景:
阅读新闻

XtraBackup备份与恢复实践

[日期:2016-08-22] 来源:Linux社区  作者:aiapple [字体: ]
说明

 
xtrabackup  --percona
特点:
  • 开源,在线备份innodb表
  • 支持限速备份,避免对业务造成影响
  • 支持流备
  • 支持增量备份
  • 支持备份文件压缩与加密
  • 支持并行备份与恢复,速度快
地址:https://www.percona.com/downloads/XtraBackup
 
xtrabackup备份原理
  • 基于innodb的crash-recovery功能
  • 备份期间允许用户读写,写请求产生redo日志
  • 从磁盘上拷贝数据文件
  • 从innodb redo log  file实时拷贝走备份期间产生的所有redo日志
  • 恢复的时候 数据文件+redo日志 = 一致性数据
 实用脚本innobackupex
  • 开源Perl脚本,封装调用xtrabackup及一系列相关工具与OS操作,最终完成备份过程
  • 支持备份Innodb和其他引擎的表
  • 备份一致性保证
 
安装问题

 
问题一:innobackupex发现mysql模块没有安装
140312 13:30:40  innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).
innobackupex: Error: Failed to connect to MySQL server as DBD::mysql module is not installed at /usr/local/mysql/bin/innobackupex line 2956.
 
因为环境是使用二进制文件安装的mysql,在Ubuntu下没有安装mysql-server;
解决:安装mysql-server
linuxidc@ubuntu:~$ sudo apt-get install mysql-server
 
 
innobackupex使用

  1. 全量备份
  2. 增量备份
  3. 流方式备份
  4. 并行备份
  5. 限流备份
  6. 压缩备份
  7. 常用参数介绍
1)全量备份:
linuxidc@ubuntu:~$ innobackupex-1.5.1 --user=root --password=000000 --defaults-file=/tmp/mysqldata/my.cnf dbbackup/
 
innobackupex-1.5.1: Backup created in directory '/home/linuxidc/dbbackup/2016-08-20_06-16-26'
innobackupex-1.5.1: MySQL binlog position: filename 'mysql-bin.000001', position 1643
160820 06:16:30  innobackupex-1.5.1: Connection to database server closed
160820 06:16:30  innobackupex-1.5.1: completed OK!
  #备份成功;
  linuxidc@ubuntu:~$ ls dbbackup/
  2016-08-20_06-16-26 
 
2)增量备份,需指定上一次备份目录
注意:innobackupex 增量备份仅针对InnoDB这类支持事务的引擎,对于MyISAM等引擎,则仍然是全备。
增量备份:
 
linuxidc@ubuntu:~$ innobackupex-1.5.1 --user=root --password=000000 --defaults-file=/tmp/mysqldata/my.cnf --incremental --incremental-dir dbbackup/2016-08-20_06-16-26/ dbbackup/
 
innobackupex-1.5.1: Backup created in directory '/home/linuxidc/dbbackup/2016-08-20_06-24-08'
innobackupex-1.5.1: MySQL binlog position: filename 'mysql-bin.000001', position 1749
160820 06:24:13  innobackupex-1.5.1: Connection to database server closed
160820 06:24:13  innobackupex-1.5.1: completed OK!
 
#备份成功
linuxidc@ubuntu:~$ ls dbbackup/
2016-08-20_06-16-26  2016-08-20_06-24-08 
3)流式备份()
 
linuxidc@ubuntu:~$ innobackupex-1.5.1 --user=root --password=000000 --defaults-file=/tmp/mysqldata/my.cnf --stream=xbstream dbbackup/ > dbbackup/stream.bak
 
innobackupex-1.5.1: Backup created in directory '/home/linuxidc/dbbackup'
innobackupex-1.5.1: MySQL binlog position: filename 'mysql-bin.000001', position 1749
160820 06:28:06  innobackupex-1.5.1: Connection to database server closed
160820 06:28:06  innobackupex-1.5.1: completed OK!
 
#备份成功
linuxidc@ubuntu:~$ ls dbbackup/
2016-08-20_06-16-26  2016-08-20_06-24-08  stream.bak 
4)并行备份--使用4个线程
 
linuxidc@ubuntu:~$ innobackupex-1.5.1 --user=root --password=000000 --defaults-file=/tmp/mysqldata/my.cnf --parallel=4 dbbackup/
 
innobackupex-1.5.1: Backup created in directory '/home/linuxidc/dbbackup/2016-08-20_06-36-34'
innobackupex-1.5.1: MySQL binlog position: filename 'mysql-bin.000001', position 1749
160820 06:36:38  innobackupex-1.5.1: Connection to database server closed
160820 06:36:38  innobackupex-1.5.1: completed OK!
 
#备份成功
linuxidc@ubuntu:~$ ls dbbackup/
2016-08-20_06-16-26  2016-08-20_06-24-08  2016-08-20_06-36-34  stream.bak 
5)限流备份--读写速度限制10M
 
linuxidc@ubuntu:~$ innobackupex-1.5.1 --user=root --password=000000 --defaults-file=/tmp/mysqldata/my.cnf --throttle=10 dbbackup/
 
innobackupex-1.5.1: Backup created in directory '/home/linuxidc/dbbackup/2016-08-20_06-38-26'
innobackupex-1.5.1: MySQL binlog position: filename 'mysql-bin.000001', position 1749
160820 06:38:31  innobackupex-1.5.1: Connection to database server closed
160820 06:38:31  innobackupex-1.5.1: completed OK!
 
#备份成功
linuxidc@ubuntu:~$ ls dbbackup/
2016-08-20_06-16-26  2016-08-20_06-36-34  stream.bak
2016-08-20_06-24-08  2016-08-20_06-38-26 
6)压缩备份--可以指定压缩备份线程
 
linuxidc@ubuntu:~$ innobackupex-1.5.1 --user=root --password=000000 --defaults-file=/tmp/mysqldata/my.cnf --compress --compress-thread 4 dbbackup/
 
innobackupex-1.5.1: Backup created in directory '/home/linuxidc/dbbackup/2016-08-20_06-40-26'
innobackupex-1.5.1: MySQL binlog position: filename 'mysql-bin.000001', position 1749
160820 06:40:30  innobackupex-1.5.1: Connection to database server closed
160820 06:40:30  innobackupex-1.5.1: completed OK!
 
#备份成功
linuxidc@ubuntu:~$ ls dbbackup/
2016-08-20_06-16-26  2016-08-20_06-36-34  2016-08-20_06-40-26
2016-08-20_06-24-08  2016-08-20_06-38-26  stream.bak 
7)常用参数
innobackupex --help | less
 
 
恢复

 
1.全量备份与恢复:
1)全量备份
 
mysql> show tables;
+--------------+
| Tables_in_tt |
+--------------+
| course       |
| t1           |
+--------------+
3 rows in set (0.00 sec)
 
linuxidc@ubuntu:~$ innobackupex --user=root --password=000000 --defaults-file=/tmp/mysqldata/my.cnf dbbackup/
 
#备份成功;
linuxidc@ubuntu:~$ ls dbbackup/
2016-08-20_06-16-26
 
2)删除表
 
mysql> drop table t1;
Query OK, 0 rows affected (0.02 sec)
 
mysql> show tables;
+--------------+
| Tables_in_tt |
+--------------+
| course       |
+--------------+
1 row in set (0.00 sec)
 
 
3)通过全量备份恢复
#只需通过apply-log指定备份文件
linuxidc@ubuntu:~$ innobackupex --apply-log dbbackup/2016-08-20_06-16-26
 
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1638934
160820 06:46:20  innobackupex: completed OK!
 
#回滚redo log文件;
linuxidc@ubuntu:~/dbbackup/2016-08-20_06-16-26$ ls
backup-my.cnf  mysql               xtrabackup_binlog_info
ibdata1        performance_schema  xtrabackup_checkpoints
ib_logfile0    tt                  xtrabackup_logfile
ib_logfile1    xtrabackup_binary

 

4)复制恢复文件到数据目录
#注意:数据目录要求是空,最好先备份,再清空;
linuxidc@ubuntu:~/dbbackup$ innobackupex  --defaults-file=/tmp/mysqldata/my.cnf --user=root --password=000000 --copy-back 2016-08-20_06-16-26/
 
innobackupex-1.5.1: Copying '/home/linuxidc/dbbackup/2016-08-20_06-16-26/ib_logfile1' to '/tmp/mysqldata/node1/ib_logfile1'
innobackupex-1.5.1: Finished copying back files.
160820 07:11:09  innobackupex-1.5.1: completed OK!
 
5)查看
 
mysql> show tables;
+--------------+
| Tables_in_tt |
+--------------+
| course       |
| t1           |
+--------------+
2 rows in set (0.00 sec) 
2.增量备份与恢复
#需指定上次备份目录
1)增加表zengliang;
mysql> show tables;
+--------------+
| Tables_in_tt |
+--------------+
| course       |
| t1           |
+--------------+
2 rows in set (0.00 sec)
 
mysql> create table zengliang(a int ,b int);
Query OK, 0 rows affected (0.06 sec) 
2)增量备份
#注意--incremental-dir后面跟基础备份目录,之后再跟增量备份目录,
#与应用增量备份日志相反
linuxidc@ubuntu:~$ innobackupex --user=root --password=000000 --defaults-file=/tmp/mysqldata/my.cnf --incremental --incremental-dir dbbackup/2016-08-20_06-16-26/ dbbackup/
 
innobackupex-1.5.1: MySQL binlog position: filename 'mysql-bin.000001', position 1749
160820 06:24:13  innobackupex-1.5.1: Connection to database server closed
160820 06:24:13  innobackupex-1.5.1: completed OK!
 
#备份成功
linuxidc@ubuntu:~$ ls dbbackup/
2016-08-20_06-16-26  2016-08-20_06-24-08

 

3)应用日志恢复数据
--apply-log    回滚日志
--redo-only    回滚合并(多个增量的时候,增量也需要用到,直到最后一个增量不用)
     a)恢复完全备份数据
 linuxidc@ubuntu:~/dbbackup$ innobackupex --apply-log --redo-only 2016-08-20_06-16-26
     b)应用增量备份日志
     #注意此时--incremental-dir后面跟的是增量备份的目录,之后再跟基础备份的目录;
     #与增量备份相反
     #注意:由于权限问题,使用innobackupex,应该使用root账户,不然这条不会通过;
   
  linuxidc@ubuntu:~/dbbackup$ innobackupex --apply-log --incremental-dir=(增量备份目录) (基础备份目录)
#不知何原因总是不太容易成功;
 
4)复制恢复文件到数据目录
#注意:数据目录要求是空,最好先备份,再清空;
linuxidc@ubuntu:~/dbbackup$ innobackupex  --defaults-file=/tmp/mysqldata/my.cnf --user=root --password=000000 --copy-back 2016-08-20_06-16-26/
 
innobackupex-1.5.1: Copying '/home/linuxidc/dbbackup/2016-08-20_06-16-26/ib_logfile1' to '/tmp/mysqldata/node1/ib_logfile1'
innobackupex-1.5.1: Finished copying back files.
 
160820 07:11:09  innobackupex-1.5.1: completed OK!

 

3.还原压缩
 
解压--innobackupex --decompress /dbbackup/(备份目录)
应用日志--innobackupex --apply-log /dbbackup/(压缩后目录)
 
 
4)还原流备
 
mkdir stream
xbstream -C stream -x < stream.bak
并行恢复--innobackupex --parallel=4 --apply-log --use-memory=200MB /dbbackup/stream
 
--use-memory:加快恢复速度;
 
binlog恢复

 
在备份恢复之后,使用binlog恢复没有备份的数据;
 
1)查看备份时binlog点;
linuxidc@ubuntu:~/dbbackup/mysql3309$ cat xtrabackup_binlog_info
mysql-bin.000002    1467  
 
2)使用mysqlbinlog分析二进制日志
linuxidc@ubuntu:/tmp/mysqldata/node1$ mysqlbinlog -vv mysql-bin.000002 | less
# at 1958
#160820  9:10:29 server id 1  end_log_pos 2078 CRC32 0x683bcdc6     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1471698629/*!*/;
insert into t6 values(111,222),(333,444),(555,666)
/*!*/;
# at 2078
#160820  9:10:29 server id 1  end_log_pos 2109 CRC32 0xf264071a     Xid = 104
COMMIT/*!*/;
# at 2109
#160820  9:11:51 server id 1  end_log_pos 2184 CRC32 0x3f63ae99     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1471698711/*!*/;
BEGIN
/*!*/;
# at 2184
#160820  9:11:51 server id 1  end_log_pos 2298 CRC32 0x5c3b7667     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1471698711/*!*/;
insert into t6 values(11,22),(33,44),(55,66)
/*!*/;
# at 2298
#160820  9:11:51 server id 1  end_log_pos 2329 CRC32 0x4ea5c481     Xid = 114
COMMIT/*!*/;
可以看到结束节点在2298
 
3)使用mysqlbinlog+管道+mysql sock登陆
linuxidc@ubuntu:/tmp/mysqldata/node1$ mysqlbinlog -vv --start-position=1467   --stop-position=2298 mysql-bin.000002 | mysql -uroot -p --socket=/home/linuxidc/dbbackup/mysql3309/mysql.sock
也可以按照时间恢复:
linuxidc@ubuntu:/tmp/mysqldata/node1$ mysqlbinlog -vv --start-datetime='2016-08-20 9:03:58'   --stop-datetime='2016-08-20 9:11:51' mysql-bin.000002 | cat

更多XtraBackup相关教程见以下内容

MySQL管理之使用XtraBackup进行热备 http://www.linuxidc.com/Linux/2014-04/99671.htm

MySQL开源备份工具Xtrabackup备份部署 http://www.linuxidc.com/Linux/2013-06/85627.htm

MySQL Xtrabackup备份和恢复 http://www.linuxidc.com/Linux/2011-12/50275.htm

用XtraBackup实现MySQL的主从复制快速部署【主不锁表】 http://www.linuxidc.com/Linux/2012-10/71919p2.htm

安装和使用 Percona 推出的 Xtrabackup 备份 MySQL http://www.linuxidc.com/Linux/2011-10/44451.htm

XtraBackup 的详细介绍请点这里
XtraBackup 的下载地址请点这里

本文永久更新链接地址http://www.linuxidc.com/Linux/2016-08/134476.htm

linux
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款