Automatically monitor SHELL scripts synchronized from Master to slave MySQL

The Code is as follows :#! /Bin/bash # checkMySQL_SlaveStatus # crontabtime00: 10 MYSQLPORT = 'netstat-na | grep "LISTEN" | grep "3306" | awk-F [: ""] + '{print $4} ''MYSQLIP = 'ifconfigeth0 | grep" inetaddr

The Code is as follows:

#!/bin/bash #check MySQL_Slave Status #crontab time 00:10 MYSQLPORT=`netstat -na|grep "LISTEN"|grep "3306"|awk -F[:" "]+ '{print $4}'` MYSQLIP=`ifconfig eth0|grep "inet addr" | awk -F[:" "]+ '{print $4}'` STATUS=$(/usr/local/webserver/mysql/bin/mysql -u yuhongchun -pyuhongchun101 -S /tmp/mysql.sock -e "show slave status\G" | grep -i "running")   IO_env=`echo $STATUS | grep IO | awk  ' {print $2}'` SQL_env=`echo $STATUS | grep SQL | awk  '{print $2}'` DATA=`date +"%y-%m-%d %H:%M:%S"`  if [ "$MYSQLPORT" == "3306" ] then   echo "mysql is running" else   mail -s "warn!server: $MYSQLIP mysql is down" yuhongchun027@163.com  fi  if [ "$IO_env" = "Yes" -a "$SQL_env" = "Yes" ] then   echo "Slave is running!" else   echo "####### $DATA #########">> /data/data/check_mysql_slave.log   echo "Slave is not running!" >>    /data/data/check_mysql_slave.log   echo "Slave is not running!" | mail -s "warn! $MYSQLIP MySQL Slave is not running" yuhongchun027@163.com fi 

We recommend that you run the task every 10 minutes.

*/10 * root/bin/sh/root/mysql_slave.sh

Remember to assign a yuhongchun user to each MySQL instance from the machine. It doesn't matter if you have more permissions. You can only run them locally, as shown below:

grant all privileges on *.* to "yuhongchun"@"127.0.0.1" identified by "yuhongchun101"; grant all privileges on *.* to "yuhongchun"@"localhost" identified by "yuhongchun101"; 

Script design ideas:

1. This script should be able to adapt to a variety of different Intranet and Internet environments, that is, environments with different IP addresses;

2. Let the script also monitor whether MySQL runs normally;

3. The IO and SQL statuses of the Server Load balancer must be YES. The multiple conditions are used to determine-.

Background environment generated by the script:

I have many websites based on the public network (without a hardware firewall and directly placed in the IDC room), all of which are in the MySQL master-slave architecture. The slave machine is mainly used to back up the database and cold backup, although it is not a problem because of downtime, it also affects data backup. There are dozens of such websites. If you perform manual checks one by one, it will take a lot of time every day, so I took the script control and designed the above script.

Script practice:

I have used this script in the production environment. You can place it on our MySQL machine for monitoring. In addition, we recommend that you manually check the script, once we found that rsync -- delete automatically deleted the data in/data, that is, from the database location, and the script did not trigger an alarm.

Post-application:

Later, the company's MySQL database was designed to be upgraded from one master to one slave to one master, multiple slave, and read/write splitting. LVS was used as the Load balancer of the slave database, this script automatically monitors the replication status from MySQL. If it cannot be synchronized, the MySQL service on the local machine is automatically disabled to avoid affecting the normal business access of the entire website. Of course, the running cycle of the script must be changed from 10 minutes to seconds. This can be achieved through the while loop.