一、实现MySQL主从
部署mysql 主从同步,需要配置不同的server id并开启binlog
环境:MySQL 5.7.x
master 10.0.0.32
slave 10.0.0.35
zabbix server 10.0.0.31
1.1 Master配置
vim /etc/mysql/mysql.conf.d/mysqld.cnf
bind-address = 0.0.0.0
server-id = 32
log-bin = /var/lib/mysql/master-log
root@ubuntu1804:~# systemctl restart mysql.service
1.2 Slave配置
vim /etc/mysql/mysql.conf.d/mysqld.cn
bind-address = 0.0.0.0
server-id = 35
relay-log = /var/lib/mysql/relay-log
root@ubuntu1804:~# systemctl restart mysql.service
1.3 MySQL Master授权账户
创建授权账户
GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'lck'@'10.0.0.%' IDENTIFIED BY '123456';
在mysql master服务器授权账户并导出数据,然后scp到mysql backup服务器
mysqldump --all-databases --single_transaction --flush-logs --master-data=2 --lock-tables > /opt/backup.sql
scp /opt/backup.sql 10.0.0.35:/opt/
1.4 MySQL slave导入数据
mysql < /opt/backup.sql
root@ubuntu1804:/opt# head -30 /opt/backup.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='master-log.000002', MASTER_LOG_POS=154;
mysql
mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.32',MASTER_USER='lck',MASTER_PASSWORD='123456',MASTER_LOG_FILE='master-log.000002',MASTER_LOG_POS=154;
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.32
Master_User: lck
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000002
Read_Master_Log_Pos: 978181
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 586565
Relay_Master_Log_File: master-log.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
......
二、Procona 监控 MySQL
官方文档及下载地址
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 #安装教程
2.1 MySQL Master安装zabbix-agent
root@ubuntu1804:/opt# cd /usr/local/src/
root@ubuntu1804:/usr/local/src# wget wget https://repo.zabbix.com/zabbix/4.0/ubuntu/pool/main/z/zabbix-release/zabbix-release_4.0-3+bionic_all.deb
root@ubuntu1804:/usr/local/src# dpkg -i zabbix-release_4.0-3+bionic_all.deb
root@ubuntu1804:/usr/local/src# apt update
root@ubuntu1804:/usr/local/src# apt -y install zabbix-agent
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=10.0.0.31
StartAgents=5
ServerActive=127.0.0.1
Hostname=10.0.0.31
Include=/etc/zabbix/zabbix_agentd.d/*.conf
systemctl restart zabbix-agent
systemctl enable zabbix-agent
2.2 MySQL Master安装Percona
#安装Percona软件包
dpkg -i percona-zabbix-templates_1.1.8-1.artful_all.deb
cp /var/lib/zabbix/percona/templates/userparameter_percona_mysql.conf /etc/zabbix/zabbix_agentd.d/
systemctl restart zabbix-agent
#安装php环境: 目前Percona与ubuntu 自带的php 7.2不兼容,需要安装php 5.6版本
add-apt-repository ppa:ondrej/php
apt-get -y update
apt install -y php5.6 php5.6-mysql
#创建mysql认证文件:
cat /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php.cnf
<?php
$mysql_user = 'root';
$mysql_pass = '';
#测试脚本能否获取数据
/var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh gg
22
2.3 zabbix web导入Percona模板
2.4 zabbbix web添加主机
2.5 zabbix web对主机关联模板
2.6 验证MySQL监控数据
Percona模板中的监控项默认是五分钟收集一次监控项数据,会结合脚本检查agent上报错数据的文件的时间戳是
否超过五分钟,脚本位置在/var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh。
/apps/zabbix_server/bin/zabbix_get -s 172.31.0.104 -p 10050 -k "MySQL.Key-read-requests"
22
三、自定义脚本监控MySQL
3.1 MySQL slave安装zabbix agent
apt install zabbix-agent
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.0.101
StartAgents=5
ServerActive=127.0.0.1
Hostname=172.31.0.104
Include=/etc/zabbix/zabbix_agentd.d/*.conf
systemctl restart zabbix-agent
systemctl enable zabbix-agent
3.2 编写监控脚本
vim /etc/zabbix/zabbix_agentd.d/mysql_monitor.sh
#!/bin/bash
Seconds_Behind_Master(){
NUM=`mysql -uroot -e "show slave status\G;" | grep "Seconds_Behind_Master:" | awk -F: '{print $2}'`
echo $NUM
}
master_slave_check(){
NUM1=`mysql -uroot -e "show slave status\G;" | grep "Slave_IO_Running" | awk -F: '{print $2}' | sed 's/^[ \t]*//g'`
#echo $NUM1
NUM2=`mysql -uroot -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
chmod a+x mysql_monitor.sh
bash mysql_monitor.sh master_slave_check
50
3.3 zabbix agent添加自定义监控项
cd /etc/zabbix/zabbix_agentd.conf.d
root@ubuntu1804:/etc/zabbix/zabbix_agentd.d# 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=10.0.0.31,10.0.0.33
StartAgents=5
ServerActive=10.0.0.33
Hostname=10.0.0.31
AllowRoot=1
User=root
Include=/etc/zabbix/zabbix_agentd.d/*.conf
UserParameter=mysql_monitor[*],/etc/zabbix/zabbix_agentd.d/mysql_monitor.sh "$1"
root@ubuntu1804:/etc/zabbix/zabbix_agentd.d# cat /lib/systemd/system/zabbix-agent.service
User=root
Group=root
root@ubuntu1804:/usr/local/src# systemctl restart zabbix-agent
root@ubuntu1804:/usr/local/src# chmod a+x /etc/zabbix/zabbix_agentd.d/mysql_monitor.sh
3.4 zabbix server测试监控项数据
/apps/zabbix_server/bin/zabbix_get -s 172.31.0.105 -p 10050 -k "mysql_monitor[master_slave_check]"
50
3.5 自定义模板
创建模板
添加监控项
添加触发器
添加图形
3.6 模板关联至主机
3.7 验证监控数据