深入分析mysql日志教程

mysql日志我们可以总结有以下几种,错误日志、查询日志、慢查询日志、二进制日志了,这些日志每种都有自己的用处了,在mysql中日志也起到非常重要的作用了.

1.错误日志

错误日志主要用于定位mysql启动和运行中的一些问题,错误日志是默认开启的,可以通过show global variables查看错误日志文件的位置:

1. mysql>showglobalvariableslike'%log_error%';
2. +---------------+--------------------------+
3. |Variable_name|Value|
4. +---------------+--------------------------+
5. |log_error|/var/log/mysql/error.log|
6. +---------------+--------------------------+
7. 1rowinset(0.00sec)

也可以通过mysql配置文件my.cnf查看错误日志文件的位置:

# Error log - should be very few entries.

log_error = /var/log/mysql/error.log

常见的错误日志也包括存储引擎相关的信息,常见的格式如下:

1. 14071620:24:11[Note]Plugin'FEDERATED'isdisabled.
2. 14071620:24:11InnoDB:TheInnoDBmemoryheapisdisabled
3. 14071620:24:11InnoDB:Mutexesandrw_locksuseGCCatomicbuiltins
4. 14071620:24:11InnoDB:Compressedtablesusezlib1.2.3.4
5. 14071620:24:11InnoDB:Initializingbufferpool,size=128.0M
6. 14071620:24:11InnoDB:Completedinitializationofbufferpool
7. 14071620:24:11InnoDB:highestsupportedfileformatisBarracuda.
8. 14071620:24:11InnoDB:Waitingforthebackgroundthreadstostart
9. 14071620:24:12InnoDB:5.5.37started;logsequencenumber20673572
10. 14071620:24:13[Note]Serverhostname(bind-address):'127.0.0.1';port:3306
11. 14071620:24:13[Note]-'127.0.0.1'resolvesto'127.0.0.1';
12. 14071620:24:13[Note]ServersocketcreatedonIP:'127.0.0.1'.
13. 14071620:24:13[Note]EventScheduler:Loaded0events
14. 14071620:24:13[Note]/usr/sbin/mysqld:readyforconnections.

2.查询日志

查询日志用于记录所用的增删查改信息,由于在并发量大时会产生大量信息,所以默认是关闭的,可以通过show global variables查看查询日志的位置以及是否开启.

1. mysql>showglobalvariableslike'%general_log%';
2. +------------------+----------------------------+
3. |Variable_name|Value|
4. +------------------+----------------------------+
5. |general_log|OFF|
6. |general_log_file|/var/lib/mysql/mysql.log|
7. +------------------+----------------------------+

本例中查询日志是关闭的,我们可以在my.cnf中开启查询日志,开启后会影响mysql服务的性能,所以一般只用在开发环境中.

1. #Beawarethatthislogtypeisaperformancekiller.
2. #Asof5.1youcanenablethelogatruntime!
3. general_log_file=/var/log/mysql/mysql.log
4. general_log=1

查询日志包括了mysql执行的每一条sql信息以及会话信息,日志格式如下:

1. 14071620:33:469Connectuser@localhostondomain
2. 9Queryselecturl,url_tokenfromarticleswhereurl_md5='c473c205d1ee72cecf2546d332abbbcd'
3. 9Queryselecttitle,urlfromhot_articleslimit60,10
4. 9Quit

3.慢查询日志

慢查询日志应该是对调试程序最有用的日志了,可以通过慢查询日志找到哪些sql语句是性能瓶颈,一般情况下,正常的web应用时不会出现大量的慢查询日志的,因此强烈建议开启,可以通过show global variables查看慢查询日志的位置以及是否开启.

1. mysql>SHOWGLOBALVARIABLESLIKE'%slow%';
2. +---------------------+-------------------------------+--phpfensi.com
3. |Variable_name|Value|
4. +---------------------+-------------------------------+
5. |log_slow_queries|ON|
6. |slow_launch_time|2|
7. |slow_query_log|ON|
8. |slow_query_log_file|/var/log/mysql/mysql-slow.log|
9. +---------------------+-------------------------------+

慢查询默认记录超过10秒的查询语句,可以精确到毫秒:

1. mysql>SHOWGLOBALVARIABLESLIKE'%long_query_tim%';
2. +-----------------+-----------+
3. |Variable_name|Value|
4. +-----------------+-----------+
5. |long_query_time|10.000000|
6. +-----------------+-----------+

