Mysql-Proxy

目录

• 使用Mysql-Proxy配置读写分离
  • 1. 下载Mysql-proxy源码包并解压
  • 2. 设置环境变量
  • 3. 启动Mysql-proxy中间件
  • 4. 查询后端状态
  • 5. 测试

使用Mysql-Proxy配置读写分离

我的环境是Centos7.4!3台服务器:Mysql-Proxy、Mysql-Master、Mysql-slave
(192.168.237.128-server|192.168.237.130-server-2|192.168.237.129-client)

首先MySQL Proxy以服务器的身份接受客户端的请求，根据相应配置对这些请求进行分析处理，然后以客户端的身份转发给相应的后端数据库服务器，再接受服务器的信息，然后返回给客户端.

1. 下载Mysql-proxy源码包并解压

[root@server data]# yum install mariadb-server mariadb-devel mariadb -y
[root@server data]# wget http://ftp.ntu.edu.tw/pub/MySQL/Downloads/MySQL-Proxy/mysql-proxy-0.8.4-linux-el6-x86-64bit.tar.gz
[root@server data]# tar -zxf mysql-proxy-0.8.4-linux-el6-x86-64bit.tar.gz
[root@server data]# mv mysql-proxy-0.8.4-linux-el6-x86-64bit /usr/local/mysql-proxy

2. 设置环境变量

[root@server data]# echo 'export PATH=$PATH:/usr/local/mysql-proxy/bin/' >> /etc/profile
[root@server data]# source /etc/profile

3. 启动Mysql-proxy中间件

[root@server data]# mysql-proxy --daemon --log-level=debug --user=mysql-proxy --keepalive --log-file=/var/log/mysql-proxy.log --plugins="proxy" --proxy-backend-addresses="192.168.237.130:3306" --proxy-read-only-backend-addresses="192.168.237.129:3306" --proxy-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua" --plugins=admin --admin-username="admin" --admin-password="admin" --admin-lua-script="/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua"

4. 查询后端状态

↓测试下是不是有权限.
[root@client ~]# mysql -h192.168.237.128 -uroot -p123456 -e "show databases;" -P4040
↓登录Proxy查看后端状态.
[root@server ~]# mysql -h192.168.237.128 -uadmin -padmin -P 4041 
 MySQL [(none)]> select * from backends;
 +-------------+----------------------+-------+------+------+-----------------+
 | backend_ndx | address              | state | type | uuid |connected_clients|
 +-------------+----------------------+-------+------+------+-----------------+
 |           1 | 192.168.237.130:3306 | up    | rw   | NULL |               0 |
 |           2 | 192.168.237.129:3306 | up    | ro   | NULL |               0 |
 +-------------+----------------------+-------+------+------+-----------------+
  2 rows in set (0.00 sec)

5. 测试

↓从Mysql并写入一条数据.
[root@client ~]# mysql
 MariaDB [(none)]> use test
  Database changed
 MariaDB [test]> create table t1(id char(20),name char(20),primary key(id));
  Query OK, 1 row affected (0.00 sec)
 MariaDB [test]> insert into t1 values("1","hyiqie.com");
  Query OK, 0 rows affected (0.03 sec)
↓登录代理端口.
[root@server-2 ~]# mysql -h 192.168.237.128 -uroot -p123456 -P4040
↓查询数据.
 MariaDB [test]> show tables;
  Empty set (0.00 sec)
 MariaDB [test]> select * from t1;
  +----+------------+
  | id | name       |
  +----+------------+
  | 1  | hyiqie.com |
  +----+------------+
  1 row in set (0.01 sec)