Common High-Availability MySQL solution _ MySQL

As one of the most basic data storage services, MySQL databases play an important role in the entire system. Therefore, it is understandable to require high availability. There are many solutions that can achieve different SLAs (service level agreements). These solutions can ensure that the database server is one of the most basic data storage services in the hardware MySQL database, it plays an important role in the entire system, so it is understandable to require high availability. There are many solutions that implement different SLAs (service level agreements), which can ensure that the database server continues to be available when the hardware or software fails.

There are two main problems to be solved for high performance: how to achieve data sharing or synchronization, and how to deal with failover. The common solution for data sharing is through the Storage Area Network (SAN) and 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 backup server, does not affect the operation of business systems on the server. This article focuses on the mature Mysql high-performance solutions.

1. master-slave replication solution

This is a high-availability solution provided by MySQL itself. The data synchronization method adopts the MySQL replication technology. MySQL replication is a log replication process. during the replication process, one server acts as the master server, and one or more other servers act as slave servers, simply put, it is to pull the binary log file from the server to the master server, and then parse the log file into the corresponding SQL statement to re-execute the operations on the slave server, this method ensures data consistency.

MySQL replication technology only provides the log synchronization and execution function, while the slave server can only provide read operations. when the master server fails, you must manually handle the failover, the common practice is to change a slave server to the master server. To some extent, this solution achieves high availability of MySQL and 90.000% SLA.

To achieve higher availability, MySQL replication technology and high availability cluster software are generally used in actual application environments to achieve automatic failover, which can achieve 95.000% SLA. In Section 8.2, we will focus on implementing the MySQL high-availability architecture solution through KeepAlived and MySQL replication technology.

2. MMM high availability solution

MMM is the abbreviation of Master-Master Replication Manager for MySQL, it provides a set of scalable script suites for monitoring, failover, and management of MySQL master-master replication configurations. In the MMM high-availability solution, a typical application is the dual-master-slave multi-slave architecture. through MySQL replication technology, two servers can be mutually master-slave, and only one node can be written at any time, this avoids data conflicts between multiple write points. At the same time, when the writable master node fails, the MMM suite can immediately monitor the service, and then automatically switch the service to another master node to continue providing services, so as to achieve high availability of MySQL.

The MMM solution is a mature MySQL high availability solution that can achieve 99.000% SLA. In section 8.3, we will focus on the implementation of MySQL high-availability solutions through MMM.

3. Heartbeat/SAN high availability solution

This solution is implemented by third-party software and hardware. in this solution, the failover processing method is high-availability cluster software Heartbeat, which monitors and manages the networks connected to each node, it also monitors cluster services. when a node fails or the service is unavailable, it automatically starts cluster services on other nodes.

In terms of data sharing, SAN (Storage Area Network) Storage is used to share data. under normal conditions, the master node of the cluster mounts the Storage for data reading and writing. when the cluster fails, heartbeat will first release the storage device mounted to the master node through an arbitration device, then mount the storage on the Slave node, and then start the service to achieve data sharing and synchronization. This data sharing method is easy to implement, but the cost is high, and there is a possibility of split-brain, it needs to be selected based on the actual application environment. This solution can achieve 99.990% SLA.

4. Heartbeat/DRBD high availability solution

This high-availability solution is also implemented by third-party hardware and software. Heartbeat is still used for failover processing. The difference is that in terms of data sharing, it adopts the block-level data synchronization software DRBD. Java background framework source code springmvc mybatis

DRBD is the Distributed Replicated Block Device. it is a software-implemented, non-shared, and storage and replication solution for image Block Device content between servers. Unlike the SAN network, it does not share storage, but copies data through the network between servers. The implementation of this scheme is a little complicated, and there is also a split-brain problem, which can achieve a 99.900% SLA.

5. MySQL Cluster high availability solution

MySQL Cluster is composed of a group of service nodes, each of which runs a variety of processes, including MySQL servers, NDB Cluster data nodes, management servers, and (possibly) dedicated Data Access program. This solution is a technical solution officially promoted by MySQL and has powerful functions. However, due to complicated implementation and configuration troubles, there are not many practical enterprise applications. The Standard and Telecom versions of MySQL Cluster can reach 99.999% SLA.