Filesystem copying inactive DBs on active MySQL server

Question

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.

https://dev.mysql.com/doc/refman/5.7/en/flush.html#flush-tables-for-export-with-list

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?

Answer 1

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

Answer 2

A quick look to the syntax will answer your question: FLUSH TABLES ... FOR EXPORT accepts a list of tables, so it won't lock tables created after the lock has been acquired.

FLUSH TABLES WITH READ LOCK acquires a global lock, which affects of course CREATE TABLE. I understand that you don't want to use it precisely because the lock is global; but FLUSH TABLES WITH READ LOCK is meant for copying all tables, FLUSH TABLES ... FOR EXPORT is not.

There is also another risk: the connection holding the lock could die, realising the lock. This risk, of course, also exists in the case of FLUSH TABLES ... FOR EXPORT.

However, you also stated that you can stop writes to the databases you want to copy. If you can do this reliably, you don't really need to lock tables. You can simply:

1. Stop the writes
2. FLUSH TABLES;
3. Copy
4. Write again

There are of course alternatives more intrinsically secure, like mysqldump and Xtrabackup. But I'll assume that you know how they work and you decided that a simple file copy is better for your use case (for example, because data are too big for mysqldump and most of your tables are not InnoDB).