Shulou(Shulou.com)05/31 Report--

This article focuses on "MySQL High availability solution Analysis". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "MySQL High availability solution Analysis".

MySQL database is the largest open source relational database at present, and there are a large number of applications to store data in MySQL database. The security and reliability of stored data is the focus of production database.

MySQL Replication

MySQL Replication is a master-slave synchronization scheme officially provided by MySQL, which is used to synchronize the data of one MySQL instance to another. Replication makes an important guarantee for data security, and it is also the most widely used MySQL disaster recovery scheme. Replication sets up a MySQL master-slave replication cluster with two or more instances, provides single-point writing and multi-point reading services, and realizes the read scale out.

Figure 1. MySQL Replication master-slave replication cluster

As shown in figure 1, one master instance (M) and three slave instances (S) generate the binlog of event through replication,Master, then send it to slave,Slave to write event to relaylog, and then submit it to its own database to achieve master-slave data synchronization. For the business layer above the database, the master-slave replication cluster based on MySQL writes a single point of Master. After the event is synchronized to Slave, the read logic can read data from any Slave, which greatly reduces the running load of Master and improves the resource utilization of Slave.

For high availability, MySQL Replication has an important drawback: the latency of data replication. Normally, MySQL Replication data replication is asynchronous, that is, after MySQL writes binlog, it is sent to Slave without waiting for Slave to return to acknowledge receipt, and the local transaction commits. Once the network delay or interruption occurs, the data delay is sent to the Slave side, and the master-slave data will be inconsistent. In this phase, once the Master goes down, the data that is not sent to the Slave is lost, and the high availability of the data cannot be achieved.

To solve this problem, google provides solutions: semi-synchronous and synchronous replication. A little modification has been made on the basis of asynchronous data replication. Semi-synchronous replication means that Master waits for event to write to Slave's relay before submitting it locally to ensure that Slave must receive the data that needs to be synchronized. Synchronous replication not only requires Slave to receive data, but also requires Slave to commit the data into the database to ensure that the master and slave data are consistent every time the data is written.

Based on semi-synchronous and synchronous replication, the high availability of MySQL Replication has been qualitatively improved, especially synchronous replication. Based on the MySQL Replication cluster of synchronous replication, the data read by each instance is consistent, and there are no Slave phantom reads. At the same time, after the Master downtime, the application can switch to any Slave to ensure the consistency of reading and writing data. However, synchronous replication brings significant performance degradation, and a compromise needs to be made here. In addition, the master-slave handover of MySQL Replication needs to be judged manually, and the replaylog of Slave needs to be submitted, so the fault recovery time will be long.

MySQL Fabric

MySQL Fabric is an extension provided by the MySQL community to manage multiple MySQL services. High availability is one of the main features of its design.

Fabric divides two or more MySQL instances into one HA Group. One of them is the Lord, and the rest are followers. HA Group guarantees that access to the data of the specified HA Group is always available. The underlying data replication is based on MySQL Replication, and then Fabric provides more features:

Failure detection and recovery: Fabric monitors the master instance in HA Group. Once the master instance is found to fail, Fabric will select one of the remaining slave instances in HA Group and promote it to master instance.

Read-write balance: Fabric can automatically handle a HA Group read and write operation, sending the write operation to the master instance, while the read request is load balanced among multiple slave instances.

Figure 2. Fabric

MHA

MHA (MySQL-master-ha) is a widely used high availability scheme for MySQL master-slave replication. The design goal of MHA is to automatically switch the slave to the master after the master instance goes down, and minimize the handover delay (usually within 10-30 seconds). At the same time, the data consistency in the switching process is guaranteed by MHA. MHA is very friendly to MySQL's master-slave replication cluster and does not make any intrusive changes to the cluster.

One of the key features of MHA is that after the master instance goes down, MHA can automatically determine which slave instance's relaylog is up-to-date in the master-slave replication cluster, and "apply" the latest slave instance difference log to the rest of the slave instances, thus ensuring that the data of each instance is consistent. Normally, MHA takes about 10 seconds to detect anomalies in the primary instance and shut down the primary instance to avoid brain fissure. Then synchronize the differential log event in about 10 seconds and enable the new Master. The RTO time of the whole MHA is about 30s.

MySQL Cluster

MySQL Cluster is a highly scalable real-time database compatible with ACID transactions. Based on the distributed architecture, there is no single point of failure. MySQL Cluster supports automatic horizontal expansion and automatic read-write load balancing.

MySQL Cluster uses a memory storage engine called NDB to integrate multiple MySQL instances to provide a unified service cluster. As shown in figure 3.

Figure 3. MySQL Cluster composition

MySQL Cluster consists of SQL Nodes,DataNodes, and NDB Management Server. SQL Nodes is the interface of the application, just like a normal mysqld service, it accepts the user's SQL input, executes it, and returns the result. Data Nodes is the data storage node, and NDB Management Server is used to manage each node in the cluster.

MySQL Cluster adopts a new way of data fragmentation and fault tolerance to achieve data security and high availability. It is composed of Partition,Replica,Data Node,Node Group.

Partition:NDB A slice of data in a table that contains a portion of the data in a table.

Replica: a copy of Partition. A Partition can have one or more Replica, and all the Replica data of a Partition is consistent.

A storage carrier for Data Node:Replica, where each Node stores one or more Replica.

Node Group: a collection of Data Node.

Figure 4. High availability of MySQL Cluster data

A MySQL Cluster has four Node, which is divided into two Grou. Node1 and 2 belong to Group0,Node3 and 4 belong to Group1. There is a table that is divided into four Partition and two Replica each. The two Replica of Partition0 and Partition2 are stored on Node1 and Node2 respectively, and the two Replica of Pratition1 and Partition3 are stored on Node3 and Node4 respectively. In this way, for a Partition of a table, there are two pieces of data in the whole cluster, which are distributed on two separate Node, and the data disaster recovery is realized. At the same time, each write to one Partition will be rendered on two Replica. If the Primary Replica is abnormal, then Backup Replica can provide services immediately to achieve high availability of data.

At this point, I believe you have a deeper understanding of "MySQL High availability solution Analysis". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.