0

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?

Improve this question
0

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
Improve this answer
  • Why would my other DBs be stalled? If I lock one DB, the others should still be accessible and writeable. I'm planning on locking a single DB (that isn't actively used by the web-server) while another DB (that is actively used by the web-server) remains up and accessible (because it isn't locked.) If I shutdown mysqld then all DBs go down and take the website down as well while I run this backup. – Omn Mar 3 '18 at 5:54
  • @Omn - How can you know when a particular DB is not going to be used for the next few minutes (for the backup)? – Rick James Mar 3 '18 at 19:28
  • Because I control access to the DB? I don't get how your questions are supposed to help answer this question. – Omn Mar 3 '18 at 19:47
  • @Omn - You stop the user of one DB while you dump; then move on to the next DB? I don't trust any InnoDB "filesystem copy" without actually stopping mysqld. There are others who do have techniques. – Rick James Mar 3 '18 at 21:55
  • If you don't know how FLUSH TABLES ... FOR EXPORT works why are you answering this question? – Omn Mar 4 '18 at 0:03
0

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).

Improve this answer
  • Is there a way to lock the creation of new tables for just one DB or for all DBs without blocking other reads and writes to the existing tables? – Omn Mar 7 '18 at 16:16
  • No. But you can use Percona Xtrabackup. You will have a consistent copy of your databases, without using locks. – Federico Razzoli Mar 7 '18 at 16:41

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.