This is the 7th day of my participation in the November Gwen Challenge. Check out the details: The last Gwen Challenge 2021

Log files record various types of activities that affect the mysql database. Common log files in mysql include the following four types:

  • The error log
  • Binary log
  • Slow Query logs
  • Query log

These log files can help us diagnose the running status of mysql database, so as to better optimize the database level.

The error log

Error log files record the startup, running, and shutdown of mysql. The error log records not only all error information, but also some warning information or correct information. You can find the error log path in the following ways:

mysql> show variables like 'log_error';
+---------------+----------------------------------+
| Variable_name | Value                            |
+---------------+----------------------------------+
| log_error     | ./weihengdeMacBook-Pro.local.err |
+---------------+----------------------------------+
1 row in set (0.00 sec)
Copy the code

You can see the full path of the error log. I haven’t made any adjustments on my machine here, so I use the host name as the file name of the error log by default. If your mysql database fails to start properly, the first file to look for is the error log file. Error logs may be recorded to find the cause of the failure. Part of the log is captured below:

2021-05-06T08:57:03.359102z 1 [System] [my-013576] [InnoDB] Initialization has started. 2021-05-06T08:57:03.439761z 1 [System] [my-013577] [InnoDB] Initialization has ended. 2021-05-06T08:57:03.495740z 0 [System] [my-011323] [Server] X Plugin ready for connections. bind-address: '127.0.0.1' port: 33060, socket: / TMP /mysqlx.sock 2021-05-06t08:57:03.521389z 0 [Warning] [my-010068] [Server] CA certificate ca.pem is self signed. 2021-05-06T08:57:03.521511z 0 [System] [my-013602] [Server] Channel mysql_main configured to support TLs.encrypted Connections are now supported for this channel. 2021-05-06T08:57:03.529529z 0 [System] [my-010931] [Server] / opt/homebrew/Cellar/mysql / 8.0.23 _1 / bin/mysqld: ready for connections. Version: '8.0.23 sockets: '/tmp/mysql.sock' port: 3306 Homebrew.Copy the code

Slow Query logs

long_query_time

You can set a threshold when mysql is started. All SQL statements that take longer than this threshold are logged to the slow query log. As follows:

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.02 sec)

mysql> show variables like 'log_slow_queries';
Empty set (0.01 sec)
Copy the code

After the long_QUERy_time threshold is set, the mysql database records all SQL statements whose running time exceeds the threshold, but not those whose running time is exactly equal to the long_QUERy_time threshold. Starting with mysql 5.1, long_query_time records the time an SQL statement runs in microseconds, as opposed to seconds before that.

log_queries_not_using_indexes

This is another parameter related to slow queries. This parameter indicates that if a SQL statement is run without an index, the mysql database will also log that SQL statement to the slow query log file.

mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+
1 row in set (0.00 sec)
Copy the code

log_throttle_queries_not_using_indexes

mysql> show variables like 'log_throttle_queries_not_using_indexes';
+----------------------------------------+-------+
| Variable_name                          | Value |
+----------------------------------------+-------+
| log_throttle_queries_not_using_indexes | 0     |
+----------------------------------------+-------+
1 row in set (0.01 sec)
Copy the code

Mysql 5.6.5 has added the log_throttLE_QUERies_NOT_using_INDEXES parameter to indicate the number of slow log SQL statements per minute that do not use indexes. The default value is 0, indicating that there is no limit. In a production environment, if no index is used, such SQL statements are frequently recorded in slow logs, resulting in an increase in slow log files. Therefore, you can control this by setting this value.