On 2023-02-04 Scott Toderash wrote:
Assuming you use mysqldump. It does lock tables. If it didn't then data could change while the backup of that table is happening, producing unpredictable results.
You could set up MySQL replication to another machine and run your backup on that one instead. That scenario would avoid the delay you are seeing but seems like a lot of trouble for this unless you need constantly consistent performance.
Doh! I didn't see your reply as for some reason it wasn't in the thread. I need to eyeball things more carefully.
Glad you thought up the same replication idea that dawned on me in the interim.
And yes, I needed to also consider the implications on not doing a full db-wide lock. I think the new transactional method Adam found will achieve the same thing as a db-wide lock without actually doing a db-wide lock: it looks like the xaction will be initiated db-wide (i.e. once for the whole db).