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.

  • It seems like something you could just try. That said, MariaDB runs a repair process when it starts up, but as of the last time I used MySQL it didn't (newer version of MySQL could have changed this), so you would probably want to use MariaDB or manually run the repair. – yakatz Mar 6 '18 at 1:47

It's not a good idea. InnoDB keeps information about tables, fields in the dictionary that resides in ibdata1. If you delete database directory the database won't be visible for clients. But there will be side effects. For example, you won't be able to create tables with same name.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.