【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
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 ！！
相關文章
- 【MySql】innobackupex 增量備份和恢復MySql
- mysql innobackupex增量備份恢復MySql
- mysql之 Innobackupex（全備+增量）備份恢復MySql
- 使用innobackupex線上增量備份和再增量備份及恢復mysql資料庫MySql資料庫
- INNOBACKUPEX的全備和增量備份恢復學習筆記筆記
- MySQL innobackupex全量備份恢復MySql
- 【MySql】innobackupex 增量備份的bugMySql
- Percona XtraBackup 2.4 innobackupex全量、增量備份恢復流程
- MySQL增量備份的指令碼（innobackupex）MySql指令碼
- innobackupex備份恢復實戰
- mysql 利用binlog增量備份、恢復MySql
- MySQL增量備份與恢復例項MySql
- MySQL 5.6 xtrabackup 全量和增量的備份和恢復MySql
- 【Mysql】xbackup全量與增量備份恢復MySql
- 【MySQL】MySQL備份和恢復MySql
- Mysql備份和恢復MySql
- 利用innobackupex備份集恢復指定庫
- oracle 增量備份恢復驗證Oracle
- MySQL 備份和恢復 一MySql
- Xtrabackup之innobackupex備份恢復詳解薦
- 【備份恢復】noarchive模式下使用增量備份恢復資料庫Hive模式資料庫
- Mysql備份系列（3）--innobackupex備份mysql大資料(全量+增量）操作記錄MySql大資料
- xtrabackup和innobackuppex的安裝和備份(含增量備份)已經恢復
- innobackupex備份mysql大資料(全量+增量）操作記錄MySql大資料
- Mysql備份恢復MySql
- mysql 備份恢復MySql
- mysql innobackupex 物理備份MySql
- 【Xtrabackup】Xtrabackup全備、增量備份及恢復示例
- 透過innobackupex實現對MySQL的增量備份與還原MySql
- MySQL備份和恢復方法彙總MySql
- MySQL日誌管理，備份和恢復MySql
- MySQL備份和恢復工具圖譜MySql
- 【Mysql】xtrabackup 備份和恢復測試MySql
- MySQL備份與恢復——基於Xtrabackup物理備份恢復MySql
- MySQL備份與恢復MySql
- mysql innobackupex備份指令碼MySql指令碼
- Mysql增量備份MySql
- Mysql備份與恢復(1)---物理備份MySql