Mysql查询监控 [原创]

spzhangfei

                                                        Mysql查询监控
方案一：用慢查询日志监控
描述：主的slow_log监控写，主的slow_log监控读。
原理：用慢查询记录语句的原理是，当语句达到设定的阀值时，会认为该语句很慢，从而记录下该语句，那么如果要记录所有语句的话，那么只要把阀值设置成0就可以了。
优点：在主上用慢查询记录的信息比较多，例如扫描表的行数，执行时间，锁表时间。比查询日志记录的信息更全更有价值。
缺点：在主上占用的资源会和查询日志差不多，因为要记录所有语句，但是当负载高的时候可以对慢查询的阀值进行动态调整，这样就不会记录所有语句了。
实施：
步骤1
主库：在主库在线修改慢查询阀值：set global long_query_time=0
从库：在从库在线修改慢查询阀值：set global long_query_time=0
步骤2 （针对用LVS的情况）
在一台服务器上每分钟抓一次lvs的权重信息记录在日志里，可根据做监控数据库的权重和总体权重来推测sql执行的数量
步骤3
定期将主从库的慢查询日志和权重日志拷贝到日志分析服务器进行分析


方案二：用慢查询日志+binlog日志监控
描述：从的binlog监控写，主的slow_log监控读。
原理：慢查询的原理和上个方案一样。但是用在从上用binlog监控语句存在一个问题，就是binlog不记录执行sql的用户和主机名只记录了连接id。那么有一个方法，就是在主上每个连接连入mysql的时候mysql就自动往一个表里插入该连接的用户名和主机名和连接id，然后通过连接id可以在从库接收的binlog中的连接id对应上。
优点：和在主库上用慢查询日志记录所有信息对比的话负担更小，因为主库只需在连接连入mysql的时候记录一下用户名和主机名
缺点：binlog中的信息没有慢查询日志的信息全，只记录了sql执行的时间。
实施：
步骤1
主库：在test库下建立用于存在每个连接连入的id信息，然后在线修改每个连接连入需要执行的sql语句：set global init-connect='insert into AuditDB.accesslog(thread_id,user, current_user) values(connection_id(),user(),current_user())’;
从库：在从库在线修改慢查询阀值：set global long_query_time=0
步骤2（针对用LVS的情况）
在一台服务器上每分钟抓一次lvs的权重信息记录在日志里，可根据做监控数据库的权重和总体权重来推测sql执行的数量
步骤3
定期将主库的监控表和从库的慢查询日志binlog日志和权重日志拷贝到日志分析服务器进行分析
经过分析后的信息
查询时间，例如：2012-12-19 18:04:11
用户名，例如：root
主机名，例如：localhost
IP，例如：10.0.1.60
SQL执行时间，例如：0.000025
锁表时间，例如：0.000001
结果行数，例如：10
扫描行数，例如：100
库名，例如：test
表名，例如：abc
SQL语句，例如：select * from abc where age=3;
SQL语句抽象，例如：select * from abc where age=N;
SQL类型，例如：select
查询监控数据库权重，例如：2
总权重，例如：10

分析结果的存放
对于存放日志可以采用mysql的数据仓库引擎infobright商业版进行存储，特点压缩比高，统计速度快。