实现MySQL读写分离_mysql 区分执行读库和写库-CSDN博客

本文链接：https://blog.csdn.net/qq_43237999/article/details/90898003

实现MySQL读写分离

问题

本案例要求配置2台MySQL服务器+1台代理服务器，实现MySQL代理的读写分离：
用户只需要访问MySQL代理服务器，而实际的SQL查询、写入操作交给后台的2台MySQL服务器来完成
其中Master服务器允许SQL查询、写入，Slave服务器只允许SQL查询

方案

使用4台RHEL 6虚拟机，如图-2所示。其中192.168.4.10、192.168.4.20分别作为MySQL主、从服务器，是整个服务的后端；另一台192.168.4.100作为MySQL代理服务器，是直接面向客户的服务前端；客户机192.168.4.120用作访问测试。如图所示。
在这里插入图片描述
对比两种方式的读写分离效果——
MySQL主从复制：客户机访问Master服务器来写数据库，客户机访问Slave服务器来读数据库。这种情况下，需要客户端自行区分向何处写、从何处读。
MySQL主从复制+代理：客户机访问Proxy服务器，读、写请求交给Proxy识别，如果是写数据库操作则交给Master，如果是读数据库操作则交给Slave处理，具体由分配策略控制。这种情况下，无需客户端区分读、写目标，而是由Proxy服务器代劳了，从而降低了客户端程序的复杂度。
其中MySQL主、从复制结构的搭建参考前面的课程，这里不再赘述。
若希望测试方便，可去除同步库的限制，由Slave自动复制Master上的所有更新：

[root@dbsvr2 ~]# vim /etc/my.cnf
[mysqld]
.. ..
#replicate_do_db=mysql  							//注释或删除相关限制
#replicate-wild-do-table=mysql.% 
#replicate_do_db=test 
#replicate-wild-do-table=test.% 

[root@dbsvr2 ~]# service mysql restart  		//重启服务
Shutting down MySQL.. 								[确定]
Starting MySQL... 									[确定]

步骤

实现此案例需要按照如下步骤进行。
步骤一：部署mysql-proxy代理服务器
1）安装MySQL官方提供的mysql-proxy软件包
MySQL官方提供了mysql-proxy的免安装版本，解压后即可使用。由于msyql-proxy使用了LUA脚本语言，因此需要提前安装好lua软件包（RHEL6镜像内已含）以确保其正常使用：

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

然后部署mysql-proxy软件包：

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

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

[root@pxysvr pub]# cd /usr/local/mysql-proxy/
[root@pxysvr mysql-proxy]# ls
bin  include  lib  libexec  licenses  share
[root@pxysvr 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@pxysvr mysql-proxy]# cp share/doc/mysql-proxy/rw-splitting.lua ./
[root@pxysvr mysql-proxy]# file rw-splitting.lua  		//查看脚本类型
rw-splitting.lua: ASCII Pascal program text

3）启动mysql-proxy代理服务
主要命令选项：
-P：指定代理监听的IP地址、端口
-r：指定读服务器的IP地址、端口
-b：指定写服务器的IP地址、端口
-s：指定lua脚本文件
–keepalive：如果服务进程崩溃，尝试重启此进程
以监听本机的3306端口为例，分别指定读、写服务器，相关操作如下：

[root@pxysvr ~]# cd /usr/local/mysql-proxy/ 
[root@pxysvr mysql-proxy]# bin/mysql-proxy -P 192.168.4.100:3306 \
    -b 192.168.4.10:3306 \
    -r 192.168.4.20:3306 \
    -s rw-splitting.lua &

启动后可确认监听状态：

[root@pxysvr mysql-proxy]# netstat -anpt | grep mysql
tcp  0    0  192.168.4.100:3306    0.0.0.0:*   LISTEN    3177/mysql-proxy

这样一来，对于客户端来说，这台代理主机192.168.4.100就相当于一台可读可写的MySQL数据库服务器了，尽管其自身并未运行MySQL服务程序。
4）启动mysql-proxy代理服务
为了在每次开机后能够自动运行mysql-proxy，可以将相关操作写到/etc/rc.local配置文件内：

