Skip to content


mysql 日志简介

1.The error log
错误日志

  • 记录mysql 的启动,运行,关闭,故障等日志
  • 可以mysqld启动时以–log-error[=file_name]指定,默认为数据目录下的host_name.err
  • 执行 FLUSH LOGS 命令后会给error log加上-old 后缀重命名,并创建一个新的空文件
  • 从5.1.20 开始启动时加上 –syslog 参数可以将日志输出至系统日志

执行/opt/mysql/bin/mysql.server restart 命令后的日志片段
cat /opt/mysql/var/localdev.c1gstudio.com.err

090521 11:44:20 [Note] /opt/mysql/libexec/mysqld: Normal shutdown

090521 11:44:20 [Note] Event Scheduler: Purging the queue. 0 events
090521 11:44:20 [Note] /opt/mysql/libexec/mysqld: Shutdown complete

090521 11:44:20 mysqld_safe mysqld from pid file /opt/mysql/var/localdev.c1gstudio.com.pid ended
090521 11:44:20 mysqld_safe Starting mysqld daemon with databases from /opt/mysql/var
090521 11:44:20 [Note] Plugin ‘InnoDB’ disabled by command line option
090521 11:44:20 [Note] Event Scheduler: Loaded 0 events
090521 11:44:20 [Note] /opt/mysql/libexec/mysqld: ready for connections.
Version: ‘5.1.26-rc-log’ socket: ‘/opt/mysql/mysql.sock’ port: 3306 Source distribution

2.The general query log
查询日志

  • 记录mysql的连接,查询等sql
  • 可用于排错,优化sql,查看连接情况等

如何开启?
修改my.conf

log = /opt/mysql/var/query_log.log
#log-warnings=2 默认1,大于1时记录“access denied”连接

mysql 日志记录非法连接

然后重启mysql

/opt/mysql/bin/mysql.server restart

浏览phpmyadmin系统变量页面时的日志片段
cat /opt/mysql/var/query_log.log

/opt/mysql/libexec/mysqld, Version: 5.1.26-rc-log (Source distribution). started with:
Tcp port: 3306 Unix socket: /opt/mysql/mysql.sock
Time Id Command Argument
090521 11:38:22 1 Connect [email protected] on
1 Query SELECT VERSION()
1 Query SET NAMES utf8
1 Query SET collation_connection = ‘utf8_unicode_ci’
1 Query SET NAMES utf8
1 Query SET collation_connection = ‘utf8_unicode_ci’
1 Query SHOW SESSION VARIABLES LIKE ‘collation_connection’
1 Query SHOW SESSION VARIABLES LIKE ‘character_set_connection’
1 Query SHOW CHARACTER SET
1 Query SHOW COLLATION
1 Query SHOW DATABASES
1 Query SHOW VARIABLES LIKE ‘profiling’
1 Query SELECT COUNT(*) FROM mysql.user
1 Query SELECT COUNT(*) FROM mysql.user
1 Init DB mysql
1 Query SHOW MASTER LOGS
1 Query SHOW GLOBAL STATUS
1 Query SHOW GLOBAL VARIABLES
1 Query SELECT UNIX_TIMESTAMP() – 5
1 Quit
090521 11:38:29 2 Connect [email protected] on
2 Query SELECT VERSION()

3.The binary log
二进制日志

  • 开启后会在数据目录下产生mysql-bin.000001等
  • 可用过主从复制,备份恢复

恢复时可用以下命令

mysqlbinlog mysql-bin.000007 mysql-bin.000008 | mysql

4.The Slow Query Log
慢查询日志

  • 可记录下大于long_query_time 参数指定时间的sql语句
  • 可用mysqldumpslow 命令来分析
  • 如何开启?
    编辑my.conf

    long_query_time = 8 #单位秒
    log-slow-queries = /opt/mysql/var/slow.log
    #log-queries-not-using-indexes 未使用索引

    然后重启mysql

    /opt/mysql/bin/mysql.server restart

    cat /opt/mysql/var/slow.log

    /opt/mysql/libexec/mysqld, Version: 5.1.26-rc-log (Source distribution). started with:
    Tcp port: 3306 Unix socket: /opt/mysql/mysql.sock
    Time Id Command Argument
    /opt/mysql/libexec/mysqld, Version: 5.1.26-rc-log (Source distribution). started with:
    Tcp port: 3306 Unix socket: /opt/mysql/mysql.sock
    Time Id Command Argument
    # Time: 090505 10:22:46
    # [email protected]: c1g[c1g] @ localhost []
    # Query_time: 9.016973 Lock_time: 0.000332 Rows_sent: 500 Rows_examined: 1107538
    use db4;
    SET timestamp=1241490166;
    SELECT distinct(c.id) FROM db4_member_p_config c,db4_member_p_f_forjobs j,db4_member_p_global g WHERE c.id = j.id AND c.uid = g.uid AND c.property = ‘1’ AND c.percent >= ’60’ AND g.education >= ‘5’ AND g.lProvince = ’25’ AND g.graduateSchool1 = ’25’ ORDER BY c.lastupdate DESC LIMIT 500;

    参考
    http://dev.mysql.com/doc/refman/5.1/en/server-logs.html

Posted in Mysql, 技术.

Tagged with , .


No Responses (yet)

Stay in touch with the conversation, subscribe to the RSS feed for comments on this post.



Some HTML is OK

or, reply to this post via trackback.