1 搭建测试环境
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| sbtest |
+----------------+
1 row in set (0.01 sec)
2 全量备份
[root@rac3 mysql]# innobackupex --defaults-file=/etc/my.cnf --user=root /opt/mysql/backup/base
3 创建新的表
mysql> create table t1 as select * from sbtest;
mysql> create table t2 as select * from sbtest limit 1000;
mysql> create table t3 as select * from sbtest limit 1000;
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| sbtest |
| t1 |
| t2 |
| t3 |
+----------------+
4 rows in set (0.00 sec)
4 增量备份：
[root@rac3 mysql]# innobackupex --defaults-file=/etc/my.cnf --user=root --incremental --incremental-basedir=/opt/mysql/backup/base /opt/mysql/backup/delta
5 关闭数据库，删除数据
[root@rac3 mysql]# service mysql stop
Shutting down MySQL. [确定]
[root@rac3 mysql]#rm -fr data
6 恢复数据
全量恢复：
[root@rac3 mysql]# innobackupex --defaults-file=/etc/my.cnf --user=root --apply-log --redo-only /opt/mysql/backup/base/2011-12-11_20-47-06
增量恢复：
[root@rac3 mysql]# innobackupex --defaults-file=/etc/my.cnf --user=root --apply-log --redo-only /opt/mysql/backup/base/2011-12-11_21-12-46 --incremental-dir=/opt/mysql/backup/delta/2011-12-11_21-25-50
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!".
111211 21:32:15 innobackupex: Starting ibbackup with command: xtrabackup_55 --defaults-file="/etc/my.cnf" --prepare --target-dir=/opt/mysql/backup/base/2011-12-11_21-12-46 --apply-log-only --incremental-dir=/opt/mysql/backup/delta/2011-12-11_21-25-50
xtrabackup_55 version 1.6.3 for Percona Server 5.5.9 Linux (x86_64) (revision id: 292)
incremental backup from 8809430318 is enabled.
xtrabackup: cd to /opt/mysql/backup/base/2011-12-11_21-12-46
xtrabackup: This target seems to be already prepared.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(8810280564)
xtrabackup: page size for /opt/mysql/backup/delta/2011-12-11_21-25-50/ibdata1.delta is 16384 bytes
Applying /opt/mysql/backup/delta/2011-12-11_21-25-50/ibdata1.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 = /opt/mysql/backup/delta/2011-12-11_21-25-50
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 2097152
111211 21:32:15 InnoDB: Using Linux native AIO
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
111211 21:32:15 InnoDB: The InnoDB memory heap is disabled
111211 21:32:15 InnoDB: Mutexes and rw_locks use GCC atomic builtins
111211 21:32:15 InnoDB: Compressed tables use zlib 1.2.3
111211 21:32:15 InnoDB: Using Linux native AIO
111211 21:32:15 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead
111211 21:32:15 InnoDB: Initializing buffer pool, size = 100.0M
111211 21:32:15 InnoDB: Completed initialization of buffer pool
111211 21:32:15 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
111211 21:32:15 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 321824, file name ./mysql-bin.000001
[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 321824, file name ./mysql-bin.000001
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
111211 21:32:16 InnoDB: Starting shutdown...
111211 21:32:16 InnoDB: Shutdown completed; log sequence number 8810280564
111211 21:32:16 innobackupex: completed OK!
拷贝文件
[root@rac3 test]# innobackupex --defaults-file=/etc/my.cnf --user=root --copy-back /opt/mysql/backup/base/2011-12-11_21-12-46
重启mysql
[root@rac3 mysql]# service mysql start
Starting MySQL... [确定]
7 进行确认！
mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| sbtest |
| t1 |
+----------------+
2 rows in set (0.00 sec)
通过以上信息可以看到innobackupex 不能备份全量备份之后到增量备份之前的新建数据对象。
