mysql主从（基于bin-log）以及mysql-proxy读写分离

321dw

准备好三台机器：
192.168.220.129（master）
192.168.220.143（slave）
192.168.220.144（proxy）

确保三台机器上mysql（5.6.29）都已装好
my.cnf配置
master:
[mysqld1]
basedir = /usr/localmysql
datadir = /data/mysql
port = 3306
server_id=1
socket = /tmp/mysql.sock
log-bin=mysql-bin


slave:
[mysqld1]
basedir = /usr/localmysql
datadir = /data/mysql
port = 3306
server_id=2
socket = /tmp/mysql.sock


master上创建复制用户
grant replication slave on *.* to 'server'@'192.168.220.144' identified by '123456';
flush privileges;
show master status;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     120  |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

slave上操作
slave stop;
change master to master_host='192.168.220.144',master_user='server',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=120;
slave start;

show slave status\G；
确保状态:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

实现读写分离
安装一些依赖包:
yum -y install gcc libevent libevent-devel readline readline-devel ncurses ncurses-devel glib2 glib2-devel

安装Lua：
wget -c tar zxvf lua-5.1.5.tar.gz
tar zxvf lua-5.1.5.tar.gz
cd lua-5.1.5
vi Makefile    #将INSTALL_TOP=/usr/local修改为:INSTALL_TOP=/usr/local/lua
vi ./src/Makefile    #将CFLAGS= -O2 -Wall $(MYCFLAGS)修改为:CFLAGS= -O2 -Wall -fPIC $(MYCFLAGS)
make linux
make install

安装MySQL Proxy
wget -c http://dev.mysql.com/get/Downloa ... tp://cdn.mysql.com/ -O /usr/local/src/mysql-proxy-0.8.3.tar.gz
tar zxvf mysql-proxy-0.8.3.tar.gzcd mysql-proxy-0.8.3./configure LDFLAGS="-lm -ldl" LUA_CFLAGS="-I/usr/local/lua/include/" LUA_LIBS=/usr/local/lua/lib/liblua.a --prefix=/usr/local/mysql-proxy --with-lua --with-mysql=/usr/local/mysql/bin/mysql_config
make && make installMySQL Proxy配置与启动
mkdir /usr/local/mysql-proxy/conf
mkdir /var/log/mysql-proxycd /usr/local/mysql-proxy/conf
vim mysql-proxy.conf
[mysql-proxy]
daemon=true
pid-file=/usr/local/mysql-proxy/mysql-proxy.pid
log-file=/var/log/mysql-proxy/mysql.log
log-level=debug

LUA_PATH="/usr/local/src//usr/local/src/mysql-proxy-0.8.3/lib/?.lua"

proxy-lua-script=/usr/local/src/mysql-proxy-0.8.3/lib/rw-splitting.lua

proxy-address=192.168.220.129:3306
proxy-read-only-backend-addresses=192.168.220.143:3306
proxy-backend-addresses=192.168.220.144:3306

keepalive=true
proxy-skip-profiling=true
#proxy-connect-timeout=2
#proxy-read-timeout=60
#proxy-write-timeout=30

admin-address=:4041
admin-lua-script=/usr/local/src/mysql-proxy-0.8.3/lib/admin.lua
admin-username=admin
admin-password=admin

plugins=proxy,admin
chmod 660 mysql-proxy.conf

vim /usr/local/src/mysql-proxy-0.8.3/lib/rw-splitting.lua
--- config
---- connection poolif not proxy.global.config.rwsplit then        proxy.global.config.rwsplit = {                                min_idle_connections = 1,                max_idle_connections = 3,                is_debug = false        }end启动MySQL Proxy
[iyunv@abc conf]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf
查看启动日志：
[iyunv@abc conf]# tail -f /var/log/mysql-proxy/mysql.log
2016-03-04 19:54:03: (message) shutting down normally, exit code is: 0
2016-03-04 19:54:18: (message) chassis-unix-daemon.c:136: [angel] we try to keep PID=10766 alive
2016-03-04 19:54:18: (debug) chassis-unix-daemon.c:157: waiting for 10766
2016-03-04 19:54:18: (debug) chassis-unix-daemon.c:121: we are the child: 10766
2016-03-04 19:54:18: (critical) plugin proxy 0.8.3 started
2016-03-04 19:54:18: (critical) plugin admin 0.8.3 started
2016-03-04 19:54:18: (debug) max open file-descriptors = 1024
2016-03-04 19:54:18: (message) proxy listening on port 192.168.220.129:3306
2016-03-04 19:54:18: (message) added read/write backend: 192.168.220.144:3306
2016-03-04 19:54:18: (message) added read-only backend: 192.168.220.143:3306