Activities

August 2012
M T W T F S S
« Jul   Sep »
 12345
6789101112
13141516171819
20212223242526
2728293031  

MySQL : Enable general log for Quick mysql check

One of the database server is heavily loaded when there is number of con-current users are increases. But sometimes it works better. So I want to debug the issue on real time scenario which will not be affected mysql server during the time.

Here is the steps the watch the run-time sql query statistics which are executed at the server end.

Turn on general log

1. Set general_log_file variable name to a valid file path.

#touch /backup/bkp/mysql_general.query.log
         #chown -R mysql.mysql /backup/bkp/mysql_general.query.log

Go to mysql console and set the variable “SET GLOBAL general_log_file=/path/to/file” and make it enable “SET GLOBAL general_log = 1″

mysql> SET GLOBAL general_log_file='/backup/bkp/mysql_general.query.log';
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL general_log = 1;
Query OK, 0 rows affected (0.06 sec)
mysql>
mysql> exit
Bye

2. Check the query output

[root@ML_web01 bkp]# tail -f /backup/bkp/mysql_general.query.log
FROM (`ml_system_values`)
WHERE `sv_label` = 'feddback_expiry_diff'
                 6172 Query     SELECT `genre_name`
FROM (`ml_track_genre`)
WHERE `track_genre_id` = '7'
                 6172 Query     SELECT `sv_value`, `sv_type`
FROM (`ml_system_values`)
WHERE `sv_label` = 'feddback_expiry_diff'
                 6172 Quit
120803 10:27:08  5804 Quit

3.How to turn off query
Set the variable general_log to off

mysql> SET GLOBAL general_log = 0;
Query OK, 0 rows affected (0.00 sec)
mysql>

Note : Do not turn this feature on for a long time since the log will be grow up according to the server busy and sometime make your server down.

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>