MySQL master-slave synchronization, implementation principle of transaction rollback

BinLog

BinLog is a binary log that records all database table structure changes (such as create, alter table) and table data modifications (insert, update, delete). BinLog files are used for master-slave database synchronization. BinLog log files have three modes.

STATEMENT mode

Content: Binlog only records the SQL statements that cause data changes

Advantages: In this mode, because no actual data is recorded, the log volume and IO consumption are very low, and the performance is optimal.

Disadvantages: However, some operations are not certain. For example, the uuid() function will randomly generate a unique identifier. When relying on binlog playback, the data generated by this operation must be different from the original data, which may cause unpredictable consequences.

ROW Mode

Content: In this mode, binlog records the source data and modified target data of each operation. StreamSets requires this mode.

Advantages: It can restore data with absolute accuracy, thus ensuring data security and reliability, and the replication and data recovery processes can be performed concurrently

Disadvantages: The disadvantage is that the binlog volume will be very large. At the same time, for operations with many modified records and long field lengths, the performance consumption during recording will be very serious. Special instructions are also required to read data.

MIXED mode

Content: It is a combination of the above two modes: STATEMENT and ROW.

Details: For most operations, STATEMENT is used to record binlogs. Only the following operations are implemented using ROW: the table storage engine is NDB, uncertain functions such as uuid() are used, insert delay statements are used, and temporary tables are used

Master-slave synchronization process:

1. The master node must enable binary logging to record any events that modify database data.

2. The slave node starts a thread (I/O Thread) to act as a MySQL client and requests events in the binary log file of the master node through the MySQL protocol.

3. The master node starts a thread (dump Thread) to check the events in its own binary log and compare them with the position requested by the other party. If there is no request position parameter, the master node will send the first event in the first log file to the slave node one by one.

4. The slave node receives the data sent by the master node and places it in the relay log file. And record the specific location inside which binary log file of the master node the request was sent to (there will be multiple binary files in the master node).

5. Start another thread (sql thread) from the node, read the events in the relay log, and execute them again locally.

The default replication mode of MySQL is asynchronous, and it has parallel replication capabilities. The master database sends the log to the slave database and then ignores it. This will cause a problem. If the master database crashes and the slave database fails to process, the log will be lost after the slave database is promoted to the master database. Two concepts arise from this.

• Fully synchronous replication
  

After the master database writes binlog, it forces the log to be synchronized to the slave database. After all slave databases have completed the execution, the log is returned to the client. However, it is obvious that this method will seriously affect the performance.

• Semisynchronous replication

The logic of semi-synchronous replication is that after the slave database successfully writes the log, it returns an ACK confirmation to the master database. The master database considers the write operation complete when it receives confirmation from at least one slave database.

RedoLog

The difference between binlog and redolog:

• redo log is specific to the InnoDB engine; binlog is implemented at the server layer of MySQL and can be used by all engines.
• The redo log is a physical log that records what changes have been made to a data page; the binlog is a logical log that records the original logic of the statement, such as adding 1 to the c field of the row with ID=2.
• Redo log is written in a cycle, and its space will eventually run out; binlog can be written in appended form. Append write means that after the binlog file reaches a certain size, it will switch to the next one and will not overwrite the previous log.

In MySQL, if every update operation needs to be written to disk, and the disk also needs to find the corresponding record and then update it, the IO cost and search cost of the whole process are very high. Write the log first, then write the disk BinLog and RedoLog.

1. When a record is updated, the InnoDB engine will first write the record to RedoLog and update the memory. At the same time, the InnoDB engine will update the operation record to disk when it is idle.

2. If there are too many updates for RedoLog to handle, you need to write some RedoLog data to disk first, and then erase some RedoLog data.

RedoLog write pos and checkpoint

RedoLog has write pos and checkpoint

write pos: is the position of the current record. It moves backwards while writing. After writing to the end of file No. 3, it returns to the beginning of file No. 0.

Check point: shortens the recovery time of the database. When the buffer pool space is insufficient, the dirty pages are flushed to disk. When redolog is unavailable, the dirty pages are flushed.

Redo log sequential writing actually writes several fixed files in a loop, and once a round is completed, it is overwritten from the beginning. It includes two locations, check point and write pos. Write pos is the position to be written, which is incremented in the loop. Check point is the current position to be erased. The space between the two is writable. When the write pos catches up with the check point, it will stop updating, overwrite some records, and then continue writing the redo log.

Crash-safe redo log

MySQL supports users to customize how to flush logs in the log buffer to the log file during commit. This control is determined by the value of the variable innodb_flush_log_at_trx_commit. This variable has three values: 0, 1, 2, and the default is 1. But note that this variable only controls whether the commit action flushes the log buffer to disk.

• When set to 1, each time a transaction is committed, the log in the log buffer is written to the OS buffer and fsync() is called to flush it to the log file on disk. This method will not lose any data even if the system crashes, but because each submission is written to disk, the IO performance is poor.
• When set to 0, logs in the log buffer are not written to the OS buffer when the transaction is committed. Instead, logs are written to the OS buffer every second and fsync() is called to write them to the log file on disk. That is to say, when it is set to 0, the data written to the disk is refreshed (approximately) every second. When the system crashes, 1 second of data will be lost.
• When set to 2, each commit is written only to the os buffer, and then fsync() is called every second to write the log in the os buffer to the log file on disk.

In the master-slave replication structure, to ensure the persistence and consistency of transactions, log-related variables need to be set as follows:

• If binary logging is enabled, set sync_binlog=1, which means that each transaction is written to disk synchronously.
• Always set innodb_flush_log_at_trx_commit=1, that is, each transaction is written to disk every time it is committed.

The settings of the above two variables ensure that each transaction is written to the binary log and transaction log and flushed to disk when it is committed.

With redo log, InnoDB can ensure that even if the database restarts abnormally, previously submitted records will not be lost. This capability is called crash-safe. Redolog two-phase commit: To make the logic between binlog and redolog consistent. The submission process is as follows:

1 prepare stage --> 2 write binlog --> 3 commit

1. When the process crashes before step 2, it is found that there is no commit after restart and the process is rolled back. Backup and restore: no binlog. Consistency
2. When the crash occurs before step 3, the restart recovery finds that although there is no commit, the prepare and binlog are complete, so the commit will be automatically performed after the restart. Backup: with binlog. Consistent

UndoLog

Undo log has two functions: providing rollback and multiple row version control (MVCC). It is mainly divided into two types

When data is modified, not only redo is recorded, but also the corresponding undo. If the transaction fails or rolls back for some reason, it can be rolled back with the help of the undo. When a record is deleted, a corresponding insert record is recorded in the undo log, and vice versa. When a record is updated, it records a corresponding opposite update record.

When a rollback is executed, the corresponding content can be read from the logical record in the undo log and rolled back.

• insert undo log
  

Represents the undo log generated when a transaction inserts a new record. It is only needed when the transaction is rolled back and can be discarded immediately after the transaction is committed.

• update undo log
  

The undo log generated when a transaction is updating or deleting. It is not only needed when the transaction is rolled back, but also when the snapshot is read. Therefore, it cannot be deleted casually. Only when the fast read or transaction rollback does not involve the log, the corresponding log will be cleared uniformly by the purge thread.

The above is the detailed content of the implementation principle of MySQL master-slave synchronization and transaction rollback. For more information about MySQL master-slave synchronization and transaction rollback, please pay attention to other related articles on 123WORDPRESS.COM!