Mysql – Filesystem copying inactive DBs on active MySQL server


My question is related to this question and answer:
Copying datadir directory enough for backing up a database with InnoDB tables?

I want to use a filesystem copy on a whole DB without shutting down the whole server. The documentation references using FLUSH TABLES ... FOR EXPORT and then UNLOCK TABLES to be able to safely do this.

But what happens if I use those queries to target a whole DB like FLUSH TABLES example_db.* FOR EXPORT then filesystem copy the whole example_db folder then run UNLOCK TABLES example_db.*

Is that safe? Or just possibly safe because new tables in the DB could theoretically be created after the existing tables are locked which would then potentially not be flushed when the filesystem directory is copied? Is there something extra I have to do here to lock the whole database?

Best Answer

Whether it is safe or not -- do you want to be stalled until it finishes? You may as well stop MySQL for the dump.

Even better would be to set up LVM, then

  1. Stop mysqld
  2. Do snapshot; this takes a minute or so, regardless of the size.
  3. Restart mysqld