Re: replication - slave copy - unclear points
Posted by:
Rick James
Date: November 09, 2011 09:04AM
> (we have InnoDB tables and the dump is one without locking the tables first)
This is not a "consistent" dump, so it should not be used.
> import a nonexistent db into the new machine I get "db does not exist"
Well, the dump apparently does not have the necessary CREATE DATABASE statements. (Again, your dump is not good, at least for this purpose.)
We did even get to the place where mysqldump can capture the binlog position (etc), so that the new slave knows how where to talk to the master.
For creating a new slave, given that you have a functioning slave, I like to do this:
1. Stop using the Existing slave. (Otherwise clients will have troubles.)
2. Stop mysqld on the existing slave. (So everything is flushed, and we can do file copies instead.)
3. Copy the entire mysql tree to the new slave. Note, this must include directories (for databases), ibdata1, iblog*, relay*, etc. (This will include information on how to talk to the Master, and where in the binlog to continue.)
4. Copy my.cnf (my.ini), but be sure to change server_id. (All server_ids must be distinct.)
5. Start mysqld on both slave. START SLAVE (if necessary).
6. Wait for slaves to catch up on replication. (Otherwise, clients will be reading stale information.)
7. Put the slaves in use.
It is possible to do similar actions with mysqldump, but it is probably no faster, since taking a "consistent" and complete dump locks the Slave for about as long as the above procedure does.