Mysqlbinlog sets interval to recover data
An interesting scene:
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
Find the following information:
# 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.pseudoclinic thread readership idling 1435pm / session.
SET @ @ session.foreign_key_checks=1, @ @ session.sql_auto_is_null=0, @ @ session.unique_checks=1, @ @ session.
SET @ @ session.sqlcards, modewords, 0planks / sessions.
SET @ @ session.auto_increment_increment=1, @ @ session.
/ *!\ C utf8 * /! * /
SET @ @ session.session. Session setting setting clientmakers 33 minutes session. Collationalization connections33 camera sessions session. Collationalization serverals 8 packs.
SET @ @ session. Session. LCC timetables namespace.
SET @ @ session.collationalization databases
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
Delete the test library you just had
Mysql > drop database test
Query OK, 1 row affected (0.01sec)
Mysql >\ Q
Bye
Restore now:
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 >
Restored.
The recovery can be repeated.
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#
Only information from log locations 793 to 1093 is restored, with two insert records in it.
Mysql > select count (*) from aa
+-+
| | count (*) |
+-+
| | 6 |
+-+
1 row in set (0.00 sec)
In addition, you can specify the recovery time by-- start-datetime-- stop-datetime.
