To restore a specified table using Mysqlbinlog

One, login database refresh Binlog
1.1) View the current Binlog
Mysql> Show master status;
+---------------------+----------+--------------+------------------+-------------------+
| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |
+---------------------+----------+--------------+------------------+-------------------+
|  test-150-bin.000003 |              2895377 |                  |                   | |
+---------------------+----------+--------------+------------------+-------------------+
1 row in Set (0.00 sec)

1.2) Refresh Binlog
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

1.3) Confirm the refresh Binlog success
Mysql>show master status;
+---------------------+----------+--------------+------------------+-------------------+
| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |
+---------------------+----------+--------------+------------------+-------------------+
|      test-150-bin.000004 |              120 |                  |                   | |
+---------------------+----------+--------------+------------------+-------------------+
1 row in Set (0.00 sec)
Using the Show Master Status command to view the current Binlog has changed from test-150-bin.000003 to test-150-bin.000004,
Prove that Binlog has been refreshed successfully.

Second, query the binary log location
Mysql> Show variables like ' log_bin% ';
+---------------------------------+------------------------------------------+
| variable_name | Value |
+---------------------------------+------------------------------------------+
| Log_bin | On |
| Log_bin_basename | /db/mysql5.6/data/test-150-bin |
| Log_bin_index | /db/mysql5.6/data/test-150-bin.index |
| log_bin_trust_function_creators | On |
| log_bin_use_v1_row_events | OFF |
+---------------------------------+------------------------------------------+

third, gets the time that the table was deleted from the binary log
$ mysqlbinlog test-150-bin.000003 | grep-i DROP   -a3-b4
commit/*!*/;
# at 122869
#140126 17:03:35 server ID 150  end_log_pos 122989 CRC32 0x8707c4c4     query   thread_id=12519 exec_time=0     error_ Code=0
SET timestamp=1390727015 /*!*/;
DROP TABLE ' Test '/* generated by Server */
/*!*/;
# at 122989
#140126 17:04:06 server ID 150  end_log_pos 123039 CRC32 0x43476aad     Rotate to test-150-bin.000004  pos:4

The first two rows of the drop statement the execution time of the drop statement is in 17:03:35
mysql> SELECT from_unixtime ('1390727015');
+-----------------------------+
| From_unixtime (' 1390727015 ') |
+-----------------------------+
| 2014-01-26 17:03:35.000000 |
+-----------------------------+
1 row in Set

Iv. Obtaining change data for a specified database from the Binlog
Recovering data from the IVR database in binary log test-150-bin.000003 with Mysqlbinlog
This assumes that from the last MySQL backup, only one binlog is generated, that is, when you log in to the database, you query to Binlog test-150-bin.000003
mysqlbinlog-d IVR--stop-datetime= ' 2014-01-26 17:03:35 ' test-150-bin.000003 >recover_ivr.sql

If you refresh Binlog from the last backup, multiple Binlog are generated during the discovery of the table being deleted. In the order in which Binlog is generated, the
The order of recovery should be in accordance with the sequence number of binglog, from small to large recovery.
If from the last backup, to the discovery table was deleted, there are two binlog files, respectively, test-150-bin.000002,test-150-bin.000003,
According to the binlog sequence number from small to large arrangement, the order of recovery should be:

mysqlbinlog-d IVR test-150-bin.000002 > Recover_ivr.sql

mysqlbinlog-d IVR--stop-datetime= ' 2014-01-26 17:03:35 ' test-150-bin.000003 >> recover_ Ivr.sql

Since the recovered file Recover_ivr.sql contains all the tables of the entire IVR database, we simply restore the specified table Testtuo and filter the recovered SQL .

[Email protected] data]$More Recover_ivr.sql |  grep-i-E ' insert|update|delete ' -a2-b2 | grep testtuo
/*!50530 SET @ @SESSION. pseudo_slave_mode=1*/;
/*!40019 SET @ @session. max_insert_delayed_threads=0*/;
/*!50003 SET @[email protected] @COMPLETION_TYPE, completion_type=0*/;
DELIMITER/*!*/;
--
# at 122721
#140126 17:03:28 server ID end_log_pos 122721 CRC32 0xe0f851bb Intvar
SET insert_id=4/*!*/;
#140126 17:03:28 server ID end_log_pos 122838 CRC32 0x9efcc7b2 Query thread_id=12578 exec_time=0 error_code =0
Use ' IVR '/*!*/;
SET timestamp=1390727008/*!*/;
INSERT into ' Testtuo ' (' name ') VALUES (' d ')
/*!*/;
# at 122838

Save the filtered results as SQL scripts and restore them to the database.

Before recovering, to avoid generating unused binary logs, you can turn off logging of the binary log
SET SESSION sql_log_bin=0;

Restore complete, enable logging of binary logs
SET SESSION sql_log_bin=1;

To restore a specified table using Mysqlbinlog