[Mysql] mysql意外drop表之后，使用innobackupex恢复

1、使用innodbackupex备份测试删除表：

innobackupex备份某以一张表

./innobackupex --defaults-file=/etc/my.cnf --databases="aaaa.test_order" --user=root --password=12345678 --port=3306 /tmp

如果要备份多个表，使用以下命令：

./innobackupex --defaults-file=/etc/my.cnf --databases="aaaa.test_order aaaa.test1" --user=root --password=12345678 --port=3306 /tmp

使用innobackupex备份出来的的数据在aaaa文件下面会存在table_name.frm和table_name.ibd两个文件。

2、如果知道表结构重新建表即可，如果表结构也无法获得，可通过该链接，对表结构进行恢复

3、恢复完表结构之后，开始恢复数据，丢弃表空间：

#为防止新的数据写入，对表加锁：
root@db 16:20:  [aaaa]> lock tables tb1 write;

root@db 16:20:  [aaaa]> alter table test_order discard tablespace; 
Query OK, 0 rows affected (0.54 sec)

将对应的ibd文件拷入对应的数据目录,修改数据文件权限

shell > cp test_order.ibd /data/mysql/data/aaaa/
shell > chown mysql.mysql test_order.ibd

载入表空间：

root@db 16:24:  [aaaa]> alter table test_order import tablespace;
Query OK, 0 rows affected, 1 warning (1 min 53.51 sec)

查看有无报错：

show warnings;

4、对备份之后的数据进行恢复，查看备份开始的时间点

cat xtrabackup_binlog_info

mysql-binlog.000489	748512183

然后查找drop的pos点：

mysqlbinlog  -v --base64-output=DECODE-ROWS /data/mysql/log/mysql-binlog.000489 | grep -C 10 -i  "DROP"

找到删除的pos点，如果不在该log文件下，需要根据（show master status)的位置向前依次筛选,找到drop表的记录，内容如下。

server id 1  end_log_pos 748512209 CRC32 0x3fa6b448   Query   thread_id=27    
DROP TABLE `test_order` /* generated by server */
/*!*/;

找到表删除的pos点（748512209），利用binlog2sql生成中间发生sql语句，（binlog2sql方法详解此处）

python binlog2sql.py  -uroot -p12345678 -daaaa -ttest_order --start-position=748512183 --stop-position=748512209 --start-file='mysql-binlog.000489'  > /tmp/re_aaaa_test_order.sql

生成的sql内容如下:

root@dax-mysql-master binlog2sql]# cat /tmp/re_aaaa_test_order.sql
INSERT INTO `aaaa`.`test_order`(`id`) VALUES (12); #start 3037 end 3222 time 2018-11-06 18:54:21
INSERT INTO `aaaa`.`test_order`(`id`) VALUES (11); #start 3037 end 3222 time 2018-11-06 18:54:21
INSERT INTO `aaaa`.`test_order`(`id`) VALUES (10); #start 3037 end 3222 time 2018-11-06 18:54:21
INSERT INTO `aaaa`.`test_order`(`id`) VALUES (9); #start 3037 end 3222 time 2018-11-06 18:54:21
INSERT INTO `aaaa`.`test_order`(`id`) VALUES (8); #start 3037 end 3222 time 2018-11-06 18:54:21
INSERT INTO `aaaa`.`test_order`(`id`) VALUES (7); #start 3037 end 3222 time 2018-11-06 18:54:21
INSERT INTO `aaaa`.`test_order`(`id`) VALUES (6); #start 3037 end 3222 time 2018-11-06 18:54:21
INSERT INTO `aaaa`.`test_order`(`id`) VALUES (5); #start 3037 end 3222 time 2018-11-06 18:54:21
INSERT INTO `aaaa`.`test_order`(`id`) VALUES (4); #start 3037 end 3222 time 2018-11-06 18:54:21
INSERT INTO `aaaa`.`test_order`(`id`) VALUES (2); #start 2804 end 2949 time 2018-11-06 18:45:45
DELETE FROM `aaaa`.`test_order` WHERE `id`=12 LIMIT 1; #start 2571 end 2716 time 2018-11-06 18:45:39
DELETE FROM `aaaa`.`test_order` WHERE `id`=11 LIMIT 1; #start 2338 end 2483 time 2018-11-06 18:45:33

生成的sql顺序是倒序的，需要重新调整（github上关于binlog2sql的用法未有关于顺序的说明）：
sed -i ‘1!G;h;$!d’ /tmp/re_aaaa_test_order.sql

[root@dax-mysql-master binlog2sql]#  sed -i '1!G;h;$!d' /tmp/re_aaaa_test_order.sql
[root@dax-mysql-master binlog2sql]# cat /tmp/re_aaaa_test_order.sql
DELETE FROM `aaaa`.`test_order` WHERE `id`=11 LIMIT 1; #start 2338 end 2483 time 2018-11-06 18:45:33
DELETE FROM `aaaa`.`test_order` WHERE `id`=12 LIMIT 1; #start 2571 end 2716 time 2018-11-06 18:45:39
INSERT INTO `aaaa`.`test_order`(`id`) VALUES (2); #start 2804 end 2949 time 2018-11-06 18:45:45
INSERT INTO `aaaa`.`test_order`(`id`) VALUES (4); #start 3037 end 3222 time 2018-11-06 18:54:21
INSERT INTO `aaaa`.`test_order`(`id`) VALUES (5); #start 3037 end 3222 time 2018-11-06 18:54:21
INSERT INTO `aaaa`.`test_order`(`id`) VALUES (6); #start 3037 end 3222 time 2018-11-06 18:54:21
INSERT INTO `aaaa`.`test_order`(`id`) VALUES (7); #start 3037 end 3222 time 2018-11-06 18:54:21
INSERT INTO `aaaa`.`test_order`(`id`) VALUES (8); #start 3037 end 3222 time 2018-11-06 18:54:21
INSERT INTO `aaaa`.`test_order`(`id`) VALUES (9); #start 3037 end 3222 time 2018-11-06 18:54:21
INSERT INTO `aaaa`.`test_order`(`id`) VALUES (10); #start 3037 end 3222 time 2018-11-06 18:54:21
INSERT INTO `aaaa`.`test_order`(`id`) VALUES (11); #start 3037 end 3222 time 2018-11-06 18:54:21
INSERT INTO `aaaa`.`test_order`(`id`) VALUES (12); #start 3037 end 3222 time 2018-11-06 18:54:21

5、把这sql文件进入导入即可进行备份后的数据恢复

[root@dax-mysql-master binlog2sql]# mysql -u root -p12345678 aaaa < /tmp/re_aaaa_test_order.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1205 (HY000) at line 1: Lock wait timeout exceeded; try restarting transaction

需要先解锁表：

unlock tables;

解锁完成在重新导入：

[root@dax-mysql-master binlog2sql]# mysql -u root -p12345678 aaaa < /tmp/re_aaaa_test_order.sql

导入成功。