Shulou(Shulou.com)06/02 Report--

The editor will share with you a sample analysis of the highly available architecture of MySQL. I hope you will gain something after reading this article. Let's discuss it together.

MySQL High availability

The high availability of MySQL is the same idea. First, there must be multiple MySQL instances to provide services, and secondly, when an instance dies, the traffic can be automatically switched. At the same time, MySQL as storage, data synchronization between nodes is also a problem (in other words, stateful services are faced with this problem).

One master and one backup:

All kinds of highly available architectures of MySQL are inseparable from the data synchronization between MySQL instances. Therefore, we first introduce the data synchronization process of MySQL under the simplest one-master-one-slave architecture.

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

The Master node has a Dump process to send data from binlog to the Slave node

The Slave node has an IO process to receive data to write to the relay log

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

To extend here, there are three forms of binlog: Statement, Row, and Mixed.

Statement: is to record each SQL in binlog.

Row: is to record the specific data of each row of changes to binlog.

Mixed:MySQL will be flexible to distinguish between the need to record sql or the record of specific changes.

If only SQL is recorded, the binlog will be relatively small, but some SQL statements may cause data inconsistencies during data synchronization due to the selection of different indexes. Recording Row can ensure that there will be no semantic deviation of SQL in master-slave synchronization, and it is easier for Row-type logs to recover data, but Row will cause binlog to be too large.

Several modes of master-slave synchronization in MySQL:

Asynchronous mode:

Under this synchronization strategy, the master database processes the data according to its own process and returns the result directly without waiting for the data synchronization between the master library and the slave library. Advantages: high efficiency. Cons: after the Master node dies, the Slave node will lose data. Full synchronization mode: the master library waits for all slave libraries to execute sql statements and ACK to complete before returning success. Advantages: good data consistency guarantee. Disadvantages: it will cause data operation delay and reduce the throughput of MySQL. Semi-synchronous mode: the master library waits for at least one slave database to write data to relay log and ACK to complete before successfully returning the result. Semi-synchronous mode is between asynchronous and full synchronous.

Semi-synchronous replication scheme is introduced in MySQL5.5. The steps of a common semi-synchronous replication scheme are shown below:

The Master node writes data to Binlog and performs Sync operations. Master sends data to the Slave node, while commit the transaction of the main library. After receiving the ACK, the Master node returns the data to the client.

This data submission mode is called after_commit

There is a problem with after_commit mode: when the master database waits for ACK, the transaction has already been commit, and other transactions in the master database can read the data of commit. At this time, if Master crashes, slave data is lost, and master-slave switching occurs, it will lead to phantom reading. In order to solve this problem, MySQL5.7 proposed a new semi-synchronous replication mode: after_sync.

The above problems are avoided by putting the transaction commit of the main library after ACK. MySQL5.7 also introduces enhanced multi-threaded slave (MTS for short) mode, when slave configures slave_parallel_workers > 0 and

Global.slave_parallel_type = 'LOGICAL_CLOCK', can support slave_parallel_workers worker threads under one schema to execute transactions committed by the master library in relay log concurrently, which greatly improves the efficiency of master-slave replication. The MySQL5.7 semi-synchronous function can be accessed through the

The rpl_semi_sync_master_wait_slave_count parameter configures the number of ACK of slave nodes, and the master-slave synchronization is considered to be complete.

Based on MySQL master-slave synchronization data is becoming more and more perfect and efficient, which leads to the first high-availability architecture of MySQL: master-slave synchronization scheme based on MySQL itself, a commonly used deployment architecture is that users access Master and Slave nodes through VIP, and each node uses keepalved exploration. Configure master-slave relationship for data synchronization.

High-availability architecture based on MHA: deploy a copy of MHA's Manager node and deploy MHA Node node in each instance of MySQL. MHA can achieve automatic failover in seconds. Of course, the data synchronization between MySQL nodes also depends on the data synchronization mode of MySQL itself.

MGR (MySQL Group Replication) mode: it feels that MySQL officials are more optimistic about the MGR cluster solution, but I don't know which company is using it in China. The MGR cluster is composed of all the MySQL Server, and each Server has a complete copy of data. The data synchronization before the copy is done based on the log in Row format and GTID, and the Paxos algorithm is used to ensure the consistency of the data. The MGR architecture is more complex than the semi-synchronous and asynchronous data synchronization methods described earlier, which can be found on the official website.

After reading this article, I believe you have some understanding of "sample Analysis of the High availability Architecture of MySQL". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

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.