MySQL – How to Stop Replicating Users on Slave

Alright, after a few more hours of investigation, I think I figured it out. Adding my answer in case this is of use to others.

According to the docs on replicate-ignore-db:

Statement-based replication. Tells the slave SQL thread not to replicate any statement where the default database (that is, the one selected by USE) is db_name.

Of course, statement-based replication is the default and what I was using. So I made the attempt to change the format by restarting the master with binlog_format=row to see what would happen. No dice. GRANTS and REVOKES still were replicated.

Further investigation into the docs on replication changes on the mysql table revealed

Statements that change the mysql database indirectly are logged as statements regardless of the value of binlog_format. This pertains to statements such as GRANT, REVOKE, SET PASSWORD, RENAME USER, CREATE (all forms except CREATE TABLE ... SELECT), ALTER (all forms), and DROP (all forms).

Gah! Ok, so I checked the binlog using mysqlbinlog and my GRANT statement was not issueing a USE mysql database call (why should it?). So replicate-ignore-db could not in good conscience ignore the statement.

My solution was to cut the changes to the mysql table out of the binary log completely by adding binlog-ignore-db=mysql to my.cnf and restart the server. Worked like a charm.

binlog_do_db = dbname says to replicate any statement when the default database is dbname. So be careful what USE statement precedes the statement:

USE non_repl_db;
CREATE TABLE ...;