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

This article introduces the relevant knowledge of "Summary of the advantages and disadvantages of MySQL eight Cluster Architecture". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Many developers may not have been exposed to MySQL architecture deployment, but most of them should have heard of cluster architecture. In fact, there are many kinds of MySQL cluster architectures. Today, I will mainly summarize 8 kinds of cluster architectures commonly used.

Master-slave architecture

Generally speaking, the master-slave architecture is the separation of reading and writing. His advantage is that the data can be backed up. And, to some extent, it alleviates the efficiency of reading and writing. So as to improve the availability of the database system.

Master / Master / Mutual backup + keepalived

One feature of the master-slave architecture is that if there is a failure, it is impossible to talk about high availability. Therefore, some companies adopt this architecture of master and master mutual backup to solve the impact of sudden single point of failure. The characteristic of this architecture is that MySQL dual-master replication, that is, Master-Slave for each other (only one Master provides write operations), can achieve hot backup of the database server, but a Master can not be dynamically switched after downtime. Using Keepalived, we can realize the unified interface between two hosts and the mechanism of automatic checking and failure switching through virtual IP, so as to realize the high availability scheme of MySQL database.

Master and master backup each other, master + slave respectively

This architecture has better performance than the master / master / backup + keepalived mode above. The separation of reading and writing can be realized.

Master and master are prepared for each other, and are added to one master from.

Compared with the above architecture, adding a slave library to each master library is too wasteful, including IO and so on. If all the slave libraries are linked to a master library, it will be more efficient.

MMM architecture

MMM (Master-Master replication manager for MySQL) is a set of scripts that support dual-master failover and dual-master daily management. MMM is developed in Perl language, based on mysql master-slave replication, mature high-availability cluster scheme, composed of a management side (monitor) and multiple agents (aget).

The advantages of this architecture: monitor the status of all Master nodes and Slave nodes, when the master node fails, automatically transfer the vip to the healthy node; more importantly, when the Master node fails, it will automatically transfer the back-end Slave node to the standby Master node to continue synchronous replication, and the switching process does not require human intervention

Disadvantages: for ip, the number of servers is required (at least two servers and 2 real ip,3 vip); when the business is busy and the amount of data is large, it is not very stable, which may lead to replication delay, handover failure and other problems; therefore, MMM scheme is not suitable for environments with high data security requirements and frequent read and write. When there is a large amount of data, there will be the problem of master-slave data out of sync.

MHA architecture

MHA (Master High Availability) is currently a relatively mature solution for MySQL high availability. It was developed by youshimaton, a Japanese DeNA company (now working for Facebook). It is a set of excellent high availability software for failover and master-slave upgrade in MySQL high availability environment. In the process of MySQL failover, MHA can automatically complete the failover operation of the database within 30 seconds, and in the process of failover, MHA can ensure the consistency of the data to the maximum extent in order to achieve high availability in the real sense.

This kind of architecture is troublesome to build, at least three machines, Taobao has carried out secondary development, can use two machines.

Mysqlproxy

One of the powerful features of MySQL Proxy is to achieve "read-write separation". The basic principle is to let the master database handle write-side transactions and the slave library to handle SELECT queries.

The separation of read and write through lua scripts is not recommended on the official website. Because the addition of a layer of proxy will lead to increased consumption of network requests, so the performance has a certain impact.

Amoeba

Amoeba for MySQL is an excellent middleware software, which can also achieve read-write separation, load balancing and other functions, and the stability is also higher than MySQL Proxy.

Amoeba (amoeba) project, focusing on the development of distributed database proxy. Located between Client and DB Server (s). Transparent to the client. It has load balancing, high availability, sql filtering, read-write separation, routing related query to the target database, and concurrent requests for merging results of multiple databases.

Its features: reduce the complex multi-database structure brought by data segmentation; provide segmentation rules and reduce, data segmentation rules, the impact on applications; reduce the number of connections between db and the client; read-write separation.

Amoeba is committed to the mysql distributed database front-end proxy layer, which mainly acts as a SQL router when accessing mysql in the application layer, and sends SQL requests to a specific database for execution according to the rules set by users in advance. Based on this, we can achieve load balancing, read-write separation, high availability and other requirements. Amoeba is the equivalent of a router for SQL requests and is intended to provide mechanisms for load balancing, read-write separation, and high availability, rather than fully implementing them.

This is the end of the summary of the advantages and disadvantages of MySQL's eight cluster architectures. Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.