How to use binlog for data recovery in MySQL

Preface

Recently, a data was operated incorrectly online. Since the database was modified directly, the only way to recover it is in MySQL binlog. Binlog uses ROW mode, which means that a SQL statement is generated for each affected record. The binlog2sql project is also used.

MySQL Binary Log, also known as bin-log, is a binary log file generated by MySQL execution changes. It has two main functions:

* Data Reply

* Master-slave database. Used by the slave side to perform additions, deletions, and modifications to keep it synchronized with the master.

Basic configuration and format of binlog

Basic configuration of binlog

Binlog needs to be configured in the mysqld node of the mysql configuration file:

# Serverid in the log
server-id = 1
# Log path log_bin = /var/log/mysql/mysql-bin.log
# How many days to save logs expire_logs_days = 10
# The size of each binlog max_binlog_size = 1000M
#binlgo mode binlog_format=ROW
# The default is all records, you can configure which ones need to be recorded and which ones are not recorded #binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name

View binlog status

• SHOW BINARY LOGS; View binlog files
• SHOW VARIABLES LIKE '%log_bin%' to view the log status
• SHOW MASTER STATUS View the log file location

Three formats of binlog

1.ROW

For row logging, each row modification generates a record.

Advantages: The context information is relatively complete. When recovering from an erroneous operation, the original information can be found directly in the log. It has good support for master-slave replication.

Disadvantages: The output is very large, and if it is an Alter statement, it will generate a large number of records

The format is as follows:

DELETE FROM `back`.`sys_user` WHERE `deptid`=27 AND `status`=1 AND `account`='admin' AND `name`='张三' AND `phone`='18200000000' AND `roleid`='1' AND `createtime`='2016-01-29 08:49:53' AND `sex`=2 AND `email`='[email protected]' AND `birthday`='2017-05-05 00:00:00' AND `avatar`='girl.gif' AND `version`=25 AND `password`='ecfadcde9305f8891bcfe5a1e28c253e' AND `salt`='8pgby' AND `id`=1 LIMIT 1; #start 4 end 796 time 2018-10-12 17:03:19

2.STATEMENT

For SQL statements, each statement generates a record

Advantages: The amount of logs generated is relatively small, and the master and slave versions can be inconsistent

Disadvantages: Some statements in the master-slave relationship cannot be supported, such as auto-increment primary keys and UUID.

The format is as follows:

delete from `sys_role`;

3.MIX

It combines the advantages of both. In general, the STATEMENT mode is used, and the ROW mode is used for unsupported statements.

Convert to sql

mysqlbinlog

Since binlog is binary, it needs to be converted into a text file first. Generally, you can use mysqlbinlog that comes with MySQL to convert it into text.

mysqlbinlog --no-defaults --base64-output='decode-rows' -d room -v mysql-bin.011012 > /root/binlog_2018-10-10

Parameter Description

• --no-defaults To prevent the error: mysqlbinlog: unknown variable 'default_character_set=utf8mb4'
• --base64-output='decode-rows' is used together with -v to perform base64 decoding. There are many other parameters used to limit the range, such as database, start time, start position, etc. These parameters are very useful when troubleshooting.

The basic blocks of binlog are as follows:

# at 417750
#181007 1:50:38 server id 1630000 end_log_pos 417844 CRC32 0x9fc3e3cd Query thread_id=440109962 exec_time=0 error_code=0
SET TIMESTAMP=1538877038/*!*/;
BEGIN

1. # at 417750

Specifies the offset of the current position relative to the beginning of the file. This can be used as the --start-position parameter in the mysqlbinlog command.

2. #181007 1:50:38 server id 1630000 end_log_pos 417844 CRC32 0x9fc3e3cd Query thread_id=440109962 exec_time=0 error_code=0

181007 1:50:38 indicates the time is 1:50:38 on October 7, 2018. Serverid is what you configured in the configuration file. End_log_pos 417844 means this block ends at 417844. thread_id: thread id of execution, exec_time: execution time, error_code: error code

3. SET TIMESTAMP=1538877038/!/;

BEGIN

Specific execution statement

The log generated by one line of record is as follows

