Analysis of the advantages, disadvantages and applicable scenarios of mainstream MySQL cluster implementation architecture_PHP tutorial

Analysis of advantages, disadvantages and applicable scenarios of mainstream MySQL cluster implementation architecture

This article mainly analyzes and summarizes several current mainstream MySQL cluster architecture implementation methods, advantages and disadvantages, and is hereby issued for your reference and study , make progress together, if there is anything wrong, please feel free to let me know~
1. Master-slave replication architecture MHA
MHA is the Master High Availability Manager and Tools for MySQL. It is written in Perl language by a Japanese MySQL expert. A MySQL high-availability management tool that aims to maintain the high availability and data consistency of the Master database.
MHA is currently a relatively mature solution for MySQL high availability and is widely used in a large number of enterprises. Its biggest feature is that it can repair the difference logs between multiple slaves, and finally make all slaves keep data consistent, and then select a slave database as the new master and point other slaves to it.
When the Master fails, MHA will randomly select one from multiple Slaves to act as the new Master. You can also specify a certain Slave to become the Master first in the configuration file.
The architecture is as follows, please refer to:

For a brief explanation of the above figure, please refer to:
1. Separation of reading and writing in MHA architecture, the best practice is in application development Plan read and write separation issues in advance when designing, and set up two connection pools when using them, namely read connection pool and write connection pool. You can also choose a compromise solution and introduce SQL Proxy;
2. For read load balancing, you can use F5 , LVS, HAPROXY or SQL Proxy and other tools, as long as they can achieve load balancing, fault checking and read and write stripping functions after the backup is upgraded to the primary, it is recommended to use LVS;
3. MHA Manager Node is mainly responsible for the main database in the crash At this time, the bin log is completely synchronized to the slave library, and the status and switching of the active and standby libraries are monitored.
2. Official cluster MySQLCluster architecture
MySQL Cluster is an enterprise-level solution officially provided by Oracle. It adopts a data storage model without shared storage, truly realizing real-time synchronization of data in the cluster and transparency of database node failures to applications. The official commitment is that MySQL Cluster can guarantee 99.999% availability of the database system.

For the following simple explanation of the above figure, please refer to:
1. For read and write load balancing, you can use F5, LVS, HAPROXY or other SQL Proxy tools, as long as the load can be achieved Just balance and fault check;
2. MySQL Cluster supports both Cluster and Sharding functions, and tables are generally designed according to actual needs;
3. Sharding tables generally require the use of NDB Cluster storage engine;
4 .MGM node: Its role is to manage other nodes in MySQL Cluster, such as providing configuration data, starting and stopping nodes, running backups, etc. Its own failure will not affect MySQL Cluster;
5.SQL node: used to access Cluster Data node;
6. NDB node: used to save Cluster data;
7. MySQL Cluster supports real-time synchronization between clusters, and the granularity can be as detailed as the row level, enabling data center deployment.
3. Open source distributed solution Cobar
Cobar is a distributed processing system for relational data developed by Alibaba’s B2B department. It can provide you with massive amounts of data in a distributed environment just like a traditional database. Data Services. The main functions of Cobar are as follows:
1. Cobar supports horizontal splitting of a table into multiple parts and puts them into different libraries to achieve horizontal splitting of the table;
2. Cobar also supports placing different tables into into different libraries;
3. In most cases, the above two methods are mixed and used;
4. Cobar can automatically send heartbeats to the MySQL connected to the backend to determine the operating status of MySQL. Once an abnormality occurs, Cobar can automatically switch to standby work.
For the following simple explanation of the above picture, please refer to:
1.Cobar supports both Cluster cluster function and Sharding data slicing function. Generally, a mix and match method will be used according to actual needs;
2.Cobar It implements functions such as load balancing of application requests, health check of back-end nodes, SQL segmentation and aggregation of query result sets;
3. MySQL primary and secondary databases should be set to primary-primary two-way synchronization mode, that is, MySQL M01 must be Set to master-master synchronization mode with MySQL S01.

http://www.bkjia.com/PHPjc/1090823.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/1090823.htmlTechArticleAnalysis of the advantages, disadvantages and applicable scenarios of mainstream MySQL cluster implementation architectures. This article mainly analyzes and summarizes several current mainstream MySQL cluster architectures. The implementation methods, advantages and disadvantages are now posted for your reference...