Hi,
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.
+12
A:
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.
Robert Gamble
2008-11-20 00:45:05
+1 for didn't know that. Used triggers instead.
Robert Gould
2008-11-20 01:34:14
It should go without saying, but leaving this turned on in a production box winds up being non-entertaining very quickly. *g*
ceejayoz
2008-11-20 02:40:42
If you have trouble enabling logging in this manner, doublt-check that the mysql user can write to the appropriate file location.
Jon Topper
2008-11-20 16:56:29
+2
A:
You should be aware that mysql logging on really impacts performance, but it can be a wise thing to do.
I usually leave it on on the dev server (except when it drives us insane :))
Lea de Groot
2008-11-22 05:36:29
A:
Can one change logging while server is running? Or I need to restart server when I want to switch logging on or off?
Mike
2010-03-22 15:29:01