Details

    • New Feature
    • Status: Open (View Workflow)
    • Critical
    • Resolution: Unresolved
    • 12.0
    • Backup
    • None

    Description

      The purpose of this work is to improve current situation around backups by implementing a SQL BACKUP command.

      Interface between client and server

      BACKUP command sends the file segments to the client via MySQL's protocol ResultSet, i.e client issues BACKUP statement, server sends a result set back

      The ResultSet consists of following fields

      • VARCHAR(N) filename
      • INT(SET?) flags (OPEN,CLOSE, SPARSE, RENAME, DELETE, etc)
      • BIGINT offset
      • LONGBLOB data

      The client

      There will be a simple client program which will issue "BACKUP" statement ,read the result set from server, and apply it to create a directory with a copy of the database. Due to abiliity to use client API (in C, .NET, Java, etc), it should be relatively easy to write specialized backup applications for special needs (e.g "streaming to clould", distributing to multiple machines)

      Interface between server and storage engines.

      Most of the actual work is done by the storage engines, server initiates backup by calling storage engine's backup()_begin function. For every file chunk, storage engine calls server's backup_file(filename, flag, offset, length, data) function, that streams file chink as result set row to client. When backup is finished, engine calls `backup_end()` so that server would know when backup is finally done.

      There default implementation of backup_start() enumerates engines own files, reads them, and signals backup_file() for every chunk read. FLUSH TABLES FOR BACKUP, must be issued for that.

      Engines that support online backup, like Innodb would have much more complicated implementation.

      Incremental and partial backups

      Incremental/differential (changes since specific backup) and partial backups( only some tables) will be supported as well, and the client-server interface does not change much for that . For incremental backup, the client application might need to invent some "patch" format, or use some standard for binary diffs (rdiff? bsdiff? xdelta? ), and save diffs to be applied to "base backup" directory later.

      Backup metadata.

      There is a need to store different kind of metadata in the backup, usually binlog position, and some kind of timestamp (LSN) for incremental backup. This information can be stored as session variable available after BACKUP command, or send as extra result set after the backup one (protocol does support multi-result sets).

      Restore

      Unlike the current solution with mariabackup or xtrabackup, there won't be any kinds of "prepare" or "restore" application,

      The directory that was made when full backup was stored should be usable for new database.

      files from partial backups could just be copied to destination (and maybe "IMPORT TABLESPACE", if we still support that).
      There should still be some kind of "patch" application for applying incremental backups to the base directory. Dependend on how our client stores incremental backup, we can do without it , and users would only need to use 3rd party bsdiff, rdiff,, mspatcha.

      Attachments

        Issue Links

          Activity

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

            f_razzoli Federico Razzoli added a comment - +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.
            marko Marko Mäkelä added a comment - 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: 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. 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.

            marko Marko Mäkelä added a comment - 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.
            marko Marko Mäkelä added a comment - - edited

            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.

            marko Marko Mäkelä added a comment - - edited 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).

            marko Marko Mäkelä added a comment - I think that we need to implement some prototype of this, or MDEV-21105 (or MDEV-7502 ).

            People

              debarun Debarun Banerjee
              wlad Vladislav Vaintroub
              Votes:
              16 Vote for this issue
              Watchers:
              37 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.