Using Mysql-proxy Proxy to implement MSYQL database read-write separation

To achieve a read-write separation, you can first see how to implement MySQL database Master-slave: http://www.cnblogs.com/sustudy/p/4174189.html

Mysql-proxy (to be optimistic about the corresponding version): http://dev.mysql.com/downloads/mysql-proxy/

Reading and writing separation Experiment environment:

IP address of the Mysql-proxy server: 192.168.20.3

IP address of primary database: 192.168.20.4

From the IP address of the database: 192.168.20.5

The steps are as follows:

1, unzip the download good Mysql-proxy package

TAR-XZVF mysql-proxy-0.8.5-linux-rhel5-x86-32bit.tar.gz

2. Copy the Mysql-proxy folder that has been unzipped to the/usr/loca/folder

Cp-p Mysql-proxy-0.8.5-linux-rhel5-x86-32bit Mysql-proxy

3. Create Msql-proxy configuration file in/etc folder: MYSQL-PROXY.CNF

VI mysql-proxy.cnf (content as follows:)

[Mysql-proxy]

Admin-username = root########### #连接主从The user name of the database

Admin-password = 123456############ the password to connect to the master-slave database

Daemon = True ############daemon process runs

KeepAlive = True############ Keep the connection (the boot process will have 2, a process is used to monitor number second, if the second process dies automatic rebuild, this parameter is not available in the old version)

proxy-backend-addresses = 192.168.20.4:3306 ############MySQL Main Library (write) address

proxy-read-only-backend-addresses = 192.168.20.5:3306############MySQL from library (read) address

Proxy-lua-script =/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua############ Read-write split script

Admin-lua-script =/usr/local/mysql-proxy/share/doc/mysql-proxy/admin-sql.lua############Admin Script

Log-file =/usr/local/mysql-proxy/cn.log############mysql-proxy Log

Log-level = Debug

4. Configure environment variables, import LUA scripts and add mysql-proxy commands

Vi/etc/profile (Add the following)

Lua_path= "/usr/local/proxy-mysql/share/doc/mysql-proxy/?" Lua

Export Lua_path

Export path= $PATH:/usr/local/mysql-proxy/bin

Save the profile file and let the changes take effect immediately, you can enter the command: Source/etc/profile

5, open Mysql-proxy Agent:

　　　　Mysql-proxy--defaults-file=/etc/mysql-proxy.cnf>/usr/local/proxy-mysql/cn.log &

The above 5 steps are completed successfully and can be tested. The test steps are as follows:

1, modify The/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua file, quickly open the separation.

If not Proxy.global.config.rwsplit then

Proxy.global.config.rwsplit = {

Min_idle_connections = 1, #修改最小连接为1

Max_idle_connections = 2, #修改最大连接为2, when the terminal has more than 2, it will open the read-write separation

Is_debug = True #debug为true

}

End

3, real-time view/usr/local/mysql-proxy/cn.log. This log records the situation of Mysql-proxy

Tail-f/usr/local/mysql-proxy/cn.log

2, open two terminals, connect agent 192.168.20.3, two terminals will open read and write separation

Terminal One connection: mysql-h192.168.20.3-uroot-p123456-p4040

Terminal Two connection: mysql-h192.168.20.3-uroot-p123456-p4040

Here the user name root and password 123456 is the primary database and the password from the database

4, to the terminal one read operation, the terminal two to write operations, a number of database operations.

5, at this time Cn.log will have changes, you can see the difference.

The experimental process is built in the absence of any errors in the case, the actual problems encountered can be solved by the search engine, and then, in the next step.

　　　　

Using Mysql-proxy Proxy to implement MSYQL database read-write separation