This article is available on GitHub at github.com/chengxy-nds…
Let’s take a quick look at the logical architecture of MySQL.
The logical architecture of MySQL can be roughly divided into three layers:
-
Layer 1: deal with client connection, authorization authentication, security verification, etc.
-
Layer 2: Server Server layer, responsible for SQL interpretation, analysis, optimization, implementation of the operation engine.
-
Layer 3: Storage engine, responsible for storage and extraction of data in MySQL.
We need to know that the server layer of MySQL does not manage transactions. Transactions are implemented by storage engine, and the storage engine supporting transactions in MySQL is the most widely used by InnoDB, so the storage engine mentioned in the subsequent paper is mainly InnoDB.
Remember! Remember! Remember! MySQL updates data in the redo log, bin log, and undo log files.
Redo log
Redo log is a transaction log of MySQL storage engine InnoDB.
MySQL data is stored in disks. Disk I/OS are required for each read/write operation. In concurrent scenarios, the performance is poor. MySQL provides an optimization means to introduce a Buffer Pool. This cache contains a partial mapping of the data pages on disk to relieve the disk strain on the database.
When data is read from the database, it is first read from the cache. If it is not in the cache, it is read from disk and put into the cache. When data is written to the database, it is first written to the cache. In this case, the data page in the cache changes and is called a dirty page. After the data is modified in the Buffer Pool, it is periodically flushed to disks according to the preset update policy.
MySQL downtime
If MySQL fails to flush dirty pages and restarts due to some reasons, the modified data in the Buffer Pool is not flushed to disk in time. As a result, data will be lost and transaction persistence cannot be guaranteed.
The redo log was introduced to solve this problem. The redo log, as its name suggests, focuses on redo! It records the changes made to each page in the database, rather than the changes made to a particular row or rows, and can be used to restore the physical data page after the commit, and only to the location of the last commit.
Redo log uses write-Ahead Logging (WAL) technology. The core of this technology is to Write logs before modifying records and ensure that the logs fall down first before the transaction is committed.
InnoDB writes the redo log first to the redo log, then to the Buffer Pool. Fsync is called to flush the redo log to disk when a transaction is committed. When data files updated in the cache are flushed to disk is handled asynchronously by background threads.
Note: The redo log is in the prepare state. The commit is not complete until the bin log is written to the disk.
This way, even if MySQL unexpectedly goes down before cleaning dirty pages, it is ok to parse and replay the changes in the redo log during restart.
Fixed size
Redo logs are written in a fixed size, circular redo log format. When the redo log is full, redo logs are written in a circular fashion.
So why design it like this?
Because the redo log records changes made to the data page, if the data page in the Buffer Pool has been flushed, those records are invalid. The new log overwrites and erases the invalid records.
In the preceding figure, write pos indicates the log sequence number (LSN) of the redo log. The log sequence number (LSN) is not flushed. The check point indicates the LSN after the redo log changes are flushed to the disk. The data on the LSN is continuously increased. All the data on the LSN is lost to the disk.
The empty portion of the redo log (green) between write POS and check point is used to record new logs. Between the check point and Write POS is the data page modification that was recorded in the Redo log before the data page was flushed back to disk. When the write POS catches up with the Check Point, it moves the Check point forward to clear the position (flush the disk) and then records a new log.
Note: The redo log is full. Before erasing, make sure that the data pages in memory have been flushed to disk. No new update requests can be received while old records are erased to make new space, and MySQL performance will deteriorate at this point. It is important to adjust the redo log file size in case of high concurrency.
crash-safe
Innodb engine has crash-safe capability because of the redo log. When MySQL restarts, Innodb automatically checks the redo log and restores data that has not been written to disk from the redo log.
When MySQL is started, it is always restored regardless of whether it was shut down properly or abnormally last time. The LSN in the data page is checked. If the LSN is smaller than the WRITE POS position in the Redo log, unfinished operations on the data page are recorded in the redo log. Data is then synchronized from the nearest check point.
For example, if the LSN of the redo log is 500 and the LSN of the data page is 300, some data is not flushed to disk before the restart. Therefore, the system rewrites logs 300 to 500 in the redo log.
Undo log
Undo log is also a transaction log belonging to MySQL storage engine InnoDB.
Undo log is a logical log. As its name suggests, undo log is mainly used for rollback, which is the key to ensuring atomicity of transactions. The undo log records the status before data modification. During data modification, a logical log that is contrary to the current operation is also recorded in the Undo log.
For example, if you update the name field of the record ID=1, the original data of name is small rich, now change the name to the programmer’s internal point
Update X set name = null where id =1 update X set name = null where id =1 In this way, when the service fails due to some reasons, the undo log can be used to roll back the data to the state before the transaction is executed to ensure the integrity of the transaction.
Some people may ask: if a record of the same thing has been modified for several times, is it necessary to write the state before the modification to undo log each time?
The answer is no!
The undo log records only the original version of the data that was modified before the transaction began. When the data was modified again, the redo log recorded the changes. The undo log performed rollback, and the redo log performed rollback.
The rollback
Uncommitted transactions, that is, transactions that have not performed a COMMIT. However, some of the dirty pages modified in this transaction may have been flushed. If the database instance goes down and restarts, a rollback is required to remove the previously flushed dirty blocks from the disk.
Roll forward
If the database instance crashes and restarts, roll forward to complete the incomplete committed transaction. If the database instance crashes and restarts, roll forward to complete the incomplete committed transaction. Restore and flush data from the redo log that was stored in memory due to an outage.
When a database instance is recovered, it is rolled forward and then rolled back.
The undo log, redo log, and bin log files are all flushed to disk before the dirty page is flushed. They work together to ensure that the data submitted by the user is not lost.
Bin log
Bin log is a logical log stored on disk in binary form at the database Server layer (engine independent). The bin log records all DDL and DML operations on the database (excluding commands such as SELECT and SHOW, which do not modify the data itself).
Binary logging is disabled by default. You can run the following command to check whether binary logs are enabled:
mysql> SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+
Copy the codeA bin log is also called an archive log because it does not iterate over previous records like a redo log. The maximum capacity of a binlog file is 1 GB by default (you can also change the value by using max_binlog_size). If a log file exceeds the maximum capacity, a new file will be created to continue writing.
mysql> show binary logs;
+-----------------+-----------+
| Log_name | File_size |
+-----------------+-----------+
| mysq-bin000001. | 8687 |
| mysq-bin000002. | 1445 |
| mysq-bin000003. | 3966 |
| mysq-bin000004. | 177 |
| mysq-bin000005. | 6405 |
| mysq-bin000006. | 177 |
| mysq-bin000007. | 154 |
| mysq-bin000008. | 154 |
Copy the codeThe bin log content format is actually the reverse logic of executing SQL commands, which is similar to undo log. Generally, when bin log is enabled, the expiration time (expire_logs_days) is set for log files. Otherwise, the log volume is very large.
mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| expire_logs_days | 0 |
+------------------+-------+
1 row in set
mysql> SET GLOBAL expire_logs_days=30;
Query OK, 0 rows affected
Copy the codeBin log is used to synchronize data between the master and slave nodes of MySQL. And point-in-time data restoration.
Master-slave synchronization
The following figure shows the master/slave replication process of MySQL to understand the application of bin log in master/slave mode.
-
Users perform DDL and DML operations in the master library and write the change records in the bin log sequence.
-
The I/O thread from the slave library connects to the Master and requests to read the log content of the specified position.
-
After receiving the slave request from the slave library, the Master pushes the log content after the specified position and the name and position of the bin log file in the Master library to the slave library.
-
After receiving data, the SLAVE I/O thread successively writes the received log content to the end of the relay log file and records the master library bin log file name and position in the master-info file for the next read.
-
After detecting the update in the relay log, the SLAVE SQL thread reads the log and parses it into an executable SQL statement. In this way, the data consistency between the master and slave libraries is realized.
Restore based on point in time
We saw that the bin log can also do data recovery, and the redo log can also do data recovery. What’s the difference?
-
Different levels: Redo log is implemented by InnoDB storage engine, bin log is implemented by MySQL server layer, but any changes made to the database by MySQL storage engine will generate bin log.
-
The redo log is used for crash recovery, ensuring that MySQL downtime does not affect persistence. Bin log is used for point-in-time recovery to ensure that the server can recover data and perform primary/secondary replication based on point in time.
-
The redo log is a physical log. The content is a disk based Page. The binlog is binary and can be set according to the binlog_format parameter.
-
The redo log is recorded in a log cycle. If the size of a file exceeds the specified value, subsequent logs are added to a new file.
-
The flush time is different: Bin log is written when the transaction is committed. The redo log is written when a transaction starts.
The bin log and redo log functions do not conflict but complement each other. The bin log and redo log functions are recorded at the same time to ensure that data is not lost when the database is restarted after a breakdown.
Relay log
The relay log file has the same format as the bin log file. The slave first reads binary log data from the master database and writes it to the slave database. Then asynchronously, the SQL thread reads and parses the relay log to execute the corresponding SQL command.
slow query log
Slow Query log: records the query statements that are executed in MySQL for a specified time. It is often used during SQL optimization. By slowly querying logs, you can find out which query statements are inefficient and time-consuming to execute.
To improve performance, this function is enabled only when you troubleshoot slow SQL queries and debug parameters. By default, the slow log query function is disabled. You can run the following command to check whether slow query logs are enabled:
mysql> SHOW VARIABLES LIKE 'slow_query%';
+---------------------+--------------------------------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /usr/local/mysql/data/iZ2zebfzaequ90bdlz820sZ-slow.log |
+---------------------+--------------------------------------------------------+
Copy the codeThe iz2zebfzaequ90bdlz820sz-slow. log file does not contain any content after executing the following command:
mysql> SET GLOBAL slow_query_log=ON;
Query OK, 0 rows affected
Copy the codeWhat is the threshold for slow queries that exceed the specified time? Let’s take a look with the long_query_time parameter and see that the default is 10 seconds.
mysql> SHOW VARIABLES LIKE 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
Copy the codeHere we change the long_query_time parameter to 0.001 seconds to execute the query SQL again to see if there is any change in the slow query log.
mysql> SET GLOBAL long_query_time=0.001;
Query OK, 0 rows affected
Copy the codeWhen the SQL is executed again, the execution time is greater than 0.001 seconds, and the slow query log starts to record.
general query log
The General Query log is used to record all user actions, including when the client connected to the server, all SQL sent by the client, and other events, such as MySQL service startup and shutdown. The MySQL server writes to the log file in the order in which it receives statements.
Generally, the Log information is too detailed and the volume of Log files is very large. Therefore, the Log function is disabled by default to improve performance. Usually, the Log function is enabled only when detailed logs are required for troubleshooting.
To check whether general query logs are enabled, run the following command:
mysql> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log | OFF |
+---------------+-------+
Copy the codeBelow, enable general log query and view the log location.
mysql> SET GLOBAL general_log=on;
Query OK, 0 rows affected
Copy the codemysql> show variables like 'general_log_file';
+------------------+---------------------------------------------------+
| Variable_name | Value |
+------------------+---------------------------------------------------+
| general_log_file | /usr/local/mysql/data/iZ2zebfzaequ90bdlz820sZ.log |
+------------------+---------------------------------------------------+
Copy the codeExecute a query SQL to see the change in log content.
mysql> select * from t_config;
+---------------------+------------+---------------------+---------------------+
| id | remark | create_time | last_modify_time |
+---------------------+------------+---------------------+---------------------+
| 1325741604307734530 |This is the broadcast schedule| 2020- 11- 09 18:06:44 | 2020- 11- 09 18:06:44 |
+---------------------+------------+---------------------+---------------------+
Copy the codeWe can see that the log details all the commands executed, SQL, SQL parsing, database Settings, and so on.
error log
Error log: it is the best understood type of log in MySQL. It records the time when the MySQL server starts and stops, as well as the diagnosis and error information.
By default, the log function is enabled. Run the following command to find the path for storing error log files.
mysql> SHOW VARIABLES LIKE 'log_error';
+---------------+----------------------------------------------------------------+
| Variable_name | Value |
+---------------+----------------------------------------------------------------+
| log_error | /usr/local/mysql/data/LAPTOP-UHQ6V8KP.err |
+---------------+----------------------------------------------------------------+
Copy the codeNote: Not all error information is recorded in the error log, such as how MySQL starts InnoDB tablespace files, initializes its storage engine, initializes its buffer pool, etc. These are also recorded in the error log file.
conclusion
MySQL as the middleware we most often contact in our work, skilled use is just a beginning, if you want to write a resume proficient, also need to have a deep understanding of its internal working principle, and these 7 kinds of logs are just a starting point in the in-depth learning process, endless learning, brother done!
I have sorted out hundreds of technical e-books. If you need them, please reply [666] on my public account of the same name. The technology group is almost full, the students who want to enter can add my friends, and the big guys blow technology together, looking forward to your joining.