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

2025-04-14 技术教程

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:WaitingformastertosendeventMaster_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'YouhaveanerrorinyourSQLsyntax;checkthemanualthatcorrespondstoyourMySQLserverversionfortherightsyntaxtousenear'IDENTIFIEDWITH'mysql_native_password'AS'*3A2EB9C80F7239A4DE3933AE266DB76A784'atline1'onquery.Defaultdatabase:''.Query:'ALTERUSER'testuser'@'localhost'IDENTIFIEDWITH'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'YouhaveanerrorinyourSQLsyntax;checkthemanualthatcorrespondstoyourMySQLserverversionfortherightsyntaxtousenear'IDENTIFIEDWITH'mysql_native_password'AS'*3A2EB9C80F7239A4DE3933AE266DB76A784'atline1'onquery.Defaultdatabase:''.Query:'ALTERUSER'testuser'@'localhost'IDENTIFIEDWITH'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:18091822:03:40***redacted***Auto_Position:01rowinset(0.00sec)

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

mysql>STOPSLAVE;QueryOK,0rowsaffected(0.02sec)mysql>SETGLOBALsql_slave_skip_counter=1;QueryOK,0rowsaffected(0.00sec)mysql>STARTSLAVE;QueryOK,0rowsaffected(0.01sec)mysql>SHOWSLAVESTATUS\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-n30/*!50530SET@@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!50003SET@OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER/*!*/;#at36174373#18091822:03:40serverid1end_log_pos36174438CRC320xc7e1e553Anonymous_GTIDlast_committed=19273sequence_number=19277rbr_only=noSET@@SESSION.GTID_NEXT='ANONYMOUS'/*!*/;#at36174438#18091822:03:40serverid1end_log_pos36174621CRC320x2e5bb235Querythread_id=563exec_time=0error_code=0SETTIMESTAMP=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/*!*/;/*!\Clatin1*//*!*/;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/*!*/;ALTERUSER'testuser'@'localhost'IDENTIFIEDWITH'mysql_native_password'AS'*3A2EB9C80F7239A4DE3933AE266DB76A7846BCB8'/*!*/;#at36174621#18091822:03:40serverid1end_log_pos36174686CRC320x86756b3fAnonymous_GTIDlast_committed=19275sequence_number=19278rbr_only=yes/*!50718SETTRANSACTIONISOLATIONLEVELREADCOMMITTED*//*!*/;SET@@SESSION.GTID_NEXT='ANONYMOUS'/*!*/;#at36174686#18091822:03:40serverid1end_log_pos36174760CRC320x30e663f9Querythread_id=529exec_time=0error_code=0SETTIMESTAMP=1537279420/*!*/;BEGIN/*!*/;#at36174760#18091822:03:40serverid1end_log_pos36174819CRC320x48054dafTable_map:`sbtest`.`sbtest1`mappedtonumber226

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

STOPSLAVE;CHANGEMASTERTOMASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=36174621;STARTSLAVE;

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

Slave_IO_State:WaitingformastertosendeventMaster_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:OpeningtablesMaster_Retry_Count:86400***redacted***Auto_Position:0

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

SETSESSIONsql_log_bin=0;GRANTUSAGEON*.*TO'testuser'@'localhost'IDENTIFIEDBY'testuser';

GTID复制

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

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

mysql>SHOWSLAVESTATUS\G***************************1.row***************************Slave_IO_State:WaitingformastertosendeventMaster_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'YouhaveanerrorinyourSQLsyntax;checkthemanualthatcorrespondstoyourMySQLserverversionfortherightsyntaxtousenear'IDENTIFIEDWITH'mysql_native_password'AS'*3A2EB9C80F7239A4DE3933AE266DB76A784'atline1'onquery.Defaultdatabase:''.Query:'ALTERUSER'testuser'@'localhost'IDENTIFIEDWITH'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'YouhaveanerrorinyourSQLsyntax;checkthemanualthatcorrespondstoyourMySQLserverversionfortherightsyntaxtousenear'IDENTIFIEDWITH'mysql_native_password'AS'*3A2EB9C80F7239A4DE3933AE266DB76A784'atline1'onquery.Defaultdatabase:''.Query:'ALTERUSER'testuser'@'localhost'IDENTIFIEDWITH'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:18091822: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:11rowinset(0.00sec)mysql>SHOWGLOBALVARIABLESLIKE'gtid_executed';+---------------+---------------------------------------------+|Variable_name|Value|+---------------+---------------------------------------------+|gtid_executed|00005723-0000-0000-0000-000000005723:1-7403|+---------------+---------------------------------------------+1rowinset(0.00sec)

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

STOPSLAVE;SETGTID_NEXT='00005723-0000-0000-0000-000000005723:7404';BEGIN;COMMIT;SETGTID_NEXT=AUTOMATIC;STARTSLAVE;

注意：如果您启用了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-n30/*!50530SET@@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!50003SET@OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER/*!*/;#at12468343#18091822:32:19serverid1end_log_pos12468408CRC320x259ee085GTIDlast_committed=7400sequence_number=7404rbr_only=noSET@@SESSION.GTID_NEXT='00005723-0000-0000-0000-000000005723:7404'/*!*/;#at12468408#18091822:32:19serverid1end_log_pos12468591CRC320xb349ad80Querythread_id=142exec_time=0error_code=0SETTIMESTAMP=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/*!*/;/*!\Clatin1*//*!*/;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/*!*/;ALTERUSER'testuser'@'localhost'IDENTIFIEDWITH'mysql_native_password'AS'*3A2EB9C80F7239A4DE3933AE266DB76A7846BCB8'/*!*/;#at12468591#18091822:32:19serverid1end_log_pos12468656CRC320xb2019f3fGTIDlast_committed=7400sequence_number=7405rbr_only=yes/*!50718SETTRANSACTIONISOLATIONLEVELREADCOMMITTED*//*!*/;SET@@SESSION.GTID_NEXT='00005723-0000-0000-0000-000000005723:7405'/*!*/;#at12468656#18091822:32:19serverid1end_log_pos12468730CRC320x76b5ea6cQuerythread_id=97exec_time=0error_code=0SETTIMESTAMP=1537281139/*!*/;BEGIN/*!*/;#at12468730#18091822:32:19serverid1end_log_pos12468789CRC320x48f0ba6dTable_map:`sbtest`.`sbtest8`mappedtonumber115

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

STOPSLAVE;CHANGEMASTERTOMASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=12468591,MASTER_AUTO_POSITION=0;STARTSLAVE;

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

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

STOPSLAVE;CHANGEMASTERTOMASTER_AUTO_POSITION=1;STARTSLAVE;

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

SETSESSIONsql_log_bin=0;GRANTUSAGEON*.*TO'testuser'@'localhost'IDENTIFIEDBY'testuser';

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