MySQL 8.0 Replication 'Unknown Database' Error Causes Replication to Stop

Question

I'm having an issue with replication for two Mysql servers. The master is running Mysql 5.6 and the slave is running Mysql 8.0 due to another dependency that requires at least version 8. I am only replicating one DB, for this example's sake 'my_db'. However at a certain point in the day the master runs backups and tries to update the DB 'my_db_backups'. I am not replicating this DB and I just want to ignore these SQL commands on the replica. I explicitly ignore this database in my mysql.conf file using:

replicate-ignore-db=my_db_backups

However when I restart the mysql server I continue to see an error:

Last_SQL_Errno: 1049
Last_SQL_Error: Error 'Unknown database 'my_db_backups'' on query. Default database: 'my_db'. Query: 'INSERT INTO my_db.`table_1` SELECT * FROM my_db_backups.`2021_01_04_17_36_22_table_1`'

Everything I've read seems to indicate that setting replicate-do-db should only execute queries related to that DB. Additionally adding an explicit ignore, replicate-ignore-db, I would expect to work as well but I'm having no luck.

My full mysql.conf looks like this:

[mysqld]
server-id=20
lower_case_table_names=1
updatable_views_with_limit=1
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
replicate-ignore-db=my_db_backups
replicate-ignore-table=my_db.mysql_8_incompatible_view_1
replicate-ignore-table=my_db.mysql_8_incompatible_view_2
replicate-ignore-table=my_db.mysql_8_incompatible_view_3
replicate-ignore-table=my_db.mysql_8_incompatible_view_4
replicate-wild-do-table=my_db.%
replicate-do-db=my_db

I have tried every combination of these replicate-do and replicate-ignore-do configurations that I can think of I've tried adding:

replicate-wild-ignore-table=my_db_backups.%

and removing:

replicate-ignore-db=my_db_backups

and I've tried putting them both in the conf file. Nothing seems to work.

I know that I can explicitly skip error 1049 which is what I may have to end up doing. I'm just pulling my hair out trying to figure out why the other configurations aren't having any effect and can't let it go.

Any help would be greatly appreciated!

Bill Karwin Bill Karwin · Accepted Answer · 2021-01-06T01:16:55

I noticed that the statement that produces the error isn't actually writing to a table in my_db_backups. It's replicating an INSERT to a table in my_db.

INSERT INTO my_db.`table_1` SELECT * FROM my_db_backups.`2021_01_04_17_36_22_table_1`

The statement is reading from a table in my_db_backups, which it did not find on the replica.

So the statement that inserts into my_db.table_1 will be executed on the replica, because the default database my_db was not ignored.

You could also fix this by using ROW-based binary logs on the master, so it doesn't matter that it's reading from a table that doesn't exist on the replica. The INSERT would result in a row image on the master, where the table does exist.

MySQL 8.0 Replication 'Unknown Database' Error Causes Replication to Stop

1 Answers