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

Plan 1: dual-computer high availability scheme

1. Database architecture diagram

two。 Characteristics

One machine An is used as the read-write library, the other B as the backup library; after the failure of the A library, the B library is used as the read-write library; after the A library is restored, An is used as the backup library.

3. Development description

In this case, the database IP address in the data source configuration can use a virtual IP address. The virtual IP address is configured by the keepalive on two database machines and detects the heartbeat of each other. When one of them fails, the virtual IP address automatically drifts to another normal library.

The active and standby configuration, troubleshooting and data completion of the database need to be maintained by DBA and operation and maintenance personnel. The program code or configuration does not need to be modified.

For specific configuration, please refer to the following materials:

Http://lizhenliang.blog.51cto.com/7876557/1362313

Http://database.51cto.com/art/201012/237204.htm

Http://gaoke.iteye.com/blog/2283890

4. Adapt to the scene

In scenarios where both reading and writing are not high (single table data is less than 5 million), dual computers are highly available.

5. Advantages and disadvantages

The advantage is that when a machine fails, it can be switched automatically; the disadvantage is that only one library is working, read and write are not separated, and concurrency is limited.

Scheme 2: master-slave structure scheme

1. Database architecture diagram

two。 Characteristics

One machine An is used as the write library and the other B as the read library. After the failure of the A library, the B library acts as the read and write library. After A repair, the B library is the write library and the A library is the read library.

3. Development description

This scheme is implemented with the help of database middleware Mycat. The datahost configuration of Mycat is as follows (pay attention to the settings of balance and writetype)

Select user ()

In the development of the project, it is necessary to configure the Mycat data source and realize the data operation to the Mycat data source. Database An and database B should be the master and slave of each other. The master configuration, troubleshooting and data completion of the database still need to be maintained by DBA and operation and maintenance personnel.

4. Adapt to the scene

Both read and write are not very high scenarios (single table data is less than 10 million) and are highly available. It is much higher than the plan.

5. Advantages and disadvantages

The advantage is that when a machine fails, it can be switched automatically; read and write are separated, and concurrency has been greatly improved. The disadvantage is that a Mycat node is introduced, and at least two Mycat are required to be highly available. The conventional solution is to introduce haproxy and keepalive to cluster mycat.

The scheme of high read and low write concurrency and low data volume

Plan 3: one master, multi-slave + read-write separation

1. Database architecture diagram

two。 Characteristics

A master write library A has multiple slave libraries. When the master library A fails, promote the slave library B as the master library, and modify the C and D libraries to be the slave libraries of B. After the A fault is repaired, it is used as the slave library of B.

3. Development description

In the development of the project, you need to use Mycat as the middleware to configure the master and slave libraries. The core configuration is as follows:

Select user ()

, used for reading, hostM1 down, and automatically switch to master-- >

For reading-- >

For reading-- >

After the failure of main library A, Mycat will automatically upgrade from B to write library. For C and D slave libraries, the master library can be automatically modified to B through MHA and other tools. Then the destination of automatic switching is realized.

MHA Manager can be deployed on a separate machine to manage multiple master-slave clusters, or it can be deployed on a slave node. MHA Node runs on each MySQL server, and MHA Manager regularly detects the master nodes in the cluster. When the master fails, it automatically promotes the slave of the latest data to the new master, and then redirects all other slave to the new master. The entire failover process is completely transparent to the application.

For more information about MHA, please refer to:

Http://www.cnblogs.com/gomysql/p/3675429.html

4. Adapt to the scene

This architecture is suitable for scenarios where the write concurrency is small, but the read concurrency is large.

5. Advantages and disadvantages

Due to the configuration of multiple read nodes, the ability of read concurrency has been qualitatively improved. In theory, there can be multiple read nodes, which can load a high level of read concurrency. Of course, Mycat still needs to design highly available solutions.

A scheme with high read and write concurrency and low data volume

Option 4: MariaDB Galera Cluster scheme

1. Database architecture diagram

two。 Characteristics

Multiple databases can be written and read at the same time under the action of load balancing; data synchronization is carried out between each database by the method of Galera Replication, that is, in theory, the data of each database is completely consistent.

3. Development description

When the database reads and writes, only need to modify the database read and write IP to keepalive virtual node; database configuration is relatively complex, need to introduce haproxy, keepalive, Galaera and other plug-ins and configuration.

4. Applicable scenario

This scheme is suitable for scenarios where the concurrency of reading and writing is large and the amount of data is not very large.

5. Advantages and disadvantages

Advantages: 1) can be read on any node; 2) automatically eliminate fault nodes; 3) automatically add new nodes; 4) truly parallel replication, based on row level 5) client connection is consistent with the experience of the operation order database. 6) synchronous replication, so it has high performance and reliability.

Disadvantages: 1) DELETE operation does not support tables without a primary key, tables without a primary key will be different in different node order. 2) when dealing with transactions, a coordination authentication procedure will be run to ensure the global consistency of transactions. If the transaction runs for a long time, it will lock all related tables in the node, causing inserts to get stuck (this is the case with a single table insert). 2) the write throughput of the entire cluster is limited by the weakest node, and if one node becomes slow, the whole cluster will be slow. In order to achieve stable and high performance requirements, all nodes should use unified hardware. 3) if there is a problem with the DDL statement, it will destroy the cluster. It is recommended to disable it. 4) this scheme is only supported by Mysql database 5.7.6 and later.

Scheme with high read and write concurrency and high data volume

Scheme 5 database middleware

1. Database architecture diagram

two。 Characteristics

Using Mycat for sharding storage can solve the problem of write load balancing and excessive data volume, and the configuration of multiple read slave libraries for each shard can reduce the read pressure on a single library.

3. Development description

In this case, Haproxy, keepalive and mycat clusters need to be configured, and a master and multi-slave cluster needs to be configured on each shard. For the complete configuration on each shard, please refer to solution 3, which can be simply understood as a shard structure. Therefore, the amount of configuration and maintenance is relatively large.

4. Applicable scenario

A scenario in which both reading and writing are very concurrent and the amount of data is very large.

5. Advantages and disadvantages

Advantages: the ultimate solution to high concurrency and high data volume.

Disadvantages: configuration and maintenance are troublesome, requiring large hardware and software equipment resources.

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.