How To Log Every Query That Goes Through MySQL

Ever wonder the easiest way to log each of your query in mysql? Here's the simplest way where you don't need to restart mysql everytime.


1. Open your command prompt window.

2. Browser to you mysql folder.
Eg :

cd Program Files\MySQL\MYSQL5.1\bin

3. Log into your mysql using cmd
Eg :

mysql -u root -p

You will be asked for your password, so enter your password

4. Now execute this statement..

SET GLOBAL log_output = 'TABLE';

then

SET GLOBAL general_log = 'ON';

And you're done. Check your general_log table in the 'mysql' database.

After certain period of time, you will realized that your general_log table is getting bigger and it will consume some time just to load. And btw, you can't delete the content just like that. So here's how to overcome that.

1. Follow step 1 and 2 as above.

2. Choose 'mysql' db by running this cmd..

USE mysql

3.Create a new table similar to general_log structure using this cmd

create table general_log_dummy like general_log

4. rename and replace the tables

rename table general_log to general_log_old, general_log_dummy to general_log

Now you have a new general_log table and you can delete the old table.