2、在数据库的最后一行添加
log=log.txt
代码
3、重启mysql数据库
4、去数据库数据目录 我的是(D:\MYSQL\data) 你会发现多了一个log.txt文件
我的是在C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.5\data
测试:
1、对数据库 *** 作
2、查看log.txt文件内容 如果发现有变化说明你就可以监控到mysql数据库的变化
数据库的查询 删除 更新 插入都可以查到
希望本篇文章可以帮助大家更快的二次开发 ^_^
日志文件类型概述:
1.
错误日志 记录启动、运行或停止mysqld时出现的问题。
My.ini配置信息:
#Enter a name for the error log file. Otherwise a default name will be used.
#log-error=d:/mysql_log_err.txt
怎样使用zabbix监控服务器的mysql数据库进入 zabbix web 台Configuration-->Hosts groups-->点击Create host group-->选择template选项卡选择模板TemplateApp MySQLTempldate OS Linux点击update 即(032.png)
进入zabbix web 台configuration-->hosts-->点击主机 name-->选择template选
项卡选择模板Template App MySQL点击左边Add按钮点击update按钮即(033.png)
Zabbix可以通过两种方式获取历史数据:1.通过Zabbix前台获取历史数据
通过Zabbix前台查看历史数据非常简单,可以通过Monitoring->Lastest data的方式查看。也可以点击右上角的As plain test按钮保存成文本文件。
2.通过前台获取的数据进行处理和二次查询有很多限制,因此可以通过SQL语句直接从后台DB查询数据。
首先大家应该熟悉SQL语句Select 常用用法:
SELECT [ALL | DISTINCT] Select_List [INTO [New_Table_name]
FROM { Table_name | View_name} [ [,{table2_name | view2_name}
[,...] ]
[ WHERE Serch_conditions ]
[ GROUP BY Group_by_list ]
[ HAVING Serch_conditions ]
[ ORDER BY Order_list [ASC| DEsC] ]
说明:
1)SELECT子句指定要查询的特定表中的列,它可以是*,表达式,列表等。
2)INTO子句指定要生成新的表。
3)FROM子句指定要查询的表或者视图。
4)WHERE子句用来限定查询的范围和条件。
5)GROUP BY子句指定分组查询子句。
6)HAVING子句用于指定分组子句的条件。
7)ORDER BY可以根据一个或者多个列来排序查询结果,在该子句中,既可以使用列名,也可以使用相对列号,ASC表示升序,DESC表示降序。
8)mysql聚合函数:sum(),count(),avg(),max(),avg()等都是聚合函数,当我们在用聚合函数的时候,一般都要用到GROUP BY 先进行分组,然后再进行聚合函数的运算。运算完后就要用到Having子句进行判断了,例如聚合函数的值是否大于某一个值等等。
从Zabbix数据库中查询监控项目方法,这里已查询主机的网卡流量为例子:
1)通过hosts表查找host的ID。
mysql>select host,hostid from hosts where host="WWW05"
+-------+--------+
| host | hostid |
+-------+--------+
| WWW05 | 10534 |
+-------+--------+
1 row in set (0.00 sec)
2)通过items表查找主的监控项和key以及itemid。
mysql>select itemid,name,key_ from items where hostid=10534 and key_="net.if.out[eth0]"
+--------+-----------------+------------------+
| itemid | name| key_ |
+--------+-----------------+------------------+
| 58860 | 发送流量: | net.if.out[eth0] |
+--------+-----------------+------------------+
1 row in set (0.00 sec)
3)通过itemid查询主机的监控项目(history_uint或者trends_uint),单位为M。
主机流入流量:
mysql>select from_unixtime(clock) as DateTime,round(value/1024/1024,2) as Traffic_in from history_uint where itemid="58855" and from_unixtime(clock)>='2014-09-20' and from_unixtime(clock)<'2014-09-21' limit 20
+---------------------+------------+
| DateTime| Traffic_in |
+---------------------+------------+
| 2014-09-20 00:00:55 | 0.10 |
| 2014-09-20 00:01:55 | 0.09 |
| 2014-09-20 00:02:55 | 0.07 |
| 2014-09-20 00:03:55 | 0.05 |
| 2014-09-20 00:04:55 | 0.03 |
| 2014-09-20 00:05:55 | 0.06 |
| 2014-09-20 00:06:55 | 0.12 |
| 2014-09-20 00:07:55 | 0.05 |
| 2014-09-20 00:08:55 | 0.10 |
| 2014-09-20 00:09:55 | 0.10 |
| 2014-09-20 00:10:55 | 0.12 |
| 2014-09-20 00:11:55 | 0.12 |
| 2014-09-20 00:12:55 | 0.13 |
| 2014-09-20 00:13:55 | 3.16 |
| 2014-09-20 00:14:55 | 0.23 |
| 2014-09-20 00:15:55 | 0.24 |
| 2014-09-20 00:16:55 | 0.26 |
| 2014-09-20 00:17:55 | 0.23 |
| 2014-09-20 00:18:55 | 0.14 |
| 2014-09-20 00:19:55 | 0.16 |
+---------------------+------------+
20 rows in set (0.82 sec)
主机流出流量:
mysql>select from_unixtime(clock) as DateTime,round(value/1024/1024,2) as Traffic_out from history_uint where itemid="58860" and from_unixtime(clock)>='2014-09-20' and from_unixtime(clock)<'2014-09-21' limit 20
+---------------------+-------------+
| DateTime| Traffic_out |
+---------------------+-------------+
| 2014-09-20 00:00:00 |4.13 |
| 2014-09-20 00:01:00 |3.21 |
| 2014-09-20 00:02:00 |2.18 |
| 2014-09-20 00:03:01 |1.61 |
| 2014-09-20 00:04:00 |1.07 |
| 2014-09-20 00:05:00 |0.92 |
| 2014-09-20 00:06:00 |1.23 |
| 2014-09-20 00:07:00 |2.76 |
| 2014-09-20 00:08:00 |1.35 |
| 2014-09-20 00:09:00 |3.11 |
| 2014-09-20 00:10:00 |2.99 |
| 2014-09-20 00:11:00 |2.68 |
| 2014-09-20 00:12:00 |2.55 |
| 2014-09-20 00:13:00 |2.89 |
| 2014-09-20 00:14:00 |4.98 |
| 2014-09-20 00:15:00 |6.56 |
| 2014-09-20 00:16:00 |7.34 |
| 2014-09-20 00:17:00 |6.81 |
| 2014-09-20 00:18:00 |7.67 |
| 2014-09-20 00:19:00 |4.11 |
+---------------------+-------------+
20 rows in set (0.74 sec)
4)如果是两台设备,汇总流量,假如公司出口有两台设备,可以用下面的SQL语句汇总每天的流量。下面SQL语句是汇总上面主机网卡的进出流量的。
mysql>select from_unixtime(clock,"%Y-%m-%d %H:%i") as DateTime,sum(round(value/1024/1024,2)) as Traffic_total from history_uint where itemid in (58855,58860) and from_unixtime(clock)>='2014-09-20'and from_unixtime(clock)<'2014-09-21' group by from_unixtime(clock,"%Y-%m-%d %H:%i") limit 20
+------------------+---------------+
| DateTime | Traffic_total |
+------------------+---------------+
| 2014-09-20 00:00 | 4.23 |
| 2014-09-20 00:01 | 3.30 |
| 2014-09-20 00:02 | 2.25 |
| 2014-09-20 00:03 | 1.66 |
| 2014-09-20 00:04 | 1.10 |
| 2014-09-20 00:05 | 0.98 |
| 2014-09-20 00:06 | 1.35 |
| 2014-09-20 00:07 | 2.81 |
| 2014-09-20 00:08 | 1.45 |
| 2014-09-20 00:09 | 3.21 |
| 2014-09-20 00:10 | 3.11 |
| 2014-09-20 00:11 | 2.80 |
| 2014-09-20 00:12 | 2.68 |
| 2014-09-20 00:13 | 6.05 |
| 2014-09-20 00:14 | 5.21 |
| 2014-09-20 00:15 | 6.80 |
| 2014-09-20 00:16 | 7.60 |
| 2014-09-20 00:17 | 7.04 |
| 2014-09-20 00:18 | 7.81 |
| 2014-09-20 00:19 | 4.27 |
+------------------+---------------+
20 rows in set (1.52 sec)
5)查询一天中主机流量的最大值,最小值和平均值。
mysql>select date as DateTime,round(min(traffic)/2014/1024,2) as TotalMinIN,round(avg(traffic)/1024/1024,2) as TotalAvgIN,round(max(traffic)/1024/1024,2) as TotalMaxIN from (select from_unixtime(clock,"%Y-%m-%d") as date,sum(value) as traffic from history_uint where itemid in (58855,58860) and from_unixtime(clock)>='2014-09-20' and from_unixtime(clock)<'2014-09-21' group by from_unixtime(clock,"%Y-%m-%d %H:%i") ) tmp
+------------+------------+------------+------------+
| DateTime | TotalMinIN | TotalAvgIN | TotalMaxIN |
+------------+------------+------------+------------+
| 2014-09-20 | 0.01 | 4.63 | 191.30 |
+------------+------------+------------+------------+
1 row in set (1.74 sec)
6)查询主机组里面所有主机CPU Idle平均值(原始值)。
mysql>select from_unixtime(hi.clock,"%Y-%m-%d %H:%i") as DateTime,g.name as Group_Name,h.host as Host, hi.value as Cpu_Avg_Idle from hosts_groups hg join groups g on g.groupid = hg.groupid join items i on hg.hostid = i.hostid join hosts h on h.hostid=i.hostid join history hi on i.itemid = hi.itemid where g.name='上海机房--项目测试' and i.key_='system.cpu.util[,idle]' and from_unixtime(clock)>='2014-09-24' and from_unixtime(clock)<'2014-09-25' group by h.host,from_unixtime(hi.clock,"%Y-%m-%d %H:%i") limit 10
+------------------+----------------------------+----------+--------------+
| DateTime | Group_Name | Host | Cpu_Avg_Idle |
+------------------+----------------------------+----------+--------------+
| 2014-09-24 00:02 | 上海机房--项目测试 | testwb01 | 94.3960 |
| 2014-09-24 00:07 | 上海机房--项目测试 | testwb01 | 95.2086 |
| 2014-09-24 00:12 | 上海机房--项目测试 | testwb01 | 95.4308 |
| 2014-09-24 00:17 | 上海机房--项目测试 | testwe01 | 95.4580 |
| 2014-09-24 00:22 | 上海机房--项目测试 | testwb01 | 95.4611 |
| 2014-09-24 00:27 | 上海机房--项目测试 | testwb01 | 95.2939 |
| 2014-09-24 00:32 | 上海机房--项目测试 | testwb01 | 96.0896 |
| 2014-09-24 00:37 | 上海机房--项目测试 | testwb01 | 96.5286 |
| 2014-09-24 00:42 | 上海机房--项目测试 | testwb01 | 96.8086 |
| 2014-09-24 00:47 | 上海机房--项目测试 | testwb01 | 96.6854 |
+------------------+----------------------------+----------+--------------+
10 rows in set (0.75 sec)
7)查询主机组里面所有主机 CPU Idle平均值(汇总值)。
mysql>select from_unixtime(hi.clock,"%Y-%m-%d %H:%i") as Date,g.name as Group_Name,h.host as Host, hi.value_avg as Cpu_Avg_Idle from hosts_groups hg join groups g on g.groupid = hg.groupid join items i on hg.hostid = i.hostid join hosts h on h.hostid=i.hostid join trends hi on i.itemid = hi.itemid where g.name='上海机房--项目测试' and i.key_='system.cpu.util[,idle]' and from_unixtime(clock)>='2014-09-10' and from_unixtime(clock)<'2014-09-11' group by h.host,from_unixtime(hi.clock,"%Y-%m-%d %H:%i") limit 10
+------------------+----------------------------+----------+--------------+
| Date | Group_Name | Host | Cpu_Avg_Idle |
+------------------+----------------------------+----------+--------------+
| 2014-09-10 00:00 | 上海机房--项目测试 | testwb01 | 99.9826 |
| 2014-09-10 01:00 | 上海机房--项目测试 | testwb01 | 99.9826 |
| 2014-09-10 02:00 | 上海机房--项目测试 | testwb01 | 99.9825 |
| 2014-09-10 03:00 | 上海机房--项目测试 | testwb01 | 99.9751 |
| 2014-09-10 04:00 | 上海机房--项目测试 | testwb01 | 99.9843 |
| 2014-09-10 05:00 | 上海机房--项目测试 | testwb01 | 99.9831 |
| 2014-09-10 06:00 | 上海机房--项目测试 | testwb01 | 99.9829 |
| 2014-09-10 07:00 | 上海机房--项目测试 | testwb01 | 99.9843 |
| 2014-09-10 08:00 | 上海机房--项目测试 | testwb01 | 99.9849 |
| 2014-09-10 09:00 | 上海机房--项目测试 | testwb01 | 99.9849 |
+------------------+----------------------------+----------+--------------+
10 rows in set (0.01 sec)
8)其它与Zabbix相关的SQL语句。
查询主机已经添加但没有开启监控主机:
select host from hosts where status=1
查询NVPS的值:
mysql>SELECT round(SUM(1.0/i.delay),2) AS qps FROM items i,hosts h WHERE i.status='0' AND i.hostid=h.hostid AND h.status='0' AND i.delay<>0
+--------+
| qps|
+--------+
| 503.40 |
+--------+
1 row in set (0.11 sec)
望采纳
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)