Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
you can dynamically update mysql without restarting – sharif Oct 4 '12 at 15:50
up vote 3 down vote accepted

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



STEP05) Write down the following two values from STEP 04

  • Master_Host (MHOST)
  • Master_Port (MPORT)
  • Relay_Master_Log_File (RMLF)
  • Exec_Master_Log_Pos (EMLP)

STEP06) On S1, mysqldump ... --all-databases > /root/MySQLDataForSlave.sql


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



STEP12) On S2, SHOW SLAVE STATUS\G (If Slave_IO_Running and Slave_SQL_Running are Yes, CONGRATULATIONS !!!)

STEP13) On S2, SHOW SLAVE STATUS\G over and over again until Seconds_Behind_Master = 0;

I actually wrote a script to automate this in the DBA StackExchange back on Feb 06, 2012.

Give it a Try !!!

share|improve this answer
This is actually the process I have used a couple of times and worked. But I am not a DBA so was never 100% sure that I managed to grab all of the data and not miss a beat. This particular case the master does alot of transactions so I can not miss one. Thank you. – zaneywisdom Oct 4 '12 at 17:08
I have done things like this for years. I wouldn't worry. Here is why: Look back at Step05. The RMLF and EMLP represents that last binlog entry from the Master that got executed on the Slave. No matter where replication is, I always start from that position. All relay logs are erased and SQL is freshly downloaded from RMLF,EMLP. The only thing that can go wrong is if the binlog RMLF does not exist. This should never be the case since you are using an active Slave that can see the Master and its binlogs. I also have an rsync technique : – RolandoMySQLDBA Oct 4 '12 at 17:33
The rsync is awesome. I just read through it and will give it a try later. Thanks for linking that here. – zaneywisdom Nov 3 '12 at 14:46

Disable replication on the slave before taking the snapshot. Hint - stop the IO thread first and let the SQL thread complete before stopping it then generating the snapshot.

share|improve this answer
Thanks for that hint I never knew that before – zaneywisdom Oct 4 '12 at 17:05

Use following guide to introduce additional slave to your existing environment:

MySQL :: MySQL 5.0 Reference Manual :: Introducing Additional Slaves to an Existing Replication Environment

share|improve this answer
Great Thanks. I completely did not see that reference manual when I was looking around. Much appreciated. – zaneywisdom Oct 4 '12 at 17:03
cool) please accept my answer) – alexus Oct 4 '12 at 17:10

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.