Re: Start slave fails

[Rick James]

The steps you outlined in your first posting are missing the necessary locks (or shutdown). Without these, you are catching one table in one state, another table with newer records.

If you don't have a slave to clone, all the "off the shelf" methods of creating a slave involve stopping or locking the Master long enough to take a full, "consistent", dump.

With code, you could write a complicated program to read from the Master and write to the Slave. This would copy most of the data, then copy some more, finally lock the master long enough to locate and copy the last few records and capture the binlog position. The program would have to understand how all the tables are structured so that it could find any changes since the last pass. The tables you have may not be structured to allow such. Some tricks for such:

* AUTO_INCREMENT, and you only INSERT, never DELETE or UPDATE.
* TIMESTAMP, and you never explicitly set that field, and the field is indexed; then you could find all new/modified rows using it. Oh, and DELETEs are a no-no.
* Otherwise, you are stuck with locking _all_ tables long enough to dump the entire table.

There are for-sale dump packages, but they tend to work well for InnoDB, but resort to brute force (as above) for MyISAM tables. (Or they ignore consistency for MyISAM tables.)