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

I don't know if you have any knowledge of previous articles like how to use binlog to recover or flash database data in MySQL. Today I'm here to tell you a little bit about it. If you are interested, let's take a look at the body. I'm sure you'll get something after seeing how to use binlog to recover or flash database data in MySQL.

Binlog in STATEMENT format

To enable binlog, you need to pass in the-- log-bin parameter when you start MySQL. Alternatively, you can set log_bin to enable binlog in the MySQL configuration file / etc/my.cnf. MySQL 5.7.When binlog is enabled, the-- server-id parameter must also be specified, otherwise the MySQL CVM will fail to start.

Binlog_format supports STATEMENT, ROW and MIXED formats. MySQL 5.5,5.6 defaults to STATEMENT,MySQL 5.7.7 and defaults to ROW. If SQL uses UUID (), RAND (), VERSION () and other functions, or uses stored procedures or custom functions, the master-slave complex time based on STATEMENT is not safe (many people may think that NOW (), CURRENT_TIMESTAMP these functions are also insecure, in fact, are safe) [doc1, doc2]. Master-slave replication based on ROW is the safest way of replication.

Now let's take a look at the changes to the binlog,/etc/my.cnf file in STATEMENT format as follows:

Server_id = 1log_bin = mysql-binbinlog_format = STATEMENTbinlog_row_image=FULL

After restarting MySQL, the corresponding binlog files, mysql-bin.index and mysql-bin.000001 will be generated under the data directory datadir, such as / var/lib/mysql/,. The file with the .index suffix saves all binlog file names. The mysql-bin.000001 file records the binlog content. Each time MySQL starts or flush logs, a new log file is created by sequence number. In addition, when the log file size exceeds max_binlog_size, a new log file is created.

Now let's try the binlog feature. Suppose there is a hello table in the testdb library, and modify one of the rows:

Mysql > select * from hello;+----+-+ | id | name | 1 | Andy | 2 | Bill | | 3 | Candy | +-- +-- + 4 rows in set (0.00 sec) mysql > update hello set name = 'Will' where id = 3 sec query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0

Binlog is a binary file. You need to use the mysqlbinlog (doc, man) command to view:

$sudo mysqlbinlog / var/lib/mysql/mysql-bin.000001 # read the binlog file $mysqlbinlog-R-h292.168.2.107-uroot-p123456 mysql-bin.000001 # directly on the mysql CVM or remotely read the binlog file

The contents of the new binlog file added after the execution of update:

# at 154 minutes 180617 22:47:49 server id 1 end_log_pos 219 CRC32 0x4bd9d69b Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=noSET @ @ SESSION.GTID_NEXT= 'ANONYMOUS219,180617 22:47:49 server id 1 end_log_pos 302 TIMESTAMPbands 1529246869 session set @ @ session. SET @ @ session.foreign_key_checks=1, @ @ session.sql_auto_is_null=0, @ @ session.unique_checks=1, @ @ session.session .sqlchocolate modewords 1075838976, @ @ session.autoincrementalization offsetbacks @ session.auto_increment_increment=1, @ @ session.autoincrementalization offsetbacks @ @ session. Session utf8 * / / *! * /; SET @ @ session.session. SET @ @ session.lcfolio timekeeper namespace namespace @ @ session.collationalization databaseholders default update hello set name ='at 302 '180617 22:47:49 server id 1 testdb` / *! /; SET timestamp 1529246869 # at 423 server id 180617 22:47:49 server id 1 end_log_pos 454 CRC32 0x68da744a Xid = 12COMMIT Universe

Binlog in ROW format

Change the binlog_format of / etc/my.cnf to ROW, and then restart MySQL. When the format is modified, a new binlog file, mysql-bin.000002, is generated.

