mysql数据库操作日志_mysql查看数据库操作记录

mysql> show variables like 'log_output';+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_output | FILE |

+---------------+-------+

1 row in set (0.00sec)

mysql>

1、开启MySQL查询日志

mysql>set global general_log = on;

Query OK, 0 rows affected (0.11 sec)

mysql>show variables like 'general_log';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| general_log | ON |

+---------------+-------+

1 row in set (0.02 sec)

mysql>

2：关闭MySQL查询日志

mysql> show variables like 'general_log';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| general_log | ON |

+---------------+-------+

1 row in set (0.01 sec)

mysql> set global general_log=off;

Query OK, 0 rows affected (0.01 sec)

mysql> show variables like 'general_log';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| general_log | OFF |

+---------------+-------+

1 row in set (0.00 sec)

mysql>

3：设置日志输出方式为表(如果设置log_output=table的话，则日志结果会记录到名为gengera_log的表中，这表的默认引擎是CSV)：

mysql>show variables like 'log_output';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_output | FILE |

+---------------+-------+

1 row in set (0.00 sec)

mysql>set global log_output='table';

Query OK, 0 rows affected (0.00 sec)

mysql>show variables like 'log_output';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_output | TABLE |

+---------------+-------+

1 row in set (0.01 sec)

mysql>

4.查看查询日志信息。

mysql> select *from mysql.general_log;+---------------------+---------------------------+-----------+-----------+--------------+----------------------------------+

| event_time | user_host | thread_id | server_id | command_type | argument |

+---------------------+---------------------------+-----------+-----------+--------------+----------------------------------+

| 2017-07-06 12:32:05 | root[root] @ localhost [] | 1 | 1 | Query | show variables like 'general%' |

| 2017-07-06 12:32:28 | root[root] @ localhost [] | 1 | 1 | Query | show variables like 'log_output' |

| 2017-07-06 12:32:41 | root[root] @ localhost [] | 1 | 1 | Query | select * from MyDB.test |

| 2017-07-06 12:34:36 | [root] @ localhost [] | 3 | 1 | Connect | root@localhost on |

| 2017-07-06 12:34:36 | root[root] @ localhost [] | 3 | 1 | Query | KILL QUERY 1 |

| 2017-07-06 12:34:36 | root[root] @ localhost [] | 3 | 1 | Quit | |

| 2017-07-06 12:34:51 | root[root] @ localhost [] | 1 | 1 | Query | select * from mysql.general_log |

+---------------------+---------------------------+-----------+-----------+--------------+----------------------------------+

7 rows in set (0.02sec)

mysql>