MySQL database optimization (bottom) high-availability architecture scheme for MySQL database

MySQL database optimization (bottom) high-availability architecture scheme for MySQL database 2011-03-09 08:53 fuqin wine 51CTOfont Size:T | T

In the last MySQL database optimization, we followed the author to learn the optimization of single-machine MySQL database, today we continue to follow the author to learn MySQL optimized cluster scheme.

Ad:51cto Net + the first App innovation contest hot Start-----------super million resources for you to take!

"51CTO exclusive feature" in the last MySQL database optimization, we followed the author to learn the optimization of a single MySQL database, today we continue to follow the author to learn MySQL optimized cluster scheme.

MySQL High-availability design solution

If the single MySQL optimization is still unable to withstand the pressure, this time we have to consider the MySQL cluster solution, the current feasible solution is:

First, MySQL Cluster

Advantage: Very high availability, very good performance. Each piece of data can have at least one copy stored on different hosts, and redundant copies of the data are synchronized in real time.

But its maintenance is very complex, there are some bugs, is not suitable for comparison of the core of the online system, so I do not recommend this.

Second, the DRBD Disk network mirroring scheme

Advantage: Software is powerful, data can be mirrored across physical hosts at the underlying fast device level, and different levels of synchronization can be configured based on performance and reliability requirements. IO operations are kept in order to meet the database's demanding data consistency.

However, the non-Distributed file system environment cannot support the mirrored data, and the performance and reliability are contradictory, which is not suitable for the environment with the more demanding performance and reliability requirements, and the maintenance cost is higher than MySQL Replication. So we can consider whether to deploy according to the actual environment.

Third, MySQL Replication

In practical scenarios, MySQL replication is one of the most widely used design tools to improve system scalability. Many MySQL users through the replication function to enhance the expansion of the system, through the simple increase in inexpensive hardware equipment multiplied even to improve the performance of the original system, is the majority of MySQL low-end users like one of the features, It is also the most important reason for many MySQL users to choose MySQL.

There are several common MySQL replication architectures, which are explained in a nutshell

MySQL replicatioin Scenario One: The General replication Architecture--master-slaves, is a master replication to one or more salve schema mode, mainly for reading pressure application database-side inexpensive expansion solution, read and write separation, Master is primarily responsible for writing stress.

MySQL replicatioin Scenario Two: cascade replication Architecture, that is, master-slaves-slaves, this is to prevent slaves reading pressure is too large, and configure a level two slaves, It is easy to solve the master side because of the slave too much to become the risk of bottle strength.

MySQL replicatioin Scenario Three: Dual Master and Cascade replication in conjunction with the architecture, i.e., master-master-slaves, the greatest benefit is that the primary master's write operations are not affected by the replication of the slave cluster, and a single point of failure of the Master master is ensured.

The above is the more common MySQL replication architecture scheme, we can according to their own company's specific environment to design, Mysql load balancer can consider using LVS or haproxy to do, high-availability software I recommend heartbeat.

the lack of MySQL replication: If master host hardware failure cannot be recovered, it may result in some data loss that is not delivered to the slave end. So everyone should be based on their current network planning, choose their own reasonable MySQL schema, with their MySQL DBA and programmer multi-ditch, multi-backup (backup I will at least do local and offsite dual backup), multi-test, data is the biggest thing, not a bit of error, remember to remember.

Because of my own limited capacity, the above understanding may have errors and omissions, we have any ideas and comments can be through my blog to communicate with me, I will be the first time to correct this article.

Fuqin Technical blog for cooking wine

http://hi.baidu.com/yuhongchun027

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

Reference document Jenzhiang "MySQL performance Tuning and Architecture Design" Electronics publishing house

MySQL database optimization (bottom) high-availability architecture scheme for MySQL database