There are three kinds of MHA, PXC, MHA, MGR, MHA for MySQL5.6 and MGR for 5.7.

MHA

MHA (Master High Availability) is a relatively mature solution for MySQL High Availability. The software consists of two parts: MHA Manager (management Node) and MHA Node (data Node).

  • MHA Manager: Can be deployed on an independent machine to manage multiple master-slave clusters or on a slave node.
  • MHA Node: Row on each MySQL server.

The MHA Manager periodically probes the master node in the cluster. When the master fails, it can automatically promote the latest slave to the new master and redirect all other slaves to the new master. The entire failover process is completely transparent to the application.

MGR

MySQL Group Replication, a high availability cluster solution recommended by MySQL, is a state machine Replication based on Paxos protocol. It completely solves the problem of data consistency in traditional asynchronous and semi-synchronous Replication, and also enables MySQL database to cover a wider range of fields. Open the door of Internet finance industry.

Set of copy

Group replication is a technique that can be used to implement fault-tolerant systems. A replication group is a cluster of servers that interact with each other through messaging. The replication group consists of multiple Server members, such as Master1, Master2, and Master3 in the figure below. All members complete their own transactions independently.

When a client initiates an update transaction, the transaction is executed locally and the commit to the transaction is initiated after completion. The resulting copy write set needs to be broadcast and copied to other members before it is actually committed. If the conflict detection succeeds, the group decides that the transaction can be committed and applied by other members, otherwise it is rolled back.

Group replication can be run in two modes:

  • In single-master mode, group replication automatically selects the master function. Only one Server member receives updates at a time, and the other members only provide read services.
  • ** In multi-master mode, ** all Server members can receive updates at the same time. There is no master or slave, and the member roles are completely equal.

PXC

Percona XtraDB Cluster is a high availability and scalability solution for MySQL with the following features:

1). Synchronous replication, the transaction is either committed or not committed on all nodes.

2). Multi-master replication. Write operations can be performed on any node.

3). Apply events on the slave server in parallel, true parallel replication.

4). Automatic node configuration.

5). Data consistency, no longer asynchronous replication.

The advantages are as follows:

1). When a query is executed, it is executed on the local node. Because all data is local, remote access is not required.

2). No need for centralized management. Any node can be lost at any point in time, but the cluster will work as usual, unaffected.

3). Good read load expansion, any node can be queried.

Disadvantages are as follows:

1). Adding new nodes costs a lot. Complete data needs to be copied.

2). The problem of write scaling cannot be solved effectively. All write operations will occur on all nodes.

3). There are as many duplicate data as there are nodes.

reference

  • MHA for MySQL high availability architecture

  • MySQL high availability OF PXC

  • MySQL MGR features