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

This article focuses on "MySQL synchronous replication and highly available solutions". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "MySQL synchronous replication and highly available solutions".

1. Preface

As the data storage service of the application program, mysql should realize the high availability of mysql database. The technology that must be used is the replication of the database. If the master node fails, it can be manually switched and applied to the slave node, which I believe the operation and maintenance students all know and can be realized. However, this situation is only a manual switch, and businesses with requirements for availability need to achieve high availability of master and slave respectively, ensuring that the database can automatically fail over when a downmachine appears in the database. Ensure the availability of applications and user experience.

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

2.MMM high availability scheme

2.1.Mysql-MMM introduction

MMM (Master-Master replication managerfor Mysql,Mysql Master Master replication Manager) is a flexible scripting program based on perl implementation for monitoring and failover of mysql replication and managing the configuration of mysql Master-Master replication (only one node is writable at a time).

2.2. module

Mmm_mond: monitor the process, be responsible for all monitoring work, determine and handle all node role activities. This script needs to be run on the supervisor.

Mmm_agentd: an agent process that runs on each mysql server, performs monitoring probe work and performs simple remote service settings. This script needs to be run on the supervised machine.

Mmm_control: a simple script that provides commands for managing mmm_mond processes.

The supervisor of mysql-mmm will provide multiple virtual IP (VIP), including a writable VIP and multiple readable VIP. Through regulatory management, these IP will be bound to the available mysql. When a mysql goes down, the supervisor will migrate the VIP to other mysql.

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

2.3. Architecture diagram

During normal operation:

When the primary node fails:

Advantages of 2.4.MMM

(1) High availability, good expansibility, automatic failure transfer, for master and master synchronization, only one database write operation is provided at the same time to ensure data consistency.

(2) the configuration is simple and the operation is easy.

Shortcomings of 2.5.MMM

(1) A backup server is needed, which wastes resources.

(2) multiple virtual IP is required

(3) agent may terminate unexpectedly and cause brain fissure.

3.MHA introduction

MHA (Master High Availability) is currently a relatively mature solution for MySQL high availability. It was developed by youshimaton, a Japanese DeNA company (now working for Facebook). It is a set of excellent high availability software for failover and master-slave upgrade in MySQL high availability environment. In the process of MySQL failover, MHA can automatically complete the failover operation of the database within 30 seconds, and in the process of failover, MHA can ensure the consistency of the data to the maximum extent in order to achieve high availability in the real sense.

Introduction to 3.1.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 slave node. MHA Node runs on each MySQL server, and MHA Manager regularly detects the master nodes in the cluster. When the master fails, it automatically promotes the slave of the latest data to the new master, and then redirects all other slave to the new master. The entire failover process is completely transparent to the application.

During MHA automatic failover, MHA tries to save binary logs from the down master server to maximize the loss of data (better with mysql semi-synchronous replication), but this is not always feasible. For example, if the primary server hardware fails or cannot be accessed through ssh, MHA cannot save binary logs and only fails over and loses the latest data. With semi-synchronous replication of MySQL 5.5, the risk of data loss can be greatly reduced. 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, thus ensuring data consistency across all nodes.

Note: currently, MHA mainly supports the architecture of one master and multiple slaves. To build MHA, you must have at least three database servers in a replication cluster. One master and two slaves, that is, one serves as master, one acts as standby master, and the other acts as slave library, because at least three servers are needed. Taobao has also been modified on this basis due to machine cost. At present, Taobao TMHA already supports one master and one slave.

3.2.MHA architecture diagram

Architecture diagram during normal operation:

Main library downtime architecture:

3.3. Failover process

(1) Save binary log events (binlog events) from crashed master

(2) identify the slave with the latest updates

(3) Relay logs (relay log) that apply differences to other slave

(4) apply binary log events saved from master (binlog events)

(5) upgrade a slave to a new master

(6) make other slave connect to the new master for replication

(7) start the vip address in the new master to ensure that the front-end request can be sent to the new master.

Advantages of 3.4.MHA

(1) No backup server is required

(2) do not change the existing environment

(3) the operation is very simple.

(4) Log differences can be repaired.

(5) any slave can be promoted to master.

