Configure MySQL to keep log of all queries on Ubuntu Linux

MySQL logoIn this article I will show you how to enable keeping general log of all SQL queries for MySQL DBMS on Ubuntu based operating systems. I find this very useful when debugging Ajax powered applications like my Quick Chat for WordPress. This way it is also easier to understand how application works by monitoring SQL queries it uses. So lets get started...

To turn MySQL general log we must edit MySQL configuration file located in /etc/mysql/my.cnf on Ubuntu Linux system and we must do that with administrative privileges. So here's how to do that using CLI editor nano:

sudo nano /etc/mysql/my.cnf

Now we must locate following lines:

#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1

To turn MySQL general log on you must uncomment those two lines by removing # sign at the beginning of the line. Here are the results:

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

No press Ctrl^X and confirm saving changes. All that's left to do is to restart MySQL service using following command:

sudo service mysql restart

Now you can monitor your MySQL server queries in real time using tail command or taking a look into /var/log/mysql/mysql.log file. Here's how to do it using tail:

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

That's it. Be aware that MySQL general log is performance killer so don't turn this on for production Linux server, just for debugging. Last step is to find that bug that made you enable MySQL general log in the first place, good luck.

DevGenii

E-commerce is a breeze with Magento Certified Developer Plus & Zend Certified PHP Engineer nearby. Get in touch!

Leave a Reply

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