基于Innobackupex的全备恢复

桃扇骨 2022-08-06 04:26 164阅读 0赞

对于MySQL数据库的热备，xtrabackup是大多数DBA朋友们的选择。xtrabackup内嵌了一个innobackupex可用于热备MySQL数据库。本文描述了基于innobackupex这个工具全备下的恢复并给出演示供大家参考。  
    有关Innobackupex的全备可参考：[Innobackupex 全备数据库][Innobackupex]

1、Innobackupex恢复原理  
    After creating a backup, the data is not ready to be restored. There might be uncommitted transactions to be undone or transactions in the logs to be replayed. Doing those pending operations will make the data ﬁles consistent and it is the purpose of the prepare stage. Once this has been done, the data is ready to be used.  
    To prepare a backup with innobackupex you have to use the --apply-log and the path to the backup directory as an argument:

Innobackupex replayed the committed transactions in the log ﬁles (some transactions could have been done while the backup was being done) and rolled back the uncommitted ones. Once this is done, all the information lay in the tablespace (the InnoDB ﬁles), and the log ﬁles are re-created.

在备份期间(copy数据时)事务存在不一致，即copy开始时，有些事务已开始，有些刚刚开始，而copy结束前或结束后才提交或回滚。  
    这些不确定的事务需要在恢复前来确定最终是否最终提交或回滚。在这个阶段的操作称之为prepare阶段。  
    这个prepare阶段依赖于备份时的xtrabackup log(来自innodb logfile)，使用--apply-log参数实现一致性。  
    --apply-log参数会根据xtrabackup log做相应的前滚或回滚，完成后会重建innodb logfile文件。

The --use-memory option The preparing process can be speed up by using more memory in it. It depends on the free or available RAM on your system, it defaults to 100MB. In general, the more memory available to the process,the better. The amount of memory used in the process can be speciﬁed by multiples of bytes:  
    恢复期间，--use-memory选项可以加速prepare过程，如果系统可用内存够大的话，该值缺省被设置为100MB。

Innobackupex恢复示意图  
![20141222131937742][]

2、演示恢复全备

