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

In order to solve the problem of how to separate reading and writing in Mysql, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

What is the separation of reading and writing?

In Mysql, read-write separation actually allows the master database to handle active writes and the slave database to handle active reads. We can use mysql-proxy middleware to achieve the read-write separation of Mysql. What role does mysql-proxy play here? In fact, we can think of mysql-proxy as an agent between master and slave databases, which is responsible for accepting requests from the client, and then determining whether the request SQL belongs to a write operation or a read operation. If it belongs to a read operation, it forwards the request to the slave database for processing, and if it is in a write operation, it forwards the request to the master database for processing.

Implement the separation of Mysql read and write

In fact, the simplest solution to achieve Mysql read-write separation is to enable master-slave synchronization, configure the master database connection and the slave database connection at the back end. If you need to deal with the update data operation, it is to complete the update operation with the master database connection. If only select query data, then use the slave database connection to obtain data from the database. But it is too tedious to manage different master-slave databases through code, so Mysql provides the official middleware mysql-proxy implementation proxy, the client only needs to request mysql-proxy connection pool to get a connection, as for according to read and write operations to select different master-slave databases these tasks only need to be handled by mysql-proxy. So this article focuses on how to use mysql-proxy to achieve read-write separation of Mysql.

Lua installation

Since mysql-proxy read-write separation is mainly achieved through rw-splitting.lua scripts, you need to install lua first:

/ * download lua and extract * / wget http://www.lua.org/ftp/lua-5.1.4.tar.gz & & tar zxvf lua-5.1.4.tar.gz/* into lua and install*/cd lua-5.1.4 & & make linux & & make install/* export * / export LUA_CFLAGS= "- I/usr/local/include" LUA_LIBS= "- L/usr/local/lib-llua-ldl" LDFLAGS= "- lm"

Mysql-proxy installation

/ * download mysql-proxy and extract * / wget mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz & & tar-zxvf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.g because by default. Read-write separation will not be enabled until the minimum number of database connections reaches 4. Since the separation of mysql-proxy read and write is mainly achieved through rw-splitting.lua scripts, you need to modify the rw-splitting.lua configuration information first. The path where the rw-splitting.lua configuration file is located: / usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua

You can see that I changed the minimum number of read-write separation connections to 1, and then I can start mysql-proxy in the next step. When starting, you need to specify the master and slave server:

. / mysql-proxy\-- proxy-read-only-backend-addresses=49.232.154.38:3306\-- proxy-backend-addresses=49.235.28.88:3306\-- log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log\-- proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua &

Next, we can first look at the meaning of several configuration parameters:

-- proxy-read-only-backend-addresses: configure read-only slave database

-- proxy-backend-addresses: configure the master server

-- proxy-lua-script:rw-splitting.lua script path

&: indicates that mysql-proxy is executed in the background.

-- log-file: the path where log files are stored

You can see that in the screenshot above, we successfully started mysql-proxy, and then we need to test whether read-write separation works. First of all, we need to make sure that the master-slave synchronization has been successfully configured, and we have no problem with the previous test. First, we log in to turn off master-slave synchronization from the database and view the existing data in the current test1 data table:

The mysql-proxy port is configured at 4040. Next, log in to port 4040 using the root account created yesterday for master-slave synchronization, and insert several pieces of data:

Next, look at the data from the test1 data table of the main database:

Then look at the data from the test1 table from the database:

So we can find that when we write the data, it is written to the main database, that is, the write operation has been successfully separated.

Then continue to use the root account to query the data of the test1 data table:

You can see that the data found is from the database, because there is only one result. If it comes from the main database, there should be three new pieces of data that we just inserted, so it can be proved that our read operation separation is also successful. At this point, our master synchronization and read-write separation are all configured successfully, and we can use this port 4040 root user to proxy database operations in the project. If it is a write operation, the agent will execute it to the master database, and if it belongs to a read operation, the agent will execute it to the slave database to achieve the separation of Mysql read and write.

A brief Analysis of mysql-proxy execution process

The purpose of our testing here is to facilitate the use of an one-master-one-slave architecture for testing, but in many cases, there is more than one back-end Mysql service. Let's finally take a look at the workflow of mysql-proxy:

In fact, the connect_server () function is called when the client initiates a connection to mysql-proxy. If this function is not implemented, mysql-proxy uses polling by default to find the appropriate client proxy to request to the client.

The user passes the account password and other information when the user verifies the account permissions, and mysql-proxy will call the read_auth () function. When the back-end mysql service authenticates the result to mysql-proxy, mysql-proxy will call read_auth_result () and return the authentication result to the client.

The authentication was successful. Every time the client initiates a request mysql-proxy, it calls read_query () to handle the business logic forwarded in the middle. Then when the back-end mysql service passes the query result to mysql-proxy, it calls read_query_result () to process the result set, and finally returns the result set to the client.

This is the end of the answer to the question on how to separate reading and writing in Mysql. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

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.