Mysql log analysis tutorial

1. Error log

The error log is primarily used to locate some of the problems with MySQL startup and running, the error log is turned on by default, and you can view the location of the error log file by 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)

You can also view the location of the error log files through the MySQL profile my.cnf:

# Error Log-should be very few entries.
Log_error =/var/log/mysql/error.log

Common error logs also include information about the storage engine, which is common in the following formats:

The

140716 20:24:11 [note] Plugin ' federated ' is disabled.
140716 20:24:11 innodb:the InnoDB memory heap be disabled
140716 20:24:11 innodb:mutexes and rw_locks use GCC a Tomic builtins
140716 20:24:11 innodb:compressed tables use zlib 1.2.3.4
140716 20:24:11 innodb:initializing buf Fer pool, size = 128.0M
140716 20:24:11 innodb:completed initialization of buffer pool
140716 20:24:11 Innodb:hi Ghest supported the file format is barracuda.
140716 20:24:11  innodb:waiting for the background threads to start
140716 20:24:12; Log sequence number 20673572
140716 20:24:13 [note] Server hostname (bind-address): ' 127.0.0.1 '; port:3306
14071 6 20:24:13 [note]  -' 127.0.0.1 ' resolves to ' 127.0.0.1 ';
140716 20:24:13 [note] Server socket created on IP: ' 127.0.0.1 '.
140716 20:24:13 [note] Event scheduler:loaded 0 Events
140716 20:24:13 [note]/usr/sbin/mysqld:ready for Connec tions.

2. Query log

The query log is used to record the additions and deletions used to check the information, because the concurrent volume will produce a large amount of information, so the default is closed. You can view the location of the query log and whether it is turned on by show global variables.

Mysql> show global variables like '%general_log% ';
+------------------+----------------------------+
| variable_name | Value |
+------------------+----------------------------+
| General_log | Off |
| General_log_file | /var/lib/mysql/mysql.log |
+------------------+----------------------------+

In this case, the query log is closed. We can open query log in my.cnf, it will affect the performance of MySQL service, so it is only used in development environment.

# Be aware the This log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
General_log_file =/var/log/mysql/mysql.log
General_log = 1

The query log includes every SQL information and session information that is executed by MySQL, and the log format is as follows:

140716 20:33:46 9 Connect user@localhost on domain
9 Query Select Url,url_token from articles where url_md5 = ' C473C205D1EE72CECF2546D332ABBBCD '
9 Query Select Title,url from Hot_articles limit 60, 10
9 Quit

3. Slow query Log

The slow query log should be the most useful log for the debugger, and you can find which SQL statements are performance bottlenecks through a slow query log. Normally, a normal Web application will not have a large number of slow query logs, so it is strongly recommended to open it. You can view the location of the slow query log and turn it on by show global variables.

Mysql> show GLOBAL VARIABLES like '%slow% ';
+---------------------+-------------------------------+
| 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 |
+---------------------+-------------------------------+

Slow query default records more than 10 seconds of query statements, can be accurate to milliseconds:

Mysql> show GLOBAL VARIABLES like '%long_query_tim% ';
+-----------------+-----------+
| variable_name | Value |
+-----------------+-----------+
| Long_query_time | 10.000000 |
+-----------------+-----------+

You can set up slow query-related options in my.cnf, such as timeout times, logging without index queries, and so on:

# Here you can-queries with especially long duration
Log_slow_queries =/var/log/mysql/mysql-slow.log
Long_query_time = 10
Log-queries-not-using-indexes

The common slow query log format includes information such as user, query time, SQL statements, number of result sets, and so on:

# 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 ';

4. Binary Log

Binary log is also called change log, mainly used to record modified data or may cause data changes in the MySQL statement, you can configure binary log related parameters in my.cnf, such as file path, expiration time, file size, and so on. You can also export SQL from the binary log to restore the database.

Log_bin =/var/log/mysql/mysql-bin.log
Expire_logs_days = 10
Max_binlog_size = 100M
Use show binary log to view the bin log information generated by MySQL, including file name, file size (in bytes), and so on.

Mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
|      mysql-bin.000001 | 6252 |
|       mysql-bin.000002 | 295 |
|       mysql-bin.000003 | 126 |
|       mysql-bin.000004 | 107 |
+------------------+-----------+
4 rows in Set (0.00 sec)

4.1 View binary Log

Binlog information can be located by show Master status and show Binlog events query:

#使用show Master Status to view the offset starting position of the next Binlog:
Mysql> Show master status;
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
|      mysql-bin.000004 |              870 |                  | |
+------------------+----------+--------------+------------------+

#使用show binlog Events Query a Binglog record
#语法: Show Binlog events [int ' log_file '] [from position] [limit [offset,] row_count]
#一下是查看一个完整的事务执行binlog记录
Mysql> show Binlog events in ' mysql-bin.000004 ' from 870 limit 4\g;
1. Row ***************************
log_name:mysql-bin.000004
pos:870
Event_type:query
Server_id:1
end_log_pos:941
Info:begin
2. Row ***************************
log_name:mysql-bin.000004
pos:941
Event_type:intvar
Server_id:1
end_log_pos:969
info:insert_id=277
3. Row ***************************
log_name:mysql-bin.000004
pos:969
Event_type:query
Server_id:1
end_log_pos:1247
Info:use ' user '; INSERT into Test (title,url,url_md5,url_token,view_time) VALUES (' Test ', ' www.baidu.com ', ' ae98f26d3b883f80b3eadb8709467607 ', ' 1438524738 ', ' 1405517003 ')
4. Row ***************************
log_name:mysql-bin.000004
pos:1247
Event_type:xid
Server_id:1
end_log_pos:1274
Info:commit/* xid=137 *
4 rows in Set (0.00 sec)

4.2 Recover data using binary log

Using the MySQL command-line tool, you can easily export the SQL statements in Binlog, and the exported SQL statements can be easily restored to the database. The specific format can refer to the output of Mysqlbinlog--help, the following are the two most common examples:

#将mysql中的命令点870到命令点1274之间的sql语句导出到文件中
Mysqlbinlog--start-position=870--stop-position=1274 mysql-bin.000004 >/tmp/mysql_restore.sql
#将mysql中的binlog中某段时间内的sql导出 that can be used to recover data for a period of time
Mysqlbinlog--start-datetime= "2012-07-16 00:00:00"--stop-datetime= "2012-07-17 00:00:00" mysql-bin.000004 >/tmp/ Mysql_restore.sql