Efficient MySQL cluster (i)

I. Common high-availability MySQL solutions:

1. Common high-availability MySQL solutions:

As one of the most basic data storage services, the database has a very important address in the storage system, so it is understandable to ask for high availability. There are a number of solutions that enable different SLAs (Service level agreements), which ensure that the service continues to be available when the database server fails in hardware or software.

There are two major issues to be addressed in high availability, one is how to implement data sharing or synchronizing data, and the other is how to handle failover. The common solution for data sharing is through the San (Storage area Network), and data synchronization can be achieved through rsync software or DRBD technology. Failover means that when a server freezes or an error occurs, it can automatically switch to a different standby server without affecting the operation of the business system on the server.

a) 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 technology is a log of the replication process, during the replication process one server as the primary server, and one or more other servers acting as slave servers, simply speaking, from the server to the master server pull binary log files, The log file is then parsed into the appropriate SQL to re-perform the operation of the primary server on the server, in this way to ensure data consistency.

MySQL replication technology only provides synchronous execution of the log, and only reads from the server, and when the primary server fails, the failover must be handled manually, 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.

To achieve higher availability, in a real-world application, MySQL replication technology is used in conjunction with highly available clustering software to automate failover, which enables a 95% SLA. For example, the solution for MySQL's highly available architecture is achieved through keepalived combined with MySQL replication technology.

b) MMM high-availability solutions:

MMM (master-master Replication Manager for MySQL) MySQL Master Replication Manager provides a scalable suite of scripts for monitoring, failover, and management of MySQL master replication configuration. In the MMM high-availability solution, the typical application is a dual-master multi-Slave architecture, through the MySQL replication technology can realize two server mutual primary from, and at any time only one node can write, avoid multi-point write 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.

C) Heartbeat/san High-availability solution:

This solution is implemented with third-party hardware and software, in which the failover is handled in a highly available cluster software heartbeat that monitors and manages the networks connected between the nodes and monitors the cluster services, when the node fails or the service is unavailable, Automatically start the Cluster service on other nodes.

in the area of data sharing, the data is shared through SAN storage, and in normal state, the cluster master node mounts the storage for data read and write, and when the cluster sends a failure, heartbeat first releases the storage device mounted by the node through a quorum device, and then mounts the storage on the standby node. Then start the service, in this way to achieve data sharing and synchronization. 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.

d) HEARTBEAT/DRBD High-availability solution:

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

DRBD (distributed replicated block device) is a software-implemented, non-shared storage replication solution for mirrored block content 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.

e) 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 program, powerful, but because the implementation of the cumbersome, configuration trouble, the actual application of enterprises are not many. MySQL Cluster the Standard Edition and the Telecom edition can reach a 99.999% SLA.

Efficient MySQL cluster (i)