XTRABACKUP(1)

📦
version 9.7.1_rc1 · 4 versions
, in arch rolling, ubuntu stonking

👑 XTRABACKUP — Percona XtraBackup 8.0, bestie edition 💖

okay so. hi. welcome to XTRABACKUP(1), aka the Percona XtraBackup 8.0 manual page, and honestly? this one's a banger. sit down, grab a strawberry, and let's get into it 🍓

The xtrabackup binary is a compiled C program that is linked with the InnoDB libraries and the standard MySQL client libraries. yes, a whole C program. she's compiled, she's linked, she's iconic 💅

xtrabackup enables point-in-time backups of InnoDB / XtraDB tables together with the schema definitions, MyISAM tables, and other portions of the server. like, the whole vibe of your server, backed up. no cap.

The InnoDB libraries provide the functionality to apply a log to data files. The MySQL client libraries are used to parse command-line options and configuration file. so the InnoDB girlies handle the log-applying, and the MySQL client girlies handle the option/config-file parsing. teamwork, fr fr 🤝

The tool runs in either --backup or --prepare mode, corresponding to the two main functions it performs. There are several variations on these functions to accomplish different tasks, and there are two less commonly used modes, --stats and --print-param. two main-character modes, plus two side-character modes. periodt.

🌈 OTHER TYPES OF BACKUPS 🌈

🦋 Incremental Backups 🦋

xtrabackup supports incremental backups. It copies only the data that has changed since the last full backup. the way that it does NOT re-copy stuff that hasn't moved? yeah. obsessed. 💖

You can perform many incremental backups between each full backup, so you can set up a backup process such as a full backup once a week and an incremental backup every day, or full backups every day and incremental backups every hour. literally pick your schedule, bestie. weekly full + daily incremental? valid. daily full + hourly incremental? also valid. you do you ✨

NOTE: Incremental backups on the MyRocks storage engine do not determine if an earlier full backup or incremental backup contains the same files. Percona XtraBackup copies all of the MyRocks files each time it takes a backup. 🫠 so MyRocks is out here copying everything, every single time. she does not do the compare. she does not do the dedupe. she just copies.

Incremental backups work because each InnoDB page (usually 16kb in size) contains a log sequence number, or LSN. The LSN is the system version number for the entire database. Each page's LSN shows how recently it was changed. so every 16kb page is basically carrying around her own little timestamp. timestamping? she's timestamping 📸

An incremental backup copies each page whose LSN is newer than the previous incremental or full backup's LSN. There are two algorithms in use to find the set of such pages to be copied.

  1. The first one, available with all the server types and versions, is to check the page LSN directly by reading all the data pages.
  2. The second one, available with Percona Server for MySQL, is to enable the changed page tracking feature on the server, which will note the pages as they are being changed. This information will be then written out in a compact separate so-called bitmap file. The xtrabackup binary will use that file to read only the data pages it needs for the incremental backup, potentially saving many read requests.

The latter algorithm is enabled by default if the xtrabackup binary finds the bitmap file. It is possible to specify --incremental-force-scan to read all the pages even if the bitmap data is available. so if you wanna force the full scan even though the bitmap is right there? you can. your call 💅

Incremental backups do not actually compare the data files to the previous backup's data files. In fact, you can use --incremental-lsn to perform an incremental backup without even having the previous backup, if you know its LSN. Incremental backups simply read the pages and compare their LSN to the last backup's LSN. You still need a full backup to recover the incremental changes, however; without a full backup to act as a base, the incremental backups are useless. 🚨 READ THAT AGAIN. no full backup as a base = your incrementals are useless. tragic. devastating. avoidable.

🎀 Creating an Incremental Backup 🎀

To make an incremental backup, begin with a full backup as usual.

The xtrabackup binary writes a file called xtrabackup_checkpoints into the backup's target directory. This file contains a line showing the to_lsn, which is the database's LSN at the end of the backup. Create the full backup with a command such as the following:

$ xtrabackup --backup --target-dir=/data/backups/base --datadir=/var/lib/mysql/

If you look at the xtrabackup_checkpoints file, you should see contents similar to the following:

backup_type = full-backuped
from_lsn = 0
to_lsn = 1291135

Now that you have a full backup, you can make an incremental backup based on it. Use a command such as the following:

$ xtrabackup --backup --target-dir=/data/backups/inc1 \
--incremental-basedir=/data/backups/base --datadir=/var/lib/mysql/

The /data/backups/inc1/ directory should now contain delta files, such as ibdata1.delta and test/table1.ibd.delta. These represent the changes since the LSN 1291135. If you examine the xtrabackup_checkpoints file in this directory, you should see something similar to the following:

backup_type = incremental
from_lsn = 1291135
to_lsn = 1291340

The meaning should be self-evident. It's now possible to use this directory as the base for yet another incremental backup:

$ xtrabackup --backup --target-dir=/data/backups/inc2 \
--incremental-basedir=/data/backups/inc1 --datadir=/var/lib/mysql/

incrementals on top of incrementals on top of incrementals. it's giving layers. it's giving lasagna 🍰

💜 Preparing the Incremental Backups 💜

The --prepare step for incremental backups is not the same as for normal backups. ok this is the part where you actually have to pay attention, bestie. 🙈

In normal backups, two types of operations are performed to make the database consistent:

  • committed transactions are replayed from the log file against the data files
  • uncommitted transactions are rolled back

You must skip the rollback of uncommitted transactions when preparing a backup, because transactions that were uncommitted at the time of your backup may be in progress, and it is likely that they will be committed in the next incremental backup. You should use the --apply-log-only option to prevent the rollback phase.

NOTE: If you do not use the --apply-log-only option to prevent the rollback phase, then your incremental backups will be useless. After transactions have been rolled back, further incremental backups cannot be applied. 💀 so like. use the flag. please. i'm begging.

Beginning with the full backup you created, you can prepare it, and then apply the incremental differences to it. Recall that you have the following backups:

/data/backups/base
/data/backups/inc1
/data/backups/inc2

To prepare the base backup, you need to run --prepare as usual, but prevent the rollback phase:

xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base

The output should end with some text such as the following:

101107 20:49:43  InnoDB: Shutdown completed; log sequence number 1291135

The log sequence number should match the to_lsn of the base backup, which you saw previously. matchy matchy ✅

This backup is actually safe to restore as-is now, even though the rollback phase has been skipped. If you restore it and start MySQL, InnoDB will detect that the rollback phase was not performed, and it will do that in the background, as it usually does for a crash recovery upon start. It will notify you that the database was not shut down normally. she'll handle it herself in the background. self-sufficient queen 👑

To apply the first incremental backup to the full backup, you should use the following command:

xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base \
--incremental-dir=/data/backups/inc1

This applies the delta files to the files in /data/backups/base, which rolls them forward in time to the time of the incremental backup. It then applies the redo log as usual to the result. The final data is in /data/backups/base, not in the incremental directory. say it with me: the final data lives in the base, NOT in the incremental dir. You should see some output such as the following:

incremental backup from 1291135 is enabled.
xtrabackup: cd to /data/backups/base/
xtrabackup: This target seems to be already prepared.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(1291340)
Applying /data/backups/inc1/ibdata1.delta ...
Applying /data/backups/inc1/test/table1.ibd.delta ...
.... snip
101107 20:56:30  InnoDB: Shutdown completed; log sequence number 1291340

Again, the LSN should match what you saw from your earlier inspection of the first incremental backup. If you restore the files from /data/backups/base, you should see the state of the database as of the first incremental backup.

Preparing the second incremental backup is a similar process: apply the deltas to the (modified) base backup, and you will roll its data forward in time to the point of the second incremental backup:

xtrabackup --prepare --target-dir=/data/backups/base \
--incremental-dir=/data/backups/inc2
NOTE: --apply-log-only should be used when merging all incrementals except the last one. That's why the previous line doesn't contain the --apply-log-only option. Even if the --apply-log-only was used on the last step, backup would still be consistent but in that case server would perform the rollback phase. so: every merge except the final one gets the flag. the last one goes flagless. got it? 🫶

If you wish to avoid the notice that InnoDB was not shut down normally, when you applied the desired deltas to the base backup, you can run --prepare again without disabling the rollback phase.

🌸 Restoring Incremental Backups 🌸

After preparing the incremental backups, the base directory contains the same data as the full backup. To restoring this backup, you can use this command: xtrabackup --copy-back --target-dir=BASE-DIR

You may have to change the ownership as detailed on restoring_a_backup. don't forget the chown, bestie, she's sensitive about her permissions 💅

✨ Incremental Streaming Backups Using xbstream ✨

Incremental streaming backups can be performed with the xbstream streaming option. Currently backups are packed in custom xbstream format. With this feature, you need to take a BASE backup as well.

Making a base backup

$ xtrabackup --backup --target-dir=/data/backups

Taking a local backup

$ xtrabackup --backup --incremental-lsn=LSN-number --stream=xbstream --target-dir=./ > incremental.xbstream

Unpacking the backup

$ xbstream -x < incremental.xbstream

Taking a local backup and streaming it to the remote server and unpacking it

$ xtrabackup --backup --incremental-lsn=LSN-number --stream=xbstream --target-dir=./
$ ssh user@hostname " cat - | xbstream -x -C > /backup-dir/"

streaming incremental backups straight over ssh? it's giving main character energy 🌈 no local disk drama, just vibes and a pipe.

🐛 Partial Backups 🐛

xtrabackup supports taking partial backups when the innodb_file_per_table option is enabled. There are three ways to create partial backups:

  1. matching the tables names with a regular expression
  2. providing a list of table names in a file
  3. providing a list of databases
WARNING: Do not copy back the prepared backup. Restoring partial backups should be done by importing the tables, not by using the --copy-back option. It is not recommended to run incremental backups after running a partial backup. Although there are some scenarios where restoring can be done by copying back the files, this may lead to database inconsistencies in many cases and it is not a recommended way to do it.

🚨 NOT the --copy-back route for partial backups, bestie!! import the tables instead. and after a partial backup? maybe don't run incrementals. just don't. 🙈

For the purposes of this manual page, we will assume that there is a database named test which contains tables named t1 and t2. setting the scene. establishing the lore. 📖

