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

Experiment Environment

The lab consists of three hosts:

mysql-master – 192.168.204.201 (primary MySQL server)

mysql-slave – 192.168.204.202 (replica server)

appserver – 192.168.204.111 (application server)

Install and Configure MySQL (MariaDB)

1. Install packages

yum install -y mariadb mariadb-server

2. Start and enable the service

systemctl start mariadb
systemctl enable mariadb

3. Verify status and ports

systemctl status mariadb
netstat -anpt | grep "mysql"

4. Open firewall port 3306

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

5. Secure installation (set root password)

mysql_secure_installation

6. Create test database and table

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','小李');
select * from user_info;

7. Grant and revoke privileges on master

GRANT all ON db_test.* TO 'admin'@'%' identified BY '123456';
flush privileges;
REVOKE all ON db_test.* FROM 'admin'@'%';
flush privileges;

Configure Master‑Slave Replication

1. Enable binary logging on master

show variables like 'log_bin';

2. Edit my.cnf (copy template and modify)

cp /usr/share/mysql/my-medium.cnf /etc/my.cnf

3. Restart MySQL and note File/Position

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

4. Create replication user on master

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

5. Backup master data

flush tables with read lock;
mysqldump -uroot -p123456 --all-databases | gzip > /root/database_$(date '+%Y-%m-%d').sql.gz

6. Transfer backup to slave and restore

scp database_*.sql.gz [email protected]:/root
ssh [email protected] "gzip -d /root/database_*.sql.gz && mysql -uroot -p123456 < /root/database_*.sql"
mysql -uroot -p123456 -e "show databases;"

7. Configure slave (server‑id, disable binlog)

cp /usr/share/mysql/my-medium.cnf /etc/my.cnf
# edit /etc/my.cnf to set:
# server-id = 2
# (comment out log-bin and binlog_format)
systemctl restart mariadb

8. Set replication coordinates and start slave

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;

9. Verify replication

show slave status \G

Set Up MySQL Proxy for Read/Write Splitting on appserver

1. Install MySQL‑Proxy

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

2. Prepare directories and copy scripts

cd /usr/local/mysql-proxy
mkdir lua logs
cp share/doc/mysql-proxy/rw-splitting.lua ./lua/
cp share/doc/mysql-proxy/admin-sql.lua ./lua/

3. Create main configuration file (/etc/mysql-proxy.cnf)

[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

4. Adjust rw‑splitting.lua (example change)

if not proxy.global.config.rwsplit then
    proxy.global.config.rwsplit = {
        min_idle_connections = 1,
        max_idle_connections = 1,
        is_debug = false
    }
end

5. Start the proxy as a daemon

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

6. Verify proxy process

netstat -anpt | grep 3306

7. Grant proxy user rights on master and slave

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

8. Test read/write splitting via proxy

mysql -h 127.0.0.1 -umyproxy -p123456 -e "select * from db_test.user_info where 1;"
mysql -h 127.0.0.1 -umyproxy -p123456 -e "insert into db_test.user_info (username,password,realname) values ('30001','123456','Jack');"
mysql -h 127.0.0.1 -umyproxy -p123456 -e "select * from db_test.user_info where 1;"
# Verify on master
mysql -uroot -p123456 -e "select * from db_test.user_info where 1;"

The steps above demonstrate a fully functional MySQL master‑slave setup with automatic read/write splitting using MySQL‑Proxy.