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.

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

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;

UPDATE: NO LONGER VALID FOR MYSQL 5.6+

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.

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

[mysqld]
general_log = on
general_log_file=/usr/log/general.log

in your my.cnf / my.ini file

Ubuntu/Debian: /etc/mysql/my.cnf
Windows: c:\ProgramData\MySQL\MySQL Server 5.x
wamp: c:\wamp\bin\mysql\mysqlx.y.z\my.ini
xampp: c:\xampp\mysql\bin\my.ini.

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;

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

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

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

[mysqld]
general_log = on
general_log_file=/var/log/mysql/mysqld_general.log

Restarted Mysql

Result:

$ sudo tail -f /var/log/mysql/mysqld_general.log
181210  9:41:04	   21 Connect	root@localhost 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