Monitor MySQL master-slave synchronization

1.shell Monitor Master-Slave synchronization

Write a shell script that monitors Slave's two yes (Slave_io and slave_sql processes), and if only one or 0 Yes is found, it indicates a master-slave problem.

___________________________________________________________

vim /sh/mysql_slave.sh#!/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/mysql/bin/mysql -u szk5043 -pszk5043 -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 " [email protected]fiif [  "$IO _env"  =  "yes"  -a  "$SQL _env"  =  "yes"  ]       then   echo  "slave is running!"       else   echo  "#######  $date  #########" > > /tmp/check_mysql_slave.log   echo  "slave is not running!"  >> /tmp/check_mysql_slave.log   mail -s  "warn!  $MySQLIP _ Replicate_error " [email protected]fi is recommended to run every 10 minutes. */10 * * * * root /bin/sh /sh/mysql_slave.sh   Remember to assign a szk5043 user to each MySQL slave, the privilege is not OK, only limited to run locally, as follows: Mysql>grant all privileges on *.*  to [email protected] identified by  ' szk5043 ';mysql>grant all  privileges on *.* to [email protected] identified by  ' szk5043 ';

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;

2.zabbix Monitor Master-Slave synchronization

Write a shell script that monitors Slave's two yes (Slave_io and slave_sql processes), and if only one or 0 Yes is found, it indicates a master-slave problem.

___________________________________________________________

A. First, assign a monitor account to MySQL.

Mysql> Grant replication Client on *. * to ' Zabbix ' @ ' localhost ';

B. Write a script

Vim/sh/mysql-replication.sh#!/bin/bash/usr/local/mysql/bin/mysql-uzabbix-e ' show slave status\g ' |grep-e ' Slave_IO_ Running| Slave_sql_running "|awk ' {print $} ' |grep-c Yes

#先用zabbix这个用户来获取slave的所有状态, then grep out the two states, then output the second column, and finally look at a few yes states, normally with two yes states.

In fact, we will find that there is only one command, but I wrote it into a script, here I say, because if you write the command in the configuration file, every time you modify, you have to restart the agent, so write to the script is more convenient debugging changes, this should be a good habit.

c. Adding monitoring items to the agent

Add the following line at the end of the file

Vim/etc/zabbix_agentd.conf userparameter=mysql.replication,/home/zabbix/mysql-replication.sh

#等号后面的字符串中, the comma is preceded by the key, followed by the executed script (the script does not forget to execute the permissions), after the addition, restart the agent program

d. Server Side Add Monitoring item

Zabbix_get-s 192.168.1.202-p10050-k "Mysql.replication" 2

#这里的192.168.1.202 is the IP of my agent, if the master-slave copy is normal, it returns 2 (representing two states are Yes)

Now it means that the server is ready to get the status from the agent.

Now add the monitoring item in the Admin interface:

System Configuration-"host-" Select the monitoring items for the host you want to monitor

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M01/7E/E6/wKioL1cMm8_AWUtXAACxTwplByc412.png "style=" float: none; "title=" 1.png "alt=" Wkiol1cmm8_awutxaacxtwplbyc412.png "/>

In the top right corner, select Create Project

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M02/7E/E6/wKioL1cMm9Cg60NLAABQ0rebcC0909.png "style=" float: none; "title=" 2.png "alt=" Wkiol1cmm9cg60nlaabq0rebcc0909.png "/>

Create a Trigger

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M00/7E/E9/wKiom1cMmxvhXoj-AABKKjhNlzQ606.png "style=" float: none; "title=" 3.png "alt=" Wkiom1cmmxvhxoj-aabkkjhnlzq606.png "/>

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M02/7E/E6/wKioL1cMm9CiQ_BEAAAz2C5KRds637.png "style=" float: none; "title=" 4.png "alt=" Wkiol1cmm9ciq_beaaaz2c5krds637.png "/>

E. Test

[Email protected] ~]#/etc/init.d/mysqld stopshutting down MySQL. success!

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M00/7E/E9/wKiom1cMmxzgPM_GAAC1DnnHoMc201.png "style=" float: none; "title=" 5.png "alt=" Wkiom1cmmxzgpm_gaac1dnnhomc201.png "/>

#可以设置相应的邮件或短信告警

This article is from the "Fuqin Wine" blog, please make sure to keep this source http://szk5043.blog.51cto.com/8456440/1762978

Monitor MySQL master-slave synchronization