Mysql 全局查询日志

逆流者 2021年04月04日 250次浏览

千万不要在生产环境开启全局查询日志!!!
千万不要在生产环境开启全局查询日志!!!
千万不要在生产环境开启全局查询日志!!!

配置启用

在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)