MySQL--->mysql view database operation Records

MySQL View database operation log the query log for MySQL logs all requests for MySQL databases. Whether or not these requests have been properly executed. The default file name is Hostname.log. By default, the MySQL query log is turned off. Production environment, if you turn on the MySQL query log, the performance still has a pretty big impact. In addition, many times, the MySQL slow query log can basically locate those performance problems of SQL, so the MySQL query log application of the scene is not much, a little bit of a feeling of chicken, it is a bit like the profiler in SQL Server, but this can not track a session, users, clients. It can only trace the entire database. The window environment

• Locate the My.ini, and add the following:
  Log= the path where the log is stored/my.log
• Save the file, restart the MySQL service, and then locate the My.Log file under the corresponding directory.

The Linux environment

• MySQL parameter general_log is used to control the opening and closing of the MySQL query log, parameter General_log_file to control the location of the query log. So if you want to determine if the MySQL database has opened the query log, you can use the following command. General_log opens the query log for on, and off indicates that the query log is turned off.

mysql> show variables like ‘%general_log%‘;+------------------+------------------------------+| Variable_name    | Value                        |+------------------+------------------------------+| general_log      | OFF                          

• In addition, the MySQL query log supports writing files or writing data tables in two forms, this is controlled by the parameter log_output, as follows:

mysql> show variables like ‘log_output‘;+---------------+-------+| Variable_name | Value |+---------------+-------+| log_output    | FILE  

• 1. Open MySQL Query log
  ```
  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 |[email protected]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)

Mysql>
```

Reference: https://www.cnblogs.com/kerrycode/p/7130403.html

MySQL--->mysql view database operation Records