The Community Forums

Interact with an entire community of cPanel & WHM users.
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Mysql Master-Master replication is not working??

Discussion in 'Database Discussions' started by usersanjib, Jan 13, 2014.

  1. usersanjib

    usersanjib Registered

    Joined:
    Jan 13, 2014
    Messages:
    2
    Likes Received:
    1
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hi all,
    I am trying to setup a master-master replication between my Cpanel mysql server and normal centos 6.3 mysql server.
    In my scenario I want to replicate a DB named "replication". I will explain what i did on my servers step by step below.

    Executed the following query on servers:

    On Cpanel Server
    mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'my IP addresss of another master server' IDENTIFIED BY 'password';
    mysql> FLUSH PRIVILEGES;

    On Master Server2

    mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'IP of my Cpanel server' IDENTIFIED BY 'password';
    mysql> FLUSH PRIVILEGES;

    On Cpanel Host edited my.cnf file as below.

    [mysqld]
    log-bin=mysql-bin
    binlog-do-db=replication
    server-id=100
    auto_increment_increment=4
    auto_increment_offset=1
    innodb_flush_log_at_trx_commit=1
    sync_binlog=1

    On My Centos Server2 edited my.cnf as below.
    [mysqld]
    log-bin=mysql-bin
    binlog-do-db=replication
    server-id=200
    auto_increment_increment=4
    auto_increment_offset=1
    innodb_flush_log_at_trx_commit=1
    sync_binlog=1

    After editing these entries I restarted mysql service on both servers. and checked the master status on both servers as below

    On Cpanel Mysql server:

    mysql > SHOW MASTER STATUS;
    +------------------+----------+--------------+------------------+
    | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +------------------+----------+--------------+------------------+
    | mysql-bin.000002 | 107 | replication | |
    +------------------+----------+--------------+------------------+

    On my Second Master Server

    mysql > SHOW MASTER STATUS;
    +------------------+----------+--------------+------------------+
    | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +------------------+----------+--------------+------------------+
    | mysql-bin.000003 | 109 | replication | |
    +------------------+----------+--------------+------------------+

    After viewing the the master status I execute another query on both servers.

    On Cpanel Mysql Server:
    mysql> CHANGE MASTER TO MASTER_HOST='IP of my Second Master Server',
    -> MASTER_USER='repl_user',
    -> MASTER_PASSWORD='password',
    -> MASTER_LOG_FILE='mysql-bin.000003',
    -> MASTER_LOG_POS=109;

    On My Second Master Server:

    mysql> CHANGE MASTER TO MASTER_HOST='IP of my Cpanel mysql Server',
    -> MASTER_USER='repl_user',
    -> MASTER_PASSWORD='password',
    -> MASTER_LOG_FILE='mysql-bin.000002',
    -> MASTER_LOG_POS=107;

    On both servers I started slave by executing the mysql command " START SLAVE;"
    By doing these steps my Cpanel host is acting as a Master Server and replicating the database "replication" to my second Master server properly but this is working only one way. when I edit my second master's database "replication" it will not replicating on Cpanel Mysql server.

    So, I checked the slave status executing the query on Cpanel mysql server:
    mysql> SHOW SLAVE STATUS\G;

    and on Second Master Server:
    mysql > SHOW MASTER STATUS;

    Now the Log Position for the master databse changed automatically. Why???? :confused:


    Please some expert give me suggestion to solve this issue. :mad:

    Thanks,
     
    double_t likes this.
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,723
    Likes Received:
    660
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Hello :)

    You may want to consult with a qualified system administrator if you do not receive additional user feedback on this topic. A list of system administration services is available on our application catalog:

    cPanel Application Catalog - System Admin Services

    Thank you.
     
  3. dalem

    dalem Well-Known Member
    PartnerNOC

    Joined:
    Oct 24, 2003
    Messages:
    2,577
    Likes Received:
    40
    Trophy Points:
    48
    Location:
    SLC
    cPanel Access Level:
    DataCenter Provider
    your auto-increment-offset = 1 on one of the servers needs to be set to auto-increment-offset = 2 I do believe

    this is one of our set ups master master
    server 1
    ----------
    server-id = 1
    log_bin = /var/lib/mysql/mysql-bin.log
    binlog_do_db = example
    auto-increment-increment = 2
    auto-increment-offset = 1

    server 2
    ---------
    server-id = 2
    log_bin = /var/lib/mysql/mysql-bin.log
    binlog_do_db = example
    auto-increment-increment = 2
    auto-increment-offset = 2
     
  4. usersanjib

    usersanjib Registered

    Joined:
    Jan 13, 2014
    Messages:
    2
    Likes Received:
    1
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    This issue is solved with the same code above and its working properly without changin anything. In my environment my Second master server has not public IP assigned, It was in my intranet, In my default gateway (router) i was forwarded the port 3306 to my Second master server. I just login my Second master server and execute this query GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'IP of my Default Gateway' IDENTIFIED BY 'password';

    after that It start working... ;)
     
  5. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,723
    Likes Received:
    660
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  6. double_t

    double_t Member

    Joined:
    Dec 2, 2007
    Messages:
    19
    Likes Received:
    1
    Trophy Points:
    3
    Helo usersanjib, did this work stable for you ? Thanks
     
Loading...

Share This Page