Research on high Availability scheme of MySQL database

MySQL as the most critical application data storage Center, how to ensure the reliability and continuity of MySQL service, we have to carefully consider a problem. When Master goes down, how do we ensure that the data is not lost as much as possible, and how to ensure that the master outage is quickly learned and the corresponding failover process needs to be carefully considered and planned.

To ensure that MySQL data is not lost, replication is a good solution, and MySQL provides a powerful replication mechanism, replication can greatly improve the data security, asynchronous replication means that SQL read and write performance is not affected too much. In a large number of enterprises in the long-term use and practice, has formed a number of reliable MySQL high-availability programs, such as: Keepalived+replication,mha, MMM, heartbeat+ shared storage, MySQL cluster and so on.

Three ways to work with HA:

(1) Main preparation method

Working principle: The host work, the standby machine is in preparation condition; When the host is down, you can configure the standby machine to take over the host of all the work, to the host after the return to normal, and then according to the user set whether the service switch to the host running, MySQL comes with the replication is such a way of working.

(2) Dual-machine Mutual preparation method

How it works: two hosts concurrently running service work and mutual detection service availability, when any one host down, another host immediately take over all its work, to ensure business continuity and reliability, the dual-machine hot standby requires third-party HA software support, such as Keepalived,heartbeat, and commercial software such as Roseha.

(3) How the cluster works

How it works: multiple hosts working together, running the same or a few services, each service defines one or more alternate hosts or load averaging, when a host failure, the service running on it can be taken over by other hosts, any host in the cluster downtime does not affect the continuity of the business. Mysql cluster is the way the cluster works.

Here are a few common MySQL high-availability scenarios:

programme One:

Mysql+replication

Scenario Overview:

MySQL replication logs all changes (updates, deletions, and so on) to the database based on the primary server in the binary log. Therefore, to replicate, you must enable the binary log on the primary server each receive a binary log from the primary server and save it to a local file, that is, the trunk log. Reads the relay log from the server SQL thread and executes the updates contained in the log, thus keeping the local data consistent with the primary server.

Scenario Topology:

Advantages:

Low cost, affordable, later maintenance convenience, the whole system architecture is simple, failure rate is low, easy to achieve read and write separation.

Disadvantages:

Can not realize automatic fault transfer, need to manually interfere with master-slave switchover, change the application layer database IP address.

Scenario Two:

mysql+keepalived/heartbeat+ shared storage

Scenario Overview:

This program is a typical dual-machine hot standby architecture, using high-reliability ha dual-machine hot standby software to ensure the stability and continuity of the database service. Under normal circumstances, two MySQL host one out of active state, one out of standby state, HA software to maintain a VIP to provide external access, when the active host problem goes down, the system will automatically switch to the standby to continue to provide services, The entire process only takes a few seconds to a few 10 seconds to complete, when the MySQL host failure repair, service can automatically failback. This scenario requires shared storage support, and all MySQL data is stored on shared storage.

Scenario Topology:

Advantages :

Simple maintenance, security, high stability, the failure system will automatically switch, using VIP failover Application layer without perception.

Disadvantages :

Need to have a shared storage device, high cost, dual-machine hot standby does not support read-write separation.

Programme III:

Mysql+master-master-replication+lvs/haproxy/nginx

Program Introduction:
This program is based on MySQL replication technology, using Master-master replication dual-master replication mode, two services

are in active state, the front-end LVS (or Haproxy or nginx) proxies and route access requests,

Write requests (application-level read-write separation) are distributed to Server1, which distributes the read to server2[or load-balanced to SERVER1 and

Server2],server1 and Server2 are mainly from each other, when any one of the failure can be two-way failover, fault recovery can be self-

Back cut.

Scenario topology:

Advantages:
Second-level fault automatic switching, automatic failback, the use of fixed IP failover application layer without awareness, can achieve read and write separation.
Disadvantages:
The architecture is slightly more complex and has some maintenance difficulties later, including the disadvantages of other master-master replication architectures.

Programme IV:

Mysql+replication+mha

Program Introduction:
MHA is a Japanese MySQL Daniel written in Perl a set of MySQL failover scheme to ensure that the database system is highly available.
During downtime (typically within 10-30 seconds), complete failover, deploy MHA to avoid master-slave consistency issues,
Server performance, do not change existing deployments, Support online switching, switch from the current running master to a new master,
It only takes a short time, only blocking the write operation, does not affect the read operation, convenient host hardware maintenance. In the high availability and
According to the conformance requirements of the system, MHA provides useful functionality when the master crash, MHA automatically identify slave between
Relay log events are different and then applied to different slave, eventually all slave are synchronized.

Scenario topology:

Advantages:
Use the VIP to provide access requests, with the best data consistency, master crash does not cause other inconsistencies from the library, the fault automatically
Fast switching, the scheme is more mature, can realize a master multi-slave, support read and write separation.
Disadvantages:
Need 3 units and above the server, the failure to cut the transfer of the major program automatically exit, to troubleshoot the problem after manually added to the HA cluster restart MHA, maintenance difficult.

Research on high Availability scheme of MySQL database