Shulou(Shulou.com)06/01 Report--

The following brings you a detailed explanation of proxy load balancing and read-write separation, hoping to give you some help in practical application. Load balancing involves many things, not many theories, and there are many books on the Internet. Today, we will use the accumulated experience in the industry to do an answer.

How to route statements when applied on the client.

There are generally two methods.

1: make a judgment directly in the mysql class of PHP. It is the easiest and does not need additional software.

For example, the discuz forum.

2: use cluster middleware

Such as the official mysql_proxy

And the domestic middleware amoeba

Principle: no matter how much mysql is used, the program communicates with proxy

Actual combat: mysql read-write separation, mysql_proxy implementation

Download and install mysql_proxy:

Http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit.tar.gz

After unzipping, the directory contains the compiled binaries. Move the extracted directory to / user/local/

View the help documentation:

First, proxy is required to connect each mysql, and then make a load balancer:

Connect proxy to the other machine (note that the port is 4040):

Then you can manipulate the database, open multiple clients, connect to proxy 4040, and insert the data separately:

Note: the equilibrium of proxy is not the balance of statements, but the balance of connections. For example: connect several connection pools, and operate which mysql is currently connected. The balance at the sentence level can easily lead to data inconsistency)

Read-write separation:

(- b 192.168.0.199: specify 199 CVM mysql as write. -r 192.168.0.200 mysql 3306: specify 200 server read. -s / user/locl/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua: specify read-write analysis script)

Notice that there is a setting in the script that has a minimum and maximum connection:

Actual combat: mysql read-write separation, mysql_proxy implementation

Download and install mysql_proxy:

Http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit.tar.gz

After unzipping, the directory contains the compiled binaries.

1: using mysql_proxy to achieve load balancing

Execute mysql_proxy

. / mysql-proxy-path/bin/mysql-proxy\

-- proxy-backend-addresses=192.168.1.199:3306\

-- proxy-backend-addresses=192.168.1.200:3306

2: connect to mysql_proxy and use mysql customers, because proxy is the front-end agent of mysql

Note that the port of proxy is 4040

Mysql-h "IP of proxy"-P 4040-u username-p password

After the connection, do the sql query, but always send it to a certain mysql server-the load balancer doesn't show it?

A: it's not that it doesn't show up.

Balance is not reflected in the sql statement, one request mysqlA server, the other request MySQL B server.

Equilibrium is the equilibrium that embodies the connection.

Mysql_proxy caches tcp/IP connections to the mysql server into the connection pool to improve performance.

In the cache pool, cached connections are roughly evenly distributed among each mysql server.

But every specific connection is always connected to a server.

. / bin/mysql-proxy\

-- proxy-backend-addresses=192.168.1.199:3306\

-- proxy-read-only-backend-addresses=192.168.1.200:3306\

-- proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua

Abbreviation:

. / bin/mysql-proxy-bang 192.168.0.199VOUR 3306-rhe 192.168.0.200 VOLU 3306-s=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua

After reading the above detailed explanation of proxy load balancing and read-write separation, if you have anything else you need to know, you can find out what you are interested in in the industry information or find our professional and technical engineers who have more than ten years of experience in the industry.

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.