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 find myself in need of setting up a master-slave replication between a MySQL server with 4000+ DBs and it's first slave.

The database is currently handling production workload and starting up replication must be done without incurring downtime.

Is there any tool or battle-tested procedure to do that? (dump all the data with minimal locking and saving the masters binlog position with complete accuracy?)

What i've been pondering so far is dump-ing the databases one by one while saving the binlog position, loading the data into the slave then starting the slave with replicate-do-db with all databases loaded so far, stopping replication once slave has caught up, rinse and repeat... But I'm not 100% sure this is the best way to do it.

Thank you, Rares

Later edit: Tables are mostly MyISAM but there are a few InnoDB.

share|improve this question
add comment

2 Answers 2

Is your MySQL database purely InnoDB or does it also include MyISAM? If it is InnoDB, you can use mysqldump with the option "--single-transaction --master-data" to get a reliable dump for your replication slave.

If you have MyISAM unfortunately to get a reliable dump it must lock -all- transactions on the server until the whole backup is complete.. and that time depends on a combination of database size in GB and the number of rows total.

share|improve this answer
    
Sadly, it's mostly MyISAM –  Rares Mirica Jun 2 '11 at 12:53
add comment
up vote 0 down vote accepted

I've created a bash script that incrementally dumps databases from the master and loads them into the slave while keeping track of replicate_do_db.

Thanks Domas Mituzas for this post that allowed me to update replicate_do_db on-the-fly. I also pull the master_log_file and master_log_pos from the dumped sql to issue START SLAVE UNTIL and SELECT MASTER_POS_WAIT in order to load data at the correct point.

Later Edit: In order for this to work as expected ROW BASED REPLICATION is a must.

share|improve this answer
add comment

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.