MySQL log type
Logs record the running status of the database and operations performed on the database. When a database is faulty, you can analyze and rectify the fault based on logs to recover the database. The MySQL log type is as follows:
| Log type | describe |
|---|---|
| Redo log | Redo logs are redo logs that record changes to physical data pages. Redo logs are written sequentially to physical files called redo log files |
| Rollback log (undo log) | A rollback log is a logical log. Unlike redo logs, undo only logically restores data to the state before the transaction, not from the physical page. |
| Binary log | Binary logs are logical logs that record database operations in binary files but do not record query statements |
| Errorlog (errorlog) | The error log records information about starting and stopping mysqld, as well as errors that occur while the server is running |
| Sllow Query log | Slow query logs record queries that take too long to execute and do not use indexes |
| General Query log | Every query and command received by the server is logged by the General Log, whether the query or command is invalid or even contains syntax errors |
| Relay log | Relay logs are binary like and can be used in an assignment schema to keep data consistent between the slave and master servers |
2. Slow query logs
Slow query logs record statements in the MySQL database whose response time exceeds the specified threshold. Slow query logging is also commonly referred to as slow logging because it is not only for SELECT statements, but also for INSERT, UPDATE, DELETE, and other statements where the response time exceeds a set threshold.
| parameter | describe |
|---|---|
| slow_query_log | Whether to enable slow log query. The value 1 is enabled and 0 is disabled |
| slow_query_log_file | Slow query log storage path (Optional). |
| long_query_time | Threshold. When the response time of an SQL statement exceeds the threshold, the statement will be logged |
| log_queries_not_using_indexes | Optionally, queries that do not use indexes are also logged to the slow query log |
| log_output | Log storage mode. The default value is FILE. FILE: logs are saved to a FILE. TABLE: logs are saved to a database. FILE,TABLE: logs are saved to a FILE and a database at the same time |
3. Enable slow log query
Slow query logs can be set temporarily by running commands or permanently by modifying the configuration file
# check whether the log is enabledshow variables like 'slow%'; # Temporarily enable slow query logsset global slow_query_log ='ON'; # set the threshold to1secondsset long_query_time=1; # Slowly query the log file locationshow variables like '%datadir%';
Copy the code