WARNING: If any of the matched or listed tables is deleted during the backup, xtrabackup will fail. so if a table ghosts you mid-backup? everything falls apart. 💀

🧚 Creating Partial Backups 🧚

There are multiple ways of specifying which part of the whole data is backed up:

  • Use the --tables option to list the table names
  • Use the --tables-file option to list the tables in a file

and that's where we're leaving it for now, because there's so much more coming up next 💖

  • Use the --databases option to list the databases
  • Use the --databases-file option to list the databases

The --tables Option 🎀

ok bestie, method numero uno is the xtrabackup --tables option. what you hand it is a regular expression, and it gets matched against the fully-qualified database name plus table name in the good old databasename.tablename format. yes it's regex, yes it's picky, love that for it.

So if you only want the tables living in the test database, you say:

$ xtrabackup --backup --datadir=/var/lib/mysql --target-dir=/data/backups/ \
--tables="^test[.].*"

And if you want just the test.t1 table and literally nothing else, you say:

$ xtrabackup --backup --datadir=/var/lib/mysql --target-dir=/data/backups/ \
--tables="^test[.]t1"

The --tables-file Option 💅

Different vibe, same goal. --tables-file takes a file — and that file can hold a whole list of table names, one per line, no separator nonsense. Only the tables named in that file get backed up, periodt.

Heads up tho: the names are matched exactly, they're case-sensitive, and there is no pattern or regular expression matching happening at all. So the names have to be fully-qualified, in databasename.tablename format, spelled exactly right. fr fr.

$ echo "mydatabase.mytable" > /tmp/tables.txt
$ xtrabackup --backup --tables-file=/tmp/tables.txt

The --databases and --databases-file options 🦋

So the --databases option takes a space-separated list of the databases and tables you want to back up, written in the databasename[.tablename] format. and like, on top of that list, make sure to specify the mysql, sys, and performance_schema databases. Those three are required when you restore the databases using xtrabackup --copy-back, so don't skip them or you'll be sad later, no cap.

NOTE: tables processed during the --prepare step may also be added to the backup even if they are not explicitly listed by the parameter — if they were created after the backup started. sneaky little tables 🙈
$ xtrabackup --databases='mysql sys performance_schema test ...'

The --databases-file Option 🌸

The --databases-file option names a file that can contain multiple databases and tables in the databasename[.tablename] format, one element name per line in the file. And same energy as the tables one: names are matched exactly, case-sensitive, with no pattern or regular expression matching. it's giving "type it right or type it twice."

