At MySQL, replication usability is of utmost importance to us. Replication information has long been part of SHOW commands, SHOW SLAVE STATUS occupying a major chunk of it. The other sources of replication information being:
SHOW MASTER STATUS,
SHOW BINLOG EVENTS,
SHOW RELAYLOG EVENTS,
SHOW VARIABLES,
SHOW STATUS,
ERROR LOGS etc.
As the replication module grows further, there is a lot more monitoring information, so much that the present interfaces seem too rigid to accommodate all the information we would like to present. So we need to organize them in a more structured manner. In MySQL-5.7.2, we introduced replication performance_schema (P_S) tables providing an SQL interface to monitor replication configuration and status partitioned into different tables, each table grouping logically related information. You can read more about the contents of these tables from official MySQL documentation.
In MySQL-5.7.5 we added some more status variables to these performance_schema tables to enable monitoring the latest replication features viz. multi-source replication in labs. Multi-source allows a MySQL slave to replicate from multiple sources (masters) directly. Talking of multi-source, one needs the replication information per source. So we added global variables that would be useful to extend to per-source scope to the replication performance\_schema tables to help monitor multi-source replication. Note that these variables still work for the single sourced replication and can still be accessed as:
Show status like ‘Slave_running’;
Show status like ‘Slave_retried_transactions’;
Show status like ‘Slave_last_heartbeat’;
Show status like ‘Slave_received_heartbeats’;
show status like ‘Slave_heartbeat_period’;
Note though that the status variables are now mostly useful in single-source mode ONLY. If more sources are added, the status variables still just apply to the first source. For other replication sources (masters), the only way to access these variables is to use the replication performance_schema tables as named in the table below. Here is how the names of server variables map to the names in the replication performance_schema tables:
| Variable name | P_S Table Name | P_S field name |
|---|---|---|
| SLAVE_HEARTBEAT_PERIOD | replication_connection_configuration | HEARTBEAT_INTERVAL |
| SLAVE_RECEIVED_HEARTBEATS | replication_connection_status | COUNT_RECEIVED_HEARTBEATS |
| SLAVE_LAST_HEARTBEAT | replication_connection_status | LAST_HEARTBEAT_TIMESTAMP |
| SLAVE_RETRIED_TRANSACTIONS | replication_execute_status | COUNT_TRANSACTIONS_RETRIES |
The variable ‘slave_running’ reports whether the slave is running or not. This can be found by inspecting the two replication components (receiver and applier) separately to see if the receiver module is running or not by executing
SELECT SERVICE_STATE FROM performance_schema.replication_connection_status;
And execute module is running or not by executing
SERVICE_STATE FROM performance_schema.replication_execute_status;
Please try out multi-source replication and our new monitoring interface in the form of replication performance_schema tables. As always, your feedback is very valuable to us.