什么是读写分离？
MySQL读写分离是指让master处理写操作，让slave处理读操作，非常适用于读操作量比较大的场景，可减轻master的压力。(例如淘宝网站，大部分人进入网站后都是读）
使用mysql-proxy实现mysql的读写分离，mysql-proxy实际上是作为后端mysql主从服务器的代理，它直接接受客户端的请求，对SQL语句进行分析，判断出是读操作还是写操作，然后分发至对应的mysql服务器上。
为什么要读写分离？
因为数据库的写操作相对读操作是比较耗时的，所以数据库的读写分离，解决的是数据库的写入，影响了查询的效率。
实验开始前规定
server3(172.25.34.4)作为主库，server4(172.25.34.5)作为从库，server5(172.25.254.6)作为中间件
首先在server3（主）和server4（从）上搭建主从复制架构,参考之前的博客。
server5（中间件）:
下载安装包并解压到指定目录
[root@server5 ~]# ls
mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
[root@server5 ~]# tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local/生成软连接
[root@server5 ~]# cd /usr/local/
[root@server5 local]# ls
bin games lib libexec sbin src
etc include lib64 mysql-proxy-0.8.5-linux-el6-x86-64bit share
[root@server5 local]# ln -s mysql-proxy-0.8.5-linux-el6-x86-64bit/ mysql-proxy
[root@server5 local]# ls
bin games lib libexec mysql-proxy-0.8.5-linux-el6-x86-64bit share
etc include lib64 mysql-proxy sbin src建立目录 存放配置文件和日志（安装后需要自己创建目录和文件）
[root@server5 local]# cd mysql-proxy
[root@server5 mysql-proxy]# ls
bin conf include lib libexec licenses share
[root@server5 bin]# mkdir log
[root@server5 mysql-proxy]# mkdir conf ##创建存放配置文件的目录
[root@server5 mysql-proxy]# cd conf/
[root@server5 conf]# vi mysql-proxy.conf #创建配置文件
[mysql-proxy]
proxy-address=0.0.0.0:3306 ##mysql-proxy运行ip和端口
proxy-backend-addresses=172.25.34.4:3306 ##master：可读写
proxy-read-only-backend-addresses=172.25.34.5:3306 ##slave：只读
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua ##lua脚本地址
pid-file=/usr/local/mysql-proxy/log/mysql-proxy.pid
plugins=proxy
log-file=/usr/local/mysql-proxy/log/mysql-proxy.log #日志位置
log-level=debug #定义log日志级别，由高到低分别有(error|warning|info|message|debug)
keepalive=true ##mysql-proxy崩溃时，尝试重启（持续连接）
daemon=true修改数据库发生读写分离时的最大最小值
[root@server5 conf]# vi /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
40 min_idle_connections = 1, ##最小连接数
41 max_idle_connections = 2, ##最大连接数，最大连接数大于2时发生读写分离给文件设置权限，再启动mysql-proxy（否则会启动失败）
[root@server5 conf]# cd /usr/local/mysql-proxy/bin/
[root@server5 bin]# chmod 660 /usr/local/mysql-proxy/conf/mysql-proxy.conf
[root@server5 bin]#/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf查看日志是否配置成功
[root@server5 bin]# cat /usr/local/mysql-proxy/log/mysql-proxy.log
2019-10-20 14:36:05: (message) chassis-unix-daemon.c:136: [angel] we try to keep PID=2101 alive
2019-10-20 14:36:05: (debug) chassis-unix-daemon.c:157: waiting for 2101
2019-10-20 14:36:05: (debug) chassis-unix-daemon.c:121: we are the child: 2101
2019-10-20 14:36:05: (critical) plugin proxy 0.8.5 started
2019-10-20 14:36:05: (debug) max open file-descriptors = 1024
2019-10-20 14:36:05: (message) proxy listening on port 0.0.0.0:3306
2019-10-20 14:36:05: (message) added read/write backend: 172.25.34.4:3306
2019-10-20 14:36:05: (message) added read-only backend: 172.25.34.5:3306server3(主库）:
授权用户，允许登陆的用户有对数据库更改权限：
mysql> grant insert,update,select on *.* to dd@'%' identified by'Westos+001';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)客户机：
安装mysql客户端
[kiosk@foundation34 Desktop]$ yum whatprovides */mysql
[kiosk@foundation34 Desktop]$ yum install mariadb-5.5.52-1.el7.x86_64 -y客户端登陆写入数据
注意：由于连接数限制，我们可以在客户机上打开多个shell进行连接测试，激活读写分离功能。
[root@foundation34 Desktop]# mysql -h 172.25.34.6 -udd -pWestos+001
MySQL [(none)]> use westos;
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 [westos]> insert into userlist values ('usr3','789');
Query OK, 1 row affected (0.01 sec)
MySQL [westos]> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 456 |
| usr3 | 789 |
+----------+----------+
3 rows in set (0.01 sec)测试：
server5检测：
lsof
[root@server5 ~]# yum search lsof
Loaded plugins: product-id, search-disabled-repos, subscription-manager
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
=============================== N/S matched: lsof ===============================
lsof.x86_64 : A utility which lists open files on a Linux/UNIX system
Name and summary matches only, use “search all” for everything.
[root@server3 ~]# yum install lsof.x86_64 -y
server3
查看数据库是否插入
mysql> use westos;
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> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 456 |
| usr3 | 789 |
+----------+----------+
3 rows in set (0.00 sec)注意：在master端可以看到数据，说明写操作是在server3（master）上，而在server4上看到数据，是因为server3和server4是主从复制关系。（不能说明server4可以进行写操作）
如果想要验证写操作，确实是在master上进行的，可以关闭备库上的slave。再次插入数据，在备库上就无法查看。
