如何在不兼容的DDL命令后修复MySQL复制

MySQL支持复制到一个更高版本的slave，这允许我们通过升级从机和指向应用程序来轻松地将MySQL设置升级到一个新版本。但如果不支持或者是应用程序在旧版本的MySQL上表现得更好，我们就需要通过降级来提升slave性能。

MySQL手册表示基于行的复制可以用于复制到较低版本，前提是没有复制的DDL与从服务器不兼容。其中有一个不兼容命令是MySQL 5.7中的新特性，5.6上版本不可用：

ALTER USER 'testuser'@'localhost' IDENTIFIED BY 'testuser';

执行该命令会中断复制。这里是一个在非GTID复制中被破坏的奴隶的例子：

*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 127.0.0.1Master_User: replMaster_Port: 5723Connect_Retry: 60Master_Log_File: mysql-bin.000002Read_Master_Log_Pos: 36915649Relay_Log_File: mysql_sandbox5641-relay-bin.000006Relay_Log_Pos: 36174552Relay_Master_Log_File: mysql-bin.000002Slave_IO_Running: YesSlave_SQL_Running: No*** redacted ***Last_Errno: 1064Last_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED WITH 'mysql_native_password' AS '*3A2EB9C80F7239A4DE3933AE266DB76A784' at line 1' on query. Default database: ''. Query: 'ALTER USER 'testuser'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*3A2EB9C80F7239A4DE3933AE266DB76A7846BCB8''Skip_Counter: 0Exec_Master_Log_Pos: 36174373Relay_Log_Space: 36916179Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: No*** redacted ***Seconds_Behind_Master: NULLMaster_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 1064Last_SQL_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED WITH 'mysql_native_password' AS '*3A2EB9C80F7239A4DE3933AE266DB76A784' at line 1' on query. Default database: ''. Query: 'ALTER USER 'testuser'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*3A2EB9C80F7239A4DE3933AE266DB76A7846BCB8''Replicate_Ignore_Server_Ids:Master_Server_Id: 1Master_UUID: 00005723-0000-0000-0000-000000005723*** redacted ***Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp: 180918 22:03:40*** redacted ***Auto_Position: 01 row in set (0.00 sec)

跳过该语句不会恢复复制：

mysql> STOP SLAVE;Query OK, 0 rows affected (0.02 sec)mysql> SET GLOBAL sql_slave_skip_counter=1;Query OK, 0 rows affected (0.00 sec)mysql> START SLAVE;Query OK, 0 rows affected (0.01 sec)mysql> SHOW SLAVE STATUS\G

修复非GTID复制

当检查从机状态时，复制仍然未修复。要修复它，必须手动跳转到下一个二进制日志位置。当前执行的二进制日志(Relay_Master_Log_File)和位置(Exec_Master_Log_Pos)分别是mysql-bin.000002和36174373。我们可以在主机上使用MySqLBiLoSQL来确定下一个位置：

mysqlbinlog -v --base64-output=DECODE-ROWS --start-position=36174373 /ssd/sandboxes/msb_5_7_23/data/mysql-bin.000002 | head -n 30/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;# at 36174373#180918 22:03:40 server id 1 end_log_pos 36174438 CRC32 0xc7e1e553 Anonymous_GTID last_committed=19273 sequence_number=19277 rbr_only=noSET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;# at 36174438#180918 22:03:40 server id 1 end_log_pos 36174621 CRC32 0x2e5bb235 Query thread_id=563 exec_time=0 error_code=0SET TIMESTAMP=1537279420/*!*/;SET @@session.pseudo_thread_id=563/*!*/;SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;SET @@session.sql_mode=1436549152/*!*/;SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;/*!\C latin1 *//*!*/;SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;SET @@session.lc_time_names=0/*!*/;SET @@session.collation_database=DEFAULT/*!*/;ALTER USER 'testuser'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*3A2EB9C80F7239A4DE3933AE266DB76A7846BCB8'/*!*/;# at 36174621#180918 22:03:40 server id 1 end_log_pos 36174686 CRC32 0x86756b3f Anonymous_GTID last_committed=19275 sequence_number=19278 rbr_only=yes/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;# at 36174686#180918 22:03:40 server id 1 end_log_pos 36174760 CRC32 0x30e663f9 Query thread_id=529 exec_time=0 error_code=0SET TIMESTAMP=1537279420/*!*/;BEGIN/*!*/;# at 36174760#180918 22:03:40 server id 1 end_log_pos 36174819 CRC32 0x48054daf Table_map: `sbtest`.`sbtest1` mapped to number 226

