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

NAME

6       xtrabackup - Percona XtraBackup 2.3 Documentation
7
8       The  xtrabackup  binary is a compiled C program that is linked with the
9       InnoDB libraries and the standard MySQL client  libraries.  The  InnoDB
10       libraries provide functionality necessary to apply a log to data files,
11       and the MySQL client libraries  provide  command-line  option  parsing,
12       configuration  file  parsing,  and  so on to give the binary a familiar
13       look and feel.
14
15       The tool runs in either --backup or --prepare  mode,  corresponding  to
16       the  two  main  functions  it performs. There are several variations on
17       these functions to accomplish different tasks, and there are  two  less
18       commonly used modes, --stats and --print-param.
19

THE BACKUP CYCLE - FULL BACKUPS

21   Creating a Backup
22       To  create  a backup, run xtrabackup with the --backup option. You also
23       need to specify a --target_dir option, which is where the  backup  will
24       be  stored,  and  a  --datadir option, which is where the MySQL data is
25       stored. If the InnoDB data or log  files  aren't  stored  in  the  same
26       directory, you might need to specify the location of those, too. If the
27       target directory does not exist, xtrabackup creates it. If  the  direc‐
28       tory  does exist and is empty, xtrabackup will succeed. xtrabackup will
29       not overwrite existing files, it will fail with operating system  error
30       17,  file exists.
31
32       The  tool  changes its working directory to the data directory and per‐
33       forms two primary tasks to complete the backup:
34
35       · It starts a log-copying thread in the background. This thread watches
36         the  InnoDB  log  files,  and when they change, it copies the changed
37         blocks to a file  called  xtrabackup_logfile  in  the  backup  target
38         directory.  This  is  necessary  because the backup might take a long
39         time, and the recovery process needs all of the log file entries from
40         the beginning to the end of the backup.
41
42       · It  copies the InnoDB data files to the target directory. This is not
43         a simple file copy; it opens and reads the files similarly to the way
44         InnoDB  does,  by reading the data dictionary and copying them a page
45         at a time.
46
47       When  the  data  files  are  finished  copying,  xtrabackup  stops  the
48       log-copying  thread, and creates a files in the target directory called
49       xtrabackup_checkpoints, which contains the type  of  backup  performed,
50       the  log  sequence number at the beginning, and the log sequence number
51       at the end.
52
53       An example command to perform a backup follows:
54
55          $ xtrabackup --backup --datadir=/var/lib/mysql/ --target-dir=/data/backups/mysql/
56
57       This takes a backup of /var/lib/mysql  and  stores  it  at  /data/back‐
58       ups/mysql/.  If  you specify a relative path, the target directory will
59       be relative to the current directory.
60
61       During the backup process, you should see a lot of output  showing  the
62       data  files  being  copied,  as  well as the log file thread repeatedly
63       scanning the log files and copying from it. Here  is  an  example  that
64       shows  the  log  thread  scanning the log in the background, and a file
65       copying thread working on the ibdata1 file:
66
67          >> log scanned up to (3646475465483)
68          >> log scanned up to (3646475517369)
69          >> log scanned up to (3646475581716)
70          >> log scanned up to (3646475636841)
71          >> log scanned up to (3646475718082)
72          >> log scanned up to (3646475988095)
73          >> log scanned up to (3646476048286)
74          >> log scanned up to (3646476102877)
75          >> log scanned up to (3646476140854)
76          [01] Copying /usr/local/mysql/var/ibdata1
77               to /usr/local/mysql/Backups/2011-04-18_21-11-15/ibdata1
78          [01]        ...done
79
80       The last thing you should see is something like  the  following,  where
81       the value of the <LSN> will be a number that depends on your system:
82
83          xtrabackup: Transaction log of lsn (<SLN>) to (<LSN>) was copied.
84
85       NOTE:
86          Log  copying thread checks the transactional log every second to see
87          if there were any new log records written that need  to  be  copied,
88          but  there is a chance that the log copying thread might not be able
89          to keep up with the amount of writes that go  to  the  transactional
90          logs,  and  will  hit  an error when the log records are overwritten
91          before they could be read.
92
93       After the backup is finished, the target directory will  contain  files
94       such  as  the  following,  assuming  you  have  a  single  InnoDB table
95       test.tbl1 and you are using MySQL's innodb_file_per_table option:
96
97          /data/backups/mysql/ibdata1
98          /data/backups/mysql/test
99          /data/backups/mysql/test/tbl1.ibd
100          /data/backups/mysql/xtrabackup_checkpoints
101          /data/backups/mysql/xtrabackup_logfile
102
103       The backup can take a long time, depending on how  large  the  database
104       is.  It  is  safe to cancel at any time, because it does not modify the
105       database.
106
107       The next step  is  getting  your  backup  ready  to  restored:  prepar‐
108       ing_the_backup.
109
110   Preparing the backup
111       After  you make a backup with --backup, the next step is to prepare it.
112       The data files are not point-in-time consistent until they've been pre‐
113       pared,  because they were copied at different times as the program ran,
114       and they might have been changed while this was happening. If  you  try
115       to  start  InnoDB  with these data files, it will detect corruption and
116       crash itself to prevent you from running on damaged data. The --prepare
117       step  makes the files perfectly consistent at a single instant in time,
118       so you can run InnoDB on them.
119
120       NOTE:
121          For prepare innobackupex --apply-log should be used which will  read
122          InnoDB    configuration   from   backup-my.cnf   automatically,   or
123          --defaults-file=backup-my.cnf option should be passed to  the  xtra‐
124          backup  binary  if it is used for preparing the backup. Otherwise it
125          could lead to incorrect restore because xtrabackup could  use  wrong
126          configuration options.
127
128       You  can  run the prepare operation on any machine; it does not need to
129       be on the originating server or the  server  to  which  you  intend  to
130       restore.  You  can  copy  the backup to a utility server and prepare it
131       there, for example.
132
133       NOTE:
134          You can prepare a backup created with older Percona XtraBackup  ver‐
135          sion  with a newer one, but not vice versa. Preparing a backup on an
136          unsupported server version should be done with  the  latest  Percona
137          XtraBackup  release which supports that server version. For example,
138          if one has a backup of MySQL 5.0  created  with  Percona  XtraBackup
139          1.6,  then  preparing  the backup with Percona XtraBackup 2.2 is not
140          supported, because  support  for  MySQL  5.0  was  removed  in  2.1.
141          Instead, the latest release in the 2.0 series should be used.
142
143       During  the  prepare  operation, xtrabackup boots up a kind of modified
144       InnoDB that's embedded inside it (the libraries it was linked against).
145       The  modifications  are  necessary  to disable InnoDB's standard safety
146       checks, such as complaining that the log file  isn't  the  right  size,
147       which  aren't appropriate for working with backups. These modifications
148       are only for the xtrabackup binary; you don't need a modified InnoDB to
149       use xtrabackup for your backups.
150
151       The  prepare step uses this "embedded InnoDB" to perform crash recovery
152       on the copied datafiles, using the copied log file. The prepare step is
153       very simple to use: you simply run xtrabackup with the --prepare option
154       and tell it which directory to prepare, for  example,  to  prepare  the
155       backup previously taken,
156
157          xtrabackup --prepare --target-dir=/data/backups/mysql/
158
159       When  this finishes, you should see an "InnoDB shutdown" with a message
160       such as the following, where again the value of LSN will depend on your
161       system:
162
163          101107 16:40:15  InnoDB: Shutdown completed; log sequence number <LSN>
164
165       Your backup is now clean and consistent, and ready to restore. However,
166       you might want to take an extra step to make restores as quick as  pos‐
167       sible.  This  is  to  prepare  the backup a second time. The first time
168       makes the data files perfectly self-consistent, but it  doesn't  create
169       fresh  InnoDB  log  files.  If you restore the backup at this point and
170       start MySQL, it will have to create new log files, which could  take  a
171       little  while,  and  you  might  not  want to wait for that. If you run
172       --prepare a second time, xtrabackup will create the log files for  you,
173       and  output status text such as the following, which is abbreviated for
174       clarity. The value of <SIZE> will depend on your MySQL configuration.
175
176          $ xtrabackup --prepare --target-dir=/data/backups/mysql/
177          xtrabackup: This target seems to be already prepared.
178          xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.
179          101107 16:54:10  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
180          InnoDB: Setting log file ./ib_logfile0 size to <SIZE> MB
181          InnoDB: Database physically writes the file full: wait...
182          101107 16:54:10  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
183          InnoDB: Setting log file ./ib_logfile1 size to <SIZE> MB
184          InnoDB: Database physically writes the file full: wait...
185          101107 16:54:15  InnoDB: Shutdown completed; log sequence number 1284108
186
187       All following prepares  (third  and  following)  will  not  change  the
188       already prepared data files, you can only see that output says
189
190          xtrabackup: This target seems to be already prepared.
191          xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.
192
193       It  is  not recommended to interrupt xtrabackup process while preparing
194       backup - it may cause data files corruption and backup will become  not
195       usable. Backup validity is not guaranteed if prepare process was inter‐
196       rupted.
197
198       If you intend the backup to be the basis for further incremental  back‐
199       ups,  you  should  use  the  --apply-log-only option when preparing the
200       backup, or you will not be able to apply incremental backups to it. See
201       the documentation on preparing incremental backups for more details.
202
203   Restoring a Backup
204       The  xtrabackup  binary does not have any functionality for restoring a
205       backup. That is up to the user to do. You might  use  rsync  or  cp  to
206       restore  the  files.  You should check that the restored files have the
207       correct ownership and permissions.
208
209       NOTE:
210          The datadir must be empty before restoring  the  backup.  Also  it's
211          important  to  note  that  MySQL server needs to be shut down before
212          restore is performed. You can't restore to a datadir  of  a  running
213          mysqld instance (except when importing a partial backup).
214
215       Example of the rsync command that can be used to restore the backup can
216       look like this:
217
218          $ rsync -avrP /data/backup/ /var/lib/mysql/
219
220       As files' attributes will be preserved, in most cases you will need  to
221       change  the  files'  ownership  to  mysql  before starting the database
222       server, as they will be owned by the user who created the backup:
223
224          $ chown -R mysql:mysql /var/lib/mysql
225
226       Note that xtrabackup backs up only the InnoDB data. You must separately
227       restore  the MySQL system database, MyISAM data, table definition files
228       (.frm files), and everything else necessary to make your database func‐
229       tional -- or innobackupex can do it for you.
230

