1INNOBACKUPEX(1)               Percona XtraBackup               INNOBACKUPEX(1)
2
3
4

NAME

6       innobackupex - innobackupex Documentation
7
8       The  innobackupex  tool is a Perl script that acts as a wrapper for the
9       xtrabackup C program. It is a patched version of  the  innobackup  Perl
10       script  that  Oracle  distributes  with  the InnoDB Hot Backup tool. It
11       enables more functionality by integrating xtrabackup  and  other  func‐
12       tions such as file copying and streaming, and adds some convenience. It
13       lets you perform  point-in-time  backups  of  InnoDB  /  XtraDB  tables
14       together with the schema definitions, MyISAM tables, and other portions
15       of the server.
16
17       This manual section explains how to use innobackupex in detail.
18

THE BACKUP CYCLE - FULL BACKUPS

20   Creating a Backup with innobackupex
21       innobackupex is the tool which provides functionality to backup a whole
22       MySQL  database instance using the xtrabackup in combination with tools
23       like xbstream and xbcrypt.
24
25       To create a full backup, invoke the script with the options  needed  to
26       connect  to the server and only one argument: the path to the directory
27       where the backup will be stored
28
29          $ innobackupex --user=DBUSER --password=DBUSERPASS /path/to/BACKUP-DIR/
30
31       and check the last line of the output for a confirmation message:
32
33          innobackupex: Backup created in directory '/path/to/BACKUP-DIR/2013-03-25_00-00-09'
34          innobackupex: MySQL binlog position: filename 'mysql-bin.000003', position 1946
35          111225 00:00:53  innobackupex: completed OK!
36
37       The backup will be stored within a time stamped  directory  created  in
38       the provided path, /path/to/BACKUP-DIR/2013-03-25_00-00-09 in this par‐
39       ticular example.
40
41   Under the hood
42       innobackupex called xtrabackup binary to backup all the data of  InnoDB
43       tables  (see  ../xtrabackup_bin/creating_a_backup  for  details on this
44       process) and copied all the table definitions  in  the  database  (.frm
45       files),  data  and  files  related to MyISAM, MERGE (reference to other
46       tables), CSV and ARCHIVE tables, along with triggers and database  con‐
47       figuration  information to a time stamped directory created in the pro‐
48       vided path.
49
50       It will also create the following files for convenience on the  created
51       directory.
52
53   Other options to consider
54   The --no-timestamp option
55       This  option  tells innobackupex not to create a time stamped directory
56       to store the backup:
57
58          $ innobackupex --user=DBUSER --password=DBUSERPASS /path/to/BACKUP-DIR/ --no-timestamp
59
60       innobackupex will  create  the  BACKUP-DIR  subdirectory  (or  fail  if
61       exists) and store the backup inside of it.
62
63   The --defaults-file option
64       You  can  provide  other  configuration  file to innobackupex with this
65       option. The only limitation is that it  has  to  be  the  first  option
66       passed:
67
68          $ innobackupex --defaults-file=/tmp/other-my.cnf --user=DBUSER --password=DBUSERPASS /path/to/BACKUP-DIR/
69
70   Preparing a Full Backup with innobackupex
71       After  creating  a  backup, the data is not ready to be restored. There
72       might be uncommitted transactions to be undone or transactions  in  the
73       logs  to be replayed. Doing those pending operations will make the data
74       files consistent and it is the purpose of the prepare stage. Once  this
75       has been done, the data is ready to be used.
76
77       To  prepare  a backup with innobackupex you have to use the --apply-log
78       and the path to the backup directory as an argument:
79
80          $ innobackupex --apply-log /path/to/BACKUP-DIR
81
82       and check the last line  of  the  output  for  a  confirmation  on  the
83       process:
84
85          150806 01:01:57  InnoDB: Shutdown completed; log sequence number 1609228
86          150806 01:01:57  innobackupex: completed OK!
87
88       If  it succeeded, innobackupex performed all operations needed, leaving
89       the data ready to use immediately.
90
91   Under the hood
92       innobackupex started the prepare process by reading  the  configuration
93       from the backup-my.cnf file in the backup directory.
94
95       After that, innobackupex replayed the committed transactions in the log
96       files (some transactions could have been  done  while  the  backup  was
97       being  done)  and  rolled back the uncommitted ones. Once this is done,
98       all the information lay in the tablespace (the InnoDB files),  and  the
99       log files are re-created.
100
101       This  implies  calling xtrabackup --prepare twice. More details of this
102       process are shown in the xtrabackup section.
103
104       Note that this preparation is not suited for  incremental  backups.  If
105       you  perform  it  on the base of an incremental backup, you will not be
106       able to "add" the increments. See incremental_backups_innobackupex.
107
108   Other options to consider
109   The --use-memory option
110       The preparing process can be speed up by using more memory  in  it.  It
111       depends  on  the  free  or available RAM on your system, it defaults to
112       100MB. In general, the more memory available to the process,  the  bet‐
113       ter.  The amount of memory used in the process can be specified by mul‐
114       tiples of bytes:
115
116          $ innobackupex --apply-log --use-memory=4G /path/to/BACKUP-DIR
117
118   Restoring a Full Backup with innobackupex
119       For convenience, innobackupex has a --copy-back option, which  performs
120       the restoration of a backup to the server's datadir
121
122          $ innobackupex --copy-back /path/to/BACKUP-DIR
123
124       It  will  copy all the data-related files back to the server's datadir,
125       determined by the server's my.cnf configuration file. You should  check
126       the last line of the output for a success message:
127
128          innobackupex: Finished copying back files.
129
130          111225 01:08:13  innobackupex: completed OK!
131
132       NOTE:
133          The   datadir   must   be  empty;  Percona  XtraBackup  innobackupex
134          --copy-back  option  will  not  copy  over  existing  files   unless
135          innobackupex --force-non-empty-directories option is specified. Also
136          it's important to note that MySQL  server  needs  to  be  shut  down
137          before  restore  is  performed.  You can't restore to a datadir of a
138          running mysqld instance (except when importing a partial backup).
139
140       As files' attributes will be preserved, in most cases you will need  to
141       change  the  files'  ownership  to  mysql  before starting the database
142       server, as they will be owned by the user who created the backup:
143
144          $ chown -R mysql:mysql /var/lib/mysql
145
146       Also note that all of these operations will be done as the user calling
147       innobackupex, you will need write permissions on the server's datadir.
148

OTHER TYPES OF BACKUPS

