全局查询日志

古城微笑少年丶 2022-09-12 07:54 180阅读 0赞

# 一 点睛 #

只允许在测试环境用，不允许在生产环境用。

# 二 配置启动 #

在 mysql 的 my.cnf 中，设置如下：

# 开启
    general_log = 1
    # 记录日志文件的路径
    general_log_file=/path/logfile
    # 输出格式
    log_output=FILE

# 三 命令启动 #

set global general_log=1;
    set global log_output='TABLE';
    # 此后，你编写的sql 语句，将会记录到mysql库里的general_log 表，可以用下面的命令查看
    select * from mysql.general_log;

# 四 实战 #

mysql> set global general_log=1;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> set global log_output='TABLE';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from emp limit 10;
    +----+--------+--------+------+--------+
    | id | empno  | name   | age  | deptId |
    +----+--------+--------+------+--------+
    |  1 | 100001 | dhCqWI |   41 |   7899 |
    |  2 | 100002 | oamjkx |   44 |    352 |
    |  3 | 100003 | gBnLPR |   46 |   3525 |
    |  4 | 100004 | xlLZMM |   40 |   3622 |
    |  5 | 100005 | mhYvjH |   43 |   3496 |
    |  6 | 100006 | PVjjtr |   41 |   7659 |
    |  7 | 100007 | izaCKS |   33 |   3127 |
    |  8 | 100008 | cnknJs |   49 |   6207 |
    |  9 | 100009 | oBREuk |   47 |   4940 |
    | 10 | 100010 | bJdzlF |   38 |   1092 |
    +----+--------+--------+------+--------+
    10 rows in set (0.00 sec)
    
    mysql> select * from mysql.general_log;
    +----------------------------+------------------------------+-----------+-----------+--------------+---------------------------------+
    | event_time                 | user_host                    | thread_id | server_id | command_type | argument                        |
    +----------------------------+------------------------------+-----------+-----------+--------------+---------------------------------+
    | 2021-09-23 08:47:20.232041 | root[root] @ localhost [::1] |         4 |         1 | Query        | select * from emp limit 10      |
    | 2021-09-23 08:47:58.393206 | root[root] @ localhost [::1] |         4 |         1 | Query        | select * from mysql.general_log |
    +----------------------------+------------------------------+-----------+-----------+--------------+---------------------------------+
    2 rows in set (0.00 sec)
    
    mysql> select * from emp limit 10;
    +----+--------+--------+------+--------+
    | id | empno  | name   | age  | deptId |
    +----+--------+--------+------+--------+
    |  1 | 100001 | dhCqWI |   41 |   7899 |
    |  2 | 100002 | oamjkx |   44 |    352 |
    |  3 | 100003 | gBnLPR |   46 |   3525 |
    |  4 | 100004 | xlLZMM |   40 |   3622 |
    |  5 | 100005 | mhYvjH |   43 |   3496 |
    |  6 | 100006 | PVjjtr |   41 |   7659 |
    |  7 | 100007 | izaCKS |   33 |   3127 |
    |  8 | 100008 | cnknJs |   49 |   6207 |
    |  9 | 100009 | oBREuk |   47 |   4940 |
    | 10 | 100010 | bJdzlF |   38 |   1092 |
    +----+--------+--------+------+--------+
    10 rows in set (0.00 sec)
    
    
    mysql> select * from mysql.general_log;
    +----------------------------+------------------------------+-----------+-----------+--------------+---------------------------------+
    | event_time                 | user_host                    | thread_id | server_id | command_type | argument                        |
    +----------------------------+------------------------------+-----------+-----------+--------------+---------------------------------+
    | 2021-09-23 08:47:20.232041 | root[root] @ localhost [::1] |         4 |         1 | Query        | select * from emp limit 10      |
    | 2021-09-23 08:47:58.393206 | root[root] @ localhost [::1] |         4 |         1 | Query        | select * from mysql.general_log |
    | 2021-09-23 08:49:00.284096 | root[root] @ localhost [::1] |         4 |         1 | Query        | select * from emp limit 10      |
    | 2021-09-23 08:49:04.648355 | root[root] @ localhost [::1] |         4 |         1 | Query        | select * from mysql.general_log |
    +----------------------------+------------------------------+-----------+-----------+--------------+---------------------------------+
    4 rows in set (0.00 sec)