MySQL Master: 192.168.26.210 (CENTOS6.6,MARIADB10)
MySQL from: 192.168.26.211 (CENTOS6.6,MARIADB10)
Read/write Splitter mysql-proxy:192.168.26.212
Master: MySQL configuration file editing:
Vim/etc/my.cnf.d/server.cnf
650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M02/8C/29/wKioL1hkZcuxBx87AAAG8lxYgW4066.png-wh_500x0-wm_3 -wmp_4-s_2906837876.png "title=" 1.png "alt=" Wkiol1hkzcuxbx87aaag8lxygw4066.png-wh_50 "/>
The following two entries are added to the configuration file:
Log-bin=mysql-bin
Server-id = 210
650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M00/8C/29/wKioL1hkZjPxwZafAAAdEF2HK6A084.png-wh_500x0-wm_3 -wmp_4-s_862373779.png "title=" 2.png "alt=" Wkiol1hkzjpxwzafaaadef2hk6a084.png-wh_50 "/>
from: MySQL configuration file editing:
Vim/etc/my.cnf.d/server.cnf
650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M01/8C/2D/wKiom1hkZk3CvrYEAAAPHEMvtlk255.png-wh_500x0-wm_3 -wmp_4-s_3525250367.png "title=" 3.png "alt=" Wkiom1hkzk3cvryeaaaphemvtlk255.png-wh_50 "/>
The following two entries are added to the configuration file:
650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M01/8C/29/wKioL1hkZmvAHEkLAAAq59S8d8Q145.png-wh_500x0-wm_3 -wmp_4-s_789161894.png "title=" 4.png "alt=" Wkiol1hkzmvaheklaaaq59s8d8q145.png-wh_50 "/>
Read-Write Separation scheduler: Mysql-proxy (0.8.5 version)
650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M00/8C/2D/wKiom1hkZpegZle1AAAsJCx0MtQ592.png-wh_500x0-wm_3 -wmp_4-s_396882799.png "title=" 5.png "alt=" Wkiom1hkzpegzle1aaasjcx0mtq592.png-wh_50 "/>
Direct Yum Installation: Yum List Msyql-proxy
650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M00/8C/29/wKioL1hkZrvSW3bJAAAScaV8v6c995.png-wh_500x0-wm_3 -wmp_4-s_3758647507.png "title=" 6.png "alt=" Wkiol1hkzrvsw3bjaaascav8v6c995.png-wh_50 "/>
Mysql-proxy editing the configuration file after installation is complete: vim/etc/mysql-proxy
[Mysql-proxy]
Daemon = True
Pid-file =/var/run/mysql-proxy.pid
Log-file =/var/log/mysql-proxy.log
Log-level = Debug
Max-open-files = 1024
Plugins = Admin,proxy
user = Mysql-proxy
#
#Proxy Configuration
proxy-address = 0.0.0.0:3306 #分离器地址默认本是4040, if MySQL is installed on this machine, use a different port
Proxy-backend-addresses =192.168.26.210:3306 #可读写服务器地址
proxy-read-only-backend-addresses = 192.168.26.211:3306 #只读服务器地址列表, multiple servers, separate
Proxy-lua-script =/usr/share/doc/mysql-proxy-0.8.5/examples/rw-splitting.lua #指定读写分离脚本路径位置
Proxy-skip-profiling = True
#
# Admin Configuration
admin-address = 0.0.0.0:4041 #管理地址
Admin-lua-script =/usr/lib64/mysql-proxy/lua/admin.lua #Mysql-proxy does not authenticate the user, but the next authentication is delivered to the backend server for verification. Therefore, you need to open permissions on Mysql-proxy on the backend server
Admin-username = Admin #管理账号
Admin-password = Admin #管理密码
650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M02/8C/2D/wKiom1hkZu6gWubAAABlFl6f45Q698.png-wh_500x0-wm_3 -wmp_4-s_2428972646.png "title=" 7.png "alt=" Wkiom1hkzu6gwubaaablfl6f45q698.png-wh_50 "/>
Save exit, remember to join the boot service startup entry: Chkconfig mysql-proxy on
650) this.width=650; "Src=" http://s5.51cto.com/wyfs02/M01/8C/2D/wKiom1hkZx2RXW82AAAQBhaU1ek491.png-wh_500x0-wm_ 3-wmp_4-s_3680503793.png "title=" 8.png "alt=" wkiom1hkzx2rxw82aaaqbhau1ek491.png-wh_50 "/>
/usr/share/doc/mysql-proxy-0.8.5/examples/rw-splitting.lua
admin.lua Script path: /usr/lib64/mysql-proxy/lua/admin.lua 
650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M01/8C/29/wKioL1hkZ-qC0dmCAAAzKo-NCmw253.png-wh_500x0-wm_3 -wmp_4-s_1039321452.png "title=" 10.png "alt=" Wkiol1hkz-qc0dmcaaazko-ncmw253.png-wh_50 "/>
Start Services: Service Mysql-proxy start
Login Mysql-proxy with Admin account
mysql-uadmin-padmin-h192.168.26.212--port=4041
650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M02/8C/2D/wKiom1hkaAzAxBrdAABUl9vGZ08463.png-wh_500x0-wm_3 -wmp_4-s_1248001696.png "title=" 11.png "alt=" Wkiom1hkaazaxbrdaabul9vgz08463.png-wh_50 "/>
Execute command view: SELECT * from Backends;
Both server owners and both have been added to the Mysql-proxy, and the primary server RW represents read-write and Ro.
650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M00/8C/2D/wKiom1hkaGiQqfNQAABY7O8B6U4261.png-wh_500x0-wm_3 -wmp_4-s_1309516406.png "title=" 12.png "alt=" Wkiom1hkagiqqfnqaaby7o8b6u4261.png-wh_50 "/>
Go back to the main server: 192.168.26.210 create an Access account and password for testing use.
Grant all on * * to ' bdadmin ' @ ' 192.168.%.% ' identified by ' jerrypass ';
650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M01/8C/29/wKioL1hkboygdIkPAAA_2d3FvoU819.png-wh_500x0-wm_3 -wmp_4-s_1638008923.png "title=" 13.png "alt=" Wkiol1hkboygdikpaaa_2d3fvou819.png-wh_50 "/>
In order to test the effect can be used tcpdump to grasp the effect of the packet:
650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M00/8C/29/wKioL1hkbquTYIUoAAB_NuvZB24806.png-wh_500x0-wm_3 -wmp_4-s_923208088.png "title=" 14.png "alt=" Wkiol1hkbqutyiuoaab_nuvzb24806.png-wh_50 "/>
Either find a host in the same network to log on to the MySQL server (210,211,212 can, note the port number when landing, here I am my manual
Changed to 3306):
650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M02/8C/2D/wKiom1hkbuTg3KrrAAAXit09Vzs701.png-wh_500x0-wm_3 -wmp_4-s_489644619.png "title=" 15.png "alt=" Wkiom1hkbutg3krraaaxit09vzs701.png-wh_50 "/>
To perform the CREATE DATABASE operation:
Create Database bdtest;
650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M02/8C/29/wKioL1hkbvzTYpEWAABAkkuWDvI275.png-wh_500x0-wm_3 -wmp_4-s_2564341645.png "title=" 16.png "alt=" Wkiol1hkbvztypewaabakkuwdvi275.png-wh_50 "/>
Observation Status:
650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M02/8C/2D/wKiom1hkbyaTQTuiAABq7uR9Plg126.png-wh_500x0-wm_3 -wmp_4-s_2364889196.png "title=" 17.png "alt=" Wkiom1hkbyatqtuiaabq7ur9plg126.png-wh_50 "/>
1 main multi-station from configuration:
[Mysql-proxy]
Daemon = True
Pid-file =/var/run/mysql-proxy.pid
Log-file =/var/log/mysql-proxy.log
Log-level = Debug
Max-open-files = 1024
Plugins = Admin,proxy
user = Mysql-proxy
#
#Proxy Configuration
Proxy-address = 0.0.0.0:3306
Proxy-backend-addresses = 192.168.26.210:3306
Proxy-read-only-backend-addresses = 192.168.26.211:3306,192.168.26.212:3306
Proxy-lua-script =/usr/share/doc/mysql-proxy-0.8.5/examples/rw-splitting.lua
#proxy-skip-profiling = True
#
# Admin Configuration
#admin-address = 0.0.0.0:4041
Admin-lua-script =/usr/lib64/mysql-proxy/lua/admin.lua
Admin-username = Admin
Admin-password = Admin
650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M02/8C/29/wKioL1hkb0Cj7K_WAABtOefdyJA668.png-wh_500x0-wm_3 -wmp_4-s_394172906.png "title=" 18.png "alt=" Wkiol1hkb0cj7k_waabtoefdyja668.png-wh_50 "/>
650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M00/8C/2D/wKiom1hkb1zQH9deAABmvCBelhw967.png-wh_500x0-wm_3 -wmp_4-s_4033125086.png "title=" 19.png "alt=" wkiom1hkb1zqh9deaabmvcbelhw967.png-wh_50 "/>650) this.width=650;" Src= "http://s1.51cto.com/wyfs02/M00/8C/29/wKioL1hkb2HAVeLJAAAp9enz2iQ084.png-wh_500x0-wm_3-wmp_4-s_701949931. PNG "title=" 20.png "alt=" Wkiol1hkb2haveljaaap9enz2iq084.png-wh_50 "/>
In particular, it is necessary to perform several queries in the test (because the operation is limited by the number of connections) to better observe the effect.
This article comes from "on the road ... Find the Lost Memory "blog, be sure to keep this source http://jdonghong.blog.51cto.com/3473478/1887164
MySQL master-slave replication and read/write separation (MariaDB)