MGR read-write separation and load balancing method based on mysql routers

Abstract

An MGR read-write separation and load balancing method based on mysql routers comprises the following steps that S1, automatic failover is conducted, MySQL Router is used for realizing read-write separation, and a front-end application layer is connected to the MySQL Router in a unified mode; S2, DB load balancing: the MySQL middleware distributes the request to a bottom-layer database according to a certain rule in a similar connection pool manner, so that reading and writing separation of the DB is realized, and the availability is high; specifically, through Nginx reverse proxy, based on apolling algorithm, load balancing is achieved through application, and a read-write separation function applied to a database layer is achieved through mysql routers; according to the mysql router, mysql read-write separation and load balancing are achieved, the mysql router is light in weight, the operation and maintenance cost is low, and the performance advantages are obvious.

Description

technical field [0001] The invention belongs to the field of database high-availability architecture design, and in particular relates to a mysql router-based MGR read-write separation and load balancing method. Background technique [0002] MySQL databases are currently being used more and more in production environments. With the continuous increase in business volume, the pressure on a single-node database is very high, and it can no longer support it in some scenarios. There is an urgent need for a lightweight MySQL read-write separation method to solve the current problems. [0003] ProxySQL is developed in C++ language. Although it is also a lightweight product, its functions are sufficient to meet most of the functions required by middleware, including: [0004] 1. The most basic read / write separation, and there are many ways. [0005] 2. User-based, schema-based, and statement-based rules can be customized to route SQL statements. In other words, the rules are fle...

AI Technical Summary

Problems solved by technology

[0013] The purpose of the present invention is to provide a MGR read-write separation and load balancing method based on mysql router to solve the technical problems that ProxySQL has a great impact on performance, high operation and maintenance costs, and poor support for MGR

Images