Ok, so you have a project where the client asks you to bypass the Magento API because it’s so painfully slow…what do you do? Not wanting to start digging through the haystack that is Magento to find the needles of MySQL I began Googling for an answer. What I quickly learned is that Magento’s MySQL queries are abstracted through PDO. Therefore I wouldn’t be able to get what I wanted through digging through source code. At this point I began wondering if there was some sort of application that could act as a proxy and then it dawned on me. I wondered if there was some sort of logging that could be done in MySQL to capture every database transaction.

Surprise surprise, there is a very simple way to enable query logging in MySQL! First off you will need shell access to your Linux box so that you can open up /etc/my.cnf which is MySQL’s configuration file. Once you’ve opened that file insert the following and close/save:


[mysqld]
log=/var/log/mysqld.log

If /var/log/mysqld.log doesn’t exist on your system you will need to create it. You can do so by running:

bash> touch /var/log/mysqld.log

Then set owner and group owner for the mysqld.log file to allow MySQL server to write to the file

bash> chown mysql:mysql /var/log/mysqld.log

At this point you should restart MySQL server. On CentOS you can simply run:

bash> /sbin/service mysqld restart

Now you should be ready to watch real-time MySQL transactions. To see those transactions you can run the following command:

bash> tail -f /var/log/mysqld.log

What I suggest doing at this point is to navigate to a particular process in Magento that you are wanting to get RAW MySQL. Stop tailing the log file and then run:

bash> cat /dev/null > /var/log/mysqld.log

This will empty the contents of the MySQL log file.  Start your tail of the log file again and run the Magento process. You should now have an excellent snap shot of exactly what MySQL is being run for a given process.

Leave a Reply