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 root@localhost 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 root@localhost 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
# User@Host: 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