“This is my 28th day of participating in the First Challenge 2022. For details: First Challenge 2022.”

The background that

With the development of information technology, the enterprise is more and more dependent on information management, the data information of each business application, mainly stored in the database, enterprise access to the data the continuity of the demand is higher and higher, in order to avoid interruption because data result in various losses, the database high availability has become the enterprise information construction of heavy. At the same time, for key businesses in industries or fields related to the national economy and people’s livelihood, such as telecommunications, finance, energy, military industry, and so on, critical data storage needs to be highly available. It is necessary to ensure that the data system runs 24/7 to prevent data loss and damage.

Introduction to high availability architecture

High availability architecture is standard for Internet services, both application services and database services need to be highly available. For a system, it may contain many modules, such as front-end application, cache, database, search, message queue, etc. Each module needs to be highly available to ensure the high availability of the whole system. For database services, high availability may be more complicated. The availability of services to users requires not only access, but also correctness assurance. Therefore, high availability of databases requires more authentication.

MySQL High availability architecture classification

  • MySQL implements highly available MMM
  • MySQL implements highly available MHA
  • MySQL implements a highly available master-slave architecture
  • MySQL implements the highly available Cluster mode

Technical analysis of MMM

MMM (Master-Master Replication Manager for MySQL) is a script program that supports dual-master failover and dual-master daily management.

  • Developed in Perl, MMM is mainly used to monitor and manage MySQL master-master (dual Master) replication. Although it is called dual Master replication, only one Master can be written at a time. The other secondary Master provides partial read services to accelerate the warm-up of secondary Master at the time of Master/Master switchover

  • The monitoring end of MMM provides multiple virtual IP addresses (VIPs), including one writable VIP and multiple readable VIPs. These IP addresses are bound to the available mysql through supervision and management. When one mysql fails, the VIPs will be migrated to other mysql.

  • MMM script implements failover on the one hand, and additional tool scripts on the other hand can implement read load balancing on multiple slaves.

  • The suite can also read load balance any number of slave servers based on a standard master/slave configuration, so you can use it to launch virtual IP addresses on a set of servers that live in replication, as well as scripts for data backup and resynchronization between nodes.

Analysis of basic components of MMM

  • Mmm_mond: Monitoring process, responsible for all monitoring work, deciding and processing all node role activities. Therefore, scripts need to run on supervision.
  • Mmm_agentd: Agent process that runs on each MSQL server to do monitoring probes and perform simple remote service Settings. This script needs to run on the supervised machine.
  • Mmm_control: A simple script that provides commands to manage mmM_MOND operations.

MMM basic implementation principle

MMM automatically and manually removes virtual IP addresses of servers with high replication latency. It also backs up data and synchronizes data between two nodes.

MySQL itself does not provide a solution for replication failover. MMM solution can be used to implement server failover, thus achieving high availability of MySQL.

Usage scenarios of MMM

Since MMM cannot guarantee data consistency, it is suitable for scenarios where data consistency is not high but service availability is maximized.

For businesses that require high data consistency, highly available architecture such as MMM is not recommended.

  • MMM project from Google: code.google.com/p/mysql-mas…
  • The official website is mysql-mmm.org

MHA profile

MHA (Master High Availability) is a relatively mature solution for MySQL High Availability. It is developed by Youshimaton from DeNA (Japan) (now working at Facebook). It is a set of excellent high availability software for failover and master/slave promotion in MySQL high availability environment. During the MySQL failover process, MHA can automatically complete the database failover operation within 0 to 30 seconds. During the failover process, MHA can ensure data consistency to the maximum extent and achieve high availability in the true sense.

  • MHA is an open source MySQL high availability program. When the MHA detects a master node failure, it automatically promotes the slave node with the latest data to become the new master node.

  • MHA can obtain additional information from other nodes to avoid consistency issues, namely MHA will get other information from the data in a node, and to send information to the most close to the master node from the nodes, so the master node fault will enhance the nodes from the master node, and the slave nodes have other all of the data from the node information.

  • The MHA also provides the online switchover function for master nodes, that is, the switchover between master and slave nodes on demand.

The basic components of MHA

MHA consists of two parts: MHA Manager (management Node) and MHA Node (data Node).

The MHA Manager can be deployed on an independent machine to manage multiple master-slave clusters or on one slave node.

