RHEL6.5下MySQL读写分离的实现

一、要求1、配置两台MySQL服务器和一台代理服务器，实现MySQL代理的读写分离。2、用户只需要访问MySQL代理服务器，实际的SQL查询、写入操作交给后台的2台MySQL服务器来完成。3、其中Master服务器允许SQL查询、写入，Slave服务器只允许SQL查询。二、方案使用4台RHEL6.5虚拟机，如图下所示。其中192.168.4.10、192.168.4.20分别作为MySQL主、从服务器，是整个服务的后端；另一台192.168.4.30作为MySQL代理服务器，是直接面向客户的服务前端；客户机192.168.4.100用作访问测试。对比两种方式的读写分离效果——

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]# lsbin 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-proxy2）准备读写分离的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 -ppwd123ERROR 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 changedmysql> 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: 0mysql> SELECT * FROM info;+------+-----------+| id | name |+------+-----------+| 1 | hanmeimei || 2 | lilei |+------+-----------+2 rows in set （0.01 sec）mysql> quitBye3）在master和slave上确认客户端新建的表[root@client ~]# mysql -h192.168.4.10 -uuser02 -ppwd123mysql> 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 -ppwd123mysql> 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 mysqltcp 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上可看到来自Slave和Proxy代理的网络连接：[root@master mysql]# netstat -anpt | grep mysqltcp 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-proxy与Master或Slave安装到一起，从而减少一台服务器，节省成本。比如： Master+Proxy：192.168.4.10/24 Slave：192.168.4.20/24这种情况下，要避免mysql-proxy与MySQL服务程序的端口冲突，必要时可更改相关程序的监听地址、端口。若要修改MySQL服务程序的监听地址、端口，可采用以下选项： --bind-address=：用来限定监听地址，比如可设为127.0.0.1 --port=：用来改端口号，比如可改为33060当然，最简单的方法还是直接将mysql-proxy改用其他端口。比如，在Master上部署mysql-proxy，启用时可监听192.168.4.10的33060端口： [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.10的3333端口，就可以读写数据库了。当服务器采用非标准的MySQL端口时，需要为mysql工具添加 -P 选项来指定端口号： [root@client ~]# mysql -h 192.168.4.10 -uuser02 -P 3333 -ppwd123 mysql>MySQL5.6 Replication主从复制（读写分离）配置完整版 http://www.linuxidc.com/Linux/2014-06/103752.htm搭建MySQL代理服务器实现读写分离+主从同步 http://www.linuxidc.com/Linux/2014-05/102265.htmMySQL-5.6+MySQL-Proxy构建主从复制与读写分离 http://www.linuxidc.com/Linux/2014-04/100933.htmMySQL数据库读写分离 http://www.linuxidc.com/Linux/2012-09/71009.htmThinkphp框架支持MySQL的读写分离 http://www.linuxidc.com/Linux/2014-06/103813.htm本文永久更新链接地址