Common high-availability MySQL Solutions

MySQL database, as one of the most basic data storage services, has a very important position in the whole system, so it is understandable to ask for high availability. There are many solutions that can implement different SLAs (service level agreements) that ensure that the database server continues to be available in the event of hardware or software failure.

There are two main problems that high performance needs to be solved, namely how to realize data sharing or synchronizing data, the other is how to deal with failover, and the general solution of data sharing is implemented by San (Storage area Network). Data synchronization can be achieved through rsync software or DRBD technology, failover means that when the server crashes or an error occurs, it can automatically switch to another standby server without affecting the operation of the business system on the server. This article focuses on the more mature MySQL high-performance solutions today.

1. Master-Slave Replication Solution

This is a highly available solution provided by MySQL itself, and the data synchronization method uses MySQL replication technology. MySQL replication is a log copy process, during the replication process one server acts as the primary server, while one or more other servers act as slave servers, simply to pull binary log files from the server to the master server, The log file is then parsed into the appropriate SQL to re-perform the operation of the master server from the server, in this way to ensure the consistency of the data.

MySQL replication technology only provides synchronous execution of the log, and only reads from the server, and when the primary server fails, it must be handled manually by the failover, typically by changing one from the server to the primary server. This solution has achieved MySQL's high availability to a certain extent and can achieve a 90% SLA.

In order to achieve higher availability, in the actual application environment, the use of MySQL replication technology with high-availability clustering software to achieve automatic failover, this way can achieve 95% SLA. Section 8.2 focuses on the solution for MySQL's highly available architecture with keepalived combined with MySQL replication technology.

2. Mmm high-availability solution

MMM is the Master-master Replication manager for MySQL abbreviation, all known as MySQL Master Replication Manager, which provides a scalable scripting suite for monitoring, failover, and management of MySQL master replication configuration. In the MMM high-availability scheme, the typical application is the dual master multi-slave architecture, through the MySQL replication technology can realize two servers mutual primary from, and at any time only one node can be written, avoids the multi-point writes the data conflict. At the same time, when a writable master node fails, the MMM suite can be immediately monitored, then automatically switch the service to the other master node, continue to provide services, to achieve high availability of MySQL.

The MMM solution is currently a more mature MySQL high-availability solution that can achieve a 99% SLA. Section 8.3 will focus on implementing MySQL's high-availability solution through MMM.

3. Heartbeat/san High-availability solution

This scheme is implemented by means of third-party hardware and software, in this scenario, the way to handle failover is a highly available cluster software heartbeat, which monitors and manages the networks connected between nodes and monitors the Cluster service, and automatically initiates cluster services on other nodes when the node fails or when the service is unavailable.

In terms of data sharing, data is shared through San (Storage area Network) storage, and in normal state the cluster master node mounts the storage for data read and write, and when the cluster fails, heartbeat first releases the storage device mounted by the primary node through a quorum device. The storage is then mounted on the standby node, and then the service is started, which enables the sharing and synchronization of the data. This kind of data sharing method is simple, but the cost is high, and there is the possibility of brain fissure, it needs to choose according to the actual application environment. This scenario can achieve a 99.99% SLA.

4. HEARTBEAT/DRBD High-availability solution

This high-availability solution is also based on the third-party hardware and software implementation, in the way of processing failover still use heartbeat, different, in terms of data sharing, using a block-level data synchronization software DRBD to achieve.

DRBD, distributed replicated block device, is a software-implemented, non-shared storage replication solution that mirrors the contents of a block of devices between servers. Unlike San Networks, it does not share storage, but rather replicates data through the network between servers. This solution is slightly more complex and has a brain fissure problem that can achieve a 99.9% SLA.

5. MySQL Cluster High-availability solution

MySQL cluster is comprised of a set of service nodes, each of which runs a variety of processes, including MySQL servers, NDB cluster data nodes, Management servers, and (possibly) specialized data access programs. This solution is the official MySQL technology solution, powerful, but because the implementation of the cumbersome, configuration trouble, the actual enterprise application is not many. MySQL cluster's standard and telecom editions can reach a 99.999% SLA.

This article is from the "Technical Achievement Dream" blog, please be sure to keep this source http://ixdba.blog.51cto.com/2895551/1718140

Common high-availability MySQL Solutions