Mysql master-master permission replication issue

Question

I came to know it's safe to replicate mysql database to have the users and permissions replicated, from a post in ServerFault is-it-ok-to-replicate-mysql-db. I have setup master-master replication with a slave for each master(totally 4 nodes). Everything is working, databases, tables are synced but NOT permissions, permissions applied to a db is not replicated from or to any server.

I dumped mysql(mysqldump -uroot -pmypass mysql > /tmp/mysql.sql) db on original server and restored it to all the servers and also given super privileges to the repli user as mentioned in the above link. But permission are not synced from or to any server. I issued GRANT ALL PRIVILEGES... on a test db and checked with SHOW GRANTS for.. but privileges are not showing on remote servers(did flush privileges on where I granted and on all).

Posting configuration may not be useful but still here it's:

Master1(192.168.1.5)

server-id = 10
replicate-same-server-id = 0
auto-increment-increment = 10
auto-increment-offset = 1

master-host = 192.168.1.10
master-user = repli
master-password = secret
master-connect-retry = 60

log-slave-updates
replicate-ignore-db=information_schema
report-host=192.168.1.5

binlog-ignore-db=information_schema
log-bin = /var/log/mysql/mysql-bin
log-bin-index = /var/log/mysql/bin-log.index
log-error = /var/log/mysql/error.log
relay-log = /var/log/mysql/relay.log
relay-log-index = /var/log/mysql/relay-log.index

Master2(192.168.1.10)

server-id = 20
replicate-same-server-id = 0
auto-increment-increment = 10
auto-increment-offset = 2

master-host = 192.168.1.5
master-user = repli
master-password = secret
master-connect-retry = 60

log-slave-updates
replicate-ignore-db=information_schema
report-host=192.168.1.10

binlog-ignore-db=information_schema
log-bin = /var/log/mysql/mysql-bin
log-bin-index = /var/log/mysql/bin-log.index
log-error = /var/log/mysql/error.log
relay-log = /var/log/mysql/relay.log
relay-log-index = /var/log/mysql/relay-log.index

Isn't there any way to check if permission are replicated? with show slave status, db replication is fine and the status looks similar to below on the masters:

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.10
                  Master_User: repli
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 4045
               Relay_Log_File: relay.000008
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: information_schema
           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: 4045
              Relay_Log_Space: 541
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
1 row in set (0.01 sec)

I have tried this setup twice(once at office and once at home), but the result is same.

Am I doing anything wrong? What's the proper way to replicate along with users and their privileges? Anybody know please?

Thanks!

Answer 1

Your grants are not replicating because you have an (unnecessary) replication filter on INFORMATION_SCHEMA. There is no reason to exclude this, as it is not replicated anyhow. When replication filters are in place, you have to USE a database or your statements are not replicated.

For example...not replicated

Grant all privileges on *.* to foo@localhost identified by 'bar';

This is replicated...

USE anydb
Grant all privileges on *.* to foo@localhost identified by 'bar';

....but instead, just remove that replication filter.

Answer 2

You can try this:

USERPASS="-umysqluserid -pmysqlpassword"
mysql ${USERPASS} -AN -e"FLUSH PRIVILEGES;"
mysql ${USERPASS} -AN -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql ${USERPASS} -AN | sed 's/$/;/g' > MySQLUserGrants.sql

Run those commands on master and server. This will ignore anonymous users.

If the files are identical, grants are fine and replicated properly.

Make sure you are using Statement-Based Replication and not Row-Based. Just a hunch...