Share various high-availability technologies in MySQL (from Kang Daniel)

Images and information from MySQL Daniel Kang teacher (MySQL Technology insider author)

Kang: Technology manager of NetEase Hangzhou Research Institute leading INNOSQL Development

MySQL high availability comparison of various technologies

The reliability of the database refers to the reliable data

A database can be used to refer to the availability of database services

Reliable data : such as ICBC, data cannot be lost

The service is available: The server cannot be crashed

Flexible use of MySQL's various high-availability technologies to meet high-availability requirements at various levels below

To reach 99.9%: use MySQL replication technology

To reach 99.99%: using MySQL NDB clustering and virtualization technology

To reach 99.999%: Geo-replication and NDB cluster technology using the shared-nothing architecture

What is Gluster geo-replication?

Gluster Geo-replication (abbreviated as Geo-replication) is a kind of remote disaster preparation technology,

It is primarily used to back up one storage in a cluster, nearly instantaneously (near real-time) through a public network (WAN) to a remote machine room

Various high-availability levels allow for downtime

DRBD: RAID1 of network disks

Scenario One: MySQL master-slave Replication (single copper)

Voting mechanism is more complex

MySQL itself does not offer a solution for replication failover, automatic switching relies on MHA scripts

Can have more than one from the library, from the library can do reports and backup

Scenario Two: Dual Master (single copper), failover simple than single master

Similarly, auto-switching requires MMM script

The disadvantage is that one of the Lord hangs up, and the slave below him also hangs off

Scenario Three: Dual-master SAN storage (single copper)

This architecture is the same as scenario two, except that there is no need to synchronize data between the two master because they are using a shared disk

This scheme is rich , no matter which master hangs up will not cause the other slave hangs, but the SAN storage is dead expensive.

Like the telecommunications industry China Unicom These companies are useful to

One of the Lord hangs up, and the slave won't hang down.

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

Scenario Four: DRBD dual master with DRBD (single copper)

Structure is the same as the scenario three, the only difference is not to use the San network storage, but instead use Local disk

Performance impacts due to real-time replication of disk data

People refer to DRBD as a "stud san"

POOR man ' S san: the Poor San

Programme five: NDB CLUSTER

Domestic use of NDB clusters of companies very few, it seems that some banks useful

NDB clusters do not need to rely on third-party components, all using official components, to ensure data consistency

A data node hangs up and other data nodes can still provide services

The management node needs to be redundant to prevent hanging out

The downside is that management and configuration are complex, and some SQL statements such as join statements need to avoid

Scenario VI: Third-party tungsten software

Written in Java, not MySQL built-in

It's also a MySQL database copy, but he didn't do it with MySQL built-in components.

Not only supports MySQL database replication but also supports replication of heterogeneous databases, and supports better replication of heterogeneous databases, such as MySQL replication to Oracle

Scheme VII: the Innosql of NetEase

Mirrored high-security mode similar to SQL Server

High Safety mode (i.e. synchronous mode) No witness server

Database in principle transaction, need to get mirror confirmation immediately to complete . In this case, the data for mirror and principle are synchronized.

However, performance may be affected because all transactions require mirror confirmation.

Difference: Innosql slave can be read, mirrored slave (from library) unreadable

Ensure data is not lost and data is highly reliable

mysql5.7 started supporting this mode

Summarize

Each scenario has different characteristics, and the configuration and application scenarios are different

Some tend to be low-cost, some tend to high-cost, some are biased to the reliability of the data, some are biased to the availability of the database

Anyway, each program has its pros and cons, and DBAs should choose a high-availability solution that suits their business situation in the context of their company's business.

More resources:

Read/write Separation: Amoeba

UBUNTU10 MySQL Build Amoeba series (article index)

Cluster technology: A discussion on the technology of database cluster

Gluster Geo-replication Working principle

If there is a wrong place, welcome everyone to shoot brick O (∩_∩) o