可以通过在my.cnf中设置慢查询相关选项,比如超时时间、记录无索引查询等:

1. #Hereyoucanseequerieswithespeciallylongduration
2. log_slow_queries=/var/log/mysql/mysql-slow.log
3. long_query_time=10
4. log-queries-not-using-indexes

常见的慢查询日志格式如下,其中包含用户、查询耗时、sql语句、结果集数量等信息:

1. #Time:14071620:47:59
2. #User@Host:user[user]@localhost[]
3. #Query_time:12.00012Lock_time:0.000136Rows_sent:1Rows_examined:1
4. usetest;
5. SETtimestamp=1405514879;
6. selecturl,url_tokenfromarticleswhereurl_md5='817563bd7ef4b2a476f1f55d0b558cd1';

4.二进制日志

二进制日志也叫作变更日志,主要用于记录修改数据或有可能引起数据改变的mysql语句,可以在my.cnf中配置二进制日志相关参数,如文件路径、过期时间、文件大小等,也可以从二进制日志中导出sql,用于恢复数据库.

1. log_bin =/var/log/mysql/mysql-bin.log
2. expire_logs_days =10
3. max_binlog_size =100M

使用show binary log查看mysql产生的bin log信息,包括文件名、文件大小,单位为字节等.

1. mysql>showbinarylogs;
2. +------------------+-----------+
3. |Log_name|File_size|
4. +------------------+-----------+
5. |mysql-bin.000001|6252|
6. |mysql-bin.000002|295|
7. |mysql-bin.000003|126|
8. |mysql-bin.000004|107|
9. +------------------+-----------+
10. 4rowsinset(0.00sec)

4.1 查看二进制日志

可以通过show master status和show binlog events查询定位binlog信息:

1. #使用showmasterstatus查看下一条binlog的偏移起始位置：
2. mysql>showmasterstatus;
3. +------------------+----------+--------------+------------------+
4. |File|Position|Binlog_Do_DB|Binlog_Ignore_DB|
5. +------------------+----------+--------------+------------------+
6. |mysql-bin.000004|870|||
7. +------------------+----------+--------------+------------------+
8. #使用showbinlogevents查询某条binglog记录
9. #语法：showbinlogevents[int'log_file'][fromposition][limit[offset,]row_count]
10. #一下是查看一个完整的事务执行binlog记录
11. mysql>showbinlogeventsin'mysql-bin.000004'from870limit4\G;
12. ***************************1.row***************************
13. Log_name:mysql-bin.000004
14. Pos:870
15. Event_type:Query
16. Server_id:1
17. End_log_pos:941
18. Info:BEGIN
19. ***************************2.row***************************
20. Log_name:mysql-bin.000004
21. Pos:941
22. Event_type:Intvar
23. Server_id:1
24. End_log_pos:969
25. Info:INSERT_ID=277
26. ***************************3.row***************************
27. Log_name:mysql-bin.000004
28. Pos:969
29. Event_type:Query
30. Server_id:1
31. End_log_pos:1247
32. Info:use`user`;insertintotest(title,url,url_md5,url_token,view_time)values('test','www.baidu.com','ae98f26d3b883f80b3eadb8709467607','1438524738','1405517003')
33. ***************************4.row***************************
34. Log_name:mysql-bin.000004
35. Pos:1247
36. Event_type:Xid
37. Server_id:1
38. End_log_pos:1274
39. Info:COMMIT/*xid=137*/
40. 4rowsinset(0.00sec)

4.2 使用二进制日志恢复数据

使用mysql的命令行工具,可以方便的将binlog中的sql语句导出,导出后的sql语句可以方便的用于恢复数据库,具体格式可以参考mysqlbinlog --help的输出,下面是两个最常用的例子:

1. #将mysql中的命令点870到命令点1274之间的sql语句导出到文件中
2. mysqlbinlog--start-position=870--stop-position=1274mysql-bin.000004>/tmp/mysql_restore.sql
3. #将mysql中的binlog中某段时间内的sql导出，可用于恢复一段时间的数据
4. mysqlbinlog--start-datetime="2012-07-1600:00:00"--stop-datetime="2012-07-1700:00:00"mysql-bin.000004>/tmp/mysql_restore.sql