基于上述输出，下一个二进制日志位置为36174621。修复slave，运行：

STOP SLAVE;CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=36174621;START SLAVE;

验证从属线程现在是否正在执行：SHOW SLAVE STATUS\G

Slave_IO_State: Waiting for master to send eventMaster_Host: 127.0.0.1Master_User: replMaster_Port: 5723Connect_Retry: 60Master_Log_File: mysql-bin.000002Read_Master_Log_Pos: 306841423Relay_Log_File: mysql_sandbox5641-relay-bin.000002Relay_Log_Pos: 190785290Relay_Master_Log_File: mysql-bin.000002Slave_IO_Running: YesSlave_SQL_Running: Yes*** redacted ***Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 226959625Relay_Log_Space: 270667273Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: No*** redacted ***Seconds_Behind_Master: 383Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 1Master_UUID: 00005723-0000-0000-0000-000000005723Master_Info_File: /ssd/sandboxes/msb_5_6_41/data/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Opening tablesMaster_Retry_Count: 86400*** redacted ***Auto_Position: 0

为了使主从一致，对从属执行兼容查询。

SET SESSION sql_log_bin = 0;GRANT USAGE ON *.* TO 'testuser'@'localhost' IDENTIFIED BY 'testuser';

GTID复制

对于GTID复制，除了为冒犯语句注入空事务之外，还需要使用上面提供的非GTID解决方案跳过它。一旦运行，将其翻转回到GTID。

下面是一个中断的GTID slave的例子：

mysql> SHOW SLAVE STATUS\G*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 127.0.0.1Master_User: replMaster_Port: 5723Connect_Retry: 60Master_Log_File: mysql-bin.000003Read_Master_Log_Pos: 14364967Relay_Log_File: mysql_sandbox5641-relay-bin.000002Relay_Log_Pos: 8630318Relay_Master_Log_File: mysql-bin.000003Slave_IO_Running: YesSlave_SQL_Running: No*** redacted ***Last_Errno: 1064Last_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED WITH 'mysql_native_password' AS '*3A2EB9C80F7239A4DE3933AE266DB76A784' at line 1' on query. Default database: ''. Query: 'ALTER USER 'testuser'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*3A2EB9C80F7239A4DE3933AE266DB76A7846BCB8''Skip_Counter: 0Exec_Master_Log_Pos: 12468343Relay_Log_Space: 10527158Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: No*** redacted ***Seconds_Behind_Master: NULLMaster_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 1064Last_SQL_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED WITH 'mysql_native_password' AS '*3A2EB9C80F7239A4DE3933AE266DB76A784' at line 1' on query. Default database: ''. Query: 'ALTER USER 'testuser'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*3A2EB9C80F7239A4DE3933AE266DB76A7846BCB8''Replicate_Ignore_Server_Ids:Master_Server_Id: 1Master_UUID: 00005723-0000-0000-0000-000000005723Master_Info_File: /ssd/sandboxes/msb_5_6_41/data/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State:Master_Retry_Count: 86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp: 180918 22:32:28Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set: 00005723-0000-0000-0000-000000005723:2280-8530Executed_Gtid_Set: 00005723-0000-0000-0000-000000005723:1-7403Auto_Position: 11 row in set (0.00 sec)mysql> SHOW GLOBAL VARIABLES LIKE 'gtid_executed';+---------------+---------------------------------------------+| Variable_name | Value |+---------------+---------------------------------------------+| gtid_executed | 00005723-0000-0000-0000-000000005723:1-7403 |+---------------+---------------------------------------------+1 row in set (0.00 sec)

由于执行的最后一个位置是7403，所以需要为违规序列7404创建一个空事务。

STOP SLAVE;SET GTID_NEXT='00005723-0000-0000-0000-000000005723:7404';BEGIN;COMMIT;SET GTID_NEXT=AUTOMATIC;START SLAVE;

