How to know MySQL replication status using a select query?

Question

Is it possible to get replication status from any system database table?

Using which I can identify whether the replication is up or down.

I need to to know whether the SLAVE_IO_RUNNING and SLAVE_SQL_RUNNING = YES from a system table.

Papst

Answer 1

This is the statement that I have used based on Manasi's top answer.

SELECT variable_value 
FROM information_schema.global_status 
WHERE variable_name='SLAVE_RUNNING';

Hypermetropia

Hi, as said here, starting with version 5.7 information_schema.global_status is now deprecated. – Pretrice

As information_schema.global_status is now deprecated, referer to @bad-tea response, it talk about enable master-info-repository and relay-log-info-repository variables that enable access to this kind of information. – Pretrice

Answer 2

hslakhan's answer works for MySQL 5.6, but for MySQL 5.7 the slave status variables have moved from information_schema to performance_schema.

Slave_IO_Running corresponds to:

SELECT SERVICE_STATE FROM performance_schema.replication_connection_status;

Slave_SQL_Running corresponds to:

SELECT SERVICE_STATE FROM performance_schema.replication_applier_status;

There are some other variables from the SHOW SLAVE STATUS output too, see https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_show_compatibility_56_slave_status for the rest.

Extensile

Answer 3

Based on this question I've written a query to answer you. Please maintain the copyright :-)

SELECT 
    channel_name AS Channel_Name,
    smi.host AS Master_Host,
    smi.user_name AS Master_User,
    smi.port AS Master_Port,
    smi.master_log_name AS Master_Log_File,
    smi.master_log_pos AS Read_Master_Log_Pos,
    ssi.master_log_pos AS Exec_Master_Log_Pos,
    rcs.service_state AS Slave_IO_Running,
    rss.service_state AS Slave_SQL_Running,
    t.processlist_time AS Seconds_Behind_Master,
    rcs.last_error_number AS Last_IO_Errno,
    rcs.last_error_message AS Last_IO_Error,
    rss.last_error_number AS Last_SQL_Errno,
    rss.last_error_message AS Last_SQL_Error,
    tc.processlist_state AS  Slave_IO_State,
    t.processlist_state AS  Slave_SQL_Running_State

FROM
    mysql.slave_master_info smi 
        JOIN
    mysql.slave_relay_log_info ssi USING (channel_name)   
        JOIN 
    performance_schema.replication_connection_status rcs USING (channel_name)
        LEFT JOIN
    performance_schema.replication_applier_status_by_worker rss USING (channel_name)
        LEFT JOIN
    performance_schema.threads t ON (rss.thread_id = t.thread_id)
        LEFT JOIN
    performance_schema.threads tc ON (rcs.thread_id = tc.thread_id)
\G

Best regards, Renan Benedicto Pereira (BR MySQL DBA)

Note: This wont work unless master_info_repository = TABLE and relay_log_info_repository=TABLE is enabled, FILE option is the default, will not work

Counterfactual

The column "processlist_time" from performance_schema.threads for the connection thread is not the same value as "Seconds_Behind_Master". This info is wrong. – Cassius

The docs for MySQL 5.6, 5.7, and 8.0 seem to indicate it should be equivalent: "For a replica SQL thread, the value is the number of seconds between the timestamp of the last replicated event and the real time of the replica host. See Section 16.2.3, “Replication Threads”. Am I missing something? – Wholism

"Please maintain the copyright :-)" - is that a genuine request for attribution, or are you just noting that the necessary query is so complex that it perhaps constitutes an original work? (Or both?) – Fogel

Answer 4

This solution uses awk to process a show command output and sends a mail in case of errors in any of the field processed. In this case the fields are Slave_IO_Running and Slave_SQL_Running. Fill free to add other fields from the 'show slave status' output - Last_Error/Seconds_Behind_Master for example or to awk the output of other show commands.

#!/bin/bash  
# get some slave stats  
Slave_IO_Running=`mysql -u root --password="pwd" -Bse "show slave status\G" | grep Slave_IO_Running | awk '{ print $2 }'`  
Slave_SQL_Running=`mysql -u root --password="pwd" -Bse "show slave status\G" | grep Slave_SQL_Running | awk '{ print $2 }'`  
Last_error=`mysql -u root --password="pwd" -Bse "show slave status\G" | grep Last_error | awk -F : '{ print $2 }'`  

if [ $Slave_SQL_Running == 'No' ] || [ $Slave_IO_Running == 'No' ];  
then  
    echo "Last Error:" $Last_error | mail -s "Replication error on slavedb!!!" [email protected]  
