使用选择查询的 mysql 复制状态

mysql replication status using a select query

提问by MySQL DBA

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?

我需要知道系统表中的 SLAVE_IO_RUNNING 和 SLAVE_SQL_RUNNING = YES 是否？

Manasi

玛纳西

回答by hslakhan

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

这是我根据 Manasi 的最佳答案使用的陈述。

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

回答by Paul Tobias

hslakhan's answerworks for MySQL 5.6, but for MySQL 5.7the slave status variables have moved from information_schemato performance_schema.

hslakhan的回答工程对MySQL 5.6，但MySQL的5.7从属状态变量已从移动information_schema到performance_schema。

Slave_IO_Runningcorresponds to:

Slave_IO_Running对应于：

SELECT SERVICE_STATE FROM performance_schema.replication_connection_status;

Slave_SQL_Runningcorresponds to:

Slave_SQL_Running对应于：

SELECT SERVICE_STATE FROM performance_schema.replication_applier_status;

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

SHOW SLAVE STATUS输出中还有一些其他变量，其余的请参见https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_show_compatibility_56_slave_status。

回答by MySQL DBA

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? :-)

我在 information_schema 数据库中得到了解决方案。请检查information_schema 数据库中的GLOBAL_STATUS 表。如果变量“SLAVE_RUNNING”为“ON”，则表示复制工作正常，您将看到变量“SLAVE_RUNNING”。如果它是“OFF”，那么复制由于任何原因而失败，您需要检查为什么？:-)

Manasi

玛纳西

回答by Renan Benedicto Pereira

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)

最好的问候， Renan Benedicto Pereira（BR MySQL DBA）

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

注意：除非启用master_info_repository = TABLE和否则这不会起作用relay_log_info_repository=TABLE，FILE 选项是默认值，不会起作用

回答by ninjabber

This solution uses awkto 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_Runningand Slave_SQL_Running. Fill free to add other fields from the 'show slave status' output - Last_Error/Seconds_Behind_Masterfor example or to awkthe output of other show commands.

此解决方案使用awk来处理 show 命令输出，并在处理的任何字段中出现错误时发送邮件。在这种情况下，字段是Slave_IO_Running和Slave_SQL_Running。自由填写以从“show slave status”输出中添加其他字段 -例如Last_Error/Seconds_Behind_Master或awk其他 show 命令的输出。

#!/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  }'`  
Slave_SQL_Running=`mysql -u root --password="pwd" -Bse "show slave status\G" | grep Slave_SQL_Running | awk '{ print  }'`  
Last_error=`mysql -u root --password="pwd" -Bse "show slave status\G" | grep Last_error | awk -F : '{ print  }'`  

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  

回答by Ashwin A

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

对此的主要语句是 SHOW SLAVE STATUS，您必须在每个从站上执行该语句。参考：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.

在 master 上，您可以使用 SHOW PROCESSLIST 检查已连接的 slave 的状态，以检查正在运行的进程列表。对于使用 --report-host 选项启动并连接到主站的从站，主站上的 SHOW SLAVE HOSTS 语句显示有关从站的基本信息。

回答by Bad Tea

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

从 MySQL 5.6 开始，您可以通过使用--master-info-repository=TABLE和启动服务器来将从属状态存储在表中而不是文件中--relay-log-info-repository=TABLE。

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

参考：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_RUNNINGand 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.

即使这样，我也不确定这些表是否包含您正在寻找的特定值 (SLAVE_IO_RUNNING和SLAVE_SQL_RUNNING)。我无法尝试这个，因为我正在运行 mysql 5.1；我只是在搜索并在 5.6 文档中找到它。

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:

听起来您正在尝试以自动方式监视线程状态。由于我没有这些表，我计划使用 shell 脚本和 cron 作业来执行此操作，如下所示：

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

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

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

回答by Fred

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?

我不太确定 tbh 有什么大惊小怪的。'show slave status' 是一个查询。您可以从任何现代编程语言执行该查询，然后只需选择您希望使用的列名，对吗？

In PHP for example I use:

例如在 PHP 中，我使用：

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

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

从 $row 中的“show slave status”获得结果后。

回答by harshil9968

You can also run this at master.

您也可以在 master 上运行它。

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

回答by ajreal

afaik, there is no select (like information_schema)

afaik，没有选择（如information_schema）

to check slave replication status

检查从属复制状态

show slave status;

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

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