# at 417750
#181010 9:50:38 server id 1630000 end_log_pos 417844 CRC32 0x9fc3e3cd Query thread_id=440109962 exec_time=0 error_code=0
SET TIMESTAMP=1539136238/*!*/;
BEGIN
/*!*/;
# at 417844
#181010 9:50:38 server id 1630000 end_log_pos 417930 CRC32 0xce36551b Table_map: `goods`.`good_info` mapped to number 129411
# at 417930
#181010 9:50:38 server id 1630000 end_log_pos 418030 CRC32 0x5827674a Update_rows: table id 129411 flags: STMT_END_F
### UPDATE `goods`.`good_info`
###WHERE
### @1='2018:10:07' /* DATE meta=0 nullable=0 is_null=0 */
### @2=9033404 /* INT meta=0 nullable=0 is_null=0 */
### @3=1 /* INT meta=0 nullable=0 is_null=0 */
### @4=8691108 /* INT meta=0 nullable=0 is_null=0 */
### @5=9033404 /* INT meta=0 nullable=0 is_null=0 */
### @6=20 /* LONGINT meta=0 nullable=0 is_null=0 */
### @7=1538877024 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### SET
### @1='2018:10:07' /* DATE meta=0 nullable=0 is_null=0 */
### @2=9033404 /* INT meta=0 nullable=0 is_null=0 */
### @3=1 /* INT meta=0 nullable=0 is_null=0 */
### @4=8691108 /* INT meta=0 nullable=0 is_null=0 */
### @5=9033404 /* INT meta=0 nullable=0 is_null=0 */
### @6=21 /* LONGINT meta=0 nullable=0 is_null=0 */
### @7=1538877024 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
# at 418030
#181010 9:50:38 server id 1630000 end_log_pos 418061 CRC32 0x468fb30e Xid = 212760460521
COMMIT /*!*/;
# at 418061

The log generated by one line of record is shown above. Start with SET TIMESTAMP=1539136238/*!*/; and end with COMMIT/*!*/; We can limit the range based on the locations indicated by the two at.

Note the # at 417750 before the SET TIMESTAMP at the beginning of a record and the # at 418061 after the COMMIT at the end.

Using binlog2sql

binlog2sql official website introduction: Parse the SQL you want from MySQL binlog. Depending on the options, you can get the original SQL, rollback SQL, INSERT SQL without primary key, etc.

The basic usage is as follows:

python binlog2sql.py -hlocalhost -P3306 -udev -p'\*' -d room -t room_info --start-file='mysql-bin.011012' --start-position 129886892 --stop-position 130917280 > rollback.sql

I will not explain the specific usage as it is explained very clearly on GitHub. You can mainly look at the many conditions used for filtering, such as start and end time --start-datetime/--stop-datetime, table name limitation -t, database limitation -d, statement limitation --sql-type. I will mainly talk about some problems I encountered.

mysql binlog mode

It needs to be set to ROW here, because the ROW mode has the original information. If you can directly use binlog2sql to reversely generate the rollback SQL, if STATEMENT cannot be generated, you need to use the MySQL scheduled backup file to do the rollback.

Specific operations for restoring data

Because what was executed online at that time was an update statement, and there was no unique key index. As a result, more than two thousand records were updated. The statement is as follows:

update room_info set status=1 where status=2;

• First locate the corresponding binlog file according to the operation time. I remember that the operation time was around 9 am, so find the corresponding binlog file whose last modification time was greater than 9 o'clock and the closest time. Use the Linux ll command to view the modification time of the file.
• Filter specific databases Because all binlog files of a MySQL instance are in one file, we first need to remove other databases that we do not want to close. Use the -d parameter to specify the data instance. Then use the start time (--start-datetime) and end time (--stop-datetime) to further filter
  

mysqlbinlog --no-defaults -v --base64-output='decode-rows' -d room --start-datetime='2018-10-10 9:00:00' --stop-datetime='2018-10-10 10:00:00' mysql-bin.011012>temp.sql

• Compressed retrieval file analysis

zip temp.zip temp.sql && sz temp.zip

Retrieve the file and analyze it locally with a text tool such as vscode, which has regular expression matching. Based on the features you have changed, for example, I have a room number 888888, which should not be modified. You can check the modification record of this room number. The statement in ROW mode is Where first and set second. Using the regular expression room_id=888888.*show_state=1.*AND show_state=2 it can be matched quickly. My statement at that time affected more than 2,000 records. Based on the statement you found, find the at before the starting SET TIMESTAMP=1539136238 and the at after the ending COMMIT.

• Generate rollback statements using binlog2sql

python binlog2sql.py -hlocalhost -P3306 -udev -p'*' -d room -t room_info -B --start-file='mysql-bin.011012' --start-position 129886892 --stop-position 130917280 > rollback.sql

in addition

Because here I have a situation where one update affects multiple records. If it is a unique key, only one record is affected. There is no need to be so troublesome. Just use binlog2sql with -d and -t parameters to limit the database and table, and then use grep to search, and you can directly get the corresponding SQL. mysqlbinlog lacks the function of limiting tables and limiting statements. For example, a Delete statement that is accurate to a table can reduce a lot of data and can be located quickly.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.