利用mysql-proxy实现MySQL的读写分离

概述： 1.什么是读写分离？

读写分离，基本的原理是让主数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE），而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。

2.为什么要读写分离？

因为数据库的“写”（写10000条数据到oracle可能要3分钟）操作是比较耗时的。但是数据库的“读”（从oracle读10000条数据可能只要5秒钟）操作比较快速。 所以读写分离，解决的是：数据库的写入，影响了查询的效率。

3.什么时候要读写分离？

数据库不一定要读写分离，如果程序使用数据库较多时，而更新少，查询多的情况下会考虑使用，利用数据库主从同步 。 可以减少数据库压力，提高性能。当然，数据库也有其它优化方案。memcache或是表折分，或是搜索引擎，都是解决方法。

4.如何实现mysql的读写分离？

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

安装包：

mysql-5.7.24-1.el7.x86_64.rpm-bundle.tar mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz

点击此处下载MySQL-proxy

实验环境：

server1 172.25.66.1 master server2 172.25.66.2 slave server3 172.25.66.3 mysql-proxy

实验前提：

已经在server1（master）和server2（slave）结点上做好了主从复制（传统的或是基于GTID的均可）

点击此处查看MySQL主从复制 检测主从复制:

#查看slave的状态 mysql> show slave status\G;

在server1上：创建数据库

mysql> show databases; mysql> create database westos; mysql> show databases;

在server2上：发现数据同步

mysql> show databases;

实验： 配置proxy端：

1.下载mysql-proxy并解压

#1.在官网上下载mysql-proxy [root@server3 ~]# ls mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz #2.解压 [root@server3 ~]# tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz [root@server3 ~]# ls mysql-proxy-0.8.5-linux-el6-x86-64bit mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz [root@server3 ~]# cd mysql-proxy-0.8.5-linux-el6-x86-64bit [root@server3 mysql-proxy-0.8.5-linux-el6-x86-64bit]# ls bin include lib libexec licenses share [root@server3 mysql-proxy-0.8.5-linux-el6-x86-64bit]# cd

2.移动mysql-proxy

[root@server3 ~]# mv mysql-proxy-0.8.5-linux-el6-x86-64bit /usr/local/ [root@server3 ~]# cd /usr/local/ [root@server3 local]# ls bin games lib libexec sbin src etc include lib64 mysql-proxy-0.8.5-linux-el6-x86-64bit share

3.制作软连接

#制作软连接是为了便于升级 [root@server3 local]# ln -s mysql-proxy-0.8.5-linux-el6-x86-64bit/ mysql-proxy [root@server3 local]# ll

[root@server3 local]# cd mysql-proxy [root@server3 mysql-proxy]# ls bin include lib libexec licenses share [root@server3 mysql-proxy]# cd bin/ [root@server3 bin]# pwd /usr/local/mysql-proxy/bin [root@server3 bin]# ls mysql-binlog-dump mysql-myisam-dump mysql-proxy #

4.更改环境变量

#1.更改环境变量；更改环境变量是为了方便直接使用mysql-proxy命令 [root@sever3 ~]# vim ~/.bash_profile ################ PATH=$PATH:$HOME/bin:/usr/local/mysql-proxy/bin

#2.刷新配置 [root@sever3 ~]# source ~/.bash_profile

5.更改lua脚本

在实际生产环境中，不需要修改，我们这里修改是为了方便测试

#lua脚本的默认存放位置 [root@sever3 ~]# cd /usr/local/mysql-proxy/share/doc/mysql-proxy [root@server3 mysql-proxy]# ls [root@server3 mysql-proxy]# vim rw-splitting.lua ####################### min_idle_connections = 1, max_idle_connections = 2, #最大连接数；即当连接数超过2的时候开始读写分离

6.建立目录

[root@server3 mysql-proxy]# cd /usr/local/mysql-proxy [root@server3 mysql-proxy]# ls bin conf include lib libexec licenses share #用于存放配置文件 [root@sever3 mysql-proxy]# mkdir conf #用于存放日志文件 [root@sever3 mysql-proxy]# mkdir logs

7.编写配置文件 注意：每行语句后面不能有空格，否则将会无法识别，此时mysql-proxy无法启动

