First, let’s look at the flow chart of a query/update statement
Mysql query/update flowchart
This article will focus on the interaction between the actuators <-> storage engines.
Instead of writing every data change to the disk immediately, mysql stores the modified result in the memory temporarily. After a period of time, mysql writes multiple changes to the disk at a time, reducing disk I/O costs and improving operation speed.
MySQL ensures transactions through write-Ahead logging (WAL) technology
The process is shown below.
Update process
Redo log Log mode
Innodb_log_files_in_group and innodb_log_file_size can be used to set the number of log files and the size of each log file. Redolog is written in a loop. When you write to the end, you go back to the beginning and write the log loop.
The following figure
Redolog recording mode
Write pos indicates the position where logs are currently recorded. When ib_logFILe_4 is full, logs are recorded from ib_logFILe_1. Check point: Write pos->checkpoint: The log changes are written to the disk and the data falls to the disk. After the data falls to the disk, the checkpoint erases the log records. That is, the empty part between write pos->checkpoint is the redo log. Checkpoint ->write Pos Data modification records between the redo logs. Pay attention to the public account Java interview those things, reply to the keyword interview, get the latest interview questions. When writepos catches up with a checkpoint, it stops recording and pushes the checkpoint forward to record a new log.
With redo logs, why do you need a binlog?
1. The size of the redo log is fixed. The log is overwritten by the redo log and cannot be used for data rollback or data recovery. Redo log is implemented by innoDB engine layer, not all engines have it.
Because of this, binlog is essential.
1. Binlog is implemented on the server layer, meaning that all engines can use binlog logging
Max_binlog_size Specifies the size of each binlog file. When the size of the file is larger than the specified value, the log will be rolled and the subsequent logs will be recorded to a new file. The statement format records the contents of the SQL statement, and the row format records the contents of the row before and after the update.
The binlog and redolog must be consistent. It is not allowed that the binlog has a log but redolog does not, and vice versa. Redolog has two states: prepare and COMMIT. If redolog is in the prepare or COMMIT state, the two records will be consistent.
Now let’s look at the complete flow chart again
Update process
Suggestions for setting related parameters:
Innodb_flush_log_at_trx_commit: if set to 1, redolog of each transaction will be persisted directly to disk (note that the redolog log itself is dropped) to ensure that data will not be lost after mysql restarts. 2. Sync_binlog: Set it to 1, indicating that the binlog of each transaction will be persisted to the disk directly. This ensures that the binlog record is complete after mysql restarts.