MySQL读写分离

 

一、要求

 

1、配置两台MySQL服务器和一台代理服务器,实现MySQL代理的读写分离。

2、用户只需要访问MySQL代理服务器,实际的SQL查询、写入操作交给后台的2MySQL服务器来完成。

3、其中Master服务器允许SQL查询、写入,Slave服务器只允许SQL查询。

 

二、方案

 

使用4RHEL6.5虚拟机,如图下所示。其中192.168.4.10192.168.4.20分别作为MySQL主、从服务器,是整个服务的后端;另一台192.168.4.30作为MySQL代理服务器,是直接面向客户的服务前端;客户机192.168.4.100用作访问测试。

wKioL1QZrhXi_EV0AAF0Yu49jvs024.jpg 

对比两种方式的读写分离效果――

 

 MySQL主从复制:客户机访问Master服务器来写数据库,客户机访问Slave服务 器来读数据库。这种情况下,需要客户端自行区分向何处写、从何处读。

 MySQL主从复制+代理:客户机访问Proxy服务器,读、写请求交给Proxy识别,如果是写数据库操作则交给Master,如果是读数据库操作则交给Slave处理,具体由分配策略控制。这种情况下,无需客户端区分读、写目标,而是由Proxy服务器代劳了,从而降低了客户端程序的复杂度。

 

以下是在mysql主从复制的基础上实现的,mysql主从复制部分请参见另一篇文章MySQL主从复制》

 

三、实现

 

1、部署mysql-proxy代理服务器

1)安装MySQL官方提供的mysql-proxy软件包

MySQL官方提供了mysql-proxy的免安装版本,解压后即可使用。由于msyql-proxy使用了LUA脚本语言,因此需要提前安装好lua软件包(RHEL6镜像内已含)以确保其正常使用:

[root@proxy ~]# yum -y install lua

然后部署mysql-proxy软件包:

[root@proxy 桌面]# tar zxf mysql-proxy-0.8.3-linux-rhel5-x86-64bit.tar.gz -C /usr/local/

[root@proxy 桌面]# cd /usr/local/      

[root@proxy local]# mv mysql-proxy-0.8.3-linux-rhel5-x86-64bit/ mysql-proxy   //将文件改短一点,方便访问

切换到部署后的目录,确认相关目录、可执行程序、脚本。一般情况下,使用bin目录下的mysql-proxy脚本来启动代理服务:

[root@proxy local]# cd mysql-proxy/

[root@proxy mysql-proxy]# ls

bin  include  lib  libexec  licenses  share

[root@proxy mysql-proxy]# ls bin/ libexec/

bin/:                                       //服务脚本目录

mysql-binlog-dump  mysql-myisam-dump  mysql-proxy

 

libexec/:                                    //可执行程序目录

mysql-binlog-dump  mysql-myisam-dump  mysql-proxy

2准备读写分离的LUA策略脚本

直接复制mysql-proxy提供的样例策略即可:

[root@proxy mysql-proxy]# cp share/doc/mysql-proxy/rw-splitting.lua ./

3)启动mysql-proxy代理服务

主要命令选项:

    -P(大写):指定代理监听的IP地址、端口

    -r:指定读服务器的IP地址、端口

    -b:指定写服务器的IP地址、端口

    -s:指定lua脚本文件

    --keepalive:如果服务进程崩溃,尝试重启此进程

 

[root@proxy mysql-proxy]# pwd

/usr/local/mysql-proxy

[root@proxy mysql-proxy]# bin/mysql-proxy -P 192.168.4.30:3306 \

> -b 192.168.4.10:3306 \

> -r 192.168.4.20:3306 \

> -s rw-splitting.lua &

[root@proxy mysql-proxy]# netstat -anpt | grep mysql   //确认监听状态

tcp  0  0  192.168.4.30:3306 0.0.0.0:*   LISTEN      16119/mysql-proxy  

对于客户端来说,这台代理主机192.168.4.30就相当于一台可读可写的MySQL数据库服务器了

为了每次开机启动mysql-proxy代理服务可以将上面的命令写到/etc/rc.local配置文件内

 [root@proxy ~]# vim /etc/rc.local

 .. ..

 /usr/local/mysql-proxy/bin/mysql-proxy -P 192.168.4.30:3306 \

 -b 192.168.4.10:3306 \

 -r 192.168.4.20:3306 \

 -s rw-splitting.lua &

2、测试通过mysql-proxy的读写分离

1)Master服务器上授权用户,允许其从192.168.4.0/24的客户机远程访问。

mysql>  GRANT all ON *.* TO user02@'192.168.4.%' IDENTIFIED BY 'pwd123';

Query OK, 0 rows affected (0.07 sec)

因为此前已配置mysql库的主从同步,SLAVE上的用户授权会自动更新

2)从客户机上192.168.4.100访问Mysql数据库

[root@client ~]# mysql -h192.168.4.30 -uuser02 -ppwd123

ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.4.30' (113)

出现上面这种情况,可以关闭代理的防火墙,或者添加一条允许访问3306端口的防火墙规则

[root@proxy ]# iptables -I INPUT -s 192.168.4.0/24 -p tcp --dport 3306 -j ACCEPT 

再次登录:

[root@client ~]# mysql -h192.168.4.30 -uuser02 -ppwd123

... ...

mysql>                     //表示登录成功

mysql> CREATE DATABASE student;           //新建库

