How to Set Up MySQL Master‑Slave Replication and Read/Write Splitting with MySQL‑Proxy

Prepare three hosts: mysql‑master (192.168.204.201), mysql‑slave (192.168.204.202) and appserver (192.168.204.111).

Install and configure MySQL

yum install -y mariadb mariadb-server

systemctl start mariadb
systemctl enable mariadb

systemctl status mariadb
netstat -anpt | grep "mysql"

firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload

mysql_secure_installation

Create test database on the master

mysql -uroot -p123456

create database db_test;
use db_test;
create table if not exists user_info(
    username varchar(16) not null,
    password varchar(32) not null,
    realname varchar(16) default '',
    primary key (username)
) default charset=utf8;

insert into user_info(username,password,realname) values
('10001','123456','小明'),
('10002','123456','小红'),
('10003','123456','小王'),
('10004','123456','小张'),
('10005','123456','小李');

Grant replication user on master

GRANT ALL ON db_test.* TO 'admin'@'%' identified by '123456';
flush privileges;

Configure master for binary logging

show variables like 'log_bin';

# cp /usr/share/mysql/my-medium.cnf /etc/my.cnf
# edit /etc/my.cnf to set:
#   log-bin=mysql-bin
#   server-id=1

systemctl restart mariadb
mysql -uroot -p123456 -e "show variables like 'log_bin';"
mysql -uroot -p123456 -e "show master status;"

Note the File and Position values.

Create replication account

grant replication slave on *.* to rep@'192.168.204.202' identified by '123456';
show grants for rep@'192.168.204.%';

Prepare slave

# cp /usr/share/mysql/my-medium.cnf /etc/my.cnf
# edit /etc/my.cnf to set:
#   server-id=2
#   skip-log-bin

systemctl restart mariadb

Backup master and restore on slave

flush tables with read lock;

mysqldump -uroot -p123456 --all-databases | gzip > /root/database_$(date +%Y-%m-%d).sql.gz

scp /root/database_*.sql.gz [email protected]:/root

gzip -d /root/database_*.sql.gz
mysql -uroot -p123456 < /root/database_*.sql

Configure slave replication

change master to
MASTER_HOST='192.168.204.201',
MASTER_USER='rep',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=245;

start slave;

show slave status \G

Both master and slave should report Yes for the replication status.

Set up MySQL‑Proxy for read/write splitting on appserver

cd ~
wget https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-sles11-x86-64bit.tar.gz
tar -xzvf mysql-proxy-0.8.5-linux-sles11-x86-64bit.tar.gz
cp -r mysql-proxy-0.8.5-linux-sles11-x86-64bit /usr/local/mysql-proxy
mkdir /usr/local/mysql-proxy/lua /usr/local/mysql-proxy/logs
cp share/doc/mysql-proxy/rw-splitting.lua ./lua/
cp share/doc/mysql-proxy/admin-sql.lua ./lua/

# /etc/mysql-proxy.cnf (remove comments before use)
[mysql-proxy]
user=root
admin-username=myproxy
admin-password=123456
proxy-address=127.0.0.1:3306
proxy-read-only-backend-addresses=192.168.204.202
proxy-backend-addresses=192.168.204.201
proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua
admin-lua-script=/usr/local/mysql-proxy/lua/admin-sql/lua
log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log
log-level=info

chmod 660 /etc/mysql-proxy.cnf

/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf --daemon

netstat -anpt | grep 3306

Grant proxy user on master and slave

grant all on *.* to 'myproxy'@'192.168.204.%' identified by '123456';
flush privileges;

Test read/write splitting

# read through proxy (should hit slave)
mysql -h 127.0.0.1 -umyproxy -p123456 -e "select * from db_test.user_info where 1;"
# write through proxy (should hit master)
mysql -h 127.0.0.1 -umyproxy -p123456 -e "insert into db_test.user_info (username,password,realname) values ('30001','123456','Jack');"
# verify write on master
mysql -uroot -p123456 -e "select * from db_test.user_info where 1;"

After the tests, the system successfully demonstrates MySQL master‑slave replication and transparent read/write splitting via MySQL‑Proxy.