zabbix 监控多个mysql_zabbix mysql mariadb多实例监控

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、完成