Top five common MySQL high-availability scenarios "go"

1. Overview

When we consider the highly available architecture of the MySQL database, we mainly consider the following aspects:

• If the database is down or an unexpected outage, and so on, can restore the availability of the database as soon as possible to reduce downtime, to ensure that the business will not be interrupted due to database failure.

• Data for non-primary nodes that are used as backups, read replicas, and so on, should be in real-time or eventually consistent with the master node's data.

• When a database switch occurs in a business, the contents of the database before and after the switchover should be consistent, and the business will not be affected by data loss or inconsistent data.

About highly available ratings here we do not discuss in detail, only the pros and cons of commonly used high-availability scenarios and the selection of highly available alternatives.

2. Highly Available scenarios

2.1. master-Slave or primary master semi-synchronous replication

Use a two-node database to build one-way or two-way semi-synchronous replication. In the version after 5.7, due to the introduction of some new features such as lossless replication, logical multithreaded replication, MySQL native half-synchronous replication is more reliable.

The common architecture is as follows:

Usually with proxy, keepalived and other third-party software used at the same time, that can be used to monitor the health of the database, but also can execute a series of administrative commands. If the main library fails, you can still continue to use the database after switching to the standby repository.

Advantages:

1. The architecture is relatively simple, using native semi-synchronous replication as the basis of data synchronization;

2. Two nodes, no host downtime after the selection of the main problem, direct switching can be;

3. Two-node, less demand resources, simple deployment;

Disadvantages:

1. Fully dependent on semi-synchronous replication, if the semi-synchronous replication is degraded to asynchronous replication, data consistency cannot be guaranteed;

2. The high availability mechanism of haproxy and keepalived needs extra consideration.

2.2. Semi-synchronous replication optimization

The semi-synchronous replication mechanism is reliable. If half-synchronous replication is always in effect, then the data can be considered consistent. However, due to the network fluctuations and other objective reasons, resulting in the semi-synchronous replication time-out and switch to asynchronous replication, then there is no guarantee of data consistency. Therefore, as far as possible to ensure the semi-synchronous replication, can improve the consistency of data.

The scheme also uses a two-node architecture, but the function optimization is done on the basis of the original semi-identical replication, which makes the mechanism of semi-synchronous replication more reliable.

You can refer to the following optimization scenarios:

2.2.1. Dual-channel replication

Semi-synchronous replication due to a time-out, replication is fractured, when replication is established, two channels are established at the same time, and one of the semi-synchronous replication channels is copied from the current location to ensure that the slave knows the progress of the current host execution. Another asynchronous replication channel begins to retroactive data that is backward from the machine. When the asynchronous replication channel catches up to the start of the semi-synchronous replication, the semi-synchronous replication is resumed.

2.2.2. binlog File Server

Build two semi-synchronous replication channels, where the semi-synchronous channel connecting the file server is normally not enabled, and when the master-slave semi-synchronous replication network problem degrades, start the semi-synchronous replication channel with the file server. When the master-slave semi-synchronous copy is restored, the semi-synchronous replication channel with the file server is closed.

Advantages:

1. Two-node, less demand resources, simple deployment;

2. The structure is simple, there is no problem of choosing the main, direct switch can be;

3. Compared to native replication, optimized semi-synchronous replication guarantees data consistency.

Disadvantages:

1. Need to modify kernel source code or use MySQL communication protocol. Need to have a certain understanding of the source code, and can do a certain degree of development of two times.

2. Still relies on semi-synchronous replication and does not fundamentally address data consistency issues.

2.3. High-Availability architecture optimization

Extend a two-node database to a multi-node database, or a multi-node db cluster. According to their own needs to choose a master two from, a master multi-slave or multi-master multi-slave cluster.

Because of the semi-synchronous replication, there is a successful response to a slave that is considered to be a successful semi-synchronous replication feature, so the reliability of multi-slave replication is better than the reliability of single-copy from semi-synchronous replication. and the probability of a multi-node simultaneous outage is less than the probability of a single node outage, so a multi-node architecture can be considered to some extent high availability is better than a two-node architecture.

However, due to the large number of databases, database management software is needed to ensure the maintainability of the database. You can choose Mmm, MHA, or various versions of Proxy and so on. Common scenarios include the following:

2.3.1. mha+ Multi-node cluster

MHA Manager periodically probes the master node in the cluster, and when master fails, it automatically promotes the slave of the latest data to the new master, and then points all other slave to the new master. The entire failover process is completely transparent to the application.

MHA node runs on each MySQL server, and the primary role is to process the binary log when switching, ensuring that the switch minimizes data loss.

MHA can also be extended to the following multi-node clusters:

Advantages:

1. Fault detection and transfer can be carried out;

2. Scalability is good, you can expand the number and structure of MySQL nodes as needed;

3. Three-node/multi-node MySQL is less likely to occur when compared to two-node MySQL replication

Disadvantages:

1. At least three nodes are required, and more resources are needed relative to the two nodes;

