MySQL Slave set-up with lots of databases

Question

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.

Answer 1

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.

Answer 2

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.