MySQL读写分离

实现MySQL读写分离

1.1 问题

本案例要求配置2台MySQL服务器+1台代理服务器,实现MySQL代理的读写分离:

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

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

1.2 方案

使用4台RHEL 6虚拟机,如图-2所示。其中192.168.4.10、192.168.4.20分别作为MySQL主、从服务器,是整个服务的后端;另一台192.168.4.100作为MySQL代理服务器,是直接面向客户的服务前端;客户机192.168.4.120用作访问测试。如图-1所示。

image001.png

图-1

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

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

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

其中MySQL主、从复制结构的搭建参考前面的课程,这里不再赘述。

若希望测试方便,可去除同步库的限制,由Slave自动复制Master上的所有更新:


实现此案例需要按照如下步骤进行。

步骤一:部署mysql-proxy代理服务器

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

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

  1. [root@pxysvr ~]# yum -y install lua

  2. [root@pxysvr pub]# tar zxf mysql-proxy-0.8.3-linux-rhel5-x86-64bit.tar.gz

  3. [root@pxysvr pub]# mv mysql-proxy-0.8.3-linux-rhel5-x86-64bit /usr/local/mysql-proxy

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

  5. [root@pxysvr pub]# cd /usr/local/mysql-proxy/

  6. [root@pxysvr mysql-proxy]# ls

  7. bin  include  lib  libexec  licenses  share

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

  9. bin/:                                                  //服务脚本目录

  10. mysql-binlog-dump  mysql-myisam-dump  mysql-proxy


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

  12. mysql-binlog-dump  mysql-myisam-dump  mysql-proxy

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

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


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

  2. [root@pxysvr mysql-proxy]# file rw-splitting.lua          //查看脚本类型

  3. rw-splitting.lua: ASCII Pascal program text

3)启动mysql-proxy代理服务

主要命令选项:

  • -P:指定代理监听的IP地址、端口

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

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

  • -s:指定lua脚本文件

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

以监听本机的3306端口为例,分别指定读、写服务器,相关操作如下:

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

  2. [root@pxysvr mysql-proxy]# bin/mysql-proxy -P 192.168.4.100:3306 \

  3. -b 192.168.4.10:3306 \

  4. -r 192.168.4.20:3306 \

  5. -s rw-splitting.lua &

启动后可确认监听状态:


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

这样一来,对于客户端来说,这台代理主机192.168.4.100就相当于一台可读可写的MySQL数据库服务器了,尽管其自身并未运行MySQL服务程序。

4)启动mysql-proxy代理服务

为了在每次开机后能够自动运行mysql-proxy,可以将相关操作写到/etc/rc.local配置文件内:

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

  2. .. ..

  3. /usr/local/mysql-proxy/bin/mysql-proxy -P 192.168.4.100:3306 \

  4. -b 192.168.4.10:3306 \

  5. -r 192.168.4.20:3306 \

  6. -s rw-splitting.lua &

步骤二:测试通过mysql-proxy的读写分离

相关测试操作可参考以下过程。

1)在MySQL Master服务器上设置用户授权

以root用户为例,允许其从192.168.4.0/24网段的客户机远程访问。首先登入到Master服务器添加下列授权:

  1. mysql> GRANT all ON *.* TO root@'192.168.4.%' IDENTIFIED BY '1234567';

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

2)从客户机192.168.4.120访问MySQL数据库

注意连接的是mysql-proxy服务器,而并不是Master或Slave:

  [root@pc120 ~]# mysql -u root -p -h 192.168.4.100

测试数据库写入操作:

  1. mysql> CREATE DATABASE proxydb;                          //新建库

  2. Query OK, 1 row affected (0.00 sec)


  3. mysql> USE proxydb;                                      //切换到新建的库

  4. Database changed

  5. mysql> CREATE TABLE proxytb(

  6. -> id int(4),host varchar(48)

  7. -> );                                                 //新建表

  8. Query OK, 0 rows affected (0.03 sec)

  9. mysql> INSERT INTO proxytb VALUES(1,'dbsvr1'),          //插入2条表记录

  10. -> (2,'dbsbr2');

测试数据库读取操作:

  1. mysql> SELECT * FROM proxytb;

切换到新建的proxydb库:

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

当并发的客户端连接较多时,可同时观察Master或Slave上的连接情况。

比如,在Master上可看到来自Slave和Proxy代理的网络连接:

  1. [root@dbsvr1 ~]# netstat -anpt | grep mysql

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

  1. [root@pc120 ~]# netstat -anpt | grep mysql

相关文章
相关标签/搜索