mysql-proxy 实现读写分离

实验环境:
系统:reddhat6.5
mysql 版本:mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar
mysql-proxy:mysql-proxy-0.8.4-linux-el6-x86-64bit.tar.gz
下载链接 http://ftp.ntu.edu.tw/pub/MySQL/Downloads/MySQL-Proxy/
172.25.62.4(master)
172.25.62.6 (slave)
172.25.62.3(proxy 代理服务器)
172.25.62.2(客户端)
基本原理:
mysql 主从复制和代理:
客户端发送请求给 mysql-proxy 代理服务器,所接受道德请求又 proxy 进行判断,如果
是写操作,则交给 master 去处理,如果是读操作,则交给 slave 处理。读写分离的策略基于
lua 脚本实现。因此客户端不用去区分读写目标,而是由 proxy 决定。
其中 Master 服务器允许 SQL 查询、写入,Slave 服务器只允许 SQL 查询
1. 172.25.62.4 和 172.25.62.6 主从复制正常
2. proxy 代理服务器:
(1)解压 mysql-proxy 源码包到/usr/local
tar zxf mysql-proxy-0.8.4-linux-el6-x86-64bit.tar.gz -C /usr/local
mv /usr/local/mysql-proxy-0.8.4-linux-el6-x86-64bit/ mysql-proxy
cd mysql-proxy/
(2)mysql-proxy 使用 lua 脚本语言,必须安装 lua 软件包,rhel6 的 lua 可以直接 yum
安装。
yum install lua -y
cd share/doc/mysql-proxy/rw-splitting.lua ./
(3)复制 lua 脚本(lua 脚本包含实现读写分离的策略)
cp share/doc/mysql-proxy/rw-splitting.lua /usr/local/mysql-proxy/
(4)启动 mysql-proxy 代理服务
/usr/local/mysql-proxy/bin./mysql-proxy
-P
172.25.62.3
172.25.62.4:3306 -r 172.25.62.6:3306 -s rw-splitting.lua &
*******参数解释********
-P 172.25.62.3:3306
-b 172.25.62.4:3306
-r 172.25.62.6:3306
-s rw-splitting.lua
:
3306
-b
指定代理监听的 ip 地址,端口
指定写服务器的 ip 地址,端口
指定读服务器的 ip 地址,端口
指定 lua 脚本
过滤监听端口,确认 mysql-proxy 是否启动成功
[root@server3 bin]# netstat -antlpe | grep mysql
tcp
0
0 172.25.62.3:3306
LISTEN
0
9560
1124/mysql-proxy
0.0.0.0:*3. 客户端测试:
在 Master 服务器上授权用户,允许其从 172.25.62.0 的客户机远程访问。
mysql> grant all on *.* to hzy@'172.25.62.%' identified by 'Westos+123';
Query OK, 0 rows affected, 1 warning (0.19 sec)
[root@server2 ~]# mysql -h172.25.62.3 -uhzy -p
//客户端上访问代理服务器的
mysql 数据库
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6341
Server version: 5.7.17-log MySQL Community Server (GPL)
mysql> create database student;
//新建 student 库
Query OK, 1 row affected (0.11 sec)
mysql> use student;
Database changed
mysql> create table info(id int(4),name varchar(48)); //创建 info 表
Query OK, 0 rows affected (1.21 sec)
mysql> insert into info values(1,'westos'),(2,'linux'); //插入数据信息
Query OK, 2 rows affected (0.18 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from info;
+------+--------+
| id | name
|
+------+--------+
|
1 | westos |
|
2 | linux |
+------+--------+
2 rows in set (0.01 sec)
mysql> quit
4. 在 master 和 slave 上查看客户端新建的数据库表信息
master:
[root@server2 ~]# mysql -h172.25.62.4 -uhzy -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6342
Server version: 5.7.17-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;
+--------------------+
| Database
|
+--------------------+
| information_schema |
| mysql
|
| performance_schema |
| student
|
| sys
|
+--------------------+
5 rows in set (0.00 sec)
mysql> use student;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| info
|
+-------------------+
1 row in set (0.00 sec)
mysql> select * from info;
+------+--------+
| id | name
|
+------+--------+
|
1 | westos |
|
2 | linux |
+------+--------+
2 rows in set (0.00 sec)
登陆 slave:
[root@server2 ~]# mysql -h172.25.62.6 -uhzy -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.17-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+| Database
|
+--------------------+
| information_schema |
| mysql
|
| performance_schema |
| student
|
| sys
|
+--------------------+
5 rows in set (0.00 sec)
mysql> select * from studnet.info;
ERROR 1146 (42S02): Table 'studnet.info' doesn't exist
mysql> select * from student.info;
+------+--------+
| id | name
|
+------+--------+
|
1 | westos |
|
2 | linux |
+------+--------+
2 rows in set (0.00 sec)
5.过滤 proxy 服务器上 mysql 访问的网络连接:

netstat -anpt | grep mysql

[root@server3 ~]# netstat -antlpe |grep mysql
tcp         0        0  172.25.62.3:3306              0.0.0.0:*                                  LISTEN            0
       10315         1191/mysql-proxy                         
tcp         0        0  172.25.62.3:46553            172.25.62.4:3306                   ESTABLISHED 0
       10319         1191/mysql-proxy
Proxy 连接了 master(172.25.62.4)执行写操作。

master 上:

[root@server4 ~]# netstat -antlpe |grep mysql
tcp        0         0  :::3306                                                      :::*                                           LISTEN             27

       16680          1890/mysqld

tcp        0         0  ::ffff:172.25.62.4:3306                              ::ffff:172.25.62.3:46553          ESTABLISHED  27

       16695          1890/mysqld

tcp        0         0  ::ffff:172.25.62.4:3306                              ::ffff:172.25.62.6:33130          ESTABLISHED  27

       16691          1890/mysqld