案例：基于Percona插件监控MySQL主从同步
安装MySQL
[root@k8s-ubuntu ~]# apt install mysql-server mysql-client
修改master配置
[root@zabbix-mysql ~]# vim /etc/mysql/mysql.conf.d/mysqld.cnf
bind-address = 0.0.0.0
server-id = 21
log-bin = /var/lib/mysql/master-log
重启
[root@zabbix-mysql ~]# systemctl restart mysql
修改slave配置
[root@mysql-slave ~]# vim /etc/mysql/mysql.conf.d/mysqld.cnf
bind-address = 0.0.0.0
server-id = 22
log-bin = /var/lib/mysql/relay-log
重启
[root@mysql-slave ~]# systemctl restart mysql.service
创建授权用户
mysql> grant REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO replication@'172.31.1.%' identified by '123456';
因为数据不是很多，所以使用mysqldump导出的方法
[root@zabbix-mysql ~]# mysqldump --all-databases --single_transaction --flush-logs --master-data=2 --lock-tables > /opt/backup.sql
拷贝到远程的slave
[root@zabbix-mysql ~]# scp /opt/backup.sql 172.31.1.22:/root/
导入数据
[root@mysql-slave ~]# mysql < /root/backup.sql
查看前50行数据
[root@zabbix-mysql ~]# head -n50 /opt/backup.sql
change master
CHANGE MASTER TO MASTER_HOST='172.31.1.21',MASTER_USER='replication',MASTER_PASSWORD='123456',MASTER_LOG_FILE='master-log.000002',MASTER_LOG_POS=154;
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.31.1.21
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000002
Read_Master_Log_Pos: 1099060
Relay_Log_File: mysql-slave-relay-bin.000002
Relay_Log_Pos: 919099
Relay_Master_Log_File: master-log.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
master安装zabbix-agent
[root@zabbix-mysql ~]# apt -y install zabbix-agent
slave安装zabbix-agent
[root@zabbix-mysql ~]# apt -y install zabbix-agent
修改agent配置（两台都一样的改配置文件）
root@zabbix-mysql-master:~# grep "^[a-Z]" /etc/zabbix/zabbix_agentd.conf
PidFile=/var/run/zabbix/zabbix_agentd.pid
LogFile=/var/log/zabbix/zabbix_agentd.log
LogFileSize=0
Server=172.31.1.20
StartAgents=5
ServerActive=172.31.1.23
Hostname=172.31.1.21
AllowRoot=1
User=root
Include=/etc/zabbix/zabbix_agentd.d/*.conf
报错
[root@zabbix-mysql ~]# systemctl start zabbix-agent
Failed to start zabbix-agent.service: Unit zabbix-agent.service is masked.
解决方法
# 解锁
[root@zabbix-mysql ~]# systemctl unmask zabbix-agent.service
Removed /etc/systemd/system/zabbix-agent.service.
[root@zabbix-mysql ~]# systemctl start zabbix-agent
[root@zabbix-mysql ~]# ss -tanl
LISTEN 0 128 [::]:10050 [::]:*
MySQL Master安装Percona
修改zabbix agent启动用户为root，包括zabbix agent配置文件和服务启动文件。
官方网站
https://www.percona.com/downloads/percona-monitoring-plugins/LATEST/
下载
[root@mysql-slave ~]# wget https://downloads.percona.com/downloads/percona-monitoring-plugins/percona-monitoring-plugins-1.1.8/binary/debian/artful/x86_64/percona-zabbix-templates_1.1.8-1.artful_all.deb
安装Percona软件包：
[root@mysql-slave ~]# dpkg -i percona-zabbix-templates_1.1.8-1.artful_all.deb
查看有什么文件
[root@mysql-slave ~]# dpkg -c percona-zabbix-templates_1.1.8-1.artful_all.deb
drwxr-xr-x root/root 0 2018-01-10 14:46 ./
drwxr-xr-x root/root 0 2018-01-10 14:46 ./usr/
drwxr-xr-x root/root 0 2018-01-10 14:46 ./usr/share/
drwxr-xr-x root/root 0 2018-01-10 14:46 ./usr/share/doc/
drwxr-xr-x root/root 0 2018-01-10 14:46 ./usr/share/doc/percona-zabbix-templates/
-rw-r--r-- root/root 360 2018-01-10 14:46 ./usr/share/doc/percona-zabbix-templates/changelog.Debian.gz
-rw-r--r-- root/root 989 2018-01-10 14:46 ./usr/share/doc/percona-zabbix-templates/copyright
drwxr-xr-x root/root 0 2018-01-10 14:46 ./var/
drwxr-xr-x root/root 0 2018-01-10 14:46 ./var/lib/
drwxr-xr-x root/root 0 2018-01-10 14:46 ./var/lib/zabbix/
drwxr-xr-x root/root 0 2018-01-10 14:46 ./var/lib/zabbix/percona/
drwxr-xr-x root/root 0 2018-01-10 14:46 ./var/lib/zabbix/percona/scripts/
-rwxr-xr-x root/root 1251 2018-01-10 14:46 ./var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh
-rw-r--r-- root/root 60679 2018-01-10 14:46 ./var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php
drwxr-xr-x root/root 0 2018-01-10 14:46 ./var/lib/zabbix/percona/templates/
-rw-r--r-- root/root 18866 2018-01-10 14:46 ./var/lib/zabbix/percona/templates/userparameter_percona_mysql.conf
-rw-r--r-- root/root 269258 2018-01-10 14:46 ./var/lib/zabbix/percona/templates/zabbix_agent_template_percona_mysql_server_ht_2.0.9-sver1.1.8.xml
拷贝文件
[root@mysql-slave ~]# cp /var/lib/zabbix/percona/templates/userparameter_percona_mysql.conf /etc/zabbix/zabbix_agentd.d/
重启zabbix-agent
[root@mysql-slave ~]# systemctl restart zabbix-agent.service
安装php环境： 目前Percona与ubuntu 自带的php 7.2不兼容，需要安装php 5.6版本
[root@mysql-slave ~]# add-apt-repository ppa:ondrej/php
更新
[root@mysql-slave ~]# apt-get -y update
安装
[root@mysql-slave ~]# apt install -y php5.6 php5.6-mysql
创建mysql认证文件：（如果root用户密码没有为空即可）
[root@mysql-slave ~]# cat /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php.cnf
<?php
$mysql_user = 'root';
$mysql_pass = '123456';
本机测试
[root@mysql-slave ~]# /var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh gg
27
zabbix-server端测试
[root@zabbix-server zabbix]# zabbix_get -s 172.31.1.22 -p 10050 -k MySQL.Key-read-requests
27
web界面添加
有模板就先导入模板
创建主机---》关联模板
案例：自定义脚本监控MySQL主从同步与同步延迟
脚本
[root@mysql-slave ~]# cat mysql_montior.sh
#!/bin/bash
#Author: xuanlv
Seconds_Behind_Master(){
NUM=`mysql -uroot -e "show slave statusG;" | grep "Seconds_Behind_Master:" | awk -F: '{print $2}'`
echo $NUM
}
master_slave_check(){
NUM1=`mysql -uroot -e "show slave statusG;" | grep "Slave_IO_Running:" | awk -F: '{print $2}' | sed 's/^[ ]*//g'`
#echo $NUM1
NUM2=`mysql -uroot -e "show slave statusG;" | grep "Slave_SQL_Running:" | awk -F: '{print $2}' | sed 's/^[ ]*//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
授权
[root@mysql-slave ~]# chmod a+x /etc/zbbix/zabbix_agentd.conf.d/mysql_montior.sh
测试脚本
[root@mysql-slave ~]# bash mysql_montior.sh Seconds_Behind_Master
配置文件zabbix-agent
[root@mysql-slave ~]# vim /etc/zabbix_agentd.conf
AllowRoot=1
User=root
UserParameter=mysql_montior[*],/etc/zabbix/zabbix_agentd.d/mysql_montior.sh "$1"
还得改system文件
[root@mysql-slave ~]# vim /lib/systemd/system/zabbix-agent.service
User=root
Group=root
重启并开机启动
[root@mysql-slave ~]# systemctl daemon-reload
[root@mysql-slave ~]# systemctl restart zabbix-agent
zabbix-server操作
[root@zabbix-server ~]# zabbix_get -s 172.31.1.22 -p 10050 -k "mysql_montior[master_slave_check]"
50
web界面添加
创建模板---》创建监控项---》创建触发器---》创建图形---主机关联模板