MySQL read-write separation

Mysql Read-write separation

MySQL read-write separation technology is also known as the MySQL Proxy service, through the MySQL On the base of master-slave synchronization, a proxy server is added to realize load balancing by allocating read and write operations to different servers for accessing the database, thus increasing the speed of database access.

To build MySQL Read and write separation service, the first to build a mysql master-Slave synchronization service, where the primary server is responsible for write operations, that is, as a write server, from the server responsible for read operations, that is, read the server. Install the agent software and MySQL database on the proxy server during the construction process . Its specific allocation policy needs to be implemented by script, this article adopts the package default script.

Build steps:

1. build MySQL master-Slave synchronization server

2. Proxy Server Install agent software and turn on service

This article uses the mysql-proxy-0.8.3-linux-rhel5-x86-64bit.tar.gz Agent package, which does not need to be installed and can be used directly after decompression.

(1) Unpacking Packages

TAR-ZXF mysql-proxy-0.8.3-linux-rhel5-x86-64bit.tar.gz-c/usr/local/mysql-proxy

Enter the extracted directory

(2) to add execute permissions for an assignment policy script

Its allocation policy script is located in the /usr/local/mysql-proxy/share/doc/mysql-proxy/ directory under the software directory , where the Rw-splitting.lua It's a policy script .

chmod a+x rw-splitting.lua// Add Execute permissions to the script

(3) release the 3306 Port and start the service

Netstat-tnlup | grep:3306// Check if 3306 Port is occupied

Enter the directory /usr/local/mysql-proxy/bin directory, where mysql-proxy is the startup script for the proxy service

Use the ./mysql-proxy [ option ] to start the service:

The options have the following meanings:

-P: Specify IP address, Port for agent listening

-r: Specifies the IP address, Port of the Read server

-B: Specifies the IP address, Port of the Write server

-S: Specify The path to the Lua script file

--keepalive: Automatically restarts this process if the process crashes

Note: This service needs to be running after it is started, so it can be placed in the background when the service is started, and it needs to be shut down by killing process when the service is shut down.

3. Test