如何在不相容的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 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的例子：

mysql> SHOW SLAVE STATUS\G
*************************** 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.000003
Read_Master_Log_Pos: 14364967
Relay_Log_File: mysql_sandbox5641-relay-bin.000002
Relay_Log_Pos: 8630318
Relay_Master_Log_File: mysql-bin.000003
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: 12468343
Relay_Log_Space: 10527158
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
Master_Info_File: /ssd/sandboxes/msb_5_6_41/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 180918 22:32:28
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 00005723-0000-0000-0000-000000005723:2280-8530
Executed_Gtid_Set: 00005723-0000-0000-0000-000000005723:1-7403
Auto_Position: 1
1 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=no
SET @@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=0
SET 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=0
SET 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 event
Master_Host: 127.0.0.1
Master_User: repl
Master_Port: 5723
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 446194575
Relay_Log_File: mysql_sandbox5641-relay-bin.000002
Relay_Log_Pos: 12704248
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
*** redacted ***
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 25172522
Relay_Log_Space: 433726939
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
*** redacted ***
Seconds_Behind_Master: 2018
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: Reading event from the relay log
Master_Retry_Count: 86400
*** redacted ***
Retrieved_Gtid_Set: 00005723-0000-0000-0000-000000005723:7405-264930
Executed_Gtid_Set: 00005723-0000-0000-0000-000000005723:1-14947
Auto_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';

在本文中，我分享瞭如何修復由於向從屬複製不相容的命令而導致複製中斷時的複製。如果有其它不相容的命令，歡迎大家在下方評論。