MySQL 5.7 Multi-threads replication operation tips

Written by Edwin Wang | Feb 16, 2018 5:00:00 AM

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.

MySQL Database Consulting

Looking to optimize your MySQL use?

View full post