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

原标题：如何在不兼容的DDL命令下修复MySQL复制

【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';

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

责任编辑：