监控mysql主从复制状态以及延迟情况

最新推荐文章于 2024-07-15 11:48:23 发布

搬砖码农SmallNNN

最新推荐文章于 2024-07-15 11:48:23 发布

阅读量968

点赞数

分类专栏： percona Centos Shell 文章标签： mysql 主从复制主从延迟复制状态

本文链接：https://blog.csdn.net/u014180504/article/details/92975138

版权

Centos 同时被 3 个专栏收录

34 篇文章 0 订阅

订阅专栏

percona

15 篇文章 0 订阅

订阅专栏

Shell

4 篇文章 0 订阅

订阅专栏

#!/bin/sh

#hosts
slave_hosts='192.168.1.1 192.168.1.2 '
slave_user='backup'
slave_pwd='123456'

loghost='192.168.103.53'
loguser='root'
logpwd='111111'

checktime=`date +%Y%m%d%H%M%S`
logpath='/home/'

echo "--${checktime}----start check-----" >> ${logpath}/log.log
mysql -h${loghost} -P3306 -u${loguser} -p${logpwd} slaveinfo -e "INSERT INTO slaveinfo(mark,host) VALUES ('---${checktime}----start check-----','${host}')"

for host in $slave_hosts;do
slave_io_thread=($(mysql -h${host} -ubackup -p123456 -e "show slave status\G"|grep "Slave_IO_Running" |awk '{print $2}'))
if [ "${slave_io_thread}" = "Yes" ];then
	echo "slave_io_thread:${slave_io_thread}" >> ${logpath}/log.log
else
	slave_ioerrno=($(mysql -h${host} -ubackup -p123456 -e "show slave status\G"|grep "Last_IO_Errno" |awk '{print $2}'))
	echo "slave_io_thread stoped,IO_Errno:${slave_ioerrno}" >> ${logpath}/log.log
	mysql -h${loghost} -P3306 -u${loguser} -p${logpwd} slaveinfo -e "INSERT INTO slaveinfo(mark,host) VALUES ('slave_io_thread stoped,IO_Errno:${slave_ioerrno}','${host}')"
fi

slave_sql_thread=($(mysql -h${host} -ubackup -p123456 -e "show slave status\G"|grep "Slave_SQL_Running" |awk '{print $2}'))
if [ "${slave_sql_thread}" = "Yes" ];then
	echo "slave_sql_thread:${slave_sql_thread}" >> ${logpath}/log.log
else
	slave_sqlerrno=($(mysql -h${host} -ubackup -p123456 -e "show slave status\G"|grep "Last_SQL_Errno" |awk '{print $2}'))
	echo "slave_sql_thread stoped,SQL_Errno:${slave_sqlerrno}" >> ${logpath}/log.log
	mysql -h${loghost} -P3306 -u${loguser} -p${logpwd} slaveinfo -e "INSERT INTO slaveinfo(mark,host) VALUES ('slave_sql_thread stoped,SQL_Errno:${slave_sqlerrno}','${host}')"
fi

slave_seconds_bghind_master=($(mysql -h${host} -ubackup -p123456 -e "show slave status\G"|grep "Seconds_Behind_Master" |awk '{print $2}'))
echo "slave_seconds_bghind_master:${slave_seconds_bghind_master}" >> ${logpath}/log.log
if [ ${slave_seconds_bghind_master} -ge 2 ];then
	mysql -h${loghost} -P3306 -u${loguser} -p${logpwd} slaveinfo -e "INSERT INTO slaveinfo(mark,host) VALUES ('slave_seconds_bghind_master:${slave_seconds_bghind_master}','${host}')"
fi

done