千万不要在生产环境开启全局查询日志!!!
千万不要在生产环境开启全局查询日志!!!
千万不要在生产环境开启全局查询日志!!!
配置启用
在mysql的my.cnf(linux)或者my.ini(windows)中,设置如下:
# 开启
general_log=1
# 记录日志文件的路径
general_log_file=/path/logfile
# 输出格式
log_output=FILE
编码启用
-
开启
set global general_log=1; -
全局日志可以存放到日志文件中,也可以存放到Mysql系统表中。存放到日志中性能更好一些,存储到表中。
set global log_output=‘TABLE’; -
命令查看
select * from mysql.general_log;
mysql> set global general_log=1;
Query OK, 0 rows affected (0.05 sec)
mysql> set global log_output='TABLE';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from mysql.general_log;
+---------------------+------------------------------------+-----------+-----------+--------------+---------------------------------+
| event_time | user_host | thread_id | server_id | command_type | argument |
+---------------------+------------------------------------+-----------+-----------+--------------+---------------------------------+
| 2021-04-04 22:14:37 | root[root] @ localhost [127.0.0.1] | 12 | 0 | Init DB | bigdata |
| 2021-04-04 22:14:37 | root[root] @ localhost [127.0.0.1] | 12 | 0 | Query | select * from mysql.general_log |
+---------------------+------------------------------------+-----------+-----------+--------------+---------------------------------+
2 rows in set (0.07 sec)
mysql> select * from dept;
+----+--------+------------+----------+
| id | deptno | dname | loc |
+----+--------+------------+----------+
| 1 | 102 | aBDnFmvpoA | KYMNGSsk |
| 2 | 103 | YowqqEapzA | fZHnrWHw |
| 3 | 104 | nXakZrKzuw | bRdSeTeQ |
| 4 | 105 | QFHsUtjQAf | bORSLeoh |
| 5 | 106 | TVVUIKxjCl | yHSpYWMV |
| 6 | 107 | qSqbmcBBcI | kYoyzeYE |
| 7 | 108 | bvWyAimNda | UwzhoVNc |
| 8 | 109 | YKDNfnZgHR | pZbkWamm |
| 9 | 110 | vvOJfwUhbK | xfmRzWlm |
| 10 | 111 | FoCVXZgJaZ | VFrPaHiV |
| 11 | 112 | fPIWICMcYO | ZDTIKBDl |
| 12 | 113 | xBpXPjBgYz | BioVMVuj |
| 13 | 114 | OstTmDeMxa | MHZbjRHL |
| 14 | 115 | JleOHRpaev | RVfNzjwE |
| 15 | 116 | JGeCAVcAts | GdyglMaQ |
| 16 | 117 | dVqSrgEDdH | ZcooAMgx |
| 17 | 118 | TbAySPyYxs | vYFiZzyR |
| 18 | 119 | PyYugwQLjO | svZNPLiK |
| 19 | 120 | ZRkAWhXpFj | dPQHMRYo |
| 20 | 121 | BQBgdVvrwi | ClxEDcEP |
| 21 | 122 | mQrmhcRaCM | bYKGctJn |
| 22 | 123 | qOXwrsQYta | VCYkeSZw |
| 23 | 124 | jFzGGpGjbI | iSQzaDOk |
| 24 | 125 | LYJzxLpoDX | fIacmcCD |
| 25 | 126 | jlEluqtXFl | nGsTpSwE |
| 26 | 127 | IABimLUpQj | zROqpCQx |
| 27 | 128 | OBqadppFgS | RIOVmvtI |
| 28 | 129 | lcDKReVrXL | LxdcYOWs |
| 29 | 130 | WGsTqWKLzr | iSOqliip |
| 30 | 131 | bmfRQHKFUK | RcMCDhZC |
+----+--------+------------+----------+
30 rows in set (0.12 sec)
mysql> select * from mysql.general_log;
+---------------------+------------------------------------+-----------+-----------+--------------+---------------------------------+
| event_time | user_host | thread_id | server_id | command_type | argument |
+---------------------+------------------------------------+-----------+-----------+--------------+---------------------------------+
| 2021-04-04 22:14:37 | root[root] @ localhost [127.0.0.1] | 12 | 0 | Init DB | bigdata |
| 2021-04-04 22:14:37 | root[root] @ localhost [127.0.0.1] | 12 | 0 | Query | select * from mysql.general_log |
| 2021-04-04 22:15:11 | root[root] @ localhost [127.0.0.1] | 12 | 0 | Init DB | bigdata |
| 2021-04-04 22:15:11 | root[root] @ localhost [127.0.0.1] | 12 | 0 | Query | select * from dept |
| 2021-04-04 22:15:15 | root[root] @ localhost [127.0.0.1] | 12 | 0 | Init DB | bigdata |
| 2021-04-04 22:15:15 | root[root] @ localhost [127.0.0.1] | 12 | 0 | Query | select * from mysql.general_log |
+---------------------+------------------------------------+-----------+-----------+--------------+---------------------------------+
6 rows in set (0.10 sec)