Use mysqlbinlog to extract binary logs

Use mysqlbinlog to extract binary logs

The MySQL binlog log records all changes to the current database since the MySQL database is enabled. Binlog logs are binary files. We can extract binary logs to generate readable SQL statements to reconstruct the current database and restore or incomplete recovery at the desired time point. This article describes how to extract binlog logs and provides related examples.

For information about binlog, see MySQL Binary Log)

 

1. Several methods for extracting mysqlbinlog

 

2. Demonstrate the show binlog events Method

Mysql> show variables like 'version '; + --------------- + ------------ + | Variable_name | Value | + --------------- + ------------ + | version | 5.6.12-log | + --------------- + ------------ + mysql> show binary logs; + ----------------- + ----------- + | Log_name | File_size | + ----------------- + ----------- + | APP01bin. 000001 | 120 | + ----------------- + --------- + a. view only the content of the first binlog file (show binlog events) mysql> use replication; Database changedmysql> select * from tb; + ------ + ------- + | id | val | + ------ + ------- + | 1 | robin | + ------ + ------- + mysql> insert into tb values (2, 'jack '); query OK, 1 row affected (0.02 sec) mysql> flush logs; Query OK, 0 rows affected (0.00 sec) mysql> insert into tb values (3, 'fred '); query OK, 1 row affected (0.00 sec) mysql> show binary logs; + ----------------- + ----------- + | Log_name | File_size | + ----------------- + ----------- + | APP01bin. 000001 | 409 | APP01bin. 000002 | 363 | + ----------------- + ----------- + mysql> show binlog events; + region + ----- + ------------- + ----------- + region + | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | + region + ----- + ------------- + ----------- + region + | APP01bin. 000001 | 4 | Format_desc | 11 | 120 | Server ver: 5.6.12-log, Binlog ver: 4 | APP01bin. 000001 | 120 | Query | 11 | 213 | BEGIN | APP01bin. 000001 | 213 | Query | 11 | 332 | use 'replicase'; insert into tb values (2, 'jack') | APP01bin. 000001 | 332 | Xid | 11 | 363 | COMMIT/* xid = 382 */| APP01bin. 000001 | 363 | Rotate | 11 | 409 | APP01bin. 000002; pos = 4 | + ----------------- + ----- + ------------- + ----------- + ------------- + accept + -- in the preceding result, the SQL statement we executed is displayed in line 3rd, 4th automatic submission of behaviors -- Author: Leshami -- Blog: http://blog.csdn.net/leshamib View the content of the specified binlog file (show binlog events in 'binname. xxxx') mysql> show binlog events in 'app01bin. 000002 '; + region + ----- + ------------- + ----------- + region + | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | + region + ----- + ------------- + ----------- + region + | APP01bin. 000002 | 4 | Format_desc | 11 | 120 | Server ver: 5.6.12-log, Binlog ver: 4 | APP01bin. 000002 | 120 | Query | 11 | 213 | BEGIN | APP01bin. 000002 | 213 | Query | 11 | 332 | use 'replicase'; insert into tb values (3, 'fred ') | APP01bin. 000002 | 332 | Xid | 11 | 363 | COMMIT/* xid = 394 */| + ----------------- + ----- + ------------- + ----------- + logs + c, view the binlog currently being written file (show master status \ G) mysql> show master status \ G ***************************** 1. row ************************** File: APP01bin. 000002 Position: 363 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec) d. Get the binlog content at the specified Position (show binlog events from) mysql> show binlog events from 213; + region + ----- + ------------ + ----------- + region + | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | + region + ----- + ------------ + ----------- + ------------- + region + | APP01bin. 000001 | 213 | Query | 11 | 332 | use 'replicase'; insert into tb values (2, 'jack') | APP01bin. 000001 | 332 | Xid | 11 | 363 | COMMIT/* xid = 382 */| APP01bin. 000001 | 363 | Rotate | 11 | 409 | APP01bin. 000002; pos = 4 | + --------------- + ----- + ------------ + ----------- + ------------- + ------------------------------------------------------ +

3. demonstrate how to extract binlog using mysqlbinlog

A. Extract the specified binlog # mysqlbinlog/opt/data/APP01bin. 000001 # mysqlbinlog/opt/data/APP01bin. 000001 | grep insert /*! 40019 SET @ session. max_insert_delayed_threads = 0 */; insert into tb values (2, 'jack ') b. Extract binlog at the specified position # mysqlbinlog -- start-position = "120" -- stop-position = "332"/opt/data/APP01bin. 000001c. Extract the binlog at the specified position and output it to the compressed file # mysqlbinlog -- start-position = "120" -- stop-position = "332"/opt/data/APP01bin. 000001 | gzip> extra_01. SQL .gzd. Extract the binlog at the specified position and import it to the database # mysqlbinlog -- start-position = "120" -- stop-position = "332"/opt/data/APP01bin. 000001 | mysql-uroot-pe: extract the binlog at the specified start time and output it to the log file # mysqlbinlog -- start-datetime = "20:15:23"/opt/data/APP01bin. 000002 -- result-file = extra02.sqlf. Multiple binlog files at the specified position are extracted. # mysqlbinlog -- start-position = "120" -- stop-position = "332"/opt/data/ APP01bin. 000001/opt/data/APP01bin. 000002 | moreg, extract the specified database binlog and convert the character set to UTF8 # mysqlbinlog -- database = test -- set-charset = utf8/opt/data/APP01bin. 000001/opt/data/APP01bin. 000002> test. sqlh, remote log extraction, specify end time # mysqlbinlog-urobin-p-h192.168.1.116-P3306 -- stop-datetime = "2014-12-15 20:30:23" -- read-from-remote-server mysql-bin.000033 | morei, remote extract uses row format binlog and output to local file # mysqlbinlog-urobin-p-P3606-h192.168.1.177 -- read-from-remote-server-vv inst3606bin. 000005> row. SQL

4. Get help information for mysqlbinlog (only common options are listed)

-D, -- database = name

-F, -- force-read

-H, -- host = name

-L, -- local-load = name

-P, -- password [= name]

-P, -- port = #

-- Protocol = name

-R, -- read-from-remote-server | -- read-from-remote-master = name

-R, -- result-file = name

-S, -- short-form

-S, -- socket = name

-- Start-datetime = name

-- Stop-datetime = name

-J, -- start-position = #

-- Server-id = #

-- Set-charset = name

-D, -- disable-log-bin

-U, -- user = name

-V, -- verbose

-V, -- version