【MySql】innobackupex 增量备份和恢复

   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 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 mysql use test; Database changed mysql select count(1) from t1;         +----------+ | count(1) | +----------+ |  4000000 | +----------+ 1 row in set (8.40 sec) mysql   ok ！！