MySQL slave server from dumps
- by HTF
I've created a slave server from live machine which is acting as a master now.
I use the following procedure to create it:
mysqldump --opt -Q -B --master-data=2 --all-databases > dump.sql
then I imported this dump on the new machine, applied the "CHANGE MASTER TO..." directive with a log file/position from the dump.
Please note that I have around 8000 databases and I didn't stop the master while the dumps were running.
The replication works fine but is this a properly method for creating a slave server? I'm planning to promote this slave to a master (different location) so I would like to make sure that there is a 100% data consistency between the servers.
I've found this article where it says:
The naive approach is just to use mysqldump to export a copy of the
master and load it on the slave server. This works if you only have
one database. With multiple database, you'll end up with inconsistent
data. Mysqldump will dump data from each database on the server in a
different transaction. That means that your export will have data from
a different point in time for each database.
Thank you
