Hi, The attached file is a basic idea of cross-engine backup which we have implemented a year ago.

This would be VERY great, and would make it more like SQL Server.

I love the way BACKUP/RESTORE works in SQL Server.

It would be very nice and convenient if we could do BACKUP/RESTORE for single databases, so I could restore a copy of the database in the same server, to restore data of a column that got messed up, etc...

Thank you very much for this.

As a within storage engine detail, a linux only function that reduce the contention is to use ioctl_ficlonerange, where available to gain a copy of the key table contents shallowly but immune to changes and release backup locks as soon as possible.

Server-side backup will prevent errors like MDEV-21255 or MDEV-27551 from occurring, because the server can simply replicate its write-ahead log to the backup stream, and the server knows when it is deleting or renaming files. If needed, the throughput of any write workload that the server is concurrently handling would slow down.

Some years ago, I thought that the most straightforward implementation of a server-side InnoDB backup would be to (re)write changed pages from the buffer pool to a result set stream.

• Advantage: Whatever tool restores the stream can simply restore all data files directly.
• Disadvantage: The stream basically becomes a log that stores full page images even for tiny changes (such as updating one byte on the page).

With MDEV-12353 and MDEV-14425, I think that the best format for streaming InnoDB changes would be the InnoDB log file. The server-side backup could dump InnoDB data file images as well as an append-only copy of the server’s log, written as an ib_logfile0 that can be applied by the recipient. This log file would have similar format as the one that is currently created by mariadb-backup --backup.

For incremental backups, it could be easiest to implement redo log archiving, say, by duplicating the circular ib_logfile0 with append-only file(s) that might be named ib_logfile.%llu and start at the LSN identified in the header or in the file name. An incremental backup client would request all log from the LSN where it last left off, and the server would either say "sorry, our log is not that old" or construct a corresponding log file (starting with an archived log file that is at least as old as that LSN). In the MDEV-14425 format, cutting some LSN from the beginning of an append-only log file is trivial: just rewrite the header so that the log payload area starts at the desired LSN, and then copy everything starting from the byte offset that represents that LSN.

The suggested "Interface between client and server" would basically replace the internal datasink.h interface that is being used in mariadb-backup, and it would be similar to the datasink_xbstream implementation.

For InnoDB, the server-side backup would have the benefit that of using the buffer pool as a cache. Asynchronous read-ahead can load pages to the buffer pool.

The algorithm for a full backup could be as follows:

1. Initiate a log checkpoint and start streaming log log records directly from log_sys.buf.
2. Enumerate all persistent tablespaces. (They may be dropped at any time because we are not holding locks; we must prevent bugs like MDEV-27551.)
3. For each tablespace that we found, acquire U-lock on every page (based on the current size of the tablespace) and dump it to the stream.
   • Skip any pages that do not exist in the buffer pool and are marked as free in the allocation bitmap page.
   • If a page that we read to the buffer pool is corrupted, issue an error message, and skip the page. (Backup must not cause a server crash like MDEV-13542.)
   • Skip any pages that are marked as free in the page descriptor, similar to MDEV-15528.
   • Skip any pages that are marked as newly initialized in the page descriptor. They will be covered by the redo log, similar to MDEV-19738.
   • If access_time==0 && oldest_modification() <= 1, the page must have been only accessed due to backup, and we might want to evict it from the buffer pool, to reduce cache pollution.
   • Switch to the next tablespace if the tablespace was flagged for deletion.

Observations:

• If files were deleted during the backup, the stream may contain some ‘garbage’ pages belonging to deleted files, as well as FILE_DELETE records in the copied log.
• If files were renamed during the backup, the stream would contain the original file names, as well as FILE_RENAME records in the copied log.
• If files were created during the backup, all changes will be covered by the copied log; see MDEV-24626.
• If files were extended during the backup, in the copied log there will be records to update the FSP_SIZE and to initialize any added pages.
• If any pages were modified after they were copied, in the copied log there will be records to cover those updates.

For incremental backups, my best idea remains that the log be archived on the server along with a log of all checkpoints. We would simply stream the log from the latest checkpoint that is at or before the requested LSN. We would probably have to keep a directory of checkpoint LSNs in the archived log, because the log header only stores the 2 latest ones. A simple way could be to switch to a new archived log file on every checkpoint. The checkpoint LSN of the archived log could be available in the file name.

