mysql全局日志开启

作者:Chris

之前有写过Mysql慢查询设置,现在需要对Mysql进行深入的分析,发现mysql性能瓶颈和寻找优化策略,这就要对全部的sql执行语句进行查询,慢查询是满足不了的,binlog也只是记录了update语句,这个时候就需要设置mysql的全局log了。

[root@test mysql5.5]# cat /etc/my.cnf |grep -v "#" |grep "log"
log=/tmp/row.log
slow-query-log = on
slow_query_log_file = /data/mysql/binlog/slowquery_3.log
log-queries-not-using-indexes = true
log-bin=mysql-bin
binlog_format=mixed
innodb_log_group_home_dir = /data/mysql/data

指定一下log参数,重启mysql服务即可,如果不能重启mysql服务器可以使用mysql> set global log=1;来开启

这样子就可以查看全局log了

此log记录了服务器接收到的每一个查询插入更新命令,无论这些是命令是否正确甚至是否包含语法错误,都会将其记录下来 ,记录的格式为 {Time ,Id ,Command,Argument }

[root@test mysql5.5]# tail -f /tmp/row.log
/usr/local/mysql/bin/mysqld, Version: 5.5.25-log (Source distribution). started with:
Tcp port: 3306  Unix socket: /data/mysql/mysql.sock
Time                 Id Command    Argument
150625 14:08:24     1 Connect   xiaolei@10.10.3.165 on
                    1 Query     /*!40101 set @@session.wait_timeout=28800 */
                    1 Query     SET PROFILING = 1
                    1 Query     SET profiling_history_size = 0
                    1 Query     SET profiling_history_size = 15
                    1 Query     SHOW STATUS
                    1 Query     SELECT COUNT(*) FROM `test`.`2222` LIMIT 0, 1000
150625 14:08:43     1 Query     TRUNCATE TABLE test.1
150625 14:08:49     1 Query     INSERT INTO test.1 SELECT * FROM test.`gc_1_17500`
                    1 Query     /*!40101 set @@session.wait_timeout=28800 */
                    1 Query     TRUNCATE TABLE test.1
150625 14:13:26     1 Query     INSERT INTO test.1 SELECT * FROM test.`gc_1_17500`
150625 14:13:49     1 Query     SET PROFILING = 1
                    1 Query     SET profiling_history_size = 0
                    1 Query     SET profiling_history_size = 15
                    1 Query     SHOW STATUS
                    1 Query     SELECT COUNT(*) FROM `test`.`1` LIMIT 0, 1000
                    1 Query     SHOW STATUS
                    1 Query     SHOW PROFILES
                    1 Query     select state, round(sum(duration),5) as `duration (summed) in sec` from information_schema.profiling where query_id = 4 group by state order by `duration (summed) in sec` desc
                    1 Query     SET PROFILING = 0
                    1 Query     EXPLAIN SELECT COUNT(*) FROM `test`.`1` LIMIT 0, 1000

但是当我查看了一些mysql的err日志发现其中log参数是过时的,但是从日志输出效果看来也没什么问题,

150625 14:35:05 [Warning] The syntax '--log' is deprecated and will be removed in a future release. Please use '--general-log'/'--general-log-file' instead.

Warning提示“--log”已经过时,并将在以后的版本中删除。请使用'--general-log'

有强迫症的我还是遵从官方的说明,使用general-log

[root@test mysql5.5]# cat /etc/my.cnf |grep -v "#" |grep "log"
general-log = on
general_log_file = /data/mysql/binlog/general.log
slow-query-log = on
slow_query_log_file = /data/mysql/binlog/slowquery_3.log
log-queries-not-using-indexes = true
log-bin=mysql-bin
binlog_format=mixed
innodb_log_group_home_dir = /data/mysql/data

通过分析全局log,我们可以得到诸如下信息:

  • 1、Mysql访问得最多的数据
  • 2、Mysql执行得最多的查询的种类
  • 3、Mysql停留时间最长的状态
  • 4、Mysql用来执行查询的使用得最频繁的子系统
  • 5、Mysql查询过程中访问的数据种类
  • 6、Mysql执行了多少种不同类型的活动,比如索引扫描。

您的评论

Build by Loppo 0.6.14