Log all queries in mysql

Question

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.

Answer 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   ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' 

  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' 

• 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; 

Answer 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;