Multi-source replication procedure

Add the following to the configuration of the MySQL instance that will act as the slave:

# /etc/mysql/maridb.conf.d/50-server.cnf
[mysqld]
server_id=19823759 # must not collide with the master
replicate-ignore-db=mysql # do not replicate the mysql database

On the master database, create a replication user and grant privileges:

grant replication slave on *.* to 'repl'@'%' identified by '!repl123';

Use mysqldump on the database that will act as the master to take a dump:

mysqldump --databases database_1 database_2 --single_transaction --master-data=1 -h host_path -u account -p > dump.sql

Use --single-transaction to avoid taking locks, and --master-data=1 so the dump header records the binlog file and position at dump time. That causes a CHANGE MASTER ... statement to appear at the top of the dump file. For multi-source replication, you need to rewrite it like this:

CHANGE MASTER 'master_name' TO
master_host='host_path',
master_user='account',
master_password='password',
master_port=3306,
MASTER_LOG_FILE='mysql-bin-changelog.052616', MASTER_LOG_POS=886626; # keep exactly as recorded in the dump

If the dump is too large to edit comfortably, you can split the file, edit only the beginning, and stitch it back together:

head -n 30 dump.sql > new_dump.sql
vim new_dump.sql
tail -n +30 dump.sql >> new_dump.sql

Load the dump into the instance that will act as the slave:

mysql -h hostpath -u account -p database_name < dump.sql

Even if the dump contains several databases, it is enough to specify just one of them as database_name, but that database must already exist.

Start the replication thread:

start slave 'master_name'

Other notes

  • Setting up replication requires SUPER privileges. On AWS RDS, ordinary users cannot have SUPER.
  • If the binary log rotates too quickly, you can inspect and adjust the retention period. For example:
    • Use call mysql.rds_show_configuration() to check binlog retention hours
    • call mysql.rds_set_configuration('binlog retention hours', 1);