zabbix监控mysql

一、mysql数据库监控的内容有

mysql的吞吐量
mysql的常规操作（增删改查）
QPS（Questions Per second:）每秒能处理多少次请求数
TPS（Transactions Per Second）每秒查询处理的事务数
mysql库大小和表大小
监控innodb缓冲池的read命中率

mkdir /etc/zabbix/scripts/
cd /etc/zabbix/scripts/
vim mysql.sh
#!/bin/bash
case $1 in#mysql吞吐量#流量发送Bytes_sent)mysqladmin extended-status|grep "Bytes_sent"|awk '{print $4}';;#流量接收Bytes_received)mysqladmin extended-status|grep "Bytes_received"|awk '{print $4}';;#mysql数据库增删改查#增Com_insert)mysqladmin extended-status|grep -w "Com_insert"|awk '{print $4}';;#删mysqladmin extended-status|grep Com_delete|sed -n '1p'|awk '{print $4}';;#改mysqladmin extended-status|grep Com_update|sed -n '1p'|awk '{print $4}';;#查mysqladmin extended-status|grep Com_select|awk '{print $4}';;esac

第二种：

#!/bin/bashuptime=`mysqladmin status|awk '{print $2}'`#echo "$uptime"case $1 inmysql_slave)#主从状态mysql -uqq -p123 -e "show slave status\G;"|grep Running | grep Running | awk -F ':' '{print $2}' | wc -l;;Byte_sent) #发送量mysqladmin extended-status|grep Byte|sed -n '2p'|awk '{print $4}';;Byte_received)#接收量mysqladmin extended-status|grep Bytes_re|awk '{print $4}';;Com_select)#查mysqladmin extended-status|grep Com_select|awk '{print $4}';;Com_telete)#删mysqladmin extended-status|grep Com_delete|sed -n '1p'|awk '{print $4}';;Com_insert)#增mysqladmin extended-status|grep Com_insert|sed -n '1p'|awk '{print $4}';;Com_update)#改mysqladmin extended-status|grep Com_update|sed -n '1p'|awk '{print $4}';;qps)#每秒请求次数的数量qu=`mysqladmin status|awk '{print $6}'`qps=`echo "scal=2;$qu/$uptime"|bc`echo "$qps";;tps)#每秒处理事务的数量commit=`mysqladmin extended-status|grep -w Com_commit|awk '{print $4}'`rollback=`mysqladmin extended-status|grep -w Com_rollback|sed -n '1p'|awk '{print $4}'`tps_tmp1=$((${commit}+${rollback}))tps=$(echo "scale=2;${tps_tmp1}/$uptime"|bc)echo "$tps";;database_size)#库的大小mysql -e "use information_schema;select round(DATA_LENGTH/1024/1024,2) from tables where TABLE_SCHEMA ='mysql';"|sed -n '9p';;tables_size)#表的大小mysql -e "use information_schema; select round(DATA_LENGTH/1024/1024,2) from tables where TABLE_SCHEMA='mysql' and TABLE_NAME='user';"|sed -n '2p';;esac

第三种：

#!/bin/bash # MySQL连接参数 
MYSQL_HOST="your-mysql-host"
MYSQL_USER="your-mysql-user"
MYSQL_PASSWORD="your-mysql-password"# 获取数据库吞吐量 
吞吐量() {local qps qps=$(mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -p "$MYSQL_PASSWORD" -e "SHOW STATUS LIKE 'Queries'" | tail -n +2 | awk '{print $2}')echo "QPS: $qps"
}# 获取数据库TPS 
tps() {local tps tps=$(mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -p "$MYSQL_PASSWORD" -e "SHOW STATUS LIKE 'Com_commit'" | tail -n +2 | awk '{print $2}')echo "TPS: $tps"
}# 获取库大小和表大小 
库表大小() {local total_size table_size total_size=$(mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -p "$MYSQL_PASSWORD" -e "SELECT SUM(data_length + index_length) / 1024 / 1024 AS Total FROM information_schema.tables WHERE table_schema = 'your-database-name';")table_size=$(mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -p "$MYSQL_PASSWORD" -e "SELECT table_name, data_length + index_length AS Size FROM information_schema.tables WHERE table_schema = 'your-database-name';")echo "Total database size: $total_size MB"echo "Table sizes:"echo "$table_size"
}# 监控InnoDB缓冲池的read命中率 
缓冲池命中率() {local buffer_pool_info buffer_pool_info=$(mysql -h "$MYSQL_HOST" -u "$MYSQL_PASSWORD" -p "$MYSQL_PASSWORD" -e "SHOW ENGINE INNODB STATUS;" | grep "Buffer pool and memory")echo "InnoDB Buffer Pool Info: $buffer_pool_info"
}# 执行监控 
吞吐量 
tps 
库表大小 
缓冲池命中率 

