Use mysqlbinlog to extract binary logs and mysqlbinlog binary logs

Use mysqlbinlog to extract binary logs and mysqlbinlog 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
A. You can use show binlog events to obtain the current and specified binlog logs, which is not suitable for extracting a large number of logs.
B. Use the mysqlbinlog command line to extract logs in batches ).

 

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 behavior submission -- Author: Leshami -- Blog: http://blog.csdn.net/leshamib?#specify the content of the binlogfile (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)
-?, -- Help
Displays the help message and exits.

-D, -- database = name
Only list the entries of the database (only applicable to local logs ).

-F, -- force-read
This option is used. If mysqlbinlog reads binary log events that it cannot recognize, it prints a warning, ignores the event, and continues. This option is not available. If mysqlbinlog reads this type of event, it stops.

-H, -- host = name
Obtain the binary log of the MySQL server on the specified host.

-L, -- local-load = name
Pre-processes local temporary files for the load data infile in the specified directory.

-O, -- offset = #
Skip the first N entries.

-P, -- password [= name]
The password used to connect to the server. If you use the short option format (-p), there must be no space between the option and password.
If the-password or-p option in the command line does not have a password value, the system prompts you to enter a password.

-P, -- port = #
The TCP/IP Port number used to connect to the remote server.

-- Protocol = name
The connection protocol used.

-R, -- read-from-remote-server | -- read-from-remote-master = name
Read Binary logs from the MySQL server. If this option is not provided, any connection Parameter options will be ignored, that is, connect to the local device.
These options are-host,-password,-port,-protocol,-socket, and-user.

-R, -- result-file = name
Point the output to the specified file.

-S, -- short-form
Only the statements contained in the log are displayed, and other information is not displayed. This method can reduce the size of the generated SQL file.

-S, -- socket = name
The socket file used for connection.

-- Start-datetime = name
Reads events that are equal to or later than the datetime parameter from binary logs. The datetime value is relative to the local time zone on the machine that runs mysqlbinlog.
The value format must be DATETIME or TIMESTAMP data type. For example, 11:25:56, we recommend that you use quotation marks.

-- Stop-datetime = name
Read all log events that are less than or equal to datetime from binary logs. For descriptions of datetime values, see the -- start-datetime option.

-J, -- start-position = #
Read from the event where the second position in the binary log is equal to N.

-- Stop-position = #
Stop reading from the event where 1st locations are equal to or greater than N in the binary log.

-- Server-id = #
Extract only binlog logs of the specified server_id

-- Set-charset = name
Add set names character_set to output

-T, -- to-last-log
The end of the binary log requested by the MySQL server is not stopped, but is printed until the end of the last binary log.
If the output is sent to the same MySQL server, an infinite loop occurs. This option requires-read-from-remote-server.

-D, -- disable-log-bin
Disable binary logs. If you use the-to-last-logs option to send the output to the same MySQL server, you can avoid infinite loops.
This option is also useful for crash recovery, avoiding copying recorded statements. Note: This option requires the SUPER permission.

-U, -- user = name
The MySQL user name used to connect to the remote server.

-V, -- verbose
Outputs binlog logs in the row mode.-vv adds comments to the column data type.

-V, -- version
Display version information and exit.

 

5. Summary
A. You can use show binlog events and mysqlbinlog to extract binlog.
B. The show binlog events parameter is not suitable for batch extraction. mysqlbinlog can be used for batch extraction to create SQL statements for database restoration.
C. mysqlbinlog can be incomplete or point-in-time recovery based on time points and position.
D. mysqlbinlog can be used to extract binlog logs locally or remotely.
E. mysqlbinlog can be extracted based on server_id and database level, but does not support table level.