Mysql-15-mysql distributed application, mysql-15-mysql

Mysql-15-mysql distributed application, mysql-15-mysql

1. Concepts and advantages of distributed applications

A Distributed Database is a logical and unified database that uses a high-speed network to connect multiple physically dispersed data storage units. The basic idea of a distributed database is to distribute the data in the original centralized database to multiple data storage nodes connected through the network for greater storage capacity and higher concurrent access traffic. In recent years, with the increase in data volumes, the distributed database technology has also developed rapidly. traditional relational databases have started from centralized models to distributed storage, from centralized computing to distributed computing.

The main purpose of the distributed database system is Disaster Tolerance and remote data backup. With the principle of proximity access, users can access the database node nearby, thus implementing load balancing in different regions. At the same time, data transmission and synchronization between databases can ensure data consistency in a distributed manner. This process completes data backup and remote data storage does not affect service access when a single point of failure occurs, you only need to switch the access traffic to a remote image.

The advantages of distributed database applications are as follows:

(1) suitable for Distributed Data Management, which can effectively improve system performance.

(2) Good system economics and flexibility.

(3) high system reliability and availability.

 

2. Main technologies of mysql distributed applications

(1) mysql Data Cutting

Sharding refers to distributing data stored in the same database to multiple databases (hosts) through a specific condition to distribute the load of a single device. Data splitting can also improve the overall availability of the system, because after a single crash, only a part of the overall data is different, not all data.

Based on the sharding rule type, there are two sharding modes. One is to split the data to different databases (hosts) based on different tables (or Schemas). This split becomes the vertical (Vertical Split) of the data ); the other is to split the data in the same table into multiple databases (hosts) according to certain conditions based on the Logical Relationship of the data in the table. This split is called the data level (horizontal) split. Vertical splitting features simple rules and more convenient implementation. It is especially suitable for systems with low Coupling Degree, low mutual influence, and clear business logic. In such a system, it is easy to split the tables used by different business modules into different databases .. Splitting Based on different tables has less impact on applications, and the splitting rules are simple and clear. Horizontal splitting is a little more complex than vertical splitting. Because different data in the same table needs to be split into different databases, the splitting rules are complicated for applications and the data maintenance in the later stage is more complicated.

MySQL and later versions support data table partitioning. After the data in the database is vertically or horizontally split and stored in different database hosts, the biggest problem facing the application system is how to integrate these data sources, there are two solutions.

 

(1) In each application module, configure and manage one (or more) data sources that you need, directly access each database, and complete data integration in the module.

 

(2) The intermediate proxy layer is used to manage all data sources. The backend database cluster is transparent to the front-end applications.

 

In the second solution, although the cost in the short term may be larger, It is very helpful for the scalability of the entire system. For the second solution, you can consider the following:

 

(1) use mysql proxy to split and integrate data.

 

Mysql proxy establishes a connection pool between the client request and the mysql server. All client requests are sent to the mysql proxy. The mysql proxy analyzes the connection pool to determine whether to perform read or write operations, then it is sent to the corresponding mysql server. Slave clusters with multiple nodes can also achieve load balancing.

 

(2) Use amoeba to split and integrate data.

 

Amoeba is an open-source framework developed based on java and focused on integrating the proxy program for Distributed Database Data sources. amoeba already has query routing, query filtering, read/write splitting, Server Load balancer, and HA mechanisms. Amoeba mainly solves the following problems:

 

① Integrate complex data sources after data splitting

 

② Provide data sharding rules and reduce the impact of data sharding rules on the database

 

③ Reduce the number of connections between databases and clients

 

④ Read/write splitting route

 

(3) Use HiveDB to split and integrate data.

 

3. mysql read/write splitting

The read/write splitting architecture uses the database replication technology (see chapter 13) to distribute read and write data on different processing nodes to improve availability and scalability. The primary database provides write operations and read operations from the database. In many systems, read operations are more important. When the primary database performs write operations, data must be synchronized to the slave database to effectively ensure database integrity. Mysql also has its own data synchronization technology. Mysql uses binary logs to copy data. After the master database is synchronized to the slave database, the slave database is generally composed of multiple databases to reduce the pressure. Read operations should be distributed to different servers according to the server pressure, rather than simply random allocation. Mysql provides mysql proxy for read/write splitting.

 

Currently, mysql read/write splitting is commonly divided into the following two types.

 

① Internal implementation based on program code

 

Routing Classification Based on select and insert in the Code. This type of method is also the most widely used in the current production environment.

 

② Implemented based on the intermediate proxy Layer

 

The proxy is located between the client and the server. After receiving the request from the client, the proxy server forwards the request to the backend database. Is the structure of ebay read/write splitting. By using share plex, data is copied to other data nodes in near real time, the database status is checked through a specific module, and load balancing and read/write splitting are performed, the system availability is greatly improved.

 

4. mysql Cluster

The Mysql cluster technology provides the redundancy feature for mysql data in a distributed system, enhancing the security, so that the failure of a single mysql server does not have a huge negative effect on the system, and the system stability is guaranteed.

Mysql cluster adopts the shared-nothing (no sharing) architecture. Mysql custer mainly uses the NDB storage engine. The NDB storage engine is a memory-based storage engine that requires that all data be loaded into the memory. Data is automatically distributed across different storage nodes in the cluster. Each storage node stores only one fragment of the complete data ). At the same time, you can set the same data to be stored on multiple storage nodes to ensure that data is not lost due to spof.

Mysql cluster requires a group of computers, and the roles of each computer may be different. Mysql cluster can be divided into three types by node type: Management Nodes (manage other nodes), data nodes (store data in the cluster, there can be multiple), and mysql nodes (store table structure, can have multiple ). A computer in the Cluster can be a set of two or three types of nodes. These three nodes are logically divided, so they do not have a one-to-one relationship with physical computers. Multiple nodes can be distributed across different geographic locations. Therefore, it is also a solution for implementing distributed databases.

The emergence of the Mysql cluster effectively realizes database load balancing, reduces the pressure on the data center nodes and Big Data Processing. When the database center node fails, the cluster will switch to other backup nodes using certain policies, effectively blocking fault issues. Failure of a single node will not affect the external service of the entire database. In addition, through the database cluster architecture, data synchronization and redundancy are performed between master and slave databases at all times. The database is multi-point and distributed, and database data backup is well completed, this avoids data loss.