1、模板数据配置文件

复制以下内容到/etc/zabbix/zabbix_agentd.d/template_db_mysql.conf中

#template_db_mysql.conf created by Zabbix for "Template DB MySQL" and Zabbix 5.0
#For OS Linux: You need create .my.cnf in zabbix-agent home directory (/var/lib/zabbix by default) 
#For OS Windows: You need add PATH to mysql and mysqladmin and create my.cnf in %WINDIR%\my.cnf,C:\my.cnf,BASEDIR\my.cnf https://dev.mysql.com/doc/refman/5.7/en/option-files.html
#The file must have three strings:
#[client]
#user='zbx_monitor'
#password='<password>'
#UserParameter=mysql.ping[*], mysqladmin -h"$1" -P"$2" ping
UserParameter=mysql.get_status_variables[*], mysql -h"$1" -P"$2" -sNX -e "show global status"
UserParameter=mysql.version[*], mysqladmin -s -h"$1" -P"$2" version
UserParameter=mysql.db.discovery[*], mysql -h"$1" -P"$2" -sN -e "show databases"
UserParameter=mysql.dbsize[*], mysql -h"$1" -P"$2" -sN -e "SELECT COALESCE(SUM(DATA_LENGTH + INDEX_LENGTH),0) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$3'"
UserParameter=mysql.replication.discovery[*], mysql -h"$1" -P"$2" -sNX -e "show slave status"
UserParameter=mysql.slave_status[*], mysql -h"$1" -P"$2" -sNX -e "show slave status"

2 、查看zabbix_agent配置文件中 Include参数

3 、创建数据库监控用户

mysql -uroot -p你的mysql密码
CREATE USER '监控用户名'@'%' IDENTIFIED BY '监控用户密码'; 
GRANT USAGE,REPLICATION CLIENT,PROCESS,SHOW DATABASES,SHOW VIEW ON *.* TO '监控用户名'@'%';

4 、创建步骤3登录用户名，密码配置文件

mkdir /var/lib/zabbixvim /var/lib/zabbix/.my.cnf# 添加如下文件
[client]
user='监控用户名'
password='监控用户密码'

二、zabbix监控mysql四大性能指标

查询吞吐量  
查询执行性能  
连接情况 
缓冲池使用情况

1、吞吐量

Com_select：select查询语句个数/每秒
Com_insert：insert操作语句个数/每秒
Com-update：update操作语句个数/每秒
Com-delete：delete操作语句个数/每秒
Questions:查询语句总量mysqladmin  -uread -p123456 -h47.98.97.124  extended-status | grep Com_select 
| Com_select                               | 18484       |mysqladmin  -uread -p123456 -h47.98.97.124  extended-status | grep Com_update
| Com_update                               | 3000        |mysqladmin  -uread -p123456 -h47.98.97.124  extended-status | grep Com_insert
| Com_insert                               | 1054        |mysqladmin  -uread -p123456 -h47.98.97.124  extended-status | grep Com_delete
| Com_delete                               | 198         |mysqladmin  -uread -p123456 -h47.98.97.124  extended-status | grep Questions 
| Questions                                | 723868      |注：
1. Writes = Com_insert + Com_update + Com_delete，如果在监控mysql吞吐量写操作的时候可以将insertupdate、delete数值进行合并。 
2. Questions是反映当前查询数量，设置告警非常必要，查询量的骤降，可能就预示着某个严重的问题。

2、查询性能

Slow_queries:慢查询数量mysqladmin  -uread -p123456 -h47.98.97.124  extended-status | grep Slow_queries
| Slow_queries                             | 0           |

