MySQL 5.1 logging changes – Log to DB and runtime config
While browsing around the MySQL site last night I discovered a number of nice new features of mysql 5.1 that relate to logging.
- Logging to DB instead of log files
- Runtime configuration of logging.
Logging to DB instead of log files
Coming from a web development background rather than a sysadmin background I’m far more comfortable manipulating and analysing data using SQL. So to be able to log all the queries or just the slow queries for an application to the db during application development or load testing is a huge benefit.
To enable logging to DB you can add the following to your my.cnf
log_output = TABLE
The logs will be written to the ‘slow_log’ and ‘general_log’ tables in the mysql database.
Note – logging to tables has more overhead than logging to file, so would suggest using it primarily for development purposes.
Full details of the options are on the mysql manual on log tables (link updated to 5.7 docs as 5.1 docs have been removed)
Runtime configuration of logging.
This allows you to turn on and off logging without restarting MySQL – which just saves a little bit of time and makes it much nicer for debugging problems.
To turn on the logging of all queries run:
SET GLOBAL general_log = 'ON';
And for just the slow query log:
SET GLOBAL slow_query_log = 'ON';
And to turn them both off use:
SET GLOBAL general_log = 'OFF';
SET GLOBAL slow_query_log = 'OFF';
If you also want to see queries not using indexes in the slow query log you can set the following variable:
SET GLOBAL log_queries_not_using_indexes = 'ON';
Hope it helps,