（八）zabbix----监控Mysql，安装procona，

一. 部署mysql 主从同步

需要配置不同的server id并开启bin log
Master mysql：

[root@zabbix-web1 apps]# cat /etc/my.cnf
[mysqld] 
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
datadir=/data/mysql
innodb_file_per_table=1
server-id=10
log-bin=/data/mysql/master-log

[client]
port=3306
socket=/var/lib/mysql/mysql.sock

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/tmp/mysql.sock

重启并设置：
/etc/init.d/mysqld restart 

	同步账户
GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'rsync-user'@'192.168.15.%' IDENTIFIED BY '123456';

Slave mysql：

[root@zabbix-serversrc]# cat /etc/my.cnf
[mysqld]
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
datadir=/data/mysql
innodb_file_per_table=1
relay-log = /data/mysql
server-id=20

[client]
port=3306
socket=/var/lib/mysql/mysql.sock

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/tmp/mysql.sock

# /etc/init.d/mysqld restart

导出master的mysql 数据：

mysqldump --all-databases --single_transaction --flush-logs --master-data=2 --locktables > /opt/backup.sql

复制到slave并导入，Position位置在sql文件：

[root@zabbix-serversrc] mysql < /opt/backup.sql
mysql> CHANGE MASTER TO MASTER_HOST='192.168.15.203',MASTER_USER='rsync-user',MASTER_PASSWORD='123456',MASTER_LOG_FILE='bin-log.000003',MASTER_LOG_POS=120;

start slave;
stop slave; 
reset slave;

show slave status\G

二. 如何监控mysql

1.安装procona

专门用于监控slave的状态
参考

官方文档及下载地址：
插件地址Monitoring
https://www.percona.com/doc/percona-monitoring-plugins/LATEST/zabbix/index.html
下载地址
https://www.percona.com/downloads/

安装教程
https://www.percona.com/doc/percona-monitoring-plugins/LATEST/zabbix/index.html#installation-instructions
本地安装:

#安装php，需要php环境
yum install php php-mysql –y 
rpm -ivh percona-zabbix-templates-1.1.8-1.noarch.rpm #安装模板文件

cp /var/lib/zabbix/percona/templates/userparameter_percona_mysql.conf /usr/local/zabbix/etc/zabbix_agentd.conf.d/ 

vim /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php.cnf
<?php
$mysql_user = 'root';
$mysql_pass = '123456';

测试数据

重启zabbix agent

并在Zabbix Server导入模板zbx_mysql_export_templates
更改模板模式为主动模式，关联到主机
验证数据：

1. 注：如果无法获取数据，就要查看/tmp目录是否有一个localhost-mysql_cacti_stats.txt 这样的类似文件，如果有的话就把权限改为zabbix用户和用户组：

chown zabbix.zabbix /tmp/localhost-mysql_cacti_stats.txt

测试

zabbix_get -s 192.168.99.24 -p 10050 -k "MySQL.Key-read-requests"

自定义监控数据库

编写脚本监控脚本MySQL主从同步及延迟

脚本内容：
脚本 mysql_monitor.sh

#!/bin/bash
Seconds_Behind_Master(){
	NUM=`mysql -uroot -hlocalhost   -e "show slave status\G;"  | grep "Seconds_Behind_Master:" | awk -F: '{print $2}'`
	echo $NUM
}

master_slave_check(){
NUM1=`mysql -uroot -hlocalhost   -e "show slave status\G;"  | grep "Slave_IO_Running" | awk -F:  '{print $2}' | sed 's/^[ \t]*//g'`
#echo $NUM1
NUM2=`mysql -uroot -hlocalhost   -e "show slave status\G;"  | grep "Slave_SQL_Running:" | awk -F:  '{print $2}' | sed 's/^[ \t]*//g'`
#echo $NUM2
if test $NUM1 == "Yes" &&  test $NUM2 == "Yes";then
    echo 50
else
    echo 100
fi
}

main(){
    case $1 in
    Seconds_Behind_Master)
        Seconds_Behind_Master;
        ;;
	master_slave_check)
	   	master_slave_check;
	   ;;
    esac
}
main $1

导入自定义配置:

vim ../zabbix_agentd.conf
263 Include=/usr/local/zabbix/etc/zabbix_agentd.conf.d/*.conf

自定义监控项：

vim all.conf
UserParameter=mysql_monitor[*],/usr/local/zabbix/etc/zabbix_agentd.conf.d/mysql_monitor.sh "$1"

确认一上配置无误后重启zabbix agentd：

systemctl restart zabbix_agentd

web界面加入-模板监控

可以导入一个自定义模板zbx_mysql_export_templates.xml然后关联
下面这个是创建

关联主机
验证