fi  

exit 0  

Ashleeashleigh

pls describe your solution – Justness

as requested, pls see above – Ashleeashleigh

Thanks for sharing this. Improvement proposal: Fetch the status only once and do the rest locally. Also, I suggest to use "grep -i" for case insensitive grep. On my server running mysql 5.6 "Last_Error" has uppercase E. – Greenlet

let people learn using man {cmd_name} give them code with some errors so that they have to understand it before just pasting it ;) – Ashleeashleigh

This answer doesn't address the original question, which was how to get the information from a system table (e.g. via SELECT statement). – Derinna

Answer 5

I got the solution in information_schema database. Please check the table GLOBAL_STATUS in information_schema database. You will see a variable "SLAVE_RUNNING" if it is "ON" that means replication is working fine. If it is "OFF" then replication has failed due to any reason and you need to check why? :-)

Manasi

Papst

Answer 6

Is there a select query that can yield this information?

I did a lot of searching for this answer, I really hate scrolling after running show slave status;.

I like @renan's answer. This answer focuses on show slave status;, the common_schema, and alternatives if that isn't available.

Easy, but not customizable (and not SELECT):

show slave status;

---

If you have access to common_schema and primarily want seconds behind master:

SELECT SECONDS_BEHIND_MASTER, 
       slave_status.* 
FROM common_schema.slave_status;

---

If you only want the last error message, ignoring seconds behind master:

SELECT LAST_ERROR_MESSAGE,
       LAST_ERROR_TIMESTAMP,
       replication_applier_status_by_worker.*
FROM performance_schema.replication_applier_status_by_worker

---

What I use (both):

This shows the most recent error on the same row as the SECONDS_BEHIND_MASTER. If this value is non-zero, it's possible there is a large query running, or there's an error that you would find in the show slave status; command, or you could find it in the performance_schema.

SELECT LAST_ERROR_MESSAGE,
       LAST_ERROR_TIMESTAMP,
       SECONDS_BEHIND_TABLE.*
FROM performance_schema.replication_applier_status_by_worker
         left join (select SECONDS_BEHIND_MASTER,
                           null                 'RUNNING_INDICATORS ->',
                           SLAVE_RUNNING,
                           SLAVE_IO_RUNNING,
                           SLAVE_SQL_RUNNING,
                           'show slave status;' FOR_MORE
                    from common_schema.slave_status) SECONDS_BEHIND_TABLE on TRUE;

---

Last: if you don't have access to common_schema but wish you did:

Please note: this is exclusively the work of Shlomi Noach, from his open source library, which I have molded into a single query. This should be identical to the output of SELECT * FROM common_schema.slave_status;

SELECT SUM(IF(is_io_thread, TIME, NULL))                                      AS Slave_Connected_time,
       SUM(is_io_thread) IS TRUE                                              AS Slave_IO_Running,
       SUM(is_sql_thread OR (is_system AND NOT is_io_thread)) IS TRUE         AS Slave_SQL_Running,
       (SUM(is_system) = 2) IS TRUE                                           AS Slave_Running,
       SUM(IF(is_sql_thread OR (is_system AND NOT is_io_thread), TIME, NULL)) AS Seconds_Behind_Master
