从xtrabackup备份恢复单表

    目前对MySQL比较流行的备份方式有两种，一种上是使用自带的mysqldump，另一种是xtrabackup，对于数据时大的环境，普遍使用了xtrabackup+binlog进行全量或者增量备份，那么如何快速的从xtrabackup备份中恢复单张表呢？从mysql 5.6版本开始，支持可移动表空间（Transportable Tablespace），利用这个功能也可以实现单表的恢复，下面进行从备份中恢复单张innodb表进行演练。

 

1. 针对InnoDB表恢复

2. 开启了参数innodb_file_per_table

3. 安装工具：mysql-utilities，其中mysqlfrm可以读取表结构。

 

进行mysql-utilities安装：

yum install mysql-utilities -y

创建一个测试，往里面插入数据后进行备份：

<test>(root@localhost) [xuanzhi]> show create table tb1\G
*************************** 1. row ***************************
       Table: tb1
Create Table: CREATE TABLE `tb1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

<test>(root@localhost) [xuanzhi]> insert into tb1 (name) values ('aa'),('bb'),('cc'),('dd');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

<test>(root@localhost) [xuanzhi]> select * from tb1;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
|  4 | dd   |
+----+------+
4 rows in set (0.00 sec)

<test>(root@localhost) [xuanzhi]> 

进行xtrabackup备份操作：

[root@localhost data]# innobackupex --defaults-file='/data/service/mysql-5.6.25/my.cnf' --user='root'  --password='123456' --sock='/data/mysql-5.6/mysql.sock'  /data

apply-log：

[root@localhost data]# innobackupex --defaults-file='/usr/local/mysql-5.6.25/my.cnf' --user='root'  --password='123456' --sock='/data/mysql-5.6/mysql.sock'  --apply-log   /data/2017-03-24_09-40-54/

进行完整备份后，我们继续往测试表tb1里插入数据，尽量模拟线上环境：

<test>(root@localhost) [xuanzhi]> insert into tb1 (name) values ('aa2'),('bb2'),('cc2'),('dd2');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

<test>(root@localhost) [xuanzhi]> select * from tb1;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
|  4 | dd   |
|  5 | aa2  |
|  6 | bb2  |
|  7 | cc2  |
|  8 | dd2  |
+----+------+
8 rows in set (0.00 sec)

<test>(root@localhost) [xuanzhi]> 

xtrabackup备份里只有四条数据，备份后的数据，我们一会使用binlog来进行恢复。

 

进行误操操作，把表drop了：

<test>(root@localhost) [xuanzhi]> drop table tb1;
Query OK, 0 rows affected (0.21 sec)

<test>(root@localhost) [xuanzhi]> show tables;
Empty set (0.02 sec)

<test>(root@localhost) [xuanzhi]> 

使用mysqlfrm从备份中读取表结构：

[root@localhost data]# mysqlfrm --diagnostic  /data/2017-03-24_09-40-54/xuanzhi/tb1.frm  
# WARNING: Cannot generate character set or collation names without the --server option.
# CAUTION: The diagnostic mode is a best-effort parse of the .frm file. As such, it may not identify all of the components of the table correctly. This is especially true for damaged files. It will also not read the default values for the columns and the resulting statement may not be syntactically correct.
# Reading .frm file for /data/2017-03-24_09-40-54/xuanzhi/tb1.frm:
# The .frm file is a TABLE.
# CREATE TABLE Statement:

CREATE TABLE `xuanzhi`.`tb1` (
  `id` int(11) NOT NULL AUTO_INCREMENT, 
  `name` char(30) DEFAULT NULL, 
PRIMARY KEY `PRIMARY` (`id`)
) ENGINE=InnoDB;

#...done.
[root@localhost data]# 

登录数据库进行建表：

<test>(root@localhost) [xuanzhi]> CREATE TABLE `xuanzhi`.`tb1` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT, 
    ->   `name` char(30) DEFAULT NULL, 
    -> PRIMARY KEY `PRIMARY` (`id`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.05 sec)

<test>(root@localhost) [xuanzhi]>

 加一个写锁，确保安全:

<test>(root@localhost) [xuanzhi]> lock tables tb1 write;
Query OK, 0 rows affected (0.00 sec)

<test>(root@localhost) [xuanzhi]> 

丢弃表空间：

<test>(root@localhost) [xuanzhi]> alter table tb1 discard tablespace; 
Query OK, 0 rows affected (0.01 sec)

从备份中拷贝ibd文件，并且修改权限:

[root@localhost data]# cp  /data/2017-03-24_09-40-54/xuanzhi/tb1.ibd /data/mysql-5.6/xuanzhi/
[root@localhost data]# chown -R mysql:mysql /data/mysql-5.6/xuanzhi/tb1.ibd 

载入表空间：

<test>(root@localhost) [xuanzhi]> alter table tb1 import tablespace;
Query OK, 0 rows affected, 1 warning (0.04 sec)

<test>(root@localhost) [xuanzhi]> show warnings;
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                     |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './xuanzhi/tb1.cfg', will attempt to import without schema verification |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

<test>(root@localhost) [xuanzhi]> 

有报一个warning，但不影响恢复，详情可以看：https://yq.aliyun.com/articles/59271，我们查一下数据：

<test>(root@localhost) [xuanzhi]> select * from tb1;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
|  4 | dd   |
+----+------+
4 rows in set (0.02 sec)

<test>(root@localhost) [xuanzhi]> 

可以看到备份的数据已经恢复了，但是备份后插入的数据明显没有出现，这个时候我们只能通过binlog进行恢复了，所以binlog的备份也是非常重要的。

我们查看一下备份时的pos点和binlog的文件名：

[root@localhost data]# cd /data/2017-03-24_09-40-54/
[root@localhost 2017-03-24_09-40-54]# cat xtrabackup_binlog_info 
mysql-bin.000002  1014

我们知道了备份后的起始POS点，还需要找出到误操前的一个POS点进行恢复，找到drop table 前的POS点：

[root@localhost 2017-03-24_09-40-54]# mysqlbinlog  -v --base64-output=DECODE-ROWS /data/mysql-5.6/mysql-bin.000002 | grep -C 10 -i  "DROP"
### SET
###   @1=8
###   @2='dd2'
# at 1292
#170324  9:43:00 server id 1313306  end_log_pos 1323 CRC32 0x9f776b03   Xid = 198
COMMIT/*!*/;
# at 1323
#170324  9:46:55 server id 1313306  end_log_pos 1445 CRC32 0x3fa6b448   Query   thread_id=27    exec_time=0     error_code=0
use `xuanzhi`/*!*/;
SET TIMESTAMP=1490320015/*!*/;
DROP TABLE `tb1` /* generated by server */
/*!*/;
# at 1445
#170324  9:51:52 server id 1313306  end_log_pos 1674 CRC32 0xdd5e1448   Query   thread_id=27    exec_time=0     error_code=0
SET TIMESTAMP=1490320312/*!*/;
CREATE TABLE `xuanzhi`.`tb1` (
  `id` int(11) NOT NULL AUTO_INCREMENT, 
  `name` char(30) DEFAULT NULL, 
PRIMARY KEY `PRIMARY` (`id`)
) ENGINE=InnoDB
/*!*/;

可以看到DROP TABLE 前的POS点是1323，那我们可以通过binlog2sql进行标准SQL的生成，binlog2sql的使用的和安装请看之前我写的博客：http://www.cnblogs.com/xuanzhi201111/p/6602489.html，再次为开源数据闪回工具的大神们点赞。

[root@localhost binlog2sql]# python binlog2sql.py  -uroot -p123456 -dxuanzhi -ttb1 --start-position=1014 --stop-position=1323 --start-file='mysql-bin.000002'  > recovery_tb1.sql

[root@localhost binlog2sql]# cat recovery_tb1.sql 
FLUSH ENGINE LOGS;
INSERT INTO `xuanzhi`.`tb1`(`id`, `name`) VALUES (5, 'aa2'); #start 1094 end 1292 time 2017-03-24 09:43:00
INSERT INTO `xuanzhi`.`tb1`(`id`, `name`) VALUES (6, 'bb2'); #start 1094 end 1292 time 2017-03-24 09:43:00
INSERT INTO `xuanzhi`.`tb1`(`id`, `name`) VALUES (7, 'cc2'); #start 1094 end 1292 time 2017-03-24 09:43:00
INSERT INTO `xuanzhi`.`tb1`(`id`, `name`) VALUES (8, 'dd2'); #start 1094 end 1292 time 2017-03-24 09:43:00
[root@pm2 binlog2sql]# 

把这sql文件进入导入即可进行备份后的数据恢复，导入数据前先进行解锁：

<test>(root@localhost) [xuanzhi]> unlock tables;
Query OK, 0 rows affected (0.01 sec)

[root@localhost binlog2sql]# mysql -uroot -p123456  <./recovery_tb1.sql 
Warning: Using a password on the command line interface can be insecure.
[root@localhost binlog2sql]# 

查看数据：

test>(root@localhost) [xuanzhi]> select * from tb1;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
|  4 | dd   |
|  5 | aa2  |
|  6 | bb2  |
|  7 | cc2  |
|  8 | dd2  |
+----+------+
8 rows in set (0.02 sec)

<test>(root@localhost) [xuanzhi]>

可以看到数据成功的恢复了，大家可以多加测试。

 

参考文章：

https://www.percona.com/blog/2017/03/15/restore-single-innodb-table-full-backup-accidentally-dropping/

https://yq.aliyun.com/articles/59271

http://www.cnblogs.com/gomysql/p/6600616.html

 

 

作者：陆炫志

出处：xuanzhi的博客 http://www.cnblogs.com/xuanzhi201111

您的支持是对博主最大的鼓励，感谢您的认真阅读。本文版权归作者所有，欢迎转载，但请保留该声明。