Extract binary logs using Mysqlbinlog

The MySQL binlog log records all changes to the current database since the MySQL database was enabled for logging. The Binlog log is a binary file that we can extract from binlog to generate readable SQL statements to reconstruct the current database and to implement point-in-time or incomplete recovery as needed. This article mainly describes the extraction of binlog logs, and gives relevant examples.

For an introduction and description of Binlog, please refer to: MySQL binary logs (binary log)

1. Several ways of extracting mysqlbinlog
A. Use show Binlog events to get the current and specified Binlog logs, which is not appropriate for fetching large numbers of logs.
b, use Mysqlbinlog command line extraction (suitable for batch extraction of logs).

2. Demo Show Binlog Events mode

Mysql> Show variables like ' version '; +---------------+------------+| variable_name | Value |+---------------+------------+| Version | 5.6.12-log |+---------------+------------+mysql> Show binary logs;+-----------------+-----------+| Log_name | File_size |+-----------------+-----------+|       app01bin.000001 | |+-----------------+-----------+a, view only the contents 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> ShowBinlog events;+-----------------+-----+-------------+-----------+-------------+-------------------------------- --------------------+| Log_name | Pos | Event_type | server_id | End_log_pos | Info |+-----------------+-----+-------------+-----------+-------------+----- -----------------------------------------------+|   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 ' Replication '; 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 |+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------+--in the above results, we can see the SQL statement we executed in line 3rd. 4th Behavior Auto Commit--author:leshami--blog:http://blog.csdn.net/leshamib, view the contents of the specified Binlog file (show Binlog events in ' binname.xxxxx ' ) mysql> show Binlog events in ' app01bin.000002 ', +-----------------+-----+-------------+-----------+------------- +----------------------------------------------------+| Log_name | Pos | Event_type | server_id | End_log_pos | Info |+-----------------+-----+-------------+-----------+-------------+----- -----------------------------------------------+|   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 ' Replication '; INSERT into TB values (3, ' Fred ') | | app01bin.000002 | 332 |        Xid |         11 | 363 | COMMIT/* xid=394 */|+-----------------+-----+-------------+-----------+-------------+----- -----------------------------------------------+c, view the Binlog file currently being written (show Master status\g) mysql> Show Master status\g*************************** 1. Row *************************** file:app01bin.000002 position:363 binlog_do_db:binlog_ignore_d B:executed_gtid_set:1 row in Set (0.00 sec) d, gets the contents of the specified position Binlog (show Binlog events from) mysql> show Binlog events from 213;+-----------------+-----+------------+-----------+-------------+------------------------------------------- ---------+| Log_name | Pos | Event_type | server_id | End_log_pos | Info |+-----------------+-----+------------+-----------+-------------+------ ----------------------------------------------+| app01bin.000001 | 213 |        Query |         11 | 332 | Use ' Replication '; 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, Demo Mysqlbinlog method extract Binlog

A, extract the specified binlog log # 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 log for the specified position location # Mysqlbinlog- -start-position= "--stop-position=" "332"/opt/data/app01bin.000001c, extract position log from specified Binlog location and output to compressed file # Mysqlbinlog--start-position= "" "--stop-position=" 332 "/opt/data/app01bin.000001 |gzip >extra_01.sql.gzd, Extract the Binlog log for the specified position location Import Database # Mysqlbinlog--start-position= ""--stop-position= "332"/opt/data/app01bin.000001 | Mysql-uroot-pe, extracts the binlog of the specified start time and outputs to the log file # Mysqlbinlog--start-datetime= "2014-12-15 20:15:23"/opt/data/ app01bin.000002--RESULT-FILE=EXTRA02.SQLF, extracting multiple Binlog log files in the specified location # mysqlbinlog--start-position= "--stop-position" = "332"/opt/data/app01bin.000001/opt/data/app01bin.000002|moreg, extracts the specified database Binlog and converts the character set to utf8# Mysqlbinlog--database= Test--set-charset=utf8/opt/data/app01bin.000001/opt/data/app01bin.000002 >TEST.SQLH, remote fetch log, 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 using row format Binlog log 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 for Mysqlbinlog (list common options only)
-?,--Help
Displays the help message and exits.

-D,--database=name
Lists only the entries for the database (local logs only).

-F,--force-read
With this option, if Mysqlbinlog reads a binary log event that it does not recognize, it prints a warning, ignores the event, and continues. Without this option, if Mysqlbinlog reads such an event, it stops.

-H,--host=name
Gets the binary log of the MySQL server on the given host.

-L,--local-load=name
Preprocess the local temporary file for the load DATA infile in the specified directory.

-O,--offset=#
Skips the first n entries.

-P,--password[=name]
The password to use when connecting to the server. If you use the Short option form (-p), there can be no spaces between options and passwords.
If you do not have a password value after the –password or-P option on the command line, you are prompted 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 the binary log from the MySQL server. If this option is not given, any connection parameter options will be ignored, that is, connect to local.
These options are –host, –password, –port, –protocol, –socket, and –user.

-R,--result-file=name
Points the output to the given file.

-S,--short-form
Only the statements contained in the log are displayed, and no additional information is displayed, which reduces the size of the generated SQL file.

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

--start-datetime=name
Events that are equal to or later than the DateTime parameter are read from the binary log, and the datetime value is relative to the local time zone on the machine that is running Mysqlbinlog.
The value format should conform to the datetime or timestamp data type. For example: 2004-12-25 11:25:56, it is recommended to use quotation marks.

--stop-datetime=name
Reads all log events that are less than or equal to datetime from the binary log. See the--start-datetime option for a description of the datetime value.

-j,--start-position=#
Start reading from the event that the 1th position in the binary log equals the n parameter.

--stop-position=#
Stops reading from the event that the 1th position in the binary log is equal to and greater than the N parameter.

--server-id=#
Extract only Binlog logs for the specified server_id

--set-charset=name
Add set NAMES character_set to output

-T,--to-last-log
Instead of stopping at the end of the binary log requested in the MySQL server, continue printing until the end of the last binary log.
If the output is sent to the same MySQL server, it will cause an infinite loop. This option requires –read-from-remote-server.

-D,--disable-log-bin
Disables binary logging. If you use the –to-last-logs option to send the output to the same MySQL server, you can avoid an infinite loop.
This option is also useful for crash recovery and avoids copying statements that have already been recorded. Note: This option requires Super permissions.

-U,--user=name
The MySQL user name to use when connecting to the remote server.

-V,--verbose
Used to output Binlog logs based on row mode,-VV add comments for column data types

-V,--version
Displays the version information and exits.

5. Summary
A, the Binlog log can be extracted by show Binlog events and Mysqlbinlog mode.
b, show Binlog events Parameters Limited is not suitable for batch extraction, Mysqlbinlog can be used for batch extraction to establish a recovery database SQL.
C, Mysqlbinlog can be based on the point of time, position and other ways to achieve incomplete recovery or point recovery.
D, Mysqlbinlog can extract binlog logs from local or remote support.
E, Mysqlbinlog can extract logs based on server_id and database-level, and does not support table-level.

Extract binary logs using Mysqlbinlog