Optimization of the MySQL database (below) the high-availability architecture solution of the MySQL database

BKJIA exclusive Article]In the previous article on MySQL database optimization, we followed the author to learn about the optimization of the single-host MySQL database. Today we continue to follow the author's learning about the MySQL Optimized Cluster solution.

MySQL High Availability design scheme

If the optimization of a single MySQL instance is still under pressure, we must consider the MySQL cluster solution at this time. 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. Therefore, you can determine whether to deploy the service 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 Replicatioin solution 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 Replicatioin solution 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 Replicatioin solution 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. For highly available software, I recommend Heartbeat.

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.

Due to my limited ability, the above understandings may have errors and omissions. If you have any ideas or comments, please contact me through my blog. I will correct this article as soon as possible.

Technical blog of fuqin liquor cooking

Http://hi.baidu.com/yuhongchun027

Http://andrewyu.blog.51cto.com

Reference document: Chaoyang Electronic Industry Press MySQL performance tuning and Architecture Design