利用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
实验环境：
server1 172.25.66.1 master
server2 172.25.66.2 slave
server3 172.25.66.3 mysql-proxy
实验前提：
已经在server1（master）和server2（slave）结点上做好了主从复制（传统的或是基于GTID的均可）
检测主从复制:
[[email protected] mysql]# mysql -uroot -p'mpqil;&fg32C'
#查看slave的状态
mysql> show slave status\G;
在server1上：创建数据库
[[email protected] mysql]# mysql -u root -p'7Hv,%)q8gp.2'
mysql> show databases;
mysql> create database westos;
mysql> show databases;
在server2上：发现数据同步
[[email protected] mysql]# mysql -u root -p'jqwyk?/Nq1<.'
mysql> show databases;
实验：
配置proxy端：
1.下载mysql-proxy并解压
#1.在官网上下载mysql-proxy
[[email protected] ~]# ls
mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
#2.解压
[[email protected] ~]# tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
[[email protected] ~]# ls
mysql-proxy-0.8.5-linux-el6-x86-64bit
mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
[[email protected] ~]# cd mysql-proxy-0.8.5-linux-el6-x86-64bit
[[email protected] mysql-proxy-0.8.5-linux-el6-x86-64bit]# ls
bin include lib libexec licenses share
[[email protected] mysql-proxy-0.8.5-linux-el6-x86-64bit]# cd
2.移动mysql-proxy
[[email protected] ~]# mv mysql-proxy-0.8.5-linux-el6-x86-64bit /usr/local/
[[email protected] ~]# cd /usr/local/
[[email protected] local]# ls
bin games lib libexec sbin src
etc include lib64 mysql-proxy-0.8.5-linux-el6-x86-64bit share
3.制作软连接
#制作软连接是为了便于升级
[[email protected] local]# ln -s mysql-proxy-0.8.5-linux-el6-x86-64bit/ mysql-proxy
[[email protected] local]# ll
[[email protected] local]# cd mysql-proxy
[[email protected] mysql-proxy]# ls
bin include lib libexec licenses share
[[email protected] mysql-proxy]# cd bin/
[[email protected] bin]# pwd
/usr/local/mysql-proxy/bin
[[email protected] bin]# ls
mysql-binlog-dump mysql-myisam-dump mysql-proxy #
4.更改环境变量
#1.更改环境变量；更改环境变量是为了方便直接使用mysql-proxy命令
[[email protected] ~]# vim ~/.bash_profile
################
PATH=$PATH:$HOME/bin:/usr/local/mysql-proxy/bin
#2.刷新配置
[[email protected] ~]# source ~/.bash_profile
5.更改lua脚本
在实际生产环境中，不需要修改，我们这里修改是为了方便测试
#lua脚本的默认存放位置
[[email protected] ~]# cd /usr/local/mysql-proxy/share/doc/mysql-proxy
[[email protected] mysql-proxy]# ls
[[email protected] mysql-proxy]# vim rw-splitting.lua
#######################
min_idle_connections = 1,
max_idle_connections = 2, #最大连接数；即当连接数超过2的时候开始读写分离
6.建立目录
[[email protected] mysql-proxy]# cd /usr/local/mysql-proxy
[[email protected] mysql-proxy]# ls
bin conf include lib libexec licenses share
#用于存放配置文件
[[email protected] mysql-proxy]# mkdir conf
#用于存放日志文件
[[email protected] mysql-proxy]# mkdir logs
7.编写配置文件
注意：每行语句后面不能有空格，否则将会无法识别，此时mysql-proxy无法启动
[[email protected] mysql-proxy]# cd conf/
[[email protected] conf]# pwd
/usr/local/mysql-proxy/conf
[[email protected] 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 #以守护进程方式运行（打入后台）
#启动报错；提示更改权限
[[email protected] conf]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf
7.更改权限
[[email protected] conf]# ll mysql-proxy.conf
-rw-r--r-- 1 root root 318 Feb 25 15:33 mysql-proxy.conf
#更改权限
[[email protected] conf]# chmod 660 mysql-proxy.conf
[[email protected] conf]# ll
total 4
-rw-rw---- 1 root root 318 Feb 25 15:33 mysql-proxy.conf
8.启动mysql-proxy
#1.启动，可以不用加绝对路径，因为已经添加了环境变量
[[email protected] conf]# mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf
#2.查看端口
[[email protected] conf]# netstat -antlp
#3.查看进程；发现会产生两个进程
[[email protected] conf]# ps aux | grep mysql-proxy
查看日志和pid：
[[email protected] conf]# cd /usr/local/mysql-proxy/logs/
[[email protected] logs]# ls
mysql-proxy.log mysql-proxy.pid
#1.查看日志
[[email protected] logs]# cat mysql-proxy.log
#2.查看pid
[[email protected] logs]# cat mysql-proxy.pid
4251
配置master端：
用户授权：
[[email protected] ~]# mysql -uroot -p'hym19970818HYM#'
#创建hym用户并授予所有数据库的所有表有创建，插入，查询权限
mysql> grant create,insert,select on *.* to [email protected]'%' identified by 'hym19970818HYM#';
#刷新授权表
mysql> flush privileges;
测试：
1.在物理机上远程登陆server3代理，发现数据同步（一直连接不断开）
注意：远程连接的用户必须能登陆主从数据库
#1.安装数据库
[[email protected] ~]# yum install -y mariadb
#2.远程登陆
[[email protected] Desktop]# mysql -h 172.25.66.3 -u hym -p'hym19970818HYM#'
MySQL [(none)]> show databases;
2.查看端口使用信息，发现此时连接的是server1结点
#1.安装lsof
[[email protected] ~]# yum install -y lsof
#2.列出端口使用信息
[[email protected] ~]# lsof -i :3306
3.在物理机上再开启一个shell远程连接server3代理（一直连接不断开）
[[email protected] Desktop]# mysql -h 172.25.66.3 -u hym -p'hym19970818HYM#'
MySQL [(none)]> show databases;
4.查看端口使用信息，发现依旧连接的是server1结点
[[email protected] ~]# lsof -i :3306
5.在物理机上再开启一个shell远程连接server3代理（一直连接不断开）
[[email protected] ~]$ mysql -h 172.25.66.3 -u hym -p'hym19970818HYM#'
MySQL [(none)]> show databases;
6.查看端口使用信息，发现连接切换到了server2结点上，因为已超过了两个连接，此时开始读写分离
#列出端口使用信息
[[email protected] ~]# 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;