Summary of MySQL Synchronous Replication and Solutions

Source: Internet
Author: User
Keywords mysql synchronous replication mysql high availability solution mysql master slave synchronous replication
1 Introduction
As the data storage service of the application, mysql must realize the high availability of the mysql database. The technology that must be used is database replication. If the master node fails, you can manually switch the application to the slave node. I believe that the operation and maintenance students know this and can achieve this. However, this situation is only a manual switch. Services that require availability need to achieve high availability of the master database and the slave database respectively to ensure that when the database is down, the database can be automatically failed over to ensure the availability and application user experience.
Simple Application Server
USD1.00 New User Coupon
* Only 3,000 coupons available.
* Each new user can only get one coupon(except users from distributors).
* The coupon is valid for 30 days from the date of receipt.

This article will introduce some commonly used database high-availability solutions. According to your different scenarios, you can choose the appropriate high-availability solution.

2. MMM high availability solution
2.1.Mysql-MMM introduction
MMM (Master-Master replication manager for Mysql, Mysql master replication manager) is a set of flexible script programs, based on perl implementation, used to monitor mysql replication and fault migration, and can manage the configuration of mysql Master-Master replication ( Only one node is writable at a time).

2.2. Components
mmm_mond: Monitoring process, responsible for all monitoring work, determining and processing all node role activities. This script needs to be run on the supervisory machine.

mmm_agentd: The agent process running on each mysql server to complete monitoring probe work and perform simple remote service settings. This script needs to be run on the supervised machine.

mmm_control: A simple script that provides commands to manage the mmm_mond process.

The supervisory end of mysql-mmm will provide multiple virtual IPs (VIPs), including one writable VIP and multiple readable VIPs. Through supervisory management, these IPs will be bound to the available mysql. When a mysql fails In time, the supervision will migrate VIP to other mysql.

During the entire supervision process, relevant authorized users need to be added to mysql so that mysql can support the maintenance of the supervision machine. Authorized users include a mmm_monitor user and a mmm_agent user. If you want to use mmm's backup tool, you must add a mmm_tools user.

2.3. Architecture diagram
When working normally:
When the master node fails:

2.4.MMM advantages
(1) High availability, good scalability, automatic transfer when failure occurs, for master-master synchronization, only one database write operation is provided at the same time to ensure data consistency.

(2) Simple configuration and easy operation.

2.5.MMM disadvantages
(1) Need a backup server, waste resources

(2) Need multiple virtual IP

(3) The agent may terminate unexpectedly, causing split brain.

3.MHA introduction
MHA (Master High Availability) is currently a relatively mature solution for MySQL high availability. It was developed by Japanese DeNA company youshimaton (now working at Facebook). It is an excellent set of failover and master solutions in MySQL high availability environment. From the promotion of high-availability software. In the MySQL failover process, MHA can automatically complete the database failover operation within 0~30 seconds, and in the process of failover, MHA can ensure data consistency to the greatest extent to achieve true High availability in the sense.

3.1. Introduction to MHA Architecture
The software consists of two parts: MHA Manager (management node) and MHA Node (data node). MHA Manager can be deployed on a separate machine to manage multiple master-slave clusters, or it can be deployed on a single slave node. MHA Node runs on each MySQL server. MHA Manager will periodically detect the master node in the cluster. When the master fails, it can automatically upgrade the slave with the latest data to the new master, and then redirect all other slaves to the new master. The master. The entire failover process is completely transparent to the application.

In the process of MHA automatic failover, MHA tries to save the binary log from the down main server to ensure that the data is not lost to the greatest extent (it is better with mysql semi-synchronous replication), but this is not always feasible. For example, if the main server hardware fails or cannot be accessed via ssh, MHA cannot save the binary log, and only fails over and loses the latest data. Using MySQL 5.5 semi-synchronous replication can greatly reduce the risk of data loss. MHA can be combined with semi-synchronous replication. If only one slave has received the latest binary log, MHA can apply the latest binary log to all other slave servers, so the data consistency of all nodes can be guaranteed.

Note: At present, MHA mainly supports a one-master multiple-slave architecture. To build MHA, a replication cluster must have at least three database servers, one master and two slaves, that is, one serves as the master, one serves as the standby master, and the other serves as the From the database, because at least three servers are required, Taobao has also modified on this basis due to machine cost considerations. Currently Taobao TMHA supports one master and one slave.

3.2.MHA architecture diagram
Architecture diagram during normal work:
The main library down time architecture:

3.3. Failover process
(1) Save binary log events (binlog events) from the crashed master;

(2) Identify the slave with the latest update;

(3) Apply the different relay log to other slaves;

(4) Apply the binlog events saved from the master;

(5) Promote a slave as a new master;

(6) Make other slaves connect to the new master for replication;

(7) Start the VIP address at the new master to ensure that front-end requests can be sent to the new master.

3.4. MHA advantages
(1) No need to backup server

(2) Do not change the existing environment

(3) The operation is very simple

(4) The log difference can be repaired

(5) Any slave can be promoted to master

3.5. MHA disadvantages
(1) All nodes need to do ssh secret key

(2) After the MHA fails, the configuration file will be modified. If the failover occurs again, the configuration file needs to be modified again.

(3) The built-in script needs to be further supplemented and perfected, and it is difficult to develop secondary development with perl.

4. DRBD+ (heartbeat, corosync)
4.1. Introduction
This solution uses Heartbeat or corosync dual-system hot backup software to ensure the high stability and continuity of the database. The consistency of the data is guaranteed by the DRBD tool (if it can be placed on distributed storage as much as possible). By default, only one mysql is working. When the main mysql server has a problem, the system will automatically switch to the standby machine to continue to provide services. When the main database is repaired, the service will be switched back to continue to be provided by the main mysql.

4.2. Components
Heartbeat and corosync are used as a heartbeat detection mechanism to monitor the status of the primary node. When the primary node goes down, quickly promote the secondary node as the new primary node and switch the IP;

drbd is responsible for data synchronization

4.3. Architecture diagram

4.4. Data synchronization process
When mysql is flashing the disk, it will finally write the data to disk through different sync methods;

After drbd receives the information of successful flashing, it transmits the corresponding disk block position and change action to the secondary node through the network;

After the secondary drbd receives the change information, it places the information on the disk;

4.5. Handover process
Prerequisite: The mysql service of the secondary node is not started;

Heartbeat detects that the primary MySQL service is stopped, then remove the IP, umount the data disk, and switch primary to secondary;

On the original secondary, upgrade drbd synchronization to primary, mount the data disk, start the mysql service, and bind the IP;

Automatically migrate from the library following the IP and port;

4.6. Program advantages
(1) Long history, high security, high stability, high availability, and automatic switchover in case of failure.

(2) Strong data consistency

4.7. Disadvantages of the scheme
(1) Need a backup server, waste resources

(2) Inconvenient to expand

(3) Whether drbd or headbetart, corosync may have split brain

6. Conclusion
The above high-availability solutions are only familiar to me, and they are more widely used. After all, mysql has been developed for more than 20 years, and there are still many high-availability solutions. If you are interested in other high-availability solutions, you can study by yourself.
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.