How to log MySQL queries made to the server

There is no need to say that this is a security risk and a performance killer, but sometimes in debug I need to checkout every single query made to the server, so in order to do this, at least on MySQL 5.7.17 it can be enabled on runtime.

To do this edit the file

/etc/mysql/mysql.conf.d/mysqld.cnf

and look for this lines

general_log_file = /var/log/mysql/mysql.log
general_log = 0

If they are commented, well, uncomment them, and make sure that general_log is set to 0, you can change the location of the file to the one of you choosing, but for me that is just fine.

Restart MySQL server

sudo service mysql restart

Once the service has been restarted there will be NO query log, that’s the runtime part of this, whenever you want to start logging the queries you need to access to the server

mysql -u root -p

When you are correctly logged you need to make this instruction

SET GLOBAL general_log = 1;

And now you can exit and start tailing the log file to see them on realtime

tail -f /var/log/mysql/mysql.log

Every time you restart the server, the log will stop, if you need that again just repeat the process from the mysql login.

I highly recommend to pipe it through a “grep Execute” to see just the executions.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s