MySQL开启general_log并设置路径

最新推荐文章于 2024-06-21 01:01:02 发布

zuozhiji

最新推荐文章于 2024-06-21 01:01:02 发布

阅读量3.4k

点赞数

分类专栏： MYSQL

本文链接：https://blog.csdn.net/jolly10/article/details/79666908

版权

MYSQL 专栏收录该内容

108 篇文章 3 订阅

订阅专栏

在本地开发的时候，有时需要临时开启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   |
+----------------------------+---------------------------+-----------+-----------+--------------+------------------------------+
4 rows in set (0.00 sec)