Log management for MySQL

Log operation is one of the most important means of database maintenance, log files will record the various information of MySQL server, so when the MySQL server was accidentally damaged, not only can the log file to see the cause of the error, but also through the log file for data recovery.

MySQL log files are divided into binary log, error log, general query log, slow query log. In addition to binary files, other log files are text files. By default, MySQL only starts the error log file, while the other log files need to be started manually before they can be started.

Binary log: The log file records various operations of the database in binary form, but does not log query statements.

Error log: This log file records information such as MySQL server startup, shutdown, and runtime errors.

General Query log: The log records the startup and shutdown information of the MySQL server, the client connection information, the Update data logging SQL statement, and the query data logging SQL statement.

Slow query log: Record the execution time more than a specified time of the various operations, through the tool analysis slow log can locate the MySQL server performance bottleneck.

There are pros and cons to using logs. After you start the log, you can implement maintenance on the MySQL server, but it will slow down the execution of your MySQL software.

1. Manipulating binary logs

The binary log (BINLOG) details the database amount change, which is the DDL and DML statements in the SQL statement, but does not include the data record query operation. With binary log files, you can learn more about what is being done in the MySQL database.

Manipulating binaries includes starting binary logs, viewing binary logs, stopping binary logs, and deleting binary logs.

1.1 Starting the binary log

By default, the binary log is off, if you want to start the binary log, you can set the MySQL server configuration file My.ini to achieve, the specific content is as follows:

[mysqld] Log -bin[=dir\[filename]]

The parameter dir is used to specify the storage path of the binary file, and the parameter filename is used to specify the file name of the binary file, in the format filename.number, where number format is 000001,000002,000003, and so on.

When the binary log is launched specifically, if the parameter dir and filename are not set, the binary log file will be saved to the default directory-the database data file using the default name hostname-bin.number.

Default configuration:

[mysqld] Log -Bin

To configure the storage path:

[mysqld] Log -Bin=D:\mysqllog\binlog

1.2 Viewing binary logs

The syntax is as follows:

Mysqlbinlog filename.  Number

1.3 To stop a binary file

You can delete the Log-bin content in the [mysqld] group directly into the My.ini. You can also pause the binary log with the command set, which reads:

SET Sql_log_bin=0SET sql_log_bin=1

Avalue of sq l_log_bin of 0 indicates the pause binary logging feature, and avalue of sq l_log_bin of 1 indicates that the binary logging function is reopened. It is important to note that only users with super privileges can execute the SET statement.

1.4 Deleting a binary file

If you want to delete a binary file, you need to do so by executing the command reset Master,purge masterlogs to and PURGE masterlogs before.

The syntax is as follows:

RESET MASTER;

Execute the preceding command to delete all binaries.

 to filename.  Number

By executing the preceding command, you can delete all binary log files that are less than number.

PURGE MASTER LOGS before ' yyyy-mm-dd Hh:MM:ss '

Execute the above command to delete all the two log files created before the specified time (Yyyy-mm-dd hh:MM:ss).

2. Operation error Log

2.1 Start error log

The syntax is as follows:

[mysqld] Log -Error[=dir\[filename]]

2.2 Viewing the error log

Text files that can be viewed directly with the text tool

2.3 Delete error Log

Mysqladmin–u root–p Flush-logs

3. Operation of the general query log

The general query log is primarily used to record all user actions on the MySQL server, including the MySQL server's startup and shutdown information, client connection information, update data logging SQL statements, and query data logging SQL statements.

3.1 Start the General query log

[mysqld] Log [=dir\[filename]]

The above methods need to restart the MySQL server can be set to take effect, there is also a way to set up the MySQL environment variables to dynamically control the opening and closing of the general query log. By setting the environment variable General_log to perform the dynamic control of the universal query log without restarting the MySQL server, the operation is as follows:

Set global General_log=on'%general_log%' \g;    

3.2 Viewing the General query log

Text files that can be viewed directly with the text tool

3.3 Stop General Query log

[mysqld] # Log [=dir\[filename]]

The above need to restart the MySQL server, dynamically set to stop the general query log another way:

set global general_log=off;

3.4 Delete a generic query log

Mysqladmin–u root–p Flush-logs

4. Operation Slow Query log

Slow query logging performs a variety of actions over a specified time, and the tool analyzes slow logs to locate MySQL server performance bottlenecks.

4.1 Turn on slow query log

[mysqld] Log -Slow-queries[=dir\[filename]]long_query_time= N

The above need to restart the MySQL server, another way to dynamically set the following:

#通过设置环境变量slow_query_log进行慢查询日志的动态控制, on means open set global  slow_query_log=on; #设置慢查询日志最大允许的时间 in seconds set global Long_query_time=3;

4.2 Viewing the Slow query log

Text files that can be viewed directly with the text tool

4.3 Parsing the slow query log

The corresponding tool is mysqldumpslow.pl, the common parameters are as follows:

"-S" specifies the sort parameter for parsing the slow query log, optionally: "Al" indicates the average lock time,

"Ar" indicates the average number of returned records, and "at" indicates the average query time. The "-T" parameter indicates that only the specified number of rows is displayed.

#分析慢查询日志

C:\Program files\mysql\mysql Server 5.5\bin>mysqldumpslow.pl-s at-t 1 "C:\Documents and Settings\All Users\applicati On Data\mysql\mysql Server 5.5\data\mhx0jcjyuc7pdaa-slow.log "

"-S at" means that the results of the analysis are sorted by average query time, and "-T 1" indicates that only the 1th that meets the criteria is displayed.

Note: When parsing a slow query log, mysqldumpslow.pl scripts written for a Perl language, which requires a corresponding Perl locale, and the installation package for the Perl environment can be downloaded http://www.perl.org/.

4.4 Stop Slow query log

[mysqld] # Log -Slow-queries[=dir\[filename]] #long_query_time= N

Comment out can stop, but the above need to restart the MySQL server, another way to dynamically set the following:

#通过设置环境变量slow_query_log进行慢查询日志的动态控制, off means close set global  slow_query_log=off;

4.5 Delete Slow query log

Delete the full query log directly from the Windows Delete command, and then recreate the corresponding file using the following syntax, as follows:

To delete a slow query log file:

C:\Program files\mysql\mysql Server 5.5\bin>del C:\Documents and Settings\All Users\Application Data\mysql\mysql Server 5.5\data\mhx0jcjyuc7pdaa-slow.log

Re-refresh the slow query log:

Mysqladmin–u root–p Flush-logs

Executing the above command, the MySQL server creates a new slow query log, at which time the slow query log is empty.

Log management for MySQL