Analysis and comparison of five kinds of MySQL database reliability schemes

This article mainly from the basic situation, cost, advantages and disadvantages and applications, and other aspects of the 5 MySQL reliability scheme for a detailed analysis and comparison, in addition, the MySQL database for the development and management of a certain reference role. For more information, please refer to the following:

　　1. MySQL Clustering (Ndb-cluster stogare)

　　Brief introduction:

The high reliability solution provided by the MySQL company as a storage engine is a transaction-safe, real-time replication of data that can be used in situations where high reliability and load balancing are required. This scheme requires at least three node servers to achieve better results.

　　Cost:

The node server has a large demand for RAM, which is linearly proportional to the size of the database;

It is best to use Gigabit Ethernet network;

You also need to use the expensive SCI card provided by the dolphin company.

　　Advantages:

Can be used in load balancing occasions;

Can be used in high reliability occasions;

High scalability;

Real database redundancy;

Easy to maintain.

　　Disadvantages:

As the database becomes larger, the need for RAM becomes larger and the cost is high;

　　Speed:

is nearly 10 times times slower than a typical stand-alone server (without Gigabit Ethernet, no SCI card, and storage engine-related restrictions).

　　Application occasions:

Redundancy, high reliability, load balancing

　　2. Mysql/gfs-gnbd/ha (active/passive)

　　Brief introduction:

If multiple MySQL servers use shared hard disks as data stores, what about this scenario?

GFS/GNBD can provide the required shared hard disk.

GFS is a secure file system for transactions. At the same time you can have a MySQL using shared data.

　　Cost:

The cost of up to n high-performance servers, one of which is active and the other as backup servers.

　　Advantages:

High reliability

Some degree of redundancy

Scaling with high reliability

　　Disadvantages:

No load Balancing

No guarantee of redundancy

Unable to scale the write operation

　　Speed

Twice times that of a single server. Good support for read operation.

　　Application Occasions

Requires highly reliable, read-intensive applications

　　3. Mysql/drbd/ha (active/passive)

Brief introduction:

If multiple MySQL servers use shared hard disks as data stores, what about this scenario?

DRBD can provide such a shared hard disk. DRBD can be set up for transaction security.

At the same time you can have a MySQL using shared data.

　　Cost:

The cost of up to n high-performance servers, one of which is active, while others serve as backup servers.

　　Advantages:

High reliability;

A certain degree of redundancy;

Scalable in terms of high reliability.

　　Disadvantages:

No load Balancing

No guarantee of redundancy

No scalability on write load

　　Speed:

Equivalent to a separate server in terms of reading and writing

　　Application Occasions

Requires high reliability, read-intensive applications

　　4. mysql Write master/multiple mysql Read slaves (active/active)

　　Brief introduction:

Consider the situation of different read and write DB database connections. You can use one primary server for write operations, and N for read operations from the server.

　　Cost:

Up to 1 high-performance write servers, n the cost of a read server

　　Advantages:

High reliability of read operation;

Load balance of read operation;

Scalable in the load balancing of read operations.

　　Disadvantages:

High reliability without write operation;

Load balancing without write operation;

No scalability in write operation;

　　Speed:

Same as individual server, good support in read operation

　　Application Occasions

Read-and-operate-intensive applications that require high reliability and load balancing.

　　5. Standalone MySQL Servers (functionally separated) (Active)

Multi-function separate server, no high reliability, load balancing ability, obvious shortcomings too much, not considered.

　　Summarize:

The HA scheme recommended by the MySQL official website is a combination of DRBD (scenario 3 in this article) and Replication (scenario 4 in this article). If coupled with the Linux heartbeat can also achieve auto-failover function, in this case, we will find that down machine time will be greatly reduced.