Shulou(Shulou.com)06/01 Report--

MySQL supports replication to a later version of slave, which allows us to easily upgrade MySQL settings to a new version by upgrading the slave and pointing to the application. But if it is not supported or if the application performs better on older versions of MySQL, we need to downgrade to improve slave performance.

The MySQL manual indicates that row-based replication can be used to copy to a lower version, provided that the DDL that is not replicated is not compatible with the slave server. One of the incompatible commands is a new feature in MySQL 5.7. version 5.6 is not available:

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

Executing this command interrupts replication. Here is an example of a slave who was destroyed in a non-GTID replication:

* * 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-0000005723 Auto_Position: 01 row in set (0.00 sec)

Skipping this statement does not resume replication:

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

Repair non-GTID replication

When checking the slave status, the replication is still not repaired. To fix it, you must manually jump to the next binary log location. The currently executed binary logs (Relay_Master_Log_File) and location (Exec_Master_Log_Pos) are mysql-bin.000002 and 36174373, respectively. We can use MySqLBiLoSQL on the host to determine the next location:

Mysqlbinlog-v-- base64-output=DECODE-ROWS-- start-position=36174373 / ssd/sandboxes/msb_5_7_23/data/mysql-bin.000002 | head-n 30max / 50530 SET @ @ SESSION.PSEUDONG SLAVEMED models 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= 'ANONYMOUSAccording to server id # 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 timespot 1537279420 "session. SET @ @ session.foreign_key_checks=1, @ @ session.sql_auto_is_null=0, @ @ session.unique_checks=1, @ @ session.session .sqlchocolate modeboxes 1436549152, SET @ @ session.session .autoincrementalization offsetbacks @ @ session.auto_increment_increment=1, @ @ session .autoincrementalization offsetbacks @ @ session. Session. Session. SET @ @ session.lctimestamp timekeeper namespace namespace password 22:03:40 server id 1 end_log_pos 36174686 CRC32 0x86756b3f Anonymous_GTID last_committed=19275 sequence_number=19278 rbrachionlyyesThen 50718 SET TRANSACTION ISOLATION LEVEL READ codes are available in the database database at set @ @ session.collationalization databaseDeFAULT'* 3A2EB9C80F7239A4DE3933AE266DB76A7846BCB8 # 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 ash ash at 36174760" 180918 22:03:40 server id 1 end_log_pos 36174819 CRC32 0x48054daf Table_map: `sbtest`.`sbtest1` mapped to number 226

Based on the above output, the next binary log location is 36174621. To repair slave, run:

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

Verify that the slave thread is now executing: 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: 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 please * redacted * * Auto_Position: 0

In order to make the master and slave consistent, execute a compatible query on the slave.

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

GTID replication

For GTID replication, in addition to injecting an empty transaction into the offensive statement, you need to skip it using the non-GTID solution provided above. Once run, flip it back to GTID.

Here is an example of an interrupted GTID slave:

Mysql > SHOW SLAVE STATUS\ gateway * 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: 86400 Masterpiece Bindl Timestamp: 180918 22:32:28Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set: 00005723-0000-0000-0000-0000005723 7403Auto_Position 2280-8530Executed_Gtid_Set: 00005723-0000-0000-0000-0000005723 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-0000005723 sec 1-7403 | +-+-+ 1 row in set

Since the last location executed is 7403, you need to create an empty transaction for the violation sequence 7404.

STOP SLAVE;SET GTID_NEXT='00005723-0000-0000-0000-0000005723 purse 7404 setting GTID_NEXT=AUTOMATIC;START SLAVE

Note: if you enable MTS, you can also obtain GTID violation coordinates from the display Last_SQL_Error of SHOW SLAVE STATUS\ G.

The next step is to find the next binary log location. The currently executed binary logs (Relay_Master_Log_File) and location (Exec_Master_Log_Pos) are mysql-bin.000003 and 12468343, respectively. We can use MySqLBiLoSQL on the host again to determine the next location:

Mysqlbinlog-v-- base64-output=DECODE-ROWS-- start-position=12468343 / ssd/sandboxes/msb_5_7_23/data/mysql-bin.000003 | head-n 30max / 50530 SET @ @ SESSION.PSEUDONG SLAVEMED models 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-0000005723 purse 7404 purge 7404" pancake "server id # at 12468408" 180918 22:32:19 server id 1 end_log_pos 12468591 TIMESTAMPPlease 1537281139 purge @ session. SET @ @ session.foreign_key_checks=1, @ @ session.sql_auto_is_null=0, @ @ session.unique_checks=1, @ @ session.session .sqlchocolate modeboxes 1436549152, SET @ @ session.session .autoincrementalization offsetbacks @ @ session.auto_increment_increment=1, @ @ session .autoincrementalization offsetbacks @ @ session. Session. Session. SET @ @ session.lcroomtimestamps namespace namespaces.session.session.collection databases database names defaults were checked by other USER 'testuser'@'localhost' IDENTIFIED WITH' mysql_native_password' AS'* 3A2EB9C80F7239A4DE3933AE266DB76A7846BCB8 purposesBCB8 purveyors at 1246859118,180918 server id 1 end_log_pos 1 end_log_pos 12468656 rbricks onlyyesbalance balance 50718 SET TRANSACTION ISOLATION LEVEL READ COMITTEDbands SET @ @ SESSION.GTID_NEXT= '00005723-0000-0000-0000-0000005723 at at 12468656 18918 22:32:19 server id 1 end_log_pos 12468730 CRC32 0x76b5ea6c Query thread_id=97 exec_time=0 error_code=0SET timespads 1537281139 pedigree server id 1 end_log_pos 12468789 CRC32 0x48f0ba6d Table_map: `sbtest`.`sbtest8` mapped to number 115

The next binary log location is 36174621. Repair slave station, running

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

Notice that I added MaskAutoPosil=0 above, and now disable GTID replication. You can run SHOW SLAVE STATUS\ G to make sure that MySQL is working well:

Mysql > SHOW SLAVE STATUS\ gateway * 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-0000005723 7405-264930Executed_Gtid_Set: 00005723-0000-0000-0000-0000005723 virtual 1-14947Auto_Position: 0

Because it is working well, you can now resume GTID replication:

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

Finally, in order to make the master and slave consistent, execute a compatible query on the slave.

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

In this article, I shared how to fix replication when replication is interrupted due to copying incompatible commands to slaves. If there are other incompatible commands, you are welcome to comment below.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.