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 achieve point-in-time recovery or incomplete recovery. This paper mainly describes the hypothesis extraction Binlog log. and give the relevant demonstration sample.

Refer to the introduction and description of Binlog: MySQL binary log (binary logs)

1. Several ways of extracting mysqlbinlog
A. Use Show Binlog events mode to get the current and specified Binlog logs. It is not appropriate to extract 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, just view 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> Show Binlog 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 |+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------+--The SQL statement we ran on line 3rd in the above results. The 4th Act commits itself voluntarily--author:leshami--blog:http://blog.csdn.net/leshamib, view the contents of the specified Binlog file (show Binlog events in ' binname.xxxx X ') 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, extract Binlog of specified start time and output to 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 frequently used options only)
-?,--Help
Displays the help message and exits.

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

-F,--force-read
Use this option. Suppose Mysqlbinlog reads a binary log event that it does not recognize. It prints a warning, ignores the event, and continues.

Without this option, it is assumed that Mysqlbinlog reads such an event to stop.

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

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

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

-P,--password[=name]
The password that is used when connecting to the server. Suppose you use the short option form (-p), there can be no spaces between options and password.
Assuming that there is no 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 MySQLServer. Assume that the option is not given, regardless of the connection parameters option is 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 included in the log are displayed. 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
Reads an event that is equal to or later than the DateTime reference from the binary log, relative to the local time zone on the machine where the mysqlbinlog is executed.
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 the indicator symbol.

--stop-datetime=name
Reads all log events that are less than or equal to datetime from the binary log. Description of datetime values see the--start-datetime option.

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

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

--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 requested binary log in MySQLServer, it continues to print until the end of the last binary log.
Assuming that the output is sent to the same mysqlserver, it will cause an infinite loop.

This option requires –read-from-remote-server.

-D,--disable-log-bin
Disables binary logging.

Assuming that the –to-last-logs option is used to send the output to the same mysqlserver, an infinite loop can be avoided.

This option is also useful for crash recovery and avoids copying statements that have already been recorded.

Gaze: This option requires super privilege.

-U,--user=name
The mysqlusername used when connecting to remote server.

-V,--verbose
Used to output Binlog logs based on row mode,-VV adds gaze to column data types

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

5. Summary
A, can use show Binlog events and Mysqlbinlog way to extract Binlog log.
b, show Binlog Events Limited is not suitable for batch extraction. Mysqlbinlog can be used for batch extraction to build SQL for the recovery database.
C, Mysqlbinlog can be based on the point of time, position, and other ways to achieve incomplete recovery or point of recovery.

D, Mysqlbinlog can extract binlog logs from local or remote support.
E, Mysqlbinlog can extract logs based on server_id and at the database level. Table level is not supported.

idkey=e61fdd22421b38cb5eef12db3c564ac0b5840f0f5cae4958e8b646740a79e680 "target=" _blank ">

Extract binary logs using Mysqlbinlog