[root@pxysvr ~]# vim /etc/rc.local
.. ..
/usr/local/mysql-proxy/bin/mysql-proxy -P 192.168.4.100:3306 \
 -b 192.168.4.10:3306 \
 -r 192.168.4.20:3306 \
 -s rw-splitting.lua &

步骤二：测试通过mysql-proxy的读写分离
相关测试操作可参考以下过程。
1）在MySQL Master服务器上设置用户授权
以root用户为例，允许其从192.168.4.0/24网段的客户机远程访问。首先登入到Master服务器添加下列授权：

mysql> GRANT all ON *.* TO root@'192.168.4.%' IDENTIFIED BY '1234567';
Query OK, 0 rows affected (0.00 sec)

因为此前已配置mysql库的主从同步，SLAVE上的root授权会自动更新：
2）从客户机192.168.4.120访问MySQL数据库
注意连接的是mysql-proxy服务器，而并不是Master或Slave：

[root@pc120 ~]# mysql -u root -p -h 192.168.4.100
Enter password:  									//验证口令
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 63
Server version: 5.6.15-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> 											//可成功登入

测试数据库写入操作：

mysql> CREATE DATABASE proxydb;  						//新建库
Query OK, 1 row affected (0.00 sec)

mysql> USE proxydb;  									//切换到新建的库
Database changed
mysql> CREATE TABLE proxytb(
     -> id int(4),host varchar(48)
     -> ); 												//新建表
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO proxytb VALUES(1,'dbsvr1'),  		//插入2条表记录
    -> (2,'dbsbr2');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

测试数据库读取操作：

mysql> SELECT * FROM proxytb;
+------+--------+
| id   | host   |
+------+--------+
|    1 | dbsvr1 |
|    2 | dbsbr2 |
+------+--------+
2 rows in set (0.01 sec)

3）在Master和Slave上确认客户端新建的库、表
切换到新建的proxydb库：

mysql> USE proxydb;
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_proxydb |
+-------------------+
| proxytb           |
+-------------------+
1 row in set (0.00 sec)

查询表记录：

mysql> SELECT * FROM proxytb;
+------+--------+
| id   | host   |
+------+--------+
|    1 | dbsvr1 |
|    2 | dbsbr2 |
+------+--------+
2 rows in set (0.00 sec)

4）观察MySQL代理访问的网络连接
当并发的客户端连接较多时，可同时观察Master或Slave上的连接情况。
比如，在Master上可看到来自Slave和Proxy代理的网络连接：

[root@dbsvr1 ~]# netstat -anpt | grep mysql
tcp        0      0 :::3306                     :::*                        LISTEN      9026/mysqld
tcp        0      0 ::ffff:192.168.4.10:3306    ::ffff:192.168.4.100:36998  ESTABLISHED 9026/mysqld
tcp        0      0 ::ffff:192.168.4.10:3306    ::ffff:192.168.4.20:43353   ESTABLISHED 9026/mysqld
.. ..

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

[root@pc120 ~]# netstat -anpt | grep mysql
tcp        0      0 192.168.4.100:3306          0.0.0.0:*                   LISTEN      2602/mysql-proxy
tcp        0      0 192.168.4.100:37121         192.168.4.10:3306           ESTABLISHED 2602/mysql-proxy
tcp        0      0 192.168.4.100:49001         192.168.4.20:3306           ESTABLISHED 2602/mysql-proxy
tcp        0      0 192.168.4.100:49003         192.168.4.20:3306           ESTABLISHED 2602/mysql-proxy
tcp        0      0 192.168.4.100:33536         192.168.4.100:3306          ESTABLISHED 5155/mysql
tcp        0      0 192.168.4.100:49006         192.168.4.20:3306           ESTABLISHED 2602/mysql-proxy
tcp        0      0 192.168.4.100:3306          192.168.4.100:33536         ESTABLISHED 2602/mysql-proxy

… …