Sample Header Ad - 728x90

MariaDB: Drop database ddl-trigger in two-way replication setup

1 vote
0 answers
41 views
I have a database replication setup in MariaDB where the main database is replicated fully to the secondary database, and only a single table is replicated back in the opposite direction. I achieved this by setting both database servers up as masters and slave and applying a table filter on the master i.e. the main database, only allowing operations made on that table. Configuration on primary database server: [mysqld] # REPLICATION server-id=1 log-bin=mysql-bin binlog-do-db=NHMD replicate-do-table=NHMD.spdataset Configuration on secondary database server: [mysqld] # REPLICATION server-id=2 log-bin=mysql-bin binlog-do-db=NHMD So far so good, but while testing this out in our test environment, I discovered, to my shock, that the filter does not apply to DDL operations, meaning -for instance- that if I drop the secondary database, the main database will be dropped as well. This is a highly undesirable situation and poses a grave risk. I have investigated whether the filter could be expanded to DDL operations but alas, it could not. Then, GitHub copilot bizarrely gaslit me into believing that it is possible to set a DROP DATABASE trigger that would prevent this, if I applied it to the secondary database. It was certain that this feature was made available from 10.7.*, but when I looked into it, it appeared to be a mere hallucination. enter image description here So I am still nowhere with a foolproof solution to this dilemma and the only two things I can think of are: * Disable the two-way replication * Make sure that no one ever by mistake drops or otherwise seriously alters the structure of the secondary database Any other suggestions?
Asked by Fedor Steeman (11 rep)
Jul 9, 2025, 07:20 AM
Last activity: Jul 9, 2025, 09:03 AM