mysql-proxy读写分离测试

原创

j99d99 2015-11-04 23:04:18 博主文章分类：mysql ©著作权

文章标签 mysql 读写分离 mysql-proxy 文章分类 服务器

©著作权归作者所有：来自51CTO博客作者j99d99的原创作品，请联系作者获取转载授权，否则将追究法律责任

master:192.168.1.207

slave:192.168.1.208

msyql-proxy:192.168.1.206

主从环境搭建：

主从原理：

myslq所有操作都会记录到binlog

主从复制，slave通过读取master的binlog在自己服务器上分析成relaylog(中继日志)就可使用到自身了

主：配置binlog

从：配置relaylog

授权从读取主的binlog

具体配置：

主：

1.vim /etc/my.cnf修改后重启mysql

开启bin-log日志

server-id=1

bin-log=mysql-bin

binlog-format=mixed

#二进制日志格式mixed/row/statement

#statement记录执行语句

#row记录的是磁盘变化

#mixed混合的,由系统根据语句决定

2.授权slave连接账号

grant replication client,replication slave on *.* to 'slave'@'192.168.1.208' identified by 'test123';

3.进入msyql终端

reset master;

show master status;记录mysql-bin

mysql> show master status;

+------------------+----------+--------------+------------------+

+------------------+----------+--------------+------------------+

| mysql-bin.000001 | 202 | | |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

从：

1.开启bin-log和relay-log日志,重启mysql

vim /etc/my.cnf

server-id=2

log-bin=mysql-bin

relay-log=mysql-relay

binglog-format=mixed

2.change master to master_host='192.168.1.207',master_user='slave',master_password='test123',master_log_file='mysql-bin.000001',master_log_pos=202;

3.stop slave;

reset slave;

start slave;

4.查看主从是否成功

show slave status\G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

此两项为YES，即表示成功

mysql-proxy搭建：

一、安装lua

wget http://www.lua.org/ftp/lua-5.1.4.tar.gz

tar zxvf lua-5.1.4.tar.gz

cd lua-5.1.4

#vim src/Makefile

将

CFLAGS= -O2 -Wall $(MYCFLAGS)

修改为

CFLAGS= -O2 -fPIC -Wall $(MYCFLAGS)

make linux(根据系统选择，本人在centos下使用的是make generic)

make install

wget http://cdn.mysql.com//Downloads/MySQL-Proxy/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz

wget http://cdn.mysql.com//Downloads/MySQL-Proxy/mysql-proxy-0.8.5.tar.gz

tar zxvf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz

mv mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit /opt/mysql-proxy

mkdir -p /opt/mysql-proxy/init.d

mkdir -p /opt/mysql-proxy/run

mkdir -p /opt/mysql-proxy/log

mkdir -p /opt/mysql-proxy/scripts

tar zxvf mysql-proxy-0.8.5.tar.gz

cd mysql-proxy-0.8.5

cp lib/rw-splitting.lua /opt/mysql-proxy/scripts/

vim /opt/mysql-proxy/scripts/rw-splitting.lua

将以下有注释部分进行修改

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

启动mysql-proxy

/opt/mysql-proxy/bin/mysql-proxy --proxy-read-only-backend-addresses=192.168.1.208:3306 --proxy-backend-addresses=192.168.1.207:3306 --daemon --pid-file=/opt/mysql-proxy/run/mysql-proxy.pid --user=mysql --log-level=debug --log-file=/opt/mysql-proxy/log/mysql-proxy.log --plugins=proxy --proxy-lua-script=/opt/mysql-proxy/scripts/rw-splitting.lua --plugins=admin --admin-username=admin --admin-password=admin --admin-lua-script=/opt/mysql-proxy/lib/mysql-proxy/lua/admin.lua --keepalive

参数解释：

PROXY_PATH=/opt/mysql-proxy/bin //定义mysql-proxy服务二进制文件路径

PROXY_OPTIONS=”–admin-username=root \ //定义内部管理服务器账号

--admin-password=password \ //定义内部管理服务器密码

--proxy-read-only-backend-addresses=192.168.10.131:3306 \ //定义后端只读从服务器地址

--proxy-backend-addresses=192.168.10.130:3306 \ //定义后端主服务器地址

--admin-lua-script=/opt/mysql-proxy/lib/mysql-proxy/lua/admin.lua \ //定义lua管理脚本路径

--proxy-lua-script=/opt/mysql-proxy/scripts/rw-splitting.lua” \ //定义lua读写分离脚本路径

PROXY_PID=/opt/mysql-proxy/run/mysql-proxy.pid //定义mysql-proxy PID文件路径

$NICELEVEL $PROXY_PATH/mysql-proxy $PROXY_OPTIONS \

--daemon \ //定义以守护进程模式启动

--keepalive \ //使进程在异常关闭后能够自动恢复