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