InnoDB 有個商業的InnoDB Hotbackup，可以對InnoDB引擎的表實現在線熱備。而 percona出品的Xtrabackup，是InnoDB Hotbackup的一個開源替代品，可以在線對InnoDB/XtraDB引擎的表進行物理備份。mysqldump支持在線備份，不過是邏輯備份，效率比較差。當數據量比較小的時候，mysqldump還可以勝任，當數據量大的時候，恢復時間卻讓人無法忍受，於是開源工具xtrabackup就應運而生了，xtrabackup屬於物理備份，效率很不錯。
xtrabackup提供了兩種命令行工具：
xtrabackup：用於備份InnoDB引擎的數據（不會備份myisam比如mysql權限相關表等，也不會自動copy frm文件）；innobackupex：一個perl腳本，在執行過程中會調用xtrabackup命令，用該命令即可以備份InnoDB，也可以備份MyISAM/copy frm文件，只不過在備份myisam表時候會添加一個讀鎖。
實驗環境：CentOS release 6.5 (Final)，mysql Ver 14.14 Distrib 5.6.14
xtrabackup安裝
#wgethttps://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.2.13/binary/redhat/6/x86_64/percona-xtrabackup-22-2.2.13-1.el6.x86_64.rpm #yum-yinstalllibaiolibaio-develperl-Time-HiRescurlcurl-develzlib-developenssl-develperlcpioexpat-develgettext-develperl-ExtUtils-MakeMakerperl-DBD-MySQL.*packagebzrbisonncurses-develzlib-devel #rpm-ivhpercona-xtrabackup-22-2.2.13-1.el6.x86_64.rpm
部分參數說明
--user指定執行備份的用戶。 --password指定執行備份用戶的密碼。 --defaults-file指定mysql的選項文件路徑。 --no-timestamp不要顯示時間戳。 --incremental告訴xtrabackup這次是創建增量備份。 --incremental-basedir指定一個全量備份的路徑作為增量備份的基礎。 --redo-only如果進行准備工作完成後，還有其他的增量備份集待處理，就需要指定這個參數。 --apply-log從指定的選項文件中讀取配置信息並應用日志等，這就意味對備份集做恢復的准備工作。 --copy-back將指定備份集恢復到指定的路徑下。
全備
#將全備的數據備份到/data/backup/base #innobackupex--user=root--password=redhat--defaults-file=/usr/local/webserver/mysql5.6/my.cnf--no-timestamp/data/backup/base ...................... innobackupex:Backupcreatedindirectory'/data/backup/base' innobackupex:MySQLbinlogposition:filename'mysql-bin.000001',position733 16020115:26:07innobackupex:Connectiontodatabaseserverclosed 16020115:26:07innobackupex:completedOK!
先對數據庫進行一些修改。
mysql>createdatabasesharelinux; QueryOK,1rowaffected(0.00sec) mysql>usesharelinux; Databasechanged mysql>createtablet1(idint,namevarchar(10)); QueryOK,0rowsaffected(0.12sec) mysql>insertintot1values(1,'zhangsan'),(2,'lisi'),(3,'wangwu'); QueryOK,3rowsaffected(0.08sec) Records:3Duplicates:0Warnings:0 mysql>select*fromt1; +------+----------+ |id|name| +------+----------+ |1|zhangsan| |2|lisi| |3|wangwu| +------+----------+ 3rowsinset(0.00sec)
第一次增量備份
#第一次增量備份目錄/data/backup/incremental_one #innobackupex--user=root--password=redhat--defaults-file=/usr/local/webserver/mysql5.6/my.cnf--no-timestamp--incremental/data/backup/incremental_one--incremental-basedir=/data/backup/base/ ...................................................... xtrabackup:Creatingsuspendfile'/data/backup/incremental_one/xtrabackup_log_copied'withpid'19979' xtrabackup:Transactionlogoflsn(22333659)to(22333659)wascopied. 16020115:39:26innobackupex:Alltablesunlocked innobackupex:Backupcreatedindirectory'/data/backup/incremental_one' innobackupex:MySQLbinlogposition:filename'mysql-bin.000001',position1238 16020115:39:26innobackupex:Connectiontodatabaseserverclosed 16020115:39:26innobackupex:completedOK!
再對數據庫進行修改
mysql>createdatabasedb01; QueryOK,1rowaffected(0.00sec) mysql>usedb01; Databasechanged mysql>createtablet2(idint,namevarchar(10)); QueryOK,0rowsaffected(0.04sec) mysql>insertintot2values(1,'zhangsan'),(2,'lisi'),(3,'wangwu'); QueryOK,3rowsaffected(0.02sec) Records:3Duplicates:0Warnings:0 mysql>select*fromt2; +------+----------+ |id|name| +------+----------+ |1|zhangsan| |2|lisi| |3|wangwu| +------+----------+ 3rowsinset(0.00sec)
第二次增量備份
#第二次增量備份目錄/data/backup/incremental_two #innobackupex--user=root--password=redhat--defaults-file=/usr/local/webserver/mysql5.6/my.cnf--no-timestamp--incremental/data/backup/incremental_two--incremental-basedir=/data/backup/incremental_one/ ........................................ innobackupex:Backupcreatedindirectory'/data/backup/incremental_two' innobackupex:MySQLbinlogposition:filename'mysql-bin.000001',position1689 16020115:59:10innobackupex:Connectiontodatabaseserverclosed 16020115:59:10innobackupex:completedOK!
模擬故障，刪除數據庫的數據文件
#ls/usr/local/webserver/mysql5.6/data/ auto.cnfib_logfile0mysql-bin.000001node1.pidtest db01ib_logfile1mysql-bin.indexperformance_schemazabbix ibdata1mysqlnode1.errsharelinux #rm-rf/usr/local/webserver/mysql5.6/data/*
恢復准備
#innobackupex--user=root--password=redhat--defaults-file=/usr/local/webserver/mysql5.6/my.cnf--apply-log--redo-only/data/backup/base/ ........................................ xtrabackup:startingshutdownwithinnodb_fast_shutdown=1 InnoDB:Startingshutdown... InnoDB:Shutdowncompleted;logsequencenumber22327338 16020116:09:44innobackupex:completedOK!
#innobackupex--user=root--password=redhat--defaults-file=/usr/local/webserver/mysql5.6/my.cnf--apply-log--redo-only/data/backup/base/--incremental-dir=/data/backup/incremental_one/ ........................................... innobackupex:Copying'/data/backup/incremental_one/performance_schema/events_stages_history.frm'to'/data/backup/base/performance_schema/events_stages_history.frm' innobackupex:Copying'/data/backup/incremental_one/performance_schema/setup_instruments.frm'to'/data/backup/base/performance_schema/setup_instruments.frm' 16020116:13:15innobackupex:completedOK!
#innobackupex--user=root--password=redhat--defaults-file=/usr/local/webserver/mysql5.6/my.cnf--apply-log/data/backup/base/--incremental-dir=/data/backup/incremental_two/ innobackupex:Copying'/data/backup/incremental_two/performance_schema/setup_instruments.frm'to'/data/backup/base/performance_schema/setup_instruments.frm' innobackupex:Copying'/data/backup/incremental_two/db01/db.opt'to'/data/backup/base/db01/db.opt' innobackupex:Copying'/data/backup/incremental_two/db01/t2.frm'to'/data/backup/base/db01/t2.frm' 16020116:18:27innobackupex:completedOK!
數據恢復
#innobackupex--user=root--password=redhat--defaults-file=/usr/local/webserver/mysql5.6/my.cnf--copy-back/data/backup/base/ ........................................... innobackupex:StartingtocopyInnoDBsystemtablespace innobackupex:in'/data/backup/base' innobackupex:backtooriginalInnoDBdatadirectory'/usr/local/webserver/mysql5.6/data' innobackupex:Copying'/data/backup/base/ibdata1'to'/usr/local/webserver/mysql5.6/data/ibdata1' innobackupex:StartingtocopyInnoDBundotablespaces innobackupex:in'/data/backup/base' innobackupex:backto'/usr/local/webserver/mysql5.6/data' innobackupex:StartingtocopyInnoDBlogfiles innobackupex:in'/data/backup/base' innobackupex:backtooriginalInnoDBlogdirectory'/usr/local/webserver/mysql5.6/data' innobackupex:Finishedcopyingbackfiles. 16020116:23:08innobackupex:completedOK!
查看目錄
#ll/usr/local/webserver/mysql5.6/data/#這個目錄下的數據已經恢復回來 total79916 drwxr-x---.2rootroot4096Feb116:18db01 -rw-r-----.1rootroot79691776Feb116:18ibdata1 drwx------.2rootroot4096Feb115:26mysql drwxr-xr-x.2rootroot4096Feb115:26performance_schema drwxr-x---.2rootroot4096Feb116:13sharelinux drwxr-xr-x.2rootroot4096Feb115:26test -rw-r--r--.1rootroot22Feb116:18xtrabackup_binlog_info -rw-r-----.1rootroot91Feb116:18xtrabackup_checkpoints -rw-r--r--.1rootroot722Feb116:18xtrabackup_info -rw-r-----.1rootroot2097152Feb116:09xtrabackup_logfile drwx------.2rootroot12288Feb115:26zabbix #chownmysql:mysql/usr/local/webserver/mysql5.6/data/-R#將目錄更改為mysql用戶 #pkillmysql#殺死原來的mysql進程，然後重啟數據庫 #servicemysqldstart StartingMySQL..[OK]
數據已經恢復回來了
mysql>showdatabases; +--------------------+ |Database| +--------------------+ |information_schema| |db01| |mysql| |performance_schema| |sharelinux| |test| |zabbix| +--------------------+ 7rowsinset(0.14sec) mysql>select*fromsharelinux.t1;#第一次增量備份的數據 +------+----------+ |id|name| +------+----------+ |1|zhangsan| |2|lisi| |3|wangwu| +------+----------+ 3rowsinset(0.03sec) mysql>select*fromdb01.t2;#第二次增量備份的數據 +------+----------+ |id|name| +------+----------+ |1|zhangsan| |2|lisi| |3|wangwu| +------+----------+ 3rowsinset(0.08sec)
