1、定义每个实例的端口
cat mysql_port
3325
3346
2、给所有实例创建一个zabbix用户只允许本地访问,生产环境具体权限各位看官自己看着给吧O(∩_∩)O
登陆:
@shtsmysql02 script]# mysql -u root -p -S /tmp/mysql3346.sock
MySQL [(none)]> GRANT ALL PRIVILEGES ON *.* TO zabbix@’127.0.0.1′ IDENTIFIED BY ‘zabbix’;
Query OK, 0 rows affected (0.01 sec)
MySQL [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
3、创建自动发现MySQL端口脚本
cat /opt/zabbixscript/mysqlzabbix/discovery_mysql.sh
#!/bin/bash
# ——————————————————————————-
# Script_name: discovery_mysql.sh
# Vision: 1.0
# ——————————————————————————-
res=`cat /opt/zabbixscript/mysqlzabbix/mysqlport|grep -v "^#"`
port=($res)
printf ‘{\n’
printf ‘\t"data":[\n’
for key in ${!port[@]}
do
if [[ "${#port[@]}" -gt 1 && "${key}" -ne "$((${#port[@]}-1))" ]];then
printf ‘\t {\n’
printf "\t\t\t\"{#MYSQLPORT}\":\"${port[${key}]}\"},\n"
else [[ "${key}" -eq "((${#port[@]}-1))" ]]
printf ‘\t {\n’
printf "\t\t\t\"{#MYSQLPORT}\":\"${port[${key}]}\"}\n"
fi
done
printf ‘\t ]\n’
printf ‘}\n’
4、创建MySQL检测脚本
# cat /data/PRG/zabbix/scripts/check_Multimysql.sh
#!/bin/bash
# ——————————————————————————-
# FileName: check_Multimysql.sh
# Vision: 1.0
# ——————————————————————————-
# 用户名
MYSQL_USER=’zabbix’
# 密码
MYSQL_PWD=’zabbix’
# 主机地址/IP
MYSQL_HOST=’127.0.0.1′
# 端口
MYSQL_PORT=$2
# 数据连接
MYSQL_CONN="/usr/bin/mysqladmin -u${MYSQL_USER} -p${MYSQL_PWD} -h${MYSQL_HOST} -P${MYSQL_PORT}"
##help函数
help() {
echo "Usage:$0 [ping|Uptime|Com_update|Slow_queries|Com_select|Com_rollback|Questions|Com_insert|Com_delete|Com_commit|Bytes_sent|Bytes_received|Com_begin] port"
}
# 参数是否正确
if [ $# -lt "2" ];then
echo "参数缺失!"
help
exit 2
fi
# 获取数据 脚本是错的需要修证
case $1 in
ping)
result=`${MYSQL_CONN} ping | grep -c alive`
echo $result
;;
Uptime)
result=`${MYSQL_CONN} status|cut -f2 -d":"|cut -f1 -d"T"`
echo $result
;;
Com_update)
result=`${MYSQL_CONN} extended-status |grep -w "Com_update"|cut -d"|" -f3`
echo $result
;;
Slow_queries)
result=`${MYSQL_CONN} status |cut -f5 -d":"|cut -f1 -d"O"`
echo $result
;;
Com_select)
result=`${MYSQL_CONN} extended-status |grep -w "Com_select"|cut -d"|" -f3`
echo $result
;;
Com_rollback)
result=`${MYSQL_CONN} extended-status |grep -w "Com_rollback"|cut -d"|" -f3`
echo $result
;;
Questions)
result=`${MYSQL_CONN} status|cut -f4 -d":"|cut -f1 -d"S"`
echo $result
;;
Com_insert)
result=`${MYSQL_CONN} extended-status |grep -w "Com_insert"|cut -d"|" -f3`
echo $result
;;
Com_delete)
result=`${MYSQL_CONN} extended-status |grep -w "Com_delete"|cut -d"|" -f3`
echo $result
;;
Com_commit)
result=`${MYSQL_CONN} extended-status |grep -w "Com_commit"|cut -d"|" -f3`
echo $result
;;
Bytes_sent)
result=`${MYSQL_CONN} extended-status |grep -w "Bytes_sent" |cut -d"|" -f3`
echo $result
;;
Bytes_received)
result=`${MYSQL_CONN} extended-status |grep -w "Bytes_received" |cut -d"|" -f3`
echo $result
;;
Com_begin)
result=`${MYSQL_CONN} extended-status |grep -w "Com_begin"|cut -d"|" -f3`
echo $result
;;
*)
help
;;
esac
——————验证用户———————
MariaDB [mysql]> select user,host from user where user= "zabbix"
-> ;
+——–+———–+
| user | host |
+——–+———–+
| zabbix | 127.0.0.1 |
+——–+———–+
1 row in set (0.00 sec)
MariaDB [mysql]>
—————-验证脚本——————–
[root@shtsapp03 ~]# zabbix_get -s 192.168.9.62 -k mysqlmulti.ping[3325]
1
————————–多实例多版本修改—————————
以上脚本多版本多实例服务不好用需要进行修改
连接串按这列格式进行修改
[root@shtsmysql02 mysqlzabbix]# mysql -uzabbix -pzabbix -h127.0.0.1 -P3346 -S /tmp/mysql3346.sock -e status
————–
mysql Ver 15.1 Distrib 5.5.56-MariaDB, for Linux (x86_64) using readline 5.1
Connection id: 310364
Current database:
Current user: zabbix@127.0.0.1
SSL: Not in use
Current pager: stdout
extended-status: 显示mysqld的所有服务器变量和他们的当前值
mariadb不支持 extended-status 所以脚本也要改
[root@shtsmysql02 mysqlzabbix]# mysql -uzabbix -pzabbix -h127.0.0.1 -P3346 -S /tmp/mysql3346.sock -e extended-status
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘extended-status’ at line 1
修改成如下命令
[root@shtsmysql02 mysqlzabbix]# mysqladmin -uzabbix -pzabbix -h127.0.0.1 -P3346 -S /tmp/mysql3346.sock extended-status
+———————————————–+———————+
| Variable_name | Value |
+———————————————–+———————+
| Aborted_clients | 10 |
按测试结果来看。mysql 可以替换成mysqladmin命令
[root@shtsmysql02 mysqlzabbix]# mysqladmin -uzabbix -pzabbix -h127.0.0.1 -P3346 -S /tmp/mysql3346.sock status
Uptime: 1863765 Threads: 5 Questions: 4422 Slow queries: 0 Opens: 167 Flush tables: 1 Open tables: 115 Queries per second avg: 0.002
[root@shtsmysql02 mysqlzabbix]#
————————–多实例多版本修改—————————
5、测试脚本是否正常
mysqlzabbix/discovery_mysql.sh
{
"data":[
{
"{#MYSQLPORT}":"3325"},
{
"{#MYSQLPORT}":"3346"}
]
}
check_Mmysql.sh脚本测试结果
[root@ ]# sh mysql /check_Mmysql.sh Bytes_sent 3346
1726305
[root@ ]# sh mysql /check_Mmysql.sh Bytes_received 3346
370292
[root@ ]# sh mysql /check_Mmysql.sh Com_begin 3346
6、zabbix agent配置文件添加自定义脚本,并重启zabbix agent
vim /data/PRG/zabbix/etc/zabbix_agentd.conf
UserParameter=mysql_discovery[*],/bin/bash /opt/zabbixscript/mysqlzabbix/discovery_mysql.sh
#####$1==command $2===port
UserParameter=mysql.status[*],/opt/zabbixscript/mysqlzabbix/check_Mmysql.sh $1 $2
UserParameter=mysql.ping[*],/opt/zabbixscript/mysqlzabbix/check_Mmysql.sh ping $1
/etc/init.d/zabbix_agentd restart
7、使用zabbix_get检查定义的key是否生效
# /usr/local/zabbix/bin/zabbix_get -s 192.168.62.200 -k mysql.ping[3307]
1
# /usr/local/zabbix/bin/zabbix_get -s 192.168.62.200 -k mysql.status[Uptime,3307]
18180813
# /usr/local/zabbix/bin/zabbix_get -s 192.168.62.200 -k mysql.status[Uptime,3306]
18180842
# /usr/local/zabbix/bin/zabbix_get -s 192.168.62.200 -k mysql.status[Uptime,3310]
18180847
# /usr/local/zabbix/bin/zabbix_get -s 192.168.62.200 -k mysql.status[Bytes_sent,3310]
58803854473
8、导入模板Template Multi Mysql.xml
左右这个x下载保存成xml
9、被监控主机关联模板
10、完成