3、连接情况

Threads_connected:当前打开的数据库连接数
max_connections:数据库当前的最大连接数mysqladmin  -uread -p123456 -h47.98.97.124  extended-status | grep -i connect
| Aborted_connects                         | 0           |
| Connections                              | 572         |
| Max_used_connections                     | 4           |
| Ssl_client_connects                      | 0           |
| Ssl_connect_renegotiates                 | 0           |
| Ssl_finished_connects                    | 0           |
| Threads_connected                        | 2           |检查并设置连接限制监控客户端连接情况相当重要，因为一旦可用连接耗尽，新的客户端连接就会遭到拒绝。MySQL 默认的连接数限制为 151。查询最大连接数
方法一：
MariaDB [(none)]> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+方法二：
mysql -uread -p123456 -h47.98.97.124 -e "show variables like 'max_connections'"|grep max_connections
max_connections 151修改连接数
MariaDB [(none)]> set global max_connections = 200;
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 200   |
+-----------------+-------+
1 row in set (0.00 sec)

三、编写一下zabbix客户端的脚本

脚本内容如下：

#!/bin/bash
# 2024年5月21日 13:13:32
# Authoed by pzz
# Used to get the MySQL parameter
CONNECTION1="mysqladmin -uroot -proot status"
CONNECTION2="mysqladmin -uroot -proot extended-status"
if [ $# -ne 1 ];thenecho "arg error,there should be one arg!"
elsecase $1 inuptime)result=`$CONNECTION1 | awk '{print $2}'`;;threads)result=`$CONNECTION1 | awk '{print $4}'`;;slow_queries)result=`$CONNECTION1 | awk '{print $9}'`;;avg_time)result=`$CONNECTION1 | awk '{print $22}'`;;bytes_sent)result=`$CONNECTION2 | grep "Bytes_sent" |awk '{print $4}'`;;*)echo "Usage:$0(uptime|threads|slow_queries|avg_time|bytes_sent)"esacecho $result
fi

脚本完成后，我们来测试一下脚本的可行性，结果如下：

可以看出，我们编写的脚本工作正常，可以运行并检测MySQL的一些性能参数。然后我们在zabbix server端上测试一下，结果如下：

可以看出，zabbix server端也运行正常。

1、zabbix web端配置

接下来，我们就可以进行zabbix web端的配置了。大致流程如下：
添加模板——添加监控项——将模板添加到主机上——添加图形。
选择配置——主机，点击图形，如下所示：

进入图形页面后，点击右上角的添加图形页面，并填写名称、以及监控项等内容，结果如下：

完成上述添加后，我们点击下方的“添加”就可以把刚才我们的自定义图形添加上去了，添加完成后的页面如下所示：

2、图形查看

最后，我们来查看一下刚才配置的结果。进入监控——图形页面，选择我们要监控的图形，结果如下：

可以看出，我们已经能够通过自定义的监控项来显示图形了，我们的zabbix监控MySQL性能实战成功！
 

四、监控MySQL主从

1、在slave主机上配置脚本

[root@slave script]# cd /etc/zabbix/
[root@slave zabbix]# ls
zabbix_agentd.conf  zabbix_agentd.d
[root@slave zabbix]# mkdir script
[root@slave zabbix]# cd script/
[root@slave script]# vim mysql_slave_status.sh
[root@slave script]# cat mysql_slave_status.sh 
#!/bin/bash
USER="root"
PASSWD="123"
NAME=$1function IO {Slave_IO_Running=`mysql -u $USER -p$PASSWD -e "show slave status\G;" 2> /dev/null |grep Slave_IO_Running |awk '{print $2}'`if [ $Slave_IO_Running == "Connecting" ];thenecho 0 elseecho 1 fi
}function SQL {Slave_SQL_Running=`mysql -u $USER -p$PASSWD -e "show slave status\G;" 2> /dev/null |grep Slave_SQL_Running: |awk '{print $2}'`
if [ $Slave_SQL_Running == "Yes" ];then
echo 0 elseecho 1 fi}case $NAME inio)IO;;sql)SQL;;*)echo -e "Usage: $0 [io | sql]"
esac
[root@slave script]# chmod +x mysql_slave_status.sh 
[root@slave script]# chown zabbix.zabbix mysql_slave_status.sh 
[root@slave script]# ll
总用量 4
-rwxr-xr-x 1 zabbix zabbix 631 9月   7 16:31 mysql_slave_status.sh

