MySQL database HA solution

I have studied the high availability of databases and need to use Database Synchronization and backup. Below are some of my studies on MySQL HA.

 

 

According to the HA solution provided in MySQL 5.0 Reference Manual, it mainly consists of the following types:

 

 

Requirements	MySQL Replication	MySQL Replication + Heartbeat	MySQL Heartbeat + DRBD	MySQL Cluster	MySQL + memcached
Availability
Automatic IP failover	No	Yes	Yes	No	No
Automatic database failover	No	No	Yes	Yes	No
Typical failover time	User/script-dependent	Varies	<30 seconds	<3 seconds	App dependent
Automatic resynchronization of data	No	No	Yes	Yes	No
Geographic redundancy support	Yes	Yes	Yes, when combined with MySQL Replication	Yes, when combined with MySQL Replication	No
Scalability
Built-in load balancing	No	No	No	Yes	Yes
Supports Read-intensive applications	Yes	Yes	Yes, when combined with MySQL Replication	Yes	Yes
Supports Write-intensive applications	No	No	Yes	Yes	No
Maximum number of nodes per group	One master, multiple slaves	One master, multiple slaves	One active (primary), one passive (secondary) node	255	Unlimited
Maximum number of slaves	Unlimited (reads only)	Unlimited (reads only)	One (failover only)	Unlimited (reads only)	Unlimited

 

The Research on the scheme is as follows, with many characters.

 

I,MySQL Replication

The single MySQL synchronization function cannot automatically switch IP addresses. The functions listed in this table are the one-way synchronization mode of MySQL Replication, which needs to be improved to two-way synchronization, you can achieve two-way data synchronization, re-synchronization, and database switching.

II,MySQL Replication + Heartbeat

For the second solution, heartbeat is introduced, so IP address switching can be implemented. heartbeat only applies to switching between host and network faults and cannot monitor database faults. Therefore, additional tools and configurations are required, the working mechanism of MySQL Replication is asynchronous communication, which may affect the synchronization data consistency.

III,MySQL + Heartbeat + DRBD

DRBD is the Distributed Replicated Block Device, which is a virtual Block Device tool developed based on the kernel, the software virtualizes a block device on an actual block device or partition and mounts it to the system. The two hosts synchronize data on the block device through configuration, two-way real-time synchronization can be implemented, but only one host is active at the same time. virtual block devices on the slave server cannot be mounted, and thus the MySQL service on the slave server cannot be started, during the switchover, you need to control the startup sequence from the server, switch DRBD in sequence, Mount virtual block devices, and start MySQL. This delay is long during debugging. In addition, DRBD is based on the kernel. During application debugging, many times the server kernel issues and the host crashes. Therefore, DRBD does not meet the high stability requirements of the server.

IV,MySQL Cluster

The cluster mode is generally applied to Server Load balancer and adopts one-way synchronization. data modification is only performed on one database server, and queries are forwarded to other database servers, at the same time, the automatic switch of IP addresses cannot be completed, so no debugging is performed.

V,MySQL + memcached

This solution uses shared memory to allow multiple MySQL instances to share the same memory buffer. Therefore, backup is not applicable.

VI, Other solutions

1.MySQL Proxy:

You can also use MySQL Proxy to implement IP address switching. The working principle is to add a Proxy device between the MySQL Server and the Client to process data requests through the lua script, you can use lua scripts to implement different functions as needed. However, this method adds another MySQL Proxy server.

2.MyBalanceAndMyReplication

For the synchronization function, in addition to MySQL synchronization and DRBD, two tools, MyBalance and MyReplication, can be implemented. However, these two tools can only perform one-way synchronization and are not suitable for Bidirectional backup, therefore, it cannot meet the requirements.

This article is from "Technical Achievement dream"