Use mysqlbinlog to extract binary logs

Source: Internet
Author: User

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

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. Only view the content of the first binlog file (show binlog events)
Mysql> use replication;
Database changed
Mysql> 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 '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 |
+ ----------------- + ----- + ------------- + ----------- + ------------- + ---------------------------------------------------- +
-- In the preceding result, the SQL statement we executed is displayed in Row 3, and the 3rd statement is automatically submitted.
-- Author: Leshami
-- Blog: http://blog.csdn.net/leshami

B. view the content of the specified binlog file (show binlog events in 'binname. xxxxx ')
Mysql> show binlog events in 'app01bin. 100 ';
+ ----------------- + ----- + ------------- + ----------- + ------------- + ---------------------------------------------------- +
| 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 'replicase'; 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_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;
+ ----------------- + ----- + ------------ + ----------- + ------------- + ---------------------------------------------------- +
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+ ----------------- + ----- + ------------ + ----------- + ------------- + ---------------------------------------------------- +
| 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. 000001

C. 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 .gz

D. Extract binlog logs at the specified position and import them to the database.
# Mysqlbinlog -- start-position = "120" -- stop-position = "332"/opt/data/APP01bin. 000001 | mysql-uroot-p

E. 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. SQL

F. Extract multiple binlog log files at the specified location
# Mysqlbinlog -- start-position = "120" -- stop-position = "332"/opt/data/APP01bin. 000001/opt/data/APP01bin. 000002 | more

G. 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. SQL

H. Remotely extract logs and specify the end time
# Mysqlbinlog-urobin-p-h192.168.1.116-P3306 -- stop-datetime = "20:30:23" -- read-from-remote-server mysql-bin.000033 | more

I. Remotely extract binlog logs in row format and output them to a 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.

Use mysqlbinlog to restore data based on location or time point

This article permanently updates the link address:

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.