MySQL master-slave synchronization monitoring shell script under Linux

Instructions:

Operating system: CentOS

Purpose: Regularly monitor whether the MySQL master-slave database is synchronized, if not, record the failure time, and execute the command to restore the master-slave synchronization state

1. Create a script file

vi / home/crontab/check_mysql_slave.sh #Edit, add the following code

#!/bin/sh

# check_mysql_slave status

# author www.osyunwei.com

ip=eth0 #Network card name

mysql_binfile=/usr/ local/mysql/bin/mysql

mysql_user=root #MySQL database account

mysql_pass=123456 #Password

mysql_sockfile=/tmp/mysql.sock

datetime=`date +"%Y-%m-%d/ %H:%M:%S"` #Get the current time

mysql_slave_logfile=/home/logs/check_mysql_slave.log #The log file path must be created in advance

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

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 $2}'`

Slave_SQL_Running=`echo $status | grep Slave_SQL_Running | awk '{print $2}'`

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;"

$mys ql_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! #Save Exit

chmod +x /home/crontab/check_mysql_slave.sh #Add script execution permissions

2. Add task plan, modify /etc/crontab

vi /etc/crontab #Add

*/10 in the last line * * * * root /home/crontab/check_mysql_slave.sh #Indicates execution every 10 minutes

:wq! #Save and exit

3. Restart crond to make the settings take effect

/etc/rc.d/init.d /crond restart #yum install -y vixie-cron installation scheduled task, some systems may not be pre-installed

chkconfig crond on #Set to boot

service crond start #Start

according to the log file /home/logs /check_mysql_slave.log Check the MySQL master-slave synchronization status

At this point, the MySQL master-slave synchronization monitoring shell script under Linux is completed.