已经搭建好MySQL主从架构
10.205.22.185 #mysql-proxy
10.205.22.186 #master
10.205.22.187 #slave
1.安装mysql-proxy软件
wget http://cdn.mysql.com/archives/mysql-proxy/mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
useradd mysql-proxy -s /sbin/nologin
tar -xvf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local/mysql-proxy
2.添加环境变量和授权
1.修改 /etc/profile文件 export PATH=$PATH:/usr/local/mysql-proxy/bin 2.修改/etc/ld.so.conf文件 /usr/local/mysql-proxy/lib
3.在master上添加proxy连接账户
grant all on *.* to 'admin'@'10.205.22.%' identified by 'admin';
3.编辑mysql-proxy配置文件
[mysql-proxy] daemon = true keepalive = 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:3307 proxy-backend-addresses = 10.205.22.186:3306 proxy-read-only-backend-addresses = 10.205.22.187:3306 proxy-lua-script = /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua #proxy-skip-profiling = true # # Admin Configuration admin-address = 0.0.0.0:4041 admin-lua-script = /usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua admin-username = admin admin-password = admin
4.启动mysql-proxy服务，查看proxy连接端口3307和管理端口4041是否启动
/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf &
5.在客户端连接代理接口测试
1.在MySQL连接比较少的情况下测试，可以修改/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
min_idle_connections = 1,
max_idle_connections = 1,
默认为4,8
2.mysql -h10.205.22.185 -uadmin -padmin -P 3307
分别做插入和查询操作，查看是否读写分离。可开启mysql操作日志查看或者抓包。
6.在客户端连接管理端口查看
mysql -h10.205.22.185 -uadmin -padmin -P 4041
mysql> SELECT * FROM backends; +-------------+--------------------+-------+------+------+-------------------+ | backend_ndx | address | state | type | uuid | connected_clients | +-------------+--------------------+-------+------+------+-------------------+ | 1 | 10.205.22.186:3306 | up | rw | NULL | 0 | | 2 | 10.205.22.187:3306 | up | ro | NULL | 0 | +-------------+--------------------+-------+------+------+-------------------+ 2 rows in set (0.00 sec)