Feasible solutions for MySQL Clusters

If the optimization of a single MySQL is still under pressure, we must consider the high-availability architecture of MySQL (many people also love to say it is a MySQL cluster, currently, feasible solutions include:

I. MySQL Cluster
Advantage: high availability and excellent performance. Each copy of data can be stored in at least one copy on different hosts, and redundant copies can be synchronized in real time. However, it is very complicated to maintain and has some bugs. At present, it is not suitable for core online systems, so I do not recommend this.

Ii. drbd disk Network image Solution
Advantage: powerful software, data can be saved on the underlying device level across physical host images, and different levels of synchronization can be configured according to performance and reliability requirements. Io operations are sequential and can meet the stringent data consistency requirements of the database. However, the non-distributed file system environment cannot support simultaneous visibility of image data. The performance and reliability are in conflict and cannot be applied to environments with demanding performance and reliability requirements. The maintenance cost is higher than that of MySQL replication. In addition, drbd is also one of the highly available solutions officially recommended for MySQL. Therefore, you can determine whether to deploy drbd based on the actual environment.

Iii. MySQL Replication
In practical application scenarios, MySQL replication is the most widely used design method to improve system scalability. After many mysql users use the replication function to improve the scalability of the system, the performance of the original system is improved by simply adding low-cost hardware devices to multiply or even order of magnitude, it is one of the favorite features of the majority of low-end mysql users, and is also the most important reason for many mysql users to choose MySQL.

There are several common MySQL replication architectures.

MySQL replication architecture 1:

The general replication architecture, master-slaves, is an architecture model that is replicated from one master to one or more salve instances. It is mainly used for low-cost scaling solutions for application databases with high read Pressure and read/write splitting, the master is mainly responsible for writing pressure.

MySQL replication architecture 2:

Cascade replication architecture, that is, master-slaves. This is also designed to prevent excessive read Pressure on slaves, and a layer-2 slaves is configured, it is easy to solve the risk that the master node becomes a bottleneck because there are too many attached slave instances.
MySQL replication architecture 3:

Dual Master combined with cascade replication architecture, that is, master-slaves, the biggest advantage is that it can avoid the impact of write operations on the master by the replication of the slave cluster, it also ensures the single point of failure of the master.
The above is a common MySQL replication architecture solution. You can design it based on your company's specific environment. For MySQL Server Load balancer, you can use LVS or haproxy. I recommend heartbeat for highly available ha software.

Disadvantages of MySQL replication:

If the hardware fault of the master host cannot be recovered, some data that is not transmitted to the slave may be lost. Therefore, you should choose your own rational MySQL architecture scheme based on your current network plan, and communicate with your MySQL dBA and programmers, multiple backups (I will at least implement local and remote dual-Backup for backup). For Multiple tests, data is the most important thing. Please remember when there are no mistakes.