150   Incremental Backups with innobackupex
151       As  not  all  information  changes between each backup, the incremental
152       backup strategy uses this to reduce the storage needs and the  duration
153       of making a backup.
154
155       This  can  be  done because each InnoDB page has a log sequence number,
156       LSN, which acts as a version number of the entire database. Every  time
157       the database is modified, this number gets incremented.
158
159       An incremental backup copies all pages since a specific LSN.
160
161       Once the pages have been put together in their respective order, apply‐
162       ing the logs will recreate the  process  that  affected  the  database,
163       yielding the data at the moment of the most recently created backup.
164
165   Creating an Incremental Backups with innobackupex
166       First, you need to make a full backup as the BASE for subsequent incre‐
167       mental backups:
168
169          $ innobackupex /data/backups
170
171       This will create a timestamped  directory  in  /data/backups.  Assuming
172       that  the  backup  is  done  last  day  of  the month, BASEDIR would be
173       /data/backups/2013-03-31_23-01-18, for example.
174
175       NOTE:
176          You can use the innobackupex --no-timestamp option to override  this
177          behavior and the backup will be created in the given directory.
178
179       If you check at the xtrabackup-checkpoints file in BASE-DIR, you should
180       see something like:
181
182          backup_type = full-backuped
183          from_lsn = 0
184          to_lsn = 1291135
185
186       To create an incremental backup the next  day,  use  the  --incremental
187       option and provide the BASEDIR:
188
189          $ innobackupex --incremental /data/backups --incremental-basedir=BASEDIR
190
191       and  another timestamped directory will be created in /data/backups, in
192       this example, /data/backups/2013-04-01_23-01-18 containing  the  incre‐
193       mental backup. We will call this INCREMENTAL-DIR-1.
194
195       If  you  check at the xtrabackup-checkpoints file in INCREMENTAL-DIR-1,
196       you should see something like:
197
198          backup_type = incremental
199          from_lsn = 1291135
200          to_lsn = 1352113
201
202       Creating another incremental backup the next day will be analogous, but
203       this time the previous incremental one will be base:
204
205          $ innobackupex --incremental /data/backups --incremental-basedir=INCREMENTAL-DIR-1
206
207       yielding  (in  this example) /data/backups/2013-04-02_23-01-18. We will
208       use INCREMENTAL-DIR-2 instead for simplicity.
209
210       At this point, the  xtrabackup-checkpoints  file  in  INCREMENTAL-DIR-2
211       should contain something like:
212
213          backup_type = incremental
214          from_lsn = 1352113
215          to_lsn = 1358967
216
217       As it was said before, an incremental backup only copy pages with a LSN
218       greater than a specific value. Providing the LSN  would  have  produced
219       directories with the same data inside:
220
221          innobackupex --incremental /data/backups --incremental-lsn=1291135
222          innobackupex --incremental /data/backups --incremental-lsn=1358967
223
224       This  is  a  very  useful way of doing an incremental backup, since not
225       always the base or the last incremental will be available in  the  sys‐
226       tem.
227
228       WARNING:
229          This  procedure  only  affects  XtraDB or InnoDB-based tables. Other
230          tables with a different storage engine, e.g. MyISAM, will be  copied
231          entirely each time an incremental backup is performed.
232
233   Preparing an Incremental Backup with innobackupex
234       Preparing  incremental  backups is a bit different than full ones. This
235       is, perhaps, the stage where more attention is needed:
236
237          · First, only the committed transactions must be  replayed  on  each
238            backup.  This will merge the base full backup with the incremental
239            ones.
240
241          · Then, the uncommitted transaction must be rolled back in order  to
242            have a ready-to-use backup.
243
244       If  you  replay the committed transactions and rollback the uncommitted
245       ones on the base backup, you will not be able to  add  the  incremental
246       ones.  If  you  do this on an incremental one, you won't be able to add
247       data from that moment and the remaining increments.
248
249       Having this in mind, the procedure is very straight-forward  using  the
250       --redo-only option, starting with the base backup:
251
252          innobackupex --apply-log --redo-only BASE-DIR
253
254       You should see an output similar to:
255
256          120103 22:00:12 InnoDB: Shutdown completed; log sequence number 1291135
257          120103 22:00:12 innobackupex: completed OK!
258
259       Then,  the  first incremental backup can be applied to the base backup,
260       by issuing:
261
262          innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1
263
264       You should see an output similar to the previous one  but  with  corre‐
265       sponding LSN:
266
267          120103 22:08:43 InnoDB: Shutdown completed; log sequence number 1358967
268          120103 22:08:43 innobackupex: completed OK!
269
270       If  no  --incremental-dir is set, innobackupex will use the most recent
271       subdirectory created in the basedir.
272
273       At this moment, BASE-DIR contains the data up  to  the  moment  of  the
274       first incremental backup. Note that the full data will always be in the
275       directory of the base backup, as we are appending the increments to it.
276
277       Repeat the procedure with the second one:
278
279          innobackupex --apply-log BASE-DIR --incremental-dir=INCREMENTAL-DIR-2
280
281       If the "completed OK!" message was shown, the final data will be in the
282       base backup directory, BASE-DIR.
283
284       NOTE:
285          --redo-only  should be used when merging all incrementals except the
286          last  one.  That's  why  the  previous  line  doesn't  contain   the
287          --redo-only  option.  Even  if  the --redo-only was used on the last
288          step, backup would still be consistent but in that case server would
289          perform the rollback phase.
290
291       You  can use this procedure to add more increments to the base, as long
292       as you do it in the chronological order that the backups were done.  If
293       you  merge the incrementals in the wrong order, the backup will be use‐
294       less. If you have doubts about the order that they must be applied, you
295       can check the file xtrabackup_checkpoints at the directory of each one,
296       as shown in the beginning of this section.
297
298       Once you merge the base with all the increments, you can prepare it  to
299       roll back the uncommitted transactions:
300
301          innobackupex --apply-log BASE-DIR
302
303       Now  your  backup  is  ready to be used immediately after restoring it.
304       This preparation step is optional.  However,  if  you  restore  without
305       doing the prepare, the database server will begin to rollback uncommit‐
306       ted transactions, the same work it would do if a  crash  had  occurred.
307       This  results in delay as the database server starts, and you can avoid
308       the delay if you do the prepare.
309
310       Note that the iblog* files will not be created by innobackupex, if  you
311       want  them  to  be  created, use xtrabackup --prepare on the directory.
312       Otherwise, the files will be created by the server once started.
313
314   Restoring Incremental Backups with innobackupex
315       After preparing the incremental backups, the  base  directory  contains
316       the same as a full one. For restoring it you can use:
317
318          innobackupex --copy-back BASE-DIR
319
320       and  you  may  have  to  change  the  ownership  as detailed on restor‐
321       ing_a_backup_ibk.
322
323   Incremental Streaming Backups using xbstream and tar
324       Incremental streaming  backups  can  be  performed  with  the  xbstream
325       streaming  option. Currently backups are packed in custom xbstream for‐
326       mat. With this feature taking a BASE backup is needed as well.
327
328       Taking a base backup:
329
330          innobackupex /data/backups
331
332       Taking a local backup:
333
334          innobackupex --incremental --incremental-lsn=LSN-number --stream=xbstream ./ > incremental.xbstream
335
336       Unpacking the backup:
337
338          xbstream -x < incremental.xbstream
339
340       Taking a local backup and streaming it to the remote server and unpack‐
341       ing it:
342
343          innobackupex  --incremental --incremental-lsn=LSN-number --stream=xbstream ./ | /
344          ssh user@hostname " cat - | xbstream -x -C > /backup-dir/"
345
346   Partial Backups
347       Percona  XtraBackup  features partial backups, which means that you may
348       backup only some specific tables or databases. The tables you  back  up
349       must  be  in  separate  tablespaces,  as  a  result of being created or
350       altered after you  enabled  the  innodb_file_per_table  option  on  the
351       server.
352
353       There  is  only  one caveat about partial backups: do not copy back the
354       prepared backup. Restoring partial backups should be done by  importing
355       the  tables,  not by using the traditional --copy-back option. Although
356       there are some scenarios where restoring can be done  by  copying  back
357       the  files,  this may be lead to database inconsistencies in many cases
358       and it is not the recommended way to do it.
359
360   Creating Partial Backups
361       There are three ways of specifying which part of the whole data will be
362       backed up: regular expressions (--include), enumerating the tables in a
363       file (--tables-file) or providing a list of databases (--databases).
364
365   Using the --include option
366       The regular expression provided to this will  be  matched  against  the
367       fully  qualified  table  name, including the database name, in the form
368       databasename.tablename.
369
370       For example,
371
372          $ innobackupex --include='^mydatabase[.]mytable' /path/to/backup
373
374       The command above will create a timestamped directory  with  the  usual
375       files  that  innobackupex  creates,  but only the data files related to
376       the tables matched.
377
378       Note that this option is passed to xtrabackup --tables and  is  matched
379       against  each  table of each database, the directories of each database
380       will be created even if they are empty.
381
382   Using the --tables-file option
383       The text file provided (the path) to this option can  contain  multiple
384       table names, one per line,  in the databasename.tablename format.
385
386       For example,
387
388          $ echo "mydatabase.mytable" > /tmp/tables.txt
389          $ innobackupex --tables-file=/tmp/tables.txt /path/to/backup
390
391       The  command  above  will create a timestamped directory with the usual
392       files that innobackupex creates, but  only  containing  the  data-files
393       related to the tables specified in the file.
394
395       This  option  is  passed  to  xtrabackup  --tables-file and, unlike the
396       --tables option, only directories of databases of the  selected  tables
397       will be created.
398
399   Using the --databases option
400       This  option accepts either a space-separated list of the databases and
401       tables to backup - in the  databasename[.tablename] form -  or  a  file
402       containing the list at one element per line.
403
404       For example,
405
406          $ innobackupex --databases="mydatabase.mytable mysql" /path/to/backup
407
408       The  command  above  will create a timestamped directory with the usual
409       files that innobackupex creates, but  only  containing  the  data-files
410       related  to mytable in the mydatabase directory and the mysql directory
411       with the entire mysql database.
412
413   Preparing Partial Backups
414       For preparing partial backups, the procedure is analogous to  restoring
415       individual tables : apply the logs and use the --export option:
416
417          $ innobackupex --apply-log --export /path/to/partial/backup
418
419       You  may see warnings in the output about tables that don't exist. This
420       is because InnoDB -based engines stores its data dictionary inside  the
421       tablespace  files  besides  the .frm files. innobackupex will use xtra‐
422       backup to remove the missing tables (those who weren't selected in  the
423       partial backup) from the data dictionary in order to avoid future warn‐
424       ings or errors:
425
426          111225  0:54:06  InnoDB: Error: table 'mydatabase/mytablenotincludedinpartialb'
427          InnoDB: in InnoDB data dictionary has tablespace id 6,
428          InnoDB: but tablespace with that id or name does not exist. It will be removed from data dictionary.
429
430       You should also see the notification of the creation of a  file  needed
431       for  importing  (.exp  file)  for  each  table  included in the partial
432       backup:
433
434          xtrabackup: export option is specified.
435          xtrabackup: export metadata of table 'employees/departments' to file `.//departments.exp` (2 indexes)
436          xtrabackup:     name=PRIMARY, id.low=80, page=3
437          xtrabackup:     name=dept_name, id.low=81, page=4
438
439       Note that you can use  the  --export  option  with  --apply-log  to  an
440       already-prepared backup in order to create the .exp files.
441
442       Finally, check the for the confirmation message in the output:
443
444          111225 00:54:18  innobackupex: completed OK!
445
446   Restoring Partial Backups
447       Restoring  should be done by restoring individual tables in the partial
448       backup to the server.
449
450       It can also be done by copying back the prepared backup  to  a  "clean"
451       datadir (in that case, make sure to include the mysql database). System
452       database can be created with:
453
454          $ sudo mysql_install_db --user=mysql
455
456   Compact Backups
457       When doing the backup of InnoDB tables it's possible to omit  the  sec‐
458       ondary  index  pages.  This will make the backups more compact and this
459       way they will take less space on disk. The downside of this is that the
460       backup  prepare process takes longer as those secondary indexes need to
461       be recreated. Difference in backup size depends on the size of the sec‐
462       ondary indexes.
463
464       For example full backup taken without and with the --compact option:
465
466          #backup size without --compact
467          2.0G  2013-02-01_10-18-38
468
469          #backup size taken with --compact option
470          1.4G  2013-02-01_10-29-48
471
472       NOTE:
473          Compact  backups  are  not  supported  for system table space, so in
474          order to  work  correctly  innodb-file-per-table  option  should  be
475          enabled.
476
477       This feature was introduced in Percona XtraBackup 2.1.
478
479   Creating Compact Backups
480       To  make  a  compact  backup  innobackupex needs to be started with the
481       --compact option:
482
483          $ innobackupex --compact /data/backups
484
485       This will create a timestamped directory in /data/backups.
486
487       NOTE:
488          You can use the innobackupex --no-timestamp option to override  this
489          behavior and the backup will be created in the given directory.
490
491       If you check at the xtrabackup_checkpoints file in BASE-DIR, you should
492       see something like:
493
494          backup_type = full-backuped
495          from_lsn = 0
496          to_lsn = 2888984349
497          last_lsn = 2888984349
498          compact = 1
499
500       When --compact wasn't used compact value will be 0. This way it's  easy
501       to check if the backup contains the secondary index pages or not.
502
503   Preparing Compact Backups
504       Preparing  the compact require rebuilding the indexes as well. In order
505       to prepare the backup a new option  --rebuild-indexes  should  be  used
506       with --apply-logs:
507
508          $ innobackupex --apply-log --rebuild-indexes /data/backups/2013-02-01_10-29-48
509
510       Output,  beside  the  standard  innobackupex output, should contain the
511       information about indexes being rebuilt, like:
512
513          130201 10:40:20  InnoDB: Waiting for the background threads to start
514          Rebuilding indexes for table sbtest/sbtest1 (space id: 10)
515            Found index k_1
516            Dropping 1 index(es).
517            Rebuilding 1 index(es).
518          Rebuilding indexes for table sbtest/sbtest2 (space id: 11)
519            Found index k_1
520            Found index c
521            Found index k
522            Found index c_2
523            Dropping 4 index(es).
524            Rebuilding 4 index(es).
525
526       Since Percona XtraBackup has no information when applying an  incremen‐
527       tal  backup to a compact full one, on whether there will be more incre‐
528       mental backups applied to it later or not, rebuilding indexes needs  to
529       be  explicitly  requested  by  a  user whenever a full backup with some
530       incremental backups merged is ready to be restored. Rebuilding  indexes
531       unconditionally  on  every  incremental  backup merge is not an option,
532       since it is an expensive operation.
533
534       NOTE:
535          To process individual tables in parallel  when  rebuilding  indexes,
536          innobackupex  --rebuild-threads  option  can  be used to specify the
537          number of threads started by Percona XtraBackup when rebuilding sec‐
538          ondary   indexes   on  --apply-log  --rebuild-indexes.  Each  thread
539          rebuilds indexes for a single .ibd tablespace at a time.
540
541   Restoring Compact Backups
542       innobackupex has a --copy-back option, which performs  the  restoration
543       of a backup to the server's datadir
544
545          $ innobackupex --copy-back /path/to/BACKUP-DIR
546
547       It  will  copy all the data-related files back to the server's datadir,
548       determined by the server's my.cnf configuration file. You should  check
549       the last line of the output for a success message:
550
551          innobackupex: Finished copying back files.
552          130201 11:08:13  innobackupex: completed OK!
553
554   Other Reading
555       · Feature preview: Compact backups in Percona XtraBackup
556
557   Encrypted Backups
558       Percona  XtraBackup  has  implemented support for encrypted backups. It
559       can be used to encrypt/decrypt local or streaming backup with  xbstream
560       option  (streaming  tar  backups  are  not  supported)  in order to add
561       another layer of protection to the backups. Encryption is done with the
562       libgcrypt library.
563
564   Creating Encrypted Backups
565       To  make  an  encrypted  backup  following options need to be specified
566       (options --encrypt-key and --encrypt-key-file are  mutually  exclusive,
567       i.e. just one of them needs to be provided):
568
569          · --encryption=ALGORITHM   -  currently  supported  algorithms  are:
570            AES128, AES192 and AES256
571
572          · --encrypt-key=ENCRYPTION_KEY - proper  length  encryption  key  to
573            use.  It  is  not  recommended  to  use this option where there is
574            uncontrolled access to the machine as the command  line  and  thus
575            the key can be viewed as part of the process info.
576
577          · --encrypt-key-file=KEYFILE  - the name of a file where the raw key
578            of the appropriate length can be read from. The  file  must  be  a
579            simple  binary  (or text) file that contains exactly the key to be
580            used.
581
582       Both --encrypt-key option  and --encrypt-key-file option can be used to
583       specify  the  encryption key. Encryption key can be generated with com‐
584       mand like:
585
586          $ openssl rand -base64 24
587
588       Example output of that command should look like this:
589
590          GCHFLrDFVx6UAsRb88uLVbAVWbK+Yzfs
591
592       This value then can be used as the encryption key
593
594   Using the --encrypt-key option
595       Example of the innobackupex command using the --encrypt-key should look
596       like this
597
598          $ innobackupex --encrypt=AES256 --encrypt-key="GCHFLrDFVx6UAsRb88uLVbAVWbK+Yzfs" /data/backups
599
600   Using the --encrypt-key-file option
601       Example of the innobackupex command using the --encrypt-key-file should
602       look like this
603
604          $ innobackupex --encrypt=AES256 --encrypt-key-file=/data/backups/keyfile /data/backups
605
606       NOTE:
607          Depending on the text editor used for making the KEYFILE, text  file
608          in  some cases can contain the CRLF and this will cause the key size
609          to grow and thus making it invalid. Suggested way to do  this  would
610          be    to    create    the    file    with:    echo    -n   "GCHFLrD‐
611          FVx6UAsRb88uLVbAVWbK+Yzfs" > /data/backups/keyfile
612
613       Both  of  these  examples  will  create  a  timestamped  directory   in
614       /data/backups containing the encrypted backup.
615
616       NOTE:
617          You  can use the innobackupex --no-timestamp option to override this
618          behavior and the backup will be created in the given directory.
619
620   Optimizing the encryption process
621       Two new options have been introduced with the  encrypted  backups  that
622       can   be   used   to   speed  up  the  encryption  process.  These  are
623       --encrypt-threads    and    --encrypt-chunk-size.    By    using    the
624       --encrypt-threads  option  multiple threads can be specified to be used
625       for encryption in parallel. Option --encrypt-chunk-size can be used  to
626       specify  the  size (in bytes) of the working encryption buffer for each
627       encryption thread (default is 64K).
628
629   Decrypting Encrypted Backups
630       Backups can be decrypted with xbcrypt. Following one-liner can be  used
631       to encrypt the whole folder:
632
633          $ 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
634
635       Percona  XtraBackup  innobackupex --decrypt option has been implemented
636       that can be used to decrypt the backups:
637
638          $ innobackupex --decrypt=AES256 --encrypt-key="GCHFLrDFVx6UAsRb88uLVbAVWbK+Yzfs" /data/backups/2015-03-18_08-31-35/
639
640       Percona XtraBackup doesn't automatically remove the encrypted files. In
641       order  to  clean  up  the  backup  directory  users  should  remove the
642       *.xbcrypt files.
643
644       NOTE:
645          innobackupex --parallel can  be  used  with  innobackupex  --decrypt
646          option to decrypt multiple files simultaneously.
647
648       When the files have been decrypted backup can be prepared.
649
650   Preparing Encrypted Backups
651       After  the  backups  have been decrypted, they can be prepared the same
652       way as the standard full backups with the --apply-logs option:
653
654          $ innobackupex --apply-log /data/backups/2015-03-18_08-31-35/
655
656       NOTE:
657          Percona XtraBackup doesn't automatically remove the encrypted files.
658          In  order  to  clean up the backup directory users should remove the
659          *.xbcrypt files.
660
661   Restoring Encrypted Backups
662       innobackupex has a --copy-back option, which performs  the  restoration
663       of a backup to the server's datadir
664
665          $ innobackupex --copy-back /path/to/BACKUP-DIR
666
667       It  will  copy all the data-related files back to the server's datadir,
668       determined by the server's my.cnf configuration file. You should  check
669       the last line of the output for a success message:
670
671          innobackupex: Finished copying back files.
672          150318 11:08:13  innobackupex: completed OK!
673
674   Other Reading
675       · The Libgcrypt Reference Manual
676