FROM (
         SELECT PROCESSLIST.*,
                USER = 'system user'                                                     AS is_system,
                (USER = 'system user' AND state_type = 'replication_io_thread') IS TRUE  AS is_io_thread,
                (USER = 'system user' AND state_type = 'replication_sql_thread') IS TRUE AS is_sql_thread,
                COMMAND = 'Binlog Dump'                                                  AS is_slave
         FROM INFORMATION_SCHEMA.PROCESSLIST
                  LEFT JOIN (
             -- Replication SQL thread states
             select 'Waiting for the next event in relay log' state, 'replication_sql_thread' state_type
             union
             select 'Reading event from the relay log' state, 'replication_sql_thread' state_type
             union
             select 'Making temp file' state, 'replication_sql_thread' state_type
             union
             select 'Slave has read all relay log; waiting for the slave I/O thread to update it' state, 'replication_sql_thread' state_type
             union
             select 'Waiting until MASTER_DELAY seconds after master executed event' state, 'replication_sql_thread' state_type
             union
             select 'Has read all relay log; waiting for the slave I/O thread to update it' state, 'replication_sql_thread' state_type
             union
             -- Replication I/O thread states
             select 'Waiting for an event from Coordinator' state, 'replication_io_thread' state_type
             union
             select 'Waiting for master update' state, 'replication_io_thread' state_type
             union
             select 'Connecting to master ' state, 'replication_io_thread' state_type
             union
             select 'Checking master version' state, 'replication_io_thread' state_type
             union
             select 'Registering slave on master' state, 'replication_io_thread' state_type
             union
             select 'Requesting binlog dump' state, 'replication_io_thread' state_type
             union
             select 'Waiting to reconnect after a failed binlog dump request' state, 'replication_io_thread' state_type
             union
             select 'Reconnecting after a failed binlog dump request' state, 'replication_io_thread' state_type
             union
             select 'Waiting for master to send event' state, 'replication_io_thread' state_type
             union
             select 'Queueing master event to the relay log' state, 'replication_io_thread' state_type
             union
             select 'Waiting to reconnect after a failed master event read' state, 'replication_io_thread' state_type
             union
             select 'Reconnecting after a failed master event read' state, 'replication_io_thread' state_type
             union
             select 'Waiting for the slave SQL thread to free enough relay log space' state, 'replication_io_thread' state_type
         ) known_states ON (known_states.state LIKE CONCAT(PROCESSLIST.STATE, '%'))
         WHERE USER = 'system user'
            OR COMMAND = 'Binlog Dump'
     ) common_schema_slave_status;

---

Sources:

• A lot of googling, where I finally found this awesome doc on common_schema.slave_status
• Whose project is open source, and accessible here, where I mainly used the definition of _known_thread_states, which is a table in the library, to recompose what those of us with access to his work can make use of in common_schema.slave_status, which is a view in the library.
• The docs on the performance schema replication tables. (I queried all tables in performance_schema.replication_* until I found one that showed the specific error I was looking for, which you can see at the above link, just scroll down a while.)

Best, Spencer

Wholism

Answer 7

The primary statement for this is SHOW SLAVE STATUS, which you must execute on each slave. Refer: http://dev.mysql.com/doc/refman/5.1/en/replication-administration-status.html

On the master, you can check the status of connected slaves using SHOW PROCESSLIST to examine the list of running processes. For slaves that were started with the --report-host option and are connected to the master, the SHOW SLAVE HOSTS statement on the master shows basic information about the slaves.

Auroora

Answer 8

Beginning in MySQL 5.6, you can store the slave status in tables rather than files by starting the server with --master-info-repository=TABLE and --relay-log-info-repository=TABLE.

Reference: http://dev.mysql.com/doc/refman/5.6/en/slave-logs.html

Even with that, I'm not sure if the tables will contain the specific values you are looking for (SLAVE_IO_RUNNING and SLAVE_SQL_RUNNING). I could not try this because I'm running mysql 5.1; I was just searching and found it in the 5.6 documentation.

It sounds like you are trying to monitor the thread status in an automated fashion. Since I do not have the tables, I plan to do this with a shell script and cron job, with something like this:

$ mysql -u root -pXXXX -e "SHOW SLAVE STATUS\G" | grep Slave_IO_Running | awk '{ print $2 }'
$ mysql -u root -pXXXX -e "SHOW SLAVE STATUS\G" | grep Slave_SQL_Running | awk '{ print $2 }'

Reference: http://www.stardothosting.com/blog/2012/02/checking-and-repairing-mysql-replication-automatically/

Ashton

Answer 9

I'm not really sure what the fuss is about tbh. 'show slave status' IS a query. You can execute that query from any modern programming language and then simply choose the column names you wish to use right?

In PHP for example I use:

    $row = $stmt->fetch();
    print "Slave_IO_Running: " . $row['Slave_IO_Running'] . "\n";

After getting the results from 'show slave status' in $row.

Bach

Answer 10

You can also run this at master.

SELECT * FROM information_schema.PROCESSLIST AS p WHERE p.COMMAND = 'Binlog Dump';

Clevie

Answer 11

In MySQL 5.7 we get this info as follows:

select
    ras.SERVICE_STATE as sql_status,
    rcs.SERVICE_STATE as io_status,
    t.PROCESSLIST_TIME as slave_lag
from
    performance_schema.replication_applier_status ras
join replication_connection_status rcs
left join performance_schema.threads t on
    t.NAME = 'thread/sql/slave_sql'
left join information_schema.processlist pl on
    pl.id = t.PROCESSLIST_ID;

Universal

Answer 12

afaik, there is no select (like information_schema)

to check slave replication status

show slave status;

reference -- http://dev.mysql.com/doc/refman/5.0/en/show-slave-status.html

Cloutman