Mysql master-master權限複製問題

我從 ServerFault is-it-ok-to-replicate-mysql-dbmysql的文章中了解到複製數據庫以複製使用者和權限是安全的。我為每個主節點（總共 4 個節點）設置了一個從節點的複制。一切正常，數據庫，表已同步，但權限未同步，應用於數據庫的權限不會從任何伺服器複製或複製到任何伺服器。master-master

mysql我將( ) db轉儲mysqldump -uroot -pmypass mysql > /tmp/mysql.sql到原始伺服器上並將其恢復到所有伺服器，並為repli使用者提供了超級權限，如上述連結中所述。但是權限不會從任何伺服器同步或同步到任何伺服器。我GRANT ALL PRIVILEGES...在一個測試數據庫上發布並檢查過，SHOW GRANTS for..但遠端伺服器上沒有顯示權限（flush privileges在我授予的位置和所有權限上都顯示了）。

發布配置可能沒有用，但仍然在這裡：

大師1(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

有沒有辦法檢查權限是否被複製？使用show slave status, db 複製很好，master 上的狀態如下所示：

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)

我已經嘗試過兩次這種設置（一次在辦公室，一次在家裡），但結果是一樣的。

我做錯什麼了嗎？與使用者及其權限一起複製的正確方法是什麼？請問有人知道嗎？

謝謝！

您的授權沒有複製，因為您在 INFORMATION_SCHEMA 上有一個（不必要的）複製過濾器。沒有理由排除這一點，因為它無論如何都不會被複製。當複製過濾器到位時，您必須使用數據庫，否則您的語句不會被複製。

例如…未複製

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

這是複制…

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

….但是，只需刪除該複製過濾器即可。

引用自：https://dba.stackexchange.com/questions/11023