1. MySQL replication base

By default, Mysql replication is asynchronous, and there are a lot of different threads that do this.

After executing the change master command on the slave library, the parameters set the IP address, port, username, password of the corresponding master library, and the location from which to request the binlog. This location contains the file name (binlog File) and log offset (binlog file Position). Then run the start slave command on the slave library to start io_thread and SQL_thread. Io_thread is responsible for establishing connections with the primary database, and SQL_thread is responsible for redoing the relay logs. After the primary library verifies the user name and password, it reads the binlog from the local library according to the location sent from the secondary library, and sends the binlog to the secondary library through dump_thread. After getting the binlog from the library, write it to the local file, which is called the relay log. Sql_thread reads transfer logs, parses commands in logs, and executes them.

Corresponding problems:

  1. Data loss (since the master library commit does not require the complete and correct receipt from the slave library, the master library cannot perceive the failure of the slave library to receive or redo the data)
  2. Master/slave delay (Another big hole, dig first)

2. MySQL semi-synchronous replication

Because of the drawbacks of asynchronous replication, mysql introduced semi-synchronous replication in version 5.5. After the transaction is committed (sync_binlog=1), after the transaction is committed (sync_binlog=1), after the transaction is committed (sync_binlog=1), after the transaction is committed (sync_binlog=1), Regardless of whether relay logs are properly executed (that is, the slave library is still asynchronous to relay log applications), the master library receives the acknowledgement and reports the commit OK result back to the client.

Corresponding problems:

  1. Phantom read
  2. Data loss

During the process of receiving binlog from the database, the master database failed to receive the confirmation from the slave database due to network fluctuation (rpl_semi_SYNC_master_timeout, default is 10s, timeout is degraded to asynchronous replication). During the waiting process, the master database broke down and the master database node switched to the slave database. The binlog from the library does not receive the complete binlog, which is reflected in the client. The user may find the order that was available before, refresh it, and suddenly cannot find it.

MySQL semi-synchronous replication is enhanced

Semi-synchronous replication enhancement (rPL_semi_sync_master_WAIT_point =alter_sync) was introduced after MySQL5.7.2 to solve MySQL5.6 semi-synchronization problems. In a two-phase commit, the binlog is sent to the slave after the binlog fync, before the redo log commit, and after the master receives the redo log commit. If the log is not transferred to the slave, the master will not commit. If the service fails, neither the master nor the slave can read the uncommitted data, eliminating phantom reads and no transfer to the slave, so there is no data loss problem.

4. One question

In the semi-synchronous replication enhanced architecture of MySQL, A is the master library and B is the slave library. One day, library A was down due to power failure, and library B took over the service and became the new main library. After that, the operation and maintenance personnel restarted the server A and wanted to add library A to the cluster as the secondary library of library B, but found that there were more transactions in library A than library B. Why?

In the case of A log failure, the binlog was written to the original primary database, but the redo log transaction had A complete prepare and no commit flag. In the case of A log failure, the original primary database did not commit the transaction. However, because the binlog is complete, a crash recovery process commits transactions to the master database based on the redo log and the complete binlog.

That is, if A goes down after binlog Fync, while waiting for confirmation from library B, A will recover with more transactions than B.

Commit the redo log if the redo log is complete and has a commit flag. If the redo log contains only a complete prepare, check whether the binlog exists and is complete: a. If so, commit the transaction; A statement is a binlog, and a COMMIT is a statement. B. A row file with an XID event (XID is the common field of binlog and redo log); B. Otherwise, roll back the transaction.Copy the code

Reference Documents:

Dev.mysql.com/doc/refman/…

Blog.csdn.net/qq_38114620…