Database High Availability Scenarios

Database High Availability Scenarios

Low-read low-write concurrency, low data volume scheme

Scenario One: Dual-machine high-availability solutions

1. Database Schema diagram

2. Features

A machine A as a read-write library, another B as a backup library, a library after the failure of the B library as a read-write library, a library after the recovery of a as a standby library.

3. Development Notes

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

The primary and standby configuration, troubleshooting, and data completion of the database require the maintenance of DBAs and operations personnel. The program code or configuration does not need to be modified.

Specific configuration can be reference:

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

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

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

4. Adapting to the scene

Both reading and writing are not high (single-table data is less than 5 million) and the dual-machine is highly available.

5. Pros and cons

The advantage is that a machine failure can automatically switch; The disadvantage is that only one library is working, read and write are not separated, and concurrency is limited.

Scenario Two: master-slave structure scheme

1. Database Schema diagram

2. Features

A machine A as a write library, another B as a read library, a library after the failure of the B library to read and write, a repair, b library for the Write library, a library for reading library.

3. Development Notes

The implementation of this scheme, the use of database middleware mycat to achieve, Mycat datahost configuration is as follows (note balance and writetype settings)

<datahost name= "Localhost1" maxcon= "$" mincon= "balance=" 1 "writetype=" 0 "dbtype=" MySQL "dbdriver=" native " Switchtype= "1" slavethreshold= ">"

<!--master for writing---

<writehost host= "hostM1" url= "192.168.1.135:3306" user= "root" password= "123"/>

<!--main 2, for reading, hostM1 down, automatic switch-based, read and write can be-

<writehost host= "hostM2" url= "192.168.1.136:3306" user= "root" password= "123"/>

</dataHost>

In project development, you configure the Mycat data source and implement data operations on the MYCAT data source. Database A and database B should be primary from each other. The master configuration, troubleshooting, and data completion of the database still require DBA and OPS personnel to maintain.

4. Adapting to the scene

Read and write are not very high scenes (single table data less than 10 million), high availability. is much higher than scenario one concurrency.

5. Pros and cons

The advantage is that a machine failure can automatically switch, read and write separation, concurrency has a great increase. The disadvantage is the introduction of a Mycat node, which requires at least two mycat to be introduced for high availability. The general solution is to introduce Haproxy and keepalive to Mycat as a cluster.

High-read low-write concurrency, low data volume scheme

Scenario Three: A master multiple from + read and write separation

1. Database Schema diagram

2. Features

A master write library a multiple from the library, when the main library a fails, promote from library B as the main write library, while modifying C, D library for b from the library. A fault repair, as b from the library.

3. Development Notes

In the project development, we need to use Mycat as the middleware to configure the main library and the slave library, the core configuration is as follows:

<datahost name= "Localhost1" maxcon= "$" mincon= "balance=" 1 "writetype=" 0 "dbtype=" MySQL "dbdriver=" native " Switchtype= "1" slavethreshold= ">"

<!--master A for writing---

<writehost host= "hostM1" url= "192.168.1.135:3306" user= "root" password= "123"/>

<!-from B, for reading, hostM1 down, auto switch Main---

<writehost host= "hostM2" url= "192.168.1.136:3306" user= "root" password= "123456"/>

<!-from C, for reading--

<writehost host= "hostM3" url= "192.168.1.137:3306" user= "root" password= "123"/>

<!-from D, for reading--

<writehost host= "hostM4" url= "192.168.1.138:3306" user= "root" password= "123"/>

</dataHost>

After the failure of the main library A, the Mycat will automatically promote from B to the write library. C, d from the library, you can automatically modify its main library to B through tools such as MHA. In order to achieve automatic switching of the target.

MHA Manager can be deployed individually on a separate machine to manage multiple master-slave clusters or on a single slave node. MHA node runs on each MySQL server, MHA Manager periodically probes the master node in the cluster, and when master fails, it automatically promotes the slave of the latest data to the new master. All other slave are then re-directed to the new master. The entire failover process is completely transparent to the application.

MHA related knowledge, please refer to:

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

4. Adapting to the scene

This architecture is suitable for writing very large scenes with small concurrency, but reading concurrency.

5. Pros and cons

Due to the configuration of multiple read nodes, the ability to read concurrency has improved. Theoretically, read nodes can be multiple and can load very high levels of read concurrency. Of course, Mycat still needs to design high-availability solutions.

High read and write concurrency, low data volume scheme

Programme IV: MariaDB galera Cluster solution

1. Database Schema diagram

2. Features

Multiple databases, under load balancing, can write and read simultaneously, the data is synchronized by Galera replication between each library, that is, each library theoretically, the data is exactly the same.

3. Development Notes

When the database reads and writes, only needs to modify the database read/write IP to keepalive the virtual node, the database configuration aspect is relatively complex, needs to introduce Haproxy, KeepAlive, Galaera and so on various plug-ins and the configuration.

4. Applicable scenarios

This scheme is suitable for scenarios with large and large data volumes.

5. Points of Merit

Advantages: 1) can be read on any node 2) automatically reject the fault node 3) automatically join the new node 4) True parallel replication, based on row level 5) The client connection is consistent with the experience of the operational single database. 6) synchronous replication, so it has high performance and reliability.

Cons: 1) The delete operation does not support tables that do not have a primary key, a table with no primary key will be different in the order of different nodes 2) will run a coordinated authentication program to ensure the global consistency of the transaction, if the transaction is long running, it will lock all the related tables in the node. Causes the Insert to snap (this is the same as single table insertion). 2) The write throughput of the entire cluster is limited by the weakest node, and if one node becomes slow, the entire cluster will be slow. For stable high-performance requirements, all nodes should use unified hardware. 3) If there is a problem with the DDL statement that destroys the cluster, it is recommended to disable. 4) MySQL database 5.7.6 and later versions only support this option.

High read and write concurrency, high data volume scheme

Scenario Five database Middleware

1. Database Schema diagram

2. Features

The use of Mycat for shard storage can solve the problem of write load balancing and excessive data volume, and each shard can reduce the reading pressure of a single library by configuring multiple read from libraries.

3. Development Notes

In this case, the Haproxy, keepalive, and mycat clusters need to be configured, and each shard needs to be configured with a master-slave cluster. Complete configuration on each shard, refer to scenario three, you can simply understand the scenario three as a shard structure. Therefore, the amount of configuration and maintenance is relatively large.

4. Applicable scenarios

A scenario in which both read and write concurrency are large and data volumes are very large.

5. Pros and cons

Pros: The ultimate solution to high concurrency and high data volumes.

Disadvantage: Configuration and maintenance are more troublesome, the need for hardware and software equipment, large resources.

Database High Availability Scenarios