Log all queries in mysql

Is it possible for me to turn on audit logging on my mysql database?

I basically want to monitor all queries for an hour, and dump the log to a file.


Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

(Note: For mysql-5.6+ this won’t work. There’s a solution that applies to mysql-5.6+ if you scroll down or click here.)

If you don’t want or cannot restart the MySQL server you can proceed like this on your running server:

  • Create your log tables on the mysql database
  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
  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
  • Enable Query logging on the database
SET global general_log = 1;
SET global log_output = 'table';
  • View the log
select * from mysql.general_log
  • Disable Query logging on the database
SET global general_log = 0;

Method 2

Besides what I came across here, running the following was the simplest way to dump queries to a log file without restarting

SET global log_output = 'FILE';
SET global general_log_file='/Applications/MAMP/logs/mysql_general.log';
SET global general_log = 1;

can be turned off with

SET global general_log = 0;

Method 3


Start mysql with the –log option:

mysqld --log=log_file_name

or place the following in your my.cnf file:

log = log_file_name

Either one will log all queries to log_file_name.

You can also log only slow queries using the --log-slow-queries option instead of --log. By default, queries that take 10 seconds or longer are considered slow, you can change this by setting long_query_time to the number of seconds a query must take to execute before being logged.

Method 4

Top answer doesn’t work in mysql 5.6+. Use this instead:

general_log = on

in your my.cnf / my.ini file

Ubuntu/Debian: /etc/mysql/my.cnf
Windows: c:ProgramDataMySQLMySQL Server 5.x
wamp: c:wampbinmysqlmysqlx.y.zmy.ini
xampp: c:xamppmysqlbinmy.ini.

Method 5

Enable the log for table

mysql> SET GLOBAL general_log = 'ON';
mysql> SET GLOBAL log_output = 'table';

View log by select query

SELECT * FROM mysql.general_log

If there’s too much queries, truncate the table:

TRUNCATE table mysql.general_log;

Method 6

Quick way to enable MySQL General Query Log without restarting.

mysql> SET GLOBAL general_log_file = '/var/www/nanhe/log/all.log';
mysql> SET GLOBAL general_log = 'ON';

I have installed mysql through homebrew, mysql version : mysql Ver 14.14 Distrib 5.7.15, for osx10.11 (x86_64) using EditLine wrapper

Method 7

For the record, general_log and slow_log were introduced in 5.1.6:


5.2.1. Selecting General Query and Slow Query Log Output Destinations

As of MySQL 5.1.6, MySQL Server provides flexible control over the
destination of output to 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

Method 8

OS / mysql version:

$ uname -a
Darwin Raphaels-MacBook-Pro.local 15.6.0 Darwin Kernel Version 15.6.0: Thu Jun 21 20:07:40 PDT 2018; root:xnu-3248.73.11~1/RELEASE_X86_64 x86_64

$ mysql --version
/usr/local/mysql/bin/mysql  Ver 14.14 Distrib 5.6.23, for osx10.8 (x86_64) using  EditLine wrapper

Adding logging (example, I don’t think /var/log/... is the best path on Mac OS but that worked:

sudo vi ./usr/local/mysql-5.6.23-osx10.8-x86_64/my.cnf

general_log = on

Restarted Mysql


$ sudo tail -f /var/log/mysql/mysqld_general.log
181210  9:41:04    21 Connect   <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="6c1e0303182c00030f0d0004031f18">[email protected]</a> on employees
           21 Query /* mysql-connector-java-5.1.47 ( Revision: fe1903b1ecb4a96a917f7ed3190d80c049b1de29 ) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
           21 Query SET NAMES latin1
           21 Query SET character_set_results = NULL
           21 Query SET autocommit=1
           21 Query SELECT USER()
           21 Query SELECT USER()
181210  9:41:10    21 Query show tables
181210  9:41:25    21 Query select count(*) from current_dept_emp

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Notify of

Inline Feedbacks
View all comments
Would love your thoughts, please comment.x