Automatically monitor scripts synchronized from MySQL

BKJIA exclusive Article] Many websites based on the public network do not have a hardware firewall and are directly placed in the IDC data center. All these websites are in the MySQL master-slave architecture. The master and slave databases backup databases 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, therefore, a script is designed to automatically monitor synchronization from MySQL.

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-.

Script content:

#crontab time 00:10#!/bin/bash#check MySQL_Slave Status#crontab time 00:10MYSQLPORT=`netstat -na|grep "LISTEN"|grep "3306"|awk -F[:" "]+ '{print $4}'`MYSQLIP=`ifconfig eth0|grep "inet addr" | awk -F[:" "]+ '{print $4}'`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  echo $MYSQLIP mysql is down" | mail -s "warn!server: $MYSQLIP mysql is down" 1309889xxxx@wo.com.cnfiif [ "$IO_env" = "Yes" -a "$SQL_env" = "Yes" ]then  echo "Slave is running!"elseecho "Slave is not running!" | mail -s "warn! $DATA $MYSQLIP MySQL Slave is not running" 1309889xxxx@163.comfi

The above is the final version. With the DATA parameter added, You can accurately locate the time point at which the MySQL Master/Slave encountered a problem. You can compare the differences with the first version:

#!/bin/bash#check MySQL_Slave Status#crontab time 00:10MYSQLPORT=`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}'`if [ "$MYSQLPORT" == "3306" ]then  echo "mysql is running"else  mail -s "warn!server: $MYSQLIP mysql is down" yuhongchun027@163.comfiif [ "$IO_env" = "Yes" -a "$SQL_env" = "Yes" ]then  echo "Slave is running!"else  echo "#######  $date  #########">> /data/data/check_mysql_slave.log  echo "Slave is not running!" >> /data/data/check_mysql_slave.log  mail -s "warn! $MySQLIP_replicate_error" yuhongchun027@163.com << /data/data/check_mysql_slave.logfi

Usage:

Use crontab to set regular run. It is recommended to run it 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";

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.

If you want to use mobile phone mail, pay attention to the following:

1. the Linux server itself has the sendmail service, and its mail sending function is very effective. It does not need to be transferred through the company's email server;

2. Although China Unicom can bind it to its mailbox and set an email arrival alarm, it finds that there are still many problems with China Unicom's number delay and arrival. We recommend that you use a mobile number;

3. This alarm policy is only used as an aid.

Bkjia.com is an exclusive special article. For more information, see the original author and source .]