Mysql > show create table hello +-+-+ | Table | Create Table+-+- -+ | hello | CREATE TABLE `hello` (`id` bigint (20) NOT NULL AUTO_INCREMENT `name` varchar (20) DEFAULT NULL, PRIMARY KEY (`id`) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 | +-+-+ 1 row in set (0.00 sec) mysql > select * from hello where id +-+-+ | id | name | +-+-+ | 1 | Andy | 2 | Lily | 3 | Will | +-+-- + 1 row in set (0.00 sec) mysql > update hello set name = 'David' where id = 3ash query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0

To see the binlog in ROW format, you need to use the sudo mysqlbinlog-v-- base64-output=DECODE-ROWS / var/lib/mysql/mysql-bin.000002 command. The corresponding new binlog content after executing update:

# at 154 "180617 22:54:13 server id 1 end_log_pos 219 CRC32 0x2ce70d4d Anonymous_GTID last_committed=0 sequence_number=1 rbriches onlylyzed brands: set @ @ SESSION.GTID_NEXT= 'ANONYMOUSAccord 22:54:13 server id 1 end_log_pos 293 CRC32 0x8183fddf Query thread_id=2 exec_time=0 error_code=0SET timespacks 1529247253 CRC32 0x8183fddf Query thread_id=2 exec_time=0 error_code=0SET timespacks 1529247253 SET @ @ session.foreign_key_checks=1, @ @ session.sql_auto_is_null=0, @ @ session.unique_checks=1, @ @ session.session .sqlchocolate modewords 1075838976, @ @ session.autoincrementalization offsetbacks @ session.auto_increment_increment=1, @ @ session.autoincrementalization offsetbacks @ @ session. Session utf8 * / / *! * /; SET @ @ session.session. SET @ @ session.lcdating timetables namespace 0 license plansSet @ @ session.collationalization databases database where session. Collationalization database contains default timetables. # at 293 "180617 22:54:13 server id 1 end_log_pos 346 CRC32 0x0fc7e1a4 Table_map: `testdb`.`hello` mapped to number 11" at 346 "180617 22:54:13 server id 1 end_log_pos 411 CRC32 0xb58e729d Update_rows: table id 110 flags: STMT_END_F### UPDATE `testdb`.`hello` # WHERE### @ 1" 3 "flags # @ 2='Will'### SET### @ 1" 3 "" # at 411 server id 180617 22:54:13 server id 1 end_log_pos 442 CRC32 0xef964db8 Xid = 13COMMIT Universe

To execute the following SQL:

Mysql > insert hello (name) values ('Frank'); Query OK, 1 row affected (0.02 sec)

The corresponding generated binlog content:

# at 442 "180617 22:55:47 server id 1 end_log_pos 507 CRC32 0x79de08a7 Anonymous_GTID last_committed=1 sequence_number=2 rbrachionlyzag" 50718 SET TRANSACTION ISOLATION LEVEL READ committable pedestal "set @ @ SESSION.GTID_NEXT= 'ANONYMOUSAccording to" at 507 "180617 22:55:47 server id 1 end_log_pos 581 CRC32 0x56f9eb6a Query thread_id=2 exec_time=0 error_code=0SET timestamp 1529247347 # at 581 "180617 22:55:47 server id 1 end_log_pos 634 CRC32 0xedb73620 Table_map: `testdb`.`hello` mapped to number 11" at 634 "180617 22:55:47 server id 1 end_log_pos 684 CRC32 0x525a6a70 Write_rows: table id 110flags: STMT_END_F### INSERT INTO `testdb`.`hello` # SET### @ 1" 2='Frank'# at 684" 180617 22:55:47 server id 1 end_log_pos 715 CRC32 0x09a0d4de Xid = 14COMIT

To execute the following SQL:

Mysql > delete from hello where id = 2 * * query OK, 1 row affected (0.02 sec)

The corresponding generated binlog content:

# at 715 "180617 22:56:44 server id 1 end_log_pos 780 CRC32 0x9f52450e Anonymous_GTID last_committed=2 sequence_number=3 rbrachionlyzag" 50718 SET TRANSACTION ISOLATION LEVEL READ commit Energizer "set @ @ SESSION.GTID_NEXT= 'ANONYMOUSAccording to" at 780 "180617 22:56:44 server id 1 end_log_pos 854 CRC32 0x0959bc8d Query thread_id=2 exec_time=0 error_code=0SET timestamp 1529247404 # at 854 October 180617 22:56:44 server id 1 end_log_pos 907 CRC32 0x2945260f Table_map: `testdb`.`hello` mapped to number 11inclusive at 907 "180617 22:56:44 server id 1 end_log_pos 956 CRC32 0xc70df255 Delete_rows: table id 110flags: STMT_END_F### DELETE FROM `testdb`.`hello` # WHERE### @ 1mm 2mm # @ 2='Bill'# at 956" 180617 22:56:44 server id 1 end_log_pos 987 CRC32 0x0c98f18e Xid = 15COMMIT

Restore using binlog increments

MySQL logical backup usually combines full backup and incremental backup, using mysqldump to back up the database on a regular basis, and then using binlog to save incremental data. When you restore data, you restore the data backed up by mysqldump to the point in time of the backup. Incremental changes to the database from the backup time point to the current time, the incremental data in binlog is restored to the database through mysqlbinlog. Now suppose you have used mysqldump to restore the database to:

Mysql > select * from hello;+----+-+ | id | name | +-+-+ | 1 | Andy | | 2 | Lily | | 3 | Will | +-+-+ 3 rows in set (0.00 sec)

The SQL executed after that:

Update hello set name = 'David' where id = 3 * insert hello (name) values (' Frank'); delete from hello where id = 2

Whether you use the STATEMENT or ROW,mysqlbinlog command, you can restore binlog increments to the database [doc].

Observing the binlog, we can see that from the beginning update hello set name = 'David' where id = 3; to the final delete from hello where id = 2 * *, the time is from "2018-06-17 22:54:13" to "2018-06-17 22:56:44", so based on the point-in-time recovery, the command is as follows:

$sudo mysqlbinlog-- start-datetime= "2018-06-17 22:54:13"-- stop-datetime= "2018-06-17 22:56:44" mysql-bin.000002 | mysql- uroot-p123456

The event location number of binlog is from 154to 956, but it should be noted that the location point range is specified with-- start-position and-- stop-position, which logically corresponds to start select * from hello;+----+-+ | id | name | 1 | Andy | 3 | David | 4 | Frank | +-+-- + 3 rows in set (0.00 sec)

Use binlog2sql flashback

Binlog2sql, by Cao Danfeng, Dianping DBA. Binlog2sql, 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. Binlog2sql, the underlying implementation depends on python-mysql-replication, and the library completes the parsing of MySQL replication protocol and binlog format.

$python binlog2sql/binlog2sql.py-h292.168.2.107-uroot-p123456-start-position=154-- stop-position=957-- start-file='mysql-bin.000002'UPDATE `testdb`.`hello` SET `id` = 3, `name` = 'David' WHERE `name` =' Will' LIMIT 1; # start 4 end 411 time 2018-06-17 22:54:13INSERT INTO `testdb`.`hello` (4, 'Frank'); # start 442 end 684 time 2018-06-17 22:55:47DELETE FROM `testdb`.hello` WHERE `id` = 2 AND `name` =' Bill' LIMIT # start 715 end 956 time 2018-06-17 22:56:44

Generate rollback sql:

$python binlog2sql/binlog2sql.py-- flashback-h292.168.2.107-uroot-p123456-- start-position=154-- stop-position=956-- start-file='mysql-bin.000002'INSERT INTO `testdb`.`hello` (`id`, `name`) VALUES (2, 'Bill'); # start 715 end 956 time 2018-06-17 22:56:44DELETE FROM `testdb`.`hello` WHERE `id` = 4 AND `name` =' Frank' LIMIT 1; # start 442 end 684 time 2018-06-17 22:55:47UPDATE `testdb`.`hello`SET `id` = 3, `name` = 'Will' WHERE `id` = 3 AND `name` =' David' LIMIT 1 # start 154end 411 time 2018-06-17 22:54:13

The practical principle of flashback is very simple: first, dump the binlog through the com-binlog-dump command of the MySQL replication protocol, then parse the binlog according to the format specification of binlog, convert the binlog into SQL, and then convert these SQL into the SQL of reverse logic, and finally execute them in reverse order.

Parsing binlog by Java

The binlog2sql above actually relies on the python-mysql-replication library, which is the Python library, at the bottom. If you want to use Java to parse binlog, you can use the mysql-binlog-connector-java (github) library. At present, open source CDC tools, such as Zendesk maxwell, Redhat debezium, LinkedIn Databus and so on, rely on mysql-binlog-connector-java or its predecessor open-replicator at the bottom. The sample code for using mysql-binlog-connector-java is as follows:

BinaryLogClient client = new BinaryLogClient ("192.168.2.107", 3306, "root", "123456"); client.setBinlogFilename ("mysql-bin.000001"); client.setBinlogPosition (4); client.setBlocking (false); client.registerEventListener (event-> {System.out.println (event);}); client.connect ()

Output (omit part):

... Event {header=EventHeaderV4 {timestamp=1529247253000, eventType=TABLE_MAP, serverId=1, headerLength=19, dataLength=34, nextPosition=346, flags=0}, data=TableMapEventData {tableId=110, database='testdb', table='hello', columnTypes=8, 15, columnMetadata=0, 40, columnNullability= {1} Event {header=EventHeaderV4 {timestamp=1529247253000, eventType=EXT_UPDATE_ROWS, serverId=1, headerLength=19, dataLength=46, nextPosition=411, flags=0}, data=UpdateRowsEventData {tableId=110, includedColumnsBeforeUpdate= {0, 1}, includedColumns= {0, 1}, rows= [{before= [3, Will], after= [3, David]}]}}... Event {header=EventHeaderV4 {timestamp=1529247347000, eventType=TABLE_MAP, serverId=1, headerLength=19 DataLength=34, nextPosition=634, flags=0}, data=TableMapEventData {tableId=110, database='testdb', table='hello', columnTypes=8, 15, columnMetadata=0, 40, columnNullability= {1} Event {header=EventHeaderV4 {timestamp=1529247347000, eventType=EXT_WRITE_ROWS, serverId=1, headerLength=19, dataLength=31, nextPosition=684, flags=0}, data=WriteRowsEventData {tableId=110, includedColumns= {0, 1}, rows= [[4, Frank]]}}... Event {header=EventHeaderV4 {timestamp=1529247404000, eventType=TABLE_MAP, serverId=1, headerLength=19, dataLength=34, nextPosition=907, flags=0}, data=TableMapEventData {tableId=110, database='testdb', table='hello', columnTypes=8, 15, columnMetadata=0, 40 ColumnNullability= {1} Event {header=EventHeaderV4 {timestamp=1529247404000, eventType=EXT_DELETE_ROWS, serverId=1, headerLength=19, dataLength=30, nextPosition=956, flags=0}, data=DeleteRowsEventData {tableId=110, includedColumns= {0, 1}, rows= [[2, Bill]]}}

After reading the article on how to use binlog to recover or flash database data in MySQL, what do you think? If you want to know more about it, you can continue to follow our industry information section.

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.