Prevent replication of ALTER commands 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 CREATE, ALTER, DROP...) whatever user run these commands (even root) but of course let SELECT, INSERT, UPDATE, 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?
EDIT 2018-02-19
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 &quot;slaves&quot; (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.
APPROACH #1

The easiest thing to do would be to revoke ALTER, DROP, and CREATE from all users except root

UPDATE mysql.user SET alter_priv='N',drop_priv='N',create_priv='N' WHERE user &lt;&gt; 'root';
UPDATE mysql.db   SET alter_priv='N',drop_priv='N',create_priv='N' WHERE user &lt;&gt; 'root';
FLUSH PRIVILEGES;


APPROACH #2

You said that you do not want to issue

SET sql_log_bin = 0;


on the client side. A very adventurous approach would be to use a Stored Procedure to do your ALTER TABLE commands. You could then apply SET sql_log_bin = 0; in the Stored Procedure. In effect, that happens on the server side.

DELIMITER $$

DROP PROCEDURE IF EXISTS `Local_Alter_Table` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `Local_Alter_Table`
(
    SQLStatement VARCHAR(2048)
)
BEGIN

    SET sql_log_bin = 0;

    SET @sql = SQLStatement;
    PREPARE s FROM @sql;
    EXECUTE s;
    DEALLOCATE PREPARE s;

    SET sql_log_bin = 1;

END $$

DELIMITER ;


Then, require users to run

CALL Local_Alter_Table('ALTER TABLE ...');


GIVE IT A TRY !!!