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.
Topics:
MySQL
Technical Track
Open Source
Myrocks
Percona
RocksDB
