MySQL 5.7 Multi-Source Replication

This post it to share a brief and precise information about MySQL 5.7 Multi Source Replication (configuration and monitoring) and not specifically for concepts of MySQL replication.

msr

There have been couple of third-party tools for multi-source replication in MySQL. But in MySQL57, its officially been included as one of the feature of MySQL.

A single source replication usually (with one master and 3 slaves) looks like the figure below:

ssr

 

For checking the complexity of multi-master, we configured 6 MySQL instances, with GTID enabled. (We can have multi-master feature with traditional file position based replication too.)

Below is the config file for a single instance. Rest 5 are all similar.

[mysqld]
datadir=/mysql/mysql6/data/
basedir=/usr
user=mysql
socket=/mysql/mysql6/data/mysql6.socket
binlog-format=MIXED
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
port=3316
server-id=16
log-bin=/mysql/mysql6/binlog/mysql6
log_error=/mysql/mysql6/log/error.log
slow_query_log=1
slow_query_log_file=/mysql/mysql6/log/slow.log
long_query_time=30

 

Configuring slave for multi-master.

On slave nrama12:3316

For every master we have to name a channel and hence the command “Change Master to

mysql> change master to master_host=’localhost’, master_port=3311, master_user=’repl’,master_password=’repl123#’ for channel ‘master1’;
mysql> change master to master_host=’localhost’, master_port=3312, master_user=’repl’,master_password=’repl123#’ for channel ‘master2’;
mysql> change master to master_host=’localhost’, master_port=3313, master_user=’repl’,master_password=’repl123#’ for channel ‘master3’;
mysql> change master to master_host=’localhost’, master_port=3314, master_user=’repl’,master_password=’repl123#’ for channel ‘master4’;
mysql> change master to master_host=’localhost’, master_port=3315, master_user=’repl’,master_password=’repl123#’ for channel ‘master5’;

Starting slave for multiple channels

mysql> start slave for channel ‘master1’;
mysql> start slave for channel ‘master2’;
mysql> start slave for channel ‘master3’;
mysql> start slave for channel ‘master4’;
mysql> start slave for channel ‘master5’;

Checking replication status

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: repl
Master_Port: 3311
Connect_Retry: 60
Master_Log_File: mysql1.000001
Read_Master_Log_Pos: 154
Relay_Log_File: nrama12-relay-bin-master1.000002
Relay_Log_Pos: 361
Relay_Master_Log_File: mysql1.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 578
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: 11
Master_UUID: 96720b8e-bda4-11e5-b8bc-080027173a36
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: master1
Master_TLS_Version:
*************************** 2. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: repl
Master_Port: 3312
Connect_Retry: 60
Master_Log_File: mysql2.000001
Read_Master_Log_Pos: 154
Relay_Log_File: nrama12-relay-bin-master2.000002
Relay_Log_Pos: 361
Relay_Master_Log_File: mysql2.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 578
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: 12
Master_UUID: f414740d-bdac-11e5-8b4d-080027173a36
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: master2
Master_TLS_Version:
*************************** 3. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: repl
Master_Port: 3313
Connect_Retry: 60
Master_Log_File: mysql3.000001
Read_Master_Log_Pos: 154
Relay_Log_File: nrama12-relay-bin-master3.000002
Relay_Log_Pos: 361
Relay_Master_Log_File: mysql3.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 578
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: 13
Master_UUID: 3f02b679-bdad-11e5-9125-080027173a36
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: master3
Master_TLS_Version:
*************************** 4. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: repl
Master_Port: 3314
Connect_Retry: 60
Master_Log_File: mysql4.000001
Read_Master_Log_Pos: 154
Relay_Log_File: nrama12-relay-bin-master4.000002
Relay_Log_Pos: 361
Relay_Master_Log_File: mysql4.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 578
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: 14
Master_UUID: 86bb2f78-be45-11e5-9b02-080027173a36
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: master4
Master_TLS_Version:
*************************** 5. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: repl
Master_Port: 3315
Connect_Retry: 60
Master_Log_File: mysql5.000001
Read_Master_Log_Pos: 154
Relay_Log_File: nrama12-relay-bin-master5.000002
Relay_Log_Pos: 361
Relay_Master_Log_File: mysql5.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 578
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: 15
Master_UUID: 891fbac6-be45-11e5-9c23-080027173a36
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: master5
Master_TLS_Version:
5 rows in set (0.00 sec)

