How to Build a Multi‑Master to Single‑Slave MySQL 5.7 Replication Setup

Prerequisites

You should already have experience installing MySQL 5.7.17 from source.

Environment Preparation

Three CentOS 6.7 x64 hosts are used:

Zabbixdb (master1) – IP 10.86.86.72

EvunMonitor (master2) – IP 10.86.93.191

oracle12c (slave) – IP 10.86.87.161

MySQL is installed under /usr/local/mysql and data files under /data/mysqldb.

Initial System Configuration

Disable the firewall and SELinux on all three hosts:

# Stop and disable iptables
/etc/init.d/iptables stop
chkconfig iptables off
chkconfig --list | grep iptables

# Disable SELinux
sed -i "s/SELINUX=enforcing/SELINUX=disabled/g" /etc/selinux/config

Install MySQL 5.7.17

Download the MySQL source package (e.g., from the provided Baidu Cloud link) and follow the standard source‑install procedure. After installation, all three machines should have the mysqld service ready.

Configure my.cnf

Adjust the configuration on each host.

Zabbixdb (master1) :

max_connections=3000
lower_case_table_names=1
innodb_buffer_pool_size=24576M
log-bin=binlog
server_id=1
binlog_format=ROW
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
general_log=on
gtid_mode=ON
enforce_gtid_consistency=ON

EvunMonitor (master2) :

innodb_buffer_pool_size=8589934592
max_connections=3000
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
log-bin=binlog
server_id=2
binlog_format=ROW
general_log=on
gtid_mode=ON
enforce_gtid_consistency=ON

oracle12c (slave) :

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
log-bin=binlog
server_id=3
binlog_format=ROW
general_log=on
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE

Restart MySQL Services

# On each host
/etc/init.d/mysqld restart

Initialize Data on Masters

Create simple test databases and tables to simulate production data.

# On Zabbixdb
mysql -uroot -p
create database zabbixdb;
use zabbixdb;
create table zab(age int);
insert into zab values(1);

# On EvunMonitor
mysql -uroot -p
create database evunmonitor;
use evunmonitor;
create table evun(age int);
insert into evun values(2);

Export and Transfer Data

Use mysqldump (suitable for small data sets) to export each master’s database, then copy the dump files to the slave.

# Export from Zabbixdb
mysqldump -uroot -ppassword --master-data=2 --single-transaction --databases --add-drop-database zabbixdb > zabbixdb.sql
scp zabbixdb.sql [email protected]:/opt

# Export from EvunMonitor
mysqldump -uroot -ppassword --master-data=2 --single-transaction --databases --add-drop-database evunmonitor > evunmonitor.sql
scp evunmonitor.sql [email protected]:/opt

Create Replication Accounts

# On each master
grant replication slave on *.* to 'repl'@'10.86.87.161' identified by 'repl';

Import Dumps on Slave

Before importing, reset any existing GTID state:

mysql -uroot -ppassword -e "reset master;"

Then load the dump files:

mysql -uroot -ppassword < /opt/zabbixdb.sql
mysql -uroot -ppassword < /opt/evunmonitor.sql

Configure Multi‑Source Replication

Set the master‑info and relay‑log repositories to tables (already added in my.cnf on the slave).

Identify the binary log file and position for each master (example values shown):

# From zabbixdb.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000003', MASTER_LOG_POS=759;

# From evunmonitor.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000003', MASTER_LOG_POS=782;

Issue CHANGE MASTER statements on the slave, distinguishing channels:

CHANGE MASTER TO MASTER_HOST='10.86.86.72', MASTER_USER='repl', MASTER_PASSWORD='repl', MASTER_LOG_FILE='binlog.000003', MASTER_LOG_POS=759 FOR CHANNEL 'zabbixdb';
CHANGE MASTER TO MASTER_HOST='10.86.93.191', MASTER_USER='repl', MASTER_PASSWORD='repl', MASTER_LOG_FILE='binlog.000003', MASTER_LOG_POS=782 FOR CHANNEL 'EvunMonitor';

Start Replication

Start all channels at once or individually:

# Start all
START SLAVE;

# Start a single channel
START SLAVE FOR CHANNEL 'zabbixdb';
START SLAVE FOR CHANNEL 'EvunMonitor';

Verify Replication Status

Run SHOW SLAVE STATUS FOR CHANNEL 'zabbixdb'\G and SHOW SLAVE STATUS FOR CHANNEL 'EvunMonitor'\G. Sample screenshots are shown below.

Data Verification

Insert new rows on each master and confirm they appear on the slave.

# On Zabbixdb master
mysql -uroot -ppassword -e "use zabbixdb; insert into zab values(3); commit;"

# On EvunMonitor master
mysql -uroot -ppassword -e "use evunmonitor; insert into evun values(4); commit;"

Query the slave:

# On slave
mysql -uroot -ppassword -e "select * from zabbixdb.zab;"
mysql -uroot -ppassword -e "select * from evunmonitor.evun;"

The results should show the original rows plus the newly inserted ones, confirming successful multi‑source replication.