技术开发 频道
IT168首页 > 技术开发 > 技术开发技术 > 正文

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

作者：田晓旭 编辑： 田晓旭 2018-10-15 10:42 IT168网站 原创
分享

　　【IT168 技术】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 event

　　Master_Host: 127.0.0.1

　　Master_User: repl

　　Master_Port: 5723

　　Connect_Retry: 60

　　Master_Log_File: mysql-bin.000002

　　Read_Master_Log_Pos: 36915649

　　Relay_Log_File: mysql_sandbox5641-relay-bin.000006

　　Relay_Log_Pos: 36174552

　　Relay_Master_Log_File: mysql-bin.000002

　　Slave_IO_Running: Yes

　　Slave_SQL_Running: No

　　*** redacted ***

　　Last_Errno: 1064

　　Last_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: 0

　　Exec_Master_Log_Pos: 36174373

　　Relay_Log_Space: 36916179

　　Until_Condition: None

　　Until_Log_File:

　　Until_Log_Pos: 0

　　Master_SSL_Allowed: No

　　*** redacted ***

　　Seconds_Behind_Master: NULL

　　Master_SSL_Verify_Server_Cert: No

　　Last_IO_Errno: 0

　　Last_IO_Error:

　　Last_SQL_Errno: 1064

　　Last_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: 1

　　Master_UUID: 00005723-0000-0000-0000-000000005723

　　*** redacted ***

　　Last_IO_Error_Timestamp:

　　Last_SQL_Error_Timestamp: 180918 22:03:40

　　*** redacted ***

　　Auto_Position: 0

　　1 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=no

　　SET @@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=0

　　SET 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=0

　　SET 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 event

　　Master_Host: 127.0.0.1

　　Master_User: repl

　　Master_Port: 5723

　　Connect_Retry: 60

　　Master_Log_File: mysql-bin.000002

　　Read_Master_Log_Pos: 306841423

　　Relay_Log_File: mysql_sandbox5641-relay-bin.000002

　　Relay_Log_Pos: 190785290

　　Relay_Master_Log_File: mysql-bin.000002

　　Slave_IO_Running: Yes

　　Slave_SQL_Running: Yes

　　*** redacted ***

　　Last_Errno: 0

　　Last_Error:

　　Skip_Counter: 0

　　Exec_Master_Log_Pos: 226959625

　　Relay_Log_Space: 270667273

　　Until_Condition: None

　　Until_Log_File:

　　Until_Log_Pos: 0

　　Master_SSL_Allowed: No

　　*** redacted ***

　　Seconds_Behind_Master: 383

　　Master_SSL_Verify_Server_Cert: No

　　Last_IO_Errno: 0

　　Last_IO_Error:

　　Last_SQL_Errno: 0

　　Last_SQL_Error:

　　Replicate_Ignore_Server_Ids:

　　Master_Server_Id: 1

　　Master_UUID: 00005723-0000-0000-0000-000000005723

　　Master_Info_File: /ssd/sandboxes/msb_5_6_41/data/master.info

　　SQL_Delay: 0

　　SQL_Remaining_Delay: NULL

　　Slave_SQL_Running_State: Opening tables

　　Master_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的例子：

　　由于执行的最后一个位置是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来确定下一个位置：

　　下一个二进制日志位置是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运行良好：

　　因为运行良好，现在可以恢复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';

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

0
相关文章
    盛拓传媒简介 关于IT168 广告服务 使用条款 投稿指南 联系我们
    北京盛拓优讯信息技术有限公司. 版权所有 中华人民共和国增值电信业务经营许可证 编号：京B2-20170206 北京市公安局海淀分局网监中心备案编号：11010802020118
    广播电视节目制作经营许可证:(京) 字第25315号 信息系统安全等级保护备案：11010813655-00001 京公网安备11010802020118号
    违法和不良信息举报电话 :010-58859066,18101376593,shengtuo20@it168.com