mysql 恢复指定区间的数据 mysql恢复到某个时间点

对mysql做基于时间点的恢复常见的方法是还原最近一次的全备份，然后从那个时间点开始重放二进制日志（前滚恢复）。只要有二进制日志，就可以恢复到任何希望的时间点。

主要的缺点就是二进制日志重放可能会是一个很慢的过程。

一个典型的场景是对有害的语句的结果做回滚操作，例如DROP TABLE。

做一个简化的例子。

1.查看当前mysql的状态，确认binlog打开。

mysql> show global variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.03 sec)

2.对数据库做一些变更

mysql> create table t3 (c1 int);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t3 values(1);
Query OK, 1 row affected (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

3.时间点A做一次全备份

[root@qht131 backup]# mysqldump -uroot -p --databases l5m >/u01/backup/l5m.sql

4.时间点B创建T4表

mysql> use l5m
Database changed
mysql> create table t4 (c1 int);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t4 values(155);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000002 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

5.时间点C删除t3和t4，并建立了t5表

mysql> drop table t3;
Query OK, 0 rows affected (0.01 sec)

mysql> drop table t4;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t5 (c1 int);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t5 values(165);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

6.时间点D发现误删除了t4表，这时开始做恢复操作，要求是恢复误操作的drop操作，t3,t4,t5表都需要恢复。

将数据库禁止正常连接阻止更多的修改或者在另外一台备份数据库上做恢复，先恢复到最近一次全备的时间点A：

[root@qht131 backup]# cat /etc/my.cnf
。。。
[mysqld]
skip-networking = 1
。。。

[root@qht131 ~]# service mysql restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL..                                           [  OK  ]

[root@qht131 ~]# mysql -uroot -p < /u01/backup/l5m.sql

恢复后t3已恢复出来，不过全备份没有t4，所以需要用基于时间点的恢复

mysql> show tables;
+---------------+
| Tables_in_l5m |
+---------------+
| t1            |
| t2            |
| t3            |
| t5            |
| test_emp      |
| tt            |
+---------------+
6 rows in set (0.16 sec)

6.查找删t4的时间点

mysqlbinlog /u01/log/mysql/mysql_bin.000002  | grep -i 'DROP TABLE'

通过查看binglog日志，发现drop的操作在mysql_bin.00002上，并且看到时间是180418 17：32：01

#180418 17:32:01 server id 10000  end_log_pos 332 CRC32 0x01a3f4c2      Query   thread_id=5     exec_time=0 error_code=0
use `l5m`/*!*/;
SET TIMESTAMP=1524043921/*!*/;
SET @@session.pseudo_thread_id=5/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549120/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
DROP TABLE `t4` /* generated by server */
/*!*/;

现在数据库恢复到删除t4之前的时间点。

虽然drop操作在mysql_bin.00002上，不过我们需要从mysql_bin.00001上面去恢复全备以后的操作

[root@qht131 mysql]# mysqlbinlog --start-datetime='2018-04-18 17:27:29' --stop-datetime='2018-04-18 17:32:01' mysql_bin.000001 > recovery.sql这

开始恢复的时间点应该是备份后的最近的时间点，结束点是删除表t4之前最近时间点

[root@qht131 mysql]# mysql -uroot -p < recovery.sql


mysql> use l5m
Database changed
mysql> show tables;
+---------------+
| Tables_in_l5m |
+---------------+
| t1            |
| t2            |
| t3            |
| t4            |
| t5            |
| test_emp      |
| tt            |
+---------------+
7 rows in set (0.00 sec)

mysql> select * from t4;
+------+
| c1   |
+------+
|  155 |
+------+
1 row in set (0.10 sec)

mysql> select * from t5;
+------+
| c1   |
+------+
|  165 |
+------+
1 row in set (0.02 sec)

至此drop的操作已恢复，最后不能忘记修改/etc/my.cnf开始网络连接并重启数据库。

总结：基本时间点恢复过程可能比较长，原因在于需要从一个个恢复所有从备份之后一直到drop之前的所有binlog，我的测试只有一个binlog需要恢复，所以看起来没那么复杂。所以全备时间间隔越短需要恢复的binglog也就会越少。

还有一种基于position的恢复，在恢复完全备以后可以跳过有害的语句，而不是基于时间点。