There are three critical types of logging that you must know about in MySql

Preface:

There are three types of logs in MySql that are very important because they are used to support transactions (persistence, rollback, etc.), database crash recovery, master/slave replication, etc.

Three crucial types of logs:

• Binlog Indicates binary logs
• Redo log Redo log
• Undo log Rollback logs

Main line of this article:

• This section describes three types of logs
• There are three types of log roles in MySql transaction processing

This section describes three types of logs:

1, binary log:

Binlog Binary logs (archive logs), which are maintained by MySql’s server layer. No matter what storage engine MySql currently uses, binlog archived logs are supported;

For those who are not familiar with the server layer of MySql, see this article: Query the execution flow of SQL

Function:

• Used for replication. In master-slave replication, the secondary library uses the binlog on the master library to replay and realize master/slave synchronization.
• Data recovery, a point-in-time restoration of a database.

Content:

Logical format. Binlog is used to record all changes in the database table structure and table data, such as INSERT, UPDATE, DELETE, CREATE, truncate, etc. Select and show operations are not recorded because they do not change the data.

Common formats:

The default format for MySql’s binlog is STATEMENT; Keep in mind that when using this format, if the transaction isolation level is set to RC read committed, there will be a bug during the master/slave replication, resulting in inconsistent master/slave data after replication.

What kind of bug should mysql choose for transaction isolation level in Internet projects

Note:

Binlog The binary log file is not started by default and needs to be manually enabled. One might wonder, what impact does opening this log file have on database performance?

It is true that enabling this log file will affect the performance of the database, but the impact is very limited. According to the MySql official website manual, enabling this log file will reduce the performance by about 1%, which is generally acceptable.

Redo log:

Redo log. This log is maintained by MySql’s InnoDB storage engine. This log file only exists in MySql’s InnoDB storage engine.

Function:

Ensure the persistence of transactions. Redo logs record the state after a transaction has been executed. They are used to recover data updated by a successful transaction that was not written to the data file. In case there are dirty pages that have not been written to disk at the time of the failure, redo the mysql service according to the redo log to achieve transaction persistence.

Everyone, if the above paragraph if not very clear, you can continue to see ah, through the following transaction example can be very well understood;

Content:

A log in physical format records the modification information of the physical data page. In short, it records the XXX modification of the XXX page.

Innodb storage engine provides redo log file groups. Each redo log file group contains redo log files. By default, a redo log file group is provided that contains two redo log files of the same size.

Innodb storage engine redo log file writing process: write redo log file 1 first, when file 1 is full sequentially, it will switch to redo log file 2, when the redo log file 2 is also full, it will switch to redo log file 1 again, cycle successively. So the redo log file is written in a loop.

Because redo log is written in a cycle, it cannot be used for data recovery of the entire database. It can only ensure the integrity of the transaction data when the database is down. If you want to recover all data, you can only use the binary log (archive log) for recovery.

Note: You can manually change the number of files under the redo log file group and specify the size of each redo log file by using the following parameters:

• Innodb_log_file_size Specifies the size of the redo log file
• Innodb_log_files_in_group Number of files in redo log file groups

Extension:

Note: Redo log file size Settings have a significant impact on innoDB storage engine performance.

Redo log files cannot be too large or too small; If the setting is too large, it can take a long time to recover after an unexpected database outage.

However, it cannot be set too small, because setting too small will cause a transaction log to need to switch redo log file for many times to write, so before overwriting the previous redo log, the need to overwrite the dirty pages in memory to write disk (flush).

Because if not write a dish, redo logs are covered off, then the database accident goes down, so did not write disk data will not be in the database to restart to recover, and if the frequent coverage to redo log, so dirty pages to brush plate will frequently, resulting in a shaking of the database performance.

How big should the redo log be? Generally speaking, the total size of the redo log file should be large enough to hold one hour of server activity.

Innodb_log_file_size =250 MB because there are two redo log files by default. If the log size is 500 MB, set innodb_log_file_size to 250 MB.

Note: if you are not very clear about dirty pages and other nouns, you can learn to understand through “Dinqi big boss – MySQL actual practice 45 talk”, “MySQL technology insider InnoDB storage engine”.

Undo log rollback log

Note that the undo log rollback log is written before the transaction is executed.

Function:

Ensure atomicity of the data, save a version of the data before the transaction occurs, can be used for rollback, and can provide multi-version concurrency control read (MVCC), that is, unlocked read;

Content:

Logic-format logs, unlike redo logs, only logically restore data to the pre-transaction state when undo is executed, rather than operating from the physical page.

For example, if we want to insert a piece of data, undo log will generate a corresponding DELETE log associated with the txID (transaction unique identifier) of the current transaction to support rollback and MVCC.

For example, if multiple statements including UPDATE, INSERT, and DELETE are executed in a transaction, if one of the statements fails to execute due to the atomicity of the transaction, the previously successful statements will be rolled back and cancelled. At this point, the current transaction txID will find the saved log in undo log to perform the rollback;

Note that the undo log is also recorded in the redo log.

Why log it in the redo log? If the transaction needs to be rolled back, but the undo log saved in memory has not been written to disk, it will be lost during the downtime, so it will not be able to roll back, and there will be data inconsistency problems. If it is recorded in the redo log, redo it according to the redo log, and then rollback it according to the redo undo log.

Extension:

Note: To improve database performance, mysql records logs in the memory buffer, and then flusher the disk according to the policy (write the logs recorded in the buffer to disk for saving).

The architecture diagram of innoDB storage engine provides an insight into this:

If you want to learn more, please refer to this article. InnoDB storage engine is divided into memory architecture and disk architecture.

There are three types of log roles in MySql transaction processing:

