What are the common MySQL high availability design solutions?

MySQL high-availability design solution. If the optimization of MySQL alone still cannot withstand the pressure, at this time we must consider the MySQL cluster solution. The currently feasible solutions are:

1. MySQL Cluster

Advantages: Very high availability and very good performance. At least one copy of each data can be stored on different hosts, and redundant data copies are synchronized in real time. Since its maintenance is cumbersome and there are some vulnerabilities, I do not recommend using it in critical online systems.

2. DRBD disk network mirroring solution

This software has the following advantages: it has powerful functions, can quickly mirror data across different physical hosts at the underlying device level, and can be based on performance and reliability Sexual needs configure different levels of synchronization. IO operations maintain order, which can meet the stringent requirements of the database for data consistency.

However, non-distributed file system environments cannot support mirror data being visible at the same time. Performance and reliability are contradictory. It cannot be applied to environments with demanding performance and reliability requirements. The maintenance cost is higher than MySQL Replication. Therefore, you can consider whether to deploy it based on the actual environment.

3. MySQL Replication

MySQL Replication is widely used in actual scenario design to improve system scalability. After many MySQL users have improved the scalability of the system through the Replication function, they have doubled or even increased the performance of the original system by simply adding low-priced hardware devices. This is a feature that the majority of low-end and mid-range MySQL users like very much. One of the most important reasons why many MySQL users choose MySQL.

There are several more conventional MySQL Replication architectures. Here are a brief explanation of them.

MySQL Replicatioin Solution 1: Conventional replication architecture Master-slaves, which is replicated from one Master to one or more Salve's architectural model is mainly used for cheap expansion solutions on the database side of applications with heavy reading pressure. Reading and writing are separated, and the Master is mainly responsible for the writing pressure.

MySQL Replicatioin Solution 2: Cascading replication architecture, namely Master-Slaves-Slaves, this is also to prevent excessive read pressure on Slaves, and configure a layer of secondary Slaves, which can easily solve the problem of attached slaves on the Master side. Risk of having too much and becoming a bottleneck.

MySQL Replicatioin Option 3: Dual Master and cascade replication combined architecture, that is, Master-Master-Slaves, the biggest advantage is that it can avoid the main Master’s write operations from being affected by the replication of the Slave cluster, and The single point of failure of the main Master is guaranteed.

Disadvantages of MySQL Replication: If the Master host hardware failure cannot be recovered, some data that has not been transmitted to the slave may be lost. Therefore, everyone should choose their own reasonable MySQL architecture solution based on their current network planning, communicate with their MySQL DBA and programmers, make multiple backups (I will at least do local and off-site backup), test more, and data The most important thing is, there should be no mistakes at all, so remember it.

The above is the detailed content of What are the common MySQL high availability design solutions?. For more information, please follow other related articles on the PHP Chinese website!