How to implement load balancing in MySQL

Preface

MySQL is a high-speed, high-performance, multi-threaded, open source relational database management system (RDBMS) based on a client/server structure. It started in 1979 and was originally the UNIREG database system created by Michael Widenius for the Swedish TcX company. At that time, UNIREG did not have a SQL (Structured Query Language) interface, which limited its application.

In May 1996, Widenius developed the initial version of MySQL and began to release it publicly on the Internet. MySQL developers have always focused on its performance from the beginning, and have spared no effort in feature set. To this day, MySQL remains true to its nature, with high speed and high performance as its primary principles. As time went by, MySQL also added advanced features of large database products, such as stored procedures, views, triggers, etc., which enabled it to be deployed and applied in enterprise-level database systems[1].

In October 2008, SUN acquired MySQL AB and began to enter the open source field. With the open source of the heavyweight operating system Solaris, SUNMySQL's share in the database market will further increase. Therefore, deploying a MySQL server cluster with load balancing function in a production environment has great practical significance for improving the speed, stability and scalability of enterprise database application systems, and can also effectively reduce the investment cost of application systems.

1. Basic ideas of load balancing

In a server cluster, try to distribute the load as evenly as possible. The usual practice is to set up a load balancer (specialized hardware device) on the front end of the server. MySQL load balancing usually requires data sharding (splitting data into small pieces and storing them in different db nodes) and replication.

In a server cluster, try to distribute the load as evenly as possible. The usual practice is to set up a load balancer (specialized hardware device) on the front end of the server. MySQL load balancing usually requires data sharding (splitting data into small pieces and storing them in different db nodes) and replication.

The main contribution of load balancing, in addition to evenly distributing database requests, is to provide management of read/write strategies. When distributing requests, it determines which nodes are writable and readable, and then sends the request to the specified node to perform the operation.

2. Ways to implement load balancing

1. MySQL read-write separation

When MySQL replicates, multiple data copies (standby databases) are generated. To reduce server pressure, the standby database is used to process read operations. The primary database can process read and write operations at the same time, which is a common strategy for MySQL clusters to achieve read-write separation.

Since the replication of the standby database is asynchronous and cannot be synchronized in real time, the main difficulty of read-write separation lies in the dirty data on the standby database. Usually, if a standby database is used for reading, the real-time requirements for the data cannot be too high. In this regard, MySQL provides several common read-write separation methods, such as query-based read-write separation, dirty data-based, session-based, etc. If you are interested, you can continue to study.

The read-write separation set by MySQL reduces the number of requests to the primary database and sends a large number of read operations to the backup database to achieve load balancing.

2. Modify DNS

In High Concurrency Load Balancing (I) - Enterprise Architecture Analysis and DNS, DNS and how DNS implements load balancing are introduced in detail. In short, n server IPs are assigned to a domain name, and requests are sent to different IP servers for processing based on different identification features of the requests.

3. Introducing middleware

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

4. Use MySQL to copy and divert query operations

Using MySQL's master-slave replication can effectively divert update operations and query operations. The specific implementation is a master server that is responsible for update operations, and multiple slave servers that are responsible for query operations. Data synchronization is achieved through replication between the master and slaves. Multiple slave servers are used to ensure availability and to create different indexes to meet the needs of different queries.

If all tables do not need to be replicated between the master and the slave, you can build a virtual slave server on the master server, set the tables that need to be replicated to the slave server to the blackhole engine, and then define the replicate-do-table parameter to only replicate these tables. This will filter out the binlogs that need to be replicated and reduce the bandwidth for transmitting binlogs. Because the virtual slave server only filters the binlog and does not actually record any data, the performance impact on the main database server is very limited.

The problem with querying through replication and shunting is that when the master database is frequently updated or there is a problem with the network, the data between the master and the slave may differ, causing objections to the query results. This needs to be taken into consideration when designing the application.

5. Use a distributed database architecture

MySQL has supported distributed transactions since 5.0.3. Currently, distributed transactions are only supported for the Innodb storage engine. The distributed database architecture is suitable for large data volumes and high loads, and has good scalability and high availability. By distributing data among multiple servers, load balancing among multiple servers is achieved, thereby improving access execution efficiency. When implementing it, you can use the Cluster function of MySQL (NDB engine) or write your own program to implement global transactions.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.