MySQL master-slave replication and read/write separation (MariaDB)

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)