A multi-source replication topology requires at least two masters
and one slave configured. In these tutorials, we will assume you
have two masters
master2, and a replication slave
slavehost. The slave will replicate one
database from each of the masters,
Masters in a multi-source replication topology can be configured to use either GTID-based replication, or binary log position-based replication. See Section 184.108.40.206, “Setting Up Replication Using GTIDs” for how to configure a master using GTID-based replication. See Section 220.127.116.11, “Setting the Replication Master Configuration” for how to configure a master using file position based replication.
Slaves in a multi-source replication topology require
TABLE repositories for the master info log and
relay log info log, which are the default in MySQL
8.0. Multi-source replication is not compatible with
file repositories, and the
FILE setting for the
variables is now deprecated.
To modify an existing replication slave that is using a
FILE repository for the slave status logs to
TABLE repositories, you can convert the
existing replication repositories dynamically by using the
mysql client to issue the following statements
on the slave:
mysql> STOP SLAVE; mysql> SET GLOBAL master_info_repository = 'TABLE'; mysql> SET GLOBAL relay_log_info_repository = 'TABLE';
Create a suitable user account on all the masters that the slave
can use to connect. You can use the same account on all the
masters, or a different account on each. If you create an account
solely for the purposes of replication, that account needs only
REPLICATION SLAVE privilege.
For example, to set up a new user,
can connect from the replication slave
slavehost, use the mysql
client to issue these statements on each of the masters:
mysql> CREATE USER 'ted'@'slavehost' IDENTIFIED BY 'password'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'ted'@'slavehost';
For more details, and important information on the default authentication plugin for new users from MySQL 8.0, see Section 18.104.22.168, “Creating a User for Replication”.