MySQL general log、slow log配置及管理

总述：

为了性能考虑，一般general log不会开启。

slow log可以定位一些有性能问题的sql，而general log会记录所有的SQL。

从MySQL5.1.6版开始，general log和slow log开始支持写到文件或者数据库表两种方式，并且日志的开启，输出方式的修改，都可以在Global级别动态修改。

注：此前的版本开启这两种日志需要重启数据库。

日志写到文件的方式

log_output={TABLE| FILE|NONE}

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

日志写到TABLE的方式

如果设置log_output=table的话，则日志结果会记录到名为general_log的表中，这表的默认引擎都是CSV。

mysql> <span style="color:#ff0000;">set global log_output='TABLE';</span>
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%log_out%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | <span style="color:#ff0000;">TABLE</span> |
+---------------+-------+
1 row in set (0.01 sec)

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> <span style="color:#ff0000;">show create table general_log;</span>
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                                                                                                                                                                  |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| general_log | CREATE TABLE `general_log` (
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `thread_id` bigint(21) unsigned NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `command_type` varchar(64) NOT NULL,
  `argument` mediumtext NOT NULL
) <span style="color:#ff0000;">ENGINE=CSV</span> DEFAULT CHARSET=utf8 COMMENT='General log' |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

动态开启、修改general log

mysql> show variables like '%general%';
+------------------+----------------------------+
| Variable_name    | Value                      |
+------------------+----------------------------+
| general_log      | OFF                        |
| general_log_file | /dbdata/data/mysqlrep2.log |
+------------------+----------------------------+
2 rows in set (0.02 sec)

mysql> 
mysql> 
mysql> set global general_log=on;
Query OK, 0 rows affected (0.03 sec)

mysql> show variables like '%general%';
+------------------+----------------------------+
| Variable_name    | Value                      |
+------------------+----------------------------+
| general_log      | ON                         |
| general_log_file | /dbdata/data/mysqlrep2.log |
+------------------+----------------------------+
2 rows in set (0.00 sec)

mysql> set global general_log_file='/dbdata/data/logs/general.log';
Query OK, 0 rows affected (0.03 sec)
mysql> show variables like '%general%';
+------------------+-------------------------------+
| Variable_name    | Value                         |
+------------------+-------------------------------+
| general_log      | ON                            |
| general_log_file | /dbdata/data/logs/general.log |
+------------------+-------------------------------+
2 rows in set (0.00 sec)
[root@mysqlrep2 logs]# ll
total 20
-rw-rw----. 1 mysql mysql 5669 Nov  6 03:14 error.log
-rw-r-----. 1 mysql mysql 7594 Nov  5 07:37 error.log.old
-rw-rw----. 1 mysql mysql  188 Nov  6 03:23 general.log

SQL_LOG_OFF

general log会记录所有执行的sql，如果不想让general log记录当前session执行的sql，可以设置参数sql_log_off。

SET sql_log_off = 'ON|OFF';

默认是OFF,记录所有sql。

mysql> show variables like '%sql_log%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_off   | OFF   |
+---------------+-------+
2 rows in set (0.00 sec)

mysql> 
mysql> set sql_log_off=on;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> show variables like '%sql_log%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_off   | ON    |
+---------------+-------+
2 rows in set (0.00 sec)

同理，关于slow query log操作类似。

mysql> show create table slow_log;
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| slow_log | CREATE TABLE `slow_log` (
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `query_time` time NOT NULL,
  `lock_time` time NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumtext NOT NULL,
  `thread_id` bigint(21) unsigned NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> show variables like '%slow_query%';
+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| slow_query_log      | OFF                             |
| slow_query_log_file | /dbdata/data/mysqlrep2-slow.log |
+---------------------+---------------------------------+
2 rows in set (0.00 sec)

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

mysql> show variables like '%slow_query%';
+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| slow_query_log      | ON                              |
| slow_query_log_file | /dbdata/data/mysqlrep2-slow.log |
+---------------------+---------------------------------+
2 rows in set (0.00 sec)