A client has a file-level backup, where every relevant file of a critical webserver is synced to another webserver around 1am.
This includes the
/var/lib/mysql directory where the databases of that server are sitting.
They are shutting down mysql on the backup server, but NOT on the main server (!).
So they are dumping in the mysql files of that still-running mysqld and start mysqld on the backup-server as if that had just crashed mid operation.
Mysql would usually figure it out, but now there happens to be more activity around 1am so sometimes mysql refuses to start without the explicit
innodb_force_recovery = 2 in
Is there any good way to tell a running mysql server to write everything to file right before the backup?
My client absolutely demands a file-level backup, so unfortunaterly something normal like mysqldump is not an option.
Put another way, if I knew my mysqld process where to SIGILL in a minute, how do I minimise the damage done to the database files, without shutting it down?
Im hoping for something along the lines of
mysql -s innodb_flush_buffers_to_disk.
As Bill Karwin commented, mysql doesn't need to write everything to disk to produce valid files, InnoDB already writes in a way that makes every step valid. The problem arose when files got changed during copying. So I ended up making the Client put a small SSD into the server, then moved mysql onto there and copied locally first.
I also slimmed mysql down significantly.
This way the copy operation was sped up to the point where it is virtually atomic.
The chance of the copy getting corrupted is still not 0, but thats honestly what the Client deserves for their backup solution anyways.