MySQL: Exclude user modifications from replication
 

I am running a MySQL database that is replicated to multiple instances for various purposes.

I want to distinguish database users between Master and Slaves. Users that have access to the Master should not automatically have access to all replication Slaves. On some slaves I would like to configure users that do not have access to the Master.

To achieve this I have excluded database "mysql" from the replica. However, I noticed that user modification queries (CREATE USER, ALTER USER, DROP USER) are still replicated to the slave. If I create a user on the Master it will also be created on the Slave. And, more importantly, if I drop specific users from the Slave and not from the Master, and then modify the users on the Master, this will lead to a replication error. Also, creating a user on the Master that already exists on the Slave will lead to errors.

Is there a way to exclude all user modification queries from the replication?

Is it a good idea to exclude the entire MySql database from replication?

MySQL database due to it's heavy usage of myisam isn't great for replication and so the queries used are often replicated instead of the database itself (Statement based replication as opposed to Row based). If you are replicating databases/tables then you should be using innodb as a minimum as innodb is at least ACID compliant.

you can disable binlogging for the session and add/drop users or ensure you are actually in the mysql table (use mysql) before running such statements but really if you are replicating, then why would you want the replication slave to have different users to the master?

Yes, I am using INNODB tables and statement based replication.

That does not seem to be an option since the database is continuously being updated so any normal updates during the period I disable binlogging would also be lost for replication.
I was thinking, is there any replication filter that I could apply to exclude specific statements?

I don't see how being in the mysql table will stop such statements from being recorded in the binlog and, thus, from the replication

I have several use cases:
One replica is actually a Data Warehouse database. It is used for a data warehouse function and contains a different set of user accounts from the Production environment. It is undesirable that database users from production also have accounts with equal privileges on the Data Warehouse servers.
My organization has a contractual obligation to maintain an extra database server containing the full dataset; however, this server should not be accessible using any of the existing database user accounts.

It only affects the session, you would use the command "SET sql_log_bin = 0" https://dev.mysql.com/doc/refman/5.7...l-log-bin.html

This won't affect other sessions/connections at all. You may want to look into the differences between global and session variables in mysql to get a better understanding of this behavior

https://dev.mysql.com/doc/refman/5.7...variables.html
Because the statement is now against the mysql database, when you are running it normally, you are actually running it against the database you are using. Tho behavior may differ between different versions of mysql and based on different forms of replication.

The only alternative I can think of is to try adding 'binlog-ignore-db=mysql' to the my.cnf of the master instead, that might catch the statements. You'd need to restart mysql to apply or manually update the global variable.

https://dev.mysql.com/doc/refman/5.6...nlog-ignore-db

Ok, thank you for the info. I will run some tests and post the result.
Regards,
Lucas