mysql跳过 drop语句利用xtrabackup进行恢复

不完全恢复

场景：数据库开启二进制日志，在有全备xtrabackup备份的情况下，某天手欠，删除了数据库中的某个业务表，对此表进行业务恢复的过程

 

（1）有全备

innobackupex --defaults-file=/etc/my.cnf--user=backup --password='bc.123456' --socket=/var/lib/mysql/mysql.sock  /backup/  --no-timestamp

mysql> show tables;

+----------------+

| Tables_in_test |

+----------------+

| t              |

| t1             |

| t2             |

+----------------+

3 rows in set (0.00 sec)

mysql> select * From t;

+------+------+

| id  | name |

+------+------+

|   1 | aaaa |

|   2 | bbb  |

|   3 | ddd  |

+------+------+

3 rows in set (0.00 sec)

 

mysql> select * From t2;

+------+------+

| id  | name |

+------+------+

|   1 | aaaa |

|   2 | cccc |

|   3 | ddd  |

|   4 | eeee |

+------+------+

4 rows in set (0.00 sec)

 

 

（2）模拟数据变动

insert into  t2  values(10,'ffffff');

insert into  t values(10,'ffffff');

mysql> flush logs;

 

mysql> update t2 set name='wwww' whereid=3;

 

（3）突然，手误删除表

mysql> drop table t2;

 

insert into  t values(11,'00000');

insert into  t values(11,'00000');

（4）做不完全恢复数据

在xtrabackup的全备目录下，找到记录全备结束时刻的时间点，

[root@dgt backup]# catxtrabackup_binlog_info

mysql-bin.000001    154

 

查看当前数据库的binlog的位置

mysql> show binary logs;

+------------------+-----------+

| Log_name         | File_size |

+------------------+-----------+

| mysql-bin.000001 |       769 |

| mysql-bin.000002 |      1127 |

+------------------+-----------+

2 rows in set (0.00 sec)

 

mysql> show master status \G;

*************************** 1. row***************************

            File: mysql-bin.000002

        Position: 1127

    Binlog_Do_DB:

 Binlog_Ignore_DB: mysql,information_schema

Executed_Gtid_Set:

1 row in set (0.00 sec)

 

从备份中恢复全库

(1)    停止数据库，原库改名

 servicemysqld stop

mv  /var/lib/mysql   /var/lib/mysql_bak3

 

(2)    全备应用日志

innobackupex --apply-log /backup

(3)拷贝数据到原库

innobackupex --defaults-file=/etc/my.cnf --copy-back --rsync /backup

 

[root@dgt lib]# chownmysql.mysql mysql -R

Servicemysqld restart

 

（4）查看误操作的二进制日志

[root@dgtmysql_bak3]# mysqlbinlog mysql-bin.000002

mysql>show variables like  'binlog_format';

+---------------+-------+

|Variable_name | Value |

+---------------+-------+

|binlog_format | ROW   |

+---------------+-------+

1 rowin set (0.01 sec)

 

（5）恢复误操作到

---此处看的是老的mysql库中的二进制日志

--查看drop时间点

[root@dgt mysql]# mysqlbinlog--base64-output=decode-rows -vv mysql-bin.000002

 

 

---此处看的是老的mysql库中的二进制日志

 

---通过shell获取drop table所在的行

[root@dgt mysql]# mysqlbinlog--base64-output=decode-rows -vv /var/lib/mysql_bak3/mysql-bin.000002 |grep -n DROP

46:DROP TABLE `t2` /* generated by server*/

---获取drop附近的时间点

[root@dgt mysql]# mysqlbinlog--base64-output=decode-rows -vv /var/lib/mysql_bak3/mysql-bin.000002 |sed -n '40,57p'

#170412 18:27:31 server id 3  end_log_pos 490 CRC32 0x9987910e          Anonymous_GTID   last_committed=1         sequence_number=2

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at 490

#170412 18:27:31 server id 3  end_log_pos 605 CRC32 0x4104eb93          Query       thread_id=7     exec_time=0    error_code=0

use `test`/*!*/;

SET TIMESTAMP=1491992851/*!*/;

DROP TABLE `t2` /* generated by server */

/*!*/;

# at 605

#170412 18:28:19 server id 3  end_log_pos 670 CRC32 0xf3ee20d5           Anonymous_GTID   last_committed=2         sequence_number=3

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at 670

#170412 18:28:19 server id 3  end_log_pos 742 CRC32 0x6ec11a51          Query       thread_id=7     exec_time=0    error_code=0

SET TIMESTAMP=1491992899/*!*/;

BEGIN

/*!*/;

# at 742

#170412 18:28:19 server id 3  end_log_pos 789 CRC32 0x1fff2eb6   Table_map: `test`.`t` mapped to number 225

 

---恢复drop语句之前的所有二进制日志

mysqlbinlog --start-position='154' --stop-datetime='2017-04-1218:27:31' /var/lib/mysql_bak3/mysql-bin.000001 /var/lib/mysql_bak3/mysql-bin.000002 |mysql -uroot –p

 

 

 

应用误操作时间点之后的binglog

mysqlbinlog --start-datetime='2017-04-12 18:28:19'/var/lib/mysql_bak3/mysql-bin.000001 /var/lib/mysql_bak3/mysql-bin.000002 |mysql -uroot -p

 

验证

mysql> select * From t2;

+------+--------+

| id  | name   |

+------+--------+

|   1 | aaaa   |

|   2 | cccc   |

|   3 | wwww   |

|   4 | eeee   |

|  10 | ffffff |

|  10 | ffffff |

+------+--------+

6 rows in set (0.00 sec)

 

mysql> select * from t;

+------+-------+

| id  | name  |

+------+-------+

|   1 | aaaa  |

|   2 | bbb   |

|   3 | ddd   |

|  11 | 00000 |

|  11 | 00000 |

+------+-------+

5 rows in set (0.00 sec)