Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to compare the various technologies available in MySQL. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

The reliability of the database refers to the reliability of the data; the availability of the database refers to the availability of database services. What is reliable is that the data, such as ICBC, cannot be lost; what is available is that the server cannot be down.

Flexibly use various high availability technologies of MYSQL to meet the high availability requirements of the following levels

To reach 99.9%: use MYSQL replication technology

To reach 99.99%: use MYSQL NDB clustering and virtualization technology

To achieve 99.999%: GEO-REPLICATION and NDB cluster technology using shared-nothing architecture

What is Gluster Geo-replication?

Gluster Geo-replication (geo-replication) is a remote disaster recovery technology, which is mainly used to backup a storage in the cluster to the remote computer room almost instantly (near real-time) through the public network (wan).

Downtime allowed for various high availability levels

DRBD: RAID1 of network disk

Scheme 1: MYSQL master-slave replication (single active user)

The voting mechanism is complicated.

MySQL itself does not provide a solution for replication failover. Automatic switching depends on MHA scripts.

There can be multiple slave libraries, and reports and backups can be made from the library.

Plan 2: double master (single active user), failover is simpler than single master.

Similarly, automatic switching requires a MMM script, but the disadvantage is that a master is dead, and the slave below him is also dead.

Scheme 3: dual-master SAN storage (single active)

This architecture is the same as scenario 2, except that there is no need to synchronize data between the two master, because they use a shared disk, this scheme is a rich scheme, no matter which master dies will not cause other slave to die, but SAN storage is extremely expensive.

Companies like China Unicom in the communications industry are so useful that if a master dies, the slave below will not be hung up.

Note: failover does not warm up, data is not pre-loaded into memory, and storage for a period of time after switching will have a certain performance impact

Scheme 4: DRBD double main DRBD (single active user)

The structure is the same as in scenario 3, except that instead of using SAN network storage, local disk is used. Because disk data is replicated in real time, performance can be affected. People call DRBD "SAN for losers"; POOR MAN'S SAN: SAN for the poor

Option 5: NDB CLUSTER

There are very few domestic companies using NDB clusters, and it seems that some banks are useful. NDB cluster does not need to rely on third-party components, all use official components to ensure data consistency, a data node dies, other data nodes can still provide services, management nodes need to do redundancy to prevent hanging up.

The disadvantage is that administration and configuration are complex, and some SQL statements, such as join statements, need to be avoided.

Plan 6: third-party Tungsten software

Written in java, not built in MYSQL, it is also MYSQL database replication, but it is not done with the built-in components of MYSQL. It not only supports MYSQL database replication and heterogeneous database replication, but also supports heterogeneous database replication, such as MYSQL replication to ORACLE.

Plan 7: NetEase's INNOSQL

Similar to SQLSERVER's mirror high security mode, High Safety mode (that is, synchronous mode) does not have a witness server.

Database transactions in Principle need to be confirmed by mirror immediately before they can be completed. In this case, the data for Mirror and Principle are synchronized.

But because all transactions require mirror confirmation, performance may have an impact.

Difference: innosql's slave can be read, while mirrored slave (slave library) is not readable. To ensure that the data will not be lost, the high reliability of the data.

Mysql5.7 began to support this mode.

Each scheme has different characteristics, configuration and application scenarios are also different, some prefer low-cost, some prefer high-cost, some prefer the reliability of data, and some prefer the availability of the database. Anyway, each scheme has its own advantages and disadvantages. DBA should combine its own business situation to choose a highly available solution that is suitable for its own business situation.

This is the end of the comparison of the various technologies highly available in MySQL. I hope the above content can be helpful to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

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.