First we use an update statement execution flow chart to show the role of three log files in transaction processing! The SQL statement is as follows:

update T set c=c+1 where ID=2;

Why there is no undo log in the flowchart:

You can see the redo log, binglog, and undo log in the flowchart, because the undo log is written before the transaction starts.

Why do redo logs and binlogs have to be present in the above transaction:

Binlog is a log maintained by the mysql server layer. It is used for master/slave replication and data backup and recovery. The redo log is a log unique to InnoDB storage engine and is used to implement crash-safe capability.

What is crash-safe?

That is, in InnoDB storage engine, if MySQL crashes suddenly at any stage during the transaction submission process, the integrity of the transaction can be guaranteed after the restart. The committed data will not be lost, and the incomplete data will be automatically rolled back.

In order to implement crash-safe, redo log is required, and undo log is required to roll back incomplete transactions.

If you want to learn more about crash-safe, you can refer to this article: The principle of crash-safe for MySQL

What are the prepare and COMMIT phases in the figure above?

This is an internal XA transaction for mysql! Log two-phase commit protocol! You’ve heard of distributed XA transactions, but how come there are internal XA transactions?

In fact, distributed XA transactions are known as external XA transactions of mysql; Internal XA transactions exist mainly in mysql to ensure data consistency between binlog and redo log, which is determined by its architecture (binlog is supported by mysql server layer, and redo log is supported by InnoDB storage engine layer).

If you are not familiar with the mysql architecture, please refer to this article: Query SQL Execution Flow

What are the data consistency issues between binlog and redo log:

Because redo logs and binlogs are used to “restore” data, redo logs are used to restore transactions and ensure transaction integrity, and binlogs are used for global or point-in-time data recovery and for master/slave replication.

The database crashes if the redo log has not been written to the redo log and the redo log has not been written to the binlog.

Data that was processed during the transaction but was not written to disk is restored during the redo log transaction. However, because the binlog does not write data, data inconsistency may occur when the binlog is used for data recovery or primary/secondary replication.

If the logic of the two logs is consistent, the redo log is used to determine whether the binlog is successfully written to the redo log. If the redo log is successfully written to the redo log, the undo log is used to rollback the redo log.

MySql internal XA transaction

An internal XA transaction divides the commit into two phases: prepare and COMMIT!

Prepare: Write the redo log and set the rollback segment to prepared. Binlog does not perform any operations.

Commit: InnoDB releases the lock, releases the rollback segment, sets the commit state, writes to binlog, and commits to the storage engine layer.

Extension:

Update: Redo log; binglog; update: redo log; update: update: redo log;

When will the buffer’s log data be written to disk? This is controlled by several configuration parameters in mysql;

Process of writing buffer logs to disk:

Before introducing the configuration parameters, let’s look at the process of writing buffer logs to disk:

• User space: the space where user programs run. For example, mysql runs in this space.

• Kernel space: kernel space refers to the operating system kernel running space, is to ensure that the operating system kernel can run safely and stably and specially opened for the kernel space;

Why do you need kernel space?

For security purposes, user space programs cannot read data directly from disk space, but must be accessed via DMA through kernel space. User space and kernel space are isolated from each other.

Redo log configuration parameters:

The innodb_flush_LOG_at_trx_COMMIT configuration parameter controls the policy for flushing redo logs to disk.

There are three possible values for this parameter:

• 0: no write redo log erase is performed during transaction commit. MySql uses its background thread to write redo logs from the log buffer to the OS cache (disk cache) every second and immediately calls fsync to write redo logs from the OS cache to the disk file (disk flush).

• 1: indicates that redo logs are written to the OS cache (disk cache) when the transaction is committed. The redo logs in the log buffer are written to the OS cache (disk cache) in real time, and the fsync operation is immediately called to write redo logs in the OS cache to the disk file (disk flush). This value is the default because persistence in the ACID of a transaction is guaranteed when set to 1;

• 2: indicates that redo logs are written to the OS cache (disk cache) when the transaction is committed. Fsync is not immediately invoked to flush the redo logs. MySQL will actively flush the redo log data in the OS cache in batches once per second. If you select this value, the concurrency of mysql is the best, but there are risks. If the operating system is down, data will be lost, but if the mysql database is down, data will not be lost because the data is stored in the OS cache.

Binlog Flush configuration parameters:

The sync_binlog configuration parameter controls the policy for flushing binary logs to disk.

There are three common values for this parameter:

• 0: indicates that after the transaction is committed, mysql writes the binlog data in the log buffer to the OS cache (disk cache), but the OPERATING system controls the flushing of its cache instead of calling fsync. This value is the default.

• 1: indicates that the binlog will be written to the OS cache (disk cache) when the transaction is committed, and the redo logs in the log buffer will be written to the OS cache (disk cache) in real time, and the fsync operation will be immediately called to write the redo logs in the OS cache to the disk file (disk flush).

• N: Indicates that after each transaction commit, only the binlog data in the log buffer is written to the OS cache (disk cache). After N transaction commits, Mysql will execute the fsync operation to flush the binlog data in the OS cache to the disk in batches. Notice that N is a number;

A classic interview question:

Interviewer: Have you ever had a database crash and restart and lost transactions?

When do you log redo logs or binlogs? Do you log redo logs or binlogs?

Interviewer: What do you know about mysql transactions?

There are three important types of logging in MySql. Because my water is limited, if you have any questions, please leave a message to discuss!

♡ like + comment + forwarding yo

If this article is helpful to you, please wave your love to make a fortune of the little hand under the praise ah, your support is my continuous creation of power, thank you!

You can wechat search [Muzilei] public number, a large number of Java learning articles, you can have a look at yo!