一、linux环境下监控MySQL
Zabbix Server自带了MySQL插件来监控mysql数据库的模板，只需要配置好agent客户端，然后在web端给主机增加模板就行了
监控项目：
Com_update： mysql执行的更新个数
Com_select： mysql执行的查询个数
Com_insert： mysql执行插入的个数
Com_delete： 执行删除的个数
Com_rollback： 执行回滚的操作个数
Bytes_received: 接受的字节数
Bytes_sent： 发送的字节数
Slow_queries： 慢查询语句的个数
Com_commit： 确认的事物个数
Com_begin： 开始的事物个数
Uptime： 服务器已启动的秒数
Questions： 客户端发送到服务器的语句个数
监控模板下载地址：http://www.zabbix.org/wiki/Zabbix_Templates#External_template_resources
脚本下载地址：https://github.com/itnihao/zabbix-book
1）创建zabbix链接MySQL的用户名，密码并授予权限。
|
1
2
3
|
mysql> grant all on *.* to zabbix@
'localhost'
identified by "123456”;
mysql> flush privileges;
|
2）在zabbix_agent服务目录下创建 .my.cnf 连接文件
|
1
2
3
4
5
|
cd
/usr/local/zabbix/etc/
vim .my.cnf
[client]
user=zabbix
password=123456
|
注意：
如果在数据库grant授权时，针对的是localhost，这个.my.cnf里面就不用加host参数了【如上配置】
但如果grant授权时针对的是本机的ip（如192.168.1.25），那么在.my.cnf文件里就要加上host参数进行指定:host=192.168.1.25
3）配置MySQL的key文件
这个可以从zabbix安装时的解压包里拷贝过来：
|
1
2
3
|
cp
/usr/local/src/zabbix3
.0.3
/conf/
zabbix_agentd
/userparameter_mysql
.conf
/usr/local/zabbix/etc/zabbix_agentd
.conf.d/
|
4)替换zabbix安装路径，注意如果MySQL没配置好环境变量可能找不到MySQL命令，可以用MySQL全路径
看到类似 HOME=/var/lib/zabbix 的路径设置，把路径全都替换为 /usr/local/zabbix/etc/，也就是上面的.my.cnf文件所在的目录路径。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
cd
/usr/local/zabbix/etc/zabbix_agentd
.conf.d/
vim userparameter_mysql.conf
# For all the following commands HOME should be set to the directory that has .my.cnf file with password information.
# Flexible parameter to grab global variables. On the frontend side, use keys like mysql.status[Com_insert].
# Key syntax is mysql.status[variable].
UserParameter=mysql.status[*],
echo
"show global status where Variable_name='$1';"
| HOME=
/usr/local/zabbix/etc/
mysql -N |
awk
'{print $$2}'
# Flexible parameter to determine database or table size. On the frontend side, use keys like mysql.size[zabbix,history,data].
# Key syntax is mysql.size[<database>,<table>,<type>].
# Database may be a database name or "all". Default is "all".
# Table may be a table name or "all". Default is "all".
# Type may be "data", "index", "free" or "both". Both is a sum of data and index. Default is "both".
# Database is mandatory if a table is specified. Type may be specified always.
# Returns value in bytes.
# 'sum' on data_length or index_length alone needed when we are getting this information for whole database instead of a single table
UserParameter=mysql.size[*],
bash
-c
'echo "select sum($(case "$3" in both|"") echo "data_length+index_length";; data|index) echo "$3_length";; free) echo "data_free";; esac)) from information_schema.tables$([[ "$1" = "all" || ! "$1" ]] || echo " where table_schema=\"$1\"")$([[ "$2" = "all" || ! "$2" ]] || echo "and table_name=\"$2\"");" | HOME=/usr/local/zabbix/etc/ mysql -N'
UserParameter=mysql.
ping
,HOME=
/usr/local/zabbix/etc/
mysqladmin
ping
|
grep
-c alive
UserParameter=mysql.version,mysql -V
%s
#/var/lib/zabbix#/usr/local/zabbix/etc/# #用命令替换
|
也可以用这个shell脚本监控，包括主从监控：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
### MySQL DB Infomation
UserParameter=mysql.status[*],
echo
"show global status where Variable_name='$1';"
|mysql--defaults-
file
=
/usr/local/zabbix/etc/
.my.cnf -N|
awk
'{print $$2}'
UserParameter=mysql.variables[*],
echo
"show global variables where Variable_name='$1';"
|mysql--defaults-
file
=
/usr/local/zabbix/etc/
.my.cnf -N|
awk
'{print $$2}'
UserParameter=mysql.
ping
,mysqladmin--defaults-
file
=
/usr/local/zabbix/etc/
.my.cnf
ping
|
grep
-c alive
UserParameter=mysql.version,
echo
"select version();"
|mysql--defaults-
file
=
/usr/local/zabbix/etc/
.my.cnf -N
#### MySQL Master Information
UserParameter=mysql.master.Slave_count,
echo
"show slave hosts;"
|mysql--defaults-
file
=
/usr/local/zabbix/etc/
.my.cnf -N|
wc
-l
UserParameter=mysql.master.Binlog_file,
echo
"show master status;"
|mysql--defaults-
file
=
/usr/local/zabbix/etc/
.my.cnf -N|
awk
'{print $1}'
|
awk
-F.
'{print $1}'
UserParameter=mysql.master.Binlog_number,
echo
"show master status;"
|mysql--defaults-
file
=
/usr/local/zabbix/etc/
.my.cnf -N|
awk
'{print $1}'
|
awk
-F.
'{print $2}'
UserParameter=mysql.master.Binlog_position,
echo
"show master status;"
|mysql --defaults-
file
=
/usr/local/zabbix/etc/
.my.cnf-N|
awk
'{print $2}'
UserParameter=mysql.master.Binlog_count,
echo
"show binary logs;"
|mysql--defaults-
file
=
/usr/local/zabbix/etc/
.my.cnf -N|
wc
-l
UserParameter=mysql.master.Binlog_total_size,
echo
"show binary logs;"
|mysql--defaults-
file
=
/usr/local/zabbix/etc/
.my.cnf -N|
awk
'{sum+=$NF}END{print sum}'
#### MySQL Slave Information
UserParameter=mysql.slave.Seconds_Behind_Master,
echo
"show slave status\G"
|mysql--defaults-
file
=
/usr/local/zabbix/etc/
.my.cnf|
grep
"Seconds_Behind_Master"
|
awk
'{print $2}'
UserParameter=mysql.slave.Slave_IO_Running,
echo
"show slave status\G"
|mysql--defaults-
file
=
/usr/local/zabbix/etc/
.my.cnf|
grep
"Slave_IO_Running"
|
awk
'{print $2}'
UserParameter=mysql.slave.Slave_SQL_Running,
echo
"show slave status\G"
|mysql--defaults-
file
=
/usr/local/zabbix/etc/
.my.cnf|
grep
"Slave_SQL_Running"
|
awk
'{print $2}'
UserParameter=mysql.slave.Relay_Log_Pos,
echo
"show slave status\G"
|mysql--defaults-
file
=
/usr/local/zabbix/etc/
.my.cnf|
grep
"Relay_Log_Pos"
|
awk
'{print $2}'
UserParameter=mysql.slave.Exec_Master_Log_Pos,
echo
"show slave status\G"
|mysql--defaults-
file
=
/usr/local/zabbix/etc/
.my.cnf|
grep
"Exec_Master_Log_Pos"
|
awk
'{print $2}'
UserParameter=mysql.slave.Read_Master_Log_Pos,
echo
"show slave status\G"
|mysql--defaults-
file
=
/usr/local/zabbix/etc/
.my.cnf|
grep
"Read_Master_Log_Pos"
|
awk
'{print $2}'
|
5）修改zabbix_agentd.conf配置文件，开启额外加载，就是去掉前面的#号
|
1
2
|
vim zabbix_agentd.conf
Include=
/usr/local/zabbix/etc/zabbix_agentd
.conf.d/
|
6）重启zabbix_agentd服务
|
1
|
/etc/init
.d
/zabbix_agentd
restart
|
7）测试看能否获取到数据
|
1
2
|
# zabbix_get -s 127.0.0.1 -p 10050 -k mysql.status[Com_select]
200661
|
8）登陆zabbix监控界面，在“配置”里为主机添加模板，完成监控。
二、windows下的MySQL监控
要想在Windows上取得MySQL的状态数据，可以用vbs脚本运行mysql命令
1）在d:\Zabbix\Scripts\目录下新建两个脚本文件内容如下：
mysql_ping.vbs
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
Set
objFS =CreateObject(
"Scripting.FileSystemObject"
)
Set
objArgs = WScript.Arguments
str1 = getCommandOutput(
"D:\SOFT_PHP_PACKAGE\mysql\bin\mysqladmin-ucactiuser -pcactiuser ping"
) //修改对应数据库路径，用户名和密码
If
Instr(str1,
"alive"
) > 0Then
WScript.Echo 1
Else
WScript.Echo 0
End
If
Function
getCommandOutput(theCommand)
Dim
objShell, objCmdExec
Set
objShell =CreateObject(
"WScript.Shell"
)
Set
objCmdExec = objshell.exec(thecommand)
getCommandOutput =objCmdExec.StdOut.ReadAll
end
Function
|
MYSQL-status.vbs
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
Set
objFS = CreateObject(
"Scripting.FileSystemObject"
)
Set
objArgs = WScript.Arguments
str1 = getCommandOutput(
"D:\SOFT_PHP_PACKAGE\mysql\bin\mysqladmin-u cactiuser -pcactiuser extended-status"
) //修改对应数据库路径，用户名和密码
Arg = objArgs(0)
str2 = Split(str1,
"|"
)
For
i = LBound(str2) to UBound(str2)
If
Trim(str2(i)) = Arg
Then
WScript.Echo TRIM(str2(i+1))
Exit
For
End
If
next
Function
getCommandOutput(theCommand)
Dim
objShell, objCmdExec
Set
objShell =CreateObject(
"WScript.Shell"
)
Set
objCmdExec = objshell.exec(thecommand)
getCommandOutput =objCmdExec.StdOut.ReadAll
end
Function
|
2）修改windows上的zabbix_agentd.comf文件，设置key值。在UserParameter下面加两句；
UserParameter=mysql.status[*], cscript/nologo d:\Zabbix\Scripts\MySQL_Ext-Status_Script.vbs $1
UserParameter=mysql.ping, cscript /nologo d:\Zabbix\Scripts\MySql_Ping.vbs
3）重启zabbix_agentd，并给主机添加MySQL模版，查看items状态。
本文转自 80后小菜鸟 51CTO博客，原文链接:http://blog.51cto.com/zhangxinqi/1920584
