innobackupex --include doc request

[Valentine Gostev]

At the current moment lp:percona-xtrabackup has the innobackupex version with fixed --include options, which allows to create partial backups, specifying what MyISAM and InnoDB tables should be included to backup.

I would like to see documented option --include (especially the restore way)

1. If we restore backup into clean mysql directory we should use innobackupex with option --copy-back (after preparing backup)

2. If we want to restore some tables to the server which is in use and has some other tables we want to keep using - we should use export/import feature to restore InnoDB tables. (partial restore).

[Valentine Gostev (longbow)]

If we just manually copy the tables' files from backup to its original location everything will be fine.

[Peter Zaitsev (pz-percona)]

Valentine,

There are few things here

1) Note without ibdata tablespace files are unusable in general sense.
There could be unmerged insert buffer entries for example not to mention
data dictionary.
2) In general the restore for partial backup may happen to the original
server as well as to empty server. Consider the partial backup just backed
up most important
tables and rest are recreated
3) If we restored ibdata1 and only some of ibd files we'll get data
dictionary entries in ibdata1 which d not have matching .ibd files which
is inconsistent database.

We need to ensure to think through what kind of partial backup and recovery
scenarios could be. To me it looks like to have working partial database
backup and restore
you actually need to "export" the tables during backup so they are self
contained and when restore them by importing in Percona Server.

On Fri, Feb 18, 2011 at 8:00 AM, Valentine Gostev <<email address hidden>
> wrote:

> Public bug reported:
>
> innobackupex from trunk
> DB server: Percona Server 5.1.54-12.5 rev 188
>
> When we create a partial backup with innobackupex, it creates the same
> directory structure like in mysql datadir, but puts only tables' files
> accoring to REGEXP.
> Also it copies ibdata1 file. If we restore backup with --copy-back, InnoDB
> tables which were not backuped will become unavailable:
>
> mysql> use include
> Database changed
> mysql> create table t1 (a varchar(20)) ENGINE=MyISAM;
> Query OK, 0 rows affected (0.01 sec)
>
> mysql> create table t2 (a varchar(20)) ENGINE=InnoDB;
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> create table m1 (a varchar(20)) ENGINE=MyISAM;
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> create table m2 (a varchar(20)) ENGINE=InnoDB;
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> insert into t1 values (1),(2),(3);
> Query OK, 3 rows affected (0.00 sec)
> Records: 3 Duplicates: 0 Warnings: 0
>
> mysql> insert into t2 values (1),(2),(3);
> Query OK, 3 rows affected (0.00 sec)
> Records: 3 Duplicates: 0 Warnings: 0
>
> mysql> insert into m1 values (1),(2),(3);
> Query OK, 3 rows affected (0.00 sec)
> Records: 3 Duplicates: 0 Warnings: 0
>
> mysql> insert into m2 values (1),(2),(3);
> Query OK, 3 rows affected (0.00 sec)
> Records: 3 Duplicates: 0 Warnings: 0
>
> root@dev302:~# innobackupex --include="^include[.]t" /root/backup >
> /dev/null 2>&1
> root@dev302:~# innobackupex --apply-log /root/backup/2011-02-18_18-40-41/
> > /dev/null 2>&1
> root@dev302:~# ls -lah backup/2011-02-18_18-40-41/
> total 23M
> drwxr-xr-x 5 root root 4.0K Feb 18 18:41 .
> drwxr-xr-x 3 root root 4.0K Feb 18 18:40 ..
> -rw-r--r-- 1 root root 342 Feb 18 18:40 backup-my.cnf
> -rw-r--r-- 1 root root 5.0M Feb 18 18:41 ib_logfile0
> -rw-r--r-- 1 root root 5.0M Feb 18 18:41 ib_logfile1
> -rw-r--r-- 1 root root 10M Feb 18 18:41 ibdata1
> drwx------ 2 root root 4.0K Feb 18 18:41 include
> drwxr-xr-x 2 root root 4.0K Feb 18 18:41 mysql
> -rw-r--r-- 1 root root 0 Feb 18 18:40 mysql-stderr
> -rw-r--r-- 1 root root 312 Feb 18 18:41 mysql-stdout
> drwx------ 2 root root 4.0K Feb 18 18:40 test
> -rw-r--r-- 1 root root 10 Feb 18 18:41 xtrabackup_binary
> -rw-r--r-- 1 root ro...

[Valentine Gostev (longbow)]

Peter, thanks for you comment. We have discussed this issue with Vadim. I will convert this bug to documenation request - I think that it is not obvious that one should use import feature instead of usual restore procedure.

[Rodrigo Gadea (rodrigo-gadea-percona)]

Documented in new docs:
http://www.percona.com/docs/beta/xtrabackup/innobackupex/partial_backups_innobackupex.html

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXB-559