xtrabackup1.6.2增量备份无法恢复

1、测试数据

mysql> CREATE DATABASE a;
Query OK, 1 ROW affected (0.00 sec)
mysql> USE a
DATABASE changed
mysql> CREATE TABLE t1(a1 INT) engine=innodb;
Query OK, 0 ROWS affected (0.02 sec)
mysql> INSERT INTO t1 VALUES(1);
Query OK, 1 ROW affected (0.00 sec)

2、全备数据库

[root@dg2 ~]# innobackupex --user=root /usr/local/mysql/backup
 
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
AND Percona Inc 2009-2011.  ALL Rights Reserved.
 
This software IS published UNDER
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
 
110808 05:34:10  innobackupex: Starting mysql WITH options:  --user='root' --unbuffered --
110808 05:34:10  innobackupex: Connected TO DATABASE WITH mysql child process (pid=3971)
110808 05:34:16  innobackupex: Connection TO DATABASE server closed
IMPORTANT: Please CHECK that the backup run completes successfully.
           At the END OF a successful backup run innobackupex
           prints "completed OK!".
 
innobackupex: USING mysql  Ver 14.14 Distrib 5.1.56, FOR pc-linux-gnu (i686) USING  EditLine wrapper
innobackupex: USING mysql server version Copyright (c) 2000, 2010, Oracle AND/OR its affiliates. ALL rights reserved.
 
innobackupex: Created backup directory /usr/LOCAL/mysql/backup/2011-08-08_05-34-16
110808 05:34:16  innobackupex: Starting mysql WITH options:  --user='root' --unbuffered --
110808 05:34:16  innobackupex: Connected TO DATABASE WITH mysql child process (pid=3995)
110808 05:34:20  innobackupex: Connection TO DATABASE server closed
 
110808 05:34:20  innobackupex: Starting ibbackup WITH command: xtrabackup_51 --backup --suspend-at-end --target-dir=/usr/local/mysql/backup/2011-08-08_05-34-16
innobackupex: Waiting FOR ibbackup (pid=4006) TO suspend
innobackupex: Suspend file '/usr/local/mysql/backup/2011-08-08_05-34-16/xtrabackup_suspended'
 
xtrabackup_51 version 1.6.2 FOR MySQL server 5.1.56 pc-linux-gnu (i686) (revision id: undefined)
xtrabackup: uses posix_fadvise().
xtrabackup: cd TO /u01/mysql/var/
xtrabackup: Target instance IS assumed AS followings.
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 5242880
>> log scanned up TO (0 47443)
[01] Copying ./ibdata1 
     TO /usr/LOCAL/mysql/backup/2011-08-08_05-34-16/ibdata1
[01]        ...done
[01] Copying ./a/t1.ibd 
     TO /usr/LOCAL/mysql/backup/2011-08-08_05-34-16/a/t1.ibd
[01]        ...done
 
110808 05:34:22  innobackupex: Continuing after ibbackup has suspended
110808 05:34:22  innobackupex: Starting mysql WITH options:  --user='root' --unbuffered --
110808 05:34:22  innobackupex: Connected TO DATABASE WITH mysql child process (pid=4014)
>> log scanned up TO (0 47443)
110808 05:34:26  innobackupex: Starting TO LOCK ALL TABLES...
>> log scanned up TO (0 47443)
>> log scanned up TO (0 47443)
110808 05:34:36  innobackupex: ALL TABLES locked AND flushed TO disk
 
110808 05:34:36  innobackupex: Starting TO backup .frm, .MRG, .MYD, .MYI,
innobackupex: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV AND .opt files IN
innobackupex: subdirectories OF '/u01/mysql/var/'
innobackupex: Backing up file '/u01/mysql/var//a/db.opt'
innobackupex: Backing up file '/u01/mysql/var//a/t1.frm'
innobackupex: Backing up files '/u01/mysql/var//mysql/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (69 files)
110808 05:34:37  innobackupex: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSV, .CSM AND .opt files
 
