如何用SQL指令查詢MySQL Replication架構中, SLAVE DB的 IO THREAD / SQL THREAD的status

以前曾經用shell scripts 來寫MySQL replication的同步告警(可利用以下寫法在 IO THREAD / SQL THREAD 任一的狀態= NO 就發錯誤訊息.

ACCOUNT="root"
PW="root123"
LOGDIR=/home/mysql/sh
mysql -u root -proot123 -e 'show slave status \G;' -h db2priv |egrep -i 'Slave_|Master_Log_File|Read_Master_Log_Pos|Master_Host|Last_Error'

IO_STAT=`mysql -u ${ACCOUNT} -p${PW} -h localhost -e "show slave status\G;" |grep Slave_IO_Running|grep Yes |wc |awk '{print $1}'`
SQL_STAT=`mysql -u ${ACCOUNT} -p${PW} -h localhost -e "show slave status\G;" |grep Slave_SQL_Running|grep Yes |wc |awk '{print $1}'`
if [ $IO_STAT = "1" ] && [ $SQL_STAT = "1" ] ; then
echo "`date` Replication Status is ok !! "
echo "`date` Replication Status is ok" >> ${LOGDIR}/rep_hourly_check.log
fi


而透過以下SQL 也可以查到IO THREAD / SQL THREAD的status
// 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;



ref:
https://dba.stackexchange.com/questions/6365/can-we-capture-only-slave-io-running-in-show-slave-status-in-mysql
https://dev.mysql.com/doc/refman/5.7/en/replication-applier-status-table.html