监控mysql
ubuntu服务器：要单独安装php5.6环境、默认7.2太新了、数据类型和模板不兼容
104主
配置主从
vim /etc/mysql/mysql.conf.d/mysqld.cnf
server-id=10
log-bin=/var/lib/mysql/master-log
#重启服务
systemctl restart mysql
105从
apt install -y mysql-server-5.7 mysql-client-5.7
vim /etc/mysql/mysql.conf.d/mysqld.cnf
server-id=105
#重启服务
systemctl restart mysql
systemctl enable mysql
104主
导出所有数据
mysqldump --all-databases --single_transaction --flush-logs --master-data=2 --lock-tables > /opt/backup.sql
拷贝到105的slave
scp /opt/backup.sql 192.168.37.105:/opt
105从
导入数据
mysql < /opt/backup.sql
104主
创建同步账号
mysql
GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'rsync-user'@'192.168.37.%' IDENTIFIED BY '123456';
flush privileges;
exit
105从
看文件前30行
head -n 30 /opt/backup.sql
...
-- CHANGE MASTER TO MASTER_LOG_FILE='master-log.000002', MASTER_LOG_POS=154;
mysql
CHANGE MASTER TO MASTER_HOST='192.168.37.104',MASTER_USER='rsync-user',MASTER_PASSWORD='123456',MASTER_LOG_FILE='master-log.000002',MASTER_LOG_POS=154;
start slave;
#看Slave_IO和Slave_SQL线程是否都为YES
show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.37.104
Master_User: rsync-user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000002
Read_Master_Log_Pos: 3438522
Relay_Log_File: ubuntu-5-relay-bin.000002
Relay_Log_Pos: 3438689
Relay_Master_Log_File: master-log.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 3438522
Relay_Log_Space: 3438899
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0 <--同步偏差值、越短越好
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 10
Master_UUID: e7b20aff-968f-11ed-ac7a-000c293f405e
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
exit
安装procona监控
下载包
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
安装
dpkg -i percona-zabbix-templates_1.1.8-1.artful_all.deb
安装依赖包
wget http://repo.zabbix.com/zabbix/4.0/ubuntu/pool/main/z/zabbix-release/zabbix-release_4.0-3%2Bbionic_all.deb
dpkg -i zabbix-release_4.0-3+bionic_all.deb
apt update && apt install zabbix-agent -y
版本太新需要用php5.6
apt-get install -y software-properties-common
sudo add-apt-repository ppa:ondrej/php
apt update
apt install php5.6-mysql -y
apt-get install -y php5.6
107
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=192.168.37.101,192.168.37.103
ListenPort=10050
ListenIP=0.0.0.0
StartAgents=3
ServerActive=192.168.37.102
Hostname=192.168.37.107
Include=/etc/zabbix/zabbix_agentd.d/*.conf
#拷贝到105
scp /etc/zabbix/zabbix_agentd.conf 192.168.37.105:/etc/zabbix/zabbix_agentd.conf
105从
vim /etc/zabbix/zabbix_agentd.conf
Server=192.168.37.101,192.168.37.102 <--
ServerActive=192.168.37.102 <--
Hostname=192.168.37.105 <--
systemctl enable zabbix-agent
systemctl restart zabbix-agent
克隆
cp /var/lib/zabbix/percona/templates/userparameter_percona_mysql.conf /etc/zabbix/zabbix_agentd.d/
vim /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
43 <--
chown zabbix.zabbix /tmp/localhost-mysql_cacti_stats.txt
systemctl restart zabbix-agent
101
systemctl restart zabbix-agent
zabbix_get -s 192.168.37.105 -p 10050 -k "MySQL.Key-read-requests"
43
105从
导出模板
cd /var/lib/zabbix/percona/templates/
sz zabbix_agent_template_percona_mysql_server_ht_2.0.9-sver1.1.8.xml
导入模板
105
cd /var/lib/zabbix/percona/scripts/
#时间改短
vim get_mysql_stats_wrapper.sh
DIR=`dirname $0`
CMD="/usr/bin/php -q $DIR/ss_get_mysql_stats.php --host $HOST --items gg"
CACHEFILE="/tmp/$HOST-mysql_cacti_stats.txt"
if [ "$ITEM" = "running-slave" ]; then
# Check for running slave
RES=`HOME=~zabbix mysql -e 'SHOW SLAVE STATUS\G' | egrep '(Slave_IO_Running|Slave_SQL_Running):' | awk -F: '{print $2}' | tr '\n' ','`
if [ "$RES" = " Yes, Yes," ]; then
echo 1
else
echo 0
fi
exit
elif [ -e $CACHEFILE ]; then
# Check and run the script
TIMEFLM=`stat -c %Y /tmp/$HOST-mysql_cacti_stats.txt`
TIMENOW=`date +%s`
if [ `expr $TIMENOW - $TIMEFLM` -gt 5 ]; then <--时间改短
sudo rm -f $CACHEFILE
$CMD 2>&1 > /dev/null
fi
else
$CMD 2>&1 > /dev/null
fi
# Parse cache file
if [ -e $CACHEFILE ]; then
cat $CACHEFILE | sed 's/ /\n/g; s/-1/0/g'| grep $ITEM | awk -F: '{print $2}'
else
echo "ERROR: run the command manually to investigate the problem: $CMD"
fi
关联
105
更改权限
#注释掉下面两项后、会root启动zabbix-agent
vim /lib/systemd/system/zabbix-agent.service
#User=zabbix
#Group=zabbix
vim /etc/sudoers
zabbix ALL=(ALL) NOPASSWD: ALL <--zabbix可以执行特殊命令
vim /etc/zabbix/zabbix_agentd.conf
Timeout=30 <--超时时间
AllowRoot=1 <--
User=root <--
#重启服务
systemctl daemon-reload
systemctl restart zabbix-agent
#可以看到是root启动zabbix服务
ps -ef|grep zabbix
root 26259 1 0 12:09 ? 00:00:00 /usr/sbin/zabbix_agentd -c /etc/zabbix/zabbix_agentd.conf
root 26261 26259 0 12:09 ? 00:00:00 /usr/sbin/zabbix_agentd: collector [idle 1 sec]
root 26262 26259 0 12:09 ? 00:00:00 /usr/sbin/zabbix_agentd: listener #1 [waiting for connection]
root 26263 26259 0 12:09 ? 00:00:00 /usr/sbin/zabbix_agentd: listener #2 [waiting for connection]
root 26264 26259 0 12:09 ? 00:00:00 /usr/sbin/zabbix_agentd: listener #3 [waiting for connection]
root 26265 26259 0 12:09 ? 00:00:00 /usr/sbin/zabbix_agentd: active checks #1 [idle 1 sec]
root 26370 1648 0 12:10 pts/0 00:00:00 grep --color=auto zabbix
cd /var/lib/zabbix/percona/scripts
bash -x get_mysql_stats_wrapper.sh gg
101
zabbix_get -s 192.168.37.105 -p 10050 -k "MySQL.Key-read-requests"
29 <--
105
相当于进入mysql后、执行show slave status\G;后、过滤出来Seconds_Behind_Master的值
mysql -uroot -hlocalhost -e "show slave status\G;" |grep "Seconds_Behind_Master:" |awk -F: '{print $2}'
0
cd /etc/zabbix/zabbix_agentd.d/
#脚本
vim 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
#添加权限
chmod a+x mysql_monitor.sh
#编辑配置文件
vim /etc/zabbix/zabbix_agentd.conf
UserParameter=mysql_monitor[*],/etc/zabbix/zabbix_agentd.d/mysql_monitor.sh "$1"
#重启服务
systemctl restart zabbix-agent
创建图形
创建触发器
关联主机
105
mysql
stop slave; <--值会升到100、看下图、如果再次启动start slave;会恢复
值升到100