注意：如果您启用了MTS，您也可以从显示Last_SQL_Error of SHOW SLAVE STATUS\G获得违反GTID坐标。

下一步是找到下一个二进制日志位置。当前执行的二进制日志(Relay_Master_Log_File)和位置(Exec_Master_Log_Pos)分别是mysql-bin.000003和12468343。我们可以再次在主机上使用MySqLBiLoSQL来确定下一个位置：

mysqlbinlog -v --base64-output=DECODE-ROWS --start-position=12468343 /ssd/sandboxes/msb_5_7_23/data/mysql-bin.000003 | head -n 30/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;# at 12468343#180918 22:32:19 server id 1 end_log_pos 12468408 CRC32 0x259ee085 GTID last_committed=7400 sequence_number=7404 rbr_only=noSET @@SESSION.GTID_NEXT= '00005723-0000-0000-0000-000000005723:7404'/*!*/;# at 12468408#180918 22:32:19 server id 1 end_log_pos 12468591 CRC32 0xb349ad80 Query thread_id=142 exec_time=0 error_code=0SET TIMESTAMP=1537281139/*!*/;SET @@session.pseudo_thread_id=142/*!*/;SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;SET @@session.sql_mode=1436549152/*!*/;SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;/*!\C latin1 *//*!*/;SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;SET @@session.lc_time_names=0/*!*/;SET @@session.collation_database=DEFAULT/*!*/;ALTER USER 'testuser'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*3A2EB9C80F7239A4DE3933AE266DB76A7846BCB8'/*!*/;# at 12468591#180918 22:32:19 server id 1 end_log_pos 12468656 CRC32 0xb2019f3f GTID last_committed=7400 sequence_number=7405 rbr_only=yes/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;SET @@SESSION.GTID_NEXT= '00005723-0000-0000-0000-000000005723:7405'/*!*/;# at 12468656#180918 22:32:19 server id 1 end_log_pos 12468730 CRC32 0x76b5ea6c Query thread_id=97 exec_time=0 error_code=0SET TIMESTAMP=1537281139/*!*/;BEGIN/*!*/;# at 12468730#180918 22:32:19 server id 1 end_log_pos 12468789 CRC32 0x48f0ba6d Table_map: `sbtest`.`sbtest8` mapped to number 115

下一个二进制日志位置是36174621。修复从站，运行

STOP SLAVE;CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=12468591, MASTER_AUTO_POSITION=0;START SLAVE;

注意，我在上面添加MaskAutoPosil=0，现在禁用GTID复制。您可以运行SHOW SLAVE STATUS\G以确定MySQL运行良好：

mysql> SHOW SLAVE STATUS\G*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 127.0.0.1Master_User: replMaster_Port: 5723Connect_Retry: 60Master_Log_File: mysql-bin.000003Read_Master_Log_Pos: 446194575Relay_Log_File: mysql_sandbox5641-relay-bin.000002Relay_Log_Pos: 12704248Relay_Master_Log_File: mysql-bin.000003Slave_IO_Running: YesSlave_SQL_Running: Yes*** redacted ***Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 25172522Relay_Log_Space: 433726939Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: No*** redacted ***Seconds_Behind_Master: 2018Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 1Master_UUID: 00005723-0000-0000-0000-000000005723Master_Info_File: /ssd/sandboxes/msb_5_6_41/data/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Reading event from the relay logMaster_Retry_Count: 86400*** redacted ***Retrieved_Gtid_Set: 00005723-0000-0000-0000-000000005723:7405-264930Executed_Gtid_Set: 00005723-0000-0000-0000-000000005723:1-14947Auto_Position: 0

因为运行良好，现在可以恢复GTID复制：

STOP SLAVE;CHANGE MASTER TO MASTER_AUTO_POSITION=1;START SLAVE;

最后，为了使主从一致，对从属执行兼容查询。

SET SESSION sql_log_bin = 0;GRANT USAGE ON *.* TO 'testuser'@'localhost' IDENTIFIED BY 'testuser';

在本文中，我分享了如何修复由于向从属复制不兼容的命令而导致复制中断时的复制。如果有其它不兼容的命令，欢迎大家在下方评论。