"MySQL" simply implements data rollback via binary log (i)

First, preface

Yes, that's right, I've had water for a while, deep reflections on sending a few. During the previous period, the work item was asked to provide a capability to roll back data based on the operations batch, due to problems that could occur in bulk operations such as Excel. In the process of development in contact with the MySQL binary log, feel some harvest, record.

Ii. The concept of binary log

First we need to know what binary Log is (see details):

Binary Log (binary), which contains "events" that describe changes to the database, such as creating a table or altering the table's data. If you take a row-based log, it can also contain statement events that have changed (for example, delete events with no corresponding rows).

This means that your operation of the database, including INSERT, delete, Crud,binlog (the command abbreviation) will be included, then, if we can parse (because from the Binlog name can be known that this is a binary file, is not human readable) out of its content, you can reverse the execution of the statement, the operation of the wrong data recovery.

This is also one of the purposes of Binlog: data recovery

Another use for Binlog is for master-slave replication. We all know that in the current big data background, the conventional single database has been unable to meet the demand for access, so there is a database cluster: the main database to write operations from the database to read operations, thereby reducing the access pressure of the database, and in order to ensure that the content of the database is consistent, it is necessary to use Binlog to ensure that Such as: Here is not specifically unfolded.

Third, through the Shell view binary Log

After understanding the concept of binlog, let's look at Binlog through the shell.

First, add the following configuration to the MY.CNF:

[mysqld]log-bin=mysql-binserver_id=1 #避免和slave机器重复log_bin_basename=xxx 可选log_bin_index=xxx 可选

Restart MySQL after saving.

Enter MySQL Command:

  mysql> show variables like '%log_bin% '; view Binglog path +---------------------------------+- --------------------------------------+| variable_name | Value |+---------------------------------+---------------------------------------+| Log_bin | On | | Log_bin_basename | /usr/local/mysql/data/mysql-bin | | Log_bin_index | /usr/local/mysql/data/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | Sql_log_bin | On |+---------------------------------+---------------------------------------+  /pre>

• Log_bin:on says binary Log is turned on.
• Log_bin_basename:binary Log's basic file name, which can be specified in MY.CNF
• The index file of the Log_bin_index:binlog file, which can be specified in MY.CNF
  
  

mysql> show binary logs;+------------------+-----------+| Log_name         | File_size |+------------------+-----------+| mysql-bin.000001 |   9309624 || mysql-bin.000002 |   9008629 || mysql-bin.000003 |    229080 || mysql-bin.000004 |  15410010 || mysql-bin.000005 |       177 || mysql-bin.000006 |   5798399 || mysql-bin.000007 |       177 |+------------------+-----------+

Displays all binary log files and file sizes for the current database

Once you know this, exit the MySQL Command and view it in the shell:

> sudo -u mysql mysqlbinlog /usr/local/mysql/data/mysql-bin.000030

Because my/usr/local/mysql/data in the installation of MySQL when the default only to the MySQL user, so add-u switch to MySQL.

You can now view the contents of the binary (truncated section):

  # at 1341475#180416 15:58:45 server ID 1 end_log_pos 1341582 CRC32 0x0ca6c030 table_map: ' user-  Center '. ' T_management_entity_role ' mapped to number 127# at 1341582#180416 15:58:45 server ID 1 end_log_pos 1341686 CRC32 0X33552CEF write_rows:table ID 127 flags:stmt_end_fbinlog ' tvfuwhmbaaaaawaaai54faaaah8aaaaaaaeadnnolxvzzxity2vudgvyabh0x21hbmfnzw1lbnrfzw50axr5x3jvbguadampdw8pdwepdxipehjgadyayac0a amaawdaaadaaaasaddapgw=tvfuwh4baaaaaaaaapz4faaaah8aaaaaaaeaagam// 8q8ikaaaarc3vix2vtcgxvewvlx2rlchqg5qch5beldxnjag9vbf93b3jrzxibmqibman6a2qzn6d7wan6a2qzn6d7wo8svtm= '/*!*/;# at 1341686#180416 15:58:45 Server ID 1 end_log_pos 1341717 CRC32 0x1fdc2123 Xid = 22495commit/*!*/;# at 1341717#180416 16:4 1:12 Server ID 1 end_log_pos 1341740 CRC32 0xca0bf05c stopset @ @SESSION. gtid_next= ' AUTOMATIC '/* Added by Mysqlbinlog *//*!*/;D elimiter; # End of Log file/*!50003 SET [Email protected]_co mpletion_type*/;/*!50530 SET @ @SESSION. pseudo_slave_mode=0*/;  

See here, feel binlog concrete inside is still non-human can read. If you want to know the secret, it looks like you should read the MySQL development manual.

Iv. Summary

The first part first records the entire operation process, the second part writes the concrete realization process. Thank you for your garden friends to watch, if there is a description of the wrong place welcome correction, with everyone to progress together!

"MySQL" simply implements data rollback via binary log (i)