I'm setting up multi-source replication with Mariadb 10.6 servers. I have 2 mariadb servers with databases that have the same name "database". I want those two servers to replicate their DB on a single slave server so master1's database is replicated in database1 and master2's database is replicated in database2.
Here's the masters configurations :
# Replication
log-bin
server_id=1 # 2 for master2
log-basename=master1 # master2 for master2
binlog-format=mixed
gtid_domain_id=1 # 2 for master2
Here's the slave configuration :
#Replication
log-bin
server_id=3
master1.replicate_rewrite_db="database->database1"
master2.replicate_rewrite_db="database->database2"
I backup master1 by doing a mysqldump :
sudo mysqldump --master-data=2 -u root -p database > master1_test.sql
and import it on the slave.
I use these commands on the mariadb cli to start replication :
stop slave 'master1';
reset slave 'master1';
CHANGE MASTER 'master1' TO MASTER_HOST='XXXX', MASTER_USER='replication', MASTER_PASSWORD='XXXXXXX', MASTER_PORT=3306, MASTER_LOG_FILE='master1-bin.000001', MASTER_LOG_POS=710, MASTER_CONNECT_RETRY=10, MASTER_USE_GTID = slave_pos;
SET GLOBAL gtid_slave_pos = "1-1-2"
start slave 'master1';
MASTER_LOG_FILE, MASTER_LOG_POS and gtid_slave_pos are taken from the dump file.
everything works fine. I tried to add a new table in master1, the creation of the new table is replicated.
However, when I tried to put some data in the new table the following commands tells me :
show slave 'master1' status \G;
Last_SQL_Error: Error 'Table 'database.test' doesn't exist' on query. Default database: 'database1'. Query: 'INSERT INTO `database`.`test` (`test`) VALUES ('1242')'
It should replace database by database1.