How to know MySQL replication status using a select query?

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.

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

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

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.

You can also run this at master.

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

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;