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

Quick way to enable MySQL General Query Log without restarting.

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

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

Answer 2

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

http://dev.mysql.com/doc/refman/5.1/en/log-destinations.html

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 the general_log and slow_log tables in the mysql database

Answer 3

In case using AWS RDS MYSQL, step by step guide here.

When set as 'file' output you can view the log directly from AWS RDS "Log" Console.

AWS RDS MYSQL Logging

Answer 4

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