Mysql Master-Master replication is not working??

[usersanjib]

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


Please some expert give me suggestion to solve this issue.

Thanks,

 

[cPanelMichael]

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.

 

[dalem]

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

 

[usersanjib]

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

 

[cPanelMichael]

I am happy to see the issue is now resolved. Thank you for updating this thread with the outcome.

 

[double_t]

Helo usersanjib, did this work stable for you ? Thanks