[root@sever3 mysql-proxy]# cd conf/ [root@server3 conf]# pwd /usr/local/mysql-proxy/conf [root@sever3 conf]# vim mysql-proxy.conf ################### [mysql-proxy] user=root #指定用户（可以不写） proxy-address=0.0.0.0:3306 #监听所有ip地址的3306端口 proxy-backend-addresses=172.25.66.1:3306 #指定master读写数据 proxy-read-only-backend-addresses=172.25.66.2:3306 #指定slav只读数据 proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua #指定读写分离配置文件位置（lua脚本） pid-file=/usr/local/mysql-proxy/logs/mysql-proxy.pid #指定存放pid文件路径 log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log #指定存放日志文件路径 log-level=debug #指定日志级别，由高到低分别有(error|warning|info|message|debug) plugins=proxy #添加插件（模块） keepalive=true #mysql-proxy崩溃时，尝试重启（保持长连接） daemon=true #以守护进程方式运行（打入后台）

#启动报错；提示更改权限 [root@server3 conf]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf

7.更改权限

[root@sever3 conf]# ll mysql-proxy.conf -rw-r--r-- 1 root root 318 Feb 25 15:33 mysql-proxy.conf #更改权限 [root@sever3 conf]# chmod 660 mysql-proxy.conf [root@sever3 conf]# ll total 4 -rw-rw---- 1 root root 318 Feb 25 15:33 mysql-proxy.conf

8.启动mysql-proxy

#1.启动，可以不用加绝对路径，因为已经添加了环境变量 [root@sever3 conf]# mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf #2.查看端口 [root@sever3 conf]# netstat -antlp

#3.查看进程；发现会产生两个进程 [root@server3 conf]# ps aux | grep mysql-proxy

查看日志和pid：

[root@server3 conf]# cd /usr/local/mysql-proxy/logs/ [root@server3 logs]# ls mysql-proxy.log mysql-proxy.pid #1.查看日志 [root@server3 logs]# cat mysql-proxy.log #2.查看pid [root@server3 logs]# cat mysql-proxy.pid 4251

配置master端： 用户授权：

[root@sever1 ~]# mysql -uroot -pWestos+001 #创建hym用户并授予所有数据库的所有表有创建，插入，查询权限 mysql> grant create,insert,select on *.* to hym@'%' identified by 'Westos+001'; #刷新授权表 mysql> flush privileges;

测试： 1.在物理机上远程登陆server3代理，发现数据同步（一直连接不断开） 注意：远程连接的用户必须能登陆主从数据库 #1.安装数据库

[root@foundation66 ~]# yum install -y mariadb #2.远程登陆 [root@foundation66 Desktop]# mysql -h 172.25.66.3 -u hym -p'Westos+001' MySQL [(none)]> show databases;

2.查看端口使用信息，发现此时连接的是server1结点

#1.安装lsof [root@server3 ~]# yum install -y lsof #2.列出端口使用信息 [root@sever3 ~]# lsof -i :3306

3.在物理机上再开启一个shell远程连接server3代理（一直连接不断开）

[root@foundation66 Desktop]# mysql -h 172.25.66.3 -u hym -p'Westos+001' MySQL [(none)]> show databases;

4.查看端口使用信息，发现依旧连接的是server1结点

[root@server3 ~]# lsof -i :3306

5.在物理机上再开启一个shell远程连接server3代理（一直连接不断开）

[kiosk@foundation66 ~]$ mysql -h 172.25.66.3 -u hym -p'Westos+001' MySQL [(none)]> show databases;

6.查看端口使用信息，发现连接切换到了server2结点上，因为已超过了两个连接，此时开始读写分离

#列出端口使用信息 [root@sever3 ~]# lsof -i :3306

7.在远程连接端：插入数据，操作成功

MySQL [(none)]> show databases; MySQL [(none)]> use westos; MySQL [westos]> create table userlist ( -> username varchar(20) not null, -> password varchar(15) not null); MySQL [westos]> insert into userlist values ('user1','111'); MySQL [westos]> select * from userlist;

8.在server1上：发现可以查看到数据

由于设定的是server2结点（slave）只读，server1结点（master）读写，所以此时server3代理会调度server1结点来执行插入（写）操作，即实现了数据库的读写分离

mysql> show databases; mysql> use westos mysql> select * from userlist;