ADVANCED FEATURES

678   Streaming and Compressing Backups
679       Streaming mode, supported by Percona XtraBackup, sends backup to STDOUT
680       in special tar or xbstream format  instead  of  copying  files  to  the
681       backup directory.
682
683       This  allows  you  to  use  other  programs to filter the output of the
684       backup, providing greater flexibility for storage of  the  backup.  For
685       example,  compression is achieved by piping the output to a compression
686       utility. One of the benefits of streaming backups and using Unix  pipes
687       is that the backups can be automatically encrypted.
688
689       To  use the streaming feature, you must use the --stream, providing the
690       format of the stream (tar or xbstream ) and where to store  the  tempo‐
691       rary files:
692
693          $ innobackupex --stream=tar /tmp
694
695       innobackupex starts xtrabackup in --log-stream mode in a child process,
696       and redirects its log to a temporary file. It  then  uses  xbstream  to
697       stream  all  of the data files to STDOUT, in a special xbstream format.
698       See ../xbstream/xbstream for details. After it finishes  streaming  all
699       of  the data files to STDOUT, it stops xtrabackup and streams the saved
700       log file too.
701
702       When compression is enabled, xtrabackup  compresses  all  output  data,
703       except  the  meta  and non-InnoDB files which are not compressed, using
704       the specified compression algorithm. The only currently supported algo‐
705       rithm  is  quicklz. The resulting files have the qpress archive format,
706       i.e. every *.qp file produced by xtrabackup is essentially  a  one-file
707       qpress archive and can be extracted and uncompressed by the qpress file
708       archiver which is available from Percona Software repositories.
709
710       Using xbstream as a stream option, backups can be copied and compressed
711       in  parallel  which  can  significantly speed up the backup process. In
712       case backups were  both  compressed  and  encrypted,  they'll  need  to
713       decrypted first in order to be uncompressed.
714
715   Examples using xbstream
716       Store the complete backup directly to a single file:
717
718          $ innobackupex --stream=xbstream /root/backup/ > /root/backup/backup.xbstream
719
720       To stream and compress the backup:
721
722          $ innobackupex --stream=xbstream --compress /root/backup/ > /root/backup/backup.xbstream
723
724       To unpack the backup to the /root/backup/ directory:
725
726          $ xbstream -x <  backup.xbstream -C /root/backup/
727
728       To send the compressed backup to another host and unpack it:
729
730          $ innobackupex --compress --stream=xbstream /root/backup/ | ssh user@otherhost "xbstream -x -C /root/backup/"
731
732   Examples using tar
733       Store the complete backup directly to a tar archive:
734
735          $ innobackupex --stream=tar /root/backup/ > /root/backup/out.tar
736
737       To send the tar archive to another host:
738
739          $ innobackupex --stream=tar ./ | ssh user@destination \ "cat - > /data/backups/backup.tar"
740
741       WARNING:
742          To  extract  Percona  XtraBackup's  archive you must use tar with -i
743          option:
744
745              $ tar -xizf backup.tar.gz
746
747       Compress with your preferred compression tool:
748
749          $ innobackupex --stream=tar ./ | gzip - > backup.tar.gz
750          $ innobackupex --stream=tar ./ | bzip2 - > backup.tar.bz2
751
752       Note that the streamed backup will need to be prepared before  restora‐
753       tion. Streaming mode does not prepare the backup.
754
755   Taking Backups in Replication Environments
756       There are options specific to back up from a replication slave.
757
758   The --slave-info option
759       This  option  is  useful when backing up a replication slave server. It
760       prints the binary log position and name of the master server.  It  also
761       writes  this  information to the xtrabackup_slave_info file as a CHANGE
762       MASTER statement.
763
764       This is useful for setting up a new slave for this master can be set up
765       by  starting  a  slave  server on this backup and issuing the statement
766       saved in the xtrabackup_slave_info file. More details of this procedure
767       can be found in replication_howto.
768
769   The --safe-slave-backup option
770       In  order  to  assure a consistent replication state, this option stops
771       the  slave  SQL  thread  and   wait   to   start   backing   up   until
772       Slave_open_temp_tables  in  SHOW  STATUS  is zero. If there are no open
773       temporary tables, the backup will take place, otherwise the SQL  thread
774       will  be  started and stopped until there are no open temporary tables.
775       The backup will fail if Slave_open_temp_tables  does  not  become  zero
776       after  --safe-slave-backup-timeout  seconds  (defaults to 300 seconds).
777       The slave SQL thread will be restarted when the backup finishes.
778
779       Using this option is always recommended  when  taking  backups  from  a
780       slave server.
781
782       WARNING:
783          Make sure your slave is a true replica of the master before using it
784          as a source  for  backup.  A  good  tool  to  validate  a  slave  is
785          pt-table-checksum.
786
787   Accelerating the backup process
788   Accelerating with --parallel copy and --compress-threads
789       When  performing  a  local backup or the streaming backup with xbstream
790       option, multiple files can be copied concurrently by using the --paral‐
791       lel  option.  This  option  specifies  the number of threads created by
792       xtrabackup to copy data files.
793
794       To take advantage of this option either the multiple tablespaces option
795       must  be  enabled (innodb_file_per_table) or the shared tablespace must
796       be stored in  multiple  ibdata  files  with  the  innodb_data_file_path
797       option.   Having multiple files for the database (or splitting one into
798       many) doesn't have a measurable impact on performance.
799
800       As this feature is implemented at a file level, concurrent file  trans‐
801       fer can sometimes increase I/O throughput when doing a backup on highly
802       fragmented data files, due to the overlap of a greater number of random
803       read requests. You should consider tuning the filesystem also to obtain
804       the maximum performance (e.g. checking fragmentation).
805
806       If the data is stored on a  single  file,  this  option  will  have  no
807       effect.
808
809       To use this feature, simply add the option to a local backup, for exam‐
810       ple:
811
812          $ innobackupex --parallel=4 /path/to/backup
813
814       By using the xbstream in streaming backups you can  additionally  speed
815       up the compression process by using the --compress-threads option. This
816       option specifies the number of threads created by xtrabackup  for   for
817       parallel data compression. The default value for this option is 1.
818
819       To use this feature, simply add the option to a local backup, for exam‐
820       ple
821
822          $ innobackupex --stream=xbstream --compress --compress-threads=4 ./ > backup.xbstream
823
824       Before applying logs, compressed files will need to be uncompressed.
825
826   Accelerating with --rsync option
827       In order to speed up the backup process and to minimize the time  FLUSH
828       TABLES  WITH  READ  LOCK  is  blocking  the writes, option innobackupex
829       --rsync should be used. When this  option  is  specified,  innobackupex
830       uses  rsync to copy all non-InnoDB files instead of spawning a separate
831       cp for each file, which can be much faster for  servers  with  a  large
832       number  of databases or tables. innobackupex will call the rsync twice,
833       once before the FLUSH TABLES WITH READ LOCK and once during to minimize
834       the  time the read lock is being held. During the second rsync call, it
835       will only synchronize the changes to non-transactional  data  (if  any)
836       since  the first call performed before the FLUSH TABLES WITH READ LOCK.
837       Note that Percona XtraBackup will use Backup locks where available as a
838       lightweight alternative to FLUSH TABLES WITH READ LOCK. This feature is
839       available in Percona Server 5.6+. Percona XtraBackup uses this automat‐
840       ically  to copy non-InnoDB data to avoid blocking DML queries that mod‐
841       ify InnoDB tables.
842
843       NOTE:
844          This option cannot be used together with innobackupex  --remote-host
845          or innobackupex --stream options.
846
847   Throttling backups with innobackupex
848       Although  innobackupex  does  not  block your database's operation, any
849       backup can add load to the system being backed up. On systems  that  do
850       not  have  much spare I/O capacity, it might be helpful to throttle the
851       rate at which innobackupex reads and writes InnoDB  data.  You  can  do
852       this with the --throttle option.
853
854       This option is passed directly to xtrabackup binary and only limits the
855       operations on the logs and files of InnoDB tables. It doesn't  have  an
856       effect  on  reading  or  writing  files  from tables with other storage
857       engine.
858
859       One way of checking the current I/O operations  at  a  system  is  with
860       iostat  command.  See  throttling_backups_xbk for details of how throt‐
861       tling works.
862
863       NOTE:
864          innobackupex --throttle option works only during the  backup  phase,
865          ie.  it will not work with innobackupex --apply-log and innobackupex
866          --copy-back options.
867
868       The --throttle option is similar to the --sleep option  in  mysqlbackup
869       and should be used instead of it, as --sleep will be ignored.
870
871   Restoring Individual Tables
872       In  server  versions  prior  to  5.6, it is not possible to copy tables
873       between servers by copying the files, even with  innodb_file_per_table.
874       However,  with the Percona XtraBackup, you can export individual tables
875       from any InnoDB database, and import  them  into  Percona  Server  with
876       XtraDB  or  MySQL 5.6 (The source doesn't have to be XtraDB or or MySQL
877       5.6, but the destination does). This  only  works  on  individual  .ibd
878       files,  and cannot export a table that is not contained in its own .ibd
879       file.
880
881       NOTE:
882          If you're running Percona Server  version  older  than  5.5.10-20.1,
883          variable    innodb_expand_import   should   be   used   instead   of
884          innodb_import_table_from_xtrabackup.
885
886   Exporting tables
887       Exporting is done in the preparation stage, not at the moment of creat‐
888       ing  the  backup.  Once  a  full backup is created, prepare it with the
889       --export option:
890
891          $ innobackupex --apply-log --export /path/to/backup
892
893       This will create for each InnoDB with its own tablespace  a  file  with
894       .exp extension. An output of this procedure would contain:
895
896          ..
897          xtrabackup: export option is specified.
898          xtrabackup: export metadata of table 'mydatabase/mytable' to file
899          `./mydatabase/mytable.exp` (1 indexes)
900          ..
901
902       Now you should see a .exp file in the target directory:
903
904          $ find /data/backups/mysql/ -name export_test.*
905          /data/backups/mysql/test/export_test.exp
906          /data/backups/mysql/test/export_test.ibd
907          /data/backups/mysql/test/export_test.cfg
908
909       These  three  files  are all you need to import the table into a server
910       running Percona Server with XtraDB or MySQL 5.6.
911
912       NOTE:
913          MySQL uses .cfg file which contains InnoDB dictionary dump  in  spe‐
914          cial  format.  This  format  is different from the .exp one which is
915          used in XtraDB for the same purpose. Strictly speaking, a .cfg  file
916          is  not  required  to  import  a  tablespace to MySQL 5.6 or Percona
917          Server 5.6. A tablespace will be imported successfully even if it is
918          from  another  server,  but  InnoDB will do schema validation if the
919          corresponding .cfg file is present in the same directory.
920
921       Each .exp (or .cfg)  file will be used for importing that table.
922
923       NOTE:
924          InnoDB does a slow shutdown (i.e. full purge + change buffer  merge)
925          on  --export,  otherwise  the tablespaces wouldn't be consistent and
926          thus couldn't be imported. All the usual performance  considerations
927          apply:  sufficient buffer pool (i.e. --use-memory, 100MB by default)
928          and fast enough storage, otherwise it can take a prohibitive  amount
929          of time for export to complete.
930
931   Importing tables
932       To  import  a  table to other server, first create a new table with the
933       same structure as the one that will be imported at that server:
934
935          OTHERSERVER|mysql> CREATE TABLE mytable (...) ENGINE=InnoDB;
936
937       then discard its tablespace:
938
939          OTHERSERVER|mysql> ALTER TABLE mydatabase.mytable DISCARD TABLESPACE;
940
941       After this, copy  mytable.ibd  and  mytable.exp  (  or  mytable.cfg  if
942       importing  to  MySQL  5.6)  files  to  database's  home, and import its
943       tablespace:
944
945          OTHERSERVER|mysql> ALTER TABLE mydatabase.mytable IMPORT TABLESPACE;
946
947       Once this is executed, data in the imported table will be available.
948
949   Point-In-Time recovery
950       Recovering up to particular moment in database's history  can  be  done
951       with innobackupex and the binary logs of the server.
952
953       Note  that  the  binary  log  contains the operations that modified the
954       database from a point in the past. You need a full datadir as  a  base,
955       and  then  you  can apply a series of operations from the binary log to
956       make the data match what it was at the point in time you want.
957
958       For taking the snapshot, we will use innobackupex for a full backup:
959
960          $ innobackupex /path/to/backup --no-timestamp
961
962       (the --no-timestamp option is for convenience in this example)  and  we
963       will prepare it to be ready for restoration:
964
965          $ innobackupex --apply-log /path/to/backup
966
967       For  more  details  on  these procedures, see creating_a_backup_ibk and
968       preparing_a_backup_ibk.
969
970       Now, suppose that time has passed, and you want to restore the database
971       to  a  certain point in the past, having in mind that there is the con‐
972       straint of the point where the snapshot was taken.
973
974       To find out what is the situation of binary logging in the server, exe‐
975       cute the following queries:
976
977          mysql> SHOW BINARY LOGS;
978          +------------------+-----------+
979          | Log_name         | File_size |
980          +------------------+-----------+
981          | mysql-bin.000001 |       126 |
982          | mysql-bin.000002 |      1306 |
983          | mysql-bin.000003 |       126 |
984          | mysql-bin.000004 |       497 |
985          +------------------+-----------+
986
987       and
988
989          mysql> SHOW MASTER STATUS;
990          +------------------+----------+--------------+------------------+
991          | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
992          +------------------+----------+--------------+------------------+
993          | mysql-bin.000004 |      497 |              |                  |
994          +------------------+----------+--------------+------------------+
995
996       The  first  query  will tell you which files contain the binary log and
997       the second one which file is currently being used  to  record  changes,
998       and  the  current position within it. Those files are stored usually in
999       the datadir (unless other location is  specified  when  the  server  is
1000       started with the --log-bin= option).
1001
1002       To find out the position of the snapshot taken, see the xtrabackup_bin‐
1003       log_info at the backup's directory:
1004
1005          $ cat /path/to/backup/xtrabackup_binlog_info
1006          mysql-bin.000003      57
1007
1008       This will tell you which file was used at moment of the backup for  the
1009       binary  log  and  its position. That position will be the effective one
1010       when you restore the backup:
1011
1012          $ innobackupex --copy-back /path/to/backup
1013
1014       As the restoration will not affect the binary log files (you  may  need
1015       to  adjust file permissions, see restoring_a_backup_ibk), the next step
1016       is extracting the queries from the binary log with mysqlbinlog starting
1017       from the position of the snapshot and redirecting it to a file
1018
1019          $ mysqlbinlog /path/to/datadir/mysql-bin.000003 /path/to/datadir/mysql-bin.000004 \
1020              --start-position=57 > mybinlog.sql
1021
1022       Note  that  if  you  have  multiple files for the binary log, as in the
1023       example, you have to extract the queries with  one  process,  as  shown
1024       above.
1025
1026       Inspect  the  file with the queries to determine which position or date
1027       corresponds to the point-in-time wanted. Once determined,  pipe  it  to
1028       the server. Assuming the point is 11-12-25 01:00:00:
1029
1030          $ mysqlbinlog /path/to/datadir/mysql-bin.000003 /path/to/datadir/mysql-bin.000004 \
1031              --start-position=57 --stop-datetime="11-12-25 01:00:00" | mysql -u root -p
1032
1033       and the database will be rolled forward up to that Point-In-Time.
1034
1035   Improved FLUSH TABLES WITH READ LOCK handling
1036       When  taking  backups, FLUSH TABLES WITH READ LOCK is being used before
1037       the non-InnoDB files are being backed up to ensure backup is being con‐
1038       sistent.  FLUSH  TABLES WITH READ LOCK can be run even though there may
1039       be a running query that has been executing  for  hours.  In  this  case
1040       everything  will be locked up in Waiting for table flush or Waiting for
1041       master to send event states. Killing the FLUSH TABLES  WITH  READ  LOCK
1042       does  not  correct  this issue either. In this case the only way to get
1043       the server operating normally again is to kill  off  the  long  running
1044       queries  that  blocked  it  to begin with. This means that if there are
1045       long running queries FLUSH TABLES WITH READ LOCK can get stuck, leaving
1046       server in read-only mode until waiting for these queries to complete.
1047
1048       NOTE:
1049          All  described  in  this section has no effect when backup locks are
1050          used. Percona XtraBackup will use Backup locks where available as  a
1051          lightweight alternative to FLUSH TABLES WITH READ LOCK. This feature
1052          is available in Percona Server 5.6+. Percona  XtraBackup  uses  this
1053          automatically  to copy non-InnoDB data to avoid blocking DML queries
1054          that modify InnoDB tables.
1055
1056       In order to prevent this from happening two  things  have  been  imple‐
1057       mented:
1058
1059       · innobackupex can wait for a good moment to issue the global lock.
1060
1061       · innobackupex can kill all or only SELECT queries which are preventing
1062         the global lock from being acquired
1063
1064   Waiting for queries to finish
1065       Good moment to issue a global lock is the moment when there are no long
1066       queries running. But waiting for a good moment to issue the global lock
1067       for extended period of time isn't  always  good  approach,  as  it  can
1068       extend  the time needed for backup to take place. To prevent innobacku‐
1069       pex from waiting to issue FLUSH TABLES WITH READ LOCK for too long, new
1070       option  has  been implemented: innobackupex --ftwrl-wait-timeout option
1071       can be used to limit the waiting time. If the good moment to issue  the
1072       lock  did  not  happen  during this time, innobackupex will give up and
1073       exit with an error message and backup will not be taken. Zero value for
1074       this option turns off the feature (which is default).
1075
1076       Another possibility is to specify the type of query to wait on. In this
1077       case innobackupex --ftwrl-wait-query-type. Possible values are all  and
1078       update.  When  all  is used innobackupex will wait for all long running
1079       queries  (execution  time   longer   than   allowed   by   innobackupex
1080       --ftwrl-wait-threshold)  to finish before running the FLUSH TABLES WITH
1081       READ  LOCK.  When  update   is   used   innobackupex   will   wait   on
1082       UPDATE/ALTER/REPLACE/INSERT queries to finish.
1083
1084       Although time needed for specific query to complete is hard to predict,
1085       we can assume that queries that are running for  a  long  time  already
1086       will  likely not be completed soon, and queries which are running for a
1087       short time will likely be completed shortly. innobackupex can  use  the
1088       value  of  innobackupex  --ftwrl-wait-threshold option to specify which
1089       query is long running and will likely block global lock for a while. In
1090       order  to use this option xtrabackup user should have PROCESS and SUPER
1091       privileges.
1092
1093   Killing the blocking queries
1094       Second option is to kill all the queries which prevent global lock from
1095       being  acquired.  In  this  case  all the queries which run longer than
1096       FLUSH TABLES WITH READ LOCK are possible blockers. Although all queries
1097       can  be  killed, additional time can be specified for the short running
1098       queries  to  complete.  This   can   be   specified   by   innobackupex
1099       --kill-long-queries-timeout  option. This option specifies the time for
1100       queries to complete, after  the  value  is  reached,  all  the  running
1101       queries will be killed. Default value is zero, which turns this feature
1102       off.
1103
1104       innobackupex --kill-long-query-type option can be used to  specify  all
1105       or  only  SELECT  queries  that  are  preventing global lock from being
1106       acquired. In order to use  this  option  xtrabackup  user  should  have
1107       PROCESS and SUPER privileges.
1108
1109   Options summary
1110       · --ftwrl-wait-timeout=N  (seconds)  -  how  long  to  wait  for a good
1111         moment. Default is 0, not to wait.
1112
1113       · --ftwrl-wait-query-type={all|update} - which long queries  should  be
1114         finished before FLUSH TABLES WITH READ LOCK is run. Default is all.
1115
1116       · --ftwrl-wait-threshold=N (seconds) - how long query should be running
1117         before we consider it long running and potential  blocker  of  global
1118         lock.
1119
1120       · --kill-long-queries-timeout=N  (seconds)  - how many time we give for
1121         queries to complete after FLUSH  TABLES  WITH  READ  LOCK  is  issued
1122         before start to kill. Default if 0, not to kill.
1123
1124       · --kill-long-query-type={all|select}  - which queries should be killed
1125         once kill-long-queries-timeout has expired.
1126
1127   Example
1128       Running the innobackupex with the following options:
1129
1130          $ innobackupex --ftwrl-wait-threshold=40 --ftwrl-wait-query-type=all --ftwrl-wait-timeout=180 --kill-long-queries-timeout=20 --kill-long-query-type=all /data/backups/
1131
1132       will cause innobackupex to spend no longer than 3 minutes  waiting  for
1133       all  queries older than 40 seconds to complete. After FLUSH TABLES WITH
1134       READ LOCK is issued, innobackupex will wait 20 seconds for lock  to  be
1135       acquired.  If lock is still not acquired after 20 seconds, it will kill
1136       all queries which are running longer that the FLUSH  TABLES  WITH  READ
1137       LOCK.
1138
1139   Store backup history on the server
1140       Percona  XtraBackup supports storing the backups history on the server.
1141       This feature was implemented in Percona XtraBackup 2.2. Storing  backup
1142       history  on the server was implemented to provide users with additional
1143       information about backups that are being taken. Backup history informa‐
1144       tion will be stored in the PERCONA_SCHEMA.XTRABACKUP_HISTORY table.
1145
1146       To  use  this  feature  three new innobackupex options have been imple‐
1147       mented:
1148
1149       · innobackupex --history =<name> : This option enables the history fea‐
1150         ture and allows the user to specify a backup series name that will be
1151         placed within the history record.
1152
1153       · innobackupex --incremental-history-name =<name> : This option  allows
1154         an  incremental  backup to be made based on a specific history series
1155         by name. innobackupex will search the history table looking  for  the
1156         most recent (highest to_lsn) backup in the series and take the to_lsn
1157         value to use as it's starting lsn. This is  mutually  exclusive  with
1158         innobackupex   --incremental-history-uuid,  innobackupex  --incremen‐
1159         tal-basedir and innobackupex --incremental-lsn options. If  no  valid
1160         LSN  can  be  found (no series by that name) innobackupex will return
1161         with an error.
1162
1163       · innobackupex --incremental-history-uuid =<uuid> : Allows an incremen‐
1164         tal  backup  to be made based on a specific history record identified
1165         by UUID. innobackupex will search the history table looking  for  the
1166         record  matching UUID and take the to_lsn value to use as it's start‐
1167         ing  LSN.  This  options  is  mutually  exclusive  with  innobackupex
1168         --incremental-basedir,  innobackupex --incremental-lsn and innobacku‐
1169         pex --incremental-history-name options. If no valid LSN can be  found
1170         (no  record by that UUID or missing to_lsn), innobackupex will return
1171         with an error.
1172
1173       NOTE:
1174          Backup that's currently being performed will NOT exist in the  xtra‐
1175          backup_history  table  within the resulting backup set as the record
1176          will not be added to that table until  after  the  backup  has  been
1177          taken.
1178
1179       If  you want access to backup history outside of your backup set in the
1180       case of some catastrophic event, you will  need  to  either  perform  a
1181       mysqldump,  partial  backup  or  SELECT  *  on  the history table after
1182       innobackupex completes and store the results with you backup set.
1183
1184   Privileges
1185       User performing the backup will need following privileges:
1186
1187       · CREATE      privilege      in      order      to      create      the
1188         PERCONA_SCHEMA.xtrabackup_history database and table.
1189
1190       · INSERT   privilege   in   order   to   add  history  records  to  the
1191         PERCONA_SCHEMA.xtrabackup_history table.
1192
1193       · SELECT privilege in  order  to  use  innobackupex  --incremental-his‐
1194         tory-name or innobackupex --incremental-history-uuid in order for the
1195         feature   to   look   up   the   innodb_to_lsn    values    in    the
1196         PERCONA_SCHEMA.xtrabackup_history table.
1197
1198   PERCONA_SCHEMA.XTRABACKUP_HISTORY table
1199       This  table contains the information about the previous server backups.
1200       Information about the backups will only be written if  the  backup  was
1201       taken with innobackupex --history option.
1202
1203                   ┌─────────────────┬────────────────────────────┐
1204                   │Column Name      │ Description                │
1205                   ├─────────────────┼────────────────────────────┤
1206                   │uuid             │ Unique backup id           │
1207                   ├─────────────────┼────────────────────────────┤
1208                   │name             │ User   provided   name  of │
1209                   │                 │ backup series.  There  may │
1210                   │                 │ be  multiple  entries with │
1211                   │                 │ the  same  name  used   to │
1212                   │                 │ identify  related  backups │
1213                   │                 │ in a series.               │
1214                   ├─────────────────┼────────────────────────────┤
1215                   │tool_name        │ Name of tool used to  take │
1216                   │                 │ backup                     │
1217                   ├─────────────────┼────────────────────────────┤
1218                   │tool_command     │ Exact  command  line given │
1219                   │                 │ to the tool  with  --pass‐ │
1220                   │                 │ word  and --encryption_key │
1221                   │                 │ obfuscated                 │
1222                   └─────────────────┴────────────────────────────┘
1223
1224
1225                   │tool_version     │ Version of  tool  used  to │
1226                   │                 │ take backup                │
1227                   ├─────────────────┼────────────────────────────┤
1228                   │ibbackup_version │ Version  of the xtrabackup │
1229                   │                 │ binary used to take backup │
1230                   ├─────────────────┼────────────────────────────┤
1231                   │server_version   │ Server  version  on  which │
1232                   │                 │ backup was taken           │
1233                   ├─────────────────┼────────────────────────────┤
1234                   │start_time       │ Time  at  the start of the │
1235                   │                 │ backup                     │
1236                   ├─────────────────┼────────────────────────────┤
1237                   │end_time         │ Time at  the  end  of  the │
1238                   │                 │ backup                     │
1239                   ├─────────────────┼────────────────────────────┤
1240                   │lock_time        │ Amount  of  time,  in sec‐ │
1241                   │                 │ onds,  spent  calling  and │
1242                   │                 │ holding  locks  for  FLUSH 
1243                   │                 │ TABLES WITH READ LOCK      
1244                   ├─────────────────┼────────────────────────────┤
1245                   │binlog_pos       │ Binlog file  and  position │
1246                   │                 │ at  end  of  FLUSH  TABLES 
1247                   │                 │ WITH READ LOCK             
1248                   ├─────────────────┼────────────────────────────┤
1249                   │innodb_from_lsn  │ LSN at beginning of backup │
1250                   │                 │ which   can   be  used  to │
1251                   │                 │ determine prior backups    │
1252                   ├─────────────────┼────────────────────────────┤
1253                   │innodb_to_lsn    │ LSN at end of backup which │
1254                   │                 │ can  be used as the start‐ │
1255                   │                 │ ing  lsn  for   the   next │
1256                   │                 │ incremental                │
1257                   ├─────────────────┼────────────────────────────┤
1258                   │partial          │ Is  this a partial backup, │
1259                   │                 │ if N that means that  it's │
1260                   │                 │ the full backup            │
1261                   ├─────────────────┼────────────────────────────┤
1262                   │incremental      │ Is   this  an  incremental │
1263                   │                 │ backup                     │
1264                   ├─────────────────┼────────────────────────────┤
1265                   │format           │ Description of result for‐ │
1266                   │                 │ mat (file, tar, xbstream)  │
1267                   ├─────────────────┼────────────────────────────┤
1268                   │compact          │ Is this a compact backup   │
1269                   ├─────────────────┼────────────────────────────┤
1270                   │compressed       │ Is   this   a   compressed │
1271                   │                 │ backup                     │
1272                   ├─────────────────┼────────────────────────────┤
1273                   │encrypted        │ Is   this   an   encrypted │
1274                   │                 │ backup                     │
1275                   └─────────────────┴────────────────────────────┘
1276
1277   Limitations
1278       · innobackupex --history option must be specified only on the innoback‐
1279         upex command line and not within a configuration file in order to  be
1280         effective.
1281
1282       · innobackupex  --incremental-history-name and innobackupex --incremen‐
1283         tal-history-uuid options must be specified only on  the  innobackupex
1284         command  line  and  not  within  a  configuration file in order to be
1285         effective.
1286

