Just trying to get some information on the best route to setup replication for this DB I've got. I'm using MySQL 5.1
I found this and it seems like the idea we have in mind.
We're looking to have the Main Master be replicated by a Master/Slave and then a final machine replicating from the Master/Slave. That solution sounds wonderful and turning on –log-slave-updates on the Master/Slave machine sounds like what I want.
The question I have is when the Main Master(Master1) goes down we tell the services to write to Master/Slave(Master2) and then what happens with Master1? We rebuild it etc.. but where in the link does it go? Does Master/Slave(Master2) become the new Main Master? And does the Slave only machine get moved to Master/Slave?
Or does Master1 go down and gets rebuild and just becomes Master2(Master/Slave) keeping the Slave machine just that, a slave?
I feel like it's the latter of the 2 options. Just looking for some advice.
Thanks!
Best Answer
Using the topology you mentioned from the MySQL Documentation
Let's setup the first scenario
EXAMPLE IPs for Each DB Server
Master1
:10.20.30.40
Master2
:10.20.30.50
Slave_1
:10.20.30.60
repluser@'%'
replpass
Make sure binary logging is enabled on all the Slaves
We will do the following
Step 01 : On Master2, run the following
Step 02 : Move your CNAME/VIP to Master2
Step 03 : On Slave_1, run
mysql> RESET MASTER; FLUSH TABLES;
Step 04 : On Slave_1, dump the data
Step 05 : When Master1 comes back up, rsync or scp
MySQLData.sql.gz
from Slave_1 to Master1Step 06 : Login to MySQL on the Master1 and setup it up to replicate from Slave_1
Don't worry about the real binary log filename and position.
Using
--master-data=1
embedsCHANGE MASTER TO
command with the real coordinates on line 23 of a standard dump.You can see it with the following
Step 07 : Load the data into Master1
Step 08 : Login to MySQL on Master1
and Make sure
Slave_IO_Running
andSlave_SQL_Running
both sayYes
EPILOGUE
When Done, the topology should be
Master1
:10.20.30.50
Master2
:10.20.30.60
Slave_1
:10.20.30.40
DISCLAIMER
Please try this out on Test Servers before deploying to Production
GIVE IT A TRY !!!
Note: I would advise that you have multiples slaves
SELECTs