(1) Overview

There are several types of logs in MySQL: redo log, undo log, bin log, error log, slow Query log, and General log.

For example, transaction operations are recorded in the redo log, undo log, and binlog. Data changes are recorded in the binlog. This chapter introduces the concepts and basic usage of MySQL log files. For developers, the most likely use of these types of logs is the slow query log.

Redo log

The redo log is a disk-based data structure used to correct incomplete transaction execution data in the event of a MySQL outage. The redo log records the status of a transaction execution.

When a transaction is started, redo logs are generated and written to the redo log file as the transaction executes. The redo log file records a (b) (4) change to a (B) (4) page. We all know that database updates are performed in memory before being flushed to disk.

A redo log is a file that has been updated in memory for the purpose of restoring data that was not flushed to disk due to an outage, for example.

(3) Undo log

Undo log is a logical log that is used to roll back and forth to a certain version. The undolog records the data before the modification. For example, when deleting a record, Undolog records a corresponding INSERT record to ensure that the data can be restored before the modification. When a transaction rollback is performed, the contents of the undo log can be used to roll back the transaction.

Undo log can also provide multi-version read under concurrent control (MVCC).

Bin log

The bin log of MySQL is used to record data about MySQL additions, deletions, and changes. In simple terms, when you perform an SQL operation that updates the contents of the database, a bin log is added. The query operation is not recorded in the bin log. The bin log is most useful for master/slave replication and database recovery.

You can run the following command to check whether binlog is enabled

show VARIABLES like '%log_bin%'
Copy the code

You can enable binlog as follows:

log-bin=mysql-bin
server-id=1
binlog_format=ROW
Copy the code

Log-bin specifies the name of the log file. By default, the log file is saved in the database directory. You can run the following command to view the log file

show VARIABLES like '%datadir%'
Copy the code

(5) Error log

The error log is used to record errors during the startup, shutdown, or running of MySQL. In the my. CNF configuration file of MySQL, you can run the log-error=/var/log/mysqld.log command to log the error of MySQL.

Run the MySQL command

 show variables like "%log_error%";
Copy the code

You can also get the location of the error log.

(6) Slow Query log

Slow query logs record SQL statements whose execution time exceeds the specified threshold. Slow query logs are used to optimize SQL statements in the production environment. You can run the following statement to check whether the slow query log is enabled and the log location:

 show variables like "%slow_query%";
Copy the code

Common configuration parameters for slow log query are as follows:

slow_query_log=1# enable slow query log0Shut down,1Open slow_query_log_file = / usr/local/mysql/mysql -8.020./data/slow-log.log5.6Long_query_time =1Log_queries_not_using_indexes # Indicates that unindexed SQL is also loggedCopy the code

Analyze slow logs. Specialized log analysis tools are used to query logs. After finding the slow SQL, you can use the EXPLAIN keyword to analyze the SQL to find out the cause of the slow.

General log

The general log records client connection information and SQL statement information executed through MySQL commands

show variables like '%general_log%';
Copy the code

You can view whether general Log is enabled and the log location.

The general log can be started using the configuration file. The parameters are as follows:

general_log = on
general_log_file = /usr/local/mysql/mysql8.020./data/hecs- 78422.log
Copy the code

Ordinary query logs record information about additions, deletions, changes, and searches, so they are generally disabled.