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

<code>yum install -y mariadb mariadb-server</code>

<code>systemctl start mariadb
systemctl enable mariadb</code>

<code>systemctl status mariadb
netstat -anpt | grep "mysql"</code>

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

<code>mysql_secure_installation</code>

Create test database on the master

<code>mysql -uroot -p123456</code>

<code>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;</code>

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

Grant replication user on master

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

Configure master for binary logging

<code>show variables like 'log_bin';</code>

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

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

Note the File and Position values.

Create replication account

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

Prepare slave

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

<code>systemctl restart mariadb</code>

Backup master and restore on slave

<code>flush tables with read lock;</code>

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

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

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

Configure slave replication

<code>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;</code>

<code>start slave;</code>

<code>show slave status \G</code>

Both master and slave should report

Yes

for the replication status.

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

<code>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/</code>

<code># /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
</code>

<code>chmod 660 /etc/mysql-proxy.cnf</code>

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

<code>netstat -anpt | grep 3306</code>

Grant proxy user on master and slave

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

Test read/write splitting

<code># 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;"
</code>

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