Sometimes you want to know the queries executed on database server. MySQL Server provides a way to show the general query log and the slow query log, if those logs are enabled. Possible destinations for log entries are log files or the general_log and slow_log tables in the mysql system database. For those who use MySQL >= 5.1.1 2, you can control this option globally at runtime:
- Execute
SET GLOBAL log_output = 'TABLE'; - Execute
SET GLOBAL general_log = 'ON'; - Take a look at the table
mysql.general_log
If you prefer to output to a file instead of a table:
SET GLOBAL log_output = "FILE"; the default.SET GLOBAL general_log_file = "/path/to/your/logfile.log";SET GLOBAL general_log = 'ON';
What the advantage of using these command?
- It is not editing the
my.cnf file and potentially permanently turning on logging - You’re not fishing around the filesystem looking for the query log – or even worse, distracted by the need for the perfect destination.
/var/log /var/data/log /opt /home/mysql_savior/var - You don’t have to restart the server and interrupt any current connections to it.
- Restarting the server leaves you where you started (log is by default still off)
For more information, see MySQL 5.1 Reference Manual – Server System Variables – general_log
For those who want to enable general log and slow query log and show them in tables in MySQL.
1. First, check if you already have the two tables slow_log and general_log existing in the mysql database.
If not, you have to create them, note that you have to create in the mysql database.
- To create the
general_log table:
CREATE TABLE `general_log` (
`event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP,
`user_host` mediumtext NOT NULL,
`thread_id` bigint(21) unsigned NOT NULL,
`server_id` int(10) unsigned NOT NULL,
`command_type` varchar(64) NOT NULL,
`argument` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'
The general query log is a general record of what mysqld is doing. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients. The general query log can be very useful when you suspect an error in a client and want to know exactly what the client sent to mysqld.
CREATE TABLE `slow_log` (
`start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP,
`user_host` mediumtext NOT NULL,
`query_time` time NOT NULL,
`lock_time` time NOT NULL,
`rows_sent` int(11) NOT NULL,
`rows_examined` int(11) NOT NULL,
`db` varchar(512) NOT NULL,
`last_insert_id` int(11) NOT NULL,
`insert_id` int(11) NOT NULL,
`server_id` int(10) unsigned NOT NULL,
`sql_text` mediumtext NOT NULL,
`thread_id` bigint(21) unsigned NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'
The slow query log consists of SQL statements that take more than long_query_time seconds to execute and require at leastmin_examined_row_limit rows to be examined. The slow query log can be used to find queries that take a long time to execute and are therefore candidates for optimization.
The minimum and default values of long_query_time are 0 and 10, respectively. The value can be specified to a resolution of microseconds.
2. Enable Query logging on the database
SET global general_log = 1;
SET global log_output = 'table';
3. Now you can view the log by running this query:
SELECT
*
FROM
mysql.general_log;
4. If you want to disable query logging on the database, run this query:
SET global general_log = 0;
References:
https://stackoverflow.com/questions/650238/how-to-show-the-last-queries-executed-on-mysql
https://tableplus.com/blog/2018/10/how-to-show-queries-log-in-mysql.html
Another Pratama 