MySQL开启general_log并设置路径

在本地开发的时候，有时需要临时开启MySQL的全局log，查看每条sql执行记录。 

以下操作直接在mysql命令行里面执行，不需重启mysql。 
需要用root身份执行命令。

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

mysql> use l5m
Database changed
mysql> select * from t1;
+------+------+
| c1 | c2 |
+------+------+
| 1 | AA |
| 2 | BB |
| 3 | cc |
+------+------+
3 rows in set (0.00 sec)

mysql> set global general_log=off;
Query OK, 0 rows affected (0.00 sec)
在安装数据库的datadir就会产生一个文件，不过也可以通过set global general_log_file来指定存放的位置

[root@qht131 mysql]# pwd
/u01/mysql
[root@qht131 mysql]# ls -lth | more
total 125M
-rw-r-----. 1 mysql mysql 418 Mar 23 22:13 qht131.log
-rw-r-----. 1 mysql mysql 48M Mar 23 19:20 ib_logfile0
-rw-r-----. 1 mysql mysql 12M Mar 23 19:20 ibdata1
qht131.log里面记录了所有的操作记录

[root@qht131 mysql]# cat qht131.log
/usr/local/mysql/bin/mysqld, Version: 5.7.21-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /u01/mysql/mysql.sock
Time Id Command Argument
2018-03-23T14:13:24.753330Z 7 Query SELECT DATABASE()
2018-03-23T14:13:24.753576Z 7 Init DB l5m
2018-03-23T14:13:27.314225Z 7 Query select * from t1
2018-03-23T14:13:44.534767Z 7 Query set global
general_log=off这这个功能一般都不建议打开，只是偶尔需要的时候开一下。

另外还可以指定输出的log存放在表里面：

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

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

mysql> select * from t1;
+------+------+
| c1 | c2 |
+------+------+
| 1 | AA |
| 2 | BB |
| 3 | cc |
+------+------+
3 rows in set (0.00 sec)

mysql> insert into t1 values(4,'d');
Query OK, 1 row affected (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> set global general_log=off;
Query OK, 0 rows affected (0.00 sec)
默认存储在mysql.general_log中：

mysql> use mysql
Database changed

mysql> mysql> select * from general_log;
+----------------------------+---------------------------+-----------+-----------+--------------+------------------------------+
| event_time | user_host | thread_id | server_id | command_type | argument |
+----------------------------+---------------------------+-----------+-----------+--------------+------------------------------+
| 2018-03-23 22:23:46.559752 | root[root] @ localhost [] | 7 | 10000 | Query | select * from t1 |
| 2018-03-23 22:23:54.014160 | root[root] @ localhost [] | 7 | 10000 | Query | insert into t1 values(4,'d') |
| 2018-03-23 22:23:56.135555 | root[root] @ localhost [] | 7 | 10000 | Query | commit |
| 2018-03-23 22:24:01.324716 | root[root] @ localhost [] | 7 | 10000 | Query | set global general_log=off |
+----------------------------+---------------------------+-----------+-----------+--------------+------------------------------+

————————————————
版权声明：本文为CSDN博主「zuozhiji」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/jolly10/article/details/79666908