Introduction to general_log log knowledge points in MySQL

The following operation demonstrations are all based on MySQL version 5.6.36:

I often encounter this problem at work: MySQL data access consumes a lot of energy, and I want to optimize it from the SQL aspect. R&D personnel often ask whether they can see which SQL statements are executed most frequently. Reply: No, you can only see the SQL currently running and the SQL recorded in the slow log. For performance reasons, the general log is usually not enabled. The slow log can locate some SQL statements with performance issues, while the general log will record all SQL statements. However, sometimes MySQL in production has performance problems. Turning on general log for a short period of time to obtain the SQL execution status is still very helpful for troubleshooting and analyzing MySQL performance problems. Or sometimes, you are not sure what SQL statement the program executed, but you need to troubleshoot the error. If you cannot find the cause, you can temporarily enable the general log.

In MySQL 5.0, if you want to enable slow log or general log, you need to restart the system. Starting from MySQL 5.1.6, general query log and slow query log start to support writing to files or database tables. In addition, the log enablement and output mode modification can be dynamically modified at the global level.

There are many ways to open the general log.

The following is a brief introduction and demonstration:

Method 1: Change the my.cnf configuration file

[root@git-server ~]# grep general_log /etc/my.cnf
general_log = 1
general_log_file = /tmp/general.log

Restart mysql, this operation is equivalent to permanent effect. Of course, this method is not allowed to be used in production. Because restarting MySQL will interrupt MySQL services. At the same time, general.log will record all DDL and DML statements about MySQL, which consumes a lot of resources. It is usually temporarily opened for a few minutes to help troubleshoot MySQL problems. It will be closed afterwards.

Method 2: Operation in MySQL command console

Only the root user has permission to access this file

By default this log is turned off.

mysql> show global variables like '%general%';
+------------------+---------------------------------+
| Variable_name | Value |
+------------------+---------------------------------+
| general_log | OFF |
| general_log_file | /data/mysql/data/git-server.log |
+------------------+---------------------------------+
2 rows in set (0.00 sec)

mysql>

Sometimes you need to temporarily enable MySQL's global general_log. You can log in to MySQL and directly set the log path and enable general_log.

mysql> set global general_log_file='/tmp/general_log';
Query OK, 0 rows affected (0.00 sec)

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

mysql> show global variables like '%general%';
+------------------+------------------+
| Variable_name | Value |
+------------------+------------------+
| general_log | ON |
| general_log_file | /tmp/general_log |
+------------------+------------------+
2 rows in set (0.00 sec)

mysql> 

[root@git-server ~]# tailf /tmp/general_log
180717 22:55:51 2 Query show databases
180717 22:56:04 2 Query SELECT DATABASE()
      2 Init DB test
180717 22:56:14 2 Query select * from student3

After use, you can directly set global general_log=off; close this log

Method 3: Save the log in the general_log table of the MySQL database

mysql> set global log_output='table';
mysql> set global general_log=on;
mysql> use mysql;
mysql> select * from test.student3;
+----+--------------+------------+--------+------+
| id | teacher_name | teacher_id | name | sex |
+----+--------------+------------+--------+------+
| 1 | Huahua| 1 | Sanan| Female|
| 4 | Sansan| 2 | Sanan| Female|
| 6 | bibi | 3 | Sanan | Female |
+----+--------------+------------+--------+------+
3 rows in set (0.00 sec)

mysql> select * from general_log;
| 2018-07-17 23:00:12 | root[root]@localhost [] | 2 | 1132333306 | Query | select * from test.student3

By looking at the information in /tmp/general.log, you can roughly see which SQL queries/updates/deletes/inserts are more frequent. For example, some tables do not change frequently but have a large query volume, so they can be cached. For tables that do not require high latency between the primary and standby databases, reads can be placed in the standby database.

The above is all the knowledge points about the general_log log introduced this time. Thank you for your reading and support for 123WORDPRESS.COM.