OTHER TYPES OF BACKUPS

232   Incremental Backups
233       Both  xtrabackup  and  innobackupex tools supports incremental backups,
234       which means that it can copy only the data that has changed  since  the
235       last full backup. You can perform many incremental backups between each
236       full backup, so you can set up a backup process such as a  full  backup
237       once  a week and an incremental backup every day, or full backups every
238       day and incremental backups every hour.
239
240       Incremental backups work because each  InnoDB  page  (usually  16kb  in
241       size)  contains  a  log  sequence number, or LSN. The LSN is the system
242       version number for the entire  database.  Each  page's  LSN  shows  how
243       recently  it  was changed. An incremental backup copies each page whose
244       LSN is newer than the previous incremental or full backup's LSN.  There
245       are  two  algorithms in use to find the set of such pages to be copied.
246       The first one, available with all the server types and versions, is  to
247       check  the  page LSN directly by reading all the data pages. The second
248       one, available with Percona Server,  is  to  enable  the  changed  page
249       tracking  feature  on the server, which will note the pages as they are
250       being changed. This information will be then written out in  a  compact
251       separate  so-called  bitmap  file.  The xtrabackup binary will use that
252       file to read only the data pages it needs for the  incremental  backup,
253       potentially  saving many read requests. The latter algorithm is enabled
254       by default if the xtrabackup binary finds the bitmap file. It is possi‐
255       ble  to  specify --incremental-force-scan to read all the pages even if
256       the bitmap data is available.
257
258       Incremental backups do not actually compare the data files to the  pre‐
259       vious  backup's  data  files. In fact, you can use --incremental-lsn to
260       perform an incremental backup without even having the previous  backup,
261       if you know its LSN. Incremental backups simply read the pages and com‐
262       pare their LSN to the last backup's LSN. You still need a  full  backup
263       to  recover  the incremental changes, however; without a full backup to
264       act as a base, the incremental backups are useless.
265
266   Creating an Incremental Backup
267       To make an incremental backup, begin with a full backup as  usual.  The
268       xtrabackup  binary writes a file called xtrabackup_checkpoints into the
269       backup's target directory.  This  file  contains  a  line  showing  the
270       to_lsn,  which  is  the database's LSN at the end of the backup. Create
271       the full backup with a command such as the following:
272
273          xtrabackup --backup --target-dir=/data/backups/base --datadir=/var/lib/mysql/
274
275       If you want a usable full backup,  use  innobackupex  since  xtrabackup
276       itself  won't copy table definitions, triggers, or anything else that's
277       not .ibd.
278
279       If you look at the xtrabackup_checkpoints file,  you  should  see  some
280       contents similar to the following:
281
282          backup_type = full-backuped
283          from_lsn = 0
284          to_lsn = 1291135
285
286       Now  that  you  have  a full backup, you can make an incremental backup
287       based on it. Use a command such as the following:
288
289          xtrabackup --backup --target-dir=/data/backups/inc1 \
290          --incremental-basedir=/data/backups/base --datadir=/var/lib/mysql/
291
292       The /data/backups/inc1/ directory should now contain delta files,  such
293       as ibdata1.delta and test/table1.ibd.delta. These represent the changes
294       since the LSN 1291135. If you examine the  xtrabackup_checkpoints  file
295       in this directory, you should see something similar to the following:
296
297          backup_type = incremental
298          from_lsn = 1291135
299          to_lsn = 1291340
300
301       The  meaning  should  be  self-evident.  It's  now possible to use this
302       directory as the base for yet another incremental backup:
303
304          xtrabackup --backup --target-dir=/data/backups/inc2 \
305          --incremental-basedir=/data/backups/inc1 --datadir=/var/lib/mysql/
306
307   Preparing the Incremental Backups
308       The --prepare step for incremental backups is not the same as for  nor‐
309       mal  backups.  In normal backups, two types of operations are performed
310       to make the database consistent: committed  transactions  are  replayed
311       from  the log file against the data files, and uncommitted transactions
312       are rolled back. You must skip the rollback of uncommitted transactions
313       when  preparing a backup, because transactions that were uncommitted at
314       the time of your backup may be in progress, and it's likely  that  they
315       will  be  committed  in the next incremental backup. You should use the
316       --apply-log-only option to prevent the rollback phase.
317
318       If you do not use the --apply-log-only option to prevent  the  rollback
319       phase,  then  your  incremental backups will be useless. After transac‐
320       tions have been rolled back,  further  incremental  backups  cannot  be
321       applied.
322
323       Beginning  with  the  full  backup you created, you can prepare it, and
324       then apply the incremental differences to it. Recall that you have  the
325       following backups:
326
327          /data/backups/base
328          /data/backups/inc1
329          /data/backups/inc2
330
331       To  prepare  the  base  backup, you need to run --prepare as usual, but
332       prevent the rollback phase:
333
334          xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base
335
336       The output should end with some text such as the following:
337
338          101107 20:49:43  InnoDB: Shutdown completed; log sequence number 1291135
339
340       The log sequence number should match the to_lsn  of  the  base  backup,
341       which you saw previously.
342
343       This  backup  is  actually  safe  to restore as-is now, even though the
344       rollback phase has been skipped. If you restore  it  and  start  MySQL,
345       InnoDB  will  detect  that the rollback phase was not performed, and it
346       will do that in the background, as it usually does for a crash recovery
347       upon start. It will notify you that the database was not shut down nor‐
348       mally.
349
350       To apply the first incremental backup to the full  backup,  you  should
351       use the following command:
352
353          xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base \
354          --incremental-dir=/data/backups/inc1
355
356       This  applies the delta files to the files in /data/backups/base, which
357       rolls them forward in time to the time of the  incremental  backup.  It
358       then  applies the redo log as usual to the result. The final data is in
359       /data/backups/base, not in the incremental directory.  You  should  see
360       some output such as the following:
361
362          incremental backup from 1291135 is enabled.
363          xtrabackup: cd to /data/backups/base/
364          xtrabackup: This target seems to be already prepared.
365          xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(1291340)
366          Applying /data/backups/inc1/ibdata1.delta ...
367          Applying /data/backups/inc1/test/table1.ibd.delta ...
368          .... snip
369          101107 20:56:30  InnoDB: Shutdown completed; log sequence number 1291340
370
371       Again,  the  LSN should match what you saw from your earlier inspection
372       of the  first  incremental  backup.  If  you  restore  the  files  from
373       /data/backups/base,  you should see the state of the database as of the
374       first incremental backup.
375
376       Preparing the second incremental backup is a similar process: apply the
377       deltas  to  the (modified) base backup, and you will roll its data for‐
378       ward in time to the point of the second incremental backup:
379
380          xtrabackup --prepare --target-dir=/data/backups/base \
381          --incremental-dir=/data/backups/inc2
382
383       NOTE:
384          --apply-log-only should be used when merging all incrementals except
385          the  last  one.  That's  why  the  previous line doesn't contain the
386          --apply-log-only option. Even if the --apply-log-only  was  used  on
387          the  last  step,  backup  would still be consistent but in that case
388          server would perform the rollback phase.
389
390       If you wish to avoid the notice that InnoDB was not shut down normally,
391       when  you  have  applied the desired deltas to the base backup, you can
392       run --prepare again without disabling the rollback phase.
393
394   Partial Backups
395       xtrabackup supports taking partial backups when the innodb_file_per_ta‐
396       ble  option is enabled. There are three ways to create partial backups:
397       matching the tables' names with a regular expression, providing a  list
398       of them in a file or providing a list of databases.
399
400       WARNING:
401          If any of the matched or listed tables is deleted during the backup,
402          xtrabackup will fail.
403
404       For the purposes of this manual page, we will assume that  there  is  a
405       database named test which contains tables named t1 and t2.
406
407   Using the --tables Option
408       The  first  method is with the --tables option. The option's value is a
409       regular expression that is matched against the fully  qualified  table‐
410       name, including the database name, in the form databasename.tablename.
411
412       To  back up only tables in the test database, you can use the following
413       command:
414
415          $ xtrabackup --backup --datadir=/var/lib/mysql --target-dir=/data/backups/ \
416          --tables="^test[.].*"
417
418       To back up only the table test.t1, you can use the following command:
419
420          $ xtrabackup --backup --datadir=/var/lib/mysql --target-dir=/data/backups/ \
421          --tables="^test[.]t1"
422
423   Using the --tables-file Option
424       The --tables-file option specifies a file that can contain multiple ta‐
425       ble  names,  one table name per line in the file. Only the tables named
426       in the file will be backed up. Names are matched  exactly,  case-sensi‐
427       tive,  with  no pattern or regular expression matching. The table names
428       must be fully qualified, in databasename.tablename format.
429
430          $ echo "mydatabase.mytable" > /tmp/tables.txt
431          $ xtrabackup --backup --tables-file=/tmp/tables.txt
432
433   Using the --databases and --databases-file options
434       The --databases option accepts a space-separated list of the  databases
435       and  tables  to  backup  -  in  the  databasename[.tablename] form. The
436       --databases-file option specifies a  file  that  can  contain  multiple
437       databases  and tables in the databasename[.tablename] form, one element
438       name per line in the file. Only named  databases  and  tables  will  be
439       backed  up.  Names are matched exactly, case-sensitive, with no pattern
440       or regular expression matching.
441
442   Preparing the Backup
443       When you use the --prepare option on a partial  backup,  you  will  see
444       warnings  about  tables  that don't exist. That is because these tables
445       exist in the data dictionary inside InnoDB, but the corresponding  .ibd
446       files  don't  exist.  They  were  not copied into the backup directory.
447       These tables will be removed from the data  dictionary,  and  when  you
448       restore the backup and start InnoDB, they will no longer exist and will
449       not cause any errors or warnings to be printed to the log file.
450
451       An example of the error message you will see during the  prepare  phase
452       follows.
453
454          InnoDB: Reading tablespace information from the .ibd files...
455          101107 22:31:30  InnoDB: Error: table 'test1/t'
456          InnoDB: in InnoDB data dictionary has tablespace id 6,
457          InnoDB: but tablespace with that id or name does not exist. It will be removed from data dictionary.
458
459   Compact Backups
460       When  doing  the backup of InnoDB tables it's possible to omit the sec‐
461       ondary index pages. This will make the backups more  compact  and  this
462       way they will take less space on disk. The downside of this is that the
463       backup prepare process takes longer as those secondary indexes need  to
464       be recreated. Difference in backup size depends on the size of the sec‐
465       ondary indexes.
466
467       For example full backup taken without and with the --compact option:
468
469          #backup size without --compact
470          2.0G  xb_backup
471
472          #backup size taken with --compact option
473          1.4G  xb_compact_backup
474
475       NOTE:
476          Compact backups are not supported for  system  table  space,  so  in
477          order  to  work  correctly  innodb-file-per-table  option  should be
478          enabled.
479
480       This feature was introduced in Percona XtraBackup 2.1.
481
482   Creating Compact Backups
483       To make a compact backup innobackupex needs  to  be  started  with  the
484       --compact option:
485
486          $ xtrabackup --backup --compact --target-dir=/data/backups
487
488       This will create a compact backup in the /data/backups.
489
490       If  you  check  at  the  xtrabackup-checkpoints  file in the target-dir
491       folder, you should see something like:
492
493          backup_type = full-backuped
494          from_lsn = 0
495          to_lsn = 2888984349
496          last_lsn = 2888984349
497          compact = 1
498
499       When --compact wasn't used compact value will be 0. This way it's  easy
500       to check if the backup contains the secondary index pages or not.
501
502   Preparing Compact Backups
503       Preparing  the compact require rebuilding the indexes as well. In order
504       to prepare the backup a new option  --rebuild-indexes  should  be  used
505       with --apply-logs:
506
507          $ xtrabackup --prepare --rebuild-indexes /data/backups/
508
509       Output,  beside  the  standard  innobackupex output, should contain the
510       information about indexes being rebuilt, like:
511
512          [01] Checking if there are indexes to rebuild in table sakila/city (space id: 9)
513          [01]   Found index idx_fk_country_id
514          [01]   Rebuilding 1 index(es).
515          [01] Checking if there are indexes to rebuild in table sakila/country (space id: 10)
516          [01] Checking if there are indexes to rebuild in table sakila/customer (space id: 11)
517          [01]   Found index idx_fk_store_id
518          [01]   Found index idx_fk_address_id
519          [01]   Found index idx_last_name
520          [01]   Rebuilding 3 index(es).
521
522       Additionally, you can  use  the  --rebuild-threads  option  to  process
523       tables in multiple threads when rebuilding indexes, e.g.:
524
525          $ xtrabackup --prepare --rebuild-indexes --rebuild-threads=16 /data/backups/
526
527       In this case Percona XtraBackup will create 16 worker threads with each
528       thread rebuilding indexes for one table at a time. It  will  also  show
529       thread IDs for each message
530
531          Starting 16 threads to rebuild indexes.
532
533          [09] Checking if there are indexes to rebuild in table sakila/city (space id: 9)
534          [09]   Found index idx_fk_country_id
535          [10] Checking if there are indexes to rebuild in table sakila/country (space id: 10)
536          [11] Checking if there are indexes to rebuild in table sakila/customer (space id: 11)
537          [11]   Found index idx_fk_store_id
538          [11]   Found index idx_fk_address_id
539          [11]   Found index idx_last_name
540          [11]   Rebuilding 3 index(es).
541
542       Since  Percona XtraBackup has no information when applying an incremen‐
543       tal backup to a compact full one, on whether there will be more  incre‐
544       mental  backups applied to it later or not, rebuilding indexes needs to
545       be explicitly requested by a user whenever  a  full  backup  with  some
546       incremental  backups merged is ready to be restored. Rebuilding indexes
547       unconditionally on every incremental backup merge  is  not  an  option,
548       since it is an expensive operation.
549
550   Restoring Compact Backups
551       The  xtrabackup  binary does not have any functionality for restoring a
552       backup. That is up to the user to do. You might  use  rsync  or  cp  to
553       restore  the  files.  You should check that the restored files have the
554       correct ownership and permissions.
555
556   Other Reading
557       · Feature preview: Compact backups in Percona XtraBackup
558

