How can you stop MySQL slave from replicating changes to the 'mysql' database?
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.
The issue with Derek Downey's answer on this post is it will always work the same way (on or off).
If you are in a situation where you'd like most grants to be replicated but not this one - or you don't want to bounce mysql (necessary to load the modified my.conf file) you can do it this way:
SET session sql_log_bin = 0;
GRANT SELECT ON *.* TO `foo`@`localhost` IDENTIFIED BY 'bar';
SET session sql_log_bin = 1;
Please remember - that last line setting sql_log_bin = 1
is very important because without it you will not replicate anything.