I am using MariaDB 10.0 multi-source replication for a specific use case.
For security reasons, I would like to prevent ALTER commands on master to replicate (such as
DROP...) whatever user run these commands (even root) but of course let
DELETE commands to replicate....
I do not want to use
SET SQL_LOG_BIN=0|1 on client side. In fact, I never want to replicate schema modification.
In practice, I wish I could revoke alter permissions to my replication user (who currently has REPLICATION SLAVE permission)
Is there a way to achieve this?
Since my requirements seems nonsense for some readers, I give some additional information about this use case.
I created one (or more) MariaDB Proxy database(s) with tables using BLACKHOLE Storage Engine. So data is not stored on this proxy server, but binlogs are.
I have other MariaDB servers running the same database schema but with INNODB storage engine that replicates data from proxy server(s) using MariaDB Multi-source Replication.
On the proxy server, I can safely recreate, for example, a table schema with a
CREATE OR REPLACE TABLE mytable (id int) ENGINE=BLACKHOLE statement as there is no data stored in it.
But this kind of statement MUST NOT run as is on the "slaves" (which are not real slaves as you noticed) as they must remain in their original storage engine or any other option they may have at the table level.
I can do this by issuing a
SET SQL_LOG_BIN=0 before executing my command, but I was looking for a way to make sure that I will not break the slaves in case I forget to do it.