【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 ！！
相關文章
- innobackupex 部分表備份和恢復
- rman 增量備份恢復
- 利用innobackupex備份集恢復指定庫
- 【MySQL】MySQL備份和恢復MySql
- oracle 增量備份恢復驗證Oracle
- innobackupex備份mysql大資料(全量+增量）操作記錄MySql大資料
- 用增量備份來快速恢復dg
- Mysql備份恢復MySql
- 【Xtrabackup】Xtrabackup全備、增量備份及恢復示例
- MySQL備份和恢復方法彙總MySql
- MySQL日誌管理，備份和恢復MySql
- MySQL備份與恢復——基於Xtrabackup物理備份恢復MySql
- MySQL 備份與恢復MySql
- innobackupex 備份MySQL資料庫MySql資料庫
- 基於percona xtrabackup 2.4.14的增量備份恢復還原mysql 5.6MySql
- 備份和恢復
- Mysql備份與恢復(1)---物理備份MySql
- MySQL備份與恢復——基於MyDumper/MyLoader 邏輯備份恢復MySql
- MySQL 非常規恢復與物理備份恢復MySql
- Mysql備份與恢復(2)---邏輯備份MySql
- redis 備份和恢復Redis
- KunlunDB備份和恢復
- dg丟失歸檔，使用rman增量備份恢復
- MySQL備份與恢復——基於OUTFILE /LOAD DATA 邏輯備份恢復MySql
- 使用Mysqldump備份和恢復MySQL資料庫MySql資料庫
- 《入門MySQL—備份與恢復》MySql
- MySQL備份與恢復——實操MySql
- 入門MySQL——備份與恢復MySql
- docker 中 MySQL 備份及恢復DockerMySql
- MySQL備份與恢復操作解析MySql
- Mysql資料備份與恢復MySql
- MySQL 定時增量備份MySql
- SqlServer備份和恢復(二)SQLServer
- SqlServer 備份和恢復(一)SQLServer
- RAC備份恢復之Voting備份與恢復
- MySQL運維實戰之備份和恢復（8.1）xtrabackup全量備份MySql運維
- MySQL 日誌管理、備份與恢復MySql
- Mysql資料庫備份及恢復MySql資料庫
- MySQL8.4備份恢復快速命令MySql