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'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

share|improve this question
    
What values did you use for MASTER_LOG_POS and MASTER_LOG_FILE? –  Ladadadada Jun 9 '12 at 6:15
    
Are you using InnoDB or MyISAM or both or something else? –  Ladadadada Jun 9 '12 at 6:24
    
I used the values from mysql dump provided by option --master-data=2. I use mostly InnoDB however there is one Wordpress installation that probably use MyISAM –  HTF Jun 9 '12 at 10:07

1 Answer 1

up vote 3 down vote accepted

The method you're using (specifically the --master-data=2 option) should be enough to get you a consistent dump, since --master-data implies --lock-all-tables (and the resulting offences against database performance that are implied). The caveats specified in the mysqldump(1) manpage for the --lock-all-tables option should be observed.

I would suggest that the article you cite was either written prior to the availability of --lock-all-tables (or it's implicit activation with --master-data) or was written by someone incapable or unwilling to read documentation. You may wish to query the author to determine which is the case.

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.