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

This article is to share with you about the use of MHA in MySQL. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Overview

MHA is a set of MySQL failover scheme written by Perl by a Japanese MySQL cow to ensure the high availability of the database system. During the downtime (usually within 10-30 seconds), complete the failover and deploy MHA, which can avoid the problem of master-slave consistency, save the cost of purchasing a new server, does not affect server performance, is easy to install, and does not change the existing deployment.

Online switching is also supported. It takes only a short time (within 0.5-2 seconds) to switch from the current running master to a new master. At this time, only write operations are blocked, which does not affect the read operation, and is convenient for host hardware maintenance.

On systems with high availability and data consistency requirements, MHA provides useful functionality to meet maintenance needs almost without interruption.

Advantages

1 master automatic monitoring and failover

"in an existing master-slave replication environment, MHA can monitor master host failures and automatically fail over."

Even if some slave do not accept the new relay log events,MHA, they will automatically identify the differential relay log events from the latest slave and apply the differential event to other slaves. So all slave are consistent. MHA second-level failover (9-12 seconds detected host failure, optional 7 seconds power off host to avoid brain fissure, then apply differential relay logs, registration to a new master, usually takes 10-30 seconds, that is, total downtime). In addition, a slave can be configured in the configuration file to become master first. Because MHA fixes consistency between slave, dba does not have to deal with consistency issues.

When the new master is migrated, the other slave is restored in parallel. Even if there are thousands of slave, the recovery time of master will not be affected, and the slave will be completed quickly.

DeNA uses MHA in 150 + master-slave environments. When one of the master crashes, MHA completes the failover in 4 seconds, which is not possible for the active / passive clustering solution.

2 Interactive (manual) master failover

MHA can be used only for failover, without monitoring master,MHA only as a failover interaction.

3 non-interactive failover

Non-interactive failover is also provided (not monitoring master, automatic failover). This feature is useful, especially if you have installed other software to monitor master. For example, master failures and vip takeover are monitored with Pacemaker (Heartbeat), and MHA failover and slave escalation are used.

4 switch master to different hosts online

In many cases, it is necessary to transfer the master to another host (such as replacing the raid controller, improving the master machine hardware, and so on). This is not a master crash, but planned maintenance must be done. Planned maintenance leads to downtime and must be restored as soon as possible. Fast master switching and elegant blocking writes are required, and MHA provides this way. Elegant master switching, blocking write operations within 0. 5-2 seconds. In many cases, a 0.5-2 second downtime is acceptable, even if it is not in the planned maintenance window. This means that when you need to change faster machines and upgrade higher versions, dba can easily take action.

5 master crash will not cause master-slave data inconsistency

After master crash, MHA automatically recognizes the difference of relay logevents between slave, then applies different slave, and finally all slave are synchronized. When used together through semi-synchronization, there is almost no data loss.

Other highly available options

6 MHA deployment does not affect the current environment settings

One of the most important design concepts of MHA is to be as easy to use as possible. With the master / slave environment above 5.0 +, other HA solutions need to change the mysql deployment settings. MHA will not let dba do these deployment configurations. Both synchronous and semi-synchronous environments can be used. Start / stop / upgrade / downgrade / install / uninstall MHA without changing the mysql master / slave (such as start / stop).

When you need to upgrade MHA to a new version, you don't need to stop mysql, just update the HMA version, and then restart MHAmanger.

MHA support includes 5. 0 MHA, 5 and 1. 5. (it should also support 5. 6, which was not updated for version 5. 6 when translating documents.) Some HA solutions require a specific version of mysql (such as mysqlcluster,mysql with global transaction id, etc.), and you may not want to migrate applications just for MasterHA. In many cases, companies have deployed many traditional mysql applications, and developers or dba don't want to spend too much time migrating to different storage engines or new features (newer bleeding edge distributions doesn't know if this should be translated).

7 No increase in server fees

MHA contains MHA Manager and MHA node. MHA node runs on each mysql server, and Manager can deploy a separate machine to monitor more than 100 master. The total number of servers will not increase much. It is important to note that Manager can also run on a machine in slaves.

8 No effect on performance

When the monitoring master,MHA sends ping packets for only a few seconds (default 3 seconds), no large queries are sent. Master-slave replication performance is not affected

9 applicable to any storage engine

Mysql is not only suitable for transaction-safe innodb engines, but also for all applicable engines, MHA. Even if you use the mysiam engine of the legacy environment, you can use MHA without migration.

Compared with other HA schemes

