mysql读写分离场景_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]: 实现主从复制

首先在server1(主)和server2(从)上搭建主从复制架构

这里我只将搭建成功的结果图附上，具体操作不再赘述

测试：主从复制

在server1(主)上创建库，进入库，创建表，插入表信息

server2(从)上可以看到表信息，则主从复制成功

在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]: 配置代理端

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

[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

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

[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

将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里面已经集成，无需再安装

创建配置文件

[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

给文件设置权限，再启动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

此时查看端口3306打开

[root@server3 conf]# netstat -anltp

1

[测试]

在物理机上安装mysql客户端

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

1

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

测试1：

master和slave状态都开启

在物理机上通过server3连接数据库，向数据库的表中插入新的数据信息

3.在server1(主)和server2(从)上都可以看到添加的信息

注意：

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

测试2：

关闭server2(slave)

在物理机添加新的信息，却不能select出来，而在master上可以查看到

说明：读写分离了，读操作是在server2(slave)上

测试3：

关掉server1(master)，开启server2的slave

Server1：

Server2：

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

说明：读写分离了，写操作在server1(master)上

测试4：

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

server3上安装tcpdump抓包工具

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

1

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

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

1

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

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