Shulou(Shulou.com)06/01 Report--

Preface

Recently, a data was mismanipulated online, and because it is a directly modified database, the only way to recover it is in mysql's binlog. Binlog uses the ROW mode, which means that a sql is generated for each record affected. At the same time, the binlog2sql project is used.

MySQL Binary Log, also known as bin-log, is a binary log file generated by mysql execution changes, which can be used for two main purposes:

* data reply

* Master-slave database. It is used for the slave end to perform additions, deletions and modifications to keep 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 configuration file of mysql:

# Serveridserver-id in the log = log path log_bin = / log saved for several days expire _ logs_days = 1 "the size of each binlog max_binlog_size = 1000M#binlgo mode binlog_format=ROW# defaults to all records, which can be configured which need to be recorded and which do not record # binlog_do_db = include_database_name#binlog_ignore_db = include_database_name

View binlog status

SHOW BINARY LOGS; View binlog File SHOW VARIABLES LIKE'% log_bin%' View Log status SHOW MASTER STATUS View Log File location

Three formats of binlog

1.ROW

For row logging, one record is generated for each row modification.

Advantages: the context information is relatively complete, when restoring a misoperation, you can directly find the original information in the log, which is good for master-slave replication.

Disadvantages: the output is very large, if it is an Alter statement, it will produce a large number of records

The format is as follows:

DELETE FROM `back`.`sys _ user`WHERE `deptid` = 27 AND `status` = 1 AND `statt` = 'admin' AND `name` =' Zhang San 'AND `phone` =' 18200000000' AND `roleid` ='1' AND `createtime` = '2016-01-29 0849 time 53' AND `sex` = 2 AND `email` =' sn93@qq.com' AND `roomday` = '2017-05-05 00RV 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

2.STATEMENT

For sql statements, each statement produces a record

Advantages: the number of logs generated is relatively small, and the master-slave versions can be inconsistent.

Disadvantages: some statements cannot be supported by master and slave, such as self-increasing primary key and UUID

The format is as follows:

Delete from `sys_ role`

3.MIX

Combining the advantages of the two, STATEMENT mode is generally adopted, and ROW mode is used for unsupported statements.

Convert to sql

Mysqlbinlog that comes with mysql

Because binlog is binary, it needs to be converted to a text file first, which can generally be converted into text using the mysqlbinlog that comes with Mysql.

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

Parameter description

-- no-defaults to prevent error reports: mysqlbinlog: unknown variable 'default_character_set=utf8mb4'--base64-output='decode-rows' is used with-v to decode base64.

There are many other parameters used to limit the scope, such as database, start time, start position, and so on. These parameters are useful when looking for misoperations.

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=0SET timestamp 1538877038Compact begin

1 、 # at 417750

Indicates the offset of the current position from the beginning of the file, which can be used as an argument to-- start-position 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 specifies the time for October 7, 18, 1 end_log_pos 50 Switzerland 38 # serverid, which you configured in the configuration file, and this block ends at 417844. Thread_id execution thread id,exec_time execution time, error_code error code

3. SET timestamp 1538877038Universe /

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 recording 2018 10 07' / * DATE meta=0 nullable=0 is_null=0 * /

# @ 2mm 9033404 / * INT meta=0 nullable=0 is_null=0 * /

# @ 3room1 / * INT meta=0 nullable=0 is_null=0 * /

# @ 44th 8691108 / * INT meta=0 nullable=0 is_null=0 * /

# @ 5mm 9033404 / * INT meta=0 nullable=0 is_null=0 * /

# @ 634 20 / * LONGINT meta=0 nullable=0 is_null=0 * /

# @ 7 TIMESTAMP 1538877024 / * TIMESTAMP (0) meta=0 nullable=0 is_null=0 * /

# SET

# @ 1 recording 2018 10 07' / * DATE meta=0 nullable=0 is_null=0 * /

# @ 2mm 9033404 / * INT meta=0 nullable=0 is_null=0 * /

# @ 3room1 / * INT meta=0 nullable=0 is_null=0 * /

# @ 44th 8691108 / * INT meta=0 nullable=0 is_null=0 * /

# @ 5mm 9033404 / * INT meta=0 nullable=0 is_null=0 * /

# @ 6room21 / * LONGINT meta=0 nullable=0 is_null=0 * /

# @ 7 TIMESTAMP 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

Com _ MIT _ blank /

# at 418061

The log generated by one-line recording is shown above. Begin with SET timestamp 1539136238, and end with COMMIT. We can limit the scope according to the location indicated by the two at.

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

Using binlog2sql

Binlog2sql official website introduction: parse the SQL you want from MySQL binlog. Depending on the options, you can get the original SQL, roll back the SQL, remove the primary key INSERT SQL, and so on.

The basic uses are 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 use of github explained very clearly, mainly look at a lot of conditions used to filter, such as start and end time-start-datetime/--stop-datetime, table name qualification-t, database qualification-d, statement qualification-sql-type, mainly talk about some of the problems I encountered.

Binlog mode of mysql

It needs to be set to ROW, because the ROW mode has the original information. If you can directly use binlog2sql to generate rollback sql in reverse, if STATEMENT cannot be generated, you need to use the files backed up regularly by mysql to roll back.

The specific operation of restoring data

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

Update room_info set status=1 where status=2; first locates the corresponding binlog file according to the operation time.

I remember that the operation time at that time was about 9: 00 in the morning, so I went to find the corresponding binlog file whose last modification time was greater than 9: 00 and the closest one. Use the ll command of linux to see when the file was modified. Filter specific databases

Because all the binlog files of a mysql instance are in one file, we first need to remove other databases that we don't want to close. The-d parameter is used to indicate the data instance. Then use the start time (--start-datetime) and the end time (- stop-datetime) to further filter

Mysqlbinlog-- no-defaults-v-- base64-output='decode-rows'-d room-- start-datetime='2018-10-10-10 9-- stop-datetime='2018-10-10-10-- stop-datetime='2018-10-10-- stop-datetime='2018-10-10-- stop-datetime='2018-10-10-10-- stop-datetime='2018-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10-10

Retrieve the file locally using text tools such as vscode analysis, in which there is a regular match. According to the characteristics you have changed, for example, I have a room number 888888, this should not be modified, you can check the change record of this room number. The statement of ROW mode is Where first and set last. It can be matched quickly using regular room _ id=888888.*show_state=1.*AND show_state=2. My statement at that time affected more than two thousand records. According to the statement you found, you went to find the at before the beginning of the SET TIMESTAMP=1539136238 and the at after the ending COMMIT.

Using binlog2sql to generate the rollback statement python binlog2sql.py-hlocalhost-P3306-udev-pendant'- d room-t room_info-B-start-file='mysql-bin.011012'-- start-position 129886892-- stop-position 130917280 > rollback.sql

In addition,

Because my side is a update affects multiple cases, if it is with a unique key, the impact of only one record, there is no need to be so troublesome, directly use binlog2sql with-d and-t parameters to define the database and tables, and then use grep to find, you can directly get the corresponding sql. Mysqlbinlog is missing the functionality of a qualifying table and qualifying statement. For example, accurate to a table of Delete statements, can reduce a lot of data, can be quickly located.

Summary

The above is the whole content of this article, I hope that the content of this article has a certain reference and learning value for your study or work, if you have any questions, you can leave a message and exchange, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.