I wanted to capture only status/result of Slave_IO_Running and Slave_SQL_Running column values, which I will be using to integrate into our application.
Is there any way to get only the values of those fields instead of getting values of all fields of SHOW SLAVE STATUS.
My OS is windows.
I am using MySQL 5.0.24
mysql> SHOW STATUS LIKE 'Slave_running'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Slave_running | ON | +---------------+-------+ 1 row in set (0.04 sec)
From the manual:
This is ON if this server is a replication slave that is connected to a replication master, and both the I/O and SQL threads are running; otherwise, it is OFF.
Caveat: If you’re running MySQL prior to version 5.1.46 or 5.5.4, it’s possible the slave IO thread could not be running, and the status variable indicate the slave is running. (MySQL Bug 51089)
As of MySQL 5.7, the above solution will not work by default. This is because many of the status variables from
information_schema are being moved into
As of MySQL 5.7.6, the Performance Schema also contains system and status variable tables (see Section 23.9.13, “Performance Schema System Variable Tables”, and Section 23.9.14, “Performance Schema Status Variable Tables”). The Performance Schema tables are intended to replace the INFORMATION_SCHEMA tables, which are deprecated as of MySQL 5.7.6 and will be removed in a future MySQL release.
These Slave_xxx status variables become unavailable through SHOW STATUS:
Applications that use these status variables should be revised to obtain this information using the replication-related Performance Schema tables.
There are two options to get the
slave_running status outside of
SHOW SLAVE STATUS
- The preferred method, as mentioned by the doc, is to update the application to pull from
// this is the SLAVE IO THREAD status
SELECT SERVICE_STATE FROM performance_schema.replication_connection_status;
// this is the SLAVE SQL THREAD status
SELECT SERVICE_STATE FROM performance_schema.replication_applier_status;
NOTE: This is for single master replication. Multi-master replication should be in
- The second option is to turn on
show_compatibility_56. This is a temporary fix to get you by without modifying the application, but will go away in a future release.
SELECT t.PROCESSLIST_TIME, t.* FROM performance_schema.threads t WHERE NAME IN('thread/sql/slave_io', 'thread/sql/slave_sql')
A useful command to display the result in a more inteligible way is
mysql> show slave STATUSG; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 127.0.0.1 Master_User: repuser Master_Port: 3305 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 426595 Relay_Log_File: ip-172-31-17-228-relay-bin.000004 Relay_Log_Pos: 19023 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: (...)