Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "Why MySQL will not lose data". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Before getting to the point, let's take a brief look at the logical architecture of MySQL. I believe I can use it.

MySQL logical architecture

The logical architecture of MySQL can be roughly divided into three layers:

The second layer: server server layer, which is responsible for SQL interpretation, analysis, optimization, execution of operation engine and so on.

We should know that the server layer of MySQL does not manage transactions, transactions are implemented by the storage engine, and the storage engine that supports transactions in MySQL is the most widely used by InnoDB, so the storage engines mentioned in the following articles are mainly based on InnoDB.

MySQL data update process

Remember! Remember! Remember! In the picture above, she is the basic process for MySQL to update data, including the general relationship among redo log, bin log and undo log logs.

Redo log (redo log)

Redo log belongs to the transaction log of the MySQL storage engine InnoDB.

The data of MySQL is stored on disk, and each time you read and write the data, you need to do disk IO operation. The performance will be very poor in concurrent scenarios. For this reason, MySQL provides an optimization means to introduce cache Buffer Pool. This cache contains the mapping of some data pages (page) on the disk to relieve the disk pressure on the database.

When reading data from the database, it is first read from the cache, and if it is not in the cache, it is read from the disk and put into the cache; when writing data to the database, it is written to the cache first, and the data page in the cache changes. This data page is called a dirty page. After modifying the data in Buffer Pool, it will be scrubbed to the disk regularly according to the set update strategy. This process is called scrubbing the dirty page.

MySQL downtime

If the dirty page is not finished, MySQL can be down and restart for some reason. If the modified data in Buffer Pool is not brushed to disk in time, the data will be lost and the persistence of the transaction cannot be guaranteed.

To solve this problem, redo log,redo Log is introduced as its name focuses on redoing! It records the modification of each page in the database, not how a row or rows are modified, and can be used to restore the physical data page after submission, and can only be restored to the location where it was last committed.

Redo log uses WAL (Write-Ahead Logging) technology, the core of this technology is that before modifying the record, you must write the log and ensure that the log is closed before the transaction commit is completed.

When you have redo log to modify the data, the InnoDB engine will first write the update record in redo log, modify the data in Buffer Pool, and when committing the transaction, call fsync to brush redo log into disk. As for when the updated data files in the cache are brushed to disk, they are processed asynchronously by the background thread.

Note: at this time, the transaction status of redo log is prepare, and the transaction has not been successfully committed yet. The transaction will not be considered to be completed until the bin log log is written to disk before it is changed to commit.

In this way, it doesn't matter if the MySQL goes down unexpectedly before brushing the dirty page, as long as the change record in redo log is parsed and replayed during reboot, and then the disk is refreshed.

Fixed size

Redo log uses a fixed-size, circular write format, and when the redo log is full, it writes in a loop from scratch, forming a ring.

Then why is it designed in this way?

Because redo log records changes on data pages, if the data pages in Buffer Pool have been scrubbed to disk, these records will be invalidated, and the new log will overwrite and erase these invalid records.

The write pos in the figure above represents the log sequence number LSN (log sequence number) currently recorded by redo log. The write has not been flushed, and the cycle is incremented; check point means that the modification record in redo log has been brushed into the LSN after the disk, and the cycle is incremented back, and all the data before this LSN has been removed.

The part between write pos and check point is the free part of redo log (green), which is used to record new logs; between check point and write pos is the modification data of the data page already recorded by redo log, and the data page has not been brushed back to disk. When write pos catches up with check point, it pushes check point forward first, freeing up the location (flushing disk) before recording a new log.

Note: the redo log log is full. Before erasing, you need to make sure that the data pages that are being erased and recorded in memory have been brushed to disk. During the period of erasing old records and freeing up new space, new update requests can no longer be received, and the performance of MySQL will degrade at this time. Therefore, in the case of large concurrency, it is very important to adjust the file size of redo log reasonably.

Crash-safe

Because of the existence of redo log, the Innodb engine has the ability of crash-safe, that is, when the MySQL is down and restarts, the system will automatically check the redo log and restore the modified data that has not been written to the disk from redo log to MySQL.

The LSN in the data page is checked first. If the LSN is less than the LSN in the redo log, that is, the write pos location, it indicates that the outstanding operations on the data page are recorded on the redo log, and then the data is synchronized from the nearest check point.

Simple understanding, for example: the LSN of redo log is 500, and the LSN of data page is 300, indicating that some of the data is not fully flushed to disk before reboot, then the system replays the records of LSN serial numbers 300 to 500 in redo log.

Undo log (rollback log)

Undo log is also a transaction log that belongs to the MySQL storage engine InnoDB.

