xtrabackup1.6.2 can't recover table if you ddl(eg:create table) after full backup
杰 李
mysql> CREATE DATABASE a;
Query OK, 1 ROW affected (0.04 sec)
mysql> USE a
DATABASE changed
mysql> CREATE TABLE t1(a1 INT) engine=innodb;
Query OK, 0 ROWS affected (0.31 sec)
mysql> INSERT INTO t1 VALUES(1);
Query OK, 1 ROW affected (0.11 sec)
2、full backup
innobackupex --user=root /usr/local/mysql/backup
the full backup is succesful!
3、write some incremental data
mysql> CREATE TABLE t2(a2 INT) engine=innodb;
Query OK, 0 ROWS affected (0.26 sec)
mysql> INSERT INTO t2 VALUES(2);
Query OK, 1 ROW affected (0.31 sec)
4、incremental backup
innobackupex --incremental /usr/local/mysql/backup/re --incremental-
basedir=/usr/local/mysql/backup/2011-06-28_20-56-15 --user=root
log:
.........................
innobackupex: Backing up file '/usr/local/mysql/var/a/t2.frm'
.........................
attention: t2.frm is backup
5、rm mysql data file
[root@dg2 var]# mysqladmin shutdown
[root@dg2 var]# rm -rf *
6、prepare full backup
innobackupex --apply-log --redo-only /usr/local/mysql/backup/
2011-06-28_20-56-15 --user=root
7、prepare incremental backup
innobackupex --apply-log /usr/local/mysql/backup/2011-06-28_20-56-15 --
incremental-dir=/usr/local/mysql/backup/re/2011-06-28_21-01-41 --
user=root
log:
xtrabackup: page SIZE FOR /usr/LOCAL/mysql/backup/re/
2011-06-28_21-01-41/a/t2.ibd.delta IS 16384 bytes
110628 21:03:09 InnoDB: Operating system error NUMBER 2 IN a file
operation.
InnoDB: The error means the system cannot find the path specified.
xtrabackup: error: cannot OPEN /usr/LOCAL/mysql/backup/
2011-06-28_20-56-15/a/t2.ibd
xtrabackup: Error: xtrabackup_apply_delta() failed.
...................................................
110628 21:03:09 InnoDB: DATABASE was NOT shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information FROM the .ibd files...
110628 21:03:10 InnoDB: Error: TABLE 'a/t2'
InnoDB: IN InnoDB DATA dictionary has tablespace id 3,
InnoDB: but tablespace WITH that id OR name does NOT exist. It will be
removed FROM DATA dictionary.
attention:table t2 is can't prepare
8、
[root@dg2 2011-06-28_20-56-15]# cp -R * /usr/local/mysql/var
chown...
9、t2 is lost
[root@dg2 bin]# ./mysqld_safe --user=mysql &
mysql> USE a;
DATABASE changed
mysql> SHOW TABLES;
+-------------+
| Tables_in_a |
+-------------+
| t1 |
+-------------+
I find t2 table in information_schema.tables,because t2 table metadata
is stored in share tablespace
question:
1、why t2 table is lost?
2、how can i recover? i can find t2.frm in backupset
thanks a lot!
杰 李
help me?
thank you!
Valentine Gostev
you have hit bug https://bugs.launchpad.net/percona-xtrabackup/+bug/759701
While applying incremental changes with innobackupex script only
InnoDB's incremental data is being applied to a full backup.
So after running innobackupex to restore incremental backup the t2.ibd
file was copied, but t2.frm file was not. That's why you see problems.
It is in plans for fix, but unfortunately I can not provide precise
estimation right now.
11.08.2011 15:02, 杰 李 пишет:
--
--------------------------
Valentine Gostev
Quality Assurance Engineer
Percona, Inc.
+1-888-401-3401 Ext. 518 (GMT +0700)
http://www.percona.com
杰 李
I am concerned about this problem,because our production database is
very big,if full backup always unfinished.
On 9月2日, 上午9时03分, Valentine Gostev <valentine.gos...@percona.com>
wrote:
> Hi,
> you have hit bughttps://bugs.launchpad.net/percona-xtrabackup/+bug/759701
Haidong Ji
> I am concerned about this problem,because our production database is
> very big,if full backup always unfinished.
>
If you'd like to have this issue resolved quickly, it is possible to
provide sponsorship to our development team, which will bump it up in
the priority queue, thereby enabling us to have resources dedicated to
this task.
We've used the sponsoring model successfully for MySQL bug fixing
and development, Percona Tool Kit (formerly MaatKit tools) development,
etc.
Let us know if that is something you are interested in.
Haidong
> On 9锟斤拷2锟斤拷, 锟斤拷锟斤拷9时03锟斤拷, Valentine Gostev
> <valentine.gos...@percona.com> wrote:
> > Hi,
> > you have hit
> > bughttps://bugs.launchpad.net/percona-xtrabackup/+bug/759701
> >
> > While applying incremental changes with innobackupex script only
> > InnoDB's incremental data is being applied to a full backup.
> >
> > So after running innobackupex to restore incremental backup the
> > t2.ibd file was copied, but t2.frm file was not. That's why you see
> > problems.
> >
> > It is in plans for fix, but unfortunately I can not provide precise
> > estimation right now.
> >
> > 11.08.2011 15:02, 锟斤拷 锟斤拷 锟斤拷讧锟街э拷:
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > > 1锟斤拷prepare test data
> >
> > > mysql> CREATE DATABASE a;
> > > Query OK, 1 ROW affected (0.04 sec)
> > > mysql> USE a
> > > DATABASE changed
> > > mysql> CREATE TABLE t1(a1 INT) engine=innodb;
> > > Query OK, 0 ROWS affected (0.31 sec)
> > > mysql> INSERT INTO t1 VALUES(1);
> > > Query OK, 1 ROW affected (0.11 sec)
> >
> > > 2锟斤拷full backup
> >
> > > innobackupex --user=root /usr/local/mysql/backup
> >
> > > the full backup is succesful!
> >
> > > 3锟斤拷write some incremental data
> >
> > > mysql> CREATE TABLE t2(a2 INT) engine=innodb;
> > > Query OK, 0 ROWS affected (0.26 sec)
> > > mysql> INSERT INTO t2 VALUES(2);
> > > Query OK, 1 ROW affected (0.31 sec)
> >
> > > 4锟斤拷incremental backup
> > > innobackupex --incremental /usr/local/mysql/backup/re
> > > --incremental-
> > > basedir=/usr/local/mysql/backup/2011-06-28_20-56-15 --user=root
> >
> > > log:
> > > .........................
> > > innobackupex: Backing up file '/usr/local/mysql/var/a/t2.frm'
> > > .........................
> >
> > > attention: t2.frm is backup
> >
> > > 5锟斤拷rm mysql data file
> > > [root@dg2 var]# mysqladmin shutdown
> > > [root@dg2 var]# rm -rf *
> >
> > > 6锟斤拷prepare full backup
> > > innobackupex --apply-log --redo-only /usr/local/mysql/backup/
> > > 2011-06-28_20-56-15 --user=root
> >
> > > 7锟斤拷prepare incremental backup
> > > innobackupex
> > > --apply-log /usr/local/mysql/backup/2011-06-28_20-56-15 --
> > > incremental-dir=/usr/local/mysql/backup/re/2011-06-28_21-01-41 --
> > > user=root
> >
> > > log:
> >
> > > xtrabackup: page SIZE FOR /usr/LOCAL/mysql/backup/re/
> > > 2011-06-28_21-01-41/a/t2.ibd.delta IS 16384 bytes
> > > 110628 21:03:09 InnoDB: Operating system error NUMBER 2 IN a file
> > > operation.
> > > InnoDB: The error means the system cannot find the path specified.
> > > xtrabackup: error: cannot OPEN /usr/LOCAL/mysql/backup/
> > > 2011-06-28_20-56-15/a/t2.ibd
> > > xtrabackup: Error: xtrabackup_apply_delta() failed.
> > > ...................................................
> > > 110628 21:03:09 InnoDB: DATABASE was NOT shut down normally!
> > > InnoDB: Starting crash recovery.
> > > InnoDB: Reading tablespace information FROM the .ibd files...
> > > 110628 21:03:10 InnoDB: Error: TABLE 'a/t2'
> > > InnoDB: IN InnoDB DATA dictionary has tablespace id 3,
> > > InnoDB: but tablespace WITH that id OR name does NOT exist. It
> > > will be removed FROM DATA dictionary.
> >
> > > attention:table t2 is can't prepare
> >
> > > 8锟斤拷
> > > [root@dg2 2011-06-28_20-56-15]# cp -R * /usr/local/mysql/var
> > > chown...
> >
> > > 9锟斤拷t2 is lost
> > > [root@dg2 bin]# ./mysqld_safe --user=mysql&
> >
> > > mysql> USE a;
> > > DATABASE changed
> > > mysql> SHOW TABLES;
> > > +-------------+
> > > | Tables_in_a |
> > > +-------------+
> > > | t1 |
> > > +-------------+
> >
> > > I find t2 table in information_schema.tables,because t2 table
> > > metadata is stored in share tablespace
> >
> > > question:
> >
> > > 1锟斤拷why t2 table is lost?
> > > 2锟斤拷how can i recover? i can find t2.frm in backupset