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

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.