MySQL 5.7 Multi-threads replication operation tips
With support of multi-threads replication starting from MySQL 5.7, the operations on slave are slightly different from single-thread replication. Here is a list of some operation tips for the convenience of use as below:
1. Skip a statement for a specific channel.
Sometimes, we might find out that one of the channels stop replication due to some error, and we may want to skip the statement for that channel so that we can restart a slave for it. We need to be very careful not to skip the statement from the other channel, since the command SET GLOBAL sql_slave_skip_counter = N is for global. How can we make sure the global sql_slave_skip_counter is applied to a specific channel and not to the other channel? Here are the steps: 1.1: Stop all slaves by: stop slave;stop slave;1.2: Set up the count of statement to skip by: SET GLOBAL sql_slave_skip_counter = N;
SET GLOBAL sql_slave_skip_counter = 1;1.3: Start slave on the channel we want to skip the statement on. The command will use the setting for global sql_slave_skip_counter = 1 to skip one statement and start slave on that channel (for example 'main') by: starting slave for channel 'channel-name';
start slave for channel 'main';1.4: Start slave on all the other channels by: start slave;
start slave;
2. Check the status of replication with detailed messages in the table performance_schema.replication_applier_status_by_worker through select * from the table:
mysql> select * from performance_schema.replication_applier_status_by_worker; | CHANNEL_NAME | WORKER_ID | THREAD_ID | SERVICE_STATE | LAST_SEEN_TRANSACTION | LAST_ERROR_NUMBER | LAST_ERROR_MESSAGE | LAST_ERROR_TIMESTAMP | | metrics | 1 | 1784802 | ON | ANONYMOUS | 0 | | 0000-00-00 00:00:00 | | accounting | 1 | 1851760 | ON | ANONYMOUS | 0 | | 0000-00-00 00:00:00 | | main | 1 | NULL | OFF | ANONYMOUS | 1051 | Worker 0 failed executing transaction 'ANONYMOUS' at master log mysql-bin.019567, end_log_pos 163723076; Error 'Unknown table 'example.accounts'' on query. Default database: 'pythian'. Query: 'DROP TABLE `example`.`accounts` /* generated by server */' | 2018-02-14 23:57:52 | | log | 1 | 1784811 | ON | ANONYMOUS | 0 | | 0000-00-00 00:00:00 | mysql> select * from performance_schema.replication_applier_status_by_worker; | CHANNEL_NAME | WORKER_ID | THREAD_ID | SERVICE_STATE | LAST_SEEN_TRANSACTION | LAST_ERROR_NUMBER | LAST_ERROR_MESSAGE | LAST_ERROR_TIMESTAMP | | metrics | 1 | 1965646 | ON | ANONYMOUS | 0 | | 0000-00-00 00:00:00 | | accounting | 1 | 1965649 | ON | ANONYMOUS | 0 | | 0000-00-00 00:00:00 | | main | 1 | 1965633 | ON | ANONYMOUS | 0 | | 0000-00-00 00:00:00 | | log | 1 | 1965652 | ON | ANONYMOUS | 0 | | 0000-00-00 00:00:00 |
3. Check the status for a specific channel by: show slave status for channel 'channel-name'\G :
mysql> show slave status for channel 'main'\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: db-test-01.int.example.com Master_User: replicator Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.019567 Read_Master_Log_Pos: 869255591 Relay_Log_File: db-test-02-relay-bin-example.000572 Relay_Log_Pos: 45525401 Relay_Master_Log_File: mysql-bin.019567 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: test.sessions,test.metrics Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 869255591 Relay_Log_Space: 869256195 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 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: 4118338212 Master_UUID: b8cee5b1-3161-11e7-8109-3ca82a217b08 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: insight Master_TLS_Version:I hope this short list of tips helps you enjoy multi-threads replication.
