MySQL – How to Stop Slave from Replicating Changes to the ‘mysql’ Database

MySQLreplication

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.

Best Answer

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.

Related Question