NOTE: tables processed during the --prepare step may also be added to the backup even if they are not explicitly listed by the parameter — if they were created after the backup started. (yes, same note. yes, it's still true. 💀)

Preparing Partial Backups 🫶

The procedure is analogous to restoring individual tables: apply the logs and use the --export option. that's the whole recipe:

$ xtrabackup --prepare --export --target-dir=/path/to/partial/backup

Now, when you run --prepare on a partial backup, you're gonna see warnings about tables that don't exist. Don't panic — this is normal. Those tables exist in the data dictionary inside InnoDB, but the matching .ibd files don't exist, because they were never copied into the backup directory. So those tables get removed from the data dictionary, and when you restore the backup and start InnoDB, they simply won't exist anymore and won't throw any errors or warnings into the log file. iconic cleanup, honestly.

Could not find any file associated with the tablespace ID: 5

Use --innodb-directories to find the tablespace files. If that fails
then use --innodb-force-recovery=1 to ignore this and to permanently
lose all changes to the missing tablespace(s).

Restoring Partial Backups 🌈

Restoring should be done by restoring individual tables in the partial backup to the server. that's the main-character move.

It can also be done by copying back the prepared backup to a "clean" datadir (in that case, make sure to include the mysql database) to the datadir you are moving the backup to. And you can create a system database with the following:

$ sudo mysql --initialize --user=mysql

Once you start the server, you may see mysql complaining about missing tablespaces:

2021-07-19T12:42:11.077200Z 1 [Warning] [MY-012351] [InnoDB] Tablespace 4, name 'test1/t1', file './d2/test1.ibd' is missing!
2021-07-19T12:42:11.077300Z 1 [Warning] [MY-012351] [InnoDB] Tablespace 4, name 'test1/t1', file './d2/test1.ibd' is missing!

In order to clean the orphan database from the data dictionary, you must manually create the missing database directory and then DROP this database from the server. both steps, bestie, not just one.

Example of creating the missing database:

$ mkdir /var/lib/mysql/test1/d2

Example of dropping the database from the server:

mysql> DROP DATABASE d2;
Query OK, 2 rows affected (0.5 sec)

ADVANCED FEATURES 👑

Analyzing Table Statistics ⭐

The xtrabackup binary can analyze InnoDB data files in read-only mode to give you statistics about them. To do this you use the --stats option. You can combine it with the --tables option to limit which files get examined. It also uses the --use-memory option.

You can run the analysis on a running server, with some chance of errors because the data is being changed while it's being analyzed. Or you can analyze a backup copy of the database. Either way, to use the statistics feature you need a clean copy of the database including correctly sized log files, so you need to execute with --prepare twice to use this functionality on a backup. twice!! it said what it said.

The result of running on a backup might look like the following:

<INDEX STATISTICS>
  table: test/table1, index: PRIMARY, space id: 12, root page 3
  estimated statistics in dictionary:
    key vals: 25265338, leaf pages 497839, size pages 498304
  real statistics:
     level 2 pages: pages=1, data=5395 bytes, data/pages=32%
     level 1 pages: pages=415, data=6471907 bytes, data/pages=95%
        leaf pages: recs=25958413, pages=497839, data=7492026403 bytes, data/pages=91%

This can be interpreted as follows:

  • The first line simply shows the table and index name and its internal identifiers. If you see an index named GEN_CLUST_INDEX, that is the table's clustered index, automatically created because you did not explicitly create a PRIMARY KEY.
  • The estimated statistics in dictionary information is similar to the data that's gathered through ANALYZE TABLE inside of InnoDB to be stored as estimated cardinality statistics and passed to the query optimizer.
  • The real statistics information is the result of scanning the data pages and computing exact information about the index.
  • The level <X> pages: output means that the line shows information about pages at that level in the index tree. The larger <X> is, the farther it is from the leaf pages, which are level 0. The first line is the root page.
  • The leaf pages output shows the leaf pages, of course. This is where the table's data is stored.
  • The external pages: output (not shown) shows large external pages that hold values too long to fit in the row itself, such as long BLOB and TEXT values.
  • The recs is the real number of records (rows) in leaf pages.
  • The pages is the page count.
  • The data is the total size of the data in the pages, in bytes.
  • The data/pages is calculated as (data / (pages * PAGE_SIZE)) * 100%. It will never reach 100% because of space reserved for page headers and footers.

A more detailed example is posted as a MySQL Performance Blog post.

Script to Format Output 🧚

The following script can be used to summarize and tabulate the output of the statistics information:

tabulate-xtrabackup-stats.pl

#!/usr/bin/env perl
use strict;
use warnings FATAL => 'all';
my $script_version = "0.1";

my $PG_SIZE = 16_384; # InnoDB defaults to 16k pages, change if needed.
my ($cur_idx, $cur_tbl);
my (%idx_stats, %tbl_stats);
my ($max_tbl_len, $max_idx_len) = (0, 0);
while ( my $line = <> ) {
   if ( my ($t, $i) = $line =~ m/table: (.*), index: (.*), space id:/ ) {
      $t =~ s!/!.!;
      $cur_tbl = $t;
      $cur_idx = $i;
      if ( length($i) > $max_idx_len ) {
         $max_idx_len = length($i);
      }
      if ( length($t) > $max_tbl_len ) {
         $max_tbl_len = length($t);
      }
   }
   elsif ( my ($kv, $lp, $sp) = $line =~ m/key vals: (\d+), \D*(\d+), \D*(\d+)/ ) {
      @{$idx_stats{$cur_tbl}->{$cur_idx}}{qw(est_kv est_lp est_sp)} = ($kv, $lp, $sp);
      $tbl_stats{$cur_tbl}->{est_kv} += $kv;
      $tbl_stats{$cur_tbl}->{est_lp} += $lp;
      $tbl_stats{$cur_tbl}->{est_sp} += $sp;
   }
   elsif ( my ($l, $pages, $bytes) = $line =~ m/(?:level (\d+)|leaf) pages:.*pages=(\d+), data=(\d+) bytes/ ) {
      $l ||= 0;
      $idx_stats{$cur_tbl}->{$cur_idx}->{real_pages} += $pages;
      $idx_stats{$cur_tbl}->{$cur_idx}->{real_bytes} += $bytes;
      $tbl_stats{$cur_tbl}->{real_pages} += $pages;
      $tbl_stats{$cur_tbl}->{real_bytes} += $bytes;
   }
}

my $hdr_fmt = "%${max_tbl_len}s %${max_idx_len}s %9s %10s %10s\n";
my @headers = qw(TABLE INDEX TOT_PAGES FREE_PAGES PCT_FULL);
printf $hdr_fmt, @headers;

my $row_fmt = "%${max_tbl_len}s %${max_idx_len}s %9d %10d %9.1f%%\n";
foreach my $t ( sort keys %tbl_stats ) {
   my $tbl = $tbl_stats{$t};
   printf $row_fmt, $t, "", $tbl->{est_sp}, $tbl->{est_sp} - $tbl->{real_pages},
      $tbl->{real_bytes} / ($tbl->{real_pages} * $PG_SIZE) * 100;
   foreach my $i ( sort keys %{$idx_stats{$t}} ) {
      my $idx = $idx_stats{$t}->{$i};
      printf $row_fmt, $t, $i, $idx->{est_sp}, $idx->{est_sp} - $idx->{real_pages},
         $idx->{real_bytes} / ($idx->{real_pages} * $PG_SIZE) * 100;
   }
}

Sample Script Output 💖

The output of the above Perl script, when run against the sample shown in the previously mentioned blog post, will appear as follows:

             TABLE           INDEX TOT_PAGES FREE_PAGES   PCT_FULL
art.link_out104                    832383      38561      86.8%
art.link_out104         PRIMARY    498304         49      91.9%
art.link_out104       domain_id     49600       6230      76.9%
art.link_out104     domain_id_2     26495       3339      89.1%
art.link_out104 from_message_id     28160        142      96.3%
art.link_out104    from_site_id     38848       4874      79.4%
art.link_out104   revert_domain    153984      19276      71.4%
art.link_out104    site_message     36992       4651      83.4%

The columns are the table and index, followed by the total number of pages in that index, the number of pages not actually occupied by data, and the number of bytes of real data as a percentage of the total size of the pages of real data. And the first line in the above output — the one where the INDEX column is empty — is a summary of the entire table. cute, right? 🎀

💖 Working with Binary Logs 💖

okay so the xtrabackup binary is besties with the log_status table. like, genuinely integrated. that means it can print out the binary log position that goes with your backup, and then you can use that position to provision a whole new replica or do a point-in-time recovery. slay. 🎀

🔍 Finding the Binary Log Position 🔍

you can find the binary log position that matches a backup after the backup has been taken. if your backup came from a server with binary logging turned on, xtrabackup makes a file called xtrabackup_binlog_info in the target directory. that file holds the binary log file name and the position of the exact point when the backup was taken. it's giving receipts. 💅

the output looks like this during the backup stage:

210715 14:14:59 Backup created in directory '/backup/'
MySQL binlog position: filename 'binlog.000002', position '156'
. . .
210715 14:15:00 completed OK!
NOTE: as of Percona XtraBackup 8.0.26-18.0, xtrabackup no longer creates the xtrabackup_binlog_pos_innodb file. why? because MySQL and Percona Server no longer update the binary log information on the global transaction system section of ibdata. so yeah, you should rely on xtrabackup_binlog_info no matter which storage engine you're using. periodt. 💀

⏰ Point-In-Time Recovery ⏰

want to do a point-in-time recovery from an xtrabackup backup? here's the vibe: you prepare and restore the backup, and then you replay the binary logs from the point shown in the xtrabackup_binlog_info file.

a more detailed procedure is found here.

🌱 Setting Up a New Replication Replica 🌱

to set up a new replica, you prepare the backup and restore it to the data directory of your shiny new replication replica.

  • if you're on version 8.0.22 or earlier: in your CHANGE MASTER TO command, use the binary log filename and position shown in the xtrabackup_binlog_info file to start replication.
  • if you're on 8.0.23 or later: use the CHANGE_REPLICATION_SOURCE_TO and the appropriate options. heads up, CHANGE_MASTER_TO is deprecated. 🫶

a more detailed procedure is found in ../howtos/setting_up_replication.

🌸 Restoring Individual Tables 🌸

Percona XtraBackup can export a table that lives in its own .ibd file. with Percona XtraBackup you can export individual tables from any InnoDB database, and import them into Percona Server for MySQL with XtraDB or MySQL 8.0. the source doesn't have to be XtraDB or MySQL 8.0 — but the destination does. also, this method only works on individual .ibd files. no exceptions bestie. ✨

the following example exports and imports this exact table:

CREATE TABLE export_test (
a int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

📦 Exporting the Table 📦

so we created the table in innodb_file_per_table mode, which means after taking a backup as usual with the --backup option, the .ibd file exists in the target directory:

$ find /data/backups/mysql/ -name export_test.*
/data/backups/mysql/test/export_test.ibd

when you prepare the backup, add the --export option to the command. here's an example:

$ xtrabackup --prepare --export --target-dir=/data/backups/mysql/
NOTE: if you're restoring an encrypted InnoDB tablespace table, add the keyring file: $ xtrabackup --prepare --export --target-dir=/tmp/table \ --keyring-file-data=/var/lib/mysql-keyring/keyring

now you should see an .exp file in the target directory:

$ find /data/backups/mysql/ -name export_test.*
/data/backups/mysql/test/export_test.exp
/data/backups/mysql/test/export_test.ibd
/data/backups/mysql/test/export_test.cfg

these three files are the only files required to import the table into a server running Percona Server for MySQL with XtraDB or MySQL 8.0. but wait — if the server uses InnoDB Tablespace Encryption, an additional .cfp file gets added, which contains the transfer key and an encrypted tablespace key. 🔐

NOTE: the .cfg metadata file contains an InnoDB dictionary dump in a special format. this format is different from the .exp one, which is used in XtraDB for the same purpose. a .cfg file is not required to import a tablespace to MySQL 8.0 or Percona Server for MySQL 8.0. a tablespace imports successfully even if the table is from another server, but InnoDB performs a schema validation if the corresponding .cfg file is located in the same directory. iykyk.

📥 Importing the Table 📥

on the destination server running Percona Server for MySQL with XtraDB and innodb_import_table_from_xtrabackup option enabled, or MySQL 8.0, create a table with the same structure, and then do these steps:

  1. Run the ALTER TABLE test.export_test DISCARD TABLESPACE; command. if you see the following error, enable innodb_file_per_table and create the table again.
Error

ERROR 1030 (HY000): Got error -1 from storage engine
  1. Copy the exported files to the test/ subdirectory of the destination server's data directory
  2. Run ALTER TABLE test.export_test IMPORT TABLESPACE;

and boom, the table is imported. you can run a SELECT to see the imported data. 👑

🧊 LRU dump backup 🧊

Percona XtraBackup includes a saved buffer pool dump into a backup to help reduce the warm up time. it restores the buffer pool state from the ib_buffer_pool file after restart. Percona XtraBackup discovers ib_buffer_pool and backs it up automatically. (imagine a cute screenshot right here 📸)

if the buffer restore option is enabled in my.cnf, the buffer pool will be in the warm state after the backup is restored. cozy. 🌈

SEE ALSO:

🌊 Streaming Backups 🌊

Percona XtraBackup supports streaming mode. streaming mode sends a backup to STDOUT in the xbstream format instead of copying the files to the backup directory. it's giving glow-up tbh. ✨

this method lets you use other programs to filter the output of the backup, which gives you way more flexibility for storing the backup. for example, compression happens by piping the output to a compression utility. and one of the benefits of streaming backups plus Unix pipes is that the backups can be automatically encrypted. 🔥

to use the streaming feature, you must use the --stream, providing the format of the stream (xbstream) and where to store the temporary files:

$ xtrabackup --stream=xbstream --target-dir=/tmp

xtrabackup uses xbstream to stream all of the data files to STDOUT, in a special xbstream format. after it finishes streaming all of the data files to STDOUT, it stops xtrabackup and streams the saved log file too. iconic behaviour honestly. 💅

SEE ALSO:

  • More information about xbstream
    • xbstream_binary

when compression is enabled, xtrabackup compresses the output data — except for the meta and non-InnoDB files, which are not compressed — using the specified compression algorithm. the only currently supported algorithm is quicklz. the resulting files have the qpress archive format, i.e. every *.qp file produced by xtrabackup is essentially a one-file qpress archive and can be extracted and uncompressed by the qpress file archiver, which is available from Percona Software repositories. 🍓

using xbstream as a stream option, backups can be copied and compressed in parallel. this option can significantly improve the speed of the backup process. heads up though: in case backups were both compressed and encrypted, they must be decrypted before they are uncompressed. 💀

here's your cheat sheet, converted from the table because bestie we don't do tables here:

  • Stream the backup into an archive named backup.xbstream:
xtrabackup --backup --stream=xbstream --target-dir=./ > backup.xbstream
  • Stream the backup into a compressed archive named backup.xbstream:
xtrabackup --backup --stream=xbstream --compress --target-dir=./ > backup.xbstream
  • Encrypt the backup:
$ xtrabackup --backup --stream=xbstream ./ > backup.xbstream gzip - | openssl des3 -salt -k "password" > backup.xbstream.gz.des3
  • Unpack the backup to the current directory:
xbstream -x < backup.xbstream
  • Send the backup compressed directly to another host and unpack it:
xtrabackup --backup --compress --stream=xbstream --target-dir=./ | ssh user@otherhost "xbstream -x"
  • Send the backup to another server with netcat 💌 — on the destination host you run:
$ nc -l 9999 | cat - > /data/backups/backup.xbstream

and on the source host you run:

$ xtrabackup --backup --stream=xbstream ./ | nc desthost 9999
  • Send the backup to another server with a one-liner ✨ — the whole thing, destination listening plus source pushing, in one go:
$ ssh user@desthost "( nc -l 9999 > /data/backups/backup.xbstream & )" && xtrabackup --backup --stream=xbstream ./ |  nc desthost 9999
  • Throttle the throughput to 10MB/sec using the pipe viewer tool [1] 🚦 — literally just clip a rate limit into the pipe:
$ xtrabackup --backup --stream=xbstream ./ | pv -q -L10m ssh user@desthost "cat - > /data/backups/backup.xbstream"
  • Checksumming the backup while it's streaming 🧮 — living for the integrity check era. On the destination host:
$ nc -l 9999 | tee >(sha1sum > destination_checksum) > /data/backups/backup.xbstream

On the source host:

$ xtrabackup --backup --stream=xbstream ./ | tee >(sha1sum > source_checksum) | nc desthost 9999

Then compare the checksums on the source host:

$ cat source_checksum
65e4f916a49c1f216e0887ce54cf59bf3934dbad  -

And compare the checksums on the destination host:

$ cat destination_checksum
65e4f916a49c1f216e0887ce54cf59bf3934dbad  -
  • Parallel compression with parallel copying backup 🌈 — the full multitasking queen setup:
xtrabackup --backup --compress --compress-threads=8 --stream=xbstream --parallel=4 --target-dir=./ > backup.xbstream

📌 FOOTNOTES 📌

  • [1] Install from the official site or from the distribution package (apt install pv).
ok bestie, real talk: note that the streamed backup will need to be prepared before restoration. Streaming mode does not prepare the backup. no cap 🫶

🔐 Encrypting Backups 🔐

Percona XtraBackup supports encrypting and decrypting both local and streaming backups with the xbstream option, adding another whole layer of protection. The encryption is implemented using the libgcrypt library from GnuPG. it's giving secure 💅

✨ Creating Encrypted Backups ✨

To make an encrypted backup you need to specify the following options (heads up: --encrypt-key and --encrypt-key-file are mutually exclusive, i.e. you just provide one of them):

  • --encrypt
  • --encrypt-key
  • --encrypt-key-file

Both the --encrypt-key option and the --encrypt-key-file option can be used to specify the encryption key. An encryption key can be generated with a command like:

openssl rand -base64 32

Example output of that command should look like this:

U2FsdGVkX19VPN7VM+lwNI0fePhjgnhgqmDBqbF3Bvs=

This value then can be used as the encryption key. 💖

🔑 The --encrypt-key Option 🔑

Example of the xtrabackup command using the --encrypt-key should look like this:

$  xtrabackup --backup --encrypt=AES256 --encrypt-key="U2FsdGVkX19VPN7VM+lwNI0fePhjgnhgqmDBqbF3Bvs=" --target-dir=/data/backup

📄 The --encrypt-key-file Option 📄

Use the --encrypt-key-file option as follows:

$ xtrabackup --backup --encrypt=AES256 --encrypt-key-file=/data/backups/keyfile --target-dir=/data/backup
NOTE: Depending on the text editor that you use to make the KEYFILE, the editor can automatically insert the CRLF (end of line) character. This will cause the key size to grow and thus making it invalid. The suggested way to create the file is by using the command line: echo -n "U2FsdGVkX19VPN7VM+lwNI0fePhjgnhgqmDBqbF3Bvs=" > /data/backups/keyfile 😭 do NOT let your editor sabotage you like this bestie.

⚡ Optimizing the encryption process ⚡

Two new options are available for encrypted backups that can be used to speed up the encryption process. These are --encrypt-threads and --encrypt-chunk-size. By using the --encrypt-threads option multiple threads can be specified to be used for encryption in parallel. Option --encrypt-chunk-size can be used to specify the size (in bytes) of the working encryption buffer for each encryption thread (default is 64K). 🔥

🔓 Decrypting Encrypted Backups 🔓

Backups can be decrypted with xbcrypt. The following one-liner can be used to encrypt the whole folder:

$ for i in `find . -iname "*\.xbcrypt"`; do xbcrypt -d --encrypt-key-file=/root/secret_key --encrypt-algo=AES256 < $i > $(dirname $i)/$(basename $i .xbcrypt) && rm $i; done

Percona XtraBackup --decrypt option has been implemented that can be used to decrypt the backups:

$ xtrabackup --decrypt=AES256 --encrypt-key="U2FsdGVkX19VPN7VM+lwNI0fePhjgnhgqmDBqbF3Bvs=" --target-dir=/data/backup/

Percona XtraBackup doesn't automatically remove the encrypted files. In order to clean up the backup directory users should remove the *.xbcrypt files. 🧹

NOTE: --parallel can be used with --decrypt option to decrypt multiple files simultaneously. 💅

When the files are decrypted, the backup can be prepared.

🎀 Preparing Encrypted Backups 🎀

After the backups have been decrypted, they can be prepared in the same way as the standard full backups with the --prepare option:

$ xtrabackup --prepare --target-dir=/data/backup/

🫶 Restoring Encrypted Backups 🫶

xtrabackup offers the --copy-back option to restore a backup to the server's datadir:

$ xtrabackup --copy-back --target-dir=/data/backup/

It will copy all the data-related files back to the server's datadir, determined by the server's my.cnf configuration file. You should check the last line of the output for a success message:

150318 11:08:13  xtrabackup: completed OK!

SEE ALSO:

🔒 FLUSH TABLES WITH READ LOCK option 🔒

The FLUSH TABLES WITH READ LOCK option does the following with a global read lock:

  • Closes all open tables
  • Locks all tables for all databases

Release the lock with UNLOCK TABLES.

NOTE: FLUSH TABLES WITH READ LOCK does not prevent inserting rows into the log tables. 👀

To ensure consistent backups, use the FLUSH TABLES WITH READ LOCK option before taking a non-InnoDB file backup. The option does not affect long-running queries.

Long-running queries with FLUSH TABLES WITH READ LOCK enabled can leave the server in a read-only mode until the queries finish. Killing the FLUSH TABLES WITH READ LOCK does not help if the database is in either the Waiting for table flush or Waiting for master to send event state. To return to normal operation, you must kill any long-running queries. 💀

NOTE: All described in this section has no effect when backup locks are used. Percona XtraBackup will use Backup locks where available as a lightweight alternative to FLUSH TABLES WITH READ LOCK. This feature is available in Percona Server for MySQL 5.6+. Percona XtraBackup uses this automatically to copy non-InnoDB data to avoid blocking DML queries that modify InnoDB tables. ✨

In order to prevent this from happening two things have been implemented:

  • xtrabackup waits for a good moment to issue the global lock
  • xtrabackup kills all queries or only the SELECT queries which prevent the global lock from being acquired

⏳ Waiting for queries to finish ⏳

You should issue a global lock when no long queries are running. Waiting to issue the global lock for extended period of time is not a good method. The wait can extend the time needed for backup to take place. The --ftwrl-wait-timeout option can limit the waiting time. If it cannot issue the lock during this time, xtrabackup stops the option, exits with an error message, and backup is not be taken.

The default value for this option is zero (0) value which turns off the option.

Okay so another possibility is that you specify which type of query you're waiting on, with --ftwrl-wait-query-type. The possible values are all and update. When it's all, xtrabackup waits for every long-running query (execution time longer than what --ftwrl-wait-threshold allows) to finish before it runs the FLUSH TABLES WITH READ LOCK. When it's update, xtrabackup waits on UPDATE/ALTER/REPLACE/INSERT queries to finish. 💅

Ngl, predicting exactly how long a specific query needs to complete is basically impossible. The vibe is: we assume long-running queries are not gonna finish any time soon, and other queries that run for a short time finish quickly. xtrabackup uses the value of the --ftwrl-wait-threshold option to figure out which queries count as long-running, and it will block a global lock. To use this option, the xtrabackup user should have PROCESS and SUPER privileges. 🔐

✨ Killing the blocking queries ✨

The second option is to just kill all the queries that are preventing you from acquiring the global lock. In this case, every query that runs longer than FLUSH TABLES WITH READ LOCK is a potential blocker. Even though all queries can be killed, you can give the short-running ones a little extra time to finish using the --kill-long-queries-timeout option. This option specifies the time for queries to complete — once that value is reached, all the running queries get killed. The default value is 0, which turns this whole feature off. 🙈

The --kill-long-query-type option can be used to specify all queries, or only SELECT queries, that are preventing the global lock from being acquired. To use this option, the xtrabackup user should have PROCESS and SUPER privileges. ✨

🎀 Options summary 🎀

  • --ftwrl-wait-timeout (seconds) — how long to wait for a good moment. Default is 0, meaning don't wait.
  • --ftwrl-wait-query-type — which long queries should be finished before FLUSH TABLES WITH READ LOCK is run. Default is all.
  • --ftwrl-wait-threshold (seconds) — how long a query should be running before we consider it long running and a potential blocker of the global lock.
  • --kill-long-queries-timeout (seconds) — how much time we give for queries to complete after FLUSH TABLES WITH READ LOCK is issued, before we start to kill. Default is 0, meaning don't kill.
  • --kill-long-query-type — which queries should be killed once kill-long-queries-timeout has expired.

🌸 Example 🌸

Running xtrabackup with the following options will make it spend no longer than 3 minutes waiting for all queries older than 40 seconds to complete. ⏳

$  xtrabackup --backup --ftwrl-wait-threshold=40 \
--ftwrl-wait-query-type=all --ftwrl-wait-timeout=180 \
--kill-long-queries-timeout=20 --kill-long-query-type=all \
--target-dir=/data/backups/

After FLUSH TABLES WITH READ LOCK is issued, xtrabackup will wait for 20 seconds for the lock to be acquired. If the lock still isn't acquired after those 20 seconds, it will kill all queries that are running longer than the FLUSH TABLES WITH READ LOCK. 💀

🔥 Accelerating the backup process 🔥

⭐ Copying with the --parallel and --compress-threads Options ⭐

When making a local or streaming backup with the xbstream option, multiple files can be copied at the same time when you use the --parallel option. This option specifies the number of threads created by xtrabackup to copy data files. 🏃‍♀️💨

To take advantage of this option, either the multiple tablespaces option must be enabled (innodb_file_per_table), or the shared tablespace must be stored in multiple ibdata files with the innodb_data_file_path option. Having multiple files for the database (or splitting one into many) doesn't have a measurable impact on performance. 🫶

Because this feature is implemented at the file level, concurrent file transfer can sometimes increase I/O throughput when doing a backup on highly fragmented data files, thanks to the overlap of a greater number of random read requests. You should consider tuning the filesystem too, to get the maximum performance (e.g. checking fragmentation). 🧚

If the data is stored on a single file, this option will have no effect. 😭

To use this feature, literally just add the option to a local backup, like:

$ xtrabackup --backup --parallel=4 --target-dir=/path/to/backup

By using the xbstream in streaming backups, you can additionally speed up the compression process with the --compress-threads option. This option specifies the number of threads created by xtrabackup for parallel data compression. The default value for this option is 1. 🌈

To use this feature, simply add the option to a local backup, for example:

$ xtrabackup --backup --stream=xbstream --compress --compress-threads=4 --target-dir=./ > backup.xbstream

Before applying logs, compressed files will need to be uncompressed. ⭐

🦋 The --rsync Option 🦋

In order to speed up the backup process and minimize the time FLUSH TABLES WITH READ LOCK is blocking the writes, the --rsync option should be used. When this option is specified, xtrabackup uses rsync to copy all non-InnoDB files instead of spawning a separate cp for each file — which can be much faster for servers with a large number of databases or tables. xtrabackup will call the rsync twice: once before the FLUSH TABLES WITH READ LOCK, and once during, to minimize the time the read lock is being held. During the second rsync call, it will only synchronize the changes to non-transactional data (if any) since the first call performed before the FLUSH TABLES WITH READ LOCK. 👑

Note that Percona XtraBackup will use Backup locks where available as a lightweight alternative to FLUSH TABLES WITH READ LOCK. This feature is available in Percona Server for MySQL 5.6+. Percona XtraBackup uses this automatically to copy non-InnoDB data, to avoid blocking DML queries that modify InnoDB tables. ✨

NOTE: ⚠️ This option cannot be used together with the --stream option.

💖 Point-In-Time recovery 💖

Recovering up to a particular moment in the database's history can be done with xtrabackup and the binary logs of the server. 🕰️

Note that the binary log contains the operations that modified the database from a point in the past. You need a full datadir as a base, and then you can apply a series of operations from the binary log to make the data match what it was at the point in time you want. 🌸

$ xtrabackup --backup --target-dir=/path/to/backup
$ xtrabackup --prepare --target-dir=/path/to/backup

For more details on these procedures, see creating_a_backup and preparing_a_backup. 📚

Now, suppose some time has passed and you want to restore the database to a certain point in the past — keeping in mind that there's the constraint of the point where the snapshot was taken. 🎀

To find out what's the situation of binary logging in the server, execute the following queries:

mysql> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       126 |
| mysql-bin.000002 |      1306 |
| mysql-bin.000003 |       126 |
| mysql-bin.000004 |       497 |
+------------------+-----------+

and

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |      497 |              |                  |
+------------------+----------+--------------+------------------+

The first query tells you which files contain the binary log, and the second one tells you which file is currently being used to record changes, plus the current position within it. Those files are stored usually in the datadir (unless another location is specified when the server is started with the --log-bin= option). 👑

To find out the position of the snapshot taken, check the xtrabackup_binlog_info in the backup's directory:

$ cat /path/to/backup/xtrabackup_binlog_info
mysql-bin.000003      57

This tells you which file was used at the moment of the backup for the binary log, and its position. That position will be the effective one when you restore the backup: 💅

$ xtrabackup --copy-back --target-dir=/path/to/backup

Since the restoration will not affect the binary log files (you may need to adjust file permissions, see restoring_a_backup), the next step is extracting the queries from the binary log with mysqlbinlog, starting from the position of the snapshot and redirecting it to a file. 🦋

$ mysqlbinlog /path/to/datadir/mysql-bin.000003 /path/to/datadir/mysql-bin.000004 \
    --start-position=57 > mybinlog.sql

Note that if you have multiple files for the binary log, like in the example, you have to extract the queries with one process, as shown above. ⭐

Inspect the file with the queries to determine which position or date corresponds to the point-in-time you want. Once determined, pipe it to the server. Assuming the point is 11-12-25 01:00:00: 🕐

$ mysqlbinlog /path/to/datadir/mysql-bin.000003 /path/to/datadir/mysql-bin.000004 \
    --start-position=57 --stop-datetime="11-12-25 01:00:00" | mysql -u root -p

and the database will be rolled forward up to that Point-In-Time. 🔥

🌈 Making Backups in Replication Environments 🌈

There are options specific to backing up from a replication replica. 💫

💜 The --slave-info Option 💜

This option is useful when backing up a replication replica server. It prints the binary log position and name of the source server. It also writes this information to the xtrabackup_slave_info file as a CHANGE MASTER statement. 📝

This option is useful for setting up a new replica for this source. You can start a replica server with this backup and issue the statement saved in the xtrabackup_slave_info file. More details of this procedure can be found in replication_howto. 🫶

🎀 The --safe-slave-backup Option 🎀

In order to assure a consistent replication state, this option stops the replication SQL thread and waits to start backing up until Slave_open_temp_tables in SHOW STATUS is zero. If there are no open temporary tables, the backup will take place; otherwise, the SQL thread will be started and stopped until there are no open temporary tables. 😭 The backup will fail if Slave_open_temp_tables does not become zero after --safe-slave-backup-timeout seconds (defaults to 300 seconds). The replication SQL thread will be restarted when the backup finishes. ✨

Using this option is always recommended when taking backups from a replica server. 👑

WARNING: ⚠️ Make sure your replica is a true replica of the source before using it as a source for backup. A good tool to validate a replica is pt-table-checksum.

💾 Store backup history on the server ✨

okay bestie, listen — Percona XtraBackup can literally keep a receipts folder on the server. It stores your backups' history right there on the server itself. This showed up in Percona XtraBackup 2.2, and the whole reason it exists is so you get extra deets about the backups you're taking. All that history info lives in the PERCONA_SCHEMA.XTRABACKUP_HISTORY table. no cap. 🎀

To use this feature, here are the options you get:

  • --history=<name> — turns the history feature ON and lets you name your backup series, which gets stashed inside the history record. 💅
  • --incremental-history-name=<name> — lets you take an incremental backup based on a specific history series, by name. xtrabackup goes digging through the history table looking for the most recent backup in that series (the one with the highest to_lsn) and grabs that to_lsn value to use as its starting lsn. This is mutually exclusive with --incremental-history-uuid, --incremental-basedir and --incremental-lsn. And if no valid LSN can be found (like, no series by that name exists), xtrabackup will return with an error. 😭
  • --incremental-history-uuid=<uuid> — lets you take an incremental backup based on a specific history record, identified by UUID. xtrabackup searches the history table for the record matching that UUID and takes its to_lsn value to use as its starting LSN. This one is mutually exclusive with --incremental-basedir, --incremental-lsn and --incremental-history-name. If no valid LSN can be found (no record with that UUID, or a missing to_lsn), xtrabackup will return with an error. 🫠
NOTE: the backup that's currently being performed will NOT exist in the xtrabackup_history table inside the resulting backup set, because the record doesn't get added to that table until after the backup has been taken. it's giving "you can't photograph yourself inside the photo" energy. 🙈

So if you want access to your backup history outside of your backup set — like, in case of some catastrophic event 👑 — you're gonna need to either run a mysqldump, do a partial backup, or run SELECT * on the history table after xtrabackup completes, and then store those results alongside your backup set. protect the receipts, bestie. 💖

For the privileges you need, go peek at pxb.privilege.

🗂️ PERCONA_SCHEMA.XTRABACKUP_HISTORY table

This table is where all the lore about your previous server backups lives. Info about backups only gets written in there if the backup was taken with the --history option. periodt. ✨

  • uuid — Unique backup id
  • name — User provided name of backup series. There may be multiple entries with the same name, used to identify related backups in a series.
  • tool_name — Name of tool used to take backup
  • tool_command — Exact command line given to the tool, with --password and --encryption_key obfuscated
  • tool_version — Version of tool used to take backup
  • ibbackup_version — Version of the xtrabackup binary used to take backup
  • server_version — Server version on which backup was taken
  • start_time — Time at the start of the backup
  • end_time — Time at the end of the backup
  • lock_time — Amount of time, in seconds, spent calling and holding locks for FLUSH TABLES WITH READ LOCK
  • binlog_pos — Binlog file and position at end of FLUSH TABLES WITH READ LOCK
  • innodb_from_lsn — LSN at beginning of backup, which can be used to determine prior backups
  • innodb_to_lsn — LSN at end of backup, which can be used as the starting lsn for the next incremental
  • partial — Is this a partial backup? If N, that means it's the full backup
  • incremental — Is this an incremental backup
  • format — Description of result format (xbstream)
  • compact — Is this a compact backup
  • compressed — Is this a compressed backup
  • encrypted — Is this an encrypted backup

🚧 Limitations

  • --history option must be specified only on the command line and not within a configuration file in order to be effective. fr fr, config file won't cut it. 💀
  • --incremental-history-name and --incremental-history-uuid options must be specified only on the command line and not within a configuration file in order to be effective. same rule, bestie. 🌸

💾 IMPLEMENTATION 💾

🛠️ Implementation Details 🛠️

Okay bestie, this bit is the behind-the-scenes tea — all the ~internal~ gossip about how the xtrabackup tool actually does its thing.

🔐 File Permissions 🔐

Real talk: xtrabackup opens your source data files in read-write mode, even though it does not modify those files. Not a single byte changed, promise. 😤

Which means — and this is the part that gets people every time — you have to run xtrabackup as a user who has permission to write the data files.

Why tho? Because xtrabackup uses the embedded InnoDB libraries to open and read the files, and InnoDB just opens everything read-write by default, since it's normally assuming it's gonna be writing to them. Not its fault, it's just built that way. 💅

⚡ Tuning the OS Buffers ⚡

Because xtrabackup reads huge amounts of data from the filesystem, it uses posix_fadvise() wherever it possibly can to tell the operating system: bestie, do NOT try to cache these blocks, I'm not coming back for them. 🚫

Without that little hint, the OS would be like "aww cute, they'll definitely want these again" and cache everything — which is NOT the case, okay? And caching giant files like that puts mad pressure on the operating system's virtual memory and can get other processes (like, oh idk, your database server 👀) swapped out. Rude.

So xtrabackup avoids all that with this hint, applied to both the source and destination files:

posix_fadvise(file, 0, 0, POSIX_FADV_DONTNEED)

On top of that, xtrabackup asks the OS to do more aggressive read-ahead optimizations on the source files:

posix_fadvise(file, 0, 0, POSIX_FADV_SEQUENTIAL)

📦 Copying Data Files 📦

When it's copying data files over to the target directory, xtrabackup reads and writes 1 MB of data at a time. That number is not configurable. Periodt. 🎀

When it's copying the log file, xtrabackup reads and writes 512 bytes at a time. Also not configurable, and that one matches InnoDB's behavior exactly (there's a workaround in Percona Server for MySQL since it has an option to tune innodb_log_block_size for XtraDB — and in that case Percona XtraBackup will match the tuning 🫶).

After reading from the files, xtrabackup iterates over that 1MB buffer a page at a time and checks each page for corruption using InnoDB's buf_page_is_corrupted() function. If a page is corrupt, it re-reads and retries up to 10 times for each page. It skips this check entirely on the doublewrite buffer. ✨

🚪 xtrabackup Exit Codes 🚪

The xtrabackup binary exits with the traditional success value of 0 after a backup when no error occurs. If an error happens during the backup, the exit value is 1.

But wait — in certain cases the exit value can be something other than 0 or 1, because of the command-line option code included from the MySQL libraries. An unknown command-line option, for example, will cause an exit code of 255. 💀 Surprise!

📚 REFERENCES 📚

📖 The xtrabackup Option Reference 📖

This page documents all of the command-line options for the xtrabackup binary.

🎭 Modes of operation 🎭

You invoke xtrabackup in one of the following modes:

  • --backup mode to make a backup in a target directory
  • --prepare mode to restore data from a backup (created in --backup mode)
  • --copy-back to copy data from a backup to the location that contained the original data; to move data instead of copying use the alternate --move-back mode.
  • --stats mode to scan the specified data files and print out index statistics.

When you intend to run xtrabackup in any of these modes, use the following syntax:

$ xtrabackup [--defaults-file=#] --backup|--prepare|--copy-back|--stats [OPTIONS]

For example, the --prepare mode is applied as follows:

$ xtrabackup --prepare --target-dir=/data/backup/mysql/

For all modes, the default options are read from the xtrabackup and mysqld configuration groups from the following files, in the given order:

  1. /etc/my.cnf
  2. /etc/mysql/my.cnf
  3. /usr/etc/my.cnf
  4. ~/.my.cnf

As the first parameter to xtrabackup (in place of the --defaults-file), you may supply one of the following:

  • --print-defaults to have xtrabackup print the argument list and exit.
  • --no-defaults to forbid reading options from any file but the login file.
  • --defaults-file to read the default options from the given file.
  • --defaults-extra-file to read the specified additional file after the global files have been read.
  • --defaults-group-suffix to read the configuration groups with the given suffix. The effective group name is constructed by concatenating the default configuration groups (xtrabackup and mysqld) with the given suffix.
  • --login-path to read the given path from the login file.

🐬 InnoDB Options 🐬

There is a whole large group of InnoDB options that are normally read from the my.cnf configuration file, so that xtrabackup boots up its embedded InnoDB in the exact same configuration as your current server. You normally do not need to specify them explicitly. These options have the same behavior in InnoDB and XtraDB. See --innodb-miscellaneous for more information. 💖

⚙️ Options ⚙️

  • --apply-log-only — this option makes it so only the redo stage gets performed when preparing a backup. It is very important for incremental backups. Like, do not skip this one.
  • --backup — make a backup and place it in --target-dir. See Creating a backup.
  • --backup-lock-timeout — the timeout in seconds for attempts to acquire metadata locks.
  • --backup-lock-retry-count — the number of attempts to acquire metadata locks.
  • --backup-locks — this option controls whether backup locks should be used instead of FLUSH TABLES WITH READ LOCK on the backup stage. The option has no effect when backup locks are not supported by the server. This option is enabled by default, disable with --no-backup-locks.
  • --check-privileges — this option checks whether Percona XtraBackup has all the required privileges. If a missing privilege is required for the current operation, it will terminate and print out an error message. If a missing privilege is not required for the current operation but may be necessary for some other XtraBackup operation, the process is not aborted and a warning is printed instead. Like so:
xtrabackup: Error: missing required privilege LOCK TABLES on *.*
xtrabackup: Warning: missing required privilege REPLICATION CLIENT on *.*
  • --close-files — do not keep files opened. When xtrabackup opens a tablespace it normally doesn't close its file handle, so that it can handle DDL operations correctly. However, if the number of tablespaces is really huge and can't fit into any limit, there's this option to close file handles once they're no longer accessed. 🫣
⚠️ Percona XtraBackup can produce inconsistent backups with this option enabled. Use at your own risk.
  • --compress — this option tells xtrabackup to compress all output data, including the transaction log file and meta data files, using either the quicklz or lz4 compression algorithm. quicklz is chosen by default.

When using --compress=quicklz or --compress, the resulting files have the qpress archive format, i.e. every *.qp file produced by xtrabackup is essentially a one-file qpress archive and can be extracted and uncompressed by the qpress file archiver.

--compress=lz4 produces *.lz4 files. You can extract the contents of these files by using a program such as lz4. 🌈

SEE ALSO:

  • --compress-chunk-size=# — size of working buffer(s) for compression threads, in bytes. The default value is 64K.
  • --compress-threads=# — this option specifies the number of worker threads used by xtrabackup for parallel data compression. This option defaults to 1. Parallel compression (--compress-threads) can be used together with parallel file copying (--parallel). For example, --parallel=4 --compress --compress-threads=2 will create 4 I/O threads that will read the data and pipe it to 2 compression threads. 🔥
  • --copy-back — copy all the files in a previously made backup from the backup directory to their original locations. This option will not copy over existing files unless the --force-non-empty-directories option is specified.
  • --core-file — write core on fatal signals.
  • --databases=# — this option specifies a list of databases and tables that should be backed up. The option accepts a list of the form "databasename1[.table_name1] databasename2[.table_name2] . . .".
  • --databases-exclude=name — excluding databases based on name. Operates the same way as --databases, but matched names are excluded from the backup. Note that this option has a higher priority than --databases.
  • --databases-file=# — this option specifies the path to the file containing the list of databases and tables that should be backed up. The file can contain the list elements of the form databasename1[.table_name1], one element per line.
  • --datadir=DIRECTORY — the source directory for the backup. This should be the same as the datadir for your MySQL server, so it should be read from my.cnf if that exists; otherwise you must specify it on the command line.

When combined with the --copy-back or --move-back option, --datadir refers to the destination directory.

Once connected to the server, in order to perform a backup you will need READ and EXECUTE permissions at a filesystem level in the server's datadir. 🔑

  • --debug-sleep-before-unlock=# — this is a debug-only option used by the xtrabackup test suite.
  • --debug-sync=name — the debug sync point. This option is only used by the xtrabackup test suite.
  • --decompress — decompresses all files with the .qp extension in a backup previously made with the --compress option. The --parallel option will allow multiple files to be decrypted simultaneously. In order to decompress, the qpress utility MUST be installed and accessible within the path. Percona XtraBackup does not automatically remove the compressed files. In order to clean up the backup directory, users should use the --remove-original option. 🧹

The --decompress option may be used with xbstream to decompress individual qpress files.

If you used the lz4 compression algorithm to compress the files (--compress=lz4), change the --decompress parameter accordingly: --decompress=lz4.

  • --decompress-threads=# — force xbstream to use the specified number of threads for decompressing.
  • --decrypt=ENCRYPTION-ALGORITHM — bestie, this one decrypts every single file with the .xbcrypt extension in a backup you already made with --encrypt. And the --parallel option? It lets multiple files get decrypted at the same time, we love multitasking 💅. Important though: Percona XtraBackup does not automatically remove the encrypted files. So if you want your backup directory cleaned up, you gotta use --remove-original, no cap.
  • --defaults-extra-file=[MY.CNF] — read this file after the global files are read. And listen — it must be given as the first option on the command-line. First. Option. Periodt.
  • --defaults-file=[MY.CNF] — only read default options from the given file. Also must be the very first option on the command-line, and it must be a real file — it cannot be a symbolic link. Like, it's giving strict. 🚫🔗
  • --defaults-group=GROUP-NAME — this sets the group that should be read from the configuration file. It's used by xtrabackup if you use the --defaults-group option, and it's needed for mysqld_multi deployments. iykyk ✨
  • --defaults-group-suffix=# — also reads groups with concat(group, suffix). Cute, right?
  • --dump-innodb-buffer-pool — this option controls whether a new dump of buffer pool content should happen or not.

With --dump-innodb-buffer-pool, xtrabackup makes a request to the server to start the buffer pool dump (heads up: it takes some time to complete and it's done in background) at the beginning of a backup — but only provided the status variable innodb_buffer_pool_dump_status reports that the dump has been completed.

$ xtrabackup --backup --dump-innodb-buffer-pool --target-dir=/home/user/backup

By default, this option is set to OFF.

If innodb_buffer_pool_dump_status reports that there's a running dump of the buffer pool, xtrabackup waits for the dump to complete using the value of --dump-innodb-buffer-pool-timeout.

The file ib_buffer_pool stores tablespace ID and page ID data, which is used to warm up the buffer pool sooner. 🔥

SEE ALSO:

  • --dump-innodb-buffer-pool-timeout — this one holds the number of seconds that xtrabackup should monitor the value of innodb_buffer_pool_dump_status to figure out if the buffer pool dump has completed. ⏱️

It's used in combination with --dump-innodb-buffer-pool. By default, it's set to 10 seconds.

  • --dump-innodb-buffer-pool-pct — this contains the percentage of the most recently used buffer pool pages to dump.

This option is effective if --dump-innodb-buffer-pool is set to ON. If this option contains a value, xtrabackup sets the MySQL system variable innodb_buffer_pool_dump_pct. And the moment the buffer pool dump completes — or it gets stopped (see --dump-innodb-buffer-pool-timeout) — the value of that MySQL system variable is restored. Put it back the way you found it, queen 👑

SEE ALSO:

  • --encrypt=ENCRYPTION_ALGORITHM — this tells xtrabackup to encrypt backup copies of InnoDB data files using the algorithm you name in ENCRYPTION_ALGORITHM. Currently supported algorithms are: AES128, AES192 and AES256. Secure the bag 🔐
  • --encrypt-key=ENCRYPTION_KEY — a proper length encryption key to use. Big warning bestie: it is not recommended to use this option where there's uncontrolled access to the machine, because the command line — and therefore the key — can be viewed as part of the process info. 😭 Everyone can see it. Do not do this on a shared box.
  • --encrypt-key-file=ENCRYPTION_KEY_FILE — the name of a file where the raw key of the appropriate length can be read from. The file must be a simple binary (or text) file that contains exactly the key to be used.

It is passed directly to the xtrabackup child process. See the xtrabackup documentation for more details.

  • --encrypt-threads=# — specifies the number of worker threads that will be used for parallel encryption/decryption. See the xtrabackup documentation for more details. 🧵
  • --encrypt-chunk-size=# — specifies the size of the internal working buffer for each encryption thread, measured in bytes. It is passed directly to the xtrabackup child process. See the xtrabackup documentation for more details.
  • --export — create files necessary for exporting tables. See Restoring Individual Tables.
  • --extra-lsndir=DIRECTORY — (for --backup): save an extra copy of the xtrabackup_checkpoints and xtrabackup_info files in this directory. Extra copies, because why not have both 💖
  • --force-non-empty-directories — when specified, it makes --copy-back and --move-back transfer files to non-empty directories. No existing files will be overwritten. But heads up: if files that need to be copied/moved from the backup directory already exist in the destination directory, it will still fail with an error. So it's only a partial pass, ngl.
  • --ftwrl-wait-timeout=SECONDS — specifies the time in seconds that xtrabackup should wait for queries that would block FLUSH TABLES WITH READ LOCK before running it. If there are still such queries when the timeout expires, xtrabackup terminates with an error. Default is 0, in which case it does not wait for queries to complete and starts FLUSH TABLES WITH READ LOCK immediately. Where supported, xtrabackup will automatically use Backup Locks as a lightweight alternative to FLUSH TABLES WITH READ LOCK to copy non-InnoDB data, so it avoids blocking DML queries that modify InnoDB tables. ✨
  • --ftwrl-wait-threshold=SECONDS — specifies the query run time threshold which is used by xtrabackup to detect long-running queries with a non-zero value of --ftwrl-wait-timeout. FLUSH TABLES WITH READ LOCK is not started until such long-running queries exist. This option has no effect if --ftwrl-wait-timeout is 0. Default value is 60 seconds. Where supported, xtrabackup will automatically use Backup Locks as a lightweight alternative to FLUSH TABLES WITH READ LOCK to copy non-InnoDB data, to avoid blocking DML queries that modify InnoDB tables.
  • --ftwrl-wait-query-type=all|update — specifies which types of queries are allowed to complete before xtrabackup will issue the global lock. Default is all.
  • --galera-info — this option creates the xtrabackup_galera_info file, which contains the local node state at the time of the backup. Should be used when you're doing the backup of Percona XtraDB Cluster. It has no effect when backup locks are used to create the backup. 🌈
  • --generate-new-master-key — generate a new master key when doing a copy-back. Fresh key, fresh start 🔑
  • --generate-transition-key — xtrabackup needs to access the same keyring file or vault server during prepare and copy-back, but it should not depend on whether the server keys have been purged.

--generate-transition-key creates and adds to the keyring a transition key for xtrabackup to use if the master key used for encryption is not found — because it has been rotated and purged.

  • --get-server-public-key — get the server public key.

SEE ALSO:

  • --help — when run with this option, or without any options at all, xtrabackup displays information about how to run the program on the command line, along with all supported options and variables with default values where appropriate. The classic "help me bestie" move 🫶
  • --history=NAME — enables the tracking of backup history in the PERCONA_SCHEMA.xtrabackup_history table. An optional history series name may be specified, and that name will be placed with the history record for the current backup being taken. 📖
  • --host=HOST — accepts a string argument that specifies the host to use when connecting to the database server with TCP/IP. It is passed to the mysql child process without alteration. See mysql --help for details.
  • --incremental — tells xtrabackup to create an incremental backup. It is passed to the xtrabackup child process. When this option is specified, either --incremental-lsn or --incremental-basedir can also be given. If neither option is given, then --incremental-basedir is passed to xtrabackup by default, set to the first timestamped backup directory in the backup base directory.

SEE ALSO:

  • More information about incremental backups See section xb_incremental
  • --incremental-basedir=DIRECTORY — when creating an incremental backup, this is the directory containing the full backup that is the base dataset for the incremental backups. Like the foundation of the whole fit 💅
  • --incremental-dir=DIRECTORY — when preparing an incremental backup, this is the directory where the incremental backup gets combined with the full backup to make a new full backup. Glow up ✨
  • --incremental-force-scan — when creating an incremental backup, force a full scan of the data pages in the instance being backuped, even if the complete changed page bitmap data is available. No shortcuts, we doing the full walk 🚶‍♀️
  • --incremental-history-name=name — specifies the name of the backup series stored in the PERCONA_SCHEMA.xtrabackup_history history record to base an incremental backup on. xtrabackup will search the history table looking for the most recent (highest innodb_to_lsn), successful backup in the series, and take the to_lsn value to use as the starting lsn for the incremental backup. This will be mutually exclusive with --incremental-history-uuid, --incremental-basedir and --incremental-lsn. If no valid lsn can be found (no series by that name, no successful backups by that name), xtrabackup will return with an error. It is used with the --incremental option. 🔍
  • --incremental-history-uuid=name — bestie, this one lets you point at which history record you wanna build your incremental backup on top of, using its UUID — that record lives in PERCONA_SCHEMA.xtrabackup_history. It plays nice with --incremental-history-name, --incremental-basedir and --incremental-lsn. And if it can't find a valid lsn (like, there's no success record with that UUID)? It errors out, periodt. Also it's meant to be used alongside the --incremental option. 💅
  • --incremental-lsn=LSN — when you're doing an incremental backup you can drop the log sequence number (LSN) directly instead of passing --incremental-basedir. For databases created in 5.1 and later, hand over the LSN as a single 64-bit integer.
⚠️ ATTENTION: if you give a wrong LSN value — a user error that Percona XtraBackup literally cannot detect — the backup will be unusable. Be careful! 😭 fr fr, be careful.
  • --innodb[=name] — this option is ignored for MySQL option compatibility. yeah. it just exists. 🙈
  • --innodb-miscellaneous — okay so there's a whole big group of InnoDB options that normally get read from the my.cnf configuration file, so xtrabackup can boot up its embedded InnoDB with the same config as your current server. Normally you do NOT need to set these yourself. These options behave the same in InnoDB and XtraDB:
    • --innodb-adaptive-hash-index
    • --innodb-additional-mem-pool-size
    • --innodb-autoextend-increment
    • --innodb-buffer-pool-size
    • --innodb-buffer-pool-filename
    • --innodb-checksum-algorithm
    • --innodb-checksums
    • --innodb-data-file-path
    • --innodb-data-home-dir
    • --innodb-directories
    • --innodb-doublewrite-file
    • --innodb-doublewrite
    • --innodb-extra-undoslots
    • --innodb-fast-checksum
    • --innodb-file-io-threads
    • --innodb-file-per-table
    • --innodb-flush-log-at-trx-commit
    • --innodb-flush-method
    • --innodb-io-capacity
    • --innodb-lock-wait-timeout
    • --innodb-log-block-size
    • --innodb-log-buffer-size
    • --innodb-log-checksums
    • --innodb-log-files-in-group
    • --innodb-log-file-size
    • --innodb-log-group-home-dir
    • --innodb-max-dirty-pages-pct
    • --innodb-open-files
    • --innodb-page-size
    • --innodb-read-io-threads
    • --innodb-redo-log-encrypt
    • --innodb-undo-directory
    • --innodb-undo-log-encrypt
    • `--innodb-undo-tablespaces``
    • --innodb-use-native-aio
    • --innodb-write-io-threads
  • --keyring-file-data=FILENAME — the path to the keyring file. Pair this one up with --xtrabackup-plugin-dir. 🔑
  • --kill-long-queries-timeout=SECONDS — how many seconds xtrabackup waits, between starting FLUSH TABLES WITH READ LOCK and killing off the queries that are blocking it. Default is 0 seconds, which means xtrabackup won't try to kill anything at all. Oh and to use this option, the xtrabackup user needs the PROCESS and SUPER privileges. Where it's supported, xtrabackup automatically uses Backup Locks as a lightweight alternative to FLUSH TABLES WITH READ LOCK to copy non-InnoDB data, so it doesn't block DML queries that modify InnoDB tables. ✨
  • --kill-long-query-type=all|select — tells you which types of queries should get killed to unblock the global lock. Default is "select".
  • --lock-ddl — issues LOCK TABLES FOR BACKUP if the server supports it (otherwise it uses LOCK INSTANCE FOR BACKUP) at the beginning of the backup, blocking all DDL operations. 🔒
NOTE: Prior to Percona XtraBackup 8.0.22-15.0, using a safe-slave-backup stops the SQL replica thread after the InnoDB tables and before the non-InnoDB tables are backed up. As of Percona XtraBackup 8.0.22-15.0, using a safe-slave-backup option stops the SQL replica thread before copying the InnoDB files.
  • --lock-ddl-per-table — locks DDL for each table before xtrabackup starts copying it, and keeps it locked until the backup is done.
NOTE: As of Percona XtraBackup 8.0.15, the --lock-ddl-per-table option is deprecated. Use the --lock-ddl option instead. 💀
  • --lock-ddl-timeout — if LOCK TABLES FOR BACKUP or LOCK INSTANCE FOR BACKUP doesn't return within the given timeout, abort the backup. no waiting around, bestie. 🙅
  • --log — this option is ignored for MySQL.
  • --log-bin — the base name for the log sequence.
  • --log-bin-index=name — the file that holds the names for binary log files.
  • --log-copy-interval=# — how long the log copying thread waits between checks, in milliseconds (default is 1 second).
  • --login-path — read the given path from the login file.
  • --move-back — moves all the files from a previously made backup out of the backup directory and into their original locations. Since this option removes backup files, it must be used with caution. 👀 careful babes.
  • --no-backup-locks — explicitly disables the --backup-locks option, which is enabled by default.
  • --no-defaults — the default options are only read from the login file.
  • --no-lock — use this to disable the table lock with FLUSH TABLES WITH READ LOCK. Only use it if ALL your tables are InnoDB and you DO NOT CARE about the binary log position of the backup. Lowkey do not use this if there are any DDL statements being executed, or if any updates are happening on non-InnoDB tables (this includes the system MyISAM tables in the mysql database) — otherwise it could lead to an inconsistent backup. Where supported, xtrabackup will automatically use Backup Locks as a lightweight alternative to FLUSH TABLES WITH READ LOCK to copy non-InnoDB data, so it doesn't block DML queries that modify InnoDB tables. And if you're considering using this because your backups are failing to acquire the lock, that could be because incoming replication events are preventing the lock from succeeding. Please try using --safe-slave-backup to momentarily stop the replication replica thread — this may help the backup succeed and then you don't need this option at all. 🫶
  • --no-server-version-check — implemented in Percona XtraBackup 8.0.21.

The --no-server-version-check option disables the server version check.

By default, a check runs that compares the source system version to the Percona XtraBackup version. If the source system version is higher than the XtraBackup version, the backup is aborted with a message.

Adding the option overrides this check and the backup proceeds — but there may be issues with the backup. 😬

See comparison for more information.

  • --no-version-check — this one disables the version check. If you don't pass it, the automatic version check is enabled implicitly whenever xtrabackup runs in --backup mode. To disable the version check, you should pass the --no-version-check option explicitly when you invoke xtrabackup.

When the automatic version check is enabled, xtrabackup does a version check against the server on the backup stage, after creating a server connection. xtrabackup sends the following info to the server:

  • MySQL flavour and version
  • Operating system name
  • Percona Toolkit version
  • Perl version

Each piece of info has a unique identifier. It's an MD5 hash value that Percona Toolkit uses to obtain statistics about how it's used. It's a random UUID — no client information is either collected or stored. 💖

  • --open-files-limit=# — the maximum number of file descriptors to reserve with setrlimit().
  • --parallel=# — how many threads to use to copy multiple data files concurrently when creating a backup. Default value is 1 (i.e., no concurrent transfer). In Percona XtraBackup 2.3.10 and newer, you can use this with the --copy-back option to copy the user data files in parallel (redo logs and system tablespaces get copied in the main thread). ⚡
  • --password=PASSWORD — the password to use when connecting to the database. It accepts a string argument. See mysql --help for details.
  • --plugin-load — list of plugins to load.
  • --port=PORT — accepts a string argument that specifies the port to use when connecting to the database server over TCP/IP. It's passed to the mysql child process without alteration. See mysql --help for details.
  • --prepare — makes xtrabackup perform a recovery on a backup created with --backup, so it's ready to use. See preparing a backup. ✨
  • --print-defaults — prints the program argument list and exits. Has to be given as the first option on the command-line, no cap.
  • --print-param — makes xtrabackup print out parameters that can be used for copying the data files back to their original locations to restore them.
  • --read-buffer-size — sets the datafile read buffer size; the given value is scaled up to page size. Default is 10Mb.
  • --rebuild-indexes — rebuilds indexes in a compact backup. This option only has effect when the --prepare and --rebuild-threads options are provided.
  • --rebuild-threads=# — uses the given number of threads to rebuild indexes in a compact backup. This option only has effect with the --prepare and --rebuild-indexes options.
  • --remove-original — implemented in Percona XtraBackup 2.4.6. When specified, it removes .qp, .xbcrypt and .qp.xbcrypt files after decryption and decompression. 🧹
  • --rocksdb-datadir — RocksDB data directory.
  • --rocksdb-wal-dir — RocksDB WAL directory.
  • --rocksdb-checkpoint-max-age — the checkpoint cannot be older than this number of seconds when the backup completes. ⏱️
  • --rocksdb-checkpoint-max-count — okay so if the checkpoint age requirement still hasn't been met after this many checkpoints, just finish the backup anyway. We're not waiting forever, bestie 💅
  • --rollback-prepared-trx — force-rolls back prepared InnoDB transactions. Bye, unfinished business 😭
  • --rsync — uses the rsync utility to optimize local file transfers. When you pass this, xtrabackup uses rsync to copy all non-InnoDB files instead of spawning a separate cp for every single file — which can be wayyyyy faster for servers with a lot of databases or tables. Cannot be used together with --stream, so pick a lane 🫶
  • --safe-slave-backup — when you pass this, xtrabackup stops the replica SQL thread right before running FLUSH TABLES WITH READ LOCK and waits to start the backup until Slave_open_temp_tables in SHOW STATUS is zero. If there are no open temporary tables, the backup just happens. If there are, the SQL thread gets started and stopped over and over until there aren't any left. The backup fails if Slave_open_temp_tables doesn't hit zero after --safe-slave-backup-timeout seconds. The replication SQL thread gets restarted when the backup finishes. This option exists to deal with replicating temporary tables, and it isn't neccessary with Row-Based-Replication.
  • --safe-slave-backup-timeout=SECONDS — how many seconds --safe-slave-backup should wait for Slave_open_temp_tables to become zero. Defaults to 300 seconds. Tick tock ⏰
  • --secure-auth — refuse a client connecting to the server if it uses the old (pre-4.1.1) protocol. (Enabled by default; use --skip-secure-auth to disable.) Ancient protocol? Not on our watch 🚫
  • --server-id=# — the server instance being backed up.
  • --server-public-key-path — the file path to the server public RSA key in the PEM format.

SEE ALSO:

  • --skip-tables-compatibility-check — see --tables-compatibility-check. That's it, that's the whole thing lol.
  • --slave-info — this one's a glow-up for backing up a replication replica server 💖 It prints the binary log position of the source server, and it also writes the binary log coordinates to the xtrabackup_slave_info file as a CHANGE MASTER command. Wanna set up a brand new replica for this source? Start a replica server on this backup and issue a CHANGE MASTER command with the binary log position saved in the xtrabackup_slave_info file. Slay.
  • --socket — this option takes a string argument that specifies the socket to use when connecting to the local database server with a UNIX domain socket. It gets passed to the mysql child process without alteration. See mysql --help for details.
  • --ssl — enable secure connection. More info can be found in the --ssl MySQL server documentation.
  • --ssl-ca — path of the file which contains the list of trusted SSL CAs. More info can be found in the --ssl-ca MySQL server documentation.
  • --ssl-capath — directory path that contains trusted SSL CA certificates in PEM format. More info can be found in the --ssl-capath MySQL server documentation.
  • --ssl-cert — path of the file which contains the X509 certificate in PEM format. More info can be found in the --ssl-cert MySQL server documentation.
  • --ssl-cipher — list of permitted ciphers to use for connection encryption. More info can be found in the --ssl-cipher MySQL server documentation.
  • --ssl-crl — path of the file that contains certificate revocation lists. More info can be found in the --ssl-crl MySQL server documentation.
  • --ssl-crlpath — path of the directory that contains certificate revocation list files. More info can be found in the --ssl-crlpath MySQL server documentation.
  • --ssl-fips-mode — SSL FIPS mode (applies only for OpenSSL); permitted values are: OFF, ON, STRICT.
  • --ssl-key — path of the file that contains the X509 key in PEM format. More info can be found in the --ssl-key MySQL server documentation.
  • --ssl-mode — security state of the connection to the server. More info can be found in the --ssl-mode MySQL server documentation.
  • --ssl-verify-server-cert — verify the server certificate Common Name value against the host name used when connecting to the server. More info can be found in the --ssl-verify-server-cert MySQL server documentation.
  • --stats — makes xtrabackup scan the specified data files and print out index statistics. Look at those numbers go ✨
  • --stream=FORMAT — stream all backup files to the standard output in the specified format. Currently, this option only supports the xbstream format. That's the only flavor on the menu rn.
  • --strict — if you pass this, xtrabackup fails with an error when invalid parameters are passed. No silent nonsense, we're strict like that.
  • --tables=name — a regular expression against which the full tablename, in databasename.tablename format, is matched. If the name matches, the table is backed up. See partial backups.
  • --tables-compatibility-check — enables the engine compatibility warning. The default value is ON. To disable the engine compatibility warning, use --skip-tables-compatibility-check.
  • --tables-exclude=name — filtering by regexp for table names. Operates the exact same way as --tables, but matched names are excluded from the backup. Note that this option has a higher priority than --tables — it wins the argument, periodt 💅
  • --tables-file=name — a file containing one table name per line, in databasename.tablename format. The backup will be limited to the specified tables.
  • --target-dir=DIRECTORY — this option specifies the destination directory for the backup. If the directory doesn't exist, xtrabackup creates it. If the directory does exist and is empty, xtrabackup will succeed. xtrabackup will not overwrite existing files, however — it will fail with operating system error 17, file exists. 🙈

If this option is a relative path, it's interpreted as being relative to the current working directory from which xtrabackup is executed.

In order to perform a backup, you need READ, WRITE, and EXECUTE permissions at a filesystem level for the directory that you supply as the value of --target-dir.

  • --innodb-temp-tablespaces-dir=DIRECTORY — directory where temp tablespace files live. This path can be absolute.
  • --throttle=# — this option limits the number of chunks copied per second. The chunk size is 10 MB. To limit the bandwidth to 10 MB/s, set the option to 1: --throttle=1.

SEE ALSO:

  • More information about how to throttle a backup → throttling_backups
  • --tls-ciphersuites — TLS v1.3 cipher to use.
  • --tls-version — TLS version to use. Permitted values are: TLSv1, TLSv1.1, TLSv1.2, TLSv1.3.
  • --tmpdir=name — specify the directory that will be used to store temporary files during the backup.
  • --transition-key=name — this option is used to enable processing the backup without accessing the keyring vault server. In this case, xtrabackup derives the AES encryption key from the specified passphrase and uses it to encrypt tablespace keys of the tablespaces being backed up. 🔐

If --transition-key doesn't have any value, xtrabackup will ask for it. The same passphrase should be specified for the --prepare command.

  • --use-memory — this option affects how much memory is allocated for preparing a backup with --prepare, or analyzing statistics with --stats. Its purpose is similar to innodb_buffer_pool_size. It does not do the same thing as the similarly named option in Oracle's InnoDB Hot Backup tool. The default value is 100MB, and if you have enough available memory, 1GB to 2GB is a good recommended value. Multiples are supported providing the unit (e.g. 1MB, 1M, 1GB, 1G).
  • --user=USERNAME — this option specifies the MySQL username used when connecting to the server, if that's not the current user. The option accepts a string argument. See mysql --help for details.
  • -v — see --version.
  • --version — this option prints the xtrabackup version and exits. Very "who am I, where am I" of it 🌸
  • --xtrabackup-plugin-dir=DIRNAME — the absolute path to the directory that contains the keyring plugin.

SEE ALSO:

👑 AUTHOR

Percona LLC and/or its affiliates

2009-2022, Percona LLC and/or its affiliates

okay bestie that's a wrap on the whole page!! 🎀 you just read the entire XTRABACKUP(1) manual and honestly? iconic. this was version 8.0, dated Jul 06, 2026, and now you know xtrabackup like the back of your hand 💅✨ go forth and back up responsibly, mwah 🦋💖

📦 where this page comes from