Shulou(Shulou.com)06/01 Report--

Friends who are familiar with mysql should know that the error log only records errors in the database layer, binlog only records add / delete / change records, but does not record who executes, only records the execution user name, although slowlog is detailed, it only records slow query sql information that exceeds the set value.

Only general-log records all operation logs, but it consumes 5% of the performance of the database, so there is generally no special need. In most cases, it is not open, such as some sql audits and unknown troubleshooting.

The method of opening it

The opening method is very simple.

# check the current status mysql > show variables like 'general%' first +-+-+ | Variable_name | Value | +-+- -+ | general_log | OFF | | general_log_file | / data/mysql/data/localhost.log | +-- +-- + 2 rows in set (0.00 sec) # can be added to my.cnf 1 on (0 off), of course, it takes a restart to take effect, it is a bit superfluous that general-log = 1log = / log/mysql_query.log path # you can also set the variable to change, 1 on (0 off), take effect immediately, do not restart, of course, the first choice is this set global general_log=1# log for frequent operations of the library, the amount of data generated will grow quickly, for the protection of the hard disk You can set other storage paths set global general_log_file=/tmp/general_log.log

Then open it and see if this file exists and generates a log. We see that localhost.log has been generated, because I am the default, so the name is like this.

# the total amount of ll is 3215528, RWMI / RW / RW / R 1 mysql mysql 56 September 5 11:32 auto.cnfdrwx- 2 mysql mysql 4096 September 17 14:09 gw-rw-rw---- 1 mysql mysql 25666 September 8 17:07 ib_buffer_pool-rw-rw----. 1 mysql mysql 1073741824 September 19 10:21 ibdata1-rw-rw----. 1 mysql mysql 1073741824 September 19 10:21 ib_logfile0-rw-rw----. 1 mysql mysql 1073741824 September 5 11:27 ib_logfile1-rw-rw---- 1 mysql mysql 5 September 8 17:11 localhost.localdomain.pid-rw-rw---- 1 mysql mysql 6699602 September 19 09:50 localhost.log-rw-rw---- 1 mysql mysql 5 September 14 09:16 localhost.piddrwx-. 2 mysql mysql 4096 September 5 11:27 mysql-rw-rw---- 1 mysql mysql 34539 September 7 14:57 mysql-bin.000006-rw-rw---- 1 mysql mysql 13746613 September 8 17:07 mysql-bin.000007-rw-rw---- 1 mysql mysql 498989 September 14 09:16 mysql-bin.000008-rw-rw---- 1 mysql mysql 48302055 September 19 10:20 mysql-bin. 000009 mysql mysql RW RW UV-1 mysql mysql 14 September 14 09:16 mysql-bin.index-rw-rw----. 1 mysql mysql 57569 September 14 09:16 mysql.errdrwx-. 2 mysql mysql 4096 September 5 11:27 performance_schemadrwx- 2 mysql mysql 4096 September 17 14:35 test

After opening it, it depends on how to analyze it.

Analysis log

In fact, it is quite intuitive, but it is easy to be confused. let's take a look at the example.

/ usr/local/mysql/bin/mysqld, Version: 5.6.32-78.0-log (Percona Server (GPL), Release 78.0, Revision 8a8e016). Started with:Tcp port: 3306 Unix socket: / tmp/mysql.sockTime Id Command Argument160919 9:28:19 30722 Connect root@192.168.1.252 on test 30722 Query SET SESSION sql_mode = REPLACE (@ @ sql_mode)) "STRICT_ALL_TABLES,", "),", STRICT_ALL_TABLES ","), "STRICT_ALL_TABLES", ""), "STRICT_TRANS_TABLES," "),", "STRICT_TRANS_TABLES", ""), "STRICT_TRANS_TABLES" ") 30722 Query SET NAMES utf8 30722 Query SELECT * FROM `type`Were `pid` = 30 30722 Quit160919 9:28:38 29975 Query SHOW GLOBAL STATUS160919 9:28:39 30728 Connect root@192.168.1.95 on test 30728 Query SET SESSION sql_mode = REPLACE (REPLACE (REPLACE) REPLACE (REPLACE (@ @ sql_mode) "STRICT_ALL_TABLES,", "),", STRICT_ALL_TABLES ","), "STRICT_ALL_TABLES", ""), "STRICT_TRANS_TABLES," ""), ", STRICT_TRANS_TABLES", ""), "STRICT_TRANS_TABLES", "") 30728 Query SET NAMES utf8 30728 Query SELECT `a`.`b`.clientname`, `b`.`deptname`, `b`.`receiveprovince`, `b`.`accouners` `b`.`receivecity`FROM`repri`Left JOIN `illmain`as `b`ON `a`.`illid` = `b`.`illID`where `a`.`id` = '21' 30728 Query SELECT `typename`FROM`type`WHERE `id` =' 8' 30728 Query SELECT `typename`FROM `type`WHERE `id` ='9' 30728 Query SELECT * FROM `handle`WHERE `id` = '21' 30728 Query SELECT * FROM `low_ `low_ `low` =' 21'AND `type` = 0ORDER BY `iddesc 30728 Query SELECT * FROM `guide`Were `ll_ type` ='9' 30728 Query SELECT * FROM `illmain`WERE `illid` = '0992016'OR `orderid` =' 0992016' 30728 Quit

Let's analyze it by column.

The first column: the time column, the first one is the date, and the last one is the hour and minute. Some of the reasons are not shown because these sql statements are executed almost simultaneously, so the time is not recorded separately.

The second column: the ID column, which is the thread ID of the first column out of show processlist. For long connections and some time-consuming sql statements, you can find out exactly which thread is running.

The third column: operation type, Connect is to connect to the database, Query is to query the database (additions, deletions, queries and changes are displayed as queries), you can worry about some specific operations.

The fourth column: details, for example, the details of the above example Connect is root@192.168.1.95 on test, which means that root@192.168.1.95 is connected to the test library, and so on, the following means what query operation has been done after the thread number 30728 is connected to the database.

There are other grant/drop/create/alter and other operations, general_log will record all, but here will not demonstrate in detail, you can have a try.

Finally, as I said at the beginning, with this information, it is possible to audit sql statements, there is no pressure to find those responsible, and the sql analysis of some difficult and complicated diseases is also very simple.

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.