Undo log belongs to logical log, such as its name mainly plays the role of rollback, it is the key to ensure the atomicity of transactions. The state of the data before modification is recorded, and in the process of data modification, a logical log opposite to the current operation is recorded to undo log.

Let's take a chestnut: if you update the name field of the ID=1 record, the original data of name is Xiaofu, and now change name to something inside the programmer.

When a transaction executes the statement update X set name = programmer where id = 1, a record of update X set name = rich where id = 1 with opposite logic will be recorded in undo log, so that when the service exception transaction fails for some reason, the data can be rolled back to the state before the transaction execution with the help of undo log to ensure the integrity of the transaction.

Then someone may ask: if a record in the same thing is modified many times, does it have to write the state of the data before modification to undo log each time?

The answer is no!

Undo log is only responsible for recording the original version of the data to be modified before the transaction starts. When we modify this row of data again, the resulting modification record will be written to redo log,undo log to complete the rollback, and redo log to complete the roll forward.

Roll back

An uncommitted transaction, that is, the transaction did not execute the commit. However, some of the dirty pages modified within the transaction may have been brushed. If the database instance is down and restarted at this time, you need to use a rollback to remove the dirty blocks that have been brushed off the disk.

Roll forward

A transaction that is not fully committed, that is, the transaction has already performed commit, but only part of the data in the dirty pages modified within the transaction has been flushed, and the other part is still on the buffer pool cache. If the database instance is down and restarted, you need to roll forward to complete the transaction that is not fully committed. Recover the previous part of the data that was not flushed in memory due to downtime from redo log and flush it into disk.

When the database instance is restored, roll forward first and then roll back.

If you take a closer look at the MySQL data update flow chart above, you will find that undo log, redo log and bin log logs are all brushed to disk before the dirty pages are brushed, and the cooperation between them ensures that the data submitted by users will not be lost.

Bin log (archive log)

Bin log is a database Server layer (independent of any engine) that stores logical logs on disk in binary form. Bin log records all DDL and DML operations in the database (excluding commands such as SELECT and SHOW, because such operations do not modify the data itself).

By default, binary logging is turned off. You can check whether the binary log is turned on with the following command:

Mysql > SHOW VARIABLES LIKE 'log_bin'; +-+-+ | Variable_name | Value | +-+-+ | log_bin | OFF | +-+-+

Bin log is also called an archive log because it does not cycle through records before erasure as redo log does, but logs all the time. By default, a binlog log file has a maximum capacity of 1G (it can also be modified by the max_binlog_size parameter). If a single log exceeds the maximum value, a new file will be created to continue writing.

Mysql > show binary logs +-+-+ | Log_name | File_size | +-+-+ | mysq-bin.000001 | 8687 | | mysq-bin.000002 | 1445 | mysq-bin.000003 | 3966 | | mysq-bin.000004 | 177| | Mysq-bin.000005 | 6405 | mysq-bin.000006 | 177th | mysq-bin.000007 | 154th | | mysq-bin.000008 | 154th |

The content format of the bin log log is actually the reverse logic of executing the SQL command, which is a bit similar to undo log. Generally speaking, when bin log is enabled, the expiration time of the log file is set (expire_logs_days parameter, which is saved permanently by default), otherwise the log volume will be 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

Bin log is mainly used in MySQL master-slave mode (master-slave), data synchronization between master-slave nodes, and data restoration based on point-in-time.

Master-slave synchronization

Through the master-slave replication process of MySQL in the following figure, we can understand the application of bin log in master-slave mode.

Users perform DDL and DML operations in the main library master, and modify the record order to write to bin log

Connect to the Master from the I / O thread of the library slave and request to read the log contents of the specified location position

After receiving the slave request from the slave library, Master pushes the contents of the log after the specified location position, the name of the master library bin log file and the location in the log to the slave library

After receiving the data, the slave I / O thread writes the received log contents to the end of the relay log file in turn, and records the read main library bin log file name and location position into the master-info file so that it can be used in the next read.

After the SQL thread of slave detects the content update in relay log, it reads the log and parses it into executable SQL statements, thus achieving data consistency between master and slave libraries.

Restore based on point in time

We see that bin log can also do data recovery, and so can redo log, so what's the difference between them?

The hierarchy is different: redo log is implemented by the InnoDB storage engine, and bin log is implemented by the server layer of MySQL, but any changes made to the database by the storage engine in the MySQL database will generate bin log.

Different functions: redo log is used for collision recovery (crash recovery) to ensure that MySQL outage will not affect persistence; bin log is used for point-in-time recovery (point-in-time recovery) to ensure that the server can recover data and master-slave replication based on point-in-time.

