Mysql读写分离--mysql-proxy
准备前提：
1、MySQL的安装与配置 --省略
版本：Server version: 5.6.35
1.1 系统版本：
[root@centos7-67 package]# lsb_release -a
LSB Version: :core-4.1-amd64:core-4.1-noarch
Distributor ID: CentOS
Description: CentOS Linux release 7.2.1511 (Core)
Release: 7.2.1511
Codename: Core
2、场景描述
数据库Master主服务器：192.168.1.83
数据库Slave从服务器：192.168.1.66
MySQL-Proxy调度服务器：192.168.1.67
以下操作，均是在192.168.1.67即MySQL-Proxy调度服务器 上进行的。
配置主从复制，并在192.168.1.66上开启 start slave;
MySQL-Proxy上安装所需软件包
yum install -y gcc* gcc-c++* autoconf* automake* zlib* libxml* ncurses-devel* libmcrypt* libtool* flex* pkgconfig* libevent* glib* readline-devel*
3、编译安装lua
MySQL-Proxy的读写分离主要是通过rw-splitting.lua脚本实现的，因此需要安装lua
下载：
从http://www.lua.org/download.html下载源码包
wget http://www.lua.org/ftp/lua-5.3.4.tar.gz
3.1 tar xf lua-5.3.4.tar.gz
3.2 cd lua-5.3.4
3.3 vi src/Makefile
CFLAGS= -O2 -Wall -fPIC -Wextra -DLUA_COMPAT_5_2 $(SYSCFLAGS) $(MYCFLAGS)
3.4 make linux
3.5 make install
下载：
https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz
4、tar xf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz
5、mv mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit /usr/local/mysql-proxy
6、cd /usr/local/mysql-proxy
7、mkdir lua #创建脚本存放目录
mkdir logs #创建日志目录
8、cp share/doc/mysql-proxy/rw-splitting.lua ./lua #复制读写分离配置文件
9、cp share/doc/mysql-proxy/admin-sql.lua ./lua #复制管理脚本
10、vi /etc/init.d/mysql-proxy #创建mysql-proxy服务管理脚本
[root@centos7-67 ~]# cat /etc/init.d/mysql-proxy
#!/bin/sh
# mysql-proxy This script starts and stops the mysql-proxy daemon
#
# chkconfig: - 78 30
# processname: mysql-proxy
# description: mysql-proxy is a proxy daemon to mysql
# Source function library.
. /etc/rc.d/init.d/functions
#PROXY_PATH=/usr/local/bin
PROXY_PATH=/usr/local/mysql-proxy/bin
prog="mysql-proxy"
# Source networking configuration.
. /etc/sysconfig/network
# Check that networking is up.
#[ ${NETWORKING} == "no" ] && exit 0
# Set default mysql-proxy configuration.
PROXY_OPTIONS="--log-level=info
--log-file=/var/log/mysql-proxy.log
--plugins=proxy -b 192.168.1.83:3306 -r 192.168.1.66:3306
--proxy-lua-script=/usr/local/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"
PROXY_PID=/usr/local/mysql-proxy/mysql-proxy.pid
# Source mysql-proxy configuration.
if [ -f /etc/sysconfig/mysql-proxy ]; then
. /etc/sysconfig/mysql-proxy
fi
PATH=$PATH:/usr/bin:/usr/local/bin:$PROXY_PATH
# By default it's all good
RETVAL=0
# See how we were called.
case "$1" in
start)
# Start daemon.
echo -n $"Starting $prog: "
$NICELEVEL $PROXY_PATH/mysql-proxy $PROXY_OPTIONS --daemon --pid-file=$PROXY_PID --user=mysql
RETVAL=$?
echo
if [ $RETVAL = 0 ]; then
touch /var/lock/subsys/mysql-proxy
fi
;;
stop)
# Stop daemons.
echo -n $"Stopping $prog: "
killproc $prog
RETVAL=$?
echo
if [ $RETVAL = 0 ]; then
rm -f /var/lock/subsys/mysql-proxy
rm -f $PROXY_PID
fi
;;
restart)
$0 stop
sleep 3
$0 start
;;
condrestart)
[ -e /var/lock/subsys/mysql-proxy ] && $0 restart
;;
status)
status mysql-proxy
RETVAL=$?
;;
*)
echo "Usage: $0 {start|stop|restart|status|condrestart}"
RETVAL=1
;;
esac
exit $RETVAL
11、脚本更改说明：
PROXY_PATH=/usr/local/mysql-proxy/bin//定义mysql-proxy服务二进制文件路径
--proxy-read-only-backend-addresses=192.168.1.66:3306 //定义从服务器可读
--proxy-backend-addresses=192.168.1.83:3306 //定义主服务器可写 --proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua"//定义lua读写分离脚本路径
PROXY_PID=/usr/local/mysql-proxy/mysql-proxy.pid//定义mysql-proxy PID文件路径
--daemon //定义以守护进程模式启动
--keepalive //使进程在异常关闭后能够自动恢复
--pid-file=$PROXY_PID //定义mysql-proxy PID文件路径
--user=mysql //以mysql用户身份启动服务
--log-level=warning //定义log日志级别，由高到低分别有(error|warning|info|message|debug)
--log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log //定义log日志文件路径
12、chmod 755 /etc/init.d/mysql-proxy
13、vi /usr/local/mysql-proxy/lua/rw-splitting.lua //修改读写分离脚本//修改默认连接，进行快速测试，不修改的话要达到连接数为4时才启用读写分离
-- connection pool
if not proxy.global.config.rwsplit then
proxy.global.config.rwsplit = {
min_idle_connections = 1,//默认为4
max_idle_connections = 1,//默认为8
is_debug = false
}
end
14、启动mysql-proxy
/etc/init.d/mysql-proxy start
15、启动成功：
[root@centos7-67 ~]# netstat -nutlp| grep mysql
tcp 0 0 0.0.0.0:4040 0.0.0.0:* LISTEN 18520/mysql-proxy
tcp 0 0 0.0.0.0:4041 0.0.0.0:* LISTEN 18520/mysql-proxy
16、测试读写分离效果
创建用于读写分离的数据库连接用户
登陆主数据库服务器192.168.1.83，通过命令行登录管理MySQL服务器
mysql> GRANT ALL ON *.* TO 'proxy1'@'192.168.1.67' IDENTIFIED BY 'password';
由于我们配置了主从复制功能，因此从数据库服务器172.16.1.66上已经同步此操作。
为了清晰的看到读写分离的效果，需要暂时关闭MySQL主从复制功能；线上是需要主从同步的，这里只是做实验测试，需要关闭
登录从数据库服务器192.168.1.66
关闭Slave同步进程
mysql> stop slave;
连接到mysql-proxy
[root@centos7-67 ~]# mysql -uproxy1 -p'password' -P4040 -h192.168.1.67
插入2条数据
mysql> insert into t1 values (120,'third');
Query OK, 1 row affected (0.02 sec)
mysql> insert into t1 values (121,'for');
Query OK, 1 row affected (0.01 sec)
验证：
mysql-proxy：
mysql> select * from t1; //没有看到数据
master:
mysql> select * from t1; //可以看到数据
slave：
mysql> select * from t1; //没有看到数据
连接管理端口：
[root@centos7-67 ~]# mysql -uadmin -padmin -h192.168.1.67 -P4041
mysql> SELECT * FROM backends;
+-------------+-------------------+-------+------+------+-------------------+
| backend_ndx | address | state | type | uuid | connected_clients |
+-------------+-------------------+-------+------+------+-------------------+
| 1 | 192.168.1.83:3306 | up | rw | NULL | 0 |
| 2 | 192.168.1.66:3306 | up | ro | NULL | 0 |
+-------------+-------------------+-------+------+------+-------------------+
2 rows in set (0.00 sec)
up：表示读写分离生效；unKnown：还没生效
结论：当在mysql-proxy插入数据时，写入到了master上，查询数据是从slave上查看，所以查询不到数据。当在slave上插入数据，在mysql-proxy上可以看到，说明读是从slave上，写是在master上。
https://blog.51cto.com/jacksoner/2084099