]]>
]]>

This is an old revision of the document!


XtraBackup Howto

This page contains basic receipts how to use xtrabackup tool

Make local backup

Very basic and very simple mode, copy MySQL data into specific directory

Make copy

innobackupex-1.5.1 /data/backups

There a lot of output, but the most important one to look for:

100313 02:43:07  innobackupex-1.5.1: completed OK!

If backup is succesfull, there will be directory with timestamp on the moment of backup, i.e. /data/backups/2010-03-13_02-42-44

Run apply-log

Apply-log step is very important, as it makes data ready to use.

In basic case it is very simple:

innobackupex-1.5.1 --apply-log /data/backups/2010-03-13_02-42-44/

Now directory /data/backups/2010-03-13_02-42-44 contains data directory which can be used mysql, you just copy it to mysql datadir ( regular cp command makes it)

:!: Important :!: To speedup apply-log process it is recommended to use —use-memory option. This option is similar to innodb_buffer_pool, and on dedicated box you may set up to 80% of available memory.

Make backup from slave

If you make backup from slave you want to see slave position at the moment of backup. You can get this with –slave-info option

innobackupex-1.5.1 --slave-info /data/backups

After finish you will find file xtrabackup_slave_info in backup directory with info like this:

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=5068932

Stream

stream mode allow to get on stdout output compatible with tar. :!: to extract from, you should use -i option

Basic mode

innobackupex-1.5.1  --stream=tar ./ > backup.tar

Backup & compress

innobackupex-1.5.1  --stream=tar ./ | gzip - > backup.tar.gz

Backup on remote host

innobackupex-1.5.1 --stream=tar ./ | ssh vadim@desthost "cat - > /data/vol1/mysqluc/backup.tar"

or the same with netcat (faster)

on desthost host:
nc -l 9999 | cat - > backup.tar

on source host:
innobackupex-1.5.1 --defaults-file=/etc/my.opt.cnf --stream=tar ./ | nc desthost 9999

Backup on remote host with throttling

you need pv utils for that. http://www.ivarch.com/programs/quickref/pv.shtml

Next example copies to desthost with 10M/sec limit.

innobackupex-1.5.1 --stream=tar ./ | pv -q -L10m | ssh vadim@desthost "cat - > /data/vol1/mysqluc/backup.tar"

Incremental backup

As this is unique feature for xtrabackup, it is not yet fully supported in innobackupex

1. Perform regular backup

innobackupex-1.5.1 /data/backups

2. Perform incremental

xtrabackup --backup --target-dir=/data/backups/incremental --incremental-basedir=/data/backups/2010-04-11_17-55-54

Restore:

3. Apply-log for regular backup

innobackupex-1.5.1 --apply-log /data/backups/2010-04-11_17-55-54/

4. Apply incremental changes

xtrabackup --prepare --target-dir=/data/backups/2010-04-11_17-55-54  --incremental-dir=/data/backups/incremental

export / import

export only single table

xtrabackup  --table=tpcc.order_line --backup --target-dir=/data/vadim/mysqluc/order_line/

Prepare it

xtrabackup --prepare --export  --target-dir=/data/vadim/mysqluc/order_line/

on target server:

alter table order_line discard tablespace;
set global innodb_expand_import=1;
 
percona-xtrabackup/xtrabackup_howto.1271039422.txt.gz · Last modified: 2011/07/17 21:05 (external edit)
Except where otherwise noted, content on this wiki is licensed under the following license:CC Attribution-Noncommercial-Share Alike 3.0 Unported
Contact Us 24 Hours A Day
SupportContact us 24×7
Emergency? Contact us for help now!
Sales North America(888) 316-9775 or
(208) 473-2904
Sales
Europe
+44-208-133-0309 (UK)
0-800-051-8984 (UK)
0-800-181-0665 (GER)
Training(855) 55TRAIN or
(925) 271-5054

 

Share This
]]> ]]>