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;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| 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 \ //使进程在异常关闭后能够自动恢复


--pid-file=$PROXY_PID \ //定义mysql-proxy PID文件路径


--user=mysql \ //以mysql用户身份启动服务


--log-level=debug \ //定义log日志级别,由高到低分别有(error|warning|info|message|debug)


--log-file=/opt/mysql-proxy/log/mysql-proxy.log //定义log日志文件路径



测试步骤:

在master上授权mysql-proxy服务器连接账号

在master建立测试库和表,会同步到slave上

关闭slave(stop slave)

连接mysql-proxy

mysql -uroot -p -P4040 -h $mysql-proxy_IP    #-P4040是mysql-proxy的监听端口

连接后再测试库插入数据,并查询后退出重新连接mysql-proxy再次查询,比较两次查询的数据

第二次查询没有刚插入的数据即表示查询到了slave,实现了读写分离