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

This article mainly introduces "what is the high-availability architecture technology of MySQL". In daily operation, I believe many people have doubts about what the high-availability architecture technology of MySQL is. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubt of "what is the high-availability architecture technology of MySQL?" Next, please follow the editor to study!

Background description

With the development of information technology, enterprises rely more and more on information management, and the data information of various business applications is mainly stored in the database. Enterprises have higher and higher requirements for the continuity of these data access. In order to avoid various losses caused by data interruption, the high availability of the database has become the top priority of enterprise information construction. At the same time, key businesses related to the national economy and people's livelihood, such as telecommunications, finance, energy, and military industry, need to be highly available for critical data storage, and the data system must be run around the clock for 7 × 24 hours to prevent data loss and data damage. Click to pick up the programming learning materials

Introduction to High availability Architecture

The high availability architecture is basically standard for Internet services, and 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 and so on. Each module needs to be highly available in order to ensure the high availability of the whole system. For database services, high availability may be more complex, and the services available to users are not only accessible, but also need to be guaranteed for correctness, so the high availability of the database needs to be treated with more authentication.

MySQL High availability Architecture Classification

MySQL implements highly available MMM

MySQL implements highly available MHA

MySQL implements highly available Master-Slave Architecture

MySQL implements a highly available Cluster pattern

Technical Analysis of MMM

MMM (Master-Master replication manager for MySQL) is a set of scripts that support dual-master failover and dual-master daily management.

MMM is developed in Perl language and is mainly used to monitor and manage MySQL Master-Master (double master) replication. Although it is called dual master replication, only one master is allowed to write to one master at a time, and the other backup master provides partial read service to speed up the warm-up of the candidate master at the time of master-master switching.

The monitoring end of 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 vip will be migrated to other mysql.

On the one hand, MMM script program realizes the function of failover, on the other hand, its internal additional tool scripts can also achieve read load balancing of multiple slave.

This suite can also load balance any number of slave servers based on the standard master-slave configuration, so you can use it to start virtual ip in a group of servers that live in replication, in addition to scripts that implement data backup and resynchronization between nodes.

Analysis of basic components of MMM

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

Mmm_agentd: an agent process that runs on each msql 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.

Basic principles of MMM implementation

MMM provides both automatic and manual ways to remove the virtual ip of servers with high replication latency in a group of servers. At the same time, it can also back up data and achieve data synchronization between the two nodes.

MySQL itself does not provide a replication failover solution, through the MMM scheme to achieve server failover, so as to achieve the high availability of mysql.

Usage scenarios of MMM

Because MMM cannot completely guarantee data consistency, MMM is suitable for scenarios where data consistency is not very high, but you want to maximize business availability.

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

The MMM project comes from Google:code.google.com/p/mysql-mas …

The official website is: mysql-mmm.org

Introduction to MHA

MHA (Master High Availability) is currently a relatively mature solution for MySQL high availability. It is developed by youshimaton (now working for Facebook) of DeNA Company in Japan. 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.

MHA is an open source MySQL highly available program. When MHA monitors a master node failure, it will automatically upgrade the slave node with the latest data to the new master node.

MHA will obtain additional information from other nodes to avoid consistency problems, that is, MHA will obtain the data information from other slave nodes and send the information to the slave node closest to the master node, so that when the master node fails, it will promote the slave node to the master node, while the slave node has all the data information of the other slave nodes.

MHA also provides the online switching function of master nodes, that is, switching master/slave nodes on demand.

Basic components of MHA

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

MHA Manager can be deployed on separate machines to manage multiple master-slave clusters, or it can be deployed on a single slave node.

The realization principle of MHA

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.

In the process of MHA automatic failover, MHA tries to save binary logs from the down primary server to ensure that the data is not lost as much as possible, 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 reduced.

MHA can be combined with semi-synchronous replication, and if only one slave has received the latest binary logs, MHA can apply the latest binary logs to all other slave servers, thus ensuring data consistency across all nodes.

Usage scenarios of MHA

At present, MHA mainly supports the architecture of one master and multiple slaves.

To build a MHA, you must have at least three database servers in a replication cluster, one master and two slaves, that is, one as a master, one as a standby master, and the other as a slave library.

Because at least three servers are needed, Taobao has also been modified on this basis for the consideration of machine cost. At present, Taobao TMHA has supported one master and one slave.

From a code point of view, MHA is a set of Perl scripts, so it is not difficult to change MHA to support one master and one slave with the technical strength of Ali system.

MySQL master-slave architecture

This kind of architecture is commonly used in start-ups, and it is also convenient for further expansion.

Features of this architecture

Low cost, quick and convenient deployment

Separation of reading and writing

It can also reduce the pressure of reading the library by increasing the number of users in time.

Single point of failure of main library

Data consistency problem (caused by synchronization delay)

High availability software can use Heartbeat and is fully responsible for the management of VIP, data and DRBD services.

After the master failure, the slave library can switch automatically and quickly, and the slave library can still synchronize with the new master library through VIP.

Separation of read and write is also supported from the library, which can be implemented using middleware or programs.

Overview of MySQL Cluster

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

At present, the amount of enterprise data is getting larger and larger, so the requirements for MySQL are further improved. Most of the previous high availability schemes usually have some defects, such as MySQL Replication scheme, it takes a certain time to detect the survival of Master, and it also takes a certain time if master-slave switching is needed, so high availability largely depends on monitoring software and automatic management tools. With the continuous development of MySQL Cluster, there has been a great improvement in performance and high availability.

Basic concepts of MySQL Cluster

MySQL Cluster is simply a MySQL clustering technology, which is composed of a group of computers. Each computer can host one or more nodes, including MySQL servers, DNB Cluster data nodes, management nodes, and specialized data access programs. Together, these nodes can improve Cluster data management for applications with high performance, high availability and scalability.

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. The SQL node accesses the data node and returns data results from the data node. The management node only configures and manages the SQL node and the data node.

Understanding MySQL Cluster nodes

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

[1. Management node]

The management node is mainly used to manage other nodes. Typically, you configure the config.ini file to configure how many copies in the cluster need to be maintained, how much memory is allocated for data and indexes on each data node, the IP address, and the disk path to save the data on each data node.

The management node typically manages Cluster configuration files and Cluster logs. Each node in the Cluster retrieves configuration information from the management server and requests to determine how the management server is located. If a new event occurs within the node, the node transmits the information about such events to the management server and writes such information to the Cluster log

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

[2.SQL node]

SQL node is simply a mysqld server, applications can not directly access the data node, can only access the data node through the SQL node to return data. Any SQL node is connected to all storage nodes, so when any storage node fails, the SQL node can transfer the request to another storage node for execution. Generally speaking, the more SQL nodes, the better, the more SQL nodes, the less load allocated to each SQL node, and the better the overall performance of the system.

[3. Data node]

Data nodes are used to store data in Cluster. MySQL Cluster replicates data between data nodes. If any node fails, there will always be another data node to store data.

Usually, these three kinds of logical nodes can be distributed on different computers, and there are at least three computers in the cluster. in order to ensure the normal maintenance of cluster services, the management nodes are usually placed on a separate host.

At this point, the study on "what is the high-availability architecture technology of MySQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.