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.


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.

  • Sadly, it's mostly MyISAM – Rares Mirica Jun 2 '11 at 12:53

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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