CentOS to write shell script to monitor Mysql master-slave replication tutorial _mysql

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 ip=eth0 #网卡名称 mysql_binfile=/usr/local/mysql/bin/mysql mysql_user=root #MySQL数据库 Account mysql_pass=123456 #密码 mysql_sockfile=/tmp/mysql.sock datetime= ' date + '%y-%m-%d/%h:%m:%s ' #获取当前时间 mysql_slave_logf Ile=/home/logs/check_mysql_slave.log #日志文件路径, we must create a good 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 status\ G "| 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 ' $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$mys

Ql_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 Schedule task, some systems may not be preloaded with

chkconfig on #设为开机启动

Service Crond Start #启动

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

PS: The next script added "when the discovery synchronization is not synchronized" will automatically extract the main library file number, as well as POS, to synchronize the main library, the script reads as follows:

#!/bin/sh #set-X #file is slave_repl.sh #Author by Kevin #date is 2011-11-13 mstool= "/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_stat slave_stat= ($ ($sltool-E" show slave status\g "|grep Running |awk ' {print $} ')) if [" ${sla Ve_stat[0]} "= yes"-a "${slave_stat[1]}" = "yes"] then echo OK slave is running "exit 0 else Echo" critica l Slave is error ' echo echo ' ********************************************************* ' echo ' now starting Replic
    ation with Master mysql! " File= ' $mstool-e "show Master Status\g" |grep "File" |awk ' {print $} ' pos= ' $mstool-e "show Master Status\g" |grep "Po S "|awk ' {print $} ' $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 2 fi

Post-run effects, 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:yes now
Replication is finished!
**********************************************************