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

The pt-online-schema-change tool relies on the trigger mechanism to implement the lock-free DDL of the table. Let's imagine that in the case of one master and one slave, there is a large table that needs to perform a DDL operation. In order to verify the length of execution of this operation, first use the pt-online-schema-change tool to execute changes on the slave library. Make sure there are no problems before making changes on the main library. Of course, session-level sql_log_bin=0 needs to be turned on before execution to avoid logging to binlog.

But we know from the official documents as follows:

Obviously, if the log format of binlog is row under the master-slave architecture, pt-online-schema-change, which depends on the trigger mechanism, will cause data loss. Because incremental data cannot be collected through triggers. That is, if the binlog based on the master library is in row format, the trigger of the slave library will not work. Because the changes made by the master library itself through the trigger have been written to the binlog synchronization to the slave library. If the trigger of the slave library also takes effect, there will be duplicates.

Summary: under the master-slave replication architecture, if the log format of the master library binlog is row, the pt-online-schema-change tool must be executed in the master database to avoid master-slave data inconsistencies. If the log format is statement, you can execute pt-online-schema-change in the slave database first and then in the master library. However, officials also recommend that the parameter binlog_format be set to row instead of statement to avoid master-slave data inconsistencies in some cases.

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.