mysqlbinlog制定区间恢复数据
一个有意思的场景：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ZLECUBE |
| log |
| mysql |
| performance_schema |
+--------------------+
5 rows in set (0.00 sec)
mysql>
mysql>
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> use test;
Database changed
mysql>
mysql>
mysql> create table aa(id int);
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)
mysql> \q
Bye
root@debian:/var/lib/mysql/log/binlog# ls -l
total 56
-rw-rw---- 1 mysql mysql 150 Feb 3 19:25 mysql-bin.000017
-rw-rw---- 1 mysql mysql 150 Feb 4 19:25 mysql-bin.000018
-rw-rw---- 1 mysql mysql 150 Feb 5 19:25 mysql-bin.000019
-rw-rw---- 1 mysql mysql 150 Feb 6 19:25 mysql-bin.000020
-rw-rw---- 1 mysql mysql 150 Feb 7 15:04 mysql-bin.000021
-rw-rw---- 1 mysql mysql 314 Feb 7 19:25 mysql-bin.000022
-rw-rw---- 1 mysql mysql 150 Feb 8 19:25 mysql-bin.000023
-rw-rw---- 1 mysql mysql 150 Feb 9 19:25 mysql-bin.000024
-rw-rw---- 1 mysql mysql 150 Feb 10 19:25 mysql-bin.000025
-rw-rw---- 1 mysql mysql 150 Feb 11 19:25 mysql-bin.000026
-rw-rw---- 1 mysql mysql 150 Feb 12 19:25 mysql-bin.000027
-rw-rw---- 1 mysql mysql 319 Feb 13 15:08 mysql-bin.000028
-rw-rw---- 1 mysql mysql 107 Feb 13 15:08 mysql-bin.000029
-rw-rw---- 1 mysql mysql 559 Feb 13 15:08 mysql-bin.index
root@debian:/var/lib/mysql/log/binlog# mysqlbinlog mysql-bin.000028
找到如下的信息：
# at 107
#170213 15:08:21 server id 1 end_log_pos 190 Query thread_id=1435 exec_time=0 error_code=0
SET TIMESTAMP=1486969701/*!*/;
SET @@session.pseudo_thread_id=1435/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
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=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database test
/*!*/;
# at 190
#170213 15:08:39 server id 1 end_log_pos 276 Query thread_id=1435 exec_time=1 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1486969719/*!*/;
create table aa(id int)
/*!*/;
# at 276
#170213 15:08:56 server id 1 end_log_pos 319 Rotate to mysql-bin.000029 pos: 4
DELIMITER ;
# End of log file
删除刚刚的test库
mysql> drop database test;
Query OK, 1 row affected (0.01 sec)
mysql> \q
Bye
现在进行恢复：
root@debian:/var/lib/mysql/log/binlog# mysqlbinlog --start-position=190 --stop-position=319 mysql-bin.000028 |mysql -uroot -pxxx
root@debian:/var/lib/mysql/log/binlog#
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| aa |
+----------------+
1 row in set (0.00 sec)
mysql>
已恢复。
可以重复恢复。
mysql> select count(*) from aa;
+----------+
| count(*) |
+----------+
| 4 |
+----------+
root@debian:/var/lib/mysql/log/binlog# mysqlbinlog --start-position=793 --stop-position=1093 mysql-bin.000029 |mysql -uroot -pxxx
root@debian:/var/lib/mysql/log/binlog#
只恢复从日志位置793到1093的信息，这里面有有两个插入记录。
mysql> select count(*) from aa;
+----------+
| count(*) |
+----------+
| 6 |
+----------+
1 row in set (0.00 sec)
此外，还可以--start-datetime --stop-datetime来指定恢复时间。