You are here

Home » Blogs » Shinguz's blog

Partial physical database restore for MariaDB and MySQL

Tue, 2024-07-02 18:13 — Shinguz

What is it about?

When describing backup and restore scenarios, a full backup and a full restore of the database instance (mariadbd/mysqld) are usually assumed. This means that the entire database instance, including all databases (schemas), is backed up and restored.

In practice, however, the situation is often different: An entire database instance is not to be restored, but only individual databases or even individual tables, because only these have broken.

In many cases, this can be done quite easily with the tools mariadb-dump/mariadb or mysqldump/mysql (logical backup). However, if the database or table is very large, the restore will not be completed in a reasonable time (some minutes to a few hours).

This is exactly where the so-called partial physical restore comes into play. Partial stands for one or more tables (or an entire database), physical for: Individual SQL statements are not executed, but the data files are physically restored. In this scenario, very large amounts of data can be restored very quickly, provided the appropriate infrastructure is in place. Rule of thumb: On fat hardware: 1 Tbyte per hour. In this way, database restores can be carried out very quickly.

MariaDB and MySQL already offer this functionality out of the box. The mechanism is reasonably practicable for individual tables (see Restore partial backup). For entire databases with possibly dozens or hundreds of tables, however, the on-board mechanism is very cumbersome and error-prone.

Use case

This is exactly where the new functionality of the FromDual Backup and Recovery Manager (brman) v2.3.0 comes into play: it considerably simplifies the partial physical database restore.

A second scenario in which this new functionality can also be used is when moving a large database from one database instance to another (e.g. from Dev to Prod).

Preparations for the partial physical database restore

In order to be able to restore a database, a clean backup must of course first be available. This can either be created with the FromDual Backup Manager (bman):

PORT=3306
BACKUPNAME=bck_full_2024-07-01
BACKUPDIR=/tmp/bck

./brman/bin/bman --target=brman:secret@127.0.0.1:${PORT} --type=full --mode=physical --policy=daily --backupdir=${BACKUPDIR} --backup-name=${BACKUPNAME} --no-compress

or you can simply create the backup with the MariaDB (mariadb-backup) or MySQL on-board tools (xtrabackup):

PORT=3306
BACKUPNAME=bck_full_2024-07-01
BACKUPDIR=/tmp/bck
POLICY=daily

mariadb-backup --user=brman --password=secret --host=127.0.0.1 --port=${PORT} --backup --target-dir=${BACKUPDIR}/${POLICY}/${BACKUPNAME}
mariadb-backup --user=brman --password=secret --host=127.0.0.1 --port=${PORT} --prepare --target-dir=${BACKUPDIR}/${POLICY}/${BACKUPNAME}

Partial physical database restore

To perform a partial physical database restore, the database must be running, in contrast to a complete physical restore.

The partial physical database restore is then simple:

PORT=3306
DATADIR=/var/lib/mysql
BACKUPNAME=bck_full_2024-07-01
BACKUPDIR=/tmp/bck

./brman/bin/rman --target=brman:secret@127.0.0.1:${PORT} --type=schema --mode=physical --policy=daily --schema=test --log=/tmp/rman.log --backupdir=${BACKUPDIR} --datadir=${DATADIR} --backup-name=${BACKUPNAME}

...
Start restore at 2024-07-01 16:29:48
  Backup with tool mariabackup version 10.11.8 (from path /home/mysql/product/mariadb-10.11/bin/mariabackup).
  Parent: We are the parent. Our child is: 63712. Waiting for database daemon...
  Child: We are the child: Starting database daemon...
  Child: Change ownership of database files (/tmp/bck/daily/bck_full_2024-07-01) to mysql
  Child: /home/mysql/product/mariadb-10.11/bin/mariadbd --no-defaults --user=mysql --basedir=/home/mysql/product/mariadb-10.11 --datadir=/tmp/bck/daily/bck_full_2024-07-01 --log-error=/tmp/my.err --port=3360 --socket=/tmp/my.sock --lower-case-table-names=0
  Parent: Tables not InnoDB or sequences: 0
  Parent: Tables with partitions: 0
  Parent: Tables with full-text index: 0
  Parent: InnoDB table `test` found to restore
  Parent: Dump database test
  Parent: /home/mysql/product/mariadb-10.11/bin/mariadb-dump --user=brman --host=127.0.0.1 --port=3360 --routines --events --triggers --no-data --skip-lock-tables --add-drop-database --databases test
  Parent: Shutdown backup database.
  Restore empty database test
  Prepare and export tables: /home/mysql/product/mariadb-10.11/bin/mariabackup --user=brman --host=127.0.0.1 --port=3321 --prepare --export --databases=test --target-dir=/tmp/bck/daily/bck_full_2024-07-01
  SET SESSION foreign_key_checks = 0
  SET SESSION sql_log_bin = off

  Restore table test
  ALTER TABLE `test`.`test` DISCARD TABLESPACE
  cp /tmp/bck/daily/bck_full_2024-07-01/test/test.cfg /home/mysql/database/mariadb-1011/data/test/test.cfg
  cp /tmp/bck/daily/bck_full_2024-07-01/test/test.ibd /home/mysql/database/mariadb-1011/data/test/test.ibd
  chown mysql: /home/mysql/database/mariadb-1011/data/test/test.cfg /home/mysql/database/mariadb-1011/data/test/test.ibd
  ALTER TABLE `test`.`test` IMPORT TABLESPACE
  rm /home/mysql/database/mariadb-1011/data/test/test.cfg
  rm /tmp/bck/daily/bck_full_2024-07-01/test/test.cfg
  ----------------------------------------
  WARNING: You should restart the database now! Otherwise possible future backups may fail. See MDEV-34418 (https://jira.mariadb.org/browse/MDEV-34418).
  ----------------------------------------

  Restore time was: 0d 0h 0' 2"
End restore at 2024-07-01 16:29:50 (rc=0)

For MariaDB, it is recommended to restart the database afterwards until the bug MDEV-34418: mariadb-backup fails on database which was partially restored with mariadb-backup is fixed. This step can be omitted for MySQL.

Restrictions

At present, the following restrictions still apply to the partial physical restore of databases with rman:

  • Only entire databases can be restored. Restoring individual tables is not yet implemented. Use the basic on-board tools for this.
  • Restoring partitioned tables is not yet implemented. Use the basic on-board tools for partitioned tables.
  • A subsequent point-in-time recovery of the database is not yet implemented and must be carried out manually.
  • A partial physical database restore for an entire Galera cluster is not yet implemented and must be performed manually. In this case, a restore to one Galera node and a subsequent synchronisation of the other nodes using SST is recommended.
  • With a physical partial database restore, a pseudo instance is started on the backup files. This pseudo instance requires a free port 3360.
  • The backup files must already be in a consistent state (--prepare).
  • With a partial physical database restore, a logical backup of the database is created without the data on the pseudo instance. This backup is restored to the instance to be repaired. This means that all objects (views, triggers, functions, procedures, events, etc.) that were created AFTER the complete physical backup are deleted before the partial physical database restore and are then no longer available.
  • The original database instance from which the backup was created and the instance on which the restore is performed must have the same setting for lower_case_table_names.
  • All three, the backup, the database instance and the rman tool must be located on the same machine.
  • The backup must currently still be available in uncompressed form.

Literature


Taxonomy upgrade extras: 
partial restore
Restore
database
schema