5 rows were been returned. 1 row for 1 channel.

msr

For checking replication status for single channel

mysql> SHOW SLAVE STATUS FOR CHANNEL ‘master1’\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: repl
Master_Port: 3311
Connect_Retry: 60
Master_Log_File: mysql1.000001
Read_Master_Log_Pos: 33715130
Relay_Log_File: nrama12-relay-bin-master1.000010
Relay_Log_Pos: 357
Relay_Master_Log_File: mysql1.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 33715130
Relay_Log_Space: 777
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: 11
Master_UUID: 96720b8e-bda4-11e5-b8bc-080027173a36
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: 96720b8e-bda4-11e5-b8bc-080027173a36:1-100046
Executed_Gtid_Set: 96720b8e-bda4-11e5-b8bc-080027173a36:1-100046
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: master1
Master_TLS_Version:
1 row in set (0.02 sec)

Be default the name convention for relay log would be
hostname-relay-bin-channel_name.00001

In our case its
nrama12-relay-bin-master1.000003
where nrama12 is hostname and master1 is one of the channel name.

Lets make some changes on one of the master and analyze the behavior of slave.

On master1 nrama12:3311

[root@nrama12 mysql]# mysql -u root -S/mysql/mysql1/data/mysql1.socket
mysql> create database test1 ;
Query OK, 1 row affected (0.01 sec)
mysql> use test1
Database changed
mysql> CREATE TABLE test1 (
Title int(4) NOT NULL AUTO_INCREMENT,
Col1 char(10) DEFAULT NULL,
Col2 varchar(20) DEFAULT NULL,
Date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (Title)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
mysql> insert into test1(col1,col2) values (“raw”,”Raw123″);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test1(col1,col2) values (“raw”,”Raw123″);
Query OK, 1 row affected (0.01 sec)
mysql> insert into test1(col1,col2) values (“raw”,”Raw123″);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test1(col1,col2) values (“raw”,”Raw123″);
Query OK, 1 row affected (0.01 sec)
mysql> insert into test1(col1,col2) values (“raw”,”Raw123″);
Query OK, 1 row affected (0.01 sec)
mysql> insert into test1(col1,col2) values (“raw”,”Raw123″);
Query OK, 1 row affected (0.01 sec)
mysql> select * from test1 limit 3\G
*************************** 1. row ***************************
Title: 1
Col1: raw
Col2: Raw123
Date: 2016-03-20 03:30:00
*************************** 2. row ***************************
Title: 2
Col1: raw
Col2: Raw123
Date: 2016-03-20 03:30:04
*************************** 3. row ***************************
Title: 3
Col1: raw
Col2: Raw123
Date: 2016-03-20 03:30:05
3 rows in set (0.00 sec)
Checking slave on nrama12:3316
[root@nrama12 mysql]# mysql -u root -S/mysql/mysql6/data/mysql6.socket
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test1 |
+——————–+
5 rows in set (0.00 sec)
mysql> use test1
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from test1 limit 3\G
*************************** 1. row ***************************
Title: 1
Col1: raw
Col2: Raw123
Date: 2016-03-20 03:30:00
*************************** 2. row ***************************
Title: 2
Col1: raw
Col2: Raw123
Date: 2016-03-20 03:30:04
*************************** 3. row ***************************
Title: 3
Col1: raw
Col2: Raw123
Date: 2016-03-20 03:30:05
3 rows in set (0.00 sec)

The commands for stopping and starting slave, thread sql_thread and io_thread will be the same if we want to do any changes for all channels. Below are few examples:

For stopping all channels at once

mysql> stop slave;

For stopping particular thread for all channel

[root@nrama12 mysql]# mysql -u root -S/mysql/mysql6/data/mysql6.socket
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: localhost
Master_User: repl
Master_Port: 3311
Connect_Retry: 60
Master_Log_File: mysql1.000001
Read_Master_Log_Pos: 3335
Relay_Log_File: nrama12-relay-bin-master1.000003
Relay_Log_Pos: 3498
Relay_Master_Log_File: mysql1.000001
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 3335
Relay_Log_Space: 3922
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: NULL
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: 11
Master_UUID: 96720b8e-bda4-11e5-b8bc-080027173a36
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: 96720b8e-bda4-11e5-b8bc-080027173a36:1-11
Executed_Gtid_Set: 96720b8e-bda4-11e5-b8bc-080027173a36:1-11
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: master1
Master_TLS_Version:
*************************** 2. row ***************************
Slave_IO_State:
Master_Host: localhost
Master_User: repl
Master_Port: 3312
Connect_Retry: 60
Master_Log_File: mysql2.000001
Read_Master_Log_Pos: 154
Relay_Log_File: nrama12-relay-bin-master2.000003
Relay_Log_Pos: 317
Relay_Master_Log_File: mysql2.000001
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 741
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: NULL
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: 12
Master_UUID: f414740d-bdac-11e5-8b4d-080027173a36
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: 96720b8e-bda4-11e5-b8bc-080027173a36:1-11
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: master2
Master_TLS_Version:
*************************** 3. row ***************************
Slave_IO_State:
Master_Host: localhost
Master_User: repl
Master_Port: 3313
Connect_Retry: 60
Master_Log_File: mysql3.000001
Read_Master_Log_Pos: 154
Relay_Log_File: nrama12-relay-bin-master3.000003
Relay_Log_Pos: 317
Relay_Master_Log_File: mysql3.000001
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 741
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: NULL
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: 13
Master_UUID: 3f02b679-bdad-11e5-9125-080027173a36
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: 96720b8e-bda4-11e5-b8bc-080027173a36:1-11
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: master3
Master_TLS_Version:
*************************** 4. row ***************************
Slave_IO_State:
Master_Host: localhost
Master_User: repl
Master_Port: 3314
Connect_Retry: 60
Master_Log_File: mysql4.000001
Read_Master_Log_Pos: 154
Relay_Log_File: nrama12-relay-bin-master4.000003
Relay_Log_Pos: 317
Relay_Master_Log_File: mysql4.000001
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 741
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: NULL
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: 14
Master_UUID: 86bb2f78-be45-11e5-9b02-080027173a36
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: 96720b8e-bda4-11e5-b8bc-080027173a36:1-11
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: master4
Master_TLS_Version:
*************************** 5. row ***************************
Slave_IO_State:
Master_Host: localhost
Master_User: repl
Master_Port: 3315
Connect_Retry: 60
Master_Log_File: mysql5.000001
Read_Master_Log_Pos: 154
Relay_Log_File: nrama12-relay-bin-master5.000003
Relay_Log_Pos: 317
Relay_Master_Log_File: mysql5.000001
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 741
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: NULL
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: 15
Master_UUID: 891fbac6-be45-11e5-9c23-080027173a36
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: 96720b8e-bda4-11e5-b8bc-080027173a36:1-11
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: master5
Master_TLS_Version:
5 rows in set (0.00 sec)

For stopping particular thread for single channel

mysql> stop slave io_thread for channel ‘master1’;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: localhost
Master_User: repl
Master_Port: 3311
Connect_Retry: 60
Master_Log_File: mysql1.000001
Read_Master_Log_Pos: 3335
Relay_Log_File: nrama12-relay-bin-master1.000004
Relay_Log_Pos: 357
Relay_Master_Log_File: mysql1.000001
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 3335
Relay_Log_Space: 3918
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: NULL
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: 11
Master_UUID: 96720b8e-bda4-11e5-b8bc-080027173a36
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: 96720b8e-bda4-11e5-b8bc-080027173a36:1-11
Executed_Gtid_Set: 96720b8e-bda4-11e5-b8bc-080027173a36:1-11
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: master1
Master_TLS_Version:
*************************** 2. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: repl
Master_Port: 3312
Connect_Retry: 60
Master_Log_File: mysql2.000001
Read_Master_Log_Pos: 154
Relay_Log_File: nrama12-relay-bin-master2.000004
Relay_Log_Pos: 317
Relay_Master_Log_File: mysql2.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 697
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: 12
Master_UUID: f414740d-bdac-11e5-8b4d-080027173a36
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: 96720b8e-bda4-11e5-b8bc-080027173a36:1-11
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: master2
Master_TLS_Version:
*************************** 3. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: repl
Master_Port: 3313
Connect_Retry: 60
Master_Log_File: mysql3.000001
Read_Master_Log_Pos: 154
Relay_Log_File: nrama12-relay-bin-master3.000004
Relay_Log_Pos: 317
Relay_Master_Log_File: mysql3.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 697
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: 13
Master_UUID: 3f02b679-bdad-11e5-9125-080027173a36
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: 96720b8e-bda4-11e5-b8bc-080027173a36:1-11
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: master3
Master_TLS_Version:
*************************** 4. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: repl
Master_Port: 3314
Connect_Retry: 60
Master_Log_File: mysql4.000001
Read_Master_Log_Pos: 154
Relay_Log_File: nrama12-relay-bin-master4.000004
Relay_Log_Pos: 317
Relay_Master_Log_File: mysql4.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 697
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: 14
Master_UUID: 86bb2f78-be45-11e5-9b02-080027173a36
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: 96720b8e-bda4-11e5-b8bc-080027173a36:1-11
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: master4
Master_TLS_Version:
*************************** 5. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: repl
Master_Port: 3315
Connect_Retry: 60
Master_Log_File: mysql5.000001
Read_Master_Log_Pos: 154
Relay_Log_File: nrama12-relay-bin-master5.000004
Relay_Log_Pos: 317
Relay_Master_Log_File: mysql5.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 697
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: 15
Master_UUID: 891fbac6-be45-11e5-9c23-080027173a36
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: 96720b8e-bda4-11e5-b8bc-080027173a36:1-11
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: master5
Master_TLS_Version:
5 rows in set (0.00 sec)

Similary for sql_thread

mysql> stop slave sql_thread for channel ‘master1’;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: repl
Master_Port: 3311
Connect_Retry: 60
Master_Log_File: mysql1.000001
Read_Master_Log_Pos: 33715130
Relay_Log_File: nrama12-relay-bin-master1.000010
Relay_Log_Pos: 357
Relay_Master_Log_File: mysql1.000001
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 33715130
Relay_Log_Space: 777
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: NULL
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: 11
Master_UUID: 96720b8e-bda4-11e5-b8bc-080027173a36
Master_Info_File: mysql.slave_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:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 96720b8e-bda4-11e5-b8bc-080027173a36:1-100046
Executed_Gtid_Set: 96720b8e-bda4-11e5-b8bc-080027173a36:1-100046
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: master1
Master_TLS_Version:
*************************** 2. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: repl
Master_Port: 3312
Connect_Retry: 60
Master_Log_File: mysql2.000001
Read_Master_Log_Pos: 154
Relay_Log_File: nrama12-relay-bin-master2.000007
Relay_Log_Pos: 317
Relay_Master_Log_File: mysql2.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 697
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: 12
Master_UUID: f414740d-bdac-11e5-8b4d-080027173a36
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: 96720b8e-bda4-11e5-b8bc-080027173a36:1-100046
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: master2
Master_TLS_Version:
*************************** 3. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: repl
Master_Port: 3313
Connect_Retry: 60
Master_Log_File: mysql3.000001
Read_Master_Log_Pos: 154
Relay_Log_File: nrama12-relay-bin-master3.000007
Relay_Log_Pos: 317
Relay_Master_Log_File: mysql3.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 697
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: 13
Master_UUID: 3f02b679-bdad-11e5-9125-080027173a36
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: 96720b8e-bda4-11e5-b8bc-080027173a36:1-100046
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: master3
Master_TLS_Version:
*************************** 4. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: repl
Master_Port: 3314
Connect_Retry: 60
Master_Log_File: mysql4.000001
Read_Master_Log_Pos: 154
Relay_Log_File: nrama12-relay-bin-master4.000007
Relay_Log_Pos: 317
Relay_Master_Log_File: mysql4.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 697
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: 14
Master_UUID: 86bb2f78-be45-11e5-9b02-080027173a36
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: 96720b8e-bda4-11e5-b8bc-080027173a36:1-100046
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: master4
Master_TLS_Version:
*************************** 5. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: repl
Master_Port: 3315
Connect_Retry: 60
Master_Log_File: mysql5.000001
Read_Master_Log_Pos: 154
Relay_Log_File: nrama12-relay-bin-master5.000005
Relay_Log_Pos: 317
Relay_Master_Log_File: mysql5.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 697
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: 15
Master_UUID: 891fbac6-be45-11e5-9c23-080027173a36
Master_Info_File: mysql.slave_master_info
SQL_Delay: 60
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: 96720b8e-bda4-11e5-b8bc-080027173a36:1-100046
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: master5
Master_TLS_Version:
5 rows in set (0.00 sec)

Since we moved to MySQL57, its good to explore other meta data tables too for checking replication status and monitoring:

Performance_schema: Replication Status and Monitoring

1. replication_connection_configuration
This table shows the replication parameters used by the slave server .

mysql> select * from performance_schema.replication_connection_configuration\G
*************************** 1. row ***************************
CHANNEL_NAME: master1
HOST: localhost
PORT: 3311
USER: repl
NETWORK_INTERFACE:
AUTO_POSITION: 0
SSL_ALLOWED: NO
SSL_CA_FILE:
SSL_CA_PATH:
SSL_CERTIFICATE:
SSL_CIPHER:
SSL_KEY:
SSL_VERIFY_SERVER_CERTIFICATE: NO
SSL_CRL_FILE:
SSL_CRL_PATH:
CONNECTION_RETRY_INTERVAL: 60
CONNECTION_RETRY_COUNT: 86400
HEARTBEAT_INTERVAL: 30.000
TLS_VERSION:

2.replication_connection_status

This table shows the current status of the I/O thread of slave server.

mysql> select * from performance_schema.replication_connection_status\G
*************************** 1. row ***************************
CHANNEL_NAME: master1
GROUP_NAME:
SOURCE_UUID: 96720b8e-bda4-11e5-b8bc-080027173a36
THREAD_ID: 67
SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 122
LAST_HEARTBEAT_TIMESTAMP: 2016-03-20 04:17:50
RECEIVED_TRANSACTION_SET: 96720b8e-bda4-11e5-b8bc-080027173a36:1-11
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00

Lets stop I/O thread for one channel and see the difference in the above table

mysql> stop slave io_thread for channel ‘master1’;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from performance_schema.replication_connection_status\G
*************************** 1. row ***************************
CHANNEL_NAME: master1
GROUP_NAME:
SOURCE_UUID: 96720b8e-bda4-11e5-b8bc-080027173a36
THREAD_ID: NULL
SERVICE_STATE: OFF
COUNT_RECEIVED_HEARTBEATS: 127
LAST_HEARTBEAT_TIMESTAMP: 2016-03-20 04:20:20
RECEIVED_TRANSACTION_SET: 96720b8e-bda4-11e5-b8bc-080027173a36:1-11
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00

3. replication_applier_configuration
This table shows the replication parameters that affect delay configured by the slave server.

mysql> select * from performance_schema.replication_applier_configuration\G
*************************** 1. row ***************************
CHANNEL_NAME: master1
DESIRED_DELAY: 0
*************************** 2. row ***************************
CHANNEL_NAME: master2
DESIRED_DELAY: 0
*************************** 3. row ***************************
CHANNEL_NAME: master3
DESIRED_DELAY: 0
*************************** 4. row ***************************
CHANNEL_NAME: master4
DESIRED_DELAY: 0
*************************** 5. row ***************************
CHANNEL_NAME: master5
DESIRED_DELAY: 0
5 rows in set (0.00 sec)

Let do some changes on replication so that we may see any differnce in this table.

mysql> stop slave for channel ‘master5’;
Query OK, 0 rows affected (0.01 sec)
mysql> change master to master_host=’localhost’, master_port=3315, master_user=’repl’,master_password=’repl123#’, master_delay=60 for channel ‘master5’;
mysql> start slave for channel ‘master5’;
Query OK, 0 rows affected (0.07 sec)
mysql> select * from performance_schema.replication_applier_configuration\G
*************************** 1. row ***************************
CHANNEL_NAME: master1
DESIRED_DELAY: 0
*************************** 2. row ***************************
CHANNEL_NAME: master2
DESIRED_DELAY: 0
*************************** 3. row ***************************
CHANNEL_NAME: master3
DESIRED_DELAY: 0
*************************** 4. row ***************************
CHANNEL_NAME: master4
DESIRED_DELAY: 0
*************************** 5. row ***************************
CHANNEL_NAME: master5
DESIRED_DELAY: 60
5 rows in set (0.00 sec)

4. replication_applier_status

This table shows the transaction execution status on the slave server.

mysql> select * from performance_schema.replication_applier_status\G
*************************** 1. row ***************************
CHANNEL_NAME: master1
SERVICE_STATE: ON
REMAINING_DELAY: NULL
COUNT_TRANSACTIONS_RETRIES: 0
*************************** 2. row ***************************
CHANNEL_NAME: master2
SERVICE_STATE: ON
REMAINING_DELAY: NULL
COUNT_TRANSACTIONS_RETRIES: 0
*************************** 3. row ***************************
CHANNEL_NAME: master3
SERVICE_STATE: ON
REMAINING_DELAY: NULL
COUNT_TRANSACTIONS_RETRIES: 0
*************************** 4. row ***************************
CHANNEL_NAME: master4
SERVICE_STATE: ON
REMAINING_DELAY: NULL
COUNT_TRANSACTIONS_RETRIES: 0
*************************** 5. row ***************************
CHANNEL_NAME: master5
SERVICE_STATE: ON
REMAINING_DELAY: NULL
COUNT_TRANSACTIONS_RETRIES: 0
5 rows in set (0.01 sec)

Here remaining_delay is waiting for DESIRED_DELAY seconds to pass since the master applied an event, this field contains the number of delay seconds remaining. At other times, this field is NULL.
The service_state is effected only when sql_thread is stopped.

5. replication_applier_status_by_coordinator

For a multi-threaded slave, the slave uses multiple worker threads and a coordinator thread to manage them, and this table shows the status of the coordinator thread.
FYI: We have to enable slave_parallel_workers by increasing the number of parallel workers from 0 to our desired number of workers.

mysql> select * from performance_schema.replication_applier_status_by_coordinator\G
Empty set (0.00 sec)
Since default 0 parallel workers or no parallel workers are there the above table will be empty. Lets enable parallel workers.
mysql> set global slave_parallel_type=”LOGICAL_CLOCK”;
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL slave_parallel_workers=20;
Query OK, 0 rows affected (0.01 sec)
mysql> stop slave;
Query OK, 0 rows affected (0.04 sec)
mysql> start slave;
Query OK, 0 rows affected (0.63 sec)

In our scenario we have 5 channels and have enabled 20 parallel workers. So total number of parallel workers or coordinater threads in a slave we will see as 20*5=100

mysql> select * from performance_schema.replication_applier_status_by_coordinator\G
*************************** 1. row ***************************
CHANNEL_NAME: master1
THREAD_ID: 86
SERVICE_STATE: ON
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
*************************** 2. row ***************************
CHANNEL_NAME: master2
THREAD_ID: 108
SERVICE_STATE: ON
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
*************************** 3. row ***************************
CHANNEL_NAME: master3
THREAD_ID: 130
SERVICE_STATE: ON
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
*************************** 4. row ***************************
CHANNEL_NAME: master4
THREAD_ID: 152
SERVICE_STATE: ON
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
*************************** 5. row ***************************
CHANNEL_NAME: master5
THREAD_ID: 174
SERVICE_STATE: ON
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
5 rows in set (0.00 sec)

6.replication_applier_status_by_worker
If the slave is not multi-threaded, this table shows the status of the applier thread.

mysql> select * from performance_schema.replication_applier_status_by_coordinator\G
*************************** 1. row ***************************
CHANNEL_NAME: master1
THREAD_ID: 438
SERVICE_STATE: ON
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
*************************** 2. row ***************************
CHANNEL_NAME: master2
THREAD_ID: 460
SERVICE_STATE: ON
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
*************************** 3. row ***************************
CHANNEL_NAME: master3
THREAD_ID: 482
SERVICE_STATE: ON
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
*************************** 4. row ***************************
CHANNEL_NAME: master4
THREAD_ID: 504
SERVICE_STATE: ON
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
*************************** 5. row ***************************
CHANNEL_NAME: master5
THREAD_ID: 526
SERVICE_STATE: ON
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
5 rows in set (0.00 sec)