All functionality of mariadb-backup --prepare would have to be integrated in the normal InnoDB startup.

Bugs like MDEV-27424 would be impossible, or they would affect the server and backup alike, if all pages for the backup were read from the server’s buffer pool. There would be no race condition between two processes reading and writing files concurrently, and no need to re-read corrupted-looking pages because of that.

MDEV-27621 is a case where backup cannot keep up with the server that is writing log. If the server process itself streamed the log like proposed in this task, it could trivially slow down the write workload as necessary.

marko not sure it is a good idea for a strained backup to slow down the server to accommodate its needs. We want backup to be as less impactful as we can and this idea goes in the opposite direction.

The DS_TYPE_LOCAL interface of mariadb-backup could be demoted to a fallback of something that would invoke the Linux system call copy_file_range. That function call would not only allow files to be copied with less context switching, but also allow instantaneous ‘copying’ on file systems that support snapshots via copy-on-write (such as xfs and btrfs).

Similarly, the DS_TYPE_STDOUT interface could be demoted to a fallback of the Linux system call splice or the nonstandard but widely implemented system call sendfile.

rpizzi, the "redo log archiving" feature of MySQL 8.0 implements the idea of server-assisted copying of the log in a restricted form: writing a copy of the log to a file system that is directly accessible at the server.

What would you choose if the choices are the following?

1. Some slowdown of the server during backup (because the server would be responsible for replicating the log)
2. Extreme I/O load during backup (MDEV-28772), or context switching load on the kernel (inter-process communication via the file system)
3. A failed backup (MDEV-27621)

If there are enough resources I would go with #2 .
Obviously the issue is when there are not enough resources for both the workload AND the backup.
In this situation we want the workload to take precedence but we also want the backup to complete, albeit
taking longer if there is resource starvation.

marko reading the blog article I am somewhat puzzled - an heavily used system will already write redo logs at a very high pace, do we (does LeFred) really think that it's a good idea to duplicate that heavy I/O in these circumstances??

To avoid redo logs overrun it should be sufficient to make them large enough.

Marko, both 1 and 2 seem like good ideas, perhaps configurable with a variable such as innodb_log_file_backup_method.

+1 for Manjot request. Among other motivations, xfs snapshots can be problematic, as they freeze IO.

I believe that a reasonable subgoal of this task would be to remove the need to execute mariadb-backup --prepare, that is, try to make it so that the server can be started directly on backed-up files. With the InnoDB log file format change of MDEV-14425, that should be even easier:

1. Incremental backups can be restored by treating the initial ib_logfile0 from the full backup and the incremental snippets as if they had been concatenated together.
   • In the MDEV-14425 format, the size of an InnoDB log block is mini-transaction; there is no padding to any 512-byte log blocks anymore.
   • As an option, incremental backup could simply append new log records to the ib_logfile0 that was updated by the last incremental or full backup.
2. Some adjustment due to MDEV-18184 will be needed.
   • Maybe, conduct some performance tests to verify if the ‘optimization’ is useful at all? A server-side backup can avoid copying newly initialized data files altogether!
   • The server could look for a backup metadata file and validate its contents, like mariadb-backup --prepare currently does.

I forgot that there are two usage scenarios of incremental backup:

• With no gaps in the redo log: If writes are seldom, or we implemented and enabled log archiving on the server, we can simply copy and apply the additional section of the log.
• If the log from the last backup LSN is missing, we must copy all data files that were changed since the last backup (based on the FIL_PAGE_LSN of each page).

In the latter case, the additional preparation might be too complex to be implemented as part of the normal crash recovery.

There is a Linux system call ioctl(FICLONE) (applicable to XFS, btrfs, bcachefs) as well as macOS fclonefileat() (APFS) and something for the Microsoft Windows ReFS that would allow more efficient copying of files, similar to GNU cp --reflink=auto. I think that we should consider an option to employ that for copying most files. MyRocks backups are probably best served by regular hard links.

Edit: MDEV-23947 has been filed for this idea.

I think that we need to implement some prototype of this, or MDEV-21105 (or MDEV-7502).