Query OK, 1 row affected (0.10 sec)

mysql> USE student;//切换到新建的库

Database changed

mysql> CREATE TABLE info( id int(4),name varchar(48));    //新建表

Query OK, 0 rows affected (1.23 sec)

mysql> INSERT INTO info VALUES(1,"hanmeimei"),(2,"lilei");  //插入2条表记录

Query OK, 2 rows affected (0.18 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM info;

+------+-----------+

| id   | name         |

+------+-----------+

|    1 | hanmeimei |

|    2 | lilei            |

+------+-----------+

2 rows in set (0.01 sec)

mysql> quit

Bye

 

3)在masterslave上确认客户端新建的表

[root@client ~]# mysql -h192.168.4.10 -uuser02 -ppwd123

mysql> USE student;

mysql> SHOW TABLES;

+-------------------+

| Tables_in_student |

+-------------------+

| info                    |

+-------------------+

1 row in set (0.00 sec)

mysql> SELECT * FROM info;

+------+-----------+

| id     | name       |

+------+-----------+

|    1   | hanmeimei |

|    2   | lilei            |

+------+-----------+

2 rows in set (0.00 sec)

 

[root@client ~]# mysql -h192.168.4.20 -uuser02 -ppwd123

mysql> SELECT * FROM student.info;

+------+-----------+

| id   | name         |

+------+-----------+

|    1 | hanmeimei |

|    2 | lilei            |

+------+-----------+

2 rows in set (0.00 sec)

 

4)观察MySQL代理访问的网络连接

Proxy代理商可看到与MySQL读、写服务器的网络连接:

[root@proxy mysql-proxy]# netstat -anpt | grep mysql

tcp        0      0 192.168.4.30:3306           0.0.0.0:*                   LISTEN      16119/mysql-proxy   

tcp        0      0 192.168.4.30:60975          192.168.4.10:3306           ESTABLISHED 16119/mysql-proxy   

tcp        0      0 192.168.4.30:60974          192.168.4.10:3306           ESTABLISHED 16119/mysql-proxy   

tcp        0      0 192.168.4.30:60977          192.168.4.10:3306           ESTABLISHED 16119/mysql-proxy   

tcp        0      0 192.168.4.30:60978          192.168.4.10:3306           ESTABLISHED 16119/mysql-proxy   

tcp        0      0 192.168.4.30:60976          192.168.4.10:3306           ESTABLISHED 16119/mysql-proxy   

Master上可看到来自SlaveProxy代理的网络连接:

[root@master mysql]# netstat -anpt | grep mysql

tcp        0      0 :::3306                     :::*                        LISTEN      3788/mysqld         

tcp        0      0 ::ffff:192.168.4.10:3306    ::ffff:192.168.4.30:60974   ESTABLISHED 3788/mysqld         

tcp        0      0 ::ffff:192.168.4.10:3306    ::ffff:192.168.4.30:60978   ESTABLISHED 3788/mysqld         

tcp        0      0 ::ffff:192.168.4.10:3306    ::ffff:192.168.4.30:60975   ESTABLISHED 3788/mysqld         

tcp        0      0 ::ffff:192.168.4.10:3306    ::ffff:192.168.4.30:60977   ESTABLISHED 3788/mysqld         

tcp        0      0 ::ffff:192.168.4.10:3306    ::ffff:192.168.4.20:33555   ESTABLISHED 3788/mysqld         

tcp        0      0 ::ffff:192.168.4.10:3306    ::ffff:192.168.4.30:60976   ESTABLISHED 3788/mysqld 

 

并发访问量没超过指定的阀值,不区分读写操作,读写都交给负责读的服务器一旦区分,以后都区分,直到连接数为0

现在即使访问不同的服务器,客户端发觉不出服务端有什么变化,想要观察的话,可以在两个服务器的同一个表里存入不同的数据,然后在客户端开多个终端登录代理观察数据的变化,

 

四、扩展

 

MySQL代理与读(或写)服务器的集成

在许多企业应用中,MySQL数据库基本上都是读多写少,因此可以将mysql-proxyMasterSlave安装到一起,从而减少一台服务器,节省成本。比如:

    Master+Proxy192.168.4.10/24

    Slave192.168.4.20/24

这种情况下,要避免mysql-proxyMySQL服务程序的端口冲突,必要时可更改相关程序的监听地址、端口。 

若要修改MySQL服务程序的监听地址、端口,可采用以下选项:

    --bind-address=:用来限定监听地址,比如可设为127.0.0.1

    --port=:用来改端口号,比如可改为33060

当然,最简单的方法还是直接将mysql-proxy改用其他端口。比如,在Master上部署mysql-proxy,启用时可监听192.168.4.1033060端口:

    [root@proxy ~]# cd /usr/local/mysql-proxy/

    [root@proxy mysql-proxy]# bin/mysql-proxy -P 192.168.4.10:3333 \

    -b 192.168.4.10:3306 \

    -r 192.168.4.20:3306 \

    -s rw-splitting.lua &

 

这样,客户端只要访问192.168.4.103333端口,就可以读写数据库了。当服务器采用非标准的MySQL端口时,需要为mysql工具添加 -P 选项来指定端口号:

 

    [root@client ~]# mysql  -h 192.168.4.10  -uuser02 -P 3333 -ppwd123

    mysql> 

相关文章
相关标签/搜索