IMPLEMENTATION

1288   How innobackupex Works
1289       From Percona XtraBackup version 2.3 innobackupex is has been  rewritten
1290       in  C  and set up as a symlink to the xtrabackup. innobackupex supports
1291       all features and syntax as 2.2 version did, but it  is  now  deprecated
1292       and will be removed in next major release. Syntax for new features will
1293       not be added to the innobackupex, only to the xtrabackup.
1294
1295       The following describes the rationale behind innobackupex actions.
1296
1297   Making a Backup
1298       If no mode is specified, innobackupex will assume the backup mode.
1299
1300       By default, it starts xtrabackup with the --suspend-at-end option,  and
1301       lets  it  copy  the  InnoDB  data files. When xtrabackup finishes that,
1302       innobackupex sees it create the xtrabackup_suspended_2  file  and  exe‐
1303       cutes  FLUSH  TABLES  WITH  READ LOCK. xtrabackup will use Backup locks
1304       where available as a lightweight alternative to FLUSH TABLES WITH  READ
1305       LOCK.  This  feature is available in Percona Server 5.6+. Percona Xtra‐
1306       Backup uses this automatically to copy non-InnoDB data to avoid  block‐
1307       ing  DML  queries that modify InnoDB tables. Then it begins copying the
1308       rest of the files.
1309
1310       innobackupex will then check MySQL variables to  determine  which  fea‐
1311       tures  are  supported  by  server.  Special  interest are backup locks,
1312       changed page bitmaps, GTID mode, etc.  If  everything  goes  well,  the
1313       binary is started as a child process.
1314
1315       innobackupex  will wait for slaves in a replication setup if the option
1316       --safe-slave-backup is set and will flush all tables  with  READ  LOCK,
1317       preventing  all  MyISAM tables from writing (unless option --no-lock is
1318       specified).
1319
1320       NOTE:
1321          Locking is done only for MyISAM and  other  non-InnoDB  tables,  and
1322          only  after  Percona  XtraBackup  is  finished  backing  up all Inn‐
1323          oDB/XtraDB data and logs. Percona XtraBackup will use  Backup  locks
1324          where  available  as  a lightweight alternative to FLUSH TABLES WITH
1325          READ LOCK. This feature is available in Percona Server 5.6+. Percona
1326          XtraBackup  uses this automatically to copy non-InnoDB data to avoid
1327          blocking DML queries that modify InnoDB tables.
1328
1329       Once this is done, the backup of the files will begin. It  will  backup
1330       .frm,  .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV, .par,  and
1331       .opt files.
1332
1333       When all the files are backed up, it resumes ibbackup and wait until it
1334       finishes copying the transactions done while the backup was done. Then,
1335       the  tables  are  unlocked,  the  slave  is  started  (if  the   option
1336       --safe-slave-backup  was  used)  and  the connection with the server is
1337       closed. Then, it removes the xtrabackup_suspended_2  file  and  permits
1338       xtrabackup to exit.
1339
1340       It   will  also  create  the  following  files  in the directory of the
1341       backup:
1342
1343       xtrabackup_checkpoints
1344              containing the LSN and the type of backup;
1345
1346       xtrabackup_binlog_info
1347              containing the position of the binary log at the moment of back‐
1348              ing up;
1349
1350       xtrabackup_binlog_pos_innodb
1351              containing the position of the binary log at the moment of back‐
1352              ing up relative to InnoDB transactions;
1353
1354       xtrabackup_slave_info
1355              containing the MySQL binlog position of the master server  in  a
1356              replication  setup  via  SHOW  SLAVE  STATUS if the --slave-info
1357              option is passed;
1358
1359       backup-my.cnf
1360              containing only the my.cnf options required for the backup.  For
1361              example,  innodb_data_file_path, innodb_log_files_in_group, inn‐
1362              odb_log_file_size, innodb_fast_checksum, innodb_page_size,  inn‐
1363              odb_log_block_size;
1364
1365       xtrabackup_binary
1366              containing the binary used for the backup;
1367
1368       mysql-stderr
1369              containing the STDERR of mysqld during the process and
1370
1371       mysql-stdout
1372              containing the STDOUT of the server.
1373
1374       Finally,  the  binary  log  position  will  be  printed  to  STDERR and
1375       innobackupex will exit returning 0 if all went OK.
1376
1377       Note that the STDERR of innobackupex is not written in  any  file.  You
1378       will have to redirect it to a file, e.g., innobackupex OPTIONS 2> back‐
1379       upout.log.
1380
1381   Restoring a backup
1382       To restore a backup with innobackupex the --copy-back  option  must  be
1383       used.
1384
1385       innobackupex  will  read  from  the  my.cnf the variables datadir, inn‐
1386       odb_data_home_dir, innodb_data_file_path, innodb_log_group_home_dir and
1387       check that the directories exist.
1388
1389       It  will copy the MyISAM tables, indexes, etc. (.frm, .MRG, .MYD, .MYI,
1390       .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV, par and .opt files)  first,  InnoDB
1391       tables  and  indexes  next  and the log files at last. It will preserve
1392       file's attributes when copying them, you may have to change the  files'
1393       ownership to mysql before starting the database server, as they will be
1394       owned by the user who created the backup.
1395
1396       Alternatively, the --move-back option may be used to restore a  backup.
1397       This  option  is  similar  to --copy-back with the only difference that
1398       instead of copying files it moves them to their  target  locations.  As
1399       this  option  removes backup files, it must be used with caution. It is
1400       useful in cases when there is not enough free disk space to  hold  both
1401       data files and their backup copies.
1402

