mysql读写分离

一、什么是读写分离？

• MySQL读写分离是指让master处理写操作，让slave处理读操作，非常适用于读操作量比较大的场景，可减轻master的压力。
• 使用mysql-proxy实现mysql的读写分离，mysql-proxy实际上是作为后端mysql主从服务器的代理，它直接接受客户端的请求，对SQL语句进行分析，判断出是读操作还是写操作，然后分发至对应的mysql服务器上。

二、为什么要读写分离？

• 因为数据库的写操作相对读操作是比较耗时的，所以数据库的读写分离，解决的是数据库的写入，影响了查询的效率。

三、实验环境：

• rhel7.3 selinux and firewalld disabled
• server1:172.25.79.1（master）
• server2:172.25.79.2（slave）
• server3:172.25.79.3（mysql-proxy）

四、搭建步骤

[server1和server2]: 实现主从复制

1. 首先在server1（主）和server2（从）上搭建主从复制架构
   这里我只将搭建成功的结果图附上，具体操作不再赘述
   

测试：主从复制

• 在server1（主）上创建库，进入库，创建表，插入表信息
  
• server2（从）上可以看到表信息，则主从复制成功
  

2. 在master上授权（授予全部权限，但是仅仅是实验环境所与不太在意安全）

mysql> grant all privileges on *.* to 'root'@'%' identified by 'HUIyange+32';
Query OK, 0 rows affected, 1 warning (0.03 sec)

• 1
• 2

[server3]: 配置代理端

1. 下载安装包并解压到指定目录

[root@server3 ~]# tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz  
[root@server3 ~]# mv mysql-proxy-0.8.5-linux-el6-x86-64bit /usr/local/mysql-proxy

• 1
• 2

2. 建立目录存放读写分离的配置文件和日志

[root@server3 ~]# cd /usr/local/mysql-proxy/
[root@server3 mysql-proxy]# ls
bin  include  lib  libexec  licenses  share
[root@server3 mysql-proxy]# mkdir conf
[root@server3 mysql-proxy]# mkdir logs

• 1
• 2
• 3
• 4
• 5

3. 将mysql-proxy的二进制命令放进系统环境变量中

[root@server3 mysql-proxy]# vim ~/.bash_profile
PATH=$PATH:$HOME/bin:/usr/local/mysql-proxy/bin
[root@server3 mysql-proxy]# source ~/.bash_profile

• 1
• 2
• 3

4. 修改数据库发生读写分离时的最大最小值

[root@server3 ~]# cd /usr/local/mysql-proxy/share/doc/mysql-proxy
[root@server3 mysql-proxy]# vim rw-splitting.lua
 40                 min_idle_connections = 1,  ##最小连接数
 41                 max_idle_connections = 2,  ##最大连接数，最大连接数大于2时发生读写分离

• 1
• 2
• 3
• 4

• 实现读写分离是有lua脚本实现的，现在mysql-proxy里面已经集成，无需再安装
  

5. 创建配置文件

[root@server3 ~]# cd /usr/local/mysql-proxy/conf/
[root@server3 conf]# vim mysql-proxy.conf
[mysql-proxy]
user=root        ##运行mysql-proxy用户
proxy-address=0.0.0.0:3306   ##mysql-proxy运行ip和端口proxy-read-only-backend-addresses=172.25.79.2:3306   ##slave：只读
proxy-backend-addresses=172.25.79.1:3306    ##master：可读写
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua     ##lua脚本地址
log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log   #日志位置
log-level=debug  #定义log日志级别，由高到低分别有(error|warning|info|message|debug)
daemon=true   ##打入后台
keepalive=true   ##mysql-proxy崩溃时，尝试重启（持续连接）

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11

6. 给文件设置权限，再启动mysql-proxy（否则会启动失败）

[root@server3 conf]# chmod 660 /usr/local/mysql-proxy/conf/mysql-proxy.conf

启动：
[root@server3 conf]# mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf  

• 1
• 2
• 3
• 4

7. 此时查看端口3306打开

[root@server3 conf]# netstat -anltp

• 1

[测试]

1. 在物理机上安装mysql客户端

[root@foundation79 ~]# yum install mysql -y

• 1

2. 在物理机上打开三个shell，都通过server3连接数据库进行如下操作：
   

测试1：

1. master和slave状态都开启
2. 在物理机上通过server3连接数据库，向数据库的表中插入新的数据信息
   
   3.在server1（主）和server2（从）上都可以看到添加的信息
   

• 注意：
  在master端可以看到数据，说明写操作是在server1（master）上，而在server2上看到数据，是因为server1和server2是主从复制关系。（不能说明server2可以进行写操作）

测试2：

1. 关闭server2（slave）
   
2. 在物理机添加新的信息，却不能select出来，而在master上可以查看到
   

• 说明：读写分离了，读操作是在server2（slave）上

测试3：

1. 关掉server1（master），开启server2的slave

• Server1：
  
• Server2：
  

3. 在物理机上连接数据库，发现插入数据失败
   

• 说明：读写分离了，写操作在server1（master）上

测试4：

• 还可以通过tcpdump抓取数据读写分离

1. server3上安装tcpdump抓包工具

[root@server3 conf]# yum install -y tcpdump

• 1

2. server3上执行以下命令：可以看到客户端登陆mysql-proxy的操作

[root@server3 conf]# tcpdump -i eth0 port 3306

• 1

3. 执行上一条命令后不要关掉，然后在物理机上连接数据库（连接3个），然后给表中插入一条新的信息后，在server3上看到相应的抓包信息
   

• 发现当连接3个物理机后，执行写操作是通过server3写入到server2（msater）中的