How to view mysql database operation records under Linux-Mysql Tutorial-php.cn

How to view mysql database operation records under Linux

王林

Release： 2023-06-01 17:52:19

forward

1965 people have browsed it

Specific method to view mysql database operation records under Linux

The parameter general_log in MySQL is used to control the opening and closing of the MySQL query log, and the parameter general_log_file is used to control Query the location of the log. If you want to determine whether the MySQL database has query logging enabled, you can use the following command. If general_log is set to ON, the query log will be turned on; if it is set to OFF, the query log will be turned 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)

Copy after login

In addition, MySQL’s query log supports writing to files or writing to data tables. This is controlled by the parameter log_output, as shown below:

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

Copy after login

Open MySQL query log

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)

Copy after login

Close MySQL query log

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)

Copy after login

Set the log output mode to table

If log_output=table is set, the log results will be recorded to the table named gengera_log. The default engine is 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)

Copy after login

View query log information.

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)

Copy after login

The above is the detailed content of How to view mysql database operation records under Linux. For more information, please follow other related articles on the PHP Chinese website!