0

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

3
  • What values did you use for MASTER_LOG_POS and MASTER_LOG_FILE?
    – Ladadadada
    Jun 9, 2012 at 6:15
  • Are you using InnoDB or MyISAM or both or something else?
    – Ladadadada
    Jun 9, 2012 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, 2012 at 10:07

1 Answer 1

3

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .