Mysql Master-Master replication is not working??

usersanjib

Registered
Jan 13, 2014
2
1
3
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,
 
  • Like
Reactions: double_t

dalem

Well-Known Member
PartnerNOC
Oct 24, 2003
2,977
152
368
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
 
  • Like
Reactions: Tomas Gonzalez

usersanjib

Registered
Jan 13, 2014
2
1
3
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... ;)
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,913
2,201
363
I am happy to see the issue is now resolved. Thank you for updating this thread with the outcome.