How can you stop MySQL slave from replicating changes to the 'mysql' database?

Question

I have my slave set to not replicate the 'mysql' database as described in this SHOW SLAVE STATUS\G;

           Slave_IO_State: Waiting for master to send event
              Master_Host: 127.0.0.1
              Master_User: replication
              Master_Port: 3306
            Connect_Retry: 60
          Master_Log_File: master-bin.000001
      Read_Master_Log_Pos: 1660
           Relay_Log_File: mysql-relay-bin.000004
            Relay_Log_Pos: 478
    Relay_Master_Log_File: master-bin.000001
         Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
          Replicate_Do_DB: 
      **Replicate_Ignore_DB: mysql**
       Replicate_Do_Table: 
   Replicate_Ignore_Table: 
  Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
               Last_Errno: 0
               Last_Error: 
             Skip_Counter: 0
      Exec_Master_Log_Pos: 1660
          Relay_Log_Space: 633
          Until_Condition: None
           Until_Log_File: 
            Until_Log_Pos: 0

Now, if I go to the MASTER server and issue a GRANT and FLUSH PRIVILEGES:

GRANT SELECT ON *.* TO `foo`@`localhost` IDENTIFIED BY 'bar';
FLUSH PRIVILEGES;

I then go back to the SLAVE server and issue:

SHOW GRANTS FOR `foo`@`localhost`;

and receive the response:

+-------------------------------------------------------------------------------------------------------------+
| Grants for foo@localhost                                                                                    |
+-------------------------------------------------------------------------------------------------------------+
| GRANT SELECT ON *.* TO 'foo'@'localhost' IDENTIFIED BY PASSWORD '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB' |
+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

How can I stop the slave from replicating changes the mysql database? I figured 'replicate_ignore_db' would have sufficed.

Answer 1

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.

Answer 2

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.