Log all SQL requests

Hello,

MySQLIf for any reason you need to be able to save all MySQL requests executed on your MysQL server for a short time (adviced), here is a simple and fast way to log them into a single table and in a file.

To save requests in a file
tested with MariaDB 5.5

In the my.cnf, add the following parameters to the [mysql] section:

general-log
general-log-file=queries.log
log-output=file

Restart MySQL. All the requests will be saved (by default) in :

/var/lib/mysql/queries.log

To save requests in a table 
tested with MySQL 5.5

First, set the log_output variable to ‘TABLE’ :

mysql> SET GLOBAL log_output = 'TABLE';

The two other values for this variable are FILE or NONE. It is possible to save log in both table and file. For the TABLE configuration, the requests are saved in the table general_log in mysql database. For the FILE configuration, another variable has to be configured :  general_log_file. (by default set to  host_name.log).

After updating this parameter, requets are not yet saved, you have to activate it using this command :

 mysql> SET GLOBAL general_log = 'ON';

You can now check the mysql.general_log table.

Concerning the request logging, I advice you, if you did not already configured it, to log slow query. ( here )

Note : Be careful not to forget to swith off this log options after you find the request you want because the number of request in this table can increase very fast regarding to the application that uses the MySQL server.

Hope this helped:-)

PS: If you think that this article is not precise enough or contain errors, don’t hesitate to contact me : contact@etiennegautier.net

Tags: ,

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.