To manage and optimize a MySQL database effectively, it is often necessary to review the queries that have been executed. This allows database administrators to analyze performance, troubleshoot issues, and optimize queries for better efficiency. Various methods enable you to view the last executed queries on a MySQL server. This article explores these options in detail, providing technical explanations and practical examples.

Using General Query Log

The General Query Log is one method to keep track of executed queries. This log contains every SQL statement received from clients, as well as client connections and disconnections.

Enabling General Query Log

To enable the General Query Log, you'll need to modify the MySQL configuration file, commonly named my.cnf or my.ini.

[mysqld]
general_log = 1
general_log_file = /var/log/mysql/mysql-general.log

• general_log: Setting this to 1 enables the General Query Log.
• general_log_file: This specifies the file where logs will be written.

After modifying the configuration file, restart the MySQL server:

sudo systemctl restart mysql

You can also enable or disable the General Query Log dynamically without restarting the server using the following MySQL commands:

SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/path/to/your/logfile.log';

Viewing the General Query Log

Once the General Query Log is enabled, it writes all queries to the specified log file. You can view this file using various command-line tools such as tail or less:

tail -f /var/log/mysql/mysql-general.log

This command updates in real-time, allowing you to see new queries as they are executed.

Using MySQL Slow Query Log

The Slow Query Log is a useful tool for identifying queries that take an excessive amount of time to execute, rather than simply logging all queries.

Enabling Slow Query Log

Add or modify the following lines in the MySQL configuration file:

1[mysqld]
2slow_query_log = 1
3slow_query_log_file = /var/log/mysql/mysql-slow.log
4long_query_time = 2

• slow_query_log: Enables the Slow Query Log.
• slow_query_log_file: Specifies the file where slow queries will be logged.
• long_query_time: Sets a threshold (in seconds) for what constitutes a "slow" query.

Restart MySQL after making these changes, or enable/disable it dynamically with:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

Analyzing Slow Queries

After enabling the Slow Query Log, you can analyze it using tools like mysqldumpslow, which summarizes the slow queries:

mysqldumpslow /var/log/mysql/mysql-slow.log

Using Performance Schema

The Performance Schema provides a way to monitor server events and enables you to inspect recently executed queries. It's more lightweight than the General Query Log.

Enabling Performance Schema

Ensure that the Performance Schema is enabled in your MySQL configuration:

[mysqld]
performance_schema = 1

For most installations, this is enabled by default.

Querying the Performance Schema

Use SQL queries to extract information about the last queries executed:

1SELECT DIGEST_TEXT, COUNT_STAR AS count,
2       SUM_TIMER_WAIT/1000000000 AS total_time, 
3       SUM_LOCK_TIME/1000000000 AS lock_time
4FROM performance_schema.events_statements_summary_by_digest
5ORDER BY SUM_TIMER_WAIT DESC
6LIMIT 10;

This query fetches summary statistics for executed statements, sorted by total execution time.

Table of Key Points

To consolidate the various methods discussed, the table below summarizes the key points:

Conclusion

Each method for accessing the last executed queries on a MySQL server provides distinct advantages. The General Query Log is ideal for comprehensive logging but can generate substantial file sizes. The Slow Query Log focuses on performance bottlenecks, helping to optimize the slowest queries. The Performance Schema offers a less intrusive approach to monitoring by focusing on server-level statistics without generating large logs. Based on your needs—whether it's debugging, performance tuning, or comprehensive monitoring—these various methods offer robust solutions for managing your MySQL databases effectively.