preface

InnoDB component structure:

  1. Buffer pool: Buffer pool to cache disk data
  2. Redo log buffer: Records operations to the buffer pool, and writes to disk according to the policy to prevent data loss due to downtime and transaction committed
  3. Undo log: When modifying data in the buffer pool, you can roll back the data even before the transaction is committed. The old value is written into the undo log file to facilitate the rollback. In this case, the data in the buffer pool is different from that in the disk and is dirty

1. Buffer Pool

Suppose we now have an update statement:

update users set name = 'lisi' where id = 1
Copy the code

What does InnoDB do when it needs to update to the database?

First, InnoDB reads if the row id = 1 exists in the buffer pool. If it does not, InnoDB loads the row from disk into the buffer pool. It also places an exclusive lock on the row to prevent multiple SQL from modifying the row at the same time.

2. Undo log files

Name =’zhangsan’, name=’ lisi’, name=’ lisi’, name=’zhangsan’ and id=1

For those familiar with database, you will know the concept of transaction. Before the transaction is committed, all operations can be rolled back, that is, name = ‘lisi’ can be rolled back to name = ‘zhangsan’, so the value before update is written to the undo log file.

3. Update the buffer pool data

After the undo log file is written, the data in memory is updated. Update id = 1 name = ‘zhangsan’ to name = ‘lisi’ At this point, the data in memory has been updated, but the disk has not changed, and there is inconsistent dirty data.

If the transaction is completed, but the MySQL service is down, and the data in memory has not been written to disk, it will cause data loss and cause inconsistent SQL execution data.

4. redo log buffer

InnoDB has a redo log buffer that stores redo logs. For example, id=1, name=’zhangsan’ is changed to name=’lisi’.

However, the redo log buffer was still in memory and could not be used to restore MySQL after a MySQL failure.

5. Does the transaction not commit affect the database outage?

The transaction is not committed, which means that the execution is not successful. Even if MySQL crashes or crashes, the modified buffer pool and redo log buffer are lost without affecting the consistency of the data. If the transaction fails, the data in the database does not change.

6. Configure policies for committing transaction and redo logs

When a transaction commits, the redo diary writes the redo log from the redo log buffer to disk according to the policy implementation. The policy is configured using innoDB_flush_log_at_trx_commit.

  1. The innoDB_flush_log_at_trx_commit parameter is 0. The redo log is not written to disk even after a transaction is committed. After the MySQL system is down, data in the memory will be lost.

  1. InnoDB_flush_log_at_trx_commit: 1. After a transaction is committed, redo logs are flushed from memory to disk. Once the transaction is committed, redo logs are stored in disk.

In this case, even if the buffer pool data is not flushed to disk, the redo log shows the modified data. After MySQL restarts, the modified data can be recovered from the redo log.

  1. InnoDB_flush_log_at_trx_commit parameters of 2, after the transaction is committed, redo log only stay in OS cache, haven’t brush into the disk, one thousand service is down at this time. Data in the OS cache will also be lost, even if the transaction commits successfully.

1 is the best policy for data security.

7. Final commit of transaction, binlog

The binlog is actually a log file belonging to MySQL Server, and it is presented here because of its association with the redo log.

1) Biglog vs. Redo log

  • Redo logs: Physical redo logs, such as what records were made to which data pages, and what changes were made to them.
  • Binlog: logic-oriented log, for example, “Update the users table to a row with ID =10. What is the updated value?”

2) Write binlog at the same time when the transaction is committed

InnoDB interacts with the executor all the time while performing updates, including loading data to the buffer pool, writing undo log files, updating memory data, writing redo logs, and brushing to disk. Writing to binlog is also performed by the executor.

Steps 1, 2, 3, and 4 are what execute the update statement, while steps 5 and 6 are what commit transactions start doing.

3) Analyzing the binlog flush policy

The sync_binlog parameter controls the disk flushing policy of the binlog

  1. The default value of sync_ binlog is 0. After a transaction is committed, binlog logs are stored in the OS cache. Data in the OS cache may be lost when the MySQL database breaks down
  2. The value of sync_binlog is 1. After the transaction is committed, the binlog log is directly flushed to disk.

4) Commit transactions based on binlog and redo log

After the binlog file is written to the disk, the location and name of the binlog file are written to the redo log file, and a commit flag is written to the redo log file.

5) What is the meaning of commit tags?

The commit flag means that redo log and binlog logs are consistent. If at Step 5 or step 6, the transaction commit starts, MySQL crashes, and the redo log does not have a commit flag, the transaction commit fails.

The commint flag indicates that the transaction finally committed successfully.

8. Dirty data in the buffer pool is flushed to disks

Dirty data is flushed to disk randomly by background I/O threads.

What if MySQL is down before flushing to disk? The transaction has been committed and the redo log has a COMMIT flag. Even after a shutdown, the redo log file updates data to memory and waits for the I/O thread to flush.

9. To summarize

After analysis of update statement execution, it is found that InnoDB storage engine contains buffer pool buffer pool, redo log buffer buffer, undo log, REod log, and MySQL Server log files.

When executing update statements, the buffer pool is modified, undo log files are written, and redo log buffers are written. When a transaction is committed, the redo log is flushed, the binlog is flushed, the binlog file name and location are written, and the COMMIT flag is written. Finally, the I/O thread waits for dirty data to be flushed randomly from the buffer pool.