Shell script Implementation monitor MySQL master-slave sync _linux Shell

The code is as follows:

Copy Code code as follows:

#!/bin/bash
#check Mysql_slave Status
#crontab Time 00:10
Mysqlport= ' Netstat-na|grep "LISTEN" |grep "3306" |awk-f[: ""]+ ' {print $} '
mysqlip= ' ifconfig eth0|grep "inet addr" | awk-f[: ""]+ ' {print $} '
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 $} '
Sql_env= ' echo $STATUS | grep SQL | awk ' {print $} '
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

Recommended to run once every 10 minutes

Copy Code code as follows:

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

Remember in each MySQL from the machine to allocate a Yuhongchun user, the permission is not OK, only limited to run locally, as follows:

Copy Code code as follows:

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 internal and external network environment, that is, IP different environment;
2, let the script also conveniently monitor the MySQL whether the normal operation;
3, slave machine IO and SQL State must be yes, integral, here used to judge-a multiple conditions.

Script-generated background environment:

I have a number of Web sites based on public networks (no hardware firewalls, directly in the IDC room to do is the MySQL master-slave architecture, from the main backup database and cold backup of the role, although from the machine downtime is not a problem, but also affect the data backup work; Such a site has dozens of, if a manual check, Every day to waste a lot of time, so play the script control, designed the above scripts.

Scripting practices:

This script I have used in the production environment, you can put it on our MySQL machine, used to monitor, and also suggested that sometimes manually check, once found that rsync--delete automatically deleted the data inside the/data/data, that is, from the location of the database, the script did not alarm.

Post application:

Later the company's MySQL database preparation by a main one from the shelf-color upgrade into a master from, read-write separation of the architecture, LVS as a load balancer from the database, this script automatically monitors from the MySQL replication state, if not synchronized automatically shut down the MySQL service of this machine, Avoid affecting the entire site's normal business access. Of course, the runtime of the script will certainly need to change, from 10 minutes to the second level, this can be implemented through the while loop.