Detailed explanation of MySQL high availability architecture

introduction

"High availability" is an eternal topic on the Internet. Let's not talk about MySQL for now. There are several commonly used solutions to ensure high availability of various services.

Service redundancy: Deploy multiple copies of the service and switch to other nodes when a node is unavailable. Service redundancy is relatively easy for stateless services.

Service backup: Some services cannot exist in multiple runtimes at the same time, such as Nginx reverse proxy and leader nodes of some clusters. At this time, a backup service can exist and be on standby at all times.

Automatic switching: After service redundancy, when a node is unavailable, quick switching is required.

To sum up, it is redundancy + failover.

MySQL High Availability

The high availability of MySQL is based on the same idea. First, there must be multiple MySQL instances to provide services. Second, when an instance fails, the traffic can be automatically switched. At the same time, when MySQL is used as storage, data synchronization between nodes is also a problem (in other words, all stateful services face this problem).

One master and one backup:

Various high-availability architectures of MySQL are inseparable from data synchronization between MySQL instances. Therefore, we first introduce the data synchronization process of MySQL in the simplest one-active-one-standby architecture.

The above figure is a schematic diagram of master-slave data synchronization.

The Master node has a Dump process that sends the data in the binlog to the Slave node.

The slave node has an IO process that receives data and writes it to the relay log.

The SQL process of the slave node writes data according to the relay log.

Here we need to extend a little bit. Binlog exists in three forms: Statement, Row, and Mixed.

Statement: records each SQL statement in binlog.

Row: records the specific data of each row modification into binlog.

Mixed: MySQL will flexibly distinguish whether it needs to record SQL or specific modified records.

If only SQL is recorded, the binlog will be smaller. However, when synchronizing data between the master and the slave, some SQL statements may cause data inconsistency during the data synchronization process due to the selection of different indexes. Recording Row can ensure that there will be no SQL semantic deviation in master-slave synchronization. At the same time, Row type logs are easier to recover data, but Row will cause the binlog to be too large.

Several modes of MySQL master-slave synchronization:

Asynchronous mode:
Under this synchronization strategy, the master database will directly return the results after processing the data according to its own process, without waiting for data synchronization between the master database and the slave database. Advantages: high efficiency. Disadvantage: After the Master node hangs up, the Slave node will lose data. Full synchronization mode: The master database will wait for all slave databases to complete the execution of the SQL statement and ACK completion before returning success. Advantages: Good data consistency guarantee. Disadvantages: It will cause data operation delays and reduce MySQL throughput. Semi-synchronous mode: The master database will wait for at least one slave database to write data to the relay log and ACK completion before successfully returning the result. Semi-synchronous mode is between asynchronous and fully synchronous.

The semi-synchronous replication solution was introduced in MySQL 5.5. The steps of the common semi-synchronous replication solution are as follows:

The Master node writes data to Binlog and performs Sync operations. The Master sends data to the Slave node and commits the transaction of the master database. After receiving ACK, the Master node returns the data to the client.

This data submission mode is called: after_commit

The after_commit mode has a problem: When the master database is waiting for ACK, the transaction has been committed, and other transactions in the master database can read the committed data. At this time, if the Master crashes, the slave data is lost, and a master-slave switch occurs, phantom reads will occur. To solve this problem, MySQL 5.7 introduces a new semi-synchronous replication mode: after_sync

The above problems are avoided by putting the transaction submission of the main database after ACK. MySQL 5.7 also introduced enhanced multi-threaded slave (MTS) mode, when the slave is configured with slave_parallel_workers > 0 and
global.slave_parallel_type = 'LOGICAL_CLOCK', which can support slave_parallel_workers worker threads to concurrently execute transactions submitted by the master in the relay log under one schema, greatly improving the efficiency of master-slave replication. MySQL 5.7 semi-synchronous function can be achieved through
The rpl_semi_sync_master_wait_slave_count parameter configures the number of ACKs from the slave node, which is considered to be the completion of master-slave synchronization.

As MySQL master-slave synchronization data becomes more and more perfect and efficient, the first MySQL high-availability architecture is introduced: Based on MySQL's own master-slave synchronization solution, a commonly used deployment architecture is: Users access the Master and Slave nodes through VIP, and each node uses keepalved exploration. Configure the master-slave relationship and synchronize data.

High availability architecture based on MHA: Deploy an MHA Manager node and deploy MHA Node nodes in each MySQL instance. MHA can achieve automatic failover within seconds. Of course, data synchronization between MySQL nodes also depends on MySQL's own data synchronization method.

MGR (MySQL Group Replication) mode: I feel that MySQL officials are more optimistic about the MGR cluster solution, but I don’t know which company in China is using it yet. The MGR cluster is composed of all MySQL Servers. Each Server has complete replica data. The replicas synchronize data based on Row-format logs and GTID, and use the Paxos algorithm to ensure data consistency. The MGR architecture is more complex than the semi-synchronous and asynchronous data synchronization methods described above. For details, please refer to the official website

Summarize

There is no silver bullet for MySQL's high-availability architecture. Just understand its principles and choose a deployment architecture that suits your business scenario.

This is the end of this article about the detailed explanation of MySQL high availability architecture. For more relevant MySQL high availability architecture content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!