MYSQL log analysis _ MySQL

MYSQL log analysis bitsCN. comMYSQL log analysis

Reprinted from: http://blog.chinaunix.net/uid-10565106-id-223928.html

A log file is a list of tracking records. it helps us keep abreast of the status of system and application services, and provides the most detailed and accurate information during troubleshooting, it helps us quickly find the cause and reduce our subjective experience to guess. such an answer is more convincing, and machines generally do not lie. Any system, such as the operating system, database, and application server, has its own log files, which are divided into different types of logs based on different functional properties. The MySQL database we will discuss later also has its own set of log files, which can be divided4Logs --Error log,Binary log,Query LogsAndSlow query log. What are their functions? how can we effectively use these log files in our actual work?

By default, these four log files are stored in the $ MYSQL_HOME/data Directory. you can also use the server startup option to customize the location and name of the logs. The following figure shows various log files, the error log node1.err, 16 binary logs starting with mysql-bin, the query log node1.log, and the slow query log node1-slow.log.

1. error log-error [=/path_to/file_name]

It records the MySQL database startup and shutdown information and any serious errors that occur during server running. Generally, when a database fails to start normally, the first thing we should think of is to view the error log. The log file records the startup and shutdown information of the MySQL database.

2. binary log-bin = [/path_to/file_name]

Binary log files are saved in binary format. you need to use the mysqlbinlog tool to view all the DDL and DML statements recorded in the log, except for the select statement.

The preceding content is recorded in the binlog file from position 232609 to the end of the last operation.

There will be 16 binary log files under the data Directory. each time the service is restarted, a new file will be generated, or the next file will be automatically generated in an order when the file reaches the maximum limit. In a busy OLTP system, a large number of logs are generated every day. Naturally, we will think that it will occupy a considerable disk space, so it is necessary to clean it regularly. The following describes several methods.

Option 1. mysql> reset master; (delete all binlog files and generate a new file starting from 000001)

Option 2. mysql> purge master logs to 'MySQL-bin.000017'; (delete all logs prior to the mysql-bin.000017)

Option 3. # mysqladmin flush-log (trigger log file update based on the expire_logs_day parameter in the configuration file my. cnf and delete all logs from the days before the current date)

3. query log-log [=/path_to/file_name]

Query all the SQL statements that the client operates on, including the select query statement. (Note: for operations on all databases that query log records, this log affects System Performance for frequently accessed applications. we recommend that you disable this log .)

4. slow query log-slow-queries [=/path_to/file_name]

The slow query log records the SQL statements whose execution time exceeds the value of long_query_time. Slow query logs can effectively help us find SQL performance problems in actual applications and find SQL statements with low execution efficiency.

We often see that on the forum, people may ask you to provide detailed log information before answering your questions, and then further analyze and help you solve the problem, this is a good habit and train of thought for solving the problem. log is the clue for us to find out the truth. After learning about MySQL logs, you can also become an authentic troublshooting expert.

BitsCN.com