Score:0

Mariadb multi-source replication - Table doesn't exist

om flag
Ror

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.

ua flag
You will probably get better results at dba.stackexchange.com
mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.