cant connect local mysql to_Zabbix监控mysql

1、利用自带mysql模板监控(监控进程、状态、版本)

1.1、客户端创建用于查询数据的账户

以管理员的身份登陆数据库

GRANT USAGE ON *.* TO 'zabbix'@'localhost' IDENTIFIED BY 'zabbix';

flush privileges;

show grants for zabbix@'localhost';

测试是否创建成功：

mysql -uzabbix -pzabbix

1.2、创建相关文件

mkdir -p /var/lib/mysql/

ln -s /tmp/mysql.sock /var/lib/mysql/

ln -s /usr/local/zabbix/conf/zabbix_agentd.conf /usr/local/etc/

ln -s /usr/local/mysql/bin/mysqladmin /usr/bin/mysqladmin

1.3、配置监控脚本,获取数据

mkdir /usr/local/zabbix/etc/

cd /usr/local/zabbix/etc/

vim chk_mysql.sh

#!/bin/bash

# 用户名

MYSQL_USER='zabbix'

# 密码

MYSQL_PWD='zabbix'

# 主机地址/IP

MYSQL_HOST='127.0.0.1'

# 端口

MYSQL_PORT='3306'

# 数据连接

MYSQL_CONN="/usr/bin/mysqladmin -u${MYSQL_USER} -p${MYSQL_PWD} -h${MYSQL_HOST} -P${MYSQL_PORT}"

# 参数是否正确

if [ $# -ne "1" ];then

echo "arg error!"

fi

# 获取数据

case $1 in

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

;;

*)

echo "Usage:$0(Uptime|Com_update|Slow_queries|Com_select|Com_rollback|Questions|Com_insert|Com_delete|Com_commit|Bytes_sent|Bytes_received|Com_begin)"

;;

esac

chmod +x /usr/local/zabbix/etc/chk_mysql.sh

1.4、修改zabbix_agentd.conf

vi /etc/zabbix/zabbix_agentd.conf

增加自定义key，在最后一行增加如下：

# 获取mysql版本

UserParameter=mysql.version,mysql -h"127.0.0.1" -uzabbix -pzabbix -e "select version();"|awk 'END {print}'

# 获取mysql性能指标,这个是上面定义好的脚本

UserParameter=mysql.status[*],/usr/local/zabbix/etc/chk_mysql.sh $1 2>>/dev/null

#mysql进程状态

UserParameter=mysql.process,ps -ef|grep mysql|grep -v grep|wc -l

1.5、重启zabbix_agentd

killall zabbix_agentd

service zabbix_agentd restart

1.6、zabbix_server端操作, 看能否获取到值

cd /usr/local/zabbix/bin

./zabbix_get -s 192.168.0.14 -p 10050 -k mysql.ping

./zabbix_get -s 192.168.0.14 -p 10050 -k mysql.status[Uptime]

./zabbix_get -s 192.168.0.14 -p 10050 -k mysql.status[Com_update]

1.7、zabbix_server端web界面操作，添加mysql模板

1.8、查看监控到的数据图

2、利用自带mysql模板监控,方法二

2.1、添加mysql环境变量

vim /etc/profile

PATH=$PATH:/usr/local/mysql/bin

soure /etc/profile

2.2、创建my.cnf配置文件

cd /usr/local/zabbix/ #zabbix安装目录创建隐藏文件

vi ./my.cnf

[mysql]

host=localhost

user=zabbix

password=zabbix

[mysqladmin]

host=127.0.0.1

user=zabbix

password=zabbix

mv my.cnf .my.cnf

2.3、客户端创建用于查询数据的账户

以管理员的身份登陆数据库

GRANT USAGE ON *.* TO 'zabbix'@'localhost' IDENTIFIED BY 'zabbix';

flush privileges;

show grants for zabbix@'localhost';

测试是否创建成功：

mysql -uzabbix -pzabbix

2.4、创建检查脚本

cd /etc/zabbix/zabbix_agentd.conf.d

上传配置文件或编辑： 注:标颜色方法一已经有了并注释

cat userparameter_mysql.conf

# 连接数

UserParameter=mysql.max_conn[*],echo "show variables where Variable_name='max_connections';" | HOME=/usr/local/zabbix mysql -N | awk '{print $$2}'

UserParameter=mysql.conn_status[*],echo "show global status where Variable_name='$1';" | HOME=/usr/local/zabbix mysql -N | awk '{print $$2}'

# 缓冲池

UserParameter=mysql.buffer_pool_size[*],echo "show variables where Variable_name='innodb_buffer_pool_size';" | HOME=/usr/local/zabbix mysql -N | awk '{printf "%.2f",$$2/1024/1024/1024}'

UserParameter=mysql.buffer_pool_usage_percent[*],echo "show global status where Variable_name='Innodb_buffer_pool_pages_free' or Variable_name='Innodb_buffer_pool_pages_total';" | HOME=/usr/local/zabbix mysql -N | awk '{a[NR]=$$2}END{printf "%.1f",100-((a[1]/a[2])*100)}'

UserParameter=mysql.buffer_pool_hit_rate[*],echo "show global status where Variable_name='Innodb_buffer_pool_read_requests' or Variable_name='Innodb_buffer_pool_reads';" | HOME=/usr/local/zabbix mysql -N | awk '{a[NR]=$$2}END{printf "%.1f",a[1]/(a[1]+a[2])*100}'

# 增删改查

#UserParameter=mysql.status[*],echo "show global status where Variable_name='$1';" | HOME=/usr/local/zabbix mysql -N | awk '{print $$2}'

# 数据库大小

UserParameter=mysql.size[*],echo "select round(sum(index_length+data_length)/1024/1024,2) from information_schema.tables where table_schema='$1'" | HOME=/usr/local/zabbix mysql -N

# 实例状态

UserParameter=mysql.ping,HOME=/usr/local/zabbix mysqladmin ping | grep -c alive

#UserParameter=mysql.version,mysql –V

2.5、测试脚本

echo "show variables where Variable_name='max_connections';" | HOME=/usr/local/zabbix mysql -N | awk '{print $2}'

2.6、修改配置文件加载脚本

vim /etc/zabbix/zabbix_agentd.conf

Include=/etc/zabbix/zabbix_agentd.conf.d

2.7、重启zabbix_agentd

killall zabbix_agentd

service zabbix_agentd restart

2.8、自定义监控项

配置--模板—监控项

监控项：MySQL max connections key: mysql.max_conn[*]

监控项： connections key: mysql.conn_status[Threads_connected]

show status like '%Thread%'; 在mysql里面查名字

监控项： buffer pool key: mysql.buffer_pool_size[*]

监控项：MySQL buffer_pool_usage_percent key: mysql.buffer_pool_usage_percent[*]

监控项：MySQL buffer_pool_hit_rate key: mysql.buffer_pool_hit_rate[*]

监控项：MySQL database size key: mysql.size[mysql] #mysql是监控库名

监控项：MySQL process key: mysql.process #mysql是监控库名

2.9、添加图形展示

MySQL connections

MySQL buffer_pool_usage_percent