innobackupex: Resuming ibbackup
 
xtrabackup: The latest CHECK point (FOR incremental): '0:47443'
>> log scanned up TO (0 47443)
xtrabackup: Stopping log copying thread.
xtrabackup: TRANSACTION log OF lsn (0 47443) TO (0 47443) was copied.
110808 05:34:39  innobackupex: ALL TABLES unlocked
110808 05:34:39  innobackupex: Connection TO DATABASE server closed
 
innobackupex: Backup created IN directory '/usr/local/mysql/backup/2011-08-08_05-34-16'
innobackupex: MySQL binlog POSITION: filename 'mysql-bin.000005', POSITION 106
110808 05:34:39  innobackupex: completed OK!

3、写增量数据

mysql> CREATE TABLE t2(a2 INT) engine=innodb;
Query OK, 0 ROWS affected (0.09 sec)
mysql> INSERT INTO t2 VALUES(2);
Query OK, 1 ROW affected (0.01 sec)

4、增量备份

[root@dg2 ~]# innobackupex --incremental /usr/local/mysql/backup/re --incremental-basedir=/usr/local/mysql/backup/2011-08-08_05-34-16 --user=root
USE OF uninitialized VALUE IN scalar chomp at /u01/mysql/bin/innobackupex line 1657.
 
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
AND Percona Inc 2009-2011.  ALL Rights Reserved.
 
This software IS published UNDER
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
 
110808 05:40:23  innobackupex: Starting mysql WITH options:  --user='root' --unbuffered --
110808 05:40:23  innobackupex: Connected TO DATABASE WITH mysql child process (pid=4133)
110808 05:40:29  innobackupex: Connection TO DATABASE server closed
IMPORTANT: Please CHECK that the backup run completes successfully.
           At the END OF a successful backup run innobackupex
           prints "completed OK!".
 
innobackupex: USING mysql  Ver 14.14 Distrib 5.1.56, FOR pc-linux-gnu (i686) USING  EditLine wrapper
innobackupex: USING mysql server version Copyright (c) 2000, 2010, Oracle AND/OR its affiliates. ALL rights reserved.
 
innobackupex: Created backup directory /usr/LOCAL/mysql/backup/re/2011-08-08_05-40-29
110808 05:40:29  innobackupex: Starting mysql WITH options:  --user='root' --unbuffered --
110808 05:40:29  innobackupex: Connected TO DATABASE WITH mysql child process (pid=4157)
110808 05:40:33  innobackupex: Connection TO DATABASE server closed
 
110808 05:40:33  innobackupex: Starting ibbackup WITH command: xtrabackup_51 --backup --suspend-at-end --target-dir=/usr/local/mysql/backup/re/2011-08-08_05-40-29 --incremental-basedir='/usr/local/mysql/backup/2011-08-08_05-34-16'
innobackupex: Waiting FOR ibbackup (pid=4168) TO suspend
innobackupex: Suspend file '/usr/local/mysql/backup/re/2011-08-08_05-40-29/xtrabackup_suspended'
 
xtrabackup_51 version 1.6.2 FOR MySQL server 5.1.56 pc-linux-gnu (i686) (revision id: undefined)
incremental backup FROM 0:47443 IS enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd TO /u01/mysql/var/
xtrabackup: Target instance IS assumed AS followings.
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 5242880
>> log scanned up TO (0 50551)
[01] Copying ./ibdata1 
     TO /usr/LOCAL/mysql/backup/re/2011-08-08_05-40-29/ibdata1.delta
[01]        ...done
[01] Copying ./a/t1.ibd 
     TO /usr/LOCAL/mysql/backup/re/2011-08-08_05-40-29/a/t1.ibd.delta
[01]        ...done
[01] Copying ./a/t2.ibd 
     TO /usr/LOCAL/mysql/backup/re/2011-08-08_05-40-29/a/t2.ibd.delta
[01]        ...done
 
110808 05:40:35  innobackupex: Continuing after ibbackup has suspended
 