The content is different: redo log is a physical log, and the content of a disk-based page Page;bin log is binary, which can be set according to the binlog_format parameter.

The writing mode is different: redo log records it in a circular way; binlog records it by appending. When the file size is larger than the given value, subsequent logs will be recorded on the new file.

The timing of flushing is different: bin log writes when the transaction commits; redo log starts writing at the beginning of the transaction.

The functions of bin log and redo log do not conflict but complement each other. They need to be recorded at the same time to ensure that when the database is down and restarted, the data will not be lost.

Relay log (relay log)

The relay log log file has the same format as the bin log log file. From the above MySQL master-slave replication process, we can see that relay log plays a transit role. Slave first reads the binary log data from the master library master, writes it locally from the slave library, and then asynchronously reads and parses the relay log to execute the corresponding SQL command.

Slow query log

Slow query log (slow query log): used to record queries that have been executed for more than a specified time in MySQL and are often used during SQL optimization. Through the slow query log, we can find out which query statements are inefficient and time-consuming.

For performance reasons, it is only enabled when troubleshooting slow SQL and debugging parameters. By default, the slow query log feature is disabled. You can check whether the slow query log is enabled with the following command:

Mysql > SHOW VARIABLES LIKE 'slow_query%' +-- + | Variable_name | Value | +- -+ | slow_query_log | OFF | | slow_query_log_file | / Usr/local/mysql/data/iZ2zebfzaequ90bdlz820sZ-slow.log | +-+-+

After opening the slow log with the following command, I found that there was no content in the iZ2zebfzaequ90bdlz820sZ-slow.log log file, probably because the SQL I executed was relatively simple and did not exceed the specified time.

Mysql > SET GLOBAL slow_query_log=ON; Query OK, 0 rows affected

It is mentioned above that only the query statements exceeding the specified time can be regarded as slow queries, so what is the time threshold? Let's take a look at the long_query_time parameter and find that the default is 10 seconds.

Mysql > SHOW VARIABLES LIKE 'long_query_time'; +-+ | Variable_name | Value | +-+-+ | long_query_time | 10.000000 | +-+-+

Here we reduce the long_query_time parameter to 0.001 seconds and execute the query SQL again to see if there are any changes in the slow query log.

Mysql > SET GLOBAL long_query_time=0.001; Query OK, 0 rows affected

Sure enough, when the SQL was executed again, the execution time was more than 0.001 seconds, and it was found that the slow query log began to be recorded.

Slow query log

General query log

General query log (general query log): used to record all the actions of the user, including when the client connected to the server, all SQL sent by the client, and other events, such as MySQL service startup and shutdown, etc. The MySQL server writes to the log file in the order in which it receives the statements.

Because the contents of general query log records are too detailed, the volume of Log files after opening will be very large, so for performance considerations, the log feature is turned off by default, and usually will only be temporarily enabled when you need to obtain detailed logs for troubleshooting.

We can check whether the general query log is enabled with the following command:

Mysql > show variables like 'general_log'; +-+-+ | Variable_name | Value | +-+-+ | general_log | OFF | +-+-+

Below, open the general query log and check the location where the log is stored.

Mysql > SET GLOBAL general_log=on; Query OK, 0 rows affected mysql > show variables like 'general_log_file' +-- + | Variable_name | Value | +- -+-+ | general_log_file | / usr/local/mysql/data/iZ2zebfzaequ90bdlz820sZ.log | +-+- -+

Execute a query SQL to see the changes in the log contents.

Mysql > select * from t_config +-+ | id | remark | create_time | last_modify_time | +- -+ | 1325741604307734530 | I am the broadcast table | 2020-11-09 18:06:44 | 2020-11-09 18:06:44 | +- -- +

We see that the log details all executed commands, SQL, SQL parsing process, database settings, and so on.

General query log

Error log

Error log (error log): should be one of the best understood logs in MySQL, mainly recording the time each time the MySQL server starts and stops, as well as diagnostic and error messages.

By default, this logging function is turned on, and you can find the path where the error log file is stored by the following command.

Mysql > SHOW VARIABLES LIKE 'log_error' +-+-- + | Variable_name | Value | +-- -+-+ | log_error | / usr/local/mysql/data/LAPTOP-UHQ6V8KP.err | +-+-- -- +

Note: not all error messages are recorded in the error log, such as how MySQL starts InnoDB's tablespace file, how to initialize its own storage engine, initialize buffer pool, etc., which are also recorded in the error log file.

This is the end of the content of "Why MySQL won't lose data". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.