ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【MySql】innobackupex 增量备份和恢复

【MySql】innobackupex 增量备份和恢复

原创 Linux操作系统 作者:杨奇龙 时间:2011-12-11 20:06:14 0 删除 编辑
   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 !!

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22664653/viewspace-713125/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
MySQL DBA NoSQL DEVOPS

注册时间:2009-10-07

  • 博文量
    1026
  • 访问量
    7531059