I am fairly new to the whole replication process of mysql so this may be an easy question to answer. I have a master and and slave. I need to set up another slave so obviously I will need to make the dump from the current slave because I CAN NOT take the master offline for a second. How can I be sure that during the time I am making the dump of the current slave database that I do not miss any master data that is newly created over that time?
Thanks all.
Best Answer
I have an interesting method but you will have to stop replicaton to do
For DB server M1, S1, and S2
STEP01) On S2, install same version of MySQL that S1 has
STEP02) On S2, make sure server_id in /etc/my.cnf is different from server_id in S1
STEP03) On S1,
STOP SLAVE;
STEP04) On S1,
SHOW SLAVE STATUS\G
STEP05) Write down the following two values from STEP 04
STEP06) On S1, mysqldump ... --all-databases > /root/MySQLDataForSlave.sql
STEP07) On S1,
START SLAVE;
STEP08) On S1,
scp /root/MySQLDataForSlave.sql S2:/root/.
STEP09) On S2,
mysql ... < /root/MySQLDataForSlave.sql
STEP10) On S2, run this command in the mysql client using values from STEP05
STEP11) On S2,
START SLAVE;
STEP12) On S2,
SHOW SLAVE STATUS\G
(IfSlave_IO_Running
andSlave_SQL_Running
are Yes, CONGRATULATIONS !!!)STEP13) On S2,
SHOW SLAVE STATUS\G
over and over again untilSeconds_Behind_Master
= 0;I actually wrote a script to automate this in the DBA StackExchange back on Feb 06, 2012.
Give it a Try !!!