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

Many novices are not very clear about how to recover the data after the MySQL database is accidentally offline. In order to help you solve this problem, the following editor will explain it in detail. People with this need can come and learn. I hope you can get something.

If there is an unexpected downtime in InnoDB, will the data be lost?

For this question, people who have a little knowledge of MySQL will answer categorically: no!

Why?

They will also say without hesitation: because there is a redo log (redo log), data can be recovered through redo log.

Good answer, so how does InnoDB recover data through redo log, and what is the specific process?

There are probably not many people left who can clarify this problem, and let's go a little further: do you need any other information besides redo log,InnoDB during the recovery process? For example, is it necessary for binlog to participate? What role do undo logs play in the recovery process?

At this point, many people may become confused: does data recovery have anything to do with undo?

In fact, the data recovery of InnoDB is a very complex process, which requires the participation of redo log, binlog, undo log and so on. Here, the recovery process of InnoDB is divided into two phases:

The first phase mainly depends on the recovery of redo log.

The second stage requires the joint participation of binlog and undo log.

Next, let's take a detailed look at the whole recovery process:

First, rely on redo log for recovery

In the first stage, after the database is started, InnoDB will find the location of the most recent checkpoint through redo log, then start according to the LSN corresponding to checkpoint, obtain the log that needs to be redone, then parse the log and save it in a hash table, and finally read the relevant pages to recover by traversing the redo log information in the hash table.

The checkpoint information of InnoDB is saved in the log file, that is, among the first 2048 bytes of ib_logfile0, checkpoint has two alternately updated. The relationship between checkpoint and log file is shown in the following figure:

(checkpoint location)

Checkpoint information is stored in 512 bytes and 1536 bytes of ib_logfile0, respectively. The default size of each checkpoint is 512 bytes. The checkpoint of InnoDB is mainly composed of three parts of information:

Checkpoint no: the main thing to save is the checkpoint number, because InnoDB has two checkpoint, which checkpoint update is determined by the checkpoint number.

Checkpoint lsn: mainly records the LSN that generated the checkpoint as flush, ensuring that all the data pages in front of the LSN have been discarded and no longer need to be recovered through redo log.

Checkpoint offset: mainly records the offset of the redo log in the ib_logfile when the checkpoint is generated. Through this offset location, you can find the starting position of the redo log that needs to be restored.

From the above checkpoint information, we can simply get the location of the redo log that needs to be restored, and then read the data by scanning the redo log sequentially. For example, if we locate the redo log location where the recovery started in the ib_logfile1 through checkpoint, the whole redo log scanning process may be as follows:

(redo log scanning process)

Step 1: the redo log is read from the specified location of ib_logfile1, and the size of 4 * page_size is read each time. Here, the default page size is 16K, so each time the 64K redo log is read into the cache, the size of each record (block) of redo log is 512bytes.

Step 2: after reading the redo log into the cache through a series of procedures such as parsing and verification, the contents of the redo log are saved to the cache recv _ sys- > buf for recovery. Each piece of information saved to the recovery cache mainly contains two parts: (space,offset) the location information and the content of the specific redo log, which we call body.

Step 3: the redo information saved in the recovery cache will be stored in a hash table (recv_sys- > addr_hash) after a hash value is calculated according to (space,offset). The same hash value, different (space,offset) is stored in a linked list, and the same (space,offset) is stored in a list. Some transactions may be relatively large, and a block of redo information cannot be saved. Therefore, you can link multiple body values with a linked list in each body.

After redo log is saved to the hash table, InnoDB can start data recovery by polling each node in the hash table for redo information, reading the specified page according to (space,offset) and overwriting the log.

During the whole process above, InnoDB made several optimizations to ensure the speed of recovery:

Optimization 1:

When reading data page information to buffer pool according to (space,offset), InnoDB does not read only one page, but reads 32 adjacent pages to buffer pool. Here's an assumption. InnoDB believes that if a page is modified, then some of the pages around it are likely to be modified, so reading 32 pages in a row at a time can avoid re-reading later.

Optimization 2:

Prior to the MySQL5.7 version, InnoDB recovery relied on a data dictionary because InnoDB had no idea which ibd file was corresponding to a specific space, and this information was maintained by the data dictionary. And before restoring, you need to open all the tablespaces. If there are tens of thousands of tables in the library, the whole process will be slow. So what improvements has MySQL5.7 made on this?

In fact, it is very simple, to solve the above problem, InnoDB has added two types of redo log to redo log to solve it.

MLOG_FILE_NAME

Used to record all modified information after checkpoint (space,filepath)

MLOG_CHECKPOINT

Is used to mark the end of the MLOG_FILE_NAME.

