Shell scripts for automatic monitoring of master-slave MySQL synchronization

The code is as follows:

2. #!/bin/bash
4. #check Mysql_slave Status
6. #crontab Time 00:10
8. Mysqlport= ' Netstat-na|grep "LISTEN" |grep "3306" |awk-f[: ""]+ ' {print $4} '
10. mysqlip= ' ifconfig eth0|grep "inet addr" | awk-f[: ""]+ ' {print $4} '
12. status=$ (/usr/local/webserver/mysql/bin/mysql-u yuhongchun-pyuhongchun101-s/tmp/mysql.sock-e "show Slave Status\G" | Grep-i "Running")
14. Io_env= ' echo $STATUS | grep IO | awk ' {print $} '
16. Sql_env= ' echo $STATUS | grep SQL | awk ' {print $} '
18. Data= ' date + '%y-%m-%d%h:%m:%s '
22. If ["$MYSQLPORT" = = "3306"]
24. Then
26. echo "MySQL is running"
28. Else
30. Mail-s "Warn!server: $MYSQLIP MySQL is down" [email protected]
32. Fi
36. If ["$IO _env" = "yes"-a "$SQL _env" = "yes"]
38. Then
40. echo "Slave is running!"
42. Else
44. echo "####### $DATA #########" >>/data/data/check_mysql_slave.log
46. echo "Slave is not running!" >>/data/data/check_mysql_slave.log
48. echo "Slave is not running!" | Mail-s "warn! $MYSQLIP MySQL Slave is not running "[email protected]
50. Fi

Recommended to run every 10 minutes

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

Remember to assign a Yuhongchun user to each MySQL slave, the permissions are larger, but only to run locally, as follows:

1. Grant all privileges on * * to "Yuhongchun" @ "127.0.0.1" identified by "yuhongchun101";
2. 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 monitor the normal operation of MySQL;

3, slave machine IO and SQL State must be yes, indispensable, here used to judge a multiple conditions.

Script-generated background environment:

I have a lot of public network-based Web site (no hardware firewall, directly into the IDC room) do are MySQL master-slave architecture, slave main backup database and cold backup role, although from the machine down the problem is not small, 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 control of the script, designed the above scripts.

Scripting practices:

This script I have used in the production environment, we can be placed on our slave MySQL machine for monitoring, and sometimes also recommended manual check, once found that rsync--delete automatically deleted the/data/data inside the data, that is, from the database location, the script does not alarm.

Post-application:

The post-company MySQL database is prepared by a master one from the rack color upgrade into a master multi-slave, read and write separation of the architecture, LVS as a load balancer from the database, this script automatically monitors the replication state from MySQL, if unable to sync automatically turn off the native MySQL service, To avoid affecting the normal business access of the entire site. Of course, the runtime of the script must also need to change, from 10 minutes to seconds, which can be achieved through the while loop.

Shell scripts for automatic monitoring of master-slave MySQL synchronization