REFERENCES

1404   The innobackupex Option Reference
1405       This  page documents all of the command-line options for the innobacku‐
1406       pex.
1407
1408   Options
1409       --apply-log
1410              Prepare a backup in BACKUP-DIR by applying the  transaction  log
1411              file  named  xtrabackup_logfile  located  in the same directory.
1412              Also, create new transaction logs. The InnoDB  configuration  is
1413              read  from  the  file backup-my.cnf created by innobackupex when
1414              the backup was made. innobackupex --apply-log uses  InnoDB  con‐
1415              figuration    from    backup-my.cnf    by   default,   or   from
1416              --defaults-file, if specified. InnoDB configuration in this con‐
1417              text  means  server variables that affect data format, i.e. inn‐
1418              odb_page_size,  innodb_log_block_size,   etc.   Location-related
1419              variables,     like     innodb_log_group_home_dir     or    inn‐
1420              odb_data_file_path are always ignored by --apply-log, so prepar‐
1421              ing a backup always works with data files from the backup direc‐
1422              tory, rather than any external ones.
1423
1424       --backup-locks
1425              This option controls if backup locks should be used  instead  of
1426              FLUSH  TABLES WITH READ LOCK on the backup stage. The option has
1427              no effect when backup locks are not  supported  by  the  server.
1428              This    option    is    enabled   by   default,   disable   with
1429              --no-backup-locks.
1430
1431       --close-files
1432              Do not keep files opened. This  option  is  passed  directly  to
1433              xtrabackup. When xtrabackup opens tablespace it normally doesn't
1434              close its file handle in order to handle the DDL operations cor‐
1435              rectly. However, if the number of tablespaces is really huge and
1436              can not fit into any limit, there is an  option  to  close  file
1437              handles once they are no longer accessed. Percona XtraBackup can
1438              produce inconsistent backups with this option  enabled.  Use  at
1439              your own risk.
1440
1441       --compact
1442              Create  a compact backup with all secondary index pages omitted.
1443              This option is passed directly to  xtrabackup.   See  the  xtra‐
1444              backup documentation for details.
1445
1446       --compress
1447              This  option  instructs  xtrabackup to compress backup copies of
1448              InnoDB data files. It is passed directly to the xtrabackup child
1449              process. See the xtrabackup documentation for details.
1450
1451       --compress-threads=#
1452              This  option specifies the number of worker threads that will be
1453              used for parallel compression. It  is  passed  directly  to  the
1454              xtrabackup  child  process. See the xtrabackup documentation for
1455              details.
1456
1457       --compress-chunk-size=#
1458              This option specifies the size of the  internal  working  buffer
1459              for  each  compression  thread,  measured in bytes. It is passed
1460              directly to the xtrabackup child process. The default  value  is
1461              64K. See the xtrabackup documentation for details.
1462
1463       --copy-back
1464              Copy  all  the files in a previously made backup from the backup
1465              directory  to  their  original  locations.  Percona   XtraBackup
1466              innobackupex  --copy-back  option  will  not  copy over existing
1467              files unless innobackupex  --force-non-empty-directories  option
1468              is specified.
1469
1470       --databases=LIST
1471              This  option  specifies  the list of databases that innobackupex
1472              should back up. The option accepts a string argument or path  to
1473              file that contains the list of databases to back up. The list is
1474              of  the  form  "databasename1[.table_name1]   databasename2[.ta‐
1475              ble_name2]  .  .  .". If this option is not specified, all data‐
1476              bases containing MyISAM and InnoDB tables  will  be  backed  up.
1477              Please  make  sure  that  --databases contains all of the InnoDB
1478              databases and tables, so that all of the  innodb.frm  files  are
1479              also backed up. In case the list is very long, this can be spec‐
1480              ified in a file, and the full path of the file can be  specified
1481              instead of the list. (See option --tables-file.)
1482
1483       --decompress
1484              Decompresses all files with the .qp extension in a backup previ‐
1485              ously made with the --compress option. The innobackupex --paral‐
1486              lel  option  will  allow  multiple  files to be decrypted and/or
1487              decompressed simultaneously. In order to decompress, the  qpress
1488              utility  MUST  be installed and accessible within the path. Per‐
1489              cona XtraBackup  doesn't  automatically  remove  the  compressed
1490              files.  In  order  to clean up the backup directory users should
1491              remove the *.qp files manually.
1492
1493       --decrypt=ENCRYPTION-ALGORITHM
1494              Decrypts all files with the .xbcrypt extension in a backup  pre‐
1495              viously  made with --encrypt option. The innobackupex --parallel
1496              option will allow multiple files to be decrypted  and/or  decom‐
1497              pressed simultaneously.
1498
1499       --defaults-file=[MY.CNF]
1500              This  option  accepts a string argument that specifies what file
1501              to read the default MySQL options from. Must  be  given  as  the
1502              first option on the command-line.
1503
1504       --defaults-extra-file=[MY.CNF]
1505              This  option specifies what extra file to read the default MySQL
1506              options from before the standard defaults-file. Must be given as
1507              the first option on the command-line.
1508
1509       --defaults-group=GROUP-NAME
1510              This  option  accepts a string argument that specifies the group
1511              which should be read from the configuration file. This is needed
1512              if  you  use  mysqld_multi.  This  can  also be used to indicate
1513              groups other than mysqld and xtrabackup.
1514
1515       --encrypt=ENCRYPTION_ALGORITHM
1516              This option instructs xtrabackup to  encrypt  backup  copies  of
1517              InnoDB  data  files using the algorithm specified in the ENCRYP‐
1518              TION_ALGORITHM. It is passed directly to  the  xtrabackup  child
1519              process. See the xtrabackup documentation for more details.
1520
1521       --encrypt-key=ENCRYPTION_KEY
1522              This option instructs xtrabackup to use the given ENCRYPTION_KEY
1523              when using the --encrypt option. It is passed  directly  to  the
1524              xtrabackup  child  process. See the xtrabackup documentation for
1525              more details.
1526
1527       --encrypt-key-file=ENCRYPTION_KEY_FILE
1528              This option instructs  xtrabackup  to  use  the  encryption  key
1529              stored in the given ENCRYPTION_KEY_FILE when using the --encrypt
1530              option. It is passed directly to the xtrabackup  child  process.
1531              See the xtrabackup documentation for more details.
1532
1533       --encrypt-threads=#
1534              This  option specifies the number of worker threads that will be
1535              used for parallel encryption. It is passed directly to the xtra‐
1536              backup  child process. See the xtrabackup documentation for more
1537              details.
1538
1539       --encrypt-chunk-size=#
1540              This option specifies the size of the  internal  working  buffer
1541              for  each  encryption  thread,  measured  in bytes. It is passed
1542              directly to the xtrabackup child  process.  See  the  xtrabackup
1543              documentation for more details.
1544
1545       --export
1546              This option is passed directly to xtrabackup --export option. It
1547              enables exporting individual  tables  for  import  into  another
1548              server. See the xtrabackup documentation for details.
1549
1550       --extra-lsndir=DIRECTORY
1551              This  option accepts a string argument that specifies the direc‐
1552              tory in which to save an extra  copy  of  the  xtrabackup_check‐
1553              points   file.   It   is   passed   directly   to   xtrabackup's
1554              --extra-lsndir option.  See  the  xtrabackup  documentation  for
1555              details.
1556
1557       --force-non-empty-directories
1558              When  specified,  it  makes  innobackupex  --copy-back option or
1559              innobackupex --move-back  option  transfer  files  to  non-empty
1560              directories.   No   existing   files  will  be  overwritten.  If
1561              --copy-back or --move-back has to copy a file  from  the  backup
1562              directory  which already exists in the destination directory, it
1563              will still fail with an error.
1564
1565       --galera-info
1566              This options creates the xtrabackup_galera_info file which  con‐
1567              tains  the  local  node  state at the time of the backup. Option
1568              should  be   used   when   performing   the   backup   of   Per‐
1569              cona-XtraDB-Cluster. Has no effect when backup locks are used to
1570              create the backup.
1571
1572       --help This option displays a help screen and exits.
1573
1574       --history=NAME
1575              This option enables the tracking of backup history in  the  PER‐
1576              CONA_SCHEMA.xtrabackup_history table. An optional history series
1577              name may be specified that  will  be  placed  with  the  history
1578              record for the current backup being taken.
1579
1580       --host=HOST
1581              This option accepts a string argument that specifies the host to
1582              use when connecting to the database server with  TCP/IP.  It  is
1583              passed  to the mysql child process without alteration. See mysql
1584              --help for details.
1585
1586       --ibbackup=IBBACKUP-BINARY
1587              This option specifies which xtrabackup binary  should  be  used.
1588              The  option accepts a string argument. IBBACKUP-BINARY should be
1589              the command used to run Percona XtraBackup. The  option  can  be
1590              useful  if  the  xtrabackup binary is not in your search path or
1591              working directory. If this option is not specified, innobackupex
1592              attempts to determine the binary to use automatically.
1593
1594       --include=REGEXP
1595              This  option is a regular expression to be matched against table
1596              names in databasename.tablename format. It is passed directly to
1597              xtrabackup's xtrabackup --tables option. See the xtrabackup doc‐
1598              umentation for details.
1599
1600       --incremental
1601              This option tells xtrabackup to create  an  incremental  backup,
1602              rather  than  a  full  one. It is passed to the xtrabackup child
1603              process. When this option is specified, either --incremental-lsn
1604              or --incremental-basedir can also be given. If neither option is
1605              given, option --incremental-basedir is passed to  xtrabackup  by
1606              default,  set  to  the first timestamped backup directory in the
1607              backup base directory.
1608
1609       --incremental-basedir=DIRECTORY
1610              This option accepts a string argument that specifies the  direc‐
1611              tory containing the full backup that is the base dataset for the
1612              incremental backup. It is used with the --incremental option.
1613
1614       --incremental-dir=DIRECTORY
1615              This option accepts a string argument that specifies the  direc‐
1616              tory where the incremental backup will be combined with the full
1617              backup  to  make  a  new  full  backup.  It  is  used  with  the
1618              --incremental option.
1619
1620       --incremental-history-name=NAME
1621              This  option  specifies  the name of the backup series stored in
1622              the PERCONA_SCHEMA.xtrabackup_history history record to base  an
1623              incremental  backup  on. Percona Xtrabackup will search the his‐
1624              tory table looking for the most recent (highest  innodb_to_lsn),
1625              successful backup in the series and take the to_lsn value to use
1626              as the starting lsn for the incremental  backup.  This  will  be
1627              mutually    exclusive   with   innobackupex   --incremental-his‐
1628              tory-uuid,:option:innobackupex     --incremental-basedir     and
1629              innobackupex --incremental-lsn. If no valid lsn can be found (no
1630              series by that name, no successful backups by that  name)  xtra‐
1631              backup   will  return  with  an  error.  It  is  used  with  the
1632              innobackupex --incremental option.
1633
1634       --incremental-history-uuid=UUID
1635              This option specifies the UUID of the  specific  history  record
1636              stored  in  the  PERCONA_SCHEMA.xtrabackup_history  to  base  an
1637              incremental   backup   on.    innobackupex    --incremental-his‐
1638              tory-name,:optionL`innobackupex    --incremental-basedir`    and
1639              innobackupex --incremental-lsn. If no valid lsn can be found (no
1640              success  record  with  that uuid) xtrabackup will return with an
1641              error. It is used with the innobackupex --incremental option.
1642
1643       --incremental-lsn=LSN
1644              This option accepts a string argument  that  specifies  the  log
1645              sequence  number  (LSN) to use for the incremental backup. It is
1646              used with the --incremental option. It is used instead of speci‐
1647              fying  --incremental-basedir. For databases created by MySQL and
1648              Percona Server 5.0-series versions, specify the  as  two  32-bit
1649              integers  in  high:low  format. For databases created in 5.1 and
1650              later, specify the LSN as a single 64-bit integer.
1651
1652       --kill-long-queries-timeout=SECONDS
1653              This option specifies the number of seconds  innobackupex  waits
1654              between  starting  FLUSH TABLES WITH READ LOCK and killing those
1655              queries that  block  it.  Default  is  0  seconds,  which  means
1656              innobackupex  will  not attempt to kill any queries. In order to
1657              use this option xtrabackup user should have  PROCESS  and  SUPER
1658              privileges.  Where  supported  (Percona  Server 5.6+) xtrabackup
1659              will automatically use Backup Locks as a lightweight alternative
1660              to  FLUSH TABLES WITH READ LOCK to copy non-InnoDB data to avoid
1661              blocking DML queries that modify InnoDB tables.
1662
1663       --kill-long-query-type=all|select
1664              This option specifies which types of queries should be killed to
1665              unblock the global lock. Default is "all".
1666
1667       --ftwrl-wait-timeout=SECONDS
1668              This  option  specifies time in seconds that innobackupex should
1669              wait for queries that would block FLUSH TABLES  WITH  READ  LOCK
1670              before  running  it.  If  there  are still such queries when the
1671              timeout expires, innobackupex terminates with an error.  Default
1672              is  0,  in  which case innobackupex does not wait for queries to
1673              complete and starts FLUSH TABLES  WITH  READ  LOCK  immediately.
1674              Where  supported (Percona Server 5.6+) xtrabackup will automati‐
1675              cally use Backup Locks as a  lightweight  alternative  to  FLUSH
1676              TABLES  WITH READ LOCK to copy non-InnoDB data to avoid blocking
1677              DML queries that modify InnoDB tables.
1678
1679       --ftwrl-wait-threshold=SECONDS
1680              This option specifies the query run time threshold which is used
1681              by  innobackupex  to detect long-running queries with a non-zero
1682              value of innobackupex --ftwrl-wait-timeout.  FLUSH  TABLES  WITH
1683              READ  LOCK is not started until such long-running queries exist.
1684              This option has no effect if --ftwrl-wait-timeout is 0.  Default
1685              value is 60 seconds. Where supported (Percona Server 5.6+) xtra‐
1686              backup will automatically use  Backup  Locks  as  a  lightweight
1687              alternative  to  FLUSH  TABLES WITH READ LOCK to copy non-InnoDB
1688              data to avoid blocking DML queries that modify InnoDB tables.
1689
1690       --ftwrl-wait-query-type=all|update
1691              This option specifies which types of queries are allowed to com‐
1692              plete before innobackupex will issue the global lock. Default is
1693              all.
1694
1695       --log-copy-interval=#
1696              This option specifies time interval between checks done  by  log
1697              copying thread in milliseconds.
1698
1699       --move-back
1700              Move  all  the files in a previously made backup from the backup
1701              directory to their original locations. As  this  option  removes
1702              backup files, it must be used with caution.
1703
1704       --no-lock
1705              Use  this  option  to  disable table lock with FLUSH TABLES WITH
1706              READ LOCK. Use it only if ALL your tables are InnoDB and you  DO
1707              NOT  CARE  about  the  binary  log  position of the backup. This
1708              option shouldn't be used if there are any DDL  statements  being
1709              executed  or  if  any updates are happening on non-InnoDB tables
1710              (this includes the system MyISAM tables in the mysql  database),
1711              otherwise  it  could  lead to an inconsistent backup. Where sup‐
1712              ported (Percona Server 5.6+) xtrabackup will  automatically  use
1713              Backup  Locks  as a lightweight alternative to FLUSH TABLES WITH
1714              READ LOCK to copy non-InnoDB data to avoid blocking DML  queries
1715              that  modify  InnoDB  tables.   If  you  are  considering to use
1716              --no-lock because your backups are failing to acquire the  lock,
1717              this  could be because of incoming replication events preventing
1718              the lock from succeeding. Please try  using  --safe-slave-backup
1719              to  momentarily stop the replication slave thread, this may help
1720              the backup to succeed and you then don't need to resort to using
1721              this   option.    xtrabackup_binlog_info  is  not  created  when
1722              --no-lock option is used (because  SHOW  MASTER  STATUS  may  be
1723              inconsistent),  but  under  certain  conditions  xtrabackup_bin‐
1724              log_pos_innodb can be used  instead  to  get  consistent  binlog
1725              coordinates as described in working_with_binlogs.
1726
1727       --no-timestamp
1728              This  option prevents creation of a time-stamped subdirectory of
1729              the BACKUP-ROOT-DIR given on the command line. When it is speci‐
1730              fied, the backup is done in BACKUP-ROOT-DIR instead.
1731
1732       --no-version-check
1733              This  option  disables the version check which is enabled by the
1734              --version-check option.
1735
1736       --parallel=NUMBER-OF-THREADS
1737              This option accepts an integer argument that specifies the  num‐
1738              ber  of  threads the xtrabackup child process should use to back
1739              up files concurrently.  Note that  this  option  works  on  file
1740              level,  that  is,  if  you have several .ibd files, they will be
1741              copied in parallel. If your tables are stored together in a sin‐
1742              gle  tablespace  file,  it will have no effect. This option will
1743              allow multiple files to be decrypted and/or decompressed  simul‐
1744              taneously.  In  order  to decompress, the qpress utility MUST be
1745              installed and accessable within  the  path.  This  process  will
1746              remove  the  original  compressed/encrypted  files and leave the
1747              results in the same location. It is  passed  directly  to  xtra‐
1748              backup's  xtrabackup --parallel option. See the xtrabackup docu‐
1749              mentation for details
1750
1751       --password=PASSWORD
1752              This option accepts a string argument specifying the password to
1753              use  when  connecting to the database. It is passed to the mysql
1754              child process without alteration. See mysql --help for details.
1755
1756       --port=PORT
1757              This option accepts a string argument that specifies the port to
1758              use  when  connecting  to the database server with TCP/IP. It is
1759              passed to the mysql child process. It is  passed  to  the  mysql
1760              child process without alteration. See mysql --help for details.
1761
1762       --rebuild-indexes
1763              This  option  only  has  effect  when  used  together  with  the
1764              --apply-log option and is passed directly  to  xtrabackup.  When
1765              used,  makes  xtrabackup  rebuild  all  secondary  indexes after
1766              applying the log. This option is normally used to  prepare  com‐
1767              pact backups. See the xtrabackup documentation for more informa‐
1768              tion.
1769
1770       --rebuild-threads=NUMBER-OF-THREADS
1771              This  option  only  has  effect  when  used  together  with  the
1772              --apply-log  and --rebuild-indexes option and is passed directly
1773              to xtrabackup. When used, xtrabackup  processes  tablespaces  in
1774              parallel  with  the  specified number of threads when rebuilding
1775              indexes. See the xtrabackup documentation for more information.
1776
1777       --redo-only
1778              This option should be used when preparing the base  full  backup
1779              and  when  merging  all  incrementals except the last one. It is
1780              passed  directly  to  xtrabackup's  xtrabackup  --apply-log-only
1781              option.  This forces xtrabackup to skip the "rollback" phase and
1782              do a "redo" only. This is necessary  if  the  backup  will  have
1783              incremental changes applied to it later. See the xtrabackup doc‐
1784              umentation for details.
1785
1786       --rsync
1787              Uses the rsync utility to optimize local  file  transfers.  When
1788              this  option  is  specified, innobackupex uses rsync to copy all
1789              non-InnoDB files instead of spawning  a  separate  cp  for  each
1790              file,  which  can be much faster for servers with a large number
1791              of databases or tables.  This option  cannot  be  used  together
1792              with --stream.
1793
1794       --safe-slave-backup
1795              When specified, innobackupex will stop the slave SQL thread just
1796              before running FLUSH TABLES WITH READ LOCK  and  wait  to  start
1797              backup  until  Slave_open_temp_tables in SHOW STATUS is zero. If
1798              there are no open temporary tables, the backup will take  place,
1799              otherwise the SQL thread will be started and stopped until there
1800              are  no  open  temporary  tables.  The  backup  will   fail   if
1801              Slave_open_temp_tables  does  not become zero after innobackupex
1802              --safe-slave-backup-timeout seconds. The slave SQL  thread  will
1803              be restarted when the backup finishes.
1804
1805       --safe-slave-backup-timeout=SECONDS
1806              How  many  seconds  innobackupex --safe-slave-backup should wait
1807              for Slave_open_temp_tables to become zero. Defaults to 300  sec‐
1808              onds.
1809
1810       --scpopt = SCP-OPTIONS
1811              This option accepts a string argument that specifies the command
1812              line options to pass to scp when  the  option  --remost-host  is
1813              specified.  If  the option is not specified, the default options
1814              are -Cp -c arcfour.
1815
1816       --slave-info
1817              This option is  useful  when  backing  up  a  replication  slave
1818              server. It prints the binary log position and name of the master
1819              server.  It  also  writes  this   information   to   the   xtra‐
1820              backup_slave_info  file  as a CHANGE MASTER command. A new slave
1821              for this master can be set up by starting a slave server on this
1822              backup  and  issuing a CHANGE MASTER command with the binary log
1823              position saved in the xtrabackup_slave_info file.
1824
1825       --socket
1826              This option accepts a string argument that specifies the  socket
1827              to  use when connecting to the local database server with a UNIX
1828              domain socket. It is passed to the mysql child  process  without
1829              alteration. See mysql --help for details.
1830
1831       --sshopt=SSH-OPTIONS
1832              This option accepts a string argument that specifies the command
1833              line options to pass to ssh when  the  option  --remost-host  is
1834              specified.
1835
1836       --stream=STREAMNAME
1837              This  option accepts a string argument that specifies the format
1838              in which to do the streamed backup. The backup will be  done  to
1839              STDOUT in the specified format. Currently, supported formats are
1840              tar and xbstream. Uses xbstream, which is available  in  Percona
1841              XtraBackup  distributions.  If  you  specify  a  path after this
1842              option, it will be interpreted as the value of tmpdir
1843
1844       --tables-file=FILE
1845              This option accepts a string argument that specifies the file in
1846              which  there are a list of names of the form database.table, one
1847              per line.  The  option  is  passed  directly  to  xtrabackup  's
1848              --tables-file option.
1849
1850       --throttle=IOS
1851              This  option accepts an integer argument that specifies the num‐
1852              ber of I/O operations (i.e., pairs of read+write) per second. It
1853              is passed directly to xtrabackup's xtrabackup --throttle option.
1854              NOTE: This option works only during the  backup  phase,  ie.  it
1855              will  not  work  with  innobackupex --apply-log and innobackupex
1856              --copy-back options.
1857
1858       --tmpdir=DIRECTORY
1859              This option accepts a string argument that specifies  the  loca‐
1860              tion  where a temporary file will be stored. It may be used when
1861              --stream is specified. For these options,  the  transaction  log
1862              will  first  be  stored to a temporary file, before streaming or
1863              copying to a remote host. This  option  specifies  the  location
1864              where  that  temporary file will be stored. If the option is not
1865              specified, the default is to use the value of tmpdir  read  from
1866              the  server  configuration.  innobackupex  is passing the tmpdir
1867              value specified in my.cnf as  the  --target-dir  option  to  the
1868              xtrabackup  binary.  Both  [mysqld]  and [xtrabackup] groups are
1869              read from my.cnf. If there is tmpdir in  both,  then  the  value
1870              being used depends on the order of those group in my.cnf.
1871
1872       --use-memory=#
1873              This  option accepts a string argument that specifies the amount
1874              of memory in bytes for xtrabackup  to  use  for  crash  recovery
1875              while  preparing a backup. Multiples are supported providing the
1876              unit (e.g. 1MB, 1M, 1GB, 1G). It is used only  with  the  option
1877              --apply-log.  It  is  passed directly to xtrabackup's xtrabackup
1878              --use-memory  option.  See  the  xtrabackup  documentation   for
1879              details.
1880
1881       --user=USER
1882              This  option  accepts  a string argument that specifies the user
1883              (i.e., the MySQL username used when connecting to the server) to
1884              login  as,  if  that's not the current user. It is passed to the
1885              mysql child process without alteration.  See  mysql  --help  for
1886              details.
1887
1888       --version
1889              This  option  displays  the  innobackupex  version and copyright
1890              notice and then exits.
1891
1892       --version-check
1893              When this option is specified, innobackupex will perform a  ver‐
1894              sion check against the server on the backup stage after creating
1895              a server connection.
1896

AUTHOR

1898       Percona LLC and/or its affiliates
1899
1901       2009-2019, Percona LLC and/or its affiliates
1902
1903
1904
1905
19062.3.5                            Oct 03, 2019                  INNOBACKUPEX(1)
Impressum