mysql query enable log

MySQL Query Enable Log

In the MySQL database, enabling query log is a very useful technology. It can help you track each executed query statement in order to better optimize SQL statements and performance. This article will introduce how to enable query logging in a MySQL database.

Turn on query log

The query log in MySQL is a tool that records each query statement. By logging the execution of a query, you can get a handle on how your database is performing and identify problem areas.

If you want to enable the query log function, you can follow the following steps:

Step 1: Open the MySQL configuration file my.cnf

on Mac or Linux , this file is usually located in the /usr/local/mysql/etc/ directory, and on Windows, it is usually located in C:ProgramDataMySQLMySQL Server xx

You can use the following command to open the my.cnf file:

sudo vim /usr/local/mysql/etc/my.cnf

Step 2: Set up the query log

In the my.cnf file, you can add the following statement to enable the query log:

[mysqld]
log=/var/log/mysql/mysql.log

This will be in /var/log Create a log file named mysql.log in the /mysql/ directory. You can modify the path and name to suit your needs.

Step 3: Restart the MySQL service

After completing the above settings, you need to restart the MySQL service for these changes to take effect.

You can use the following command to restart the MySQL service:

sudo service mysql restart

Alternatively, you can run the following command in the terminal:

sudo /usr/local/mysql/support-files/mysql.server restart

Step 4: Verify that the query log has been Enable

You can execute the following command to verify whether the query log is enabled:

SHOW VARIABLES LIKE 'general_log';

If the query result is ON, it means that the query log is enabled.

If the query result is OFF, you can use the following command to enable the query log:

SET GLOBAL general_log = 'ON';

You have now enabled the MySQL query log, and you can execute some query statements to observe changes in the log file. .

Usage of query log

When query log is enabled, each query will be recorded in the log file. In the log file, each query begins with a timestamp and the SQL statement that was executed. The following is a sample log entry:

070726 16:55:09        4 Connect     root@localhost on test
            4 Query       SELECT DATABASE()
            4 Query       SELECT * FROM customers

In this example, the first three lines are connection information between the MySQL client and the server. The last two lines are the actual executed query statements.

If you want to analyze log files to identify query performance issues, you can use tools such as pt-query-digest and MySQL Enterprise Monitor. These tools search across all query statements in a log file and summarize the results into an easy-to-understand report. You can use these reports to find slow or frequent queries and make adjustments accordingly.

Summary

By using the MySQL query log, you can obtain the execution status of the database and help you better optimize SQL query statements and performance. Through this article, you already know how to enable query logs and analyze log files. In actual database applications, it is very useful to use the query log function, which can help you quickly solve performance problems and improve the efficiency of database applications.

The above is the detailed content of mysql query enable log. For more information, please follow other related articles on the PHP Chinese website!