1. 普通查询日志(general query log)介绍

普通查询日志记录客户端连接信息和执行的sql语句信息

1.1 临时开启general_log日志开关

mysql> show variables like 'general_log%';
+------------------+---------------------------+
| Variable_name    | Value                     |
+------------------+---------------------------+
| general_log      | OFF                       |            -> 默认为关闭状态
| general_log_file | /data/3306/data/node1.log |
+------------------+---------------------------+
mysql> set global general_log = ON;
mysql> set global general_log_file = "/data/3306/data/general_90root.log";
mysql> show variables like 'general_log%';
+------------------+------------------------------------+
| Variable_name    | Value                              |
+------------------+------------------------------------+
| general_log      | ON                                 |
| general_log_file | /data/3306/data/general_90root.log |
+------------------+------------------------------------+
## 以上配置临时生效

1.2 永久开启general_log日志开关

[root@node1 ~]# cat /data/3306/my.cnf
[mysqld]
character_set_server = utf8
general_log = ON
general_log_file = "/data/3306/data/general_90root.log"; 
[root@node1 ~]# /data/3306/mysql restart
[root@node1 ~]#  mysql -uroot -p90root3306 -S /data/3306/mysql.sock
mysql> show variables like 'general_log%';
+------------------+-------------------------------------+
| Variable_name    | Value                               |
+------------------+-------------------------------------+
| general_log      | ON                                  |
| general_log_file | /data/3306/data/general_90root.log  |
+------------------+-------------------------------------+
[root@node1 ~]# tail -f /data/3306/data/general_90root.log
160723  4:54:49     4 Query     show databases
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| 90_root            |
| 90root             |
| 90root_gbk         |
| 90root_test        |
| ab                 |
| mysql              |
| performance_schema |
| test               |
| test_sz            |
+--------------------+

企业里一般是关闭的, 因为性能的问题

2. 慢查询日志(slow query log)介绍

慢查询日志只记录执行时间超出指定值的sql语句

慢查询的设置对于数据库sql的优化非常重要

[root@node1 ~]# cat /data/3306/my.cnf
[mysqld]
long_query_time     = 1                 -> sql执行语句超过1秒记录到慢查询日志
slow-query-log-file   = /data/3306/slow.log     -> 慢查询日志文件路径
log_queries_not_using_indexes           -> 没有走索引的sql语句记录到慢查询日志
[root@node1 ~]# /data/3306/mysql restart