a、当前环境
    robin@localhost[(none)]> show variables like 'version';
    +---------------+------------+
    | Variable_name | Value      |
    +---------------+------------+
    | version       | 5.6.12-log |
    +---------------+------------+
    
    robin@localhost[(none)]> create database fullbakdb;
    Query OK, 1 row affected (0.01 sec)
    
    robin@localhost[(none)]> use fullbakdb
    
    robin@localhost[fullbakdb]> create table tb(id smallint,val varchar(20));
    
    robin@localhost[fullbakdb]> insert into tb values(1,'robin'),(2,'leshami');
    
    robin@localhost[fullbakdb]> select * from tb;
    +------+---------+
    | id   | val     |
    +------+---------+
    |    1 | robin   |
    |    2 | leshami |
    +------+---------+
    
    b、全备数据库
    SHELL> innobackupex --user=robin -password=xxx --port=3606 --socket=/tmp/mysql3606.sock \  
    > --defaults-file=/data/inst3606/data3606/my3606.cnf /data/bak/hotbak  
    
    -- 下面是备份完成后的内容
    SHELL>  pwd
    /data/bak/hotbak
    SHELL>  ll
    drwxr-xr-x 7 root root 4096 2014/12/22 09:04 2014-12-22_09-04-05
    
    --查看备份生成的相关文件
    SHELL>  ll 2014-12-22_09-04-05
    total 77944
    -rw-r--r-- 1 root root      357 2014/12/22 09:04 backup-my.cnf
    drwx------ 2 root root     4096 2014/12/22 09:04 fullbakdb
    -rw-r----- 1 root root 79691776 2014/12/22 09:04 ibdata1
    drwx------ 2 root root     4096 2014/12/22 09:04 mysql
    drwxr-xr-x 2 root root     4096 2014/12/22 09:04 performance_schema
    drwx------ 2 root root     4096 2014/12/22 09:04 recover
    drwx------ 2 root root     4096 2014/12/22 09:04 sakila
    -rw-r--r-- 1 root root       26 2014/12/22 09:04 xtrabackup_binlog_info
    -rw-r----- 1 root root       93 2014/12/22 09:04 xtrabackup_checkpoints
    -rw-r--r-- 1 root root      684 2014/12/22 09:04 xtrabackup_info
    -rw-r----- 1 root root     2560 2014/12/22 09:04 xtrabackup_logfile
    
    c、清空表tb以便测试恢复功能
    robin@localhost[fullbakdb]> truncate table tb;
    Query OK, 0 rows affected (0.01 sec)
    
    robin@localhost[fullbakdb]> select * from tb;
    Empty set (0.00 sec)
    
    d、恢复全备
    --关闭原有实例
    SHELL> mysqldown -P3606
    SHELL> netstat -nltp|grep mysql|grep 3606
    
    --准备全备文件
    SHELL> innobackupex --apply-log --user=robin -password=xxx --port=3606 --socket=/tmp/mysql3606.sock \
    > --defaults-file=/data/inst3606/data3606/my3606.cnf /data/bak/hotbak/2014-12-22_09-04-05
    
    InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
                ......非重要信息忽略，下同......
    xtrabackup: Starting InnoDB instance for recovery.  --开始innodb实例恢复
                ...........
    InnoDB: Starting crash recovery.    --开始crash recovery
                ...........
    141222 09:13:59  innobackupex: Restarting xtrabackup with command: xtrabackup  --defaults-file="/data/inst3606/data3606/my3606.cnf" 
    --defaults-group="mysqld" --prepare --target-dir=/data/bak/hotbak/2014-12-22_09-04-05 --tmpdir=/tmp
    for creating ib_logfile*           --注意这里，使用了--prepare，并且创建innodb的logfile
    
    xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.
    xtrabackup: using the following InnoDB configuration for recovery:
                 ............
    -- Author : Leshami
    -- Blog   : http://blog.csdn.net/leshami    
    InnoDB: Shutdown completed; log sequence number 391275633
    141222 09:14:02  innobackupex: completed OK!        --成功恢复 
    
    --查看恢复后文件的相关信息
    SHELL>  ll 2014-12-22_09-04-05
    total 178404
    -rw-r--r-- 1 root root      357 2014/12/22 09:04 backup-my.cnf
    drwx------ 2 root root     4096 2014/12/22 09:04 fullbakdb
    -rw-r----- 1 root root 79691776 2014/12/22 09:14 ibdata1
    -rw-r--r-- 1 root root 50331648 2014/12/22 09:14 ib_logfile0
    -rw-r--r-- 1 root root 50331648 2014/12/22 09:13 ib_logfile1
    drwx------ 2 root root     4096 2014/12/22 09:04 mysql
    drwxr-xr-x 2 root root     4096 2014/12/22 09:04 performance_schema
    drwx------ 2 root root     4096 2014/12/22 09:04 recover
    drwx------ 2 root root     4096 2014/12/22 09:04 sakila
    -rw-r--r-- 1 root root       26 2014/12/22 09:04 xtrabackup_binlog_info
    -rw-r--r-- 1 root root       24 2014/12/22 09:14 xtrabackup_binlog_pos_innodb
    -rw-r----- 1 root root       93 2014/12/22 09:14 xtrabackup_checkpoints
    -rw-r--r-- 1 root root      684 2014/12/22 09:04 xtrabackup_info
    -rw-r----- 1 root root  2097152 2014/12/22 09:13 xtrabackup_logfile
    
    --观察上面文件夹内容的变化，可以看到09:14为新增或发生变化的文件，主要是生成了系统表空间数据文件及innodb日志文件
    --同时有关Innodb的检查点文件也进行了更新(注，热备只涉及到InnoDB引擎，所有与InnoDB相关的都会在apply-log时发生变化
    
    --将原有文件夹重命名到新位置，并创建原文件夹
    SHELL> mv /data/inst3606/data3606 /data/inst3606/data3606bk
    SHELL> mkdir -p /data/inst3606/data3606
    
    --将已经恢复好的数据文件复制回原始位置
    SHELL> innobackupex --user=robin -password=xxx --port=3606 --defaults-file=/data/inst3606/data3606bk/my3606.cnf \ 
    > --copy-back /data/bak/hotbak/2014-12-22_09-04-05
                        ......非重要信息忽略，下同......
    innobackupex: Starting to copy files in '/data/bak/hotbak/2014-12-22_09-04-05' --启动将备份的文件复制回原路径
    innobackupex: back to original data directory '/data/inst3606/data3606'        --原路径位置 
                         ............复制所有的数据文件，索引文件个，格式文件等............
    innobackupex: Starting to copy InnoDB system tablespace  --复制系统表空间
    innobackupex: in '/data/bak/hotbak/2014-12-22_09-04-05'
    innobackupex: back to original InnoDB data directory '/data/inst3606/data3606'
    innobackupex: Copying '/data/bak/hotbak/2014-12-22_09-04-05/ibdata1' to '/data/inst3606/data3606/ibdata1'
    
    innobackupex: Starting to copy InnoDB undo tablespaces   --复制undo表空间
    innobackupex: in '/data/bak/hotbak/2014-12-22_09-04-05'
    innobackupex: back to '/data/inst3606/data3606'
    
    innobackupex: Starting to copy InnoDB log files          --复制redo表空间
    innobackupex: in '/data/bak/hotbak/2014-12-22_09-04-05'
    innobackupex: back to original InnoDB log directory '/data/inst3606/data3606'
    innobackupex: Copying '/data/bak/hotbak/2014-12-22_09-04-05/ib_logfile1' to '/data/inst3606/data3606/ib_logfile1'
    innobackupex: Copying '/data/bak/hotbak/2014-12-22_09-04-05/ib_logfile0' to '/data/inst3606/data3606/ib_logfile0'
    innobackupex: Finished copying back files.
    
    141222 09:34:47  innobackupex: completed OK!
    
    --权限修改
    SHELL> cp /data/inst3606/data3606bk/my3606.cnf /data/inst3606/data3606/my3606.cnf
    SHELL> chown -R mysql:mysql /data/inst3606/data3606
    
    --启动恢复后的实例
    SHELL>  mysqld_safe --defaults-file=/data/inst3606/data3606bk/my3606.cnf &
    
    --验证恢复情况
    SHELL>  sql -P3606
    robin@localhost[(none)]> select * from fullbakdb.tb;
    +------+---------+
    | id   | val     |
    +------+---------+
    |    1 | robin   |
    |    2 | leshami |
    +------+---------+
    
    robin@localhost[(none)]> drop database fullbakdb;
    Query OK, 1 row affected (0.01 sec)

3、小结  
a、Xtrabackup恢复的目的要是保证事务(数据)的一致性，Xtrabackup log会记录这些事务备份期间的状态  
b、恢复过程分为2个阶段，一个是Prepare阶段，一个是copy back阶段，恢复前关闭原有实例  
c、Prepare阶段会根据从innodb logfile捕获出来的信息(记录在xtrabackup log)来进行相应的前滚或回滚  
d、Prepare阶段会在成功前滚或回滚后创建新的innodb logfile(空)  
e、copy back阶段则是将成功恢复的全部文件复制回原来或指定的数据目录(目录应为空目录)  
f、copy back前需要关闭原有实例，如果恢复到不同的实例则不需要  
g、copy back完成后应做相应的权限修改  
h、启动恢复后的实例并进行相关验证

[![鹏城DBA总群][DBA]][DBA_DBA]

[Innobackupex]: http://blog.csdn.net/leshami/article/details/41043269
[20141222131937742]: /images/20220806/54f0972c438c48ab97b8f378e811bb65.png
[DBA]: http://pub.idqqimg.com/wpa/images/group.png
[DBA_DBA]: /images/20220806/539df9b718de4640ac71f76c96c1099a.png