2、编写一个自配置文件，里面指定上面编写的脚本的路径，然后重启服务

[root@slave script]# cd /etc/zabbix/zabbix_agentd.d/
[root@slave zabbix_agentd.d]# ls
[root@slave zabbix_agentd.d]# vim userparameter_mysql_slave.conf
UserParameter=mysql.slave[*],/etc/zabbix/script/mysql_slave_status.sh $1自定义数值[*代表可用环境变量执行]       接脚本路径        可变量参数
[root@slave zabbix_agentd.d]# chown -R zabbix.zabbix /etc/zabbix/zabbix_agentd.d/userparameter_mysql_slave.conf 
[root@slave zabbix_agentd.d]# ll
总用量 4
-rw-r--r-- 1 zabbix zabbix 73 9月   7 16:36 userparameter_mysql_slave.conf
[root@slave zabbix_agentd.d]# systemctl restart zabbix-agent.service

3、去zabbix server验证状态，使用zabbix_get命令验证，需要先下载zabbix-get软件包

[root@server ~]# yum -y install zabbix-get
Last metadata expiration check: 0:22:40 ago on Wed 07 Sep 2022 04:15:48 PM CST.
Package zabbix-get-4.4.10-1.el8.x86_64 is already installed.
Dependencies resolved.
Nothing to do.
Complete!
[root@server ~]# zabbix_get -s 192.168.47.200 -k mysql.slave[sql]
0
[root@server ~]# zabbix_get -s 192.168.47.200 -k mysql.slave[io]
0

4、在zabbix web平台配置

新建监控项


新建触发器

6、测试验证，将mysql主从关闭，查看zabbix告警信息，验证邮箱是否能接收到邮件

mysql -u root -predhat -e "stop slave;"
[root@slave zabbix_agentd.d]# mysql -uroot -p123 -e"stop slave;"
[root@slave zabbix_agentd.d]# mysql -uroot -p123 -e"show slave status\G;"
*************************** 1. row ***************************Slave_IO_State: Master_Host: masterMaster_User: userMaster_Port: 3306Connect_Retry: 60Master_Log_File: Read_Master_Log_Pos: 4Relay_Log_File: mariadb-relay-bin.000001Relay_Log_Pos: 4Relay_Master_Log_File: Slave_IO_Running: NoSlave_SQL_Running: No

六、zabbix监控主从延迟

1、配置库脚本

[root@slave zabbix_agentd.d]# cd /etc/zabbix/script/
[root@slave script]# ls
mysql_slave_status.sh
[root@slave script]# vim mysql_delay.sh#!/bin/bash     
delay=$(mysql -uroot -p123 -e 'show slave status\G' 2> /dev/null | grep 'Seconds_Behind_Master' | awk '{print $2}')
if [ $delay == "NULL" ];then
echo 0
elif [ $delay -ge 0 ] && [ $delay -le 200 ];then         
echo 0
else
echo $delay
fi
[root@slave script]# chown -R zabbix.zabbix mysql_delay.sh 
[root@slave script]# chmod +x mysql_delay.sh 
[root@slave script]# ll
总用量 8
-rwxr-xr-x 1 zabbix zabbix 258 9月   7 17:37 mysql_delay.sh

2、配置agentd文件，并重启服务

[root@slave script]# vim /etc/zabbix/zabbix_agentd.d/userparameter_mysql_slave.conf UserParameter=mysql.slave[*],/etc/zabbix/script/mysql_slave_status.sh $1
UserParameter=check_mysql_delay,/bin/bash /etc/zabbix/script/mysql_delay.sh
[root@slave script]# systemctl restart zabbix-agent.service 

3、zabbix server主机进行脚本测试

[root@slave script]# ./mysql_delay.sh 
0[root@server ~]# zabbix_get -s 192.168.47.200 -k check_mysql_delay
0

4、在zabbix web平台操作

添加监控项