1XTRABACKUP(1) Percona XtraBackup XTRABACKUP(1)
2
6 xtrabackup - Percona XtraBackup 8.0 Documentation
7 The xtrabackup binary is a compiled C program that is linked with the
9 InnoDB libraries and the standard MySQL client libraries.
10 xtrabackup enables point-in-time backups of InnoDB / XtraDB tables to‐
12 gether with the schema definitions, MyISAM tables, and other portions
13 of the server.
14 The InnoDB libraries provide the functionality to apply a log to data
16 files. The MySQL client libraries are used to parse command-line op‐
17 tions and configuration file.
18 The tool runs in either --backup or --prepare mode, corresponding to
20 the two main functions it performs. There are several variations on
21 these functions to accomplish different tasks, and there are two less
22 commonly used modes, --stats and --print-param.
23
25 Incremental Backups
26 xtrabackup supports incremental backups. It copies only the data that
27 has changed since the last full backup. You can perform many incremen‐
28 tal backups between each full backup, so you can set up a backup
29 process such as a full backup once a week and an incremental backup ev‐
30 ery day, or full backups every day and incremental backups every hour.
31 Incremental backups work because each InnoDB page (usually 16kb in
33 size) contains a log sequence number, or LSN. The LSN is the system
34 version number for the entire database. Each page's LSN shows how re‐
35 cently it was changed. An incremental backup copies each page whose LSN
36 is newer than the previous incremental or full backup's LSN. There are
37 two algorithms in use to find the set of such pages to be copied. The
38 first one, available with all the server types and versions, is to
39 check the page LSN directly by reading all the data pages. The second
40 one, available with Percona Server for MySQL, is to enable the changed
41 page tracking feature on the server, which will note the pages as they
42 are being changed. This information will be then written out in a com‐
43 pact separate so-called bitmap file. The xtrabackup binary will use
44 that file to read only the data pages it needs for the incremental
45 backup, potentially saving many read requests. The latter algorithm is
46 enabled by default if the xtrabackup binary finds the bitmap file. It
47 is possible to specify --incremental-force-scan to read all the pages
48 even if the bitmap data is available.
49 Incremental backups do not actually compare the data files to the pre‐
51 vious backup's data files. In fact, you can use --incremental-lsn to
52 perform an incremental backup without even having the previous backup,
53 if you know its LSN. Incremental backups simply read the pages and com‐
54 pare their LSN to the last backup's LSN. You still need a full backup
55 to recover the incremental changes, however; without a full backup to
56 act as a base, the incremental backups are useless.
57 Creating an Incremental Backup
59 To make an incremental backup, begin with a full backup as usual. The
60 xtrabackup binary writes a file called xtrabackup_checkpoints into the
61 backup's target directory. This file contains a line showing the
62 to_lsn, which is the database's LSN at the end of the backup. Create
63 the full backup with a command such as the following:
64 $ xtrabackup --backup --target-dir=/data/backups/base --datadir=/var/lib/mysql/
66 If you look at the xtrabackup_checkpoints file, you should see contents
68 similar to the following:
69 backup_type = full-backuped
71 from_lsn = 0
72 to_lsn = 1291135
73 Now that you have a full backup, you can make an incremental backup
75 based on it. Use a command such as the following:
76 $ xtrabackup --backup --target-dir=/data/backups/inc1 \
78 --incremental-basedir=/data/backups/base --datadir=/var/lib/mysql/
79 The /data/backups/inc1/ directory should now contain delta files, such
81 as ibdata1.delta and test/table1.ibd.delta. These represent the changes
82 since the LSN 1291135. If you examine the xtrabackup_checkpoints file
83 in this directory, you should see something similar to the following:
84 backup_type = incremental
86 from_lsn = 1291135
87 to_lsn = 1291340
88 The meaning should be self-evident. It's now possible to use this di‐
90 rectory as the base for yet another incremental backup:
91 $ xtrabackup --backup --target-dir=/data/backups/inc2 \
93 --incremental-basedir=/data/backups/inc1 --datadir=/var/lib/mysql/
94 Preparing the Incremental Backups
96 The --prepare step for incremental backups is not the same as for nor‐
97 mal backups. In normal backups, two types of operations are performed
98 to make the database consistent: committed transactions are replayed
99 from the log file against the data files, and uncommitted transactions
100 are rolled back. You must skip the rollback of uncommitted transactions
101 when preparing a backup, because transactions that were uncommitted at
102 the time of your backup may be in progress, and it is likely that they
103 will be committed in the next incremental backup. You should use the
104 --apply-log-only option to prevent the rollback phase.
105 NOTE:
107 If you do not use the --apply-log-only option to prevent the roll‐
108 back phase, then your incremental backups will be useless. After
109 transactions have been rolled back, further incremental backups can‐
110 not be applied.
111 Beginning with the full backup you created, you can prepare it, and
113 then apply the incremental differences to it. Recall that you have the
114 following backups:
115 /data/backups/base
117 /data/backups/inc1
118 /data/backups/inc2
119 To prepare the base backup, you need to run --prepare as usual, but
121 prevent the rollback phase:
122 xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base
124 The output should end with some text such as the following:
126 101107 20:49:43 InnoDB: Shutdown completed; log sequence number 1291135
128 The log sequence number should match the to_lsn of the base backup,
130 which you saw previously.
131 This backup is actually safe to restore as-is now, even though the
133 rollback phase has been skipped. If you restore it and start MySQL,
134 InnoDB will detect that the rollback phase was not performed, and it
135 will do that in the background, as it usually does for a crash recovery
136 upon start. It will notify you that the database was not shut down nor‐
137 mally.
138 To apply the first incremental backup to the full backup, you should
140 use the following command:
141 xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base \
143 --incremental-dir=/data/backups/inc1
144 This applies the delta files to the files in /data/backups/base, which
146 rolls them forward in time to the time of the incremental backup. It
147 then applies the redo log as usual to the result. The final data is in
148 /data/backups/base, not in the incremental directory. You should see
149 some output such as the following:
150 incremental backup from 1291135 is enabled.
152 xtrabackup: cd to /data/backups/base/
153 xtrabackup: This target seems to be already prepared.
154 xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(1291340)
155 Applying /data/backups/inc1/ibdata1.delta ...
156 Applying /data/backups/inc1/test/table1.ibd.delta ...
157 .... snip
158 101107 20:56:30 InnoDB: Shutdown completed; log sequence number 1291340
159 Again, the LSN should match what you saw from your earlier inspection
161 of the first incremental backup. If you restore the files from
162 /data/backups/base, you should see the state of the database as of the
163 first incremental backup.
164 Preparing the second incremental backup is a similar process: apply the
166 deltas to the (modified) base backup, and you will roll its data for‐
167 ward in time to the point of the second incremental backup:
168 xtrabackup --prepare --target-dir=/data/backups/base \
170 --incremental-dir=/data/backups/inc2
171 NOTE:
173 --apply-log-only should be used when merging all incrementals except
174 the last one. That's why the previous line doesn't contain the --ap‐
175 ply-log-only option. Even if the --apply-log-only was used on the
176 last step, backup would still be consistent but in that case server
177 would perform the rollback phase.
178 If you wish to avoid the notice that InnoDB was not shut down normally,
180 when you applied the desired deltas to the base backup, you can run
181 --prepare again without disabling the rollback phase.
182 Restoring Incremental Backups
184 After preparing the incremental backups, the base directory contains
185 the same data as the full backup. To restoring this backup, you can use
186 this command: xtrabackup --copy-back --target-dir=BASE-DIR
187 You may have to change the ownership as detailed on restoring_a_backup.
189 Incremental Streaming Backups Using xbstream
191 Incremental streaming backups can be performed with the xbstream
192 streaming option. Currently backups are packed in custom xbstream for‐
193 mat. With this feature, you need to take a BASE backup as well. Making
194 a base backup.INDENT 0.0
195 $ xtrabackup --backup --target-dir=/data/backups
197Taking a local backup.INDENT 0.0
198 $ xtrabackup --backup --incremental-lsn=LSN-number --stream=xbstream --target-dir=./ > incremental.xbstream
200Unpacking the backup.INDENT 0.0
201 $ xbstream -x < incremental.xbstream
203Taking a local backup and streaming it to the remote server and unpacking
204it.INDENT 0.0
205 $ xtrabackup --backup --incremental-lsn=LSN-number --stream=xbstream --target-dir=./
207 $ ssh user@hostname " cat - | xbstream -x -C > /backup-dir/"
208 Partial Backups
210 xtrabackup supports taking partial backups when the innodb_file_per_ta‐
211 ble option is enabled. There are three ways to create partial backups:
212 1. matching the tables names with a regular expression
214 2. providing a list of table names in a file
216 3. providing a list of databases
218 WARNING:
220 Do not copy back the prepared backup.
221 Restoring partial backups should be done by importing the tables,
223 not by using the --copy-back option. It is not recommended to run
224 incremental backups after running a partial backup.
225 Although there are some scenarios where restoring can be done by
227 copying back the files, this may lead to database inconsistencies in
228 many cases and it is not a recommended way to do it.
229 For the purposes of this manual page, we will assume that there is a
231 database named test which contains tables named t1 and t2.
232 WARNING:
234 If any of the matched or listed tables is deleted during the backup,
235 xtrabackup will fail.
236 Creating Partial Backups
238 There are two ways of specifying which part of the whole data will be
239 backed up: enumerating the tables in a file (--tables-file) or provid‐
240 ing a list of databases (--databases).
241 The --tables Option
243 The first method involves the xtrabackup --tables option. The option's
244 value is a regular expression that is matched against the fully quali‐
245 fied tablename, including the database name, in the form database‐
246 name.tablename.
247 To back up only tables in the test database, you can use the following
249 command:
250 $ xtrabackup --backup --datadir=/var/lib/mysql --target-dir=/data/backups/ \
252 --tables="^test[.].*"
253 To back up only the table test.t1, you can use the following command:
255 $ xtrabackup --backup --datadir=/var/lib/mysql --target-dir=/data/backups/ \
257 --tables="^test[.]t1"
258 The --tables-file Option
260 The --tables-file option specifies a file that can contain multiple ta‐
261 ble names, one table name per line in the file. Only the tables named
262 in the file will be backed up. Names are matched exactly, case-sensi‐
263 tive, with no pattern or regular expression matching. The table names
264 must be fully qualified, in databasename.tablename format.
265 $ echo "mydatabase.mytable" > /tmp/tables.txt
267 $ xtrabackup --backup --tables-file=/tmp/tables.txt
268 The --databases and --databases-file options
270 xtrabackup --databases accepts a space-separated list of the databases
271 and tables to backup in the format databasename[.tablename]. In addi‐
272 tion to this list make sure to specify the mysql, sys, and perfor‐
273 mance_schema databases. These databases are required when restoring the
274 databases using xtrabackup --copy-back.
275 NOTE:
277 Tables processed during the --prepare step may also be added to the
278 backup even if they are not explicitly listed by the parameter if
279 they were created after the backup started.
280 $ xtrabackup --databases='mysql sys performance_schema ...'
282 xtrabackup --databases-file specifies a file that can contain multiple
284 databases and tables in the databasename[.tablename] form, one element
285 name per line in the file. Names are matched exactly, case-sensitive,
286 with no pattern or regular expression matching.
287 NOTE:
289 Tables processed during the --prepare step may also be added to the
290 backup even if they are not explicitly listed by the parameter if
291 they were created after the backup started.
292 Preparing Partial Backups
294 The procedure is analogous to restoring individual tables : apply the
295 logs and use the --export option:
296 $ xtrabackup --prepare --export --target-dir=/path/to/partial/backup
298 When you use the xtrabackup --prepare option on a partial backup, you
300 will see warnings about tables that don't exist. This is because these
301 tables exist in the data dictionary inside InnoDB, but the correspond‐
302 ing .ibd files don't exist. They were not copied into the backup direc‐
303 tory. These tables will be removed from the data dictionary, and when
304 you restore the backup and start InnoDB, they will no longer exist and
305 will not cause any errors or warnings to be printed to the log file.
306 An example of the error message you will see during the prepare phase
308 follows.
309 InnoDB: Reading tablespace information from the .ibd files...
311 101107 22:31:30 InnoDB: Error: table 'test1/t'
312 InnoDB: in InnoDB data dictionary has tablespace id 6,
313 InnoDB: but tablespace with that id or name does not exist. It will be removed from data dictionary.
314 Restoring Partial Backups
316 Restoring should be done by restoring individual tables in the partial
317 backup to the server.
318 It can also be done by copying back the prepared backup to a "clean"
320 datadir (in that case, make sure to include the mysql database). System
321 database can be created with:
322 $ sudo mysql_install_db --user=mysql
324
326 Analyzing Table Statistics
327 The xtrabackup binary can analyze InnoDB data files in read-only mode
328 to give statistics about them. To do this, you should use the --stats
329 option. You can combine this with the --tables option to limit the
330 files to examine. It also uses the --use-memory option.
331 You can perform the analysis on a running server, with some chance of
333 errors due to the data being changed during analysis. Or, you can ana‐
334 lyze a backup copy of the database. Either way, to use the statistics
335 feature, you need a clean copy of the database including correctly
336 sized log files, so you need to execute with --prepare twice to use
337 this functionality on a backup.
338 The result of running on a backup might look like the following:
340 <INDEX STATISTICS>
342 table: test/table1, index: PRIMARY, space id: 12, root page 3
343 estimated statistics in dictionary:
344 key vals: 25265338, leaf pages 497839, size pages 498304
345 real statistics:
346 level 2 pages: pages=1, data=5395 bytes, data/pages=32%
347 level 1 pages: pages=415, data=6471907 bytes, data/pages=95%
348 leaf pages: recs=25958413, pages=497839, data=7492026403 bytes, data/pages=91%
349 This can be interpreted as follows:
351 • The first line simply shows the table and index name and its internal
353 identifiers. If you see an index named GEN_CLUST_INDEX, that is the
354 table's clustered index, automatically created because you did not
355 explicitly create a PRIMARY KEY.
356 • The estimated statistics in dictionary information is similar to the
358 data that's gathered through ANALYZE TABLE inside of InnoDB to be
359 stored as estimated cardinality statistics and passed to the query
360 optimizer.
361 • The real statistics information is the result of scanning the data
363 pages and computing exact information about the index.
364 • The level <X> pages: output means that the line shows information
366 about pages at that level in the index tree. The larger <X> is, the
367 farther it is from the leaf pages, which are level 0. The first line
368 is the root page.
369 • The leaf pages output shows the leaf pages, of course. This is where
371 the table's data is stored.
372 • The external pages: output (not shown) shows large external pages
374 that hold values too long to fit in the row itself, such as long BLOB
375 and TEXT values.
376 • The recs is the real number of records (rows) in leaf pages.
378 • The pages is the page count.
380 • The data is the total size of the data in the pages, in bytes.
382 • The data/pages is calculated as (data / (pages * PAGE_SIZE)) * 100%.
384 It will never reach 100% because of space reserved for page headers
385 and footers.
386 A more detailed example is posted as a MySQL Performance Blog post.
388 Script to Format Output
390 The following script can be used to summarize and tabulate the output
391 of the statistics information:
392 tabulate-xtrabackup-stats.pl
394 #!/usr/bin/env perl
396 use strict;
397 use warnings FATAL => 'all';
398 my $script_version = "0.1";
399 my $PG_SIZE = 16_384; # InnoDB defaults to 16k pages, change if needed.
401 my ($cur_idx, $cur_tbl);
402 my (%idx_stats, %tbl_stats);
403 my ($max_tbl_len, $max_idx_len) = (0, 0);
404 while ( my $line = <> ) {
405 if ( my ($t, $i) = $line =~ m/table: (.*), index: (.*), space id:/ ) {
406 $t =~ s!/!.!;
407 $cur_tbl = $t;
408 $cur_idx = $i;
409 if ( length($i) > $max_idx_len ) {
410 $max_idx_len = length($i);
411 }
412 if ( length($t) > $max_tbl_len ) {
413 $max_tbl_len = length($t);
414 }
415 }
416 elsif ( my ($kv, $lp, $sp) = $line =~ m/key vals: (\d+), \D*(\d+), \D*(\d+)/ ) {
417 @{$idx_stats{$cur_tbl}->{$cur_idx}}{qw(est_kv est_lp est_sp)} = ($kv, $lp, $sp);
418 $tbl_stats{$cur_tbl}->{est_kv} += $kv;
419 $tbl_stats{$cur_tbl}->{est_lp} += $lp;
420 $tbl_stats{$cur_tbl}->{est_sp} += $sp;
421 }
422 elsif ( my ($l, $pages, $bytes) = $line =~ m/(?:level (\d+)|leaf) pages:.*pages=(\d+), data=(\d+) bytes/ ) {
423 $l ||= 0;
424 $idx_stats{$cur_tbl}->{$cur_idx}->{real_pages} += $pages;
425 $idx_stats{$cur_tbl}->{$cur_idx}->{real_bytes} += $bytes;
426 $tbl_stats{$cur_tbl}->{real_pages} += $pages;
427 $tbl_stats{$cur_tbl}->{real_bytes} += $bytes;
428 }
429 }
430 my $hdr_fmt = "%${max_tbl_len}s %${max_idx_len}s %9s %10s %10s\n";
432 my @headers = qw(TABLE INDEX TOT_PAGES FREE_PAGES PCT_FULL);
433 printf $hdr_fmt, @headers;
434 my $row_fmt = "%${max_tbl_len}s %${max_idx_len}s %9d %10d %9.1f%%\n";
436 foreach my $t ( sort keys %tbl_stats ) {
437 my $tbl = $tbl_stats{$t};
438 printf $row_fmt, $t, "", $tbl->{est_sp}, $tbl->{est_sp} - $tbl->{real_pages},
439 $tbl->{real_bytes} / ($tbl->{real_pages} * $PG_SIZE) * 100;
440 foreach my $i ( sort keys %{$idx_stats{$t}} ) {
441 my $idx = $idx_stats{$t}->{$i};
442 printf $row_fmt, $t, $i, $idx->{est_sp}, $idx->{est_sp} - $idx->{real_pages},
443 $idx->{real_bytes} / ($idx->{real_pages} * $PG_SIZE) * 100;
444 }
445 }
446 Sample Script Output
447 The output of the above Perl script, when run against the sample shown
449 in the previously mentioned blog post, will appear as follows:
450 TABLE INDEX TOT_PAGES FREE_PAGES PCT_FULL
452 art.link_out104 832383 38561 86.8%
453 art.link_out104 PRIMARY 498304 49 91.9%
454 art.link_out104 domain_id 49600 6230 76.9%
455 art.link_out104 domain_id_2 26495 3339 89.1%
456 art.link_out104 from_message_id 28160 142 96.3%
457 art.link_out104 from_site_id 38848 4874 79.4%
458 art.link_out104 revert_domain 153984 19276 71.4%
459 art.link_out104 site_message 36992 4651 83.4%
460 The columns are the table and index, followed by the total number of
462 pages in that index, the number of pages not actually occupied by data,
463 and the number of bytes of real data as a percentage of the total size
464 of the pages of real data. The first line in the above output, in which
465 the INDEX column is empty, is a summary of the entire table.
466 Working with Binary Logs
468 The xtrabackup binary integrates with information that InnoDB stores in
469 its transaction log about the corresponding binary log position for
470 committed transactions. This enables it to print out the binary log po‐
471 sition to which a backup corresponds, so you can use it to set up new
472 replication replicas or perform point-in-time recovery.
473 Finding the Binary Log Position
475 You can find the binary log position corresponding to a backup once the
476 backup has been prepared. This can be done by either running the xtra‐
477 backup with the --prepare or --apply-log-only option. If your backup is
478 from a server with binary logging enabled, xtrabackup will create a
479 file named xtrabackup_binlog_info in the target directory. This file
480 contains the binary log file name and position of the exact point in
481 the binary log to which the prepared backup corresponds.
482 You will also see output similar to the following during the prepare
484 stage:
485 InnoDB: Last MySQL binlog file position 0 3252710, file name ./mysql-bin.000001
487 ... snip ...
488 [notice (again)]
489 If you use binary log and don't use any hack of group commit,
490 the binary log position seems to be:
491 InnoDB: Last MySQL binlog file position 0 3252710, file name ./mysql-bin.000001
492 This output can also be found in the xtrabackup_binlog_pos_innodb file,
494 but it is only correct when no other than XtraDB or InnoDB are used as
495 storage engines.
496 If other storage engines are used (i.e. MyISAM), you should use the
498 xtrabackup_binlog_info file to retrieve the position.
499 The message about hacking group commit refers to an early implementa‐
501 tion of emulated group commit in Percona Server for MySQL.
502 Point-In-Time Recovery
504 To perform a point-in-time recovery from an xtrabackup backup, you
505 should prepare and restore the backup, and then replay binary logs from
506 the point shown in the xtrabackup_binlog_info file.
507 A more detailed procedure is found here.
509 Setting Up a New Replication Replica
511 To set up a new replica, you should prepare the backup, and restore it
512 to the data directory of your new replication replica. If you are using
513 version 8.0.22 or earlier, in your CHANGE MASTER TO command, use the
514 binary log filename and position shown in the xtrabackup_binlog_info
515 file to start replication.
516 If you are using 8.0.23 or later, use the CHANGE_REPLICATION_SOURCE_TO
518 and the appropriate options. CHANGE_MASTER_TO is deprecated.
519 A more detailed procedure is found in ../howtos/setting_up_replica‐
521 tion.
522 Restoring Individual Tables
524 With Percona XtraBackup, you can export individual tables from any Inn‐
525 oDB database, and import them into Percona Server for MySQL with XtraDB
526 or MySQL 8.0. (The source doesn't have to be XtraDB or MySQL 8.0, but
527 the destination does.) This only works on individual .ibd files, and
528 cannot export a table that is not contained in its own .ibd file.
529 Let's see how to export and import the following table:
531 CREATE TABLE export_test (
533 a int(11) DEFAULT NULL
534 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
535 Exporting the Table
537 This table should be created in innodb_file_per_table mode, so after
538 taking a backup as usual with --backup, the .ibd file should exist in
539 the target directory:
540 $ find /data/backups/mysql/ -name export_test.*
542 /data/backups/mysql/test/export_test.ibd
543 when you prepare the backup, add the extra parameter --export to the
545 command. Here is an example:
546 $ xtrabackup --prepare --export --target-dir=/data/backups/mysql/
548 NOTE:
550 If you're trying to restore encrypted InnoDB tablespace table you'll
551 need to specify the keyring file as well:
552 $ xtrabackup --prepare --export --target-dir=/tmp/table \
554 --keyring-file-data=/var/lib/mysql-keyring/keyring
555 Now you should see a .exp file in the target directory:
557 $ find /data/backups/mysql/ -name export_test.*
559 /data/backups/mysql/test/export_test.exp
560 /data/backups/mysql/test/export_test.ibd
561 /data/backups/mysql/test/export_test.cfg
562 These three files are all you need to import the table into a server
564 running Percona Server for MySQL with XtraDB or MySQL 8.0. In case
565 server is using InnoDB Tablespace Encryption additional .cfp file be
566 listed for encrypted tables.
567 NOTE:
569 MySQL uses .cfg file which contains InnoDB dictionary dump in spe‐
570 cial format. This format is different from the .exp` one which is
571 used in XtraDB for the same purpose. Strictly speaking, a .cfg` file
572 is not required to import a tablespace to MySQL 8.0 or Percona
573 Server for MySQL 8.0. A tablespace will be imported successfully
574 even if it is from another server, but InnoDB will do schema valida‐
575 tion if the corresponding .cfg file is present in the same direc‐
576 tory.
577 Importing the Table
579 On the destination server running Percona Server for MySQL with XtraDB
580 and innodb_import_table_from_xtrabackup option enabled, or MySQL 8.0,
581 create a table with the same structure, and then perform the following
582 steps:
583 1. Run the ALTER TABLE test.export_test DISCARD TABLESPACE; command. If
585 you see this error then you must enable innodb_file_per_table and
586 create the table again.
587 Error
589 ERROR 1030 (HY000): Got error -1 from storage engine
591 2. Copy the exported files to the test/ subdirectory of the destination
593 server's data directory
594 3. Run ALTER TABLE test.export_test IMPORT TABLESPACE;
596 The table should now be imported, and you should be able to SELECT from
598 it and see the imported data.
599 LRU dump backup
601 Percona XtraBackup includes a saved buffer pool dump into a backup to
602 enable reducing the warm up time. It restores the buffer pool state
603 from ib_buffer_pool file after restart. Percona XtraBackup discovers
604 ib_buffer_pool and backs it up automatically. [image]
605 If the buffer restore option is enabled in my.cnf buffer pool will be
607 in the warm state after backup is restored.
608 SEE ALSO:
610 MySQL Documentation: Saving and Restoring the Buffer Pool State
612 https://dev.mysql.com/doc/refman/8.0/en/innodb-preload-buffer-pool.html
613 Streaming Backups
615 Streaming mode, supported by Percona XtraBackup, sends backup to STDOUT
616 in the xbstream format instead of copying files to the backup direc‐
617 tory.
618 This allows you to use other programs to filter the output of the
620 backup, providing greater flexibility for storage of the backup. For
621 example, compression is achieved by piping the output to a compression
622 utility. One of the benefits of streaming backups and using Unix pipes
623 is that the backups can be automatically encrypted.
624 To use the streaming feature, you must use the --stream, providing the
626 format of the stream (xbstream ) and where to store the temporary
627 files:
628 $ xtrabackup --stream=xbstream --target-dir=/tmp
630 xtrabackup uses xbstream to stream all of the data files to STDOUT, in
632 a special xbstream format. After it finishes streaming all of the data
633 files to STDOUT, it stops xtrabackup and streams the saved log file
634 too.
635 SEE ALSO:
637 More information about xbstream
639 xbstream_binary
640 When compression is enabled, xtrabackup compresses all output data, ex‐
642 cept the meta and non-InnoDB files which are not compressed, using the
643 specified compression algorithm. The only currently supported algorithm
644 is quicklz. The resulting files have the qpress archive format, i.e.
645 every *.qp file produced by xtrabackup is essentially a one-file qpress
646 archive and can be extracted and uncompressed by the qpress file
647 archiver which is available from Percona Software repositories.
648 Using xbstream as a stream option, backups can be copied and compressed
650 in parallel which can significantly speed up the backup process. In
651 case backups were both compressed and encrypted, they'll need to de‐
652 crypted first in order to be uncompressed.
653┌───────────────────────────┬────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
655│Task │ Command │
656└───────────────────────────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
657│Stream the backup into an │ xtrabackup --backup │
662│archive named backup.xb‐ │ --stream=xbstream --tar‐ │
663│stream │ get-dir=./ > backup.xb‐ │
664│ │ stream │
665├───────────────────────────┼────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
666│Stream the backup into a │ xtrabackup --backup │
667│compressed archive named │ --stream=xbstream --com‐ │
668│backup.xbstream │ press --target-dir=./ > │
669│ │ backup.xbstream │
670├───────────────────────────┼────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
671│Encrypt the backup │ $ xtrabackup --backup │
672│ │ --stream=xbstream ./ > │
673│ │ backup.xbstream gzip - | │
674│ │ openssl des3 -salt -k │
675│ │ "password" > backup.xb‐ │
676│ │ stream.gz.des3 │
677├───────────────────────────┼────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
678│Unpack the backup to the │ xbstream -x < backup.xb‐ │
679│current directory │ stream │
680├───────────────────────────┼────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
681│Send the backup compressed │ xtrabackup --backup --com‐ │
682│directly to another host │ press --stream=xbstream │
683│and unpack it │ --target-dir=./ | ssh │
684│ │ user@otherhost "xbstream │
685│ │ -x" │
686├───────────────────────────┼────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
687│Send the backup to another │ On the destination host: │
688│server using netcat. │ │
689│ │ $ nc -l 9999 | cat - > /data/backups/backup.xbstream │
690│ │ │
691│ │ On the source host: │
692│ │ │
693│ │ $ xtrabackup --backup --stream=xbstream ./ | nc desthost 9999 │
694├───────────────────────────┼────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
695│Send the backup to another │ $ ssh user@desthost "( nc -l 9999 > /data/backups/backup.xbstream & )" │
696│server using a one-liner: │ && xtrabackup --backup --stream=xbstream ./ | nc desthost 9999 │
697├───────────────────────────┼────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
698│Throttle the throughput to │ $ xtrabackup --backup --stream=xbstream ./ | pv -q -L10m ssh │
699│10MB/sec using the pipe │ user@desthost "cat - > /data/backups/backup.xbstream" │
700│viewer tool [1] │ │
701├───────────────────────────┼────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
702│Checksumming the backup │ On the destination host: │
703│during the streaming: │ │
704│ │ $ nc -l 9999 | tee >(sha1sum > destination_checksum) > /data/backups/backup.xbstream │
705│ │ │
706│ │ On the source host: │
707│ │ │
708│ │ $ xtrabackup --backup --stream=xbstream ./ | tee >(sha1sum > source_checksum) | nc desthost 9999 │
709│ │ │
710│ │ Compare the checksums on the source host: │
711│ │ │
712│ │ $ cat source_checksum │
713│ │ 65e4f916a49c1f216e0887ce54cf59bf3934dbad - │
714│ │ │
715│ │ Compare the checksums on the destination host: │
716│ │ │
717│ │ $ cat destination_checksum │
718│ │ 65e4f916a49c1f216e0887ce54cf59bf3934dbad - │
719├───────────────────────────┼────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
720│Parallel compression with │ xtrabackup --backup --compress --compress-threads=8 --stream=xbstream --parallel=4 --target-dir=./ > │
721│parallel copying backup │ backup.xbstream │
722└───────────────────────────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
723
725 [1] Install from the official site or from the distribution package
726 (apt install pv)
727 Note that the streamed backup will need to be prepared before
729 restoration. Streaming mode does not prepare the backup.
730 Encrypting Backups
732 Percona XtraBackup supports encrypting and decrypting local and stream‐
733 ing backups with xbstream option adding another layer of protection.
734 The encryption is implemented using the libgcrypt library from GnuPG.
735 Creating Encrypted Backups
737 To make an encrypted backup the following options need to be specified
738 (options --encrypt-key and --encrypt-key-file are mutually exclusive,
739 i.e. just one of them needs to be provided):
740 • --encrypt
742 • :option:` --encrypt-key`
744 • :option:` --encrypt-key-file`
746 Both the --encrypt-key option and --encrypt-key-file option can be used
748 to specify the encryption key. An encryption key can be generated with
749 a command like openssl rand -base64 24
750 Example output of that command should look like this:
752 GCHFLrDFVx6UAsRb88uLVbAVWbK+Yzfs
754 This value then can be used as the encryption key
756 The --encrypt-key Option
758 Example of the xtrabackup command using the --encrypt-key should look
759 like this:
760 $ xtrabackup --backup --encrypt=AES256 --encrypt-key="GCHFLrDFVx6UAsRb88uLVbAVWbK+Yzfs" --target-dir=/data/backup
762 The --encrypt-key-file Option
764 Use the --encrypt-key-file option as follows:
765 $ xtrabackup --backup --encrypt=AES256 --encrypt-key-file=/data/backups/keyfile --target-dir=/data/backup
767 NOTE:
769 Depending on the text editor that you use to make the KEYFILE, the
770 editor can automatically insert the CRLF (end of line) character.
771 This will cause the key size to grow and thus making it invalid. The
772 suggested way to create the file is by using the command line: echo
773 -n "GCHFLrDFVx6UAsRb88uLVbAVWbK+Yzfs" > /data/backups/keyfile.
774 Optimizing the encryption process
776 Two new options are available for encrypted backups that can be used to
777 speed up the encryption process. These are --encrypt-threads and --en‐
778 crypt-chunk-size. By using the --encrypt-threads option multiple
779 threads can be specified to be used for encryption in parallel. Option
780 --encrypt-chunk-size can be used to specify the size (in bytes) of the
781 working encryption buffer for each encryption thread (default is 64K).
782 Decrypting Encrypted Backups
784 Backups can be decrypted with xbcrypt. The following one-liner can be
785 used to encrypt the whole folder:
786 $ 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
788 Percona XtraBackup --decrypt option has been implemented that can be
790 used to decrypt the backups:
791 $ xtrabackup --decrypt=AES256 --encrypt-key="GCHFLrDFVx6UAsRb88uLVbAVWbK+Yzfs" --target-dir=/data/backup/
793 Percona XtraBackup doesn't automatically remove the encrypted files. In
795 order to clean up the backup directory users should remove the
796 *.xbcrypt files.
797 NOTE:
799 --parallel can be used with --decrypt option to decrypt multiple
800 files simultaneously.
801 When the files are decrypted, the backup can be prepared.
803 Preparing Encrypted Backups
805 After the backups have been decrypted, they can be prepared in the same
806 way as the standard full backups with the --prepare option:
807 $ xtrabackup --prepare --target-dir=/data/backup/
809 Restoring Encrypted Backups
811 xtrabackup offers the --copy-back option to restore a backup to the
812 server's datadir:
813 $ xtrabackup --copy-back --target-dir=/data/backup/
815 It will copy all the data-related files back to the server's datadir,
817 determined by the server's my.cnf configuration file. You should check
818 the last line of the output for a success message:
819 150318 11:08:13 xtrabackup: completed OK!
821 SEE ALSO:
823 GnuPG Documentation: libgcrypt library
825 http://www.gnupg.org/documentation/manuals/gcrypt/
826 Handling FLUSH TABLES WITH READ LOCK
828 When making backups, FLUSH TABLES WITH READ LOCK is used before the
829 non-InnoDB files are backed up to ensure that the backup is consistent.
830 FLUSH TABLES WITH READ LOCK can be run even though there may be a run‐
831 ning query that has been executing for hours.
832 In this case, everything is locked in the Waiting for table flush or
834 Waiting for master to send event state. Killing the FLUSH TABLES WITH
835 READ LOCK does not correct this problem. The only way to get the server
836 operating normally again is to kill off the long running queries that
837 blocked it to begin with. This means that if there are long running
838 queries FLUSH TABLES WITH READ LOCK can get stuck, leaving server in
839 read-only mode until waiting for these queries to complete.
840 NOTE:
842 All described in this section has no effect when backup locks are
843 used. Percona XtraBackup will use Backup locks where available as a
844 lightweight alternative to FLUSH TABLES WITH READ LOCK. This feature
845 is available in Percona Server for MySQL 5.6+. Percona XtraBackup
846 uses this automatically to copy non-InnoDB data to avoid blocking
847 DML queries that modify InnoDB tables.
848 In order to prevent this from happening two things have been imple‐
850 mented:
851 • xtrabackup can wait for a good moment to issue the global lock.
853 • xtrabackup can kill all or only SELECT queries which are preventing
855 the global lock from being acquired
856 Waiting for queries to finish
858 Good moment to issue a global lock is the moment when there are no long
859 queries running. But waiting for a good moment to issue the global lock
860 for extended period of time isn't always good approach, as it can ex‐
861 tend the time needed for backup to take place. To prevent xtrabackup
862 from waiting to issue FLUSH TABLES WITH READ LOCK for too long, new op‐
863 tion has been implemented: --ftwrl-wait-timeout option can be used to
864 limit the waiting time. If the good moment to issue the lock did not
865 happen during this time, xtrabackup will give up and exit with an error
866 message and backup will not be taken. Zero value for this option turns
867 off the feature (which is default).
868 Another possibility is to specify the type of query to wait on. In this
870 case --ftwrl-wait-query-type. Possible values are all and update. When
871 all is used xtrabackup will wait for all long running queries (execu‐
872 tion time longer than allowed by --ftwrl-wait-threshold) to finish be‐
873 fore running the FLUSH TABLES WITH READ LOCK. When update is used xtra‐
874 backup will wait on UPDATE/ALTER/REPLACE/INSERT queries to finish.
875 Although the time needed for a specific query to complete is hard to
877 predict, we can assume that the queries that have been running for a
878 long time are not likely to finish soon. The queries which are running
879 for a short time are likely to finish shortly. xtrabackup can use the
880 value of --ftwrl-wait-threshold option to specify which query is long
881 running and will likely block global lock for a while. In order to use
882 this option xtrabackup user should have PROCESS and SUPER privileges.
883 Killing the blocking queries
885 The second option is to kill all the queries which prevent from acquir‐
886 ing the global lock. In this case, all queries which run longer than
887 FLUSH TABLES WITH READ LOCK are potential blockers. Although all
888 queries can be killed, additional time can be specified for the short
889 running queries to finish using the --kill-long-queries-timeout option.
890 This option specifies the time for queries to complete, after the value
891 is reached, all the running queries will be killed. The default value
892 is zero, which turns this feature off.
893 The --kill-long-query-type option can be used to specify all or only
895 SELECT queries that are preventing global lock from being acquired. In
896 order to use this option xtrabackup user should have PROCESS and SUPER
897 privileges.
898 Options summary
900 • --ftwrl-wait-timeout (seconds) - how long to wait for a good moment.
901 Default is 0, not to wait.
902 • --ftwrl-wait-query-type - which long queries should be finished be‐
904 fore FLUSH TABLES WITH READ LOCK is run. Default is all.
905 • --ftwrl-wait-threshold (seconds) - how long query should be running
907 before we consider it long running and potential blocker of global
908 lock.
909 • --kill-long-queries-timeout (seconds) - how many time we give for
911 queries to complete after FLUSH TABLES WITH READ LOCK is issued be‐
912 fore start to kill. Default if 0, not to kill.
913 • --kill-long-query-type - which queries should be killed once
915 kill-long-queries-timeout has expired.
916 Example
918 Running the xtrabackup with the following options will cause xtrabackup
919 to spend no longer than 3 minutes waiting for all queries older than 40
920 seconds to complete.
921 $ xtrabackup --backup --ftwrl-wait-threshold=40 \
923 --ftwrl-wait-query-type=all --ftwrl-wait-timeout=180 \
924 --kill-long-queries-timeout=20 --kill-long-query-type=all \
925 --target-dir=/data/backups/
926 After FLUSH TABLES WITH READ LOCK is issued, xtrabackup will wait for
928 20 seconds for lock to be acquired. If lock is still not acquired after
929 20 seconds, it will kill all queries which are running longer that the
930 FLUSH TABLES WITH READ LOCK.
931 Accelerating the backup process
933 Copying with the --parallel and --compress-threads Options
934 When making a local or streaming backup with xbstream option, multiple
936 files can be copied at the same time when using the --parallel option.
937 This option specifies the number of threads created by xtrabackup to
938 copy data files.
939 To take advantage of this option either the multiple tablespaces option
941 must be enabled (innodb_file_per_table) or the shared tablespace must
942 be stored in multiple ibdata files with the innodb_data_file_path op‐
943 tion. Having multiple files for the database (or splitting one into
944 many) doesn't have a measurable impact on performance.
945 As this feature is implemented at the file level, concurrent file
947 transfer can sometimes increase I/O throughput when doing a backup on
948 highly fragmented data files, due to the overlap of a greater number of
949 random read requests. You should consider tuning the filesystem also to
950 obtain the maximum performance (e.g. checking fragmentation).
951 If the data is stored on a single file, this option will have no ef‐
953 fect.
954 To use this feature, simply add the option to a local backup, for exam‐
956 ple:
957 $ xtrabackup --backup --parallel=4 --target-dir=/path/to/backup
959 By using the xbstream in streaming backups, you can additionally speed
961 up the compression process with the --compress-threads option. This op‐
962 tion specifies the number of threads created by xtrabackup for for par‐
963 allel data compression. The default value for this option is 1.
964 To use this feature, simply add the option to a local backup, for exam‐
966 ple:
967 $ xtrabackup --backup --stream=xbstream --compress --compress-threads=4 --target-dir=./ > backup.xbstream
969 Before applying logs, compressed files will need to be uncompressed.
971 The --rsync Option
973 In order to speed up the backup process and to minimize the time FLUSH
975 TABLES WITH READ LOCK is blocking the writes, the option --rsync should
976 be used. When this option is specified, xtrabackup uses rsync to copy
977 all non-InnoDB files instead of spawning a separate cp for each file,
978 which can be much faster for servers with a large number of databases
979 or tables. xtrabackup will call the rsync twice, once before the FLUSH
980 TABLES WITH READ LOCK and once during to minimize the time the read
981 lock is being held. During the second rsync call, it will only synchro‐
982 nize the changes to non-transactional data (if any) since the first
983 call performed before the FLUSH TABLES WITH READ LOCK. Note that Per‐
984 cona XtraBackup will use Backup locks where available as a lightweight
985 alternative to FLUSH TABLES WITH READ LOCK. This feature is available
986 in Percona Server for MySQL 5.6+. Percona XtraBackup uses this automat‐
987 ically to copy non-InnoDB data to avoid blocking DML queries that mod‐
988 ify InnoDB tables.
989 NOTE:
991 This option cannot be used together with the --stream option.
992 Point-In-Time recovery
994 Recovering up to particular moment in database's history can be done
995 with xtrabackup and the binary logs of the server.
996 Note that the binary log contains the operations that modified the
998 database from a point in the past. You need a full datadir as a base,
999 and then you can apply a series of operations from the binary log to
1000 make the data match what it was at the point in time you want.
1001 $ xtrabackup --backup --target-dir=/path/to/backup
1003 $ xtrabackup --prepare --target-dir=/path/to/backup
1004 For more details on these procedures, see creating_a_backup and prepar‐
1006 ing_a_backup.
1007 Now, suppose that some time has passed, and you want to restore the
1009 database to a certain point in the past, having in mind that there is
1010 the constraint of the point where the snapshot was taken.
1011 To find out what is the situation of binary logging in the server, exe‐
1013 cute the following queries:
1014 mysql> SHOW BINARY LOGS;
1016 +------------------+-----------+
1017 | Log_name | File_size |
1018 +------------------+-----------+
1019 | mysql-bin.000001 | 126 |
1020 | mysql-bin.000002 | 1306 |
1021 | mysql-bin.000003 | 126 |
1022 | mysql-bin.000004 | 497 |
1023 +------------------+-----------+
1024 and
1026 mysql> SHOW MASTER STATUS;
1028 +------------------+----------+--------------+------------------+
1029 | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
1030 +------------------+----------+--------------+------------------+
1031 | mysql-bin.000004 | 497 | | |
1032 +------------------+----------+--------------+------------------+
1033 The first query will tell you which files contain the binary log and
1035 the second one which file is currently being used to record changes,
1036 and the current position within it. Those files are stored usually in
1037 the datadir (unless other location is specified when the server is
1038 started with the --log-bin= option).
1039 To find out the position of the snapshot taken, see the xtrabackup_bin‐
1041 log_info at the backup's directory:
1042 $ cat /path/to/backup/xtrabackup_binlog_info
1044 mysql-bin.000003 57
1045 This will tell you which file was used at moment of the backup for the
1047 binary log and its position. That position will be the effective one
1048 when you restore the backup:
1049 $ xtrabackup --copy-back --target-dir=/path/to/backup
1051 As the restoration will not affect the binary log files (you may need
1053 to adjust file permissions, see restoring_a_backup), the next step is
1054 extracting the queries from the binary log with mysqlbinlog starting
1055 from the position of the snapshot and redirecting it to a file
1056 $ mysqlbinlog /path/to/datadir/mysql-bin.000003 /path/to/datadir/mysql-bin.000004 \
1058 --start-position=57 > mybinlog.sql
1059 Note that if you have multiple files for the binary log, as in the ex‐
1061 ample, you have to extract the queries with one process, as shown
1062 above.
1063 Inspect the file with the queries to determine which position or date
1065 corresponds to the point-in-time wanted. Once determined, pipe it to
1066 the server. Assuming the point is 11-12-25 01:00:00:
1067 $ mysqlbinlog /path/to/datadir/mysql-bin.000003 /path/to/datadir/mysql-bin.000004 \
1069 --start-position=57 --stop-datetime="11-12-25 01:00:00" | mysql -u root -p
1070 and the database will be rolled forward up to that Point-In-Time.
1072 Making Backups in Replication Environments
1074 There are options specific to back up from a replication replica.
1075 The --slave-info Option
1077 This option is useful when backing up a replication replica server. It
1078 prints the binary log position and name of the source server. It also
1079 writes this information to the xtrabackup_slave_info file as a CHANGE
1080 MASTER statement.
1081 This option is useful for setting up a new replica for this source.
1083 You can start a replica server with this backup and issue the statement
1084 saved in the xtrabackup_slave_info file. More details of this procedure
1085 can be found in replication_howto.
1086 The --safe-slave-backup Option
1088 In order to assure a consistent replication state, this option stops
1089 the replication SQL thread and waits to start backing up until
1090 Slave_open_temp_tables in SHOW STATUS is zero. If there are no open
1091 temporary tables, the backup will take place, otherwise the SQL thread
1092 will be started and stopped until there are no open temporary tables.
1093 The backup will fail if Slave_open_temp_tables does not become zero af‐
1094 ter --safe-slave-backup-timeout seconds (defaults to 300 seconds). The
1095 replication SQL thread will be restarted when the backup finishes.
1096 Using this option is always recommended when taking backups from a
1098 replica server.
1099 WARNING:
1101 Make sure your replica is a true replica of the source before using
1102 it as a source for backup. A good tool to validate a replica is
1103 pt-table-checksum.
1104 Store backup history on the server
1106 Percona XtraBackup supports storing the backups history on the server.
1107 This feature was implemented in Percona XtraBackup 2.2. Storing backup
1108 history on the server was implemented to provide users with additional
1109 information about backups that are being taken. Backup history informa‐
1110 tion will be stored in the PERCONA_SCHEMA.XTRABACKUP_HISTORY table.
1111 To use this feature the following options are available:
1113 • --history =<name> : This option enables the history feature and al‐
1115 lows the user to specify a backup series name that will be placed
1116 within the history record.
1117 • --incremental-history-name =<name> : This option allows an incremen‐
1119 tal backup to be made based on a specific history series by name.
1120 xtrabackup will search the history table looking for the most recent
1121 (highest to_lsn) backup in the series and take the to_lsn value to
1122 use as it's starting lsn. This is mutually exclusive with --incremen‐
1123 tal-history-uuid, --incremental-basedir and --incremental-lsn op‐
1124 tions. If no valid LSN can be found (no series by that name) xtra‐
1125 backup will return with an error.
1126 • --incremental-history-uuid =<uuid> : Allows an incremental backup to
1128 be made based on a specific history record identified by UUID. xtra‐
1129 backup will search the history table looking for the record matching
1130 UUID and take the to_lsn value to use as it's starting LSN. This op‐
1131 tions is mutually exclusive with --incremental-basedir, --incremen‐
1132 tal-lsn and --incremental-history-name options. If no valid LSN can
1133 be found (no record by that UUID or missing to_lsn), xtrabackup will
1134 return with an error.
1135 NOTE:
1137 Backup that's currently being performed will NOT exist in the xtra‐
1138 backup_history table within the resulting backup set as the record
1139 will not be added to that table until after the backup has been
1140 taken.
1141 If you want access to backup history outside of your backup set in the
1143 case of some catastrophic event, you will need to either perform a
1144 mysqldump, partial backup or SELECT * on the history table after xtra‐
1145 backup completes and store the results with you backup set.
1146 Privileges
1148 User performing the backup will need following privileges:
1149 • CREATE privilege in order to create the
1151 PERCONA_SCHEMA.xtrabackup_history database and table.
1152 • INSERT privilege in order to add history records to the
1154 PERCONA_SCHEMA.xtrabackup_history table.
1155 • SELECT privilege in order to use --incremental-history-name or --in‐
1157 cremental-history-uuid in order for the feature to look up the inn‐
1158 odb_to_lsn values in the PERCONA_SCHEMA.xtrabackup_history table.
1159 PERCONA_SCHEMA.XTRABACKUP_HISTORY table
1160 This table contains the information about the previous server backups.
1162 Information about the backups will only be written if the backup was
1163 taken with --history option.
1164 ┌─────────────────┬────────────────────────────┐
1166 │Column Name │ Description │
1167 ├─────────────────┼────────────────────────────┤
1168 │uuid │ Unique backup id │
1169 ├─────────────────┼────────────────────────────┤
1170 │name │ User provided name of │
1171 │ │ backup series. There may │
1172 │ │ be multiple entries with │
1173 │ │ the same name used to │
1174 │ │ identify related backups │
1175 │ │ in a series. │
1176 ├─────────────────┼────────────────────────────┤
1177 │tool_name │ Name of tool used to take │
1178 │ │ backup │
1179 ├─────────────────┼────────────────────────────┤
1180 │tool_command │ Exact command line given │
1181 │ │ to the tool with --pass‐ │
1182 │ │ word and --encryption_key │
1183 │ │ obfuscated │
1184 ├─────────────────┼────────────────────────────┤
1185 │tool_version │ Version of tool used to │
1186 │ │ take backup │
1187 ├─────────────────┼────────────────────────────┤
1188 │ibbackup_version │ Version of the xtrabackup │
1189 │ │ binary used to take backup │
1190 ├─────────────────┼────────────────────────────┤
1191 │server_version │ Server version on which │
1192 │ │ backup was taken │
1193 ├─────────────────┼────────────────────────────┤
1194 │start_time │ Time at the start of the │
1195 │ │ backup │
1196 ├─────────────────┼────────────────────────────┤
1197 │end_time │ Time at the end of the │
1198 │ │ backup │
1199 └─────────────────┴────────────────────────────┘
1200 │lock_time │ Amount of time, in sec‐ │
1204 │ │ onds, spent calling and │
1205 │ │ holding locks for FLUSH │
1206 │ │ TABLES WITH READ LOCK │
1207 ├─────────────────┼────────────────────────────┤
1208 │binlog_pos │ Binlog file and position │
1209 │ │ at end of FLUSH TABLES │
1210 │ │ WITH READ LOCK │
1211 ├─────────────────┼────────────────────────────┤
1212 │innodb_from_lsn │ LSN at beginning of backup │
1213 │ │ which can be used to de‐ │
1214 │ │ termine prior backups │
1215 ├─────────────────┼────────────────────────────┤
1216 │innodb_to_lsn │ LSN at end of backup which │
1217 │ │ can be used as the start‐ │
1218 │ │ ing lsn for the next in‐ │
1219 │ │ cremental │
1220 ├─────────────────┼────────────────────────────┤
1221 │partial │ Is this a partial backup, │
1222 │ │ if N that means that it's │
1223 │ │ the full backup │
1224 ├─────────────────┼────────────────────────────┤
1225 │incremental │ Is this an incremental │
1226 │ │ backup │
1227 ├─────────────────┼────────────────────────────┤
1228 │format │ Description of result for‐ │
1229 │ │ mat (file, tar, xbstream) │
1230 ├─────────────────┼────────────────────────────┤
1231 │compact │ Is this a compact backup │
1232 ├─────────────────┼────────────────────────────┤
1233 │compressed │ Is this a compressed │
1234 │ │ backup │
1235 ├─────────────────┼────────────────────────────┤
1236 │encrypted │ Is this an encrypted │
1237 │ │ backup │
1238 └─────────────────┴────────────────────────────┘
1239 Limitations.INDENT 0.0
1240 • --history option must be specified only on the command line and not
1242 within a configuration file in order to be effective.
1243 • --incremental-history-name and --incremental-history-uuid options
1245 must be specified only on the command line and not within a configu‐
1246 ration file in order to be effective.
1247
1249 Implementation Details
1250 This page contains notes on various internal aspects of the xtrabackup
1251 tool's operation.
1252 File Permissions
1254 xtrabackup opens the source data files in read-write mode, although it
1255 does not modify the files. This means that you must run xtrabackup as a
1256 user who has permission to write the data files. The reason for opening
1257 the files in read-write mode is that xtrabackup uses the embedded Inn‐
1258 oDB libraries to open and read the files, and InnoDB opens them in
1259 read-write mode because it normally assumes it is going to write to
1260 them.
1261 Tuning the OS Buffers
1263 Because xtrabackup reads large amounts of data from the filesystem, it
1264 uses posix_fadvise() where possible, to instruct the operating system
1265 not to try to cache the blocks it reads from disk. Without this hint,
1266 the operating system would prefer to cache the blocks, assuming that
1267 xtrabackup is likely to need them again, which is not the case. Caching
1268 such large files can place pressure on the operating system's virtual
1269 memory and cause other processes, such as the database server, to be
1270 swapped out. The xtrabackup tool avoids this with the following hint on
1271 both the source and destination files:
1272 posix_fadvise(file, 0, 0, POSIX_FADV_DONTNEED)
1274 In addition, xtrabackup asks the operating system to perform more ag‐
1276 gressive read-ahead optimizations on the source files:
1277 posix_fadvise(file, 0, 0, POSIX_FADV_SEQUENTIAL)
1279 Copying Data Files
1281 When copying the data files to the target directory, xtrabackup reads
1282 and writes 1 MB of data at a time. This is not configurable. When copy‐
1283 ing the log file, xtrabackup reads and writes 512 bytes at a time. This
1284 is also not possible to configure, and matches InnoDB's behavior (work‐
1285 around exists in Percona Server for MySQL because it has an option to
1286 tune innodb_log_block_size for XtraDB, and in that case Percona Xtra‐
1287 Backup will match the tuning).
1288 After reading from the files, xtrabackup iterates over the 1MB buffer a
1290 page at a time, and checks for page corruption on each page with Inn‐
1291 oDB's buf_page_is_corrupted() function. If the page is corrupt, it
1292 re-reads and retries up to 10 times for each page. It skips this check
1293 on the doublewrite buffer.
1294 xtrabackup Exit Codes
1296 The xtrabackup binary exits with the traditional success value of 0 af‐
1297 ter a backup when no error occurs. If an error occurs during the
1298 backup, the exit value is 1.
1299 In certain cases, the exit value can be something other than 0 or 1,
1301 due to the command-line option code included from the MySQL libraries.
1302 An unknown command-line option, for example, will cause an exit code of
1303 255.
1304
1306 The xtrabackup Option Reference
1307 This page documents all of the command-line options for the xtrabackup
1308 binary.
1309 Modes of operation
1311 You invoke xtrabackup in one of the following modes:
1312 • --backup mode to make a backup in a target directory
1314 • --prepare mode to restore data from a backup (created in --backup
1316 mode)
1317 • --copy-back to copy data from a backup to the location that contained
1319 the original data; to move data instead of copying use the alternate
1320 --move-back mode.
1321 • --stats mode to scan the specified data files and print out index
1323 statistics.
1324 When you intend to run xtrabackup in any of these modes, use the fol‐
1326 lowing syntax:
1327 $ xtrabackup [--defaults-file=#] --backup|--prepare|--copy-back|--stats [OPTIONS]
1329 For example, the --prepare mode is applied as follows:
1331 $ xtrabackup --prepare --target-dir=/data/backup/mysql/
1333 For all modes, the default options are read from the xtrabackup and
1335 mysqld configuration groups from the following files in the given or‐
1336 der:
1337 1. /etc/my.cnf
1339 2. /etc/mysql/my.cnf
1341 3. /usr/etc/my.cnf
1343 4. ~/.my.cnf.
1345 As the first parameter to xtrabackup (in place of the --defaults-file,
1347 you may supply one of the following:
1348 • --print-defaults to have xtrabackup print the argument list and exit.
1350 • --no-defaults to forbid reading options from any file but the login
1352 file.
1353 • --defaults-file to read the default options from the given file.
1355 • --defaults-extra-file to read the specified additional file after the
1357 global files have been read.
1358 • --defaults-group-suffix to read the configuration groups with the
1360 given suffix. The effective group name is constructed by concatenat‐
1361 ing the default configuration groups (xtrabackup and mysqld) with the
1362 given suffix.
1363 • --login-path to read the given path from the login file.
1365 InnoDB Options
1366 There is a large group of InnoDB options that are normally read from
1368 the my.cnf configuration file, so that xtrabackup boots up its embedded
1369 InnoDB in the same configuration as your current server. You normally
1370 do not need to specify them explicitly. These options have the same be‐
1371 havior in InnoDB and XtraDB. See --innodb-miscellaneous for more infor‐
1372 mation.
1373 Options
1375 --apply-log-only
1376 This option causes only the redo stage to be performed when pre‐
1377 paring a backup. It is very important for incremental backups.
1378 --backup
1380 Make a backup and place it in --target-dir. See Creating a
1381 backup.
1382 --backup-lock-timeout
1384 The timeout in seconds for attempts to acquire metadata locks.
1385 --backup-lock-retry-count
1387 The number of attempts to acquire metadata locks.
1388 --backup-locks
1390 This option controls if backup locks should be used instead of
1391 FLUSH TABLES WITH READ LOCK on the backup stage. The option has
1392 no effect when backup locks are not supported by the server.
1393 This option is enabled by default, disable with
1394 --no-backup-locks.
1395 --check-privileges
1397 This option checks if Percona XtraBackup has all required privi‐
1398 leges. If a missing privilege is required for the current oper‐
1399 ation, it will terminate and print out an error message. If a
1400 missing privilege is not required for the current operation, but
1401 may be necessary for some other XtraBackup operation, the
1402 process is not aborted and a warning is printed.
1403 xtrabackup: Error: missing required privilege LOCK TABLES on *.*
1405 xtrabackup: Warning: missing required privilege REPLICATION CLIENT on *.*
1406 --close-files
1408 Do not keep files opened. When xtrabackup opens tablespace it
1409 normally doesn't close its file handle in order to handle the
1410 DDL operations correctly. However, if the number of tablespaces
1411 is really huge and can not fit into any limit, there is an op‐
1412 tion to close file handles once they are no longer accessed.
1413 Percona XtraBackup can produce inconsistent backups with this
1414 option enabled. Use at your own risk.
1415 --compress
1417 This option tells xtrabackup to compress all output data, in‐
1418 cluding the transaction log file and meta data files, using ei‐
1419 ther the quicklz or lz4 compression algorithm. quicklz is chosen
1420 by default.
1421 When using --compress=quicklz or --compress, the resulting files
1423 have the qpress archive format, i.e. every *.qp file produced by
1424 xtrabackup is essentially a one-file qpress archive and can be
1425 extracted and uncompressed by the qpress file archiver.
1426 --compress=lz4 produces *.lz4 files. You can extract the con‐
1428 tents of these files by using a program such as lz4.
1429 SEE ALSO:
1431 QuickLZ
1433 http://www.quicklz.com
1434 LZ4 https://lz4.github.io/lz4/
1436 --compress-chunk-size=#
1438 Size of working buffer(s) for compression threads in bytes. The
1439 default value is 64K.
1440 --compress-threads=#
1442 This option specifies the number of worker threads used by xtra‐
1443 backup for parallel data compression. This option defaults to 1.
1444 Parallel compression (--compress-threads) can be used together
1445 with parallel file copying (--parallel). For example, --paral‐
1446 lel=4 --compress --compress-threads=2 will create 4 I/O threads
1447 that will read the data and pipe it to 2 compression threads.
1448 --copy-back
1450 Copy all the files in a previously made backup from the backup
1451 directory to their original locations. This option will not copy
1452 over existing files unless --force-non-empty-directories option
1453 is specified.
1454 --core-file
1456 Write core on fatal signals.
1457 --databases=#
1459 This option specifies a list of databases and tables that should
1460 be backed up. The option accepts the list of the form "database‐
1461 name1[.table_name1] databasename2[.table_name2] . . .".
1462 --databases-exclude=name
1464 Excluding databases based on name, Operates the same way as
1465 --databases, but matched names are excluded from backup. Note
1466 that this option has a higher priority than --databases.
1467 --databases-file=#
1469 This option specifies the path to the file containing the list
1470 of databases and tables that should be backed up. The file can
1471 contain the list elements of the form databasename1[.ta‐
1472 ble_name1], one element per line.
1473 --datadir=DIRECTORY
1475 The source directory for the backup. This should be the same as
1476 the datadir for your MySQL server, so it should be read from
1477 my.cnf if that exists; otherwise you must specify it on the com‐
1478 mand line.
1479 When combined with the --copy-back or --move-back option,
1481 --datadir refers to the destination directory.
1482 Once connected to the server, in order to perform a backup you
1484 will need READ and EXECUTE permissions at a filesystem level in
1485 the server's datadir.
1486 --debug-sleep-before-unlock=#
1488 This is a debug-only option used by the xtrabackup test suite.
1489 --debug-sync=name
1491 The debug sync point. This option is only used by the xtrabackup
1492 test suite.
1493 --decompress
1495 Decompresses all files with the .qp extension in a backup previ‐
1496 ously made with the --compress option. The --parallel option
1497 will allow multiple files to be decrypted simultaneously. In or‐
1498 der to decompress, the qpress utility MUST be installed and ac‐
1499 cessible within the path. Percona XtraBackup does not automati‐
1500 cally remove the compressed files. In order to clean up the
1501 backup directory users should use --remove-original option.
1502 The --decompress option may be used with xbstream to decompress
1504 individual qpress files.
1505 If you used the lz4 compression algorithm to compress the files
1507 (--compress=lz4), change the --decompress parameter accordingly:
1508 --decompress=lz4.
1509 --decompress-threads=#
1511 Force xbstream to use the specified number of threads for decom‐
1512 pressing.
1513 --decrypt=ENCRYPTION-ALGORITHM
1515 Decrypts all files with the .xbcrypt extension in a backup pre‐
1516 viously made with --encrypt option. The --parallel option will
1517 allow multiple files to be decrypted simultaneously. Percona
1518 XtraBackup doesn't automatically remove the encrypted files. In
1519 order to clean up the backup directory users should use
1520 --remove-original option.
1521 --defaults-extra-file=[MY.CNF]
1523 Read this file after the global files are read. Must be given as
1524 the first option on the command-line.
1525 --defaults-file=[MY.CNF]
1527 Only read default options from the given file. Must be given as
1528 the first option on the command-line. Must be a real file; it
1529 cannot be a symbolic link.
1530 --defaults-group=GROUP-NAME
1532 This option is to set the group which should be read from the
1533 configuration file. This is used by xtrabackup if you use the
1534 --defaults-group option. It is needed for mysqld_multi deploy‐
1535 ments.
1536 --defaults-group-suffix=#
1538 Also reads groups with concat(group, suffix).
1539 --dump-innodb-buffer-pool
1541 This option controls whether or not a new dump of buffer pool
1542 content should be done.
1543 With --dump-innodb-buffer-pool, xtrabackup makes a request to
1545 the server to start the buffer pool dump (it takes some time to
1546 complete and is done in background) at the beginning of a backup
1547 provided the status variable innodb_buffer_pool_dump_status re‐
1548 ports that the dump has been completed.
1549 $ xtrabackup --backup --dump-innodb-buffer-pool --target-dir=/home/user/backup
1551 By default, this option is set to OFF.
1553 If innodb_buffer_pool_dump_status reports that there is running
1555 dump of buffer pool, xtrabackup waits for the dump to complete
1556 using the value of --dump-innodb-buffer-pool-timeout
1557 The file ib_buffer_pool stores tablespace ID and page ID data
1559 used to warm up the buffer pool sooner.
1560 SEE ALSO:
1562 MySQL Documentation: Saving and Restoring the Buffer Pool
1564 State
1565 https://dev.mysql.com/doc/refman/5.7/en/innodb-preload-buffer-pool.html
1566 --dump-innodb-buffer-pool-timeout
1568 This option contains the number of seconds that xtrabackup
1569 should monitor the value of innodb_buffer_pool_dump_status to
1570 determine if buffer pool dump has completed.
1571 This option is used in combination with
1573 --dump-innodb-buffer-pool. By default, it is set to 10 seconds.
1574 --dump-innodb-buffer-pool-pct
1576 This option contains the percentage of the most recently used
1577 buffer pool pages to dump.
1578 This option is effective if --dump-innodb-buffer-pool option is
1580 set to ON. If this option contains a value, xtrabackup sets the
1581 MySQL system variable innodb_buffer_pool_dump_pct. As soon as
1582 the buffer pool dump completes or it is stopped (see
1583 --dump-innodb-buffer-pool-timeout), the value of the MySQL sys‐
1584 tem variable is restored.
1585 SEE ALSO:
1587 Changing the timeout for buffer pool dump
1589 --dump-innodb-buffer-pool-timeout
1590 MySQL Documentation: innodb_buffer_pool_dump_pct system vari‐
1592 able
1593 https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_buffer_pool_dump_pct
1594 --encrypt=ENCRYPTION_ALGORITHM
1596 This option instructs xtrabackup to encrypt backup copies of
1597 InnoDB data files using the algorithm specified in the ENCRYP‐
1598 TION_ALGORITHM. Currently supported algorithms are: AES128,
1599 AES192 and AES256
1600 --encrypt-key=ENCRYPTION_KEY
1602 A proper length encryption key to use. It is not recommended to
1603 use this option where there is uncontrolled access to the ma‐
1604 chine as the command line and thus the key can be viewed as part
1605 of the process info.
1606 --encrypt-key-file=ENCRYPTION_KEY_FILE
1608 The name of a file where the raw key of the appropriate length
1609 can be read from. The file must be a simple binary (or text)
1610 file that contains exactly the key to be used.
1611 It is passed directly to the xtrabackup child process. See the
1613 xtrabackup documentation for more details.
1614 --encrypt-threads=#
1616 This option specifies the number of worker threads that will be
1617 used for parallel encryption/decryption. See the xtrabackup
1618 documentation for more details.
1619 --encrypt-chunk-size=#
1621 This option specifies the size of the internal working buffer
1622 for each encryption thread, measured in bytes. It is passed di‐
1623 rectly to the xtrabackup child process. See the xtrabackup docu‐
1624 mentation for more details.
1625 --export
1627 Create files necessary for exporting tables. See Restoring Indi‐
1628 vidual Tables.
1629 --extra-lsndir=DIRECTORY
1631 (for --backup): save an extra copy of the xtrabackup_checkpoints
1632 and xtrabackup_info files in this directory.
1633 --force-non-empty-directories
1635 When specified, it makes --copy-back and --move-back option
1636 transfer files to non-empty directories. No existing files will
1637 be overwritten. If files that need to be copied/moved from the
1638 backup directory already exist in the destination directory, it
1639 will still fail with an error.
1640 --ftwrl-wait-timeout=SECONDS
1642 This option specifies time in seconds that xtrabackup should
1643 wait for queries that would block FLUSH TABLES WITH READ LOCK
1644 before running it. If there are still such queries when the
1645 timeout expires, xtrabackup terminates with an error. Default is
1646 0, in which case it does not wait for queries to complete and
1647 starts FLUSH TABLES WITH READ LOCK immediately. Where supported
1648 xtrabackup will automatically use Backup Locks as a lightweight
1649 alternative to FLUSH TABLES WITH READ LOCK to copy non-InnoDB
1650 data to avoid blocking DML queries that modify InnoDB tables.
1651 --ftwrl-wait-threshold=SECONDS
1653 This option specifies the query run time threshold which is used
1654 by xtrabackup to detect long-running queries with a non-zero
1655 value of --ftwrl-wait-timeout. FLUSH TABLES WITH READ LOCK is
1656 not started until such long-running queries exist. This option
1657 has no effect if --ftwrl-wait-timeout is 0. Default value is 60
1658 seconds. Where supported xtrabackup will automatically use
1659 Backup Locks as a lightweight alternative to FLUSH TABLES WITH
1660 READ LOCK to copy non-InnoDB data to avoid blocking DML queries
1661 that modify InnoDB tables.
1662 --ftwrl-wait-query-type=all|update
1664 This option specifies which types of queries are allowed to com‐
1665 plete before xtrabackup will issue the global lock. Default is
1666 all.
1667 --galera-info
1669 This option creates the xtrabackup_galera_info file which con‐
1670 tains the local node state at the time of the backup. Option
1671 should be used when performing the backup of Percona XtraDB
1672 Cluster. It has no effect when backup locks are used to create
1673 the backup.
1674 --generate-new-master-key
1676 Generate a new master key when doing a copy-back.
1677 --generate-transition-key
1679 xtrabackup needs to access the same keyring file or vault server
1680 during prepare and copy-back but it should not depend on whether
1681 the server keys have been purged.
1682 --generate-transition-key creates and adds to the keyring a
1684 transition key for xtrabackup to use if the master key used for
1685 encryption is not found because it has been rotated and purged.
1686 --get-server-public-key
1688 Get the server public key
1689 SEE ALSO:
1691 MySQL Documentation: The --get-server-public-key Option
1692 https://dev.mysql.com/doc/refman/5.7/en/connection-options.html#option_general_get-server-public-key
1693 --help When run with this option or without any options xtrabackup dis‐
1695 plays information about how to run the program on the command
1696 line along with all supported options and variables with default
1697 values where appropriate.
1698 --history=NAME
1700 This option enables the tracking of backup history in the PER‐
1701 CONA_SCHEMA.xtrabackup_history table. An optional history series
1702 name may be specified that will be placed with the history
1703 record for the current backup being taken.
1704 --host=HOST
1706 This option accepts a string argument that specifies the host to
1707 use when connecting to the database server with TCP/IP. It is
1708 passed to the mysql child process without alteration. See mysql
1709 --help for details.
1710 --incremental
1712 This option tells xtrabackup to create an incremental backup. It
1713 is passed to the xtrabackup child process. When this option is
1714 specified, either --incremental-lsn or --incremental-basedir can
1715 also be given. If neither option is given, option
1716 --incremental-basedir is passed to xtrabackup by default, set to
1717 the first timestamped backup directory in the backup base direc‐
1718 tory.
1719 SEE ALSO:
1721 More information about incremental backups
1723 See section xb_incremental
1724 --incremental-basedir=DIRECTORY
1726 When creating an incremental backup, this is the directory con‐
1727 taining the full backup that is the base dataset for the incre‐
1728 mental backups.
1729 --incremental-dir=DIRECTORY
1731 When preparing an incremental backup, this is the directory
1732 where the incremental backup is combined with the full backup to
1733 make a new full backup.
1734 --incremental-force-scan
1736 When creating an incremental backup, force a full scan of the
1737 data pages in the instance being backuped even if the complete
1738 changed page bitmap data is available.
1739 --incremental-history-name=name
1741 This option specifies the name of the backup series stored in
1742 the PERCONA_SCHEMA.xtrabackup_history history record to base an
1743 incremental backup on. xtrabackup will search the history table
1744 looking for the most recent (highest innodb_to_lsn), successful
1745 backup in the series and take the to_lsn value to use as the
1746 starting lsn for the incremental backup. This will be mutually
1747 exclusive with --incremental-history-uuid, --incremental-basedir
1748 and --incremental-lsn. If no valid lsn can be found (no series
1749 by that name, no successful backups by that name) xtrabackup
1750 will return with an error. It is used with the --incremental op‐
1751 tion.
1752 --incremental-history-uuid=name
1754 This option specifies the UUID of the specific history record
1755 stored in the PERCONA_SCHEMA.xtrabackup_history to base an in‐
1756 cremental backup on. --incremental-history-name,
1757 --incremental-basedir and --incremental-lsn. If no valid lsn can
1758 be found (no success record with that UUID) xtrabackup will re‐
1759 turn with an error. It is used with the --incremental option.
1760 --incremental-lsn=LSN
1762 When creating an incremental backup, you can specify the log se‐
1763 quence number (LSN) instead of specifying --incremental-basedir.
1764 For databases created in 5.1 and later, specify the LSN as a
1765 single 64-bit integer. ATTENTION: If a wrong LSN value is speci‐
1766 fied (a user error which Percona XtraBackup is unable to de‐
1767 tect), the backup will be unusable. Be careful!
1768 --innodb[=name]
1770 This option is ignored for MySQL option compatibility.
1771 --innodb-miscellaneous
1773 There is a large group of InnoDB options that are normally read
1774 from the my.cnf configuration file, so that xtrabackup boots up
1775 its embedded InnoDB in the same configuration as your current
1776 server. You normally do not need to specify these explicitly.
1777 These options have the same behavior in InnoDB and XtraDB:
1778 • --innodb-adaptive-hash-index
1780 • --innodb-additional-mem-pool-size
1782 • --innodb-autoextend-increment
1784 • --innodb-buffer-pool-size
1786 • --innodb-buffer-pool-filename
1788 • --innodb-checksum-algorithm
1790 • --innodb-checksums
1792 • --innodb-data-file-path
1794 • --innodb-data-home-dir
1796 • --innodb-directories
1798 • --innodb-doublewrite-file
1800 • --innodb-doublewrite
1802 • --innodb-extra-undoslots
1804 • --innodb-fast-checksum
1806 • --innodb-file-io-threads
1808 • --innodb-file-per-table
1810 • --innodb-flush-log-at-trx-commit
1812 • --innodb-flush-method
1814 • --innodb-io-capacity
1816 • --innodb-lock-wait-timeout
1818 • --innodb-log-block-size
1820 • --innodb-log-buffer-size
1822 • --innodb-log-checksums
1824 • --innodb-log-files-in-group
1826 • --innodb-log-file-size
1828 • --innodb-log-group-home-dir
1830 • --innodb-max-dirty-pages-pct
1832 • --innodb-open-files
1834 • --innodb-page-size
1836 • --innodb-read-io-threads
1838 • --innodb-redo-log-encrypt
1840 • --innodb-undo-directory
1842 • --innodb-undo-log-encrypt
1844 • --innodb-undo-tablespaces`
1846 • --innodb-use-native-aio
1848 • --innodb-write-io-threads
1850 --keyring-file-data=FILENAME
1852 The path to the keyring file. Combine this option with
1853 --xtrabackup-plugin-dir.
1854 --kill-long-queries-timeout=SECONDS
1856 This option specifies the number of seconds xtrabackup waits be‐
1857 tween starting FLUSH TABLES WITH READ LOCK and killing those
1858 queries that block it. Default is 0 seconds, which means xtra‐
1859 backup will not attempt to kill any queries. In order to use
1860 this option xtrabackup user should have the PROCESS and SUPER
1861 privileges. Where supported, xtrabackup automatically uses
1862 Backup Locks as a lightweight alternative to FLUSH TABLES WITH
1863 READ LOCK to copy non-InnoDB data to avoid blocking DML queries
1864 that modify InnoDB tables.
1865 --kill-long-query-type=all|select
1867 This option specifies which types of queries should be killed to
1868 unblock the global lock. Default is "all".
1869 --lock-ddl
1871 Issue LOCK TABLES FOR BACKUP if it is supported by server (oth‐
1872 erwise use LOCK INSTANCE FOR BACKUP) at the beginning of the
1873 backup to block all DDL operations.
1874 NOTE:
1876 Prior to Percona XtraBackup 8.0.22-15.0, using a
1877 safe-slave-backup stops the SQL replica thread after the Inn‐
1878 oDB tables and before the non-InnoDB tables are backed up.
1879 As of Percona XtraBackup 8.0.22-15.0, using a
1881 safe-slave-backup option stops the SQL replica thread before
1882 copying the InnoDB files.
1883 --lock-ddl-per-table
1885 Lock DDL for each table before xtrabackup starts to copy it and
1886 until the backup is completed.
1887 As of Percona XtraBackup 8.0.15, the --lock-ddl-per-table option
1889 is deprecated. Use the --lock-ddl option instead.
1890 --lock-ddl-timeout
1892 If LOCK TABLES FOR BACKUP or LOCK INSTANCE FOR BACKUP does not
1893 return within given timeout, abort the backup.
1894 --log This option is ignored for MySQL
1896 --log-bin
1898 The base name for the log sequence.
1899 --log-bin-index=name
1901 File that holds the names for binary log files.
1902 --log-copy-interval=#
1904 This option specifies the time interval between checks done by
1905 the log copying thread in milliseconds (default is 1 second).
1906 --login-path
1908 Read the given path from the login file.
1909 --move-back
1911 Move all the files in a previously made backup from the backup
1912 directory to their original locations. As this option removes
1913 backup files, it must be used with caution.
1914 --no-backup-locks
1916 Explicity disables the --backup-locks option which is enabled by
1917 default.
1918 --no-defaults
1920 The default options are only read from the login file.
1921 --no-lock
1923 Use this option to disable table lock with FLUSH TABLES WITH
1924 READ LOCK. Use it only if ALL your tables are InnoDB and you DO
1925 NOT CARE about the binary log position of the backup. This op‐
1926 tion shouldn't be used if there are any DDL statements being ex‐
1927 ecuted or if any updates are happening on non-InnoDB tables
1928 (this includes the system MyISAM tables in the mysql database),
1929 otherwise it could lead to an inconsistent backup. Where sup‐
1930 ported xtrabackup will automatically use Backup Locks as a
1931 lightweight alternative to FLUSH TABLES WITH READ LOCK to copy
1932 non-InnoDB data to avoid blocking DML queries that modify InnoDB
1933 tables. If you are considering to use this because your backups
1934 are failing to acquire the lock, this could be because of incom‐
1935 ing replication events are preventing the lock from succeeding.
1936 Please try using --safe-slave-backup to momentarily stop the
1937 replication replica thread, this may help the backup to succeed
1938 and you do not need to use this option.
1939 xtrabackup_binlog_info is not created when --no-lock option is
1941 used (because SHOW MASTER STATUS may be inconsistent), but under
1942 certain conditions xtrabackup_binlog_pos_innodb can be used in‐
1943 stead to get consistent binlog coordinates as described in work‐
1944 ing_with_binlogs.
1945 --no-version-check
1947 This option disables the version check. If you do not pass this
1948 option, the automatic version check is enabled implicitly when
1949 xtrabackup runs in the --backup mode. To disable the version
1950 check, you should pass explicitly the --no-version-check option
1951 when envoking xtrabackup.
1952 When the automatic version check is enabled, xtrabackup performs
1954 a version check against the server on the backup stage after
1955 creating a server connection. xtrabackup sends the following in‐
1956 formation to the server:
1957 • MySQL flavour and version
1959 • Operating system name
1961 • Percona Toolkit version
1963 • Perl version
1965 Each piece of information has a unique identifier. This is a MD5
1967 hash value that Percona Toolkit uses to obtain statistics about
1968 how it is used. This is a random UUID; no client information is
1969 either collected or stored.
1970 --open-files-limit=#
1972 The maximum number of file descriptors to reserve with setr‐
1973 limit().
1974 --parallel=#
1976 This option specifies the number of threads to use to copy mul‐
1977 tiple data files concurrently when creating a backup. The de‐
1978 fault value is 1 (i.e., no concurrent transfer). In Percona
1979 XtraBackup 2.3.10 and newer, this option can be used with the
1980 --copy-back option to copy the user data files in parallel (redo
1981 logs and system tablespaces are copied in the main thread).
1982 --password=PASSWORD
1984 This option specifies the password to use when connecting to the
1985 database. It accepts a string argument. See mysql --help for
1986 details.
1987 --plugin-load
1989 List of plugins to load.
1990 --port=PORT
1992 This option accepts a string argument that specifies the port to
1993 use when connecting to the database server with TCP/IP. It is
1994 passed to the mysql child process without alteration. See mysql
1995 --help for details.
1996 --prepare
1998 Makes xtrabackup perform a recovery on a backup created with
1999 --backup, so that it is ready to use. See preparing a backup.
2000 --print-defaults
2002 Print the program argument list and exit. Must be given as the
2003 first option on the command-line.
2004 --print-param
2006 Makes xtrabackup print out parameters that can be used for copy‐
2007 ing the data files back to their original locations to restore
2008 them.
2009 --read-buffer-size
2011 Set the datafile read buffer size, given value is scaled up to
2012 page size. Default is 10Mb.
2013 --rebuild-indexes
2015 Rebuilds indexes in a compact backup. This option only has ef‐
2016 fect when the --prepare and --rebuild-threads options are pro‐
2017 vided.
2018 --rebuild-threads=#
2020 Uses the given number of threads to rebuild indexes in a compact
2021 backup. This option only has effect with the --prepare and
2022 --rebuild-indexes options.
2023 --remove-original
2025 Implemented in Percona XtraBackup 2.4.6, this option when speci‐
2026 fied will remove .qp, .xbcrypt and .qp.xbcrypt files after de‐
2027 cryption and decompression.
2028 --rocksdb-datadir
2030 RocksDB data directory
2031 --rocksdb-wal-dir
2033 RocksDB WAL directory.
2034 --rocksdb-checkpoint-max-age
2036 The checkpoint cannot be older than this number of seconds when
2037 the backup completes.
2038 --rocksdb-checkpoint-max-count
2040 Complete the backup even if the checkpoint age requirement has
2041 not been met after this number of checkpoints.
2042 --rollback-prepared-trx
2044 Force rollback prepared InnoDB transactions.
2045 --rsync
2047 Uses the rsync utility to optimize local file transfers. When
2048 this option is specified, xtrabackup uses rsync to copy all
2049 non-InnoDB files instead of spawning a separate cp for each
2050 file, which can be much faster for servers with a large number
2051 of databases or tables. This option cannot be used together
2052 with --stream.
2053 --safe-slave-backup
2055 When specified, xtrabackup will stop the replica SQL thread just
2056 before running FLUSH TABLES WITH READ LOCK and wait to start
2057 backup until Slave_open_temp_tables in SHOW STATUS is zero. If
2058 there are no open temporary tables, the backup will take place,
2059 otherwise the SQL thread will be started and stopped until there
2060 are no open temporary tables. The backup will fail if
2061 Slave_open_temp_tables does not become zero after
2062 --safe-slave-backup-timeout seconds. The replication SQL thread
2063 will be restarted when the backup finishes. This option is im‐
2064 plemented in order to deal with replicating temporary tables and
2065 isn't neccessary with Row-Based-Replication.
2066 --safe-slave-backup-timeout=SECONDS
2068 How many seconds --safe-slave-backup should wait for
2069 Slave_open_temp_tables to become zero. Defaults to 300 seconds.
2070 --secure-auth
2072 Refuse client connecting to server if it uses old (pre-4.1.1)
2073 protocol. (Enabled by default; use --skip-secure-auth to dis‐
2074 able.)
2075 --server-id=#
2077 The server instance being backed up.
2078 --server-public-key-path
2080 The file path to the server public RSA key in the PEM format.
2081 SEE ALSO:
2083 MySQL Documentation: The --server-public-key-path Option
2085 https://dev.mysql.com/doc/refman/8.0/en/connection-options.html#option_general_server-public-key-path
2086 --skip-tables-compatibility-check
2088 See --tables-compatibility-check.
2089 --slave-info
2091 This option is useful when backing up a replication replica
2092 server. It prints the binary log position of the source server.
2093 It also writes the binary log coordinates to the xtra‐
2094 backup_slave_info file as a CHANGE MASTER command. A new replica
2095 for this source can be set up by starting a replica server on
2096 this backup and issuing a CHANGE MASTER command with the binary
2097 log position saved in the xtrabackup_slave_info file.
2098 --socket
2100 This option accepts a string argument that specifies the socket
2101 to use when connecting to the local database server with a UNIX
2102 domain socket. It is passed to the mysql child process without
2103 alteration. See mysql --help for details.
2104 --ssl Enable secure connection. More information can be found in --ssl
2106 MySQL server documentation.
2107 --ssl-ca
2109 Path of the file which contains list of trusted SSL CAs. More
2110 information can be found in --ssl-ca MySQL server documentation.
2111 --ssl-capath
2113 Directory path that contains trusted SSL CA certificates in PEM
2114 format. More information can be found in --ssl-capath MySQL
2115 server documentation.
2116 --ssl-cert
2118 Path of the file which contains X509 certificate in PEM format.
2119 More information can be found in --ssl-cert MySQL server docu‐
2120 mentation.
2121 --ssl-cipher
2123 List of permitted ciphers to use for connection encryption. More
2124 information can be found in --ssl-cipher MySQL server documenta‐
2125 tion.
2126 --ssl-crl
2128 Path of the file that contains certificate revocation lists.
2129 More information can be found in --ssl-crl MySQL server documen‐
2130 tation.
2131 --ssl-crlpath
2133 Path of directory that contains certificate revocation list
2134 files. More information can be found in --ssl-crlpath MySQL
2135 server documentation.
2136 --ssl-fips-mode
2138 SSL FIPS mode (applies only for OpenSSL); permitted values are:
2139 OFF, ON, STRICT.
2140 --ssl-key
2142 Path of file that contains X509 key in PEM format. More informa‐
2143 tion can be found in --ssl-key MySQL server documentation.
2144 --ssl-mode
2146 Security state of connection to server. More information can be
2147 found in --ssl-mode MySQL server documentation.
2148 --ssl-verify-server-cert
2150 Verify server certificate Common Name value against host name
2151 used when connecting to server. More information can be found in
2152 --ssl-verify-server-cert MySQL server documentation.
2153 --stats
2155 Causes xtrabackup to scan the specified data files and print out
2156 index statistics.
2157 --stream=FORMAT
2159 Stream all backup files to the standard output in the specified
2160 format. Currently, this option only supports the xbstream for‐
2161 mat.
2162 --strict
2164 If this option is specified, xtrabackup fails with an error when
2165 invalid parameters are passed.
2166 --tables=name
2168 A regular expression against which the full tablename, in
2169 databasename.tablename format, is matched. If the name matches,
2170 the table is backed up. See partial backups.
2171 --tables-compatibility-check
2173 Enables the engine compatibility warning. The default value is
2174 ON. To disable the engine compatibility warning use
2175 --skip-tables-compatibility-check.
2176 --tables-exclude=name
2178 Filtering by regexp for table names. Operates the same way as
2179 --tables, but matched names are excluded from backup. Note that
2180 this option has a higher priority than --tables.
2181 --tables-file=name
2183 A file containing one table name per line, in database‐
2184 name.tablename format. The backup will be limited to the speci‐
2185 fied tables.
2186 --target-dir=DIRECTORY
2188 This option specifies the destination directory for the backup.
2189 If the directory does not exist, xtrabackup creates it. If the
2190 directory does exist and is empty, xtrabackup will succeed.
2191 xtrabackup will not overwrite existing files, however; it will
2192 fail with operating system error 17, file exists.
2193 If this option is a relative path, it is interpreted as being
2195 relative to the current working directory from which xtrabackup
2196 is executed.
2197 In order to perform a backup, you need READ, WRITE, and EXECUTE
2199 permissions at a filesystem level for the directory that you
2200 supply as the value of --target-dir.
2201 --innodb-temp-tablespaces-dir=DIRECTORY
2203 Directory where temp tablespace files live, this path can be ab‐
2204 solute.
2205 --throttle=#
2207 This option limits the number of chunks copied per second. The
2208 chunk size is 10 MB. To limit the bandwidth to 10 MB/s, set the
2209 option to 1: --throttle=1.
2210 SEE ALSO:
2212 More information about how to throttle a backup
2214 throttling_backups
2215 --tls-ciphersuites
2217 TLS v1.3 cipher to use.
2218 --tls-version
2220 TLS version to use, permitted values are: TLSv1, TLSv1.1,
2221 TLSv1.2, TLSv1.3.
2222 --tmpdir=name
2224 Specify the directory that will be used to store temporary files
2225 during the backup
2226 --transition-key=name
2228 This option is used to enable processing the backup without ac‐
2229 cessing the keyring vault server. In this case, xtrabackup de‐
2230 rives the AES encryption key from the specified passphrase and
2231 uses it to encrypt tablespace keys of tablespaces being backed
2232 up.
2233 If --transition-key does not have any value, xtrabackup will ask
2235 for it. The same passphrase should be specified for the
2236 --prepare command.
2237 --use-memory
2239 This option affects how much memory is allocated for preparing a
2240 backup with --prepare, or analyzing statistics with --stats. Its
2241 purpose is similar to innodb_buffer_pool_size. It does not do
2242 the same thing as the similarly named option in Oracle's InnoDB
2243 Hot Backup tool. The default value is 100MB, and if you have
2244 enough available memory, 1GB to 2GB is a good recommended value.
2245 Multiples are supported providing the unit (e.g. 1MB, 1M, 1GB,
2246 1G).
2247 --user=USERNAME
2249 This option specifies the MySQL username used when connecting to
2250 the server, if that's not the current user. The option accepts a
2251 string argument. See mysql --help for details.
2252 -v See --version
2254 --version
2256 This option prints xtrabackup version and exits.
2257 --xtrabackup-plugin-dir=DIRNAME
2259 The absolute path to the directory that contains the keyring
2260 plugin.
2261 SEE ALSO:
2263 Percona Server for MySQL Documentation: keyring_vault plugin
2265 with Data at Rest Encryption
2266 https://www.percona.com/doc/percona-server/LATEST/management/data_at_rest_encryption.html#keyring-vault-plugin
2267 MySQL Documentation: Using the keyring_file File-Based Plugin
2269 https://dev.mysql.com/doc/refman/5.7/en/keyring-file-plugin.html
2270
2272 Percona LLC and/or its affiliates
2273
2275 2009-2021, Percona LLC and/or its affiliates
22768.0 May 31, 2021 XTRABACKUP(1)