CODEWITHSUNDEEP
mysqlreplicationstatus
MySQL DBA
MySQL DBA

Reputation: 5822

How to know MySQL replication status using a select query?

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.

Upvotes: 23

Views: 59604

Answers (12)

Jolrael
Jolrael

Reputation: 1

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;

Upvotes: 0

spen.smith
spen.smith

Reputation: 587

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

Upvotes: 3

Renan Benedicto Pereira
Renan Benedicto Pereira

Reputation: 311

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

Upvotes: 12

harshil9968
harshil9968

Reputation: 3244

You can also run this at master. 

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

Upvotes: 0

Fred
Fred

Reputation: 1

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.

Upvotes: 0

Paul Tobias
Paul Tobias

Reputation: 2221

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.

Upvotes: 14

ninjabber
ninjabber

Reputation: 371

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

Upvotes: 5

hslakhan
hslakhan

Reputation: 161

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';

Upvotes: 16

Bad Tea
Bad Tea

Reputation: 41

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/

Upvotes: 2

MySQL DBA
MySQL DBA

Reputation: 5822

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

Upvotes: 4

Ashwin A
Ashwin A

Reputation: 3867

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.

Upvotes: 2

ajreal
ajreal

Reputation: 47321

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

Upvotes: -3

Related Questions