错误日志主要用于定位mysql启动和运行中的一些问题,错误日志是默认开启的,可以通过show global variables查看错误日志文件的位置:
mysql> show global variables like '%log_error%'; +---------------+--------------------------+ | Variable_name | Value | +---------------+--------------------------+ | log_error | /var/log/mysql/error.log | +---------------+--------------------------+ 1 row in set (0.00 sec) 也可以通过mysql配置文件my.cnf查看错误日志文件的位置:
# Error log - should be very few entries.
log_error = /var/log/mysql/error.log
2.慢查询日志
慢查询日志应该是对调试程序最有用的日志了,可以通过慢查询日志找到哪些sql语句是性能瓶颈,一般情况下,正常的web应用时不会出现大量的慢查询日志的,因此强烈建议开启,可以通过show global variables查看慢查询日志的位置以及是否开启.
mysql> SHOW GLOBAL VARIABLES LIKE '%slow%'; +---------------------+-------------------------------+ --phpfensi.com | Variable_name | Value | +---------------------+-------------------------------+ | log_slow_queries | ON | | slow_launch_time | 2 | | slow_query_log | ON | | slow_query_log_file | /var/log/mysql/mysql-slow.log | +---------------------+-------------------------------+ 慢查询默认记录超过10秒的查询语句,可以精确到毫秒:
mysql> SHOW GLOBAL VARIABLES LIKE '%long_query_tim%'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ 可以通过在my.cnf中设置慢查询相关选项,比如超时时间、记录无索引查询等:
# Here you can see queries with especially long duration log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 10 log-queries-not-using-indexes 常见的慢查询日志格式如下,其中包含用户、查询耗时、sql语句、结果集数量等信息:
# Time: 140716 20:47:59 # User@Host: user[user] @ localhost [] # Query_time: 12.00012 Lock_time: 0.000136 Rows_sent: 1 Rows_examined: 1 use test; SET timestamp=1405514879; select url,url_token from articles where url_md5 = '817563bd7ef4b2a476f1f55d0b558cd1';