ADVANCED FEATURES

560   Throttling Backups
561       Although xtrabackup does  not  block  your  database's  operation,  any
562       backup  can  add load to the system being backed up. On systems that do
563       not have much spare I/O capacity, it might be helpful to  throttle  the
564       rate  at  which  xtrabackup reads and writes data. You can do this with
565       the --throttle option, this option limits the number of I/O  operations
566       per second in 1 MB units.
567
568       Image below shows how throttling works when --throttle =1.  [image]
569
570       In   --backup   mode,  this  option  limits  the  number  of  pairs  of
571       read-and-write operations per second that xtrabackup will  perform.  If
572       you are creating an incremental backup, then the limit is the number of
573       read IO operations per second.
574
575       By default, there is no throttling, and  xtrabackup  reads  and  writes
576       data  as quickly as it can. If you set too strict of a limit on the I/O
577       operations, the backup might be so slow that it  will  never  catch  up
578       with  the  transaction logs that InnoDB is writing, so the backup might
579       never complete.
580
581   Scripting Backups With xtrabackup
582       The xtrabackup tool has several features to enable scripts  to  control
583       it  while  they  perform  related tasks. The innobackupex script is one
584       example, but xtrabackup is easy to control with your  own  command-line
585       scripts too.
586
587   Suspending After Copying
588       In  backup  mode,  xtrabackup  normally copies the log files in a back‐
589       ground thread, copies the data files in a foreground thread,  and  then
590       stops  the  log  copying  thread  and  finishes.  If you use the --sus‐
591       pend-at-end option, instead of stopping the log thread  and  finishing,
592       xtrabackup  continues  to copy the log files, and creates a file in the
593       target directory called xtrabackup_suspended.  As  long  as  that  file
594       exists,  xtrabackup  will continue to watch the log files and copy them
595       into the xtrabackup_logfile in the target directory. When the  file  is
596       removed, xtrabackup will finish copying the log file and exit.
597
598       This  functionality  is useful for coordinating the InnoDB data backups
599       with other actions. Perhaps the most obvious is copying the table defi‐
600       nitions  (the  .frm  files) so that the backup can be restored. You can
601       start xtrabackup in the  background, wait for the  xtrabackup_suspended
602       file  to be created, and then copy any other files you need to complete
603       the backup. This is exactly what the innobackupex tool  does  (it  also
604       copies MyISAM data and other files).
605
606   Generating Meta-Data
607       It  is  a  good  idea for the backup to include all the information you
608       need to restore the backup. The xtrabackup tool can print out the  con‐
609       tents  of  a  my.cnf  file  that are needed to restore the data and log
610       files. If you add the --print-param option, it will print out something
611       like the following:
612
613          # This MySQL options file was generated by XtraBackup.
614          [mysqld]
615          datadir = /data/mysql/
616          innodb_data_home_dir = /data/innodb/
617          innodb_data_file_path = ibdata1:10M:autoextend
618          innodb_log_group_home_dir = /data/innodb-logs/
619
620       You can redirect this output into a file in the target directory of the
621       backup.
622
623   Agreeing on the Source Directory
624       It's possible that the presence of a defaults  file  or  other  factors
625       could  cause  xtrabackup to back up data from a different location than
626       you expected. To prevent this, you can  use  --print-param  to  ask  it
627       where  it  will  be copying data from. You can use the output to ensure
628       that xtrabackup and your script are working on the same dataset.
629
630   Log Streaming
631       You can instruct xtrabackup to omit  copying  data  files,  and  simply
632       stream  the  log file to its standard output instead with --log-stream.
633       This automatically adds the --suspend-at-end option.  Your  script  can
634       then  perform  tasks such as streaming remote backups by piping the log
635       files into an SSH connection and copying  the  data  files  to  another
636       server with a tool such as rsync or the xbstream binary.
637
638   Analyzing Table Statistics
639       The  xtrabackup  binary can analyze InnoDB data files in read-only mode
640       to give statistics about them. To do this, you should use  the  --stats
641       option.  You  can  combine  this  with the --tables option to limit the
642       files to examine. It also uses the --use-memory option.
643
644       You can perform the analysis on a running server, with some  chance  of
645       errors  due to the data being changed during analysis. Or, you can ana‐
646       lyze a backup copy of the database. Either way, to use  the  statistics
647       feature,  you  need  a  clean  copy of the database including correctly
648       sized log files, so you need to execute with  --prepare  twice  to  use
649       this functionality on a backup.
650
651       The result of running on a backup might look like the following:
652
653          <INDEX STATISTICS>
654            table: test/table1, index: PRIMARY, space id: 12, root page 3
655            estimated statistics in dictionary:
656              key vals: 25265338, leaf pages 497839, size pages 498304
657            real statistics:
658               level 2 pages: pages=1, data=5395 bytes, data/pages=32%
659               level 1 pages: pages=415, data=6471907 bytes, data/pages=95%
660                  leaf pages: recs=25958413, pages=497839, data=7492026403 bytes, data/pages=91%
661
662       This can be interpreted as follows:
663
664       · The first line simply shows the table and index name and its internal
665         identifiers. If you see an index named GEN_CLUST_INDEX, that  is  the
666         table's  clustered  index,  automatically created because you did not
667         explicitly create a PRIMARY KEY.
668
669       · The estimated statistics in dictionary information is similar to  the
670         data  that's  gathered  through  ANALYZE TABLE inside of InnoDB to be
671         stored as estimated cardinality statistics and passed  to  the  query
672         optimizer.
673
674       · The  real  statistics  information is the result of scanning the data
675         pages and computing exact information about the index.
676
677       · The level <X> pages: output means that  the  line  shows  information
678         about  pages  at that level in the index tree. The larger <X> is, the
679         farther it is from the leaf pages, which are level 0. The first  line
680         is the root page.
681
682       · The  leaf pages output shows the leaf pages, of course. This is where
683         the table's data is stored.
684
685       · The external pages: output (not shown)  shows  large  external  pages
686         that hold values too long to fit in the row itself, such as long BLOB
687         and TEXT values.
688
689       · The recs is the real number of records (rows) in leaf pages.
690
691       · The pages is the page count.
692
693       · The data is the total size of the data in the pages, in bytes.
694
695       · The data/pages is calculated as (data / (pages * PAGE_SIZE)) *  100%.
696         It  will  never reach 100% because of space reserved for page headers
697         and footers.
698
699       A more detailed example is posted as a MySQL Performance Blog post.
700
701   Script to Format Output
702       The following script can be used to summarize and tabulate  the  output
703       of the statistics information:
704
705          tabulate-xtrabackup-stats.pl
706
707          #!/usr/bin/perl
708          use strict;
709          use warnings FATAL => 'all';
710          my $script_version = "0.1";
711
712          my $PG_SIZE = 16_384; # InnoDB defaults to 16k pages, change if needed.
713          my ($cur_idx, $cur_tbl);
714          my (%idx_stats, %tbl_stats);
715          my ($max_tbl_len, $max_idx_len) = (0, 0);
716          while ( my $line = <> ) {
717             if ( my ($t, $i) = $line =~ m/table: (.*), index: (.*), space id:/ ) {
718                $t =~ s!/!.!;
719                $cur_tbl = $t;
720                $cur_idx = $i;
721                if ( length($i) > $max_idx_len ) {
722                   $max_idx_len = length($i);
723                }
724                if ( length($t) > $max_tbl_len ) {
725                   $max_tbl_len = length($t);
726                }
727             }
728             elsif ( my ($kv, $lp, $sp) = $line =~ m/key vals: (\d+), \D*(\d+), \D*(\d+)/ ) {
729                @{$idx_stats{$cur_tbl}->{$cur_idx}}{qw(est_kv est_lp est_sp)} = ($kv, $lp, $sp);
730                $tbl_stats{$cur_tbl}->{est_kv} += $kv;
731                $tbl_stats{$cur_tbl}->{est_lp} += $lp;
732                $tbl_stats{$cur_tbl}->{est_sp} += $sp;
733             }
734             elsif ( my ($l, $pages, $bytes) = $line =~ m/(?:level (\d+)|leaf) pages:.*pages=(\d+), data=(\d+) bytes/ ) {
735                $l ||= 0;
736                $idx_stats{$cur_tbl}->{$cur_idx}->{real_pages} += $pages;
737                $idx_stats{$cur_tbl}->{$cur_idx}->{real_bytes} += $bytes;
738                $tbl_stats{$cur_tbl}->{real_pages} += $pages;
739                $tbl_stats{$cur_tbl}->{real_bytes} += $bytes;
740             }
741          }
742
743          my $hdr_fmt = "%${max_tbl_len}s %${max_idx_len}s %9s %10s %10s\n";
744          my @headers = qw(TABLE INDEX TOT_PAGES FREE_PAGES PCT_FULL);
745          printf $hdr_fmt, @headers;
746
747          my $row_fmt = "%${max_tbl_len}s %${max_idx_len}s %9d %10d %9.1f%%\n";
748          foreach my $t ( sort keys %tbl_stats ) {
749             my $tbl = $tbl_stats{$t};
750             printf $row_fmt, $t, "", $tbl->{est_sp}, $tbl->{est_sp} - $tbl->{real_pages},
751                $tbl->{real_bytes} / ($tbl->{real_pages} * $PG_SIZE) * 100;
752             foreach my $i ( sort keys %{$idx_stats{$t}} ) {
753                my $idx = $idx_stats{$t}->{$i};
754                printf $row_fmt, $t, $i, $idx->{est_sp}, $idx->{est_sp} - $idx->{real_pages},
755                   $idx->{real_bytes} / ($idx->{real_pages} * $PG_SIZE) * 100;
756             }
757          }
758
759   Sample Script Output
760       The  output of the above Perl script, when run against the sample shown
761       in the previously mentioned blog post, will appear as follows:
762
763                    TABLE           INDEX TOT_PAGES FREE_PAGES   PCT_FULL
764          art.link_out104                    832383      38561      86.8%
765          art.link_out104         PRIMARY    498304         49      91.9%
766          art.link_out104       domain_id     49600       6230      76.9%
767          art.link_out104     domain_id_2     26495       3339      89.1%
768          art.link_out104 from_message_id     28160        142      96.3%
769          art.link_out104    from_site_id     38848       4874      79.4%
770          art.link_out104   revert_domain    153984      19276      71.4%
771          art.link_out104    site_message     36992       4651      83.4%
772
773       The columns are the table and index, followed by the  total  number  of
774       pages in that index, the number of pages not actually occupied by data,
775       and the number of bytes of real data as a percentage of the total  size
776       of the pages of real data. The first line in the above output, in which
777       the INDEX column is empty, is a summary of the entire table.
778
779   Working with Binary Logs
780       The xtrabackup binary integrates with information that InnoDB stores in
781       its  transaction  log  about  the corresponding binary log position for
782       committed transactions. This enables it to print  out  the  binary  log
783       position to which a backup corresponds, so you can use it to set up new
784       replication slaves or perform point-in-time recovery.
785
786   Finding the Binary Log Position
787       You can find the binary log position corresponding to a backup once the
788       backup  has been prepared. This can be done by either running the xtra‐
789       backup with --prepare or innobackupex with --apply-log option. If  your
790       backup  is  from  a server with binary logging enabled, xtrabackup will
791       create a file named xtrabackup_binlog_info  in  the  target  directory.
792       This  file  contains the binary log file name and position of the exact
793       point in the binary log to which the prepared backup corresponds.
794
795       You will also see output similar to the following  during  the  prepare
796       stage:
797
798          InnoDB: Last MySQL binlog file position 0 3252710, file name ./mysql-bin.000001
799          ... snip ...
800          [notice (again)]
801            If you use binary log and don't use any hack of group commit,
802            the binary log position seems to be:
803          InnoDB: Last MySQL binlog file position 0 3252710, file name ./mysql-bin.000001
804
805       This output can also be found in the xtrabackup_binlog_pos_innodb file,
806       but it is only correct when no other than XtraDB or InnoDB are used  as
807       storage engines.
808
809       If  other  storage  engines  are used (i.e. MyISAM), you should use the
810       xtrabackup_binlog_info file to retrieve the position.
811
812       The message about hacking group commit refers to an  early  implementa‐
813       tion of emulated group commit in Percona Server.
814
815   Point-In-Time Recovery
816       To  perform  a  point-in-time  recovery  from an xtrabackup backup, you
817       should prepare and restore the backup, and then replay binary logs from
818       the point shown in the xtrabackup_binlog_info file.
819
820       A more detailed procedure is found here (with innobackupex).
821
822   Setting Up a New Replication Slave
823       To  set up a new replica, you should prepare the backup, and restore it
824       to the data directory of your  new  replication  slave.  Then  in  your
825       CHANGE  MASTER  TO  command,  use  the binary log filename and position
826       shown in the xtrabackup_binlog_info file to start replication.
827
828       A more detailed procedure is  found  in   ../howtos/setting_up_replica‐
829       tion.
830
831   Restoring Individual Tables
832       In  server  versions  prior  to  5.6, it is not possible to copy tables
833       between servers by copying the files, even with  innodb_file_per_table.
834       However, with Percona XtraBackup, you can export individual tables from
835       any InnoDB database, and import them into Percona Server with XtraDB or
836       MySQL  5.6.  (The source doesn't have to be XtraDB or or MySQL 5.6, but
837       the destination does.) This only works on individual  .ibd  files,  and
838       cannot export a table that is not contained in its own .ibd file.
839
840       Let's see how to export and import the following table:
841
842          CREATE TABLE export_test (
843            a int(11) DEFAULT NULL
844          ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
845
846       NOTE:
847          If  you're  running  Percona  Server version older than 5.5.10-20.1,
848          variable   innodb_expand_import   should   be   used   instead    of
849          innodb_import_table_from_xtrabackup.
850
851   Exporting the Table
852       This  table  should have been created in innodb_file_per_table mode, so
853       after taking a backup as usual with  --backup,  the  .ibd  file  should
854       exist in the target directory:
855
856          $ find /data/backups/mysql/ -name export_test.*
857          /data/backups/mysql/test/export_test.ibd
858
859       when  you  prepare  the backup, add the extra parameter --export to the
860       command. Here is an example:
861
862          $ xtrabackup --prepare --export --target-dir=/data/backups/mysql/
863
864       Now you should see a .exp file in the target directory:
865
866          $ find /data/backups/mysql/ -name export_test.*
867          /data/backups/mysql/test/export_test.exp
868          /data/backups/mysql/test/export_test.ibd
869          /data/backups/mysql/test/export_test.cfg
870
871       These three files are all you need to import the table  into  a  server
872       running Percona Server with XtraDB or MySQL 5.6.
873
874       NOTE:
875          MySQL  uses  .cfg file which contains InnoDB dictionary dump in spe‐
876          cial format. This format is different from the .exp`  one  which  is
877          used in XtraDB for the same purpose. Strictly speaking, a .cfg` file
878          is not required to import a  tablespace  to  MySQL  5.6  or  Percona
879          Server 5.6. A tablespace will be imported successfully even if it is
880          from another server, but InnoDB will do  schema  validation  if  the
881          corresponding .cfg file is present in the same directory.
882
883   Importing the Table
884       On  the  destination  server  running  Percona  Server  with XtraDB and
885       innodb_import_table_from_xtrabackup option enabled, or MySQL 5.6,  cre‐
886       ate  a  table  with  the same structure, and then perform the following
887       steps:
888
889       · Execute ALTER TABLE test.export_test DISCARD TABLESPACE;
890
891         · If you see  the  following  message,  then  you  must  enable  inn‐
892           odb_file_per_table  and create the table again: ERROR 1030 (HY000):
893           Got error -1 from storage engine
894
895       · Copy the exported files to the test/ subdirectory of the  destination
896         server's data directory
897
898       · Execute ALTER TABLE test.export_test IMPORT TABLESPACE;
899
900       The table should now be imported, and you should be able to SELECT from
901       it and see the imported data.
902
903       NOTE:
904          Persistent statistics for imported tablespace will  be  empty  until
905          you  run the ANALYZE TABLE on the imported table. They will be empty
906          because they  are  stored  in  the  system  tables  mysql.innodb_ta‐
907          ble_stats  and  mysql.innodb_index_stats  and they aren't updated by
908          server during the import. This is due to upstream bug #72368.
909
910   LRU dump backup
911       This feature reduces the warm up time by restoring  buffer  pool  state
912       from  ib_lru_dump  file  after  restart.  Percona  XtraBackup discovers
913       ib_lru_dump and backs it up automatically.  [image]
914
915       If the buffer restore option is enabled in my.cnf buffer pool  will  be
916       in  the  warm  state  after  backup is restored. To enable this set the
917       variable innodb_buffer_pool_restore_at_startup =1 in Percona Server 5.5
918       or innodb_auto_lru_dump =1 in Percona Server 5.1.
919

IMPLEMENTATION

921   Implementation Details
922       This  page contains notes on various internal aspects of the xtrabackup
923       tool's operation.
924
925   File Permissions
926       xtrabackup opens the source data files in read-write mode, although  it
927       does not modify the files. This means that you must run xtrabackup as a
928       user who has permission to write the data files. The reason for opening
929       the  files in read-write mode is that xtrabackup uses the embedded Inn‐
930       oDB libraries to open and read the files,  and  InnoDB  opens  them  in
931       read-write  mode  because  it  normally assumes it is going to write to
932       them.
933
934   Tuning the OS Buffers
935       Because xtrabackup reads large amounts of data from the filesystem,  it
936       uses  posix_fadvise()  where possible, to instruct the operating system
937       not to try to cache the blocks it reads from disk. Without  this  hint,
938       the  operating  system  would prefer to cache the blocks, assuming that
939       xtrabackup is likely to need them again, which is not the case. Caching
940       such  large  files can place pressure on the operating system's virtual
941       memory and cause other processes, such as the database  server,  to  be
942       swapped out. The xtrabackup tool avoids this with the following hint on
943       both the source and destination files:
944
945          posix_fadvise(file, 0, 0, POSIX_FADV_DONTNEED)
946
947       In addition, xtrabackup asks  the  operating  system  to  perform  more
948       aggressive read-ahead optimizations on the source files:
949
950          posix_fadvise(file, 0, 0, POSIX_FADV_SEQUENTIAL)
951
952   Copying Data Files
953       When  copying  the data files to the target directory, xtrabackup reads
954       and writes 1MB of data at a time. This is not configurable. When  copy‐
955       ing the log file, xtrabackup reads and writes 512 bytes at a time. This
956       is also not possible to configure, and matches InnoDB's behavior (work‐
957       around  exists  in Percona Server because it has an option to tune inn‐
958       odb_log_block_size for XtraDB, and in that case Percona XtraBackup will
959       match the tuning).
960
961       After reading from the files, xtrabackup iterates over the 1MB buffer a
962       page at a time, and checks for page corruption on each page  with  Inn‐
963       oDB's  buf_page_is_corrupted()  function.  If  the  page is corrupt, it
964       re-reads and retries up to 10 times for each page. It skips this  check
965       on the doublewrite buffer.
966
967   xtrabackup Exit Codes
968       The  xtrabackup  binary  exits  with the traditional success value of 0
969       after a backup when no error occurs. If  an  error  occurs  during  the
970       backup, the exit value is 1.
971
972       In  certain  cases,  the exit value can be something other than 0 or 1,
973       due to the command-line option code included from the MySQL  libraries.
974       An unknown command-line option, for example, will cause an exit code of
975       255.
976

