MySQL master-slave synchronous monitoring shell script under Linux

Description

Operating system: CentOS

Objective: To monitor whether the MySQL master-slave database is synchronized, if it is not synchronized, record the failure time, and execute the command to restore the synchronization status.

1. Create script file

Vi/home/crontab/check_mysql_slave.sh #编辑, add the following code

#!/bin/sh

# Check_mysql_slave Status

# author Www.111cn.net

Ip=eth0 #网卡名称

Mysql_binfile=/usr/local/mysql/bin/mysql

Mysql_user=root #MySQL数据库账号

mysql_pass=123456 #密码

Mysql_sockfile=/tmp/mysql.sock

Datetime= ' date + '%y-%m-%d/%h:%m:%s ' #获取当前时间

Mysql_slave_logfile=/home/logs/check_mysql_slave.log #日志文件路径, must be created in advance.

slave_ip= ' ifconfig $ip |grep "inet addr" | awk-f[: ""]+ ' {print $} '

status=$ ($mysql _binfile-u$mysql_user-p$mysql_pass-s $mysql _sockfile-e "show slave statusg" | grep-i "Running")

Slave_io_running= ' echo $status | grep slave_io_running | awk ' {print $} '

Slave_sql_running= ' echo $status | grep slave_sql_running | awk ' {print $} '

If ["$Slave _io_running" = "yes"-a "$Slave _sql_running" = "yes"]

Then echo "Slave is running!"

Else

echo "$datetime $slave _ip slave is not running!" >> $mysql _slave_logfile

$mysql _binfile-u$mysql_user-p$mysql_pass-s $mysql _sockfile-e "SLAVE STOP;"

$mysql _binfile-u$mysql_user-p$mysql_pass-s $mysql _sockfile-e "SET GLOBAL sql_slave_skip_counter=1;"

$mysql _binfile-u$mysql_user-p$mysql_pass-s $mysql _sockfile-e "SLAVE START;"

$mysql _binfile-u$mysql_user-p$mysql_pass-s $mysql _sockfile-e "EXIT"

Fi

: wq! #保存退出

chmod +x/home/crontab/check_mysql_slave.sh #添加脚本执行权限

2, add task plan, modify/etc/crontab

Vi/etc/crontab #在最后一行添加

*/10 * * * * root/home/crontab/check_mysql_slave.sh #表示每10分钟执行一次

: wq! #保存退出

3, restart Crond to make the settings effective

/etc/rc.d/init.d/crond Restart #yum install-y Vixie-cron installation Scheduled Tasks, some systems may not have pre-installed

Chkconfig Crond on #设为开机启动

Service Crond Start #启动

Can view MySQL master-slave sync status based on log file/home/logs/check_mysql_slave.log

Original from: osyunwei.com