1

I have an active MASTER/SLAVE replication (Mariadb 10.2) with a single database (see scenario 1)

scenario

I decided to add in IMPORT database to populate the MASTER database as described in scenario 2. However, the SLAVE instance go OFF and “Unknown database import” error is traced back.

enter image description here

To overcome this, I created then a database with same name in SLAVE server (import) and the error is gone (see scenario 3). enter image description here

However, the created database (slave import) was without tables (in purpose) so to be sure that data is inserted in the slave I tested by creating a test table in the MASTER database and I did insert data from the Master's import database.

As a result, data was obviously available in the slave database even if the slave's import database has no tables.

So basically, we need just the same db name in slave section to get worked without the need of tables.

Could you please explain to me how it works ?and why we need the same db in slave if its a row-based format ?

Improve this question

1 Answer 1

Reset to default
0

There are several things going on here, and you will need to look into your configuration on both master and slave to establish where there are any of the following options set on the master:

binlog_do_db
binlog_ignore_db

and whether any of the following are set on the slave:

replicate_do_db
replicate_ignore_db

The thing that you should be aware of is that what matters is the current database on the connection where you are performing the database/table creation because DDL is still logged as statements even in row-based binlog.

So if you do:

mariadb> use replicated_database
mariadb> CREATE TABLE ignored_database.table_name ...

this will not get replicated across.

The solution you are looking for is:

1) On the master, set your binlog_ignore_db = ignored_import_database

2) Make sure the data import process connects to the ignored_import_database when it is performing the data loading so that any DDL doesn't make it across, and make sure that there is no database switching ("use database some_unignored_db") inside the import payload.

Improve this answer
4
  • Thanks for these information, but still this does not explain to me the fact that when I created the ignored_import_database just empty in the slave server... the error disappear !
    – Yassine LD
    Apr 30, 2020 at 13:18
  • The chances are that something in the import is performing some DML on the ignored_import_database while connected to the replicated database. Have you made sure that your import process runs entirely and purely connected to the ignored_import_database and never switches current database to the replicated database throughout the process?
    – Gordan Bobić
    Apr 30, 2020 at 20:48
  • Yes it has been a switch. So you mean that if we perform some DML queries on the import_database while connected to the master db it shall not pass in the slave side (unknown database error)?
    – Yassine LD
    May 1, 2020 at 22:48
  • 1
    Yes, that's right. Filtering will take care of DML with row based logging. It's DDL while connected to the unfiltered database that will get replicated even if it is referring to the ignored database.
    – Gordan Bobić
    May 1, 2020 at 23:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.