REFERENCES

978   The xtrabackup Option Reference
979       This page documents all of the command-line options for the  xtrabackup
980       binary.
981
982   Options
983       --apply-log-only
984              This option causes only the redo stage to be performed when pre‐
985              paring a backup. It is very important for incremental backups.
986
987       --backup
988              Make a backup and place  it  in  --target-dir.  See  Creating  a
989              backup.
990
991       --binlog-info
992              This  option  controls  how  Percona  XtraBackup should retrieve
993              server's binary log coordinates  corresponding  to  the  backup.
994              Possible  values are OFF, ON, LOCKLESS and AUTO. See the Percona
995              XtraBackup lockless_bin-log  manual page for more information
996
997       --close-files
998              Do not keep files opened. When xtrabackup  opens  tablespace  it
999              normally  doesn't  close  its file handle in order to handle the
1000              DDL operations correctly. However, if the number of  tablespaces
1001              is  really  huge  and  can  not  fit into any limit, there is an
1002              option to close file handles once they are no  longer  accessed.
1003              Percona  XtraBackup  can  produce inconsistent backups with this
1004              option enabled. Use at your own risk.
1005
1006       --compact
1007              Create a compact backup by skipping secondary index pages.
1008
1009       --compress
1010              This option  tells  xtrabackup  to  compress  all  output  data,
1011              including  the  transaction  log file and meta data files, using
1012              the specified compression algorithm.  The  only  currently  sup‐
1013              ported  algorithm  is  'quicklz'.  The  resulting files have the
1014              qpress archive format, i.e. every *.qp file  produced  by  xtra‐
1015              backup  is  essentially  a  one-file  qpress  archive and can be
1016              extracted and uncompressed by the qpress  file archiver.
1017
1018       --compress-chunk-size=#
1019              Size of working buffer(s) for compression threads in bytes.  The
1020              default value is 64K.
1021
1022       --compress-threads=#
1023              This option specifies the number of worker threads used by xtra‐
1024              backup for parallel data compression. This option defaults to 1.
1025              Parallel compression ('--compress-threads') can be used together
1026              with parallel file copying ('--parallel'). For example,  '--par‐
1027              allel=4   --compress  --compress-threads=2'  will  create  4  IO
1028              threads that will read the data and pipe  it  to  2  compression
1029              threads.
1030
1031       --create-ib-logfile
1032              This  option  is not currently implemented. To create the InnoDB
1033              log files, you must prepare the backup twice at present.
1034
1035       --datadir=DIRECTORY
1036              The source directory for the backup. This should be the same  as
1037              the  datadir  for  your  MySQL server, so it should be read from
1038              my.cnf if that exists; otherwise you must specify it on the com‐
1039              mand line.
1040
1041       --defaults-extra-file=[MY.CNF]
1042              Read this file after the global files are read. Must be given as
1043              the first option on the command-line.
1044
1045       --defaults-file=[MY.CNF]
1046              Only read default options from the given file. Must be given  as
1047              the  first  option  on the command-line. Must be a real file; it
1048              cannot be a symbolic link.
1049
1050       --defaults-group=GROUP-NAME
1051              This option is to set the group which should be  read  from  the
1052              configuration  file. This is used by innobackupex if you use the
1053              --defaults-group option. It is needed for  mysqld_multi  deploy‐
1054              ments.
1055
1056       --export
1057              Create files necessary for exporting tables. See Restoring Indi‐
1058              vidual Tables.
1059
1060       --extra-lsndir=DIRECTORY
1061              (for --backup): save an extra copy of the xtrabackup_checkpoints
1062              file in this directory.
1063
1064       --incremental-basedir=DIRECTORY
1065              When  creating an incremental backup, this is the directory con‐
1066              taining the full backup that is the base dataset for the  incre‐
1067              mental backups.
1068
1069       --incremental-dir=DIRECTORY
1070              When  preparing  an  incremental  backup,  this is the directory
1071              where the incremental backup is combined with the full backup to
1072              make a new full backup.
1073
1074       --incremental-force-scan
1075              When  creating  an  incremental backup, force a full scan of the
1076              data pages in the instance being backuped even if  the  complete
1077              changed page bitmap data is available.
1078
1079       --incremental-lsn=LSN
1080              When  creating  an  incremental  backup, you can specify the log
1081              sequence     number     (LSN)     instead     of      specifying
1082              --incremental-basedir.  For  databases created by MySQL and Per‐
1083              cona Server 5.0-series versions, specify the LSN as  two  32-bit
1084              integers  in  high:low  format. For databases created in 5.1 and
1085              later, specify the LSN as a  single  64-bit  integer.   ##ATTEN‐
1086              TION##:  If  a  wrong LSN value is specified (a user error which
1087              XtraBackup is unable to detect), the backup will be unusable. Be
1088              careful!
1089
1090       --innodb-log-arch-dir=DIRECTORY
1091              This  option  is  used  to  specify the directory containing the
1092              archived logs. It can only be used with the xtrabackup --prepare
1093              option.
1094
1095       --innodb-miscellaneous
1096              There  is a large group of InnoDB options that are normally read
1097              from the my.cnf configuration file, so that xtrabackup boots  up
1098              its  embedded  InnoDB  in the same configuration as your current
1099              server. You normally do not need to  specify  these  explicitly.
1100              These options have the same behavior that they have in InnoDB or
1101              XtraDB. They are as follows:
1102
1103                 --innodb-adaptive-hash-index
1104                 --innodb-additional-mem-pool-size
1105                 --innodb-autoextend-increment
1106                 --innodb-buffer-pool-size
1107                 --innodb-checksums
1108                 --innodb-data-file-path
1109                 --innodb-data-home-dir
1110                 --innodb-doublewrite-file
1111                 --innodb-doublewrite
1112                 --innodb-extra-undoslots
1113                 --innodb-fast-checksum
1114                 --innodb-file-io-threads
1115                 --innodb-file-per-table
1116                 --innodb-flush-log-at-trx-commit
1117                 --innodb-flush-method
1118                 --innodb-force-recovery
1119                 --innodb-io-capacity
1120                 --innodb-lock-wait-timeout
1121                 --innodb-log-buffer-size
1122                 --innodb-log-files-in-group
1123                 --innodb-log-file-size
1124                 --innodb-log-group-home-dir
1125                 --innodb-max-dirty-pages-pct
1126                 --innodb-open-files
1127                 --innodb-page-size
1128                 --innodb-read-io-threads
1129                 --innodb-write-io-threads
1130
1131       --log-copy-interval=#
1132              This option specifies time interval between checks done  by  log
1133              copying thread in milliseconds (default is 1 second).
1134
1135       --log-stream
1136              Makes xtrabackup not copy data files, and output the contents of
1137              the InnoDB log files to STDOUT until the  --suspend-at-end  file
1138              is deleted. This option enables --suspend-at-end automatically.
1139
1140       --no-defaults
1141              Don't  read  default options from any option file. Must be given
1142              as the first option on the command-line.
1143
1144       --databases=#
1145              This option specifies the list  of  databases  and  tables  that
1146              should  be  backed  up.  The option accepts the list of the form
1147              "databasename1[.table_name1] databasename2[.table_name2] . . .".
1148
1149       --databases-file=#
1150              This option specifies the path to the file containing  the  list
1151              of  databases  and tables that should be backed up. The file can
1152              contain  the  list  elements  of  the  form   databasename1[.ta‐
1153              ble_name1], one element per line.
1154
1155       --parallel=#
1156              This  option specifies the number of threads to use to copy mul‐
1157              tiple data  files  concurrently  when  creating  a  backup.  The
1158              default value is 1 (i.e., no concurrent transfer).
1159
1160       --prepare
1161              Makes  xtrabackup  perform  recovery  on  a  backup created with
1162              --backup, so that it is ready to use. See preparing a backup.
1163
1164       --print-defaults
1165              Print the program argument list and exit. Must be given  as  the
1166              first option on the command-line.
1167
1168       --print-param
1169              Makes xtrabackup print out parameters that can be used for copy‐
1170              ing the data files back to their original locations  to  restore
1171              them. See scripting-xtrabackup.
1172
1173       --rebuild_indexes
1174              Rebuild  secondary  indexes  in InnoDB tables after applying the
1175              log. Only has effect with --prepare.
1176
1177       --rebuild_threads=#
1178              Use this number of threads  to  rebuild  indexes  in  a  compact
1179              backup. Only has effect with --prepare and --rebuild-indexes.
1180
1181       --secure-auth
1182              Refuse  client  connecting  to server if it uses old (pre-4.1.1)
1183              protocol. (Enabled by default; use  --skip-secure-auth  to  dis‐
1184              able.)
1185
1186       --stats
1187              Causes xtrabackup to scan the specified data files and print out
1188              index statistics.
1189
1190       --stream=name
1191              Stream all backup files to the standard output in the  specified
1192              format. Currently supported formats are 'xbstream' and 'tar'.
1193
1194       --suspend-at-end
1195              Causes  xtrabackup  to create a file called xtrabackup_suspended
1196              in the --target-dir.  Instead  of  exiting  after  copying  data
1197              files,  xtrabackup  continues  to  copy  the log file, and waits
1198              until the xtrabackup_suspended file  is  deleted.  This  enables
1199              xtrabackup  and  other  programs  to  coordinate their work. See
1200              scripting-xtrabackup.
1201
1202       --tables=name
1203              A regular  expression  against  which  the  full  tablename,  in
1204              databasename.tablename  format, is matched. If the name matches,
1205              the table is backed up. See partial backups.
1206
1207       --tables-file=name
1208              A  file  containing  one  table  name  per  line,  in  database‐
1209              name.tablename  format. The backup will be limited to the speci‐
1210              fied tables. See scripting-xtrabackup.
1211
1212       --target-dir=DIRECTORY
1213              This option specifies the destination directory for the  backup.
1214              If  the  directory does not exist, xtrabackup creates it. If the
1215              directory does exist and  is  empty,  xtrabackup  will  succeed.
1216              xtrabackup  will  not overwrite existing files, however; it will
1217              fail with operating system error 17, file exists.
1218
1219              If this option is a relative path, it is  interpreted  as  being
1220              relative  to the current working directory from which xtrabackup
1221              is executed.
1222
1223       --throttle=#
1224              This  option  limits  --backup  to  the  specified   number   of
1225              read+write  pairs  of  operations  per  second. See throttling a
1226              backup.
1227
1228       --tmpdir=name
1229              This option is currently not used for anything  except  printing
1230              out the correct tmpdir parameter when --print-param is used.
1231
1232       --to-archived-lsn=LSN
1233              This  option is used to specify the LSN to which the logs should
1234              be applied when backups are being prepared. It can only be  used
1235              with the xtrabackup --prepare option.
1236
1237       --use-memory=#
1238              This option affects how much memory is allocated for preparing a
1239              backup with --prepare, or analyzing statistics with --stats. Its
1240              purpose  is  similar  to innodb_buffer_pool_size. It does not do
1241              the same thing as the similarly named option in Oracle's  InnoDB
1242              Hot  Backup  tool.  The  default value is 100MB, and if you have
1243              enough available memory, 1GB to 2GB is a good recommended value.
1244              Multiples  are  supported providing the unit (e.g. 1MB, 1M, 1GB,
1245              1G).
1246
1247       --version
1248              This option prints xtrabackup version and exits.
1249

AUTHOR

1251       Percona LLC and/or its affiliates
1252
1254       2009-2016, Percona LLC and/or its affiliates
1255
1256
1257
1258
12592.3.5                            Jan 04, 2019                    XTRABACKUP(1)
Impressum