MySQL master-slave (based on bin-log) and Mysql-proxy read/write separation

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