#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    2
    Rep Power
    0

    How to replicate MySQL users?


    Hi Guys,

    i'm trying desperatly to replicate the MySQL users from one MySQL server to another.

    General situtaion: Two Debian 7.1 servers with MySQL (5.5.31-0+wheezy1-log (Debian)) on each.
    Master-Master replication between them is working fine - for my custom database.
    But when it comes to sync the MySQl users from DB MySQL nothing is happen. No errors. Just no users on node 2.

    Didn't find anything at web / forum search.

    Thanks in advance for hints.
  2. #2
  3. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    You have probably got some kind of configuration like:
    http://dev.mysql.com/doc/refman/5.0/...d-ignore-table
    or
    http://dev.mysql.com/doc/refman/5.0/...nlog-ignore-db
    or
    http://dev.mysql.com/doc/refman/5.0/...cate-ignore-db

    What I'm saying is that default is actually to replicate everything, but pretty often the replication configuration is set up to ignore replicating the MySQL database.
    And this ignore configuration can be performed in a lot of different ways from not even writing down any changes to the binary log on the master (binlog-ignore-db) or ignore a database during replication (on the slave).

    So check your configuration and hopefully you will find the ignoring parts
    /Stefan
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    2
    Rep Power
    0
    Thanks for your advice Stefan.
    Unfortunately I've checked this several times without result.

    Here's the current slave status (from node2):

    mysql> show slave status \G

    Slave_IO_State: Waiting for master to send event
    Master_Host: node1
    Master_User: repl
    Master_Port: 3306
    Connect_Retry: 60
    Master_Log_File: mysql-bin.000009
    Read_Master_Log_Pos: 107
    Relay_Log_File: mysqld-relay-bin.000028
    Relay_Log_Pos: 210
    Relay_Master_Log_File: mysql-bin.000009
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    Replicate_Do_DB: owncloud,mysql
    Replicate_Ignore_DB:
    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: 107
    Relay_Log_Space: 513
    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:
    Replicate_Ignore_Server_Ids:
    Master_Server_Id: 1

    ###########################

    And this is the master status (from node1):

    mysql> show master status \G

    File: mysql-bin.000009
    Position: 107
    Binlog_Do_DB: owncloud,mysql
    Binlog_Ignore_DB:
  6. #4
  7. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    Perform a GRANT and then check by extracting the SQL commands from the binlog to find out if the changes are written to the binlog.
    That way you will know if the problem is on the master och slave side.
    If the changes to the mysql database is present in the binlog then the master is performing correctly and you will have to look a the slave.
    But if the changes aren't written to the binlog then you will have to troubleshoot on the master.
    /Stefan

IMN logo majestic logo threadwatch logo seochat tools logo