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来指定恢复时间。