Implementation principle of MHA

  • The MHA Node runs on every MySQL server. The MHA Manager periodically probes the master Node in the cluster. When the master fails, it can automatically promote the latest slave to the new master, and then redirect all other slaves to the new master. The entire failover process is completely transparent to the application.
  • During automatic MHA failover, the MHA tries to save binary logs from the primary server that went down to maximize data loss, but this is not always possible.
  • For example, if the primary server hardware fails or is inaccessible over SSH, the MHA cannot save binary logs and only fails over and loses the most recent data. Semi-synchronous replication with MySQL 5.5 reduces 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 to ensure data consistency on all nodes.

Usage scenarios of MHA

Currently, MHA mainly supports a master and many slave architecture.

  • To set up MHA, there must be at least three database servers in a replication cluster. One is the master, one is the standby master, and the other is the slave.

  • Because at least three servers are required, considering the cost of the machine, Taobao has also been transformed on this basis. Currently, Taobao TMHA has supported one master and one slave.

  • From the code level, MHA is just a set of Perl scripts, so I believe with the technical strength of Alibaba, it is not difficult to change MHA to support one master one slave.

MySQL master-slave architecture

This kind of architecture is commonly used by start-ups and is convenient for further expansion

This architecture feature

  1. Low cost, fast and convenient deployment
  2. Reading and writing separation
  3. It can also reduce library read stress by adding slave libraries in a timely manner
  4. Primary library single point of failure
  5. Data consistency problems (due to synchronization delays)

  1. The high availability software uses Heartbeat and is fully responsible for the management of VIP, data, and DRBD services
  2. After the master fails, it can automatically and quickly switch over, and the slave library can still synchronize data with the new master library through VIP
  3. Slave libraries also support read/write separation, which can be implemented using middleware or programs

MySQL Cluster overview

MySQL Cluster technology provides redundancy for MySQL in distributed system, enhances security, improves system reliability and data validity. A MySQL cluster requires a group of computers, each of which can be understood as a node, with different functions. The MySQL Cluster is divided into three types of nodes by function: management node, data node, and SQL node. A computer in a Cluster can be a node or a set of two or three nodes. These nodes are combined to provide high reliability and high performance Cluster data management for applications.

Nowadays, enterprises have more and more data, so they have higher requirements on MySQL. Most of the high availability solutions in the past usually have certain defects. For example, MySQL Replication solution, it takes a certain amount of time to check whether the Master is alive, and it also takes a certain amount of time to perform a primary/secondary switchover. High availability therefore relies heavily on monitoring software and automated management tools. With the continuous development of MySQL Cluster, performance and high availability have been greatly improved.

Basic concepts of MySQL Cluster

MySQL Cluster is simply a kind of MySQL Cluster technology. It is composed of a group of computers. Each computer can hold one or more nodes, including MySQL server, data node of DNB Cluster, other management nodes, and specialized data access program. Cluster data management that can improve performance, high availability and scalability for applications;

The access process of MySQL Cluster is roughly like this. Applications usually use certain load balancing algorithms to distribute data access to different SQL nodes. SQL nodes access data from data nodes and return data results from data nodes.

Understand the MySQL Cluster node

MySQL Cluster can be divided into three types of nodes according to node types, namely management node, SQL node and data node. All these nodes constitute a complete MySQL Cluster system. In fact, data is stored in the storage engine of NDB storage server, while table structure is stored in MySQL server. Applications access data through the MySQL server, while the cluster management server manages the NDB storage server through the management tool ndb_mgmd.

[1. Management Node]

The management node is used to manage other nodes. The config.ini file is typically configured to configure how many replicas in the cluster need to be maintained, how much memory is allocated for data and indexes on each data node, IP addresses, and disk paths to store data on each data node.

Management nodes usually manage Cluster configuration files and Cluster logs. Each node in the Cluster retrieves configuration information from the management server and requests a way to determine where the management server is located. If a new event occurs on a node, the node transmits the event information to the management server and writes the event information to the Cluster log.

In the MySQL Cluster system, at least one management node is required. It is also worth noting that the management node is usually started first because the data node and SQL node need to read the configuration information of the Cluster before starting.

【2.SQL node 】

SQL nodes are simply mysqld servers. Applications cannot access data nodes directly, but can only access data nodes through SQL nodes to return data. Any SQL node is connected to all storage nodes, so if any storage node fails, the SQL node can transfer the request to another storage node. Generally speaking, the more SQL nodes, the better. The more SQL nodes, the smaller the load allocated to each SQL node, the better the overall performance of the system;

[3. Data Node]

A data node is used to store data in a Cluster. The MySQL Cluster copies data between data nodes. If a node fails, another data node will always store data.

Generally, the three nodes with different logic can be distributed on different computers. A cluster has at least three computers. To ensure the normal maintenance of cluster services, the management node is usually placed on a separate host.