使用mysql-proxy配置mysql读写分离
使用mysql-proxy配置mysql读写分离
简介
对于很多大型网站（pv值百万、千万）来说，在所处理的业务中，其中有70%的业务是查询（select）相关的业务操作（新闻网站，插入一条新闻。查询操作），剩下的则是写（insert、update、delete，只要能对MySQL的数据造成更改的操作都叫写操作）操作。在使用负载均衡集群之后，可以很大程度的提升网站的整体性能，但是最终的数据处理的压力还是会落到MySQL数据库上，所有很有必要使用一些技术来提升MySQL的负载能力。（读写分离）
写操作专门交给写服务器处理（一般网站来说写是比较少的 读写比 4:1） 那么需要把读的任务分配多台服务器来完成的架构，就叫做读写分离。
实现方式
第一种：php程序上自己做逻辑判断，写php代码的时候，自己在程序上做逻辑判读写匹配。select，insert、update、delete做正则匹配，根据结果选择写服务器（主服务器）。如果是select操作则选择读服务器（从服务器器） mysql_connect(‘读写的区分’)
第二种：MySQL- Proxy是实现”读写分离(Read/Write Splitting)”的一个软件（MySQL官方提供 ，也叫中间件），基本的原理是让主数据库处理写操作（insert、update、delete），而从数据库处理查询操作（select）。而数据库的一致性则通过主从复制来实现。所以说主从复制是读写分离的基础。
注意：MySQL-proxy它能实现读写语句的区分主要依靠的是内部一个lua脚本（能实现读写语句的判断）。
注意：如果只在主服务器（写服务器）上完成数据的写操作话；这个时候从服务器上没有执行写操作，是没有数据的。这个时候需要使用另外一个技术来实现主从服务器的数据一致性，这个技术叫做 主从复制技术。所以说主从复制是读写分离的基础。
优点： mysql proxy是一个处于你的client和mysql server端之间的简单程序，它可以监听、分析和改变它们的通信。它使用灵活，没有限制，常见用途：负载均衡，故障、查询分析，查询过滤和修改等等。
一、3台机器安装mysql
先把所有机器的防火墙全部关闭
# systemctl stop firewalld
# systemctl disable firewalld
# getenforce
Disabled# wget http://mirrors.sohu.com/mysql/MySQL-5.6/mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz
# tar zxvf mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz
# mv mysql-5.6.35-linux-glibc2.5-x86_64 /usr/local/mysql
# cd /usr/local/mysql
# useradd mysql
# mkdir -p /data/mysql
# chown -R mysql:mysql /data/mysql
# yum -y install libaio*
# yum -y install openssl-devel
# ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql
# echo $?
# cp support-files/my-default.cnf /etc/my.cnf
# cp support-files/mysql.server /etc/init.d/mysqld
# vi /etc/init.d/mysqld
vim编辑下面两行basedir和datadir配置
basedir=/usr/local/mysql
datadir=/data/mysql
# chmod 755 /etc/init.d/mysqld
# chkconfig --add mysqld
# chkconfig --list
# /etc/init.d/mysqld start
# ps aux |grep mysql
# netstat -lntp |grep mysql
# export PATH=$PATH:/usr/local/mysql/bin/
# vim /etc/profile
# source /etc/profile
# mysql -uroot二、配置基本信息
主zhdy-02（59.130）
vim /etc/my.cnf
server-id=130
log_bin=zhdy02
datadir=/data/mysql从zhdy-06（59.135）
vim /etc/my.cnf
server-id=135
datadir=/data/mysql从zhdy-07（59.136）
vim /etc/my.cnf
server-id=136
datadir=/data/mysql3台均需要重启服务：
# /etc/init.d/mysqld restart三、mysql主从授权
主zhdy-02（59.130）
mysql> grant replication slave on *.* to 'repl'@'192.168.59.135' identified by 'zhangduanya';
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to 'repl'@'192.168.59.136' identified by 'zhangduanya';
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| zhdy02.000001 | 542 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)从zhdy-06（59.135）
mysql> change master to master_host='192.168.59.130', master_user='repl', master_password='zhangduanya', master_log_file='zhdy02.000001', master_log_pos=542;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)从zhdy-07（59.136）
mysql> change master to master_host='192.168.59.130', master_user='repl', master_password='zhangduanya', master_log_file='zhdy02.000001', master_log_pos=542;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)直到出现两个“yes”即可！
Slave_IO_Running: Yes
Slave_SQL_Running: Yes错误：
2017-09-26 16:13:34 4681 [ERROR] Slave I/O: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work. Error_code: 1593原因分析：
mysql 5.6的复制引入了uuid的概念，各个复制结构中的server_uuid得保证不一样，但是查看到直接copy data文件夹后server_uuid是相同的，
show variables like '%server_uuid%';解决方法：
找到data文件夹下的auto.cnf文件，修改里面的uuid值，保证各个db的uuid不一样，重启db即可！！
四、主mysql（59.130）配置Mysql-proxy
4.1 下载
cd /usr/local/src
wget https://cdn.mysql.com/archives/mysql-proxy/mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz4.2 安装并配置环境变量
tar zxvf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
#因为是二进制包，所以解压后直接移动到指定位置即可
mv mysql-proxy-0.8.5-linux-el6-x86-64bit /usr/local/mysql-proxy
#配置环境变量
vim ~/.bash_profile
#加入以下内容
export PATH=$PATH:/usr/local/mysql-proxy/bin
source ~/.bash_profile4.3 配置mysql-proxy
mysql-proxy自带读写分离脚本，我们还需要修改一下
cp /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua /usr/local/mysql-proxy/lib/mysql-proxy/lua/
修改读写分离脚本：
vim /usr/local/mysql-proxy/lib/mysql-proxy/lua/rw-splitting.lua
--- config
--
-- connection pool
if not proxy.global.config.rwsplit then
proxy.global.config.rwsplit = {
min_idle_connections = 1,
max_idle_connections = 1,
is_debug = false
}
end- 默认最小4个（最大8个）以上客户端连接才会实现读写分离，改为最小1个最大1个，便于读写分离的测试
- 在读写分离测试时，需要开启多个窗口连接mysql-proxy，这是因为mysql-proxy会检查客户端连接，当连接没有超过min_idle_connections预设值时，不会进行读写分离，即查询操作会发生在master上。
4.4 设置LUA_PATH变量
在/etc/profile中添加LUA_PATH变量的值
vim /etc/profile
#加入以下内容
LUA_PATH="/usr/local/mysql-proxy/lib/mysql-proxy/lua/?.lua"
export LUA_PATH
执行source /etc/profile该变量可以通过mysql-proxy -V查看
[[email protected]02 src]# mysql-proxy -V
mysql-proxy 0.8.5
chassis: 0.8.5
glib2: 2.16.6
libevent: 2.0.21-stable
LUA: Lua 5.1.4
package.path: /usr/local/mysql-proxy/lib/mysql-proxy/lua/?.lua;/usr/local/mysql-proxy/lib/mysql-proxy/lua/?.lua
package.cpath: /usr/local/mysql-proxy/lib/mysql-proxy/lua/?.so;
-- modules
proxy: 0.8.5帮助：
参数 | 作用 |
|---|---|
–proxy-address | 指定代理服务监听的地址和端口（默认是4040），简写为-P |
–admin-address | 指定管理主机地址和端口（默认是4041） |
–proxy-backend-addresses | 指定后端mysql服务器的地址和端口（master），简写为-b |
–proxy-read-only-backend-addresses | 指定后端只读mysql服务器的地址和端口（slave），缩为-r |
–proxy-lua-script | 指定mysql代理功能的Lua脚本文件 |
–daemon | 以守护进程模式启动mysql-proxy |
–defaults-file | 指定配置文件路径 |
–log-file | 指定日志文件路径 |
–log-level | 指定日志级别有error,warning,info,message,debug |
–user | 指定运行mysql-proxy进程的用户 |
–admin-username | 指定登录到mysql-proxy管理界面的用户名 |
–admin-password | 指定登录到mysql-proxy管理界面的用户密码 |
–admin-lua-script=script-file | 管理模块的lua脚本文件路径（创建管理接口） |
–plugins | 加载插件（admin,proxy） |
–proxy-lua-script | 指定读写分离脚本位置 |
–admin-lua-script | 指定管理界面脚本位置 |
4.5 启动MYSQL-PROXY
启动时，我们需要什么功能就指定相应参数：
mysql-proxy --daemon --log-level=debug \
--log-file=/var/log/mysql-proxy.log \
--plugins=proxy -b 192.168.59.130:3306 -r 192.168.59.135:3306 -r 192.168.59.136:3306 \
--proxy-lua-script=/usr/local/mysql-proxy/lib/mysql-proxy/lua/rw-splitting.lua \
--plugins=admin --admin-username=admin \
--admin-password=admin \
--admin-lua-script=/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua- 需要指定plugins=proxy才能开启代理功能，同理，需要指定plugins=admin才能使用管理功能。
4.6 查看端口
4.7 查看启动日志
[[email protected] src]# tail -f /var/log/mysql-proxy.log
2017-09-26 16:55:10: (critical) plugin proxy 0.8.5 started
2017-09-26 16:55:10: (critical) plugin admin 0.8.5 started
2017-09-26 16:55:10: (debug) max open file-descriptors = 1024
2017-09-26 16:55:10: (message) proxy listening on port :4040
2017-09-26 16:55:10: (message) added read/write backend: 192.168.59.130:3306
2017-09-26 16:55:10: (message) added read-only backend: 192.168.59.135:3306
2017-09-26 16:55:10: (message) added read-only backend: 192.168.59.136:3306
2017-09-26 16:55:10: (message) admin-server listening on port :40414.8 停止MYSQL-PROXY
ps aux |grep "mysql-proxy"
killall -9 mysql-proxy五、读写分离测试
5.1、创建并授权MYSQL-PROXY登录用户
登录master 和 slave，执行以下sql语句
mysql -uroot
> grant all on *.* to 'proxy'@'%' identified by 'zhangduanya';
> flush privileges;5.2、登录MYSQL-PROXY管理后台查看状态
查看master和slave的状态
mysql -uadmin -padmin -h192.168.59.130 -P4041
mysql> SELECT * FROM backends;
+-------------+---------------------+---------+------+------+-------------------+
| backend_ndx | address | state | type | uuid | connected_clients |
+-------------+---------------------+---------+------+------+-------------------+
| 1 | 192.168.59.130:3306 | unknown | rw | NULL | 0 |
| 2 | 192.168.59.135:3306 | unknown | ro | NULL | 0 |
| 3 | 192.168.59.136:3306 | unknown | ro | NULL | 0 |
+-------------+---------------------+---------+------+------+-------------------+
3 rows in set (0.00 sec)- 之所以是unknown状态，是因为我们还没有客户端连接，我们多开几个会话后，重新查看状态。
此时，我们的服务已经搭建，接下来我们来进行测试
六、总结
在上述环境中，mysql-master存在单点故障。如果在可用性要求较高的场合，单点隐患是绝对不允许的。为了避免mysql-proxy单点隐患有两种方法;
一种方法是：mysql-proxy配合keepalived做双机。
另一种方法是：为了避免mysql-master单点故障可以使用DRBD+heartbear做双机。
避免mysql-slave单点故障增加多台mysql-slave即可，因为mysql-proxy会自动屏蔽后端发生故障的mysql-slave。