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

I believe many inexperienced people are at a loss about the highly available MySQL solutions in operation and maintenance. therefore, this paper summarizes the causes and solutions of the problem. I hope you can solve this problem through this article.

As one of the most basic data storage services, database plays a very important role in the storage system, so it is understandable to require it to have high availability. There are many solutions that can implement different SLA (service level agreements), which can ensure that the database server will continue to be available in the event of a hardware or software failure.

There are two main problems to be solved in high availability, one is how to share or synchronize data, and the other is how to deal with failover. The general solution of data sharing is achieved through SAN (Storage Area Network), while data synchronization can be achieved through rsync software or DRBD technology. Failover means that when the server crashes or an error occurs, you can automatically switch to another standby server without affecting the operation of the business system on the server. The editor introduces the relatively mature MySQL high availability solution at present.

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 technology is a process of log replication, in which one server acts as the master server, while one or more other servers act as the slave server. To put it simply, pull the binary log file from the server to the master server, and then parse the log file into the corresponding SQL to re-perform the operation of the master server on the slave server to ensure the consistency of the data.

MySQL replication technology only provides the synchronous execution function of the log, while the slave server can only provide read operations, and when the master server fails, the failover must be handled manually, usually by changing a slave server to the master server. This solution achieves the high availability of MySQL to some extent, achieving 90.000% SLA.

In order to achieve higher availability, in the actual application environment, MySQL replication technology combined with highly available cluster software is generally used to achieve automatic failover, which can achieve 95.000% SLA.

2. MMM highly available solution

MMM (Master-Master Replication Manager for MySQL) MySQL Master replication Manager, which provides a scalable suite of scripts for monitoring, failover, and management of MySQL master replication configurations. In the MMM high availability solution, the typical application is the dual master and multi-slave architecture. Through MySQL replication technology, two servers can be master and slave to each other, and only one node can be written at any time to avoid multi-point write data conflicts. At the same time, when the writable master node fails, the MMM suite can monitor it immediately, and then automatically switch the service to another master node to continue to provide services, thus achieving high availability of MySQL.

MMM scheme is a relatively mature MySQL high availability solution, which can achieve 99.000% SLA.

3. Heartbeat/SAN highly available solution

This solution is implemented with the help of third-party hardware and software. In this solution, the way to deal with failover is high-availability cluster software Heartbeat, which monitors and manages the network connected between nodes, and monitors cluster services. When nodes fail or services are unavailable, cluster services are automatically started on other nodes.

In terms of data sharing, data is shared through SAN storage. Under normal conditions, the primary node of the cluster will mount the storage for data reading and writing. When the cluster fails, Heartbeat will first release the storage device mounted on the primary node through an arbitration device, then mount the storage on the standby node, and then start the service to achieve data sharing and synchronization in this way. This kind of data sharing method is simple to implement, but the cost is high, and there is the possibility of brain fissure, so it needs to be chosen according to the actual application environment. This scheme can achieve 99.990% SLA.

4. Heartbeat/DRBD highly available solution

This high availability solution is also implemented with the help of third-party software and hardware, and Heartbeat is still used in the way of dealing with failover. The difference is that in the aspect of data sharing, the data synchronization software DRBD based on block level is used to achieve.

DRBD (Distributed Replicated Block Device) is a software-implemented storage replication solution that does not share the content of mirrored block devices between servers. Unlike the SAN network, it does not share storage, but replicates data over a network between servers. This scheme is a little more complicated to implement, and it also has the problem of brain fissure, which can achieve 99.900% SLA.

5. MySQL Cluster high availability solution

MySQL Cluster consists 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 technical solution officially promoted by MySQL, which is powerful, but because of the tedious implementation and troublesome configuration, there are not many practical applications in enterprises. The standard and telecom versions of MySQL Cluster can achieve 99.999% SLA.

After reading the above, have you mastered the methods of high-availability MySQL solutions in operation and maintenance? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.