1

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 my.cnf.

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.

Update:
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.

3
  • I know this is stupid, but I'm not paying myself. I'm on damage controll here, not trying to find an actually good solution.
    – Redjard
    Nov 8, 2021 at 13:18
  • Yeah, it's called "shutdown".
    – Rick James
    Nov 8, 2021 at 19:21
  • On this server, systemctl stop mysqld is aliased to systemctl stop mysqld; fire Redjard
    – Redjard
    Nov 10, 2021 at 9:08

2 Answers 2

1

No, there is no way to ensure InnoDB flushes everything to disk, except by doing a clean shutdown of mysqld.

InnoDB has a few background threads that continue to modify pages in the tablespace, even if a global lock blocks out any query activity. These threads do asynchronous work like flushing modified pages to storage, purging the undo log, and merging the change buffer into indexes. This work can continue for hours even if all query activity is blocked.

If you run a simple filesystem backup while this work is in progress, there's a strong chance that the files would change during the filesystem backup, and the backup would not read an InnoDB tablespace file atomically. That is, the tablespace file would change in the time it takes to read from the start to the finish. Since the pages inside an InnoDB tablespace are full of pointers to other pages, this is likely going to result in a corrupted file.

The only way to prevent this is to shut down mysqld.

This is why physical backup tools like Percona XtraBackup are very useful. While xtrabackup is copying the tablespace files, it is also progressively reading the InnoDB redo log (ib_logfile*) so it can merge those changes into the tablespaces, and won't miss any as the log wraps around. This is safe to do on a running MySQL Server.

So one solution would be to make a backup using xtrabackup before the filesystem backup runs. Then if you need to restore from that filesystem backup, use xtrabackup's copy, and overwrite the one in the datadir.

A better alternative to this would be to backup the filesystem using LVM snapshots, which are atomic. At least that way you don't get the risk of a race condition, so you should be able to do crash recovery on the InnoDB tablespaces and get a viable restore. But this is probably not acceptable to the client either, if they insist on simple filesystem copy commands.

1
  • Loss of the ChangeBuffer can be recovered from. (I don't know how much slower this is than with a clean shutdown.)
    – Rick James
    Nov 8, 2021 at 19:29
0

Plan A:

Have a Replica, do the shutdown on the replica. And do it some time before the rsync (file-level copy).

Plan B

Or set up LVM so you can snapshot the Primary server. You still need to do the shutdown, but it could be separately done an hour (say) before the rsync. Then the rsync is done against the "snapshot". (And be sure to drop the snapshot; otherwise, it grow unchecked in size.)

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.