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

Source: Internet
Author: User
Tags lua readline

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.