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.

To ensure the data on the slave and master are in the same place for replication, the new master should be stopped and data transferred to the new slave before starting the master again, at least this is one option.

How can I start replication of a MySQL server without stopping the master?

share|improve this question

4 Answers 4

up vote 5 down vote accepted

easy option - if you have small amount of data use

mysqldump --master-data -uyouruser -p.... -a > dump.sql

this will lock all tables and generate dump with info about current master position. dump will lock all tables - probably not something you want to do when you have busy website and 20gb data to dump.

more advanced option. take your data on lvm partition and use lvm snapshot. that is in one process lock all tables FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; in another proces run sync few times for a good measure and create lvm snapshot of your data / innodb logs. just after creating snapshot [ that takes 2-3 seconds for me ] unlock all tables.

now you have consistent snapshot and your sql server still running. mount snapshot and 'ship it' asap to another server. there run mysql on this data, let innodb reover all data from it's logs and run mysqldump which output put to slave.

start slave using information from SHOW MASTER STATUS that you've run just before taking LVM snapshot.

mylvmbackup does exactly that. read about it here.

share|improve this answer
1  
This is good advice. Note that the master must already be logging everything to the binlog for this to work, otherwise the slave will have nothing to replicate from. –  John Dalton Jun 3 '09 at 1:30
    
And nowadays you may want to use Perconas Xtrabackup. –  Prof. Falken Nov 21 '11 at 15:48

FLUSH TABLES WITH READ LOCK on the master, copy the data to the slave (possibly with periodic SELECT NULL operations on the client that's talking to the master to prevent the locks from being lost to connection timeouts), then start replication on the slave and UNLOCK TABLES on the master.

All the master operations specified need to be within a single client session. Don't go trying to exit it while the copy is in progress.

share|improve this answer

As pQd mentioned above:

mysqldump --master-data -uyouruser -p.... -a > dump.sql

but why not ssh into the remove machine and pipe the data directly trough ssh?

share|improve this answer
    
sometimes ssh can slower than let's say nc mysqlperformanceblog.com/2009/05/31/… –  pQd Jun 1 '09 at 20:06
    
netcat kicks ass... you are right... (how about security?) –  elcuco Jun 2 '09 at 16:32
1  
@elcuco netcat comes with all the security benefits of telent and ftp combined, that is: no security what so ever if your data crosses untrusted network. –  pQd Jun 3 '09 at 9:18

I can't comment yet, but what about the difference between MyISAM and INNODB? I'm not sure you can simply do a lock tables with INNODB and expect a good snapshot.

share|improve this answer

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.