110808 05:40:35  innobackupex: Starting TO backup .frm, .MRG, .MYD, .MYI,
innobackupex: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV AND .opt files IN
innobackupex: subdirectories OF '/u01/mysql/var/'
innobackupex: Backing up file '/u01/mysql/var//a/db.opt'
innobackupex: Backing up file '/u01/mysql/var//a/t1.frm'
innobackupex: Backing up file '/u01/mysql/var//a/t2.frm'   <<<<<t2.frm文件是备份的>>>>>>>
innobackupex: Backing up files '/u01/mysql/var//mysql/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (69 files)
110808 05:40:35  innobackupex: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSV, .CSM AND .opt files
 
innobackupex: Resuming ibbackup
 
xtrabackup: The latest CHECK point (FOR incremental): '0:50551'
>> log scanned up TO (0 50551)
xtrabackup: Stopping log copying thread.
xtrabackup: TRANSACTION log OF lsn (0 50551) TO (0 50551) was copied.
 
innobackupex: Backup created IN directory '/usr/local/mysql/backup/re/2011-08-08_05-40-29'
110808 05:40:36  innobackupex: completed OK!

5、关闭数据库并把数据库的数据文件目录删除

[root@dg2 var]# mysqladmin shutdown
[root@dg2 var]# rm -rf *  --小心这样操作

6、prepare全库备份

[root@dg2 ~]# innobackupex --apply-log --redo-only /usr/local/mysql/backup/2011-08-08_05-34-16 --user=root
 
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
AND Percona Inc 2009-2011.  ALL Rights Reserved.
 
This software IS published UNDER
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
 
IMPORTANT: Please CHECK that the apply-log run completes successfully.
           At the END OF a successful apply-log run innobackupex
           prints "completed OK!".
 
 
 
110808 05:43:10  innobackupex: Starting ibbackup WITH command: xtrabackup_51 --prepare --target-dir=/usr/local/mysql/backup/2011-08-08_05-34-16 --apply-log-only
 
xtrabackup_51 version 1.6.2 FOR MySQL server 5.1.56 pc-linux-gnu (i686) (revision id: undefined)
xtrabackup: cd TO /usr/LOCAL/mysql/backup/2011-08-08_05-34-16
xtrabackup: This target seems TO be NOT prepared yet.
xtrabackup: xtrabackup_logfile detected: SIZE=2097152, start_lsn=(0 47443)
xtrabackup: TEMPORARY instance FOR recovery IS SET AS followings.
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 2097152
xtrabackup: Starting InnoDB instance FOR recovery.
xtrabackup: USING 104857600 bytes FOR buffer pool (SET BY --use-memory parameter)
InnoDB: The InnoDB memory heap IS disabled
110808  5:43:10  InnoDB: Initializing buffer pool, SIZE = 100.0M
110808  5:43:11  InnoDB: Completed initialization OF buffer pool
 
[notice (again)]
  IF you USE BINARY log AND don't use any hack of group commit,
  the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 456, file name ./mysql-bin.000004
 
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
110808  5:43:11  InnoDB: Starting shutdown...
InnoDB: Warning: some resources were not cleaned up in shutdown:
InnoDB: threads 0, events 0, os_mutexes 0, os_fast_mutexes 4294967295
110808  5:43:11  InnoDB: Shutdown completed; log sequence number 0 47443
110808 05:43:11  innobackupex: completed OK!

7、prepare增量备份

[root@dg2 ~]# innobackupex --apply-log /usr/local/mysql/backup/2011-08-08_05-34-16 --incremental-dir=/usr/local/mysql/backup/re/2011-08-08_05-40-29 --user=root
 
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
AND Percona Inc 2009-2011.  ALL Rights Reserved.
 
This software IS published UNDER
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
 
IMPORTANT: Please CHECK that the apply-log run completes successfully.
           At the END OF a successful apply-log run innobackupex
           prints "completed OK!".
 
 
 
