mariabackup / selective table restore

Written by
Walter Doekes
Published on 2021-09-12

When using mariabackup (xtrabackup/innobackupex) for your MySQL/MariaDB backups, you get a snapshot of the mysql lib dir. This is faster than doing an old-style mysqldump, but it is slightly more complicated to restore. Especially if you just want access to data from a single table.

Assume you have a big database, and you're backing it up like this, using the mariadb-backup package:

# ulimit -n 16384

# mariabackup \
    --defaults-file=/etc/mysql/debian.cnf \
    --backup \
    --compress --compress-threads=2 \
    --target-dir=/var/backups/mysql \
    [--parallel=8] [--galera-info]

...
[00] 2021-09-12 15:23:52 mariabackup: Generating a list of tablespaces
[00] 2021-09-12 15:23:53 >> log scanned up to (132823770290)
[01] 2021-09-12 15:23:53 Compressing ibdata1 to /var/backups/mysql/ibdata1.qp
...
[00] 2021-09-12 15:25:40 Compressing backup-my.cnf
[00] 2021-09-12 15:25:40         ...done
[00] 2021-09-12 15:25:40 Compressing xtrabackup_info
[00] 2021-09-12 15:25:40         ...done
[00] 2021-09-12 15:25:40 Redo log (from LSN 132823770281 to 132823770290) was copied.
[00] 2021-09-12 15:25:40 completed OK!

Optionally followed by a:

# find /var/backups/mysql \
    -type f '!' -name '*.gpg' -print0 |
  sort -z |
  xargs -0 sh -exc 'for src in "$@"; do
      dst=${src}.gpg &&
        gpg --batch --yes --encrypt \
          --compression-algo none \
          --trust-model always \
          --recipient EXAMPLE_RECIPIENT \
          --output "$dst" "$src" &&
        touch -r "$src" "$dst" &&
        rm "$src" || exit $?
  done' unused_argv0

You'll end up with a bunch of qpress-compressed gpg-encrypted files, like these:

# ls -F1 /var/backups/mysql/
aria_log.00000001.qp.gpg
aria_log_control.qp.gpg
backup-my.cnf.qp.gpg
ib_buffer_pool.qp.gpg
ibdata1.qp.gpg
ib_logfile0.qp.gpg
my_project_1/
my_project_2/
my_project_3/
mysql/
performance_schema/
xtrabackup_binlog_info.qp.gpg
xtrabackup_checkpoints.qp.gpg
xtrabackup_info.qp.gpg

Let's assume we want only my_project_3.important_table restored.

Start out by figuring out where the decryption key was at:

$ gpg --list-packets /var/backups/mysql/my_project_3/important_table.ibd.qp.gpg
gpg: encrypted with 4096-bit RSA key, ID 1122334455667788, created 2017-10-10
      "Example Recipient" <recipient@example.com>"
gpg: decryption failed: No secret key
# off=0 ctb=85 tag=1 hlen=3 plen=524
:pubkey enc packet: version 3, algo 1, keyid 1122334455667788
  data: [4096 bits]
# off=527 ctb=d2 tag=18 hlen=3 plen=3643 new-ctb
:encrypted data packet:
  length: 3643
  mdc_method: 2

This PGP keyid we see, corresponds to the fingerprint of an encryption subkey:

$ gpg --list-keys --with-subkey-fingerprints recipient@example.com
pub   rsa4096 2017-10-10 [SC] [expires: 2021-10-13]
      ...some..key...
uid           [ unknown] Example Recipient <recipient@example.com>
sub   rsa4096 2017-10-10 [E] [expires: 2021-10-13]
      0000000000000000000000001122334455667788      <-- here it is!
sub   rsa4096 2017-10-10 [A] [expires: 2021-10-13]
      ...some..other..key...
sub   rsa4096 2017-10-10 [S] [expires: 2021-10-13]
      ...yet..another..key..

That matches. Good.

After assuring you have the right credentials, it's time to select which files we actually need. They are:

backup-my.cnf.qp.gpg
ibdata1.qp.gpg
ib_logfile0.qp.gpg
my_project_3/important_table.frm.qp.gpg
my_project_3/important_table.ibd.qp.gpg
xtrabackup_binlog_info.qp.gpg
xtrabackup_checkpoints.qp.gpg
xtrabackup_info.qp.gpg

Collect the files, decrypt and decompress.

Decrypting can be done with gpg, decompressing can either be done using qpress -dov $SRC >${SRC%.qp} or mariabackup --decompress --target-dir=.

(Yes, for --decompress and --prepare the --target-dir= setting means the backup-location, i.e. where the backups are now. Slightly confusing indeed.)

$ find . -name '*.gpg' -print0 |
  xargs -0 sh -xec 'for src in "$@"; do
      gpg --decrypt --output "${src%.gpg}" "$src" &&
        rm "$src" || exit $?
  done' unused_argv0

