innobackupex 是使用pl封装了xtrabackup之后的工具,在使用的时候会调用xtrabackup。
1 创建环境
-bash-3.2$ mysql
mysql> use test;
Database changed
mysql> select count(1) from t1;
+----------+
| count(1) |
+----------+
| 2000000 |
+----------+
1 row in set (4.39 sec)
2 全量备份
[root@rac3 backup]# innobackupex --defaults-file=/etc/my.cnf --user=root /opt/mysql/backup/base
111211 18:50:49 innobackupex: Starting mysql with options: --defaults-file='/etc/my.cnf' --user='root' --unbuffered --
111211 18:50:49 innobackupex: Connected to database with mysql child process (pid=25327)
111211 18:50:55 innobackupex: Connection to database server closed
....省略....
111211 18:52:16 innobackupex: All tables locked and flushed to disk
111211 18:52:16 innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI,
innobackupex: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files in
innobackupex: subdirectories of '/opt/mysql/data'
innobackupex: Backing up files '/opt/mysql/data/performance_schema/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (18 files)
innobackupex: Backing up files '/opt/mysql/data/mysql/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (72 files)
innobackupex: Backing up file '/opt/mysql/data/test/t1.frm'
innobackupex: Backing up file '/opt/mysql/data/test/sbtest.frm'
innobackupex: Backing up file '/opt/mysql/data/yang/db.opt'
innobackupex: Backing up file '/opt/mysql/data/sbtest/db.opt'
innobackupex: Backing up file '/opt/mysql/data/sbtest/sbtest.frm'
111211 18:52:16 innobackupex: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSV, .CSM and .opt files
....省略....
innobackupex: MySQL binlog position: filename 'mysql-bin.000026', position 107
111211 18:52:19 innobackupex: completed OK!
3 再次插入数据
mysql> insert into t1 select * from t1;
Query OK, 2000000 rows affected (44.87 sec)
Records: 2000000 Duplicates: 0 Warnings: 0
mysql> exit
Bye
4 增量备份
[root@rac3 backup]# innobackupex --defaults-file=/etc/my.cnf --user=root --incremental --incremental-basedir=/opt/mysql/backup/base/2011-12-11_18-50-55/ /opt/mysql/backup/delta
....省略....
innobackupex: Created backup directory /opt/mysql/backup/delta/2011-12-11_18-56-39
111211 18:56:39 innobackupex: Starting mysql with options: --defaults-file='/etc/my.cnf' --user='root' --unbuffered --
111211 18:56:39 innobackupex: Connected to database with mysql child process (pid=25649)
111211 18:56:43 innobackupex: Connection to database server closed
111211 18:56:43 innobackupex: Starting ibbackup with command: xtrabackup_55 --defaults-file="/etc/my.cnf" --backup --suspend-at-end --target-dir=/opt/mysql/backup/delta/2011-12-11_18-56-39 --incremental-basedir='/opt/mysql/backup/base/2011-12-11_18-50-55/'
....省略....
[01] Copying ./ibdata1
to /opt/mysql/backup/delta/2011-12-11_18-56-39/ibdata1.delta
>> log scanned up to (8291192731)
>> log scanned up to (8291192731)
>> log scanned up to (8291192731)
>> log scanned up to (8291192731)
>> log scanned up to (8291192731)
[01] ...done
111211 18:57:17 innobackupex: Continuing after ibbackup has suspended
111211 18:57:17 innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI,
innobackupex: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files in
innobackupex: subdirectories of '/opt/mysql/data'
innobackupex: Backing up files '/opt/mysql/data/performance_schema/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (18 files)
innobackupex: Backing up files '/opt/mysql/data/mysql/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (72 files)
innobackupex: Backing up file '/opt/mysql/data/test/t1.frm'
innobackupex: Backing up file '/opt/mysql/data/test/sbtest.frm'
innobackupex: Backing up file '/opt/mysql/data/yang/db.opt'
innobackupex: Backing up file '/opt/mysql/data/sbtest/db.opt'
innobackupex: Backing up file '/opt/mysql/data/sbtest/sbtest.frm'
111211 18:57:17 innobackupex: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSV, .CSM and .opt files
innobackupex: Resuming ibbackup
....省略....
111211 18:57:18 innobackupex: completed OK!
5 删除数据库
[root@rac3 mysql]# pwd
/opt/mysql
[root@rac3 mysql]# ls
1.txt backup data data.tar.gz--压缩文件是防止恢复失败
[root@rac3 mysql]# rm -fr data
6 恢复数据库
6.1 恢复完整的备份集：
[root@rac3 ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --apply-log --redo-only /opt/mysql/backup/base/2011-12-11_18-50-55
111211 19:20:42 innobackupex: Starting ibbackup with command: xtrabackup_55 --defaults-file="/etc/my.cnf" --prepare --target-dir=/opt/mysql/backup/base/2011-12-11_18-50-55 --apply-log-only
xtrabackup_55 version 1.6.3 for Percona Server 5.5.9 Linux (x86_64) (revision id: 292)
xtrabackup: cd to /opt/mysql/backup/base/2011-12-11_18-50-55
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(7774347286)
xtrabackup: Temporary instance for recovery is set as followings.
....省略....
InnoDB: Last MySQL binlog file position 0 502, file name ./mysql-bin.000025
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
111211 19:20:43 InnoDB: Starting shutdown...
111211 19:20:43 InnoDB: Shutdown completed; log sequence number 7774347286
111211 19:20:43 innobackupex: completed OK!
[root@rac3 ~]#
6.2 恢复增量备份集：
[root@rac3 ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --apply-log --redo-only /opt/mysql/backup/base/2011-12-11_18-50-55 --incremental-dir=/opt/mysql/backup/delta/2011-12-11_18-56-39
IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".--结果出现
completed OK表示完全成功
111211 19:24:16 innobackupex: Starting ibbackup with command: xtrabackup_55 --defaults-file="/etc/my.cnf" --prepare --target-dir=/opt/mysql/backup/base/2011-12-11_18-50-55 --apply-log-only --incremental-dir=/opt/mysql/backup/delta/2011-12-11_18-56-39
xtrabackup_55 version 1.6.3 for Percona Server 5.5.9 Linux (x86_64) (revision id: 292)
incremental backup from 7774347286 is enabled.
xtrabackup: cd to /opt/mysql/backup/base/2011-12-11_18-50-55
xtrabackup: This target seems to be already prepared.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(8291192731)
xtrabackup: page size for /opt/mysql/backup/delta/2011-12-11_18-56-39/ibdata1.delta is 16384 bytes
Applying /opt/mysql/backup/delta/2011-12-11_18-56-39/ibdata1.delta ...
xtrabackup: Temporary instance for recovery is set as followings.
....省略....
111211 19:24:29 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 296, file name ./mysql-bin.000026
111211 19:24:33 InnoDB: Starting shutdown...
111211 19:24:34 InnoDB: Shutdown completed; log sequence number 8291192731
111211 19:24:34 innobackupex: completed OK!
6.3 执行拷贝恢复的文件到原来的数据位置
[root@rac3 ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --copy-back /opt/mysql/backup/base/2011-12-11_18-50-55
IMPORTANT: Please check that the copy-back run completes successfully.
At the end of a successful copy-back run innobackupex
prints "completed OK!".
innobackupex: Starting to copy MyISAM tables, indexes,
innobackupex: .MRG, .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV, .opt, and .frm files
innobackupex: in '/opt/mysql/backup/base/2011-12-11_18-50-55'
innobackupex: back to original data directory '/opt/mysql/data'
innobackupex: Copying directory '/opt/mysql/backup/base/2011-12-11_18-50-55/performance_schema'
innobackupex: Copying directory '/opt/mysql/backup/base/2011-12-11_18-50-55/mysql'
innobackupex: Copying file '/opt/mysql/backup/base/2011-12-11_18-50-55/xtrabackup_binlog_pos_innodb'
innobackupex: Copying directory '/opt/mysql/backup/base/2011-12-11_18-50-55/test'
innobackupex: Copying file '/opt/mysql/backup/base/2011-12-11_18-50-55/xtrabackup_binlog_info'
innobackupex: Copying directory '/opt/mysql/backup/base/2011-12-11_18-50-55/yang'
innobackupex: Copying file '/opt/mysql/backup/base/2011-12-11_18-50-55/xtrabackup_checkpoints'
innobackupex: Copying file '/opt/mysql/backup/base/2011-12-11_18-50-55/xtrabackup_binary'
innobackupex: Copying directory '/opt/mysql/backup/base/2011-12-11_18-50-55/sbtest'
innobackupex: Starting to copy InnoDB tables and indexes
innobackupex: in '/opt/mysql/backup/base/2011-12-11_18-50-55'
innobackupex: back to original InnoDB data directory '/opt/mysql/data'
innobackupex: Copying file '/opt/mysql/backup/base/2011-12-11_18-50-55/ibdata1'
innobackupex: Starting to copy InnoDB log files
innobackupex: in '/opt/mysql/backup/base/2011-12-11_18-50-55'
innobackupex: back to original InnoDB log directory '/opt/mysql/data'
innobackupex: Finished copying back files.
111211 19:29:08 innobackupex: completed OK!
-bash-3.2$ mysql
6.4
执行拷贝文件之后
修改权限，
[root@rac3 mysql]# chown -R mysql:mysql dat
7 测试
mysql> use test;
Database changed
mysql> select count(1) from t1;
+----------+
| count(1) |
+----------+
| 4000000 |
+----------+
1 row in set (8.40 sec)
mysql>
ok ！！
