Log all queries in mysql

C

8

361

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.

Chatterjee answered 20/11, 2008 at 0:39 Comment(4)

Duplicate question https://mcmap.net/q/56129/-how-to-show-the-last-queries-executed-on-mysql/684229 – Lacteous 11/6, 2013 at 8:31

For readers benefit: Don't miss to read the question in the above comment. – Granary 9/2, 2014 at 18:49

You can refer my existing answer posted here dba.stackexchange.com/a/62477/6037 – Varicotomy 19/5, 2014 at 4:22

To turn logging on/off without restarting mysql, see this answer. – Suzannesuzerain 23/10, 2020 at 21:21

F

178

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.

Filch answered 20/11, 2008 at 0:45 Comment(10)

It should go without saying, but leaving this turned on in a production box winds up being non-entertaining very quickly. g – Thirteenth 20/11, 2008 at 2:40

If you have trouble enabling logging in this manner, doublt-check that the mysql user can write to the appropriate file location. – Aerobe 20/11, 2008 at 16:56

On Ubuntu, mysqld changes to /var/lib/mysql, so unless you specify an absolute path, the file will end up there. – Swampy 28/6, 2012 at 6:58

Is it possible to log queries over 1 particuarl db / table only? – Humfrid 4/8, 2012 at 16:8

@Humfrid phpmyadmin has now a 'tracking' option for tables where you specify a log('version') and it will keep record of the queries affecting it with information about time and the whole query. – Tannate 7/8, 2013 at 14:57

So you have to start/stop to change this setting? – Chatterjee 1/10, 2014 at 20:27

general_log = on general_log_file=/path/to/query.log – Charlinecharlock 29/6, 2015 at 11:26

This accepted answer should be deleted or edited to reflect the fact that it doesn't work with MySQL 5.6.+. – Excogitate 31/7, 2016 at 17:29

Is it somehow possible to log only queries that alternate the data, so no selects but updates, deletes etc? – Mendacity 7/2, 2017 at 13:38

none of the answers actually work for -all- queries. the only thing that gets logged are CORRECT queries (wether they have a result set or effect or not). queries that trigger syntax errors are NOT logged. therefore these logs are suitable for optimizing already bug-free code's performance, but not for figuring out what's going on if the resulting queries are not correct SQL syntax wise. also the 2 tables 'created' already seem to ship with the default installation, at least on any machine i've tried it with. :P – Else 18/2, 2019 at 9:33

M

283

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;

Merill answered 10/12, 2013 at 4:16 Comment(9)

Absolutely love this, works for existing and new connections on the DB – Opiate 19/2, 2015 at 13:57

There were some Statistics entries - not sure what those are but, otherwise, works really well. – Yuma 21/4, 2015 at 15:4

For this to work your user must have the SUPER privilege which is a global DB privilege and so cannot be limited to specific schemas or tables: GRANT SUPER ON *.* TO user1@localhost – Rhumb 16/7, 2018 at 20:55

I'd upvote that more than once if I could, I have a bookmark pointing here :) Thanks a lot! – Hadfield 9/12, 2019 at 11:50

better than the accepted answer, starting mysql with mysqld is a bit clumsy in many situations. This is useful in the most common case where you want to do some quick debugging and then switch it off. – Wroth 26/7, 2020 at 15:10

Status update: this is still one of my favorite bookmarks :) – Hadfield 21/4, 2021 at 10:12

Oh, look who's visiting again :) If we ever meet I'm so gonna pay you beer/coffee/lunch! – Hadfield 18/10, 2021 at 10:27

this doesn't do anything, nothing gets written to the log – Tlemcen 1/6, 2023 at 8:41

Worked on MySQL 5.7.33 – Simile 10/4 at 9:18

C

266

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

Cloverleaf answered 18/1, 2013 at 17:3 Comment(6)

I'm not sure if this is true for every version of MySQL (I'm on 5.5), but I didn't have to create the tables. I followed the same advice minus creating the tables, which is mentioned here: https://mcmap.net/q/56129/-how-to-show-the-last-queries-executed-on-mysql – Peach 27/3, 2014 at 23:18

Maybe it was already created for one or other reason, @TylerCollier – Cloverleaf 28/3, 2014 at 10:10

It should be noted that the CREATE TABLE commands should (if the tables do not already exist) be executed on the mysql database, not on any user-created databases. Perhaps the SQL statements could be updated to reflect that. – Satrap 23/1, 2015 at 13:12

For viewing the log SELECT * FROM mysql.general_log order by (event_time) desc will do better. just saying.:-) – Elnora 8/12, 2015 at 6:23

Don't agree with note - with server version 5.6.37 works fully correctly. Thanks. – Dipeptide 5/11, 2017 at 20:11

To know if logging is enable : SHOW VARIABLES LIKE 'general_log'; – Benzaldehyde 17/12, 2018 at 16:29

F

178

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