4 ms·
Technically, you can set up a full lock (FLUSH TABLES WITH READ LOCK), start the individual table dumps (with --single-transaction), and release the full lock (
by falcolas 13y ago
Technically, you can set up a full lock (FLUSH TABLES WITH READ LOCK), start the individual table dumps (with --single-transaction), and release the full lock (UNLOCK TABLES), and get a consistent snapshot of the database (this is the method used by mydumper, referenced above).
The individual dumps will be performed within transactions, and the global lock at the beginning will ensure that all transactions started at the same point. Using locks like this does not prevent reads, just writes, so you don't have a major interruption of service.
Of course, this is assuming that you're using InnoDB as your storage engine; otherwise you need to maintain the global lock throughout the dump of the non-InnoDB tables.