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

Xiaobian to share with you MySQL to achieve Load Balancer function method, I hope you have something to gain after reading this article, let's discuss it together!

preface

MySQL is a high-speed, high-performance, multithreaded, open-source relational database management system (RDBMS) built on a client/server architecture. It started in 1979 as UNIREG database system created by Michael Widenius for Swedish TcX company. UNIREG did not have SQL(Structured Query Language) interface at that time, which limited its application.

In May 1996, Widenius developed the first version of MySQL and began publishing it publicly on the Internet. MySQL developers have been concerned about its performance since the beginning, regardless of the feature set, and to this day, MySQL remains true to speed and performance. Over time, MySQL also added advanced features of large database products, such as stored procedures, views, triggers, etc., so that it began to be deployed in enterprise database systems.[1]

In October 2008, SUN acquired MySQLAB and began moving into open source. SUNMySQL's share of the database market will increase further with the open-source release of Solaris, a heavyweight operating system. Therefore, deploying MySQL server cluster with Load Balancer function in production environment has great practical significance for improving the speed, stability and scalability of enterprise database application system, and can also effectively reduce the investment cost of application system.

I. Basic Idea of Load Balancer

Average load as much as possible in a server cluster. The usual practice is to set up a Load Balancer (specialized hardware device) at the front end of the server. MySQL's Load Balancer is usually inseparable from data fragmentation (dividing data into small blocks and storing them in different db nodes) and replication operations.

Average load as much as possible in a server cluster. The usual practice is to set up a Load Balancer (specialized hardware device) at the front end of the server. MySQL's Load Balancer is usually inseparable from data fragmentation (dividing data into small blocks and storing them in different db nodes) and replication operations.

The main contribution of Load Balancer is to provide administrative read/write policies in addition to equalizing database requests. When the request is distributed, it determines which nodes are writable and readable, and then sends the request to the specified node to perform the operation.

II. Ways to Realize Load Balancer

1, mysql read and write separation

During mysql replication, multiple data copies (backup libraries) are generated. To reduce the pressure on the server, the backup library is used to process read operations. The master library can handle read and write simultaneously. This is a common strategy for mysql cluster to achieve read and write separation.

Because the replication of the backup database is asynchronous and cannot be synchronized in real time, the main difficulty of reading and writing separation lies in the dirty data on the backup database. Usually, if you use a backup library for reading, the real-time requirements for the data are generally not too high. In this regard, mysql provides several common ways to separate reading and writing, such as query-based separation of reading and writing, dirty data-based, session-based, etc., interested in continuing to study.

The read/write separation set by mysql reduces the amount of requests from the main library, and sends a large number of read operations to the standby library to achieve Load Balancer.

2. Modify DNS

DNS and how DNS achieves load are described in detail in High Concurrent Load Balancer (I)-Enterprise Architecture Analysis and DNS. In short, n server IPs are assigned to a domain name, and requests are sent to different IP servers for processing according to different identification characteristics of requests.

3. Introducing middleware

mysql officially provides a mysql payload middleware, mysql_proxy, which also needs to be installed on the server and modify the configuration file (mysql server IP), which is similar to nginx in essence and is also a proxy server.

4. Use mysql to copy the shunt query operation

The master-slave replication of mysql can effectively distribute update operations and query operations. The specific implementation is that one master server undertakes update operations, multiple slave servers undertake query operations, and data synchronization is realized between master and slave through replication. Multiple slave servers are used to ensure availability on the one hand, and to create different indexes to meet the needs of different queries on the other hand.

For situations where all tables do not need to be copied between master and slave, you can set up a virtual slave server on the master server, set the tables that need to be copied to the slave server as blackhole engines, and then define the replicate-do-table parameter to copy only these tables. In this way, binlogs that need to be copied are filtered out, reducing the bandwidth for transmitting binlogs. Because the virtual slave server is only used to filter binlogs and does not actually record any data, the performance impact on the master database server is also very limited.

The problem of query by replication diversion is that when the main database is updated frequently or when there is a problem in the network, there may be differences in the data between the master and the slave, resulting in disagreement between the query results. The application needs to be considered when designing.

5. Distributed database architecture

MySQL has supported distributed transactions since 5.0.3, and currently distributed transactions are only supported by the Innodb storage engine. Distributed database architecture is suitable for large data volume, high load situation, good scalability and high availability. Load averaging among multiple servers is achieved by distributing data among multiple servers, improving access execution efficiency. When implementing it, you can use mysql's Cluster function (NDB engine) or write your own program to implement global transactions.

After reading this article, I believe you have a certain understanding of "MySQL to achieve Load Balancer function method," if you want to know more related knowledge, welcome to pay attention to the industry information channel, thank you for reading!

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.