2. Logic is more complex, after failure to troubleshoot problems, positioning problems more difficult;

3. Data consistency is still guaranteed by native semi-synchronous replication, and there is still a risk of inconsistent data;

4. May be caused by the network partition brain crack phenomenon;

2.3.2. Zookeeper+proxy

Zookeeper uses the distributed algorithm to ensure the consistency of the cluster data, using zookeeper can effectively guarantee the high availability of the proxy, and can avoid the generation of the network partition phenomenon better.

Advantages:

1. Better guarantee the high availability of the whole system, including proxy, MySQL;

2. Scalability is good, can be extended to large-scale cluster;

Disadvantages:

1. Data consistency still relies on native MySQL semi-synchronous replication;

2. By introducing ZK, the logic of the whole system becomes more complex;

2.4. Shared storage

Shared storage realizes the decoupling of database server and storage device, and the data synchronization between different databases no longer relies on MySQL's native replication function, but the data consistency is ensured by means of disk data synchronization.

2.4.1. San shared storage

The concept of SAN is to allow the storage device and processor (server) to establish a direct high-speed network (compared to the LAN) connection, through which the centralized storage of data. Common architectures are as follows:

With shared storage, the MySQL server can mount the file system and operate properly, and if the main library goes down, the repository can mount the same file system, ensuring that the main and standby libraries use the same data.

Advantages:

1. Two nodes, simple deployment, simple switching logic;

2. Good assurance of strong consistency of data;

3. There is no data inconsistency due to MySQL logic error;

Disadvantages:

1. Need to consider the high availability of shared storage;

2. The price is expensive;

2.4.2. DRBD Disk Replication

DRBD is a software-based, network-based block replication storage solution that is primarily used to mirror data between servers, such as disks, partitions, logical volumes, and so on, when a user writes data to a local disk, and sends the data to the disk of another host on the network, such that the local host (master node) and the remote host ( Data on the standby node can be guaranteed in real-time synchronization. Common architectures are as follows:

When the local host problems, the remote host also retains a copy of the same data, can continue to use, to ensure the security of the data.

DRBD is a fast-level synchronous replication technology implemented by the Linux kernel module that achieves the same shared storage effect as a SAN.

Advantages:

1. Two nodes, simple deployment, simple switching logic;

2. Low price compared to SAN storage network;

3. Ensure strong consistency of data;

Disadvantages:

1. Has a large impact on IO performance;

2. Read operations are not available from the library;

2.5. Distributed protocols

The distributed protocol can solve the data consistency problem well. The more common scenarios are as follows:

2.5.1. MySQL cluster

MySQL cluster is the official cluster deployment solution that enables database high availability and data consistency by using the NDB storage engine to back up redundant data in real time.

Advantages:

1. Use all official components and do not rely on third party software;

2. Can achieve strong consistency of data;

Disadvantages:

1. less domestic use;

2. The configuration is more complex and requires the use of the NDB storage engine, which differs from the MySQL conventional engine.

3. At least three nodes;

2.5.2. Galera

MySQL high-availability cluster based on Galera is a MySQL cluster solution for multi-master data synchronization, which is simple to use and has no single point of failure and high availability. The common architecture is as follows:

Advantages:

1. Multi-Master Write, no delay replication, can ensure strong data consistency;

2. There are mature communities, with internet companies in large-scale use;

3. Automatic failover, automatically add, eliminate nodes;

Disadvantages:

1. Need to hit Wsrep patch for native MySQL node

2. Supports only InnoDB storage engine

3. At least three nodes;

2.5.3. Poaxs

The problem solved by the Paxos algorithm is how a distributed system can agree on a value (resolution). This algorithm is considered to be the most effective of the same kind of algorithm. The combination of Paxos and MySQL can achieve strong consistency in distributed MySQL data. The common architecture is as follows:

Advantages:

1. Multi-Master Write, no delay replication, can ensure strong data consistency;

2. Have a mature theoretical basis;

3. Automatic failover, automatically add, eliminate nodes;

Disadvantages:

1. Supports only InnoDB storage engine

2. At least three nodes;

3. Summary

With the increasing demand for data consistency, more and more methods are being tried to solve the problem of distributed data consistency, such as MySQL's own optimization, MySQL cluster architecture optimization, Paxos, Raft, 2PC algorithm introduction and so on.

And the method of using distributed algorithm to solve the problem of MySQL database data consistency is more and more accepted by people, a series of mature products such as phxsql, MariaDB galera Cluster, Percona XtraDB More and more cluster are being used on a large scale.

With the official MySQL Group replication ga, the use of distributed protocols to solve data consistency problem has become the mainstream direction. Expect more and more excellent solutions to be presented, MySQL high availability problems can be better solved.

Turn from

Five common MySQL high-availability scenarios-today's headline (www.toutiao.com)
http://www.toutiao.com/i6400710949384225282/

Top five common MySQL high-availability scenarios "go"