Mysql High Availability Architecture Solution

Introduction to Mysql

Mysql is the quintessential open source relational database and is the database of choice for many websites, applications, and enterprise software products.

Mysql Features:

• Easy to use, powerful, support for transactions, triggers, stored procedures
• Diverse and feature-rich management tools
• Large databases that can be managed as multi-million dollar data
• Adoption of the GPL open source agreement, allowing free modification of the source code and applied to commercial systems
• Mysql's InnoDB transactional storage engine conforms to the transactional ACID model, ensuring complete and reliable data storage.

Highly available architecture

• master-slave mode

• MHA

• MMM

• MGR

master-slave mode

Introduction to Master-Slave Mode

Master-slave mode is the most basic Mysql highly available architecture, a server as the Master node, a number of servers as Slave nodes. Only the Master handles write data requests, and read requests can be handled only by the Slave node, or the Master and Slave can handle them at the same time.

The Master and Slave are connected via themaster-slave replicationtechnique to keep the data consistent, i.e., the Master node synchronizes the data to the Slave node.

Master-slave mode with high availability is based on master-slave replication technology.

master-slave replication technology

• When changes (additions, deletions, modifications) are made to the Master data, the Master writes the change log to the binary log file binlog.
• Slave starts a separate thread (I/O thread) to establish a network connection with the Master, and fetches the change log from the Master's binlog
• The I/O thread of the Slave captures the data change log and saves it to the relay log file in order.
• The Slave starts a separate thread (Sql thread) to read the logs from the relay log and executes them, so that the data in the Slave library is consistent with that of the Master.

Master-Slave Mode Considerations

Before Mysql 5.5, master-slave replication was asynchronous, and master commits did not need to be acknowledged by the slaves, so this is an extreme case:

• Slave failed to read Master's binlog.
• Slave failed to process relay log.
• Slave Failed to Execute Sql Statement
• etc. ......

Similar extreme cases will lead to data inconsistency. So in Mysql 5.5 master-slave replication provides a semi-synchronous way, specifically, it is to increase the ACK confirmation mechanism, when the Slave receives the binlog, it will send an acknowledgement message to the Master, the Master receives the ACK acknowledgement message before committing the transaction. Semi-synchronization can improve data consistency, but the Master needs to wait for the Slave's confirmation when writing data, so the performance will be degraded.

Replication storm problem, to consider such a more extreme situation, a Master, 10 Slave, this case is based on master-slave replication technology, the Master needs to write data at the same time before the data replication request to deal with 10 Slave, this situation for the Master can only be said to be overburdened, if coupled with a "half-synchronization mechanism", write performance will be greatly reduced. If you add the "half-synchronization mechanism", write performance will be greatly reduced, this situation is called the replication storm problem. The solution to this problem is for the Master to handle only one Slave's master-slave replication, while the other Slave replications are handled by the Slave.

MHA（MasterHighAvailability）

Introduction to the MHA model

With the master-slave model as a foundation, it's time to consider the following next:

• How to Detect Node Failures
• How to re-elect a master node after it fails

MHA is solving these two problems. Theoretically, MHA mode can complete automatic fault detection and automatic master-slave switchover of master-slave clusters within 10s-30s.

MHA consists of two parts:

• MHA-Manager: It is responsible for automatically detecting whether the Master has failed, checking the status of master-slave replication, performing automatic master-slave switchover and so on. It needs to be deployed on a separate server.
• MHA-Node: responsible for repairing differences in master-slave data, usually deployed in conjunction with a Mysql server instance.

MHA Workflow

• Heartbeat between Manager and Master, if no heartbeat is detected for 4 consecutive times, the Master is considered down and the Master instance is bound to a Node.

• The Manager analyzes the binlogs of each Slave and selects a Slave that is closer to the Master's data as an alternative Master, and a Slave instance is bound to one Node each.

• The Slave's Node is trying to access the server where the Master resides via SSH:

  • If it is reachable, the Slave's Node obtains the Master's binlog data, and if it finds that there is a difference between the Master and Slave data, it will actively replicate the difference data to the Slave to keep the Master and Slave data consistent.
  • If it is unreachable, the Node compares the relay log differences of each Slave and does the difference data patching.

• Manager promotes the alternative Master to Master.

MMM（Multi-MasterReplicationManagerForMysql）

MMM mode is simply the introduction of virtual IP (vip) technology, this architecture, a cluster has two Master and a number of Slave, when one of the Master is unavailable, the MMM will instruct the vip to switch to another Master, and at the same time, will send a message to all the Slave to change the Master, after which the master-slave replication will switch to the new Master.

This solution is old, does not support Mysql GTID , and the community is not active enough, and is currently unmaintained.

MGR（MysqlGroupReplication）

MGR, Mysql Group Replication Mode is a highly available solution introduced in Mysql version 5.7.17 with the following features:

• High consistency: data replication is based on the distributed consensus algorithm Paxos, which can ensure the consistency of data across multiple nodes
• High fault tolerance: as long as not more than half of the nodes are down, the service can continue to be provided
• Flexibility: MGR supports single-master and multi-master modes. In single-master mode, if the Master fails, the Slaves will reelect a new Master, and in multi-master mode, each Mysql node can handle write requests at the same time.

MGR requires a replication group of at least three Mysql nodes, i.e., one master and two slaves, and a transaction must be passed by more than half of the nodes in the replication group before it can be committed.

If a transaction conflict occurs when different write operations are performed on different Mysql nodes, the transaction that commits first is executed first and the transaction that commits later is rolled back. In multi-master mode, since each Mysql node can execute write requests, the probability of a transaction conflict in a scenario with high concurrency of write requests will be very high, resulting in a large number of transaction rollbacks.

In single-master mode, MGR automatically selects a Master for the replica group to be responsible for write requests. If more than half of the nodes in the replica group fail to communicate with the Master, it is assumed that the Master is down, and then the Master will be re-selected based on the weights and ID identifiers of each node.

MGR is more suitable for scenarios where consistency is strong and write concurrency is not large.

summarize

This article describes the Mysql High Availability Architecture program, which introduces theMaster-Slave mode, MHA mode, MMM mode, MGR mode The realization of the program, no program is perfect, the developer in the choice of what kind of program to apply to the project there is no standard answer, the right is the best.