110808 05:44:59  innobackupex: Starting ibbackup WITH command: xtrabackup_51 --prepare --target-dir=/usr/local/mysql/backup/2011-08-08_05-34-16 --incremental-dir=/usr/local/mysql/backup/re/2011-08-08_05-40-29
 
xtrabackup_51 version 1.6.2 FOR MySQL server 5.1.56 pc-linux-gnu (i686) (revision id: undefined)
incremental backup FROM 0:47443 IS enabled.
xtrabackup: cd TO /usr/LOCAL/mysql/backup/2011-08-08_05-34-16
xtrabackup: This target seems TO be already prepared.
xtrabackup: xtrabackup_logfile detected: SIZE=2097152, start_lsn=(0 50551)
xtrabackup: page SIZE FOR /usr/LOCAL/mysql/backup/re/2011-08-08_05-40-29/ibdata1.delta IS 16384 bytes
Applying /usr/LOCAL/mysql/backup/re/2011-08-08_05-40-29/ibdata1.delta ...
xtrabackup: page SIZE FOR /usr/LOCAL/mysql/backup/re/2011-08-08_05-40-29/a/t1.ibd.delta IS 16384 bytes
Applying /usr/LOCAL/mysql/backup/re/2011-08-08_05-40-29/a/t1.ibd.delta ...
xtrabackup: page SIZE FOR /usr/LOCAL/mysql/backup/re/2011-08-08_05-40-29/a/t2.ibd.delta IS 16384 bytes
110808  5:44:59  InnoDB: Operating system error NUMBER 2 IN a file operation.  
<<<<<<<<恢复t2表时出错>>>>>>>
InnoDB: The error means the system cannot find the path specified.
xtrabackup: target DATA file /usr/LOCAL/mysql/backup/2011-08-08_05-34-16/a/t2.ibd IS NOT found, creating a NEW one
Applying /usr/LOCAL/mysql/backup/re/2011-08-08_05-40-29/a/t2.ibd.delta ...
xtrabackup: TEMPORARY instance FOR recovery IS SET AS followings.
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = /usr/LOCAL/mysql/backup/re/2011-08-08_05-40-29
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 2097152
xtrabackup: Starting InnoDB instance FOR recovery.
xtrabackup: USING 104857600 bytes FOR buffer pool (SET BY --use-memory parameter)
InnoDB: The InnoDB memory heap IS disabled
110808  5:44:59  InnoDB: Initializing buffer pool, SIZE = 100.0M
110808  5:44:59  InnoDB: Completed initialization OF buffer pool
InnoDB: The log SEQUENCE NUMBER IN ibdata files does NOT MATCH
InnoDB: the log SEQUENCE NUMBER IN the ib_logfiles!
110808  5:44:59  InnoDB: DATABASE was NOT shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information FROM the .ibd files...
InnoDB: LAST MySQL binlog file POSITION 0 379, file name ./mysql-bin.000005
110808  5:45:00  InnoDB: Started; log SEQUENCE NUMBER 0 50551
 
[notice (again)]
  IF you USE BINARY log AND don't use any hack of group commit,
  the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 379, file name ./mysql-bin.000005
 
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
110808  5:45:00  InnoDB: Starting shutdown...
InnoDB: Warning: some resources were not cleaned up in shutdown:
InnoDB: threads 0, events 0, os_mutexes 0, os_fast_mutexes 4294967295
110808  5:45:05  InnoDB: Shutdown completed; log sequence number 0 50551
110808 05:45:05  innobackupex: completed OK!

8、把全备下面所有内容都copy到mysql的数据文件目录;修改文件及目录属主

[root@dg2 2011-08-08_05-34-16]# cp -R * /u01/mysql/var/
chown...

9、启动数据库并查看恢复情况

[root@dg2 bin]# ./mysqld_safe --user=mysql &
 
mysql> USE a;
DATABASE changed
mysql> SHOW TABLES;
+-------------+
| Tables_in_a |
+-------------+
| t1          |
+-------------+
1 ROW IN SET (0.01 sec)

t2没有被恢复!!!


Post a Comment