Three machines ready:
192.168.220.129 (Master)
192.168.220.143 (slave)
192.168.220.144 (proxy)
Make sure that MySQL (5.6.29) is installed on all three machines
MY.CNF Configuration
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
Create a replication user on 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 | | | |
+------------------+----------+--------------+------------------+-------------------+
Operation on 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;
Ensure status:
Slave_io_running:yes
Slave_sql_running:yes
Implement read-Write separation
Install some dependent packages:
Yum-y install gcc libevent libevent-devel readline readline-devel ncurses ncurses-devel glib2 glib2-devel
To install 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 modified to: Install_top=/usr/local/lua
Vi./src/makefile #将CFLAGS =-o2-wall $ (mycflags) modified to: cflags=-o2-wall-fpic $ (mycflags)
Make Linux
Make install
Install MySQL Proxy
Wget-c http://dev.mysql.com/get/Downloads/MySQL-Proxy/mysql-proxy-0.8.3.tar.gz/from/http://cdn.mysql.com/-O/USR /local/src/mysql-proxy-0.8.3.tar.gz
Tar zxvf mysql-proxy-0.8.3.tar.gz
CD 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 install
MySQL Proxy Configuration and startup
Mkdir/usr/local/mysql-proxy/conf
Mkdir/var/log/mysql-proxy
Cd/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
Start MySQL Proxy
[Email protected] conf]#/usr/local/mysql-proxy/bin/mysql-proxy--defaults-file=/usr/local/mysql-proxy/conf/ Mysql-proxy.conf
To view the boot log:
[Email protected] 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 is 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
MySQL master-slave (based on bin-log) and Mysql-proxy read/write separation