Linux下怎麼查看mysql資料庫操作記錄-mysql教程-PHP中文網

Linux下怎麼查看mysql資料庫操作記錄

王林

發布： 2023-06-01 17:52:19

轉載

1965 人瀏覽過

Linux下查看mysql資料庫操作記錄具體方法

MySQL中的參數general_log用來控制開啟、關閉MySQL查詢日誌,參數general_log_file用來控制查詢日誌的位置。如果你想判斷MySQL資料庫是否啟用了查詢日誌，可以使用以下指令。如果將general_log設為ON，則會開啟查詢日誌；如果設定為OFF，則會關閉查詢日誌。

mysql> show variables like &#39;%general_log%&#39;;
+------------------+------------------------------+
| Variable_name    | Value                        |
+------------------+------------------------------+
| general_log      | OFF                          |
| general_log_file | /var/lib/mysql/DB-Server.log |
+------------------+------------------------------+
2 rows in set (0.00 sec)

登入後複製

另外，MySQL的查詢日誌支援寫入檔案或寫入資料表兩種形式，這個由參數log_output控制，如下所示：

mysql> show variables like &#39;log_output&#39;;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)

登入後複製

開啟MySQL查詢日誌

mysql> set global general_log = on;
Query OK, 0 rows affected (0.11 sec)

mysql> show variables like &#39;general_log&#39;;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log   | ON    |
+---------------+-------+
1 row in set (0.02 sec)

登入後複製

關閉MySQL查詢日誌

mysql> show variables like &#39;general_log&#39;;
+---------------+-------+
| 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 &#39;general_log&#39;;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log   | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

登入後複製

設定日誌輸出方式為表

如果設定log_output=table的話，則日誌結果會記錄到名為gengera_log的表中，這表的預設引擎是CSV）。

mysql> show variables like &#39;log_output&#39;;
+---------------+-------+
| 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> 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 &#39;general%&#39;   |
| 2017-07-06 12:32:28 | root[root] @ localhost [] |         1 |         1 | Query        | show variables like &#39;log_output&#39; |
| 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.02 sec)

登入後複製

以上是Linux下怎麼查看mysql資料庫操作記錄的詳細內容。更多資訊請關注PHP中文網其他相關文章！