錯誤日志主要用于定位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';
