Shulou(Shulou.com)05/31 Report--

This article mainly shows you "what are the general_log log knowledge points in mysql", which is easy to understand and clear, hoping to help you solve your doubts, the following let the editor lead you to study and learn "what are the general_log log knowledge points in mysql" this article.

The following demonstrations are based on the mysql5.6.36 version:

In our work, we often encounter such a problem: mysql data access is very powerful, and we want to optimize it from the sql aspect. Research and development often ask which SQL implementations can be seen more frequently? Replied: no, you can only see the currently running SQL and the SQL recorded in the slow log. Because for performance reasons, general log is generally not enabled. Slow log can locate some sql with performance problems, while general log records all SQL. However, sometimes there are performance problems with mysql in production. Turning on general log for a short time to obtain the execution of sql is of great help to troubleshooting and analyzing the performance problems of mysql. Or sometimes, if you don't know what sql statement the program executes, but you have to rule out errors and can't find the reason, you can open the general log log briefly.

The mysql5.0 version needs to be restarted if you want to enable slow log and general log. Starting from the MySQL5.1.6 version, general query log and slow query log support writing to a file or database table. The log can be enabled and the output method can be modified dynamically at the 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.cnfgeneral_log = 1general_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 it is necessary to restart mysql, it will interrupt the business of mysql. At the same time, general.log will record all the DDL and DML statements about mysql, which is very resource-consuming and is usually temporarily turned on for a few minutes when helping to troubleshoot mysql. It has to be closed afterwards.

Method 2: operate in the mysql command console

The 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 you need to enable the global general_log of MySQL temporarily. You can log in to mysql and directly set the path to the log 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_log180717 22:55:51 2 Query show databases180717 22:56:04 2 Query SELECT DATABASE () 2 Init DB test180717 22:56:14 2 Query select * from student3

You can close this log directly by set global general_log=off; after using it.

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 | scattered | 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 |

Looking at the / tmp/general.log information, you can roughly see which sql queries / updates / deletes / inserts are more frequent. For example, some tables do not change frequently, and the query volume is very large, so it is entirely possible for cache; to read tables that do not require high latency between master and slave, and so on.

The above is all the contents of the article "what are the knowledge points of general_log logs in mysql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.