High availability analysis of MySQL database

Objective

MySQL database is currently the largest open source application of relational database, there are a huge number of applications to store data in the MySQL database. The security and reliability of storing data is the focus of the production database. In this paper, we analyze the current availability of MySQL with more support.

MySQL Replication

MySQL replication is a master-slave synchronization scheme provided by MySQL, which synchronizes data from one MySQL instance to another. Replication is an important guarantee for data security and is now the most widely used MySQL disaster recovery solution. Replication uses two or more instances to build a MySQL master-slave replication cluster, providing a single point of write, multi-point read service, realized the read scale out.

Figure 1. MySQL Replication Master-slave replication cluster

As shown, a master instance (M), three slave instances (S), generates an event Binlog through Replication,master, and then sends Slave,slave to write the event to Relaylog and then submits it to its own database. Implement master-Slave data synchronization. For the business layer on the database, MySQL-based master-slave replication cluster, single-point write master, after the event synchronization to slave, read logic can read from any one slave, in order to read and write the separation of the way, greatly reducing the running load of master, It also improves the utilization of slave resources.

For high availability, MySQL replication has an important flaw: the latency of data replication. In general, MySQL replication data replication is asynchronous, that is, after the MySQL write Binlog, sent to slave does not wait for slave return acknowledgement received, the local transaction is submitted. In the event of network delays or interruptions, data delays are sent to the slave side, and the master-slave data is inconsistent. At this stage, when Master is down, data that is not sent to slave is lost, and data is not highly available.

To solve this problem, Google offers a solution: semi-synchronous and synchronous replication. On the basis of asynchronous data replication, a bit of modification was made. Semi-synchronous replication is the relay that master waits for an event to write to slave, and then commits the local, ensuring that the slave must receive the data that needs to be synchronized. Synchronous replication does not only require slave to receive data, but also requires slave to commit the data to the database, ensuring that data is written every time, and the master-slave data is consistent.

Based on semi-synchronous and synchronous replication, the high availability of MySQL replication is improved, especially for synchronous replication. Based on the synchronous replication of the MySQL replication cluster, the data read by each instance is consistent and there is no slave phantom read. At the same time, when Master goes down, the application switches to any slave to ensure the consistency of the read and write data. However, synchronous replication has resulted in significant performance degradation, a tradeoff that needs to be made here. In addition, MySQL replication master-Slave switching requires manual intervention to determine, while the need to slave Replaylog submitted complete, the recovery time will be relatively long.

MySQL Fabric

MySQL fabric is an extension that the MySQL community provides to manage multiple MySQL services. High availability is one of the main features of its design.

Fabric divides two and more MySQL instances into an ha Group. One of them is the Lord, and the rest is from. The HA group guarantees that access to the specified HA group data is always available. Its underlying data replication is based on MySQL Replication, and then fabric provides more features:

Fail-over detection and recovery: fabric monitors The primary instance in the HA group, and once the primary instance is found to be invalidated, fabric chooses one from the remaining instances in the HA group and promotes it to the primary instance.

Read-Write Equalization: Fabric can automatically handle the read and write operations of an HA group, sending writes to the primary instance, while read requests are load balanced across multiple instances.

Figure 2. Fabric

MHA

MHA (mysql-master-ha) is a highly available scheme for MySQL master-slave replication, which is now widely used. The MHA design goal is to automatically implement the main instance after the outage, the slave switch mainly, and minimize the switching delay (usually within the 10-30s switch completed). At the same time, the data consistency in the switching process is ensured 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 primary instance is down, MHA can automatically determine which of the master-slave replication clusters is up-to-date from the instance's Relaylog, and the latest from the instance's difference log "applied" to the rest of the slave instances, ensuring that the data for each instance is consistent. Typically, the MHA needs to detect the primary instance anomaly around 10s and close the primary instance to avoid brain fissures. Then synchronize the difference log event around 10s and enable the new master. The RTO time of the entire MHA is around 30s.

MySQL Cluster

MySQL cluster is a highly scalable, acid-compliant real-time database that does not have a single point of failure based on a distributed architecture, and MySQL cluster supports automatic horizontal scaling and automatic read and write load balancing.

MySQL cluster uses a memory storage engine called NDB to consolidate multiple MySQL instances and provide a unified service cluster. As shown in three.

Figure 3. MySQL Cluster composition

MySQL cluster consists of SQL Nodes,datanodes, and NDB Management server. SQL nodes is an interface to an application that accepts the user's SQL input, executes and returns results, just like a normal mysqld service. Data nodes is a datastore node, NDB Management Server is used to manage each node in the cluster.

MySQL cluster uses new data fragmentation and fault-tolerant methods to achieve data security and high availability. It is composed of Partition,replica,data Node,node group.

Partition:ndb a piece of data from a table that contains part of the data for a table.

Replica: A copy of the partition. A partition can have one or more replica, and all replica data for a partition are consistent.

The data Node:replica storage carrier, each Node stores one or more replica.

Node Group: A collection of data node.

Figure 4. MySQL Cluster Data High availability

A MySQL cluster has 4 node and is divided into two grou. Node1 and 2 belong to Group0,node3 and 4 belong to Group1,. There is a table divided into 4 partition and two replica respectively. The two replica of Partition0 and Partition2, respectively, are stored on Node1 and Node2, and two Pratition1 and Partition3 are replica and Node3 respectively. Thus, for a partition of a table, there are two data in the whole cluster and distributed on two separate node to achieve the data disaster recovery. At the same time, each write to a partition will be presented on two replica, if the primary replica exception, then backup replica can immediately provide services to achieve high data availability.

Summary

In this paper, we analyze several MySQL data replication and high-availability programs, MySQL replication is the most widely used data replication scheme, because it is MySQL native support, and it derives the semi-synchronous replication for some defects in different scenarios. High-availability scenarios such as strong synchronous replication.

On this basis, for the convenience of operation and maintenance, MySQL fabric and MHA came into being, which solved the problem of data consistency and process automation in different directions. In addition, with the gradual maturation of distributed system architectures and scenarios. MySQL cluster designed a new distributed architecture, using multi-copy, sharding and other features, support horizontal expansion, to achieve 5 9 of database service quality assurance.

High availability analysis of MySQL database