$ find . -name '*.qp' -print0 |
  xargs -0 sh -xec 'for src in "$@"; do
      qpress -dov "$src" >"${src%.qp}" &&
        rm "$src" || exit $?
  done' unused_argv0

Ok, we have files. Time to whip out the correct mariabackup, for example from a versioned Docker image.

$ docker run -it \
  -v `pwd`:/var/lib/mysql:rw mariadb:10.3.23 \
  bash

Inside the docker image, we'll fetch screen, which we'll be needing shortly:

# apt-get update -qq &&
    apt-get install -qqy screen less

Fix ownership, and "prepare" the mysql files:

# cd /var/lib/mysql

# chown -R mysql:mysql .

# su -s /bin/bash mysql

$ mariabackup --prepare --use-memory=20G --target-dir=.

(You may want to tweak that --use-memory=20G to your needs. For a 10GiB ib_logfile0, this setting made a world of difference: 10 minutes restore time, instead of infinite.)

(Also, mariabackup has a --databases="DB[.TABLE1][ DB.TABLE2 ...]" option that might come in handy if you're working with all files during the --prepare phase.)

mariabackup based on MariaDB server 10.3.23-MariaDB debian-linux-gnu (x86_64)
[00] 2021-09-12 16:04:30 cd to /var/lib/mysql/
...
2021-09-12 16:04:30 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=132823770281
2021-09-12 16:04:30 0 [Note] InnoDB: Last binlog file 'mysql-bin.000008', position 901
[00] 2021-09-12 16:04:30 Last binlog file mysql-bin.000008, position 901
[00] 2021-09-12 16:04:31 completed OK!

At this point we don't need to copy/move them to /var/lib/mysql. We're there already.

All set, fire up a screen (or tmux, or whatever) and start mysqld, explicitly ignoring mysql permissions.

$ screen

$ mysqld --skip-grant-tables 2>&1 |
    tee /tmp/mysql-boot-error.log |
    grep -vE '\[ERROR\]|Ignoring tablespace'

2021-09-12 16:05:56 0 [Note] mysqld (mysqld 10.3.23-MariaDB-1:10.3.23+maria~bionic-log) starting as process 526 ...
...
2021-09-12 16:05:56 0 [Note] InnoDB: Setting log file ./ib_logfile101 size to 50331648 bytes
2021-09-12 16:05:56 0 [Note] InnoDB: Setting log file ./ib_logfile1 size to 50331648 bytes
2021-09-12 16:05:57 0 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2021-09-12 16:05:57 0 [Note] InnoDB: New log files created, LSN=132823770290
...

At this point the screen would get flooded with the following error messages, if it weren't for the grep -v:

2021-09-12 16:05:57 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2021-09-12 16:05:57 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2021-09-12 16:05:57 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-09-12 16:05:57 0 [ERROR] InnoDB: Cannot open datafile for read-only: './my_project_1/aboutconfig_item.ibd' OS error: 71
2021-09-12 16:05:57 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2021-09-12 16:05:57 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2021-09-12 16:05:57 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-09-12 16:05:57 0 [ERROR] InnoDB: Could not find a valid tablespace file for ``my_project_1`.`aboutconfig_item``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2021-09-12 16:05:57 0 [Warning] InnoDB: Ignoring tablespace for `my_project_1`.`aboutconfig_item` because it could not be opened.

You'll get those for every table that you didn't include. Let's just ignore them.

Finally, when mysqld is done plowing through the (possibly big) ibdata1, it should read something like:

...
2021-09-12 16:05:57 6 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1017: Can't find file: './mysql/' (errno: 2 "No such file or directory")
2021-09-12 16:05:57 0 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
2021-09-12 16:05:57 0 [Note] Server socket created on IP: '127.0.0.1'.
2021-09-12 16:05:57 0 [Warning] Can't open and lock time zone table: Table 'mysql.time_zone_leap_second' doesn't exist trying to live without them
2021-09-12 16:05:57 7 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1017: Can't find file: './mysql/' (errno: 2 "No such file or directory")
2021-09-12 16:05:57 0 [Note] Reading of all Master_info entries succeeded
2021-09-12 16:05:57 0 [Note] Added new Master_info '' to hash table
2021-09-12 16:05:57 0 [Note] mysqld: ready for connections.
Version: '10.3.23-MariaDB-1:10.3.23+maria~bionic-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  mariadb.org binary distribution

At this point, you can fire up a mysql or mysqldump client and extract the needed data.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| my_project_3       |
+--------------------+

MariaDB [(none)]> select count(*) from my_project_3.important_table;
+----------+
| count(*) |
+----------+
|        6 |
+----------+

Good, we have the data. And we didn't need to decrypt/decompress everything.

Stopping the mysqld is a matter of: mysqladmin shutdown

mariabackup / selective table restore

mariabackup / selective table restore

Location

Links

OSSO B.V.