Introduction of general_log Log Knowledge Points in mysql

The following operational demonstrations are based on mysql version 5.6. 36:

We often encounter such problems in our work: mysql data access energy is very large, and we want to optimize it from sql. R&D often asks which SQL executes frequently. Answer: No, you can only see the currently running SQL and the SQL recorded in the slow log. Because for performance reasons, 1 general log will not be turned on. slow log can locate 1 sql with performance problems, while general log records all SQL. However, sometimes mysql in production has performance problems. It is of great help to check and analyze the performance problems of mysql by opening general log in a short time to obtain the implementation of sql. Or sometimes, it is not clear what sql statement the program executed, but it is necessary to eliminate errors. If the reason cannot be found, the general log log can be opened briefly.

mysql version 5.0, if you want to open slow log and general log, you need to restart it. Starting from MySQL version 5.1. 6, general query log and slow query log support writing to files or database tables, and log opening and output mode modification can be dynamically modified at Global level.

There are many ways to turn on 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, which is equivalent to permanent effect. Of course, this method is not allowed to be used in production. Because you want to restart mysql, the service of mysql will be interrupted. At the same time, general. log will record all DDL and DML statements about mysql, which consumes resources. Generally, it is temporarily opened for a few minutes when helping to troubleshoot mysql. It will be closed afterwards.

Method 2: Operate in the mysql command console

root user is required to have access to 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 it is necessary to temporarily open the global general_log of MySQL. You can log in to mysql to directly set the log path and open 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

set global general_log=off; Close this log

Method 3: Save the log in the mysql database general_log table

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 |  Flower flower      |     1 | 3 Ann   |  Female   |
| 4 |  Scattering      |     2 | 3 Ann   |  Female   |
| 6 | bibi     |     3 | 3 Ann   |  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 

Looking at the info/tmp/general. log, you can see roughly which sql are queried/updated/deleted/inserted more frequently. For example, some tables do not change frequently, and the query volume is very large, so it can be cache;; For tables with low requirements for master-standby delay, reading can be put into standby library; Wait

The above is the introduction of general_log log of all knowledge points, thank you for reading and support of this site.