"20180507" MySQL master-slave online modification changed from statement to row format from library Binlog format

Demand

Within the company there are dozens of sets of MySQL master-slave instances based on traditional replication, and the Binlog format is statement format. After taking over these MySQL master and slave instances, you have thought about changing the Binlog format to the row format. And this time it's because we elk. A third-party tool needs to parse and listen for binlog information, and can only parse the row format Binlog, taking the opportunity to change the Binlog format of the company part of the MySQL master-slave copy instance to row format.

Row and statement

1. Row format
   • Advantages: It is possible to fully guarantee the consistency of the master-slave data, and will not occur because the use of MySQL's own function in SQL results in inconsistent data. For example, when using the now () function, it is possible that the time-lapse data is inconsistent due to the delay from the library. The problem has been encountered on the line.
   • Disadvantage: It will consume a larger disk space and disk IO; Another important problem is that because the row format is modified on a per-line basis, if you perform an update in master to modify 5000 rows of data, then slave will perform 5,000 modifications to the data. Then this leads to a more serious master-slave delay. (because we are using MySQL5.6 on line, is schema-based parallel replication, and slave hardware resources are worse than master)
2. Statement format
   • Advantage: It consumes less disk storage and IO.
   • Disadvantage: Data inconsistency may result, and data inconsistency may occur when doing binlog-based recovery.

The difficulty of Binlog format change

Although Binlog changes can be modified online, because there are many long links on MySQL master, even if you change dynamically, long links are written and modified in the old Binlog format. In the beginning there are two scenarios:

• Restart Master. However, the online business cannot be interrupted, so modifications cannot be made.
• Kill all the long links. But because of the long link too many, one to kill the words are really time-consuming and experience, so also not be recognized.

Solution Solutions

Finally return to the demand itself, about our needs is the need to parse Binlog format is the row format, so our final solution is to modify the slave above the binlog format of the row format. (the Log_slave_updates parameter must be open above slave.) Otherwise, the SQL that master passes through Binlog to slave is not recorded in slave local Binlog)

• However, it is important to note that after the modifications are completed, you need to restart replication on the slave above. That is, stop slave,start slave. Otherwise, it will not take effect.
• There is also a need to note that when the slave above has been modified to the row format, this time in the slave binlog format modified to statement format, the copy will be error, even if the restart slave will be an error.

"20180507" MySQL master-slave online modification changed from statement to row format from library Binlog format