基于Innobackupex的全备恢复

对于MySQL数据库的热备，xtrabackup是大多数DBA朋友们的选择。xtrabackup内嵌了一个innobackupex可用于热备MySQL数据库。本文描述

对于MySQL数据库的热备，xtrabackup是大多数DBA朋友们的选择。xtrabackup内嵌了一个innobackupex可用于热备MySQL数据库。本文描述了基于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 specied by multiples of bytes:

恢复期间，--use-memory选项可以加速prepare过程，如果系统可用内存够大的话，该值缺省被设置为100MB。

Innobackupex恢复示意图

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