[Level 2] MySQL GeneralLog and Slow Query Log.

If you want MySQL to log "slow" queries, there are two ways to do.

In configuration file:

[mysqld]
general_log

slow_query_log

long_query_time=5

log_output=FILE,TABLE

In parameter:

#mysqld_safe --general_log --slow-query-log --long-query-time=5 --log-output=FILE,TABLE

Use command to change run-time environment:

mysql> set global general_log=1;

Query OK, 0 rows affected, 1 warning (0.18 sec)

mysql> set global slow_query_log=1;

Query OK, 0 rows affected, 1 warning (0.18 sec)

mysql> set global long_query_time=5;
Query OK, 0 rows affected (0.00 sec)

mysql> set local long_query_time=3;
Query OK, 0 rows affected (0.00 sec)

mysql> set global log_output='FILE,TABLE';
Query OK, 0 rows affected (0.00 sec)

mysql>

Check environment:

mysql> show global variables like 'general_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON   |
+----------------+-------+
1 row in set (0.00 sec)

mysql> show global variables like 'slow_query_log';

+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
1 row in set (0.00 sec)

mysql> show global variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 5.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

mysql> show local variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 3.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

mysql> show global variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE,TABLE  |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

Description:
a. general_log(--general-log)
  The flag for MySQL to decide to log slow queries or not, default is on
b. slow_query_log(--slow-query-log)
  The flag for MySQL to decide to log slow queries or not, default is off
c. long_query_time(--long-query-time)
  The thread hole for MySQL to decide which SQL statement should be logged.
d. log_output(--log-output)
  The destination for MySQL log, default is FILE.
  (MySQL 5.1.6 through 5.1.20, the default logging destination is TABLE)
e. General log table and Slow query log table can only support CSV/MyISAM storage,
   default is CSV. And if you want to change storage engine, please stop log first.

Wish this helps.

regards,
Stanley Huang