Enabling logging of issued SQL statements in MySQL

Logging of issued SQL statements might come handy if there are more users accessing one MySQL server. Moreover it’s useful not only for security, it’s also beneficial when a new application is being tested and deployed so statements can be monitored and adjusted in case something goes wrong. I.e. queries that are built “on fly” may sometimes cause difficulties and unexpected results.

Prerequisites

Of course there should be a MySQL database installed and running on your machine. If you’re missing it, get one here.

Enabling logging

#Open a terminal and login to MySQL as root, issue the commands as below. mysql -u root -p -A --now check whether logging of statements is enabled SELECT @@general_log; --if we get zero, then logging isn’t enabled: +---------------+ | @@general_log | +---------------+ | 0 | +---------------+ 1 row in set (0.00 sec) --enable logging via following command: set global general_log = 1; --result will look like following Query OK, 0 rows affected (0.15 sec) --now make sure logging is enabled by executing the SELECT statement once again: SELECT @@general_log; --expected value is 1 : +---------------+ | @@general_log | +---------------+ | 1 | +---------------+ 1 row in set (0.00 sec) --and now simply check path of the log file by issuing: SELECT @@general_log_file; --the result might look like this: +----------------------------+ | @@general_log_file | +----------------------------+ | /mysql/data/linux-au7h.log | +----------------------------+ 1 row in set (0.00 sec) --now open another OS terminal preferably as root or privileged user and monitor statements: tail -f /mysql/data/linux-au7h.log

Cleanup

If everything is clear, turn off the logging by issuing following commands.

--this should be executed in a MySQL console set global general_log = 1; --now go to the OS terminal box as root or privileged user and empty the log file: cat /dev/null > /mysql/data/linux-au7h.log

Conclusion

As usual, logging may be a great thing as it allows one tracking all queries that are being issued over entire MySQL server. The negative aspect is that large database servers will clog up the log file which means you should rotate logs on a daily or weekly basis, old ones should be compressed or deleted.

Further reading & help

If you need help with your MySQL database, drop me a line. You’re also welcome to visit other articles devoted to MySQL - they’re listed below.

↑