MySQL - Master to master replication without downtime in Live production
I am glad you were able to mysqldump 1.5TB database. You are sure patient.
Now for the reality check. You will some downtime for reboots and pausing.
No need to mysqldump anymore.
STEP 01
Did you put this line in /etc/my.cnf
on both Master1 and Master2 ?
log-slave-updates
If you not sure, run this on both Master1 and Master
select @@global.log_slave_updates;
If you get this on both Master1 and Master2
mysql> select @@global.log_slave_updates;
+----------------------------+
| @@global.log_slave_updates |
+----------------------------+
| 1 |
+----------------------------+
1 row in set (0.00 sec)
then goto STEP 02
.
This needs to be in /etc/my.cnf
on both Master1 and Master2.
If you did not, please add it and restart mysqld on both Master1 and Master2.
STEP 02
Did you enable binary logging on Master2 ?
You should have a line in Master1's /etc/my.cnf
like this
log-bin = ....
Please make sure Master2 has it.
If you are not sure if it has it, login to MySQL on Master2 and run
mysql> show binary logs;
If it echoes
mysql> show binary logs;
ERROR 1381 (HY000): You are not using binary logging
Then, add that log-bin
line to /etc/my.cnf
in Master2 and restart mysql in Master2.
Once log-slave-updates and log-bin have been enabled on both Master1 and Master2, you are ready to setup replication in the other direction
STEP 03
On Master2
STOP SLAVE;
FLUSH TABLES;
FLUSH LOGS;
SHOW BINARY LOGS;
STEP 04
On Master1
CHANGE MASTER TO
MASTER_HOST='IP or Hostname of Master2',
MASTER_PORT=3306,
MASTER_USER='...',
MASTER_PASSWORD='...',
MASTER_LOG_FILE='First Binary Log On Master2',
MASTER_LOG_POS=4
;
SHOW SLAVE STATUS\G
STEP 05
Test Connectivity from Master1 to Master2
On Master1
START SLAVE IO_THREAD;
DO SLEEP(10);
SHOW SLAVE STATUS\G
Look for Slave_IO_Running
. It should be 'Yes'.
Run SHOW SLAVE STATUS\G
like 3-5 times. Relay_Log_Space
should be increasing.
STEP 06
Start processing SQL from Relay Logs
Run this on Master1
START SLAVE SQL_THREAD;
DO SLEEP(10);
SHOW SLAVE STATUS\G
Look for Slave_SQL_Running
. It should be 'Yes'.
Look for Seconds_Behind_Master
. It should be 0. If it greater than 0, keep running SHOW SLAVE STATUS\G
until Seconds_Behind_Master
is 0.