Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
    
Anybody has any idea? –  user53864 Jan 20 '12 at 17:40
add comment

2 Answers

up vote 3 down vote accepted

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.

share|improve this answer
    
You are absolutely right!, I just did use mysql and tried granting the permission and it easily replicated to other servers. But I want to grant directly without use any database because I mysql forget sometimes and just do the grant which will not be replicated. You were telling something about the filter which I don't have any idea, how do I remove that?. Thank you very much! –  user53864 Jan 21 '12 at 2:27
    
You have replicate-ignore-db=information_schema in my.cnf. Get rid of that line and restart MySQL. Also remove binlog-ignore-db. –  Aaron Brown Jan 21 '12 at 4:19
    
But it's said replicating information_schema is not at all recommended. Doesn't it cause any problem if I remove ignore statements?. Thanks! –  user53864 Jan 21 '12 at 5:00
    
Where do you see that it will cause problems? No, it won't cause any problems. I don't even think entries in information-schema are written to the binary log. I will verify. I have dozens of slaves running in both traditional master slave & master master on a very high traffic ecommerce site & have never had a replication problem related to information schema. –  Aaron Brown Jan 21 '12 at 12:32
    
I did read somewhere in post in the ServerFault saying not good to do information_schema, I didn't found the link back to paste here. I am thinking it will have debian related information(user) of specific machine and thinking if overwritten would lead to some malfunction. If it seems no problem, I'll do that. Thanks for your great reply! –  user53864 Jan 22 '12 at 13:36
show 2 more comments

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...

share|improve this answer
    
Waited for you thinking if there is any luck am I blessed with like an answer of Ronaldo.I feel that the complete site is controlled by you having a great interest to help others. Awesome!. Ofcourse I am speaking irrelevant but a small feeling that you should be rewarded. Hope you keep up. –  user53864 Jan 21 '12 at 2:39
    
Wow, is that a script for listing all Users' privileges to execute MySQLUserGrants.sql on other servers?. In my web environment triggers and routines are vastly used and I heard statement-based replication has issues in replicating those and so thinking of MIXED on production, does it make any sense? –  user53864 Jan 21 '12 at 2:46
    
You can also use pt-show-grants from the Percona Toolkit to get this information. –  Aaron Brown Jan 21 '12 at 12:46
    
Why are you recommending against row based replication? –  Aaron Brown Jan 21 '12 at 12:46
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.