I'm working out the fastest way to spin up a new slave server, which will only require some of our databases. The basic plan is:
- Stop existing slave
- Take LVM snapshot and re-start slave (so it can go back to handling traffic)
- Mount snapshot
- Delete directories of unneeded databases in snapshot
- Make a tarball of entire mysql directory, excluding the directories of the databases I don't want to transfer, and transfer to new slave
- Swap out mysql directory on new slave. Start mysql there and update user table and grants, then flush privileges.
- Update permissions on master to allow replication user from new slave IP.
What I'm not sure about is whether just deleting the database directories will work, or if I will run into problems with metadata not matching. If necessary I could mount the snapshot at the actual mysql location, and start a CLI session to drop the databases the right way, but it's more effort and will take longer, plus put more load on the source server, so if I can just delete the directories that would be ideal. Maybe delete the directories and clean up the appropriate information_schema table(s) afterward, on the new slave?
The purpose of all this is that I'm planning to be able to react quickly in the event of an unanticipated traffic spike, so the priorities are speed in spinning up the new slave, as well as minimizing added load and downtime of the existing slave, since it will be under high load in this situation.