Shortcomings of 3.5.MHA

(1) all nodes are required to be ssh keys.

(2) the configuration file will be modified after the failure of MHA. If you fail over again, you need to modify the configuration file again.

(3) the self-contained script needs to be further supplemented and improved, and it is difficult to develop with perl.

4. DRBD+ (heartbeat,corosync)

4.1. Brief introduction of the scheme

This scheme uses Heartbeat or corosync dual-computer hot standby software to ensure the high stability and continuity of the database, and the consistency of data is ensured by the tool DRBD (if it can be put on distributed storage as far as possible). By default, there is only one mysql working. When there is a problem with the master mysql server, the system will automatically switch to the slave to continue to provide services. When the master database is repaired, the service will be switched back to continue to be provided by the master mysql.

4.2. module

As a heartbeat detection mechanism, Heartbeat,corosync monitors the status of primary nodes. When the master node is down, quickly upgrade the secondary node to the new master node and switch the IP

Drbd is responsible for data synchronization

4.3. Architecture diagram

4.4. Data synchronization process

When mysql flushes the disk, it will write the data to disk through different sync methods.

After receiving the message of successful disk flushing, drbd will transmit the corresponding disk block location and change action to the secondary node through the network.

When the drbd of secondary receives the change information, it will drop the information on the disk.

4.5. Switching process

Premise: mysql service of secondary node is not started

If heartbeat detects that the mysql service of primary stops, remove the IP, umount the data disk, and switch the primary to secondary.

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

Automatically migrate from the library with IP and port

4.6. Advantages of the scheme

(1) it has a long history, high security, high stability, high availability and automatic switching when failure occurs.

(2) strong data consistency

4.7. Shortcomings of the scheme

(1) A backup server is needed, which wastes resources.

(2) it is not convenient to expand

(3) brain cleavage may occur in both drbd and headbetart,corosync.

5.Mysql route introduction

5.1. What is mysql route?

MySQL Router is a lightweight agent between application client and dbserver that detects, analyzes and forwards queries to back-end database instances and returns the results to client. Is a substitute for mysql-proxy. Its architecture and functions are as follows.

(1) Router realizes the separation of read and write, and the program is not directly connected to the database IP, but fixed to mysql router. MySQL Router is transparent to front-end applications. The application treats MySQL Router as a normal mysql instance and sends the query to MySQL Router, while MySQL Router returns the query result to the front-end application.

(2) if the database server fails, the business can run normally. The server is automatically logged off by MySQL Router. The program configuration does not require any modification.

(3) if the master database fails, the master-slave switch is determined by MySQL Router, and the business can be accessed normally. The program configuration does not need to be modified.

5.2. Principle of separation of reading and writing

After accepting the request of the front-end application, MySQL Router distinguishes between read and write according to different ports, sends all the queries connected to the read-write port to the master library, and sends the select query connected to the read-only port to multiple slave libraries in a polling manner, so as to achieve the purpose of separation of read and write. The result returned by the read and write is given to MySQL Router, which is returned by MySQL Router to the client application.

Use of 5.3.Mysql router

The main uses of MySQL Router are read-write separation, automatic switching of master and master failures, load balancing, connection pooling and so on.

Pit for automatic switching of 5.4.Mysql router master and master failover

There is no problem with the Mysql router master failover function after testing, but there is a large hole to be noted. After the master database is switched, the address of the master server connected to the slave library will not change, so you need to write your own script to judge.

5.5. Advantages

(1) realize the high availability of mysql based on DAL layer.

(2) the master and master failover and read-write separation can be realized at the same time.

(3) plug-in architecture allows users to extend additional functions.

5.6. Shortcoming

(1) the high availability feature needs to be further improved: after the master database is switched, the slave library will not automatically switch the address of the master library.

(2) different ports are used for reading and writing, and the application needs to be modified.

6.mysql Cluster

Very little is used in China, mainly because of three points:

(1) Storage engine needs to be changed

(2) pay

(3) there are almost no use cases in China.

Advantages:

High availability, availability up to 99.999%

At this point, I believe you have a deeper understanding of "MySQL synchronous replication and highly available solutions". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.