Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

3 Answers 3

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

STEP03) On S1, STOP SLAVE;

STEP04) On S1, SHOW SLAVE STATUS\G

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

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

CHANGE MASTER TO
master_host='MHOST',
master_port=MPORT,
master_user='repluser',
master_password='replpass',
master_log_file='RMLF',
master_log_pos=EMLP;

STEP11) On S2, START SLAVE;

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. –  zooooommmm 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 : serverfault.com/a/257426/69271 –  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. –  zooooommmm 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 –  zooooommmm Oct 4 '12 at 17:05

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

MySQL :: MySQL 5.0 Reference Manual :: 16.1.1.9 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. –  zooooommmm Oct 4 '12 at 17:03
    
cool) please accept my answer) –  alexus Oct 4 '12 at 17:10

Your Answer

 
discard

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.