Doing everything manually Mysql replication is synchronous or semi-synchronous. When master crashes, it is quite possible that some slave have not accepted the latest relay log events, which means that each slave is in a different state from each other. The problem of artificial repair of consistency is no longer trivial. If there is no consistency problem, the master and slave may not start (for example, duplicate key error). It is unusual to restart master-slave replication for more than an hour.

Single master and single slave in the case of a single master and slave, some slave lags behind other slave will not happen. One of the master crashes, can easily transfer the application to a new master, provide external services, fault migration is very simple.

Master, one candidate master, and multiple slaves dual-master multi-slave architecture is also very common. The primary master is down and the standby master will take over from the primary master. In some cases it is configured as a multi-master architecture.

M (RW)-M2 (R) M (RW), promoted from M2

| | |

+-(master crash)-- > +-Xmuri

S (R) S2 (R) S (?) S (?)

(Fromwhich position should S restart replication?)

But this is not a master failover scenario. The current master is down, and the remaining slave may not accept all relay log events. Fixing data consistency is still a problem.

This architecture is widely used, but not everyone can deeply understand the above problems. The current master is down, slave becomes inconsistent or slave cannot copy data from the new master.

Maybe double master, one of the master is read-only, and each master has at least one slave that may solve the problem.

M (RW)-M2 (R)

| | |

S (R) S2 (R)

Pacemaker + DRBD Pecemaker (Heartbeat) + DRBD+Mysql is a general scheme. But this plan also has the following problems

1 the problem of cost, especially running a large number of master-slave environment. Pecemaker+DRBD is an active / passive solution, so it requires a passive server that does not provide any application services. Basically, you need four mysql servers, one active master,one passive master,two slaves.

2 downtime (downtime). Pacemaker+DRBD is the master / slave cluster. The primary master is down and the standby master is enabled. This can take a long time, especially without using innodb plugin. Even with innodb plugin, it is unusual to start accepting connections on a backup master in a few minutes. In addition, because the data / file cache on the standby master is empty, recovery time, warm-up (populating data to data buffer pool) takes a lot of time to ignore. In practice, one or more slave is required to provide sufficient reading services. During warm-up time, empty caching results in write performance degradation

(3) write problem decline or consistency problem. In order for the active / passive cluster to really work, the transaction log (binary log and innodb log) must be refreshed after each commit (commit), that is, innodb-flush-log-at-trx-commit=1,sync-binlog=1 must be set. Setting sync-binlog=1 degrades write performance because the fsync () function is serialized (sync-binlog=1,group commit invalidation). In most cases, sync-binlog=1. Net is not set. If sync-binlog=1 is not set, the active master crash, the new master (previously passive server) may lose some of the binary log events that has been sent to the slave. If master dies, slave A receives mysqld-bin.000123, location 1500. The location where the binlog data is flushed to the hard disk is 1000, so the new master data can only be 1000 of the mysqld-bin.000123, and then create a new binary log mysqld-bin.000124 at startup. If this happens, slave A cannot continue to copy because the new master does not have mysqld-bin.000123 location 1500.

(4) complex. For most people, installing / initializing pacemake and DRBD is not an easy task. Initializing DRBD requires recreating the system partition is also not easy compared to other cases. Dba is required to have sufficient skills in the DRBD and linux kernel layers. If dba executes an error command (such as executing drbdadm-overwrite-data-of-peer primary on the passive node), the active data will be corrupted. The important thing is that if there is a problem with the IO layer of the hard disk, it is not easy for most dba to deal with this problem.

MySQL Cluster Mysql cluster is a real high-availability solution, but you have to use the NDB storage engine. If you use innodb, you will not be able to take advantage of mysql cluster clustering.

Semi-Synchronous Replication semi-synchronous replication greatly reduces the risk that binlog event exists only on crashed master. This is very useful to avoid data loss. However, semi-synchronization can not solve all consistency problems, only one (not all) slave can accept the binlog events of the master-side commit, and the other slave may not accept all the binlog events. Cannot apply different binlog events from new slave to other slave, nor can it guarantee mutual consistency

GlobalTransaction ID GlobalTransaction ID aims to achieve the same goal as MHA, but it covers more. MHA is only a two-level replication, but global transaction id covers any level of replication environment, and dba can cover the third level even if the second-level replication fails. Check Google'sglobal transaction id project for details .

Thank you for reading! This is the end of this article on "what is the use of MHA in MySQL?". I hope the above content can be of some help to you, so that you can 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.