The addition of the above two redo log types perfectly solves the previous problems. The space and filepath pairs that need to be restored later are saved in the redo log. Therefore, when restoring, you only need to scan back from the location of checkpoint to the location of MLOG_CHECKPOINT, so that you can get the space and filepath that need to be restored. During the recovery process, you only need to open these ibd files. Of course, because the correspondence between space and filepath is saved through redo, the recovery no longer depends on the data dictionary.

It should be emphasized here that MLOG_CHECKPOINT exists at most once in each checkpoint point. If there are multiple logs of MLOG_CHECKPOINT type, the redo is corrupted and the InnoDB will report an error.

If it exists at most once, will there be a situation that does not exist?

The answer is yes, after each checkpoint, if no data update occurs, then the MLOG_CHECKPOINT will not be recorded. Therefore, as long as you find out whether the MLOG_CHECKPOINT after the latest checkpoint of redo log exists, you can determine whether the last MySQL is shut down normally.

This is exactly what MySQL version 5.7 does when it is restored by InnoDB. Generally speaking, MySQL5.7 needs to perform up to 3 redo log scans during recovery:

1. Redo log is scanned first, mainly to find MLOG_CHECKPOINT, and redo log parsing is not carried out here. If you do not find MLOG_CHECKPOINT, InnoDB does not need to recovery, the next two scans can be omitted; if MLOG_CHECKPOINT is found, get MLOG_FILE_NAME to the specified list, and then just open the tablespace in the linked list.

2. The next scan is based on finding the MLOG_CHECKPOINT for the first time. This scan parses the redo log to the hash table. If the hash table has not been filled up after scanning the entire file, then you do not need a third scan, just finish the recovery.

3. Finally, it is carried out on the basis of the second time. After the second scan fills up the hash table, there is still redo log left, then it needs to be scanned in a loop. After the hash table is full, recovery is performed immediately until all the redo log is finished by apply.

All the redo log is parsed and the apply is completed, and the first phase of the whole InnoDB recovery is over, in which all records that have been recorded in redo log but have not completed data flushing are reopened.

However, it is not enough for InnoDB to rely on the recovery of redo log alone, it is still possible to lose data (or cause master-slave data inconsistency).

Because in the process of transaction commit, writing binlog and writing redo log commit are two processes, write binlog first and redo commit later. If MySQL writes binlog and downtime occurs before redo commit, there will be a problem: the record is already included in binlog, but redo is not persisted. The fact that the binlog has been closed means that the data can be apply on the slave. If the redo is not persisted, it means that the data on the master has not been closed, and it cannot be recovered through redo.

This leads to the inconsistency of master-slave data, in other words, the master has lost part of the data, so how does MySQL ensure that the data is still consistent in this case? This requires a second phase of recovery.

Second, binlog and undo log jointly participate

As mentioned earlier, binlog and undo log are needed in the second phase of recovery, so let's take a look at the specific recovery logic.

In fact, this stage of recovery is also divided into two parts: the first part is to obtain the xid list of all transactions that may not be committed according to binlog; the second part is to construct a linked list of all uncommitted transactions according to the information in undo, and finally determine whether the transaction can be committed through the coordination of the above two parts.

(get xid list according to binlog)

As shown in the figure above, during the second stage of recovery, MySQL will first read all the event information of the last binlog file, then save the xid to a list, and then perform the second part of the recovery, as follows:

(construct transaction linked list based on undo)

We know that there are 128rollback segments in the current version of InnoDB, and the location pointer of undo log is saved in each rollback segment. By scanning the undo log, we can construct a linked list of transactions that have not yet been committed (transactions that exist in insert_undo_list and update_undo_lsit are uncommitted), so we can locate the rollback segment through the solt information under the start page (0Co5), and then locate the undo page according to the slot of the undo under the rollback segment. Build all the undo information into a undo_list, and then use undo_list to create an uncommitted transaction linked list trx_sys- > trx_list.

Based on the above two steps, we have built the xid list and the uncommitted transaction list, so which transactions in these uncommitted transaction lists need to be committed? Which should be rolled back?

The judgment is simple: any transaction that xid exists in the xid list built through binlog needs to be committed. In other words, all transactions that have recorded binlog need to be committed, while the remaining transactions that do not record binlog need to be rolled back

III. Review and optimization

It is only through the above two stages of data recovery that InnoDB finally completes the entire recovery process. In retrospect, is there any room for optimization in the above two stages? For example, in the first stage, after the hash table is constructed, can the transaction be recovered concurrently? In theory, each hash node is generated according to (space,offset), there is no conflict between different hash node, and can be recovered in parallel.

Or when reading data pages according to the hash table, read 32 consecutive pages at a time. Some of the 32 pages read here may not be needed and are also read into Buffer Pool at the same time. Can you build a red-black tree and insert it according to the (space,offset) key combination, so that if you need to restore, you can serialize the reading of all pages according to the sorting principle of the red-black tree? There is no need to read additional pages.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.