Compile a shell script in CentOS to monitor MySQL master-slave replication,

Compile a shell script in CentOS to monitor MySQL master-slave replication,

Objective: To regularly monitor whether the MySQL master-slave database is synchronized. If not, record the fault time and execute commands to restore the master-slave database to the synchronization state.

1. Create a script file

Vi/home/crontab/check_mysql_slave.sh # edit and add the following code

#! /Bin/sh # check_mysql_slave statusip = eth0 # Nic name mysql_binfile =/usr/local/mysql/bin/mysqlmysql_user = root # MySQL database account mysql_pass = 123456 # password mysql_sockfile =/tmp/ mysql. sockdatetime = 'date + "% Y-% m-% d/% H: % M: % S "'# obtain the current time mysql_slave_logfile =/home/logs/check_mysql_slave.log # log file path, 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 status \ G" | 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! "Elseecho" $ 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! # Save and exit chmod + x/home/crontab/check_mysql_slave.sh # Add the script execution permission

2. Add a task scheduler and modify/etc/crontab.

Vi/etc/crontab # Add */10 ***** root/home/crontab/check_mysql_slave.sh in the last line # indicates that the command is executed 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 plan task, some systems may not have pre-installed chkconfig crond on # set to start service crond start # start

You can view the MySQL Master/Slave synchronization status based on the log file/home/logs/check_mysql_slave.log.

PS:Next, this script adds "When synchronization fails", the file number and pos of the master database will be automatically extracted and synchronized to the master database. The script content is as follows:
 

#!/bin/sh#set -x#file is slave_repl.sh#Author by Kevin#date is 2011-11-13mstool="/usr/local/mysql-3307/bin/mysql -h 192.168.1.106 -uroot -pw!zl7POg27 -P 3307"sltool="/usr/local/mysql-3307/bin/mysql -h 192.168.1.107 -uroot -pw!zl7POg27 -P 3307"declare -a slave_statslave_stat=($($sltool -e "show slave status\G"|grep Running |awk '{print $2}'))if [ "${slave_stat[0]}" = "Yes" -a "${slave_stat[1]}" = "Yes" ]   then   echo "OK slave is running"   exit 0else   echo "Critical slave is error"   echo   echo "*********************************************************"   echo "Now Starting replication with Master Mysql!"    file=`$mstool -e "show master status\G"|grep "File"|awk '{print $2}'`     pos=`$mstool -e "show master status\G"|grep "Pos"|awk '{print $2}'`     $sltool -e "slave stop;change master to master_host='192.168.1.106',master_port=3307,master_user='repl',master_password='w!zl7POg27',master_log_file='$file',master_log_pos=$pos;slave start;"    sleep 3    $sltool -e "show slave status\G;"|grep Running  echo  echo "Now Replication is Finished!"  echo  echo "**********************************************************"    exit 2fi

 
The effect after running is as follows:
 

# ./slave_stop3307.sh *******************************Now stop Slave Replication!      Slave_IO_Running: No     Slave_SQL_Running: No*******************************# ./slave_repl3307.sh Critical slave is error*********************************************************Now Starting replication with Master Mysql!      Slave_IO_Running: Yes     Slave_SQL_Running: YesNow Replication is Finished!**********************************************************

Articles you may be interested in:

• Five methods for executing SQL statements in Shell scripts to operate mysql
• Connect, read, and write mysql database instances using shell scripts
• Share some functional and practical Linux shell scripts of MySQL