為了保障數(shù)據(jù)的安全，需要定期對(duì)數(shù)據(jù)進(jìn)行備份。備份的方式有很多種，效果也不一樣。一旦數(shù)據(jù)庫中的數(shù)據(jù)出現(xiàn)了錯(cuò)誤，就需要使用備份好的數(shù)據(jù)進(jìn)行還原恢復(fù)。從而將損失降到最低。下面我們來了解一下MySQL常見的有三種備份恢復(fù)方式：
1、利用Mysqldump+二進(jìn)制日志實(shí)現(xiàn)備份
2、利用LVM快照+二進(jìn)制日志實(shí)現(xiàn)備份
3、使用Xtrabackup備份
一：實(shí)驗(yàn)環(huán)境介紹：
系統(tǒng)介紹：CentOS6.4_X64
數(shù)據(jù)庫版本：mysql-5.5.33
二：基于Mysqldump命令實(shí)現(xiàn)備份恢復(fù)
2.1、思路概念
Mysqldump是一個(gè)邏輯備份命令；意思就是將數(shù)據(jù)庫中的數(shù)據(jù)備份成一個(gè)文本文件；也可以說是將表的結(jié)構(gòu)和數(shù)據(jù)存儲(chǔ)在文本文件中。
Mysqldump命令的工作原理很簡(jiǎn)單，它先查出需要備份的表的結(jié)構(gòu)，再在文本文件中生成一個(gè)CREATE語句。然后，將表中的所有記錄轉(zhuǎn)換為一條INSTERT語句。這些CREATE語句和INSTERT語句都是還原時(shí)使用的。還原數(shù)據(jù)時(shí)就可以使用其中的CREATE語句來創(chuàng)建表。使用其中的INSERT語句來還原數(shù)據(jù)。它可以實(shí)現(xiàn)整個(gè)服務(wù)器備份，也可以實(shí)現(xiàn)單個(gè)或部分?jǐn)?shù)據(jù)庫、單個(gè)或部分表、表中的某些行、存儲(chǔ)過程、存儲(chǔ)函數(shù)、觸發(fā)器的備份；并且能自動(dòng)記錄備份時(shí)刻的二進(jìn)制日志文件及相應(yīng)的位置。對(duì)于InnoDB存儲(chǔ)引擎來講支持基于單事務(wù)模式實(shí)現(xiàn)熱備，對(duì)于MyISAM則最多支持溫備。
2.2、備份策略
Mysqldump全備+二進(jìn)制日志增備
2.3、過程實(shí)現(xiàn)
（1）Mysqldump全備
由于Mysql數(shù)據(jù)庫默認(rèn)的為MyISAM存儲(chǔ)引擎所以只有使用溫備（備份同時(shí)僅支持讀請(qǐng)求）進(jìn)行，所以我們要為所有數(shù)據(jù)庫添加讀鎖
|
1
2
|
[root@stu18 ~]#mysqldump -uroot -pmypass --lock-all-tables --master-data=2 --events --routines--all-databases > /zhao/database_`date +%F`.sql |
解析：–lock-all-tables表示為所有表施加讀鎖；–master-data=2表示在備份文件中記錄當(dāng)前二進(jìn)制日志的位置；–events表示備份數(shù)據(jù)的同時(shí)備份時(shí)間調(diào)度器代碼；–routines表示備份數(shù)據(jù)的同時(shí)備份存儲(chǔ)過程和存儲(chǔ)函數(shù)；–all-databases表示備份所有庫。
|
1
2
3
4
5
6
7
8
9
10
11
12
|
[root@stu18 zhao]# less database_2013-08-13.sql-- #表示注釋項(xiàng)-- Position to start replication or point-in-time recovery from---- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=14203; #這里表示當(dāng)前處于mysql-bin.000001這個(gè)二進(jìn)制日志中，事件為14203這是通過--master-data=2產(chǎn)生的---- Current Database: `hellodb`--CREATE DATABASE /*!32312 IF NOT EXISTS*/ `hellodb` /*!40100 DEFAULT CHARACTER SET utf8 */; |
（2）二進(jìn)制全備
方法一： 導(dǎo)出二進(jìn)制日志文件內(nèi)容
|
1
2
|
[root@stu18 data]# mysqlbinlog mysql-bin.000001 >/zhao/binlog_`date +%F`.sql |
方法二：滾動(dòng)日志復(fù)制文件
|
1
2
3
4
|
mysql> flush logs; #滾動(dòng)日志[root@stu18 data]# cp mysql-bin.000001 /zhao/mysql-bin.000001 #復(fù)制導(dǎo)出二進(jìn)制文件 |
（3）二進(jìn)制增備
首先添加數(shù)據(jù)信息
|
1
2
|
mysql> use hellodb;mysql> INSERT INTO students(Name,Age,Gender,ClassID,TeacherID) values ('Yang kang',22,'M',3,3); |
然后二進(jìn)制增備
|
1
2
|
[root@stu18 data]# mysqlbinlog --start-position=14203 --stop-position=14527 mysql-bin.000001 > /zhao/binlog_`date +%F_%H`.sql |
解析：–start-position=14203是上次全備之后的二進(jìn)制事件位置；–stop-position=14527最近一天的二進(jìn)制事件位置。
2.4、模擬數(shù)據(jù)庫損壞，實(shí)現(xiàn)恢復(fù)工作
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql> DROP DATABASE hellodb; #刪除數(shù)據(jù)庫############下面這些過程要在離線狀態(tài)下執(zhí)行############mysql> SET sql_log_bin=0; #先關(guān)閉二進(jìn)制日志mysql> flush logs; #滾動(dòng)日志[root@stu18 ~]# mysql -uroot -pmypass < /zhao/database_2013-08-13.sql #導(dǎo)入數(shù)據(jù)庫備份文件[root@stu18 ~]# mysql -uroot -pmypass < /zhao/binlog_2013-08-13_19.sql #導(dǎo)入增量備份文件[root@stu18 ~]# mysql -uroot –pmypass #登錄查看，恢復(fù)完成mysql> SET sql_log_bin=1; |
這種備份方式恢復(fù)簡(jiǎn)單，但是恢復(fù)還原之后索引會(huì)出現(xiàn)錯(cuò)誤需要重建，而且備份結(jié)果會(huì)占據(jù)很大的空間，請(qǐng)酌情使用。
PS：mysqldump常用命令小結(jié)
備份MySQL數(shù)據(jù)庫的命令
|
1
|
mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql |
備份MySQL數(shù)據(jù)庫為帶刪除表的格式
備份MySQL數(shù)據(jù)庫為帶刪除表的格式，能夠讓該備份覆蓋已有數(shù)據(jù)庫而不需要手動(dòng)刪除原有數(shù)據(jù)庫。
|
1
|
mysqldump -–add-drop-table -uusername -ppassword databasename > backupfile.sql |
直接將MySQL數(shù)據(jù)庫壓縮備份
|
1
|
mysqldump -hhostname -uusername -ppassword databasename | gzip > backupfile.sql.gz |
備份MySQL數(shù)據(jù)庫某個(gè)(些)表
|
1
|
mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 > backupfile.sql |
同時(shí)備份多個(gè)MySQL數(shù)據(jù)庫
|
1
|
mysqldump -hhostname -uusername -ppassword –databases databasename1 databasename2 databasename3 > multibackupfile.sql |
僅僅備份數(shù)據(jù)庫結(jié)構(gòu)
|
1
|
mysqldump –no-data –databases databasename1 databasename2 databasename3 > structurebackupfile.sql |
備份服務(wù)器上所有數(shù)據(jù)庫
|
1
|
mysqldump –all-databases > allbackupfile.sql |
還原MySQL數(shù)據(jù)庫的命令
|
1
|
mysql -hhostname -uusername -ppassword databasename < backupfile.sql |
還原壓縮的MySQL數(shù)據(jù)庫
|
1
|
gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename |
將數(shù)據(jù)庫轉(zhuǎn)移到新服務(wù)器
|
1
|
mysqldump -uusername -ppassword databasename | mysql –host=*.*.*.* -C databasename |
三、基于LVM快照實(shí)現(xiàn)備份恢復(fù)
3.1、思路明細(xì)
（1）LVM這種備份方式要求Mysql的數(shù)據(jù)保存在邏輯卷上
（2）需要給Mysql服務(wù)器施加讀鎖（mysql>FLUSH TABLES WITH READLOCK;），這里不可直接退出服務(wù)器
（3）另起終端為數(shù)據(jù)所在的卷創(chuàng)建快照（lvcreate），保證事務(wù)日志和數(shù)據(jù)文件必須在同一卷上（分別創(chuàng)建可能會(huì)導(dǎo)致數(shù)據(jù)文件和事務(wù)日志不一致，從而可能導(dǎo)致無法正常恢復(fù)）
3.2、備份策略
LVM快照全備+二進(jìn)制日志增備（對(duì)于即時(shí)點(diǎn)恢復(fù)還要恢復(fù)至后續(xù)的二進(jìn)制位置）
3.3、前提條件
（1）創(chuàng)建邏輯卷及掛載邏輯卷，此過程在此就不做演示了
（2）初始化mysql將其數(shù)據(jù)目錄指向/mydata/data
|
1
2
3
4
|
[root@stu18 ~]# cd /usr/local/mysql/[root@stu18 mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data |
（3）編輯查看配置文件，重啟服務(wù)
|
1
2
3
4
5
6
7
8
|
[root@stu18 mysql]# vim /etc/my.cnfdatadir = /mydata/data#查看此項(xiàng)是否定義數(shù)據(jù)目錄位置sync_binlog=1 #添加此項(xiàng)，每個(gè)事務(wù)提交時(shí)候，把事務(wù)日志從緩存區(qū)寫到日志文件中，并且刷新日志文件的數(shù)據(jù)到磁盤上；[root@stu18 mysql]# service mysqld start |
3.4、過程展示
（1）確保事務(wù)日志和數(shù)據(jù)文件必須在同一卷上
|
1
2
3
4
5
6
|
[root@stu18 ~]# ls /mydata/data/hellodb myclass mysql-bin.000003 stu18.magedu.com.erribdata1 mysql mysql-bin.000004 stu18.magedu.com.pidib_logfile0 mysql-bin.000001 mysql-bin.index studentib_logfile1 mysql-bin.000002 performance_schema test |
解析：其中ib_logfile0與ib_logfile1是日志文件
（2）施加全局鎖并滾動(dòng)日志
|
1
2
|
mysql> FLUSH TABLES WITH READ LOCK;mysql> FLUSH LOGS; |
（3）查看并保存當(dāng)前正在使用的二進(jìn)制日志及當(dāng)前執(zhí)行二進(jìn)制日志位置（非常重要）
|
1
2
3
4
5
6
7
8
|
mysql> SHOW MASTER STATUS;+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000004 | 187 | | |+------------------+----------+--------------+------------------+[root@stu18 zhao]# mysql -uroot -pmypass -e 'SHOW MASTER STATUS;' >/zhao/lvmback-2013-08-14/binlog.txt |
（4）創(chuàng)建快照卷
|
1
2
|
[root@stu18 zhao]# lvcreate -L 100M -s -p r -n mydata-lvm /dev/vg1/mydata |
（5）立即切換終端釋放鎖
|
1
|
mysql> UNLOCK TABLES; |
（6）備份數(shù)據(jù)
|
1
2
|
[root@stu18 data]# cp -a * /zhao/lvmback-2013-08-14/ |
（7）二進(jìn)制實(shí)現(xiàn)增量備份
|
1
2
3
4
5
6
7
8
9
10
11
|
mysql> use hellodb; #指定默認(rèn)數(shù)據(jù)庫Database changedmysql> CREATE TABLE testtb (id int,name CHAR(10)); #創(chuàng)建表Query OK, 0 rows affected (0.35 sec)mysql> INSERT INTO testtb VALUES (1,'tom'); #添加數(shù)據(jù)Query OK, 1 row affected (0.09 sec)[root@stu18 data]# mysqlbinlog --start-position=187 mysql-bin.000004 > /zhao/lvmlogbin_2013-08-14/binlog.sql #日志實(shí)現(xiàn)增量備份 |
（8）模擬數(shù)據(jù)庫崩潰
|
1
2
3
4
5
6
|
[root@stu18 ~]# service mysqld stop[root@stu18 ~]# cd /mydata/data/[root@stu18 data]# rm -rf * |
（9）恢復(fù)數(shù)據(jù)
|
1
2
3
4
5
6
7
8
9
10
|
[root@stu18 ~]# cp /zhao/lvmback-2013-08-14/* /mydata/data/ -a #完全備份恢復(fù)[root@stu18 ~]# cd /mydata/data/ #查看恢復(fù)數(shù)據(jù)內(nèi)容[root@stu18 data]# chown -R mysql.mysql * #更改屬主屬組[root@stu18 data]# service mysqld start #啟動(dòng)服務(wù)[root@stu18 data]# mysql -uroot –pmypass #登錄測(cè)試 |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
mysql> SHOW DATABASES; #查看數(shù)據(jù)完整性，無測(cè)試表testtd使用二進(jìn)制恢復(fù)mysql> SET sql_log_bin=0 #關(guān)閉二進(jìn)制日志mysql> source /zhao/lvmlogbin_2013-08-14/binlog.sql; #二進(jìn)制恢復(fù)mysql> SHOW TABLES; #查看恢復(fù)結(jié)果+-------------------+| Tables_in_hellodb |+-------------------+| classes || coc || courses || scores || students || teachers || testtb || toc |+-------------------+mysql> SET sql_log_bin=1; #開啟二進(jìn)制日志 |
此工具是接近于熱備的方式實(shí)現(xiàn)的，并且用此方法來備份恢復(fù)數(shù)據(jù)速度是非常快的。
四:基于xtrabackup來實(shí)現(xiàn)備份恢復(fù)
4.1、優(yōu)勢(shì)特性
完全以熱備的形式進(jìn)行，能夠?qū)崿F(xiàn)快速可靠地完全備份和部分備份，支持增量備份，支持時(shí)間點(diǎn)還原，備份過程中不會(huì)打擾到事務(wù)操作，能夠?qū)崿F(xiàn)網(wǎng)絡(luò)傳輸和壓縮功能從而有效的節(jié)約磁盤空間，備份完成后可自動(dòng)驗(yàn)證數(shù)據(jù)是否可用，恢復(fù)速度較快等等。更多優(yōu)勢(shì)特性請(qǐng)參考http://www.percona.com/software/percona-xtrabackup
注意：以上這些優(yōu)勢(shì)特性只能在InnoDB引擎上完美實(shí)現(xiàn)，而在MyISAM存儲(chǔ)引擎上依然最多只能使用溫備的形式進(jìn)行并且還不支持增量備份。
另外Xtrabackup更多的高級(jí)功能還依賴于Mysql數(shù)據(jù)庫對(duì)于InnoDB實(shí)現(xiàn)了單獨(dú)的表空間，否則也就沒有辦法實(shí)現(xiàn)單表導(dǎo)入導(dǎo)出查看方式如下：
|
1
2
3
4
5
6
7
8
9
|
mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_file%';+--------------------------+----------+| Variable_name | Value |+--------------------------+----------+| innodb_file_format | Antelope || innodb_file_format_check | ON || innodb_file_format_max | Antelope || innodb_file_per_table | ON |+--------------------------+----------+ |
其中的innodb_file_per_table為ON則表示實(shí)現(xiàn)了單表單空間。若為OFF則需要使用mysqldump全備然后更改配置文件刪除原來的數(shù)據(jù)文件并重新初始化服務(wù)器最后將數(shù)據(jù)重新導(dǎo)入。所以建議以后在安裝Mysql服務(wù)器時(shí)將其選項(xiàng)默認(rèn)設(shè)置成1即可（innodb_file_per_table = 1）。單表單空間的數(shù)據(jù)顯示形式為：
|
1
2
3
4
5
6
|
[root@stu18 hellodb]# lsclasses.frm coc.MYD courses.MYI scores.MYI teachers.frm testtb.ibdclasses.MYD coc.MYI db.opt students.frm teachers.MYD toc.frmclasses.MYI courses.frm scores.frm students.MYD teachers.MYI toc.MYDcoc.frm courses.MYD scores.MYD students.MYI testtb.frm toc.MYI |
4.2、安裝Xtrabackup
下載percona-xtrabackup最新的版本為2.1.4（percona-xtrabackup-2.1.4-656.rhel6.x86_64.rpm）
安裝：
|
1
2
|
[root@stu18 ~]# rpm -ivh percona-xtrabackup-2.1.4-656.rhel6.x86_64.rpm |
若有錯(cuò)誤無法安裝請(qǐng)安裝perl-DBD-mysql依賴包
|
1
2
|
[root@stu18 ~]# yum -y install perl-DBD-mysql |
注意：不同的環(huán)境依賴的關(guān)系包可能有多個(gè)，請(qǐng)依照提示進(jìn)行配置
4.3、完全備份
使用innobakupex備份時(shí)，其會(huì)調(diào)用xtrabackup備份所有的InnoDB表，復(fù)制所有關(guān)于表結(jié)構(gòu)定義的相關(guān)文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相關(guān)文件，同時(shí)還會(huì)備份觸發(fā)器和數(shù)據(jù)庫配置信息相關(guān)的文件。這些文件會(huì)被保存至一個(gè)以時(shí)間命令的目錄中。完全備份命令如下：
|
1
|
# innobackupex --user=DBUSER--password=DBUSERPASS /path/to/BACKUP-DIR/ |
實(shí)現(xiàn)過程及說明：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
<p>[root@stu18 ~]# mkdir /innobackup #創(chuàng)建備份文件目錄[root@stu18 ~]# innobackupex --user=root --password=mypass /innobackup/ #完全備份################如果執(zhí)行正確其后輸出的幾行信息通常如下###############xtrabackup: Transaction log of lsn (1604655) to (1604655) was copied. #二進(jìn)制日志的位置（lsn）130814 07:04:55 innobackupex: All tables unlockedinnobackupex: Backup created in directory '/innobackup/2013-08-14_07-04-49'#備份文件保存的位置innobackupex: MySQL binlog position: filename 'mysql-bin.000003', position 538898130814 07:04:55 innobackupex: Connection to database server closed130814 07:04:55 innobackupex: completed</p> |
OK! 備份完成
切換至備份文件目錄查看備份的數(shù)據(jù)信息及創(chuàng)建生成的文件：
|
1
2
3
4
5
6
7
|
<p>[root@stu18 ~]# cd /innobackup/2013-08-14_07-04-49/[root@stu18 2013-08-14_07-04-49]# lsbackup-my.cnf myclass student xtrabackup_binlog_infohellodb mysql test xtrabackup_checkpointsibdata1 performance_schema xtrabackup_binary xtrabackup_logfile</p> |
針對(duì)文件解析：
(1)xtrabackup_checkpoints —— 備份類型（如完全或增量）、備份狀態(tài)（如是否已經(jīng)為prepared狀態(tài)）和LSN(日志序列號(hào))范圍信息；
每個(gè)InnoDB頁(通常為16k大小)都會(huì)包含一個(gè)日志序列號(hào)，即LSN。LSN是整個(gè)數(shù)據(jù)庫系統(tǒng)的系統(tǒng)版本號(hào)，每個(gè)頁面相關(guān)的LSN能夠表明此頁面最近是如何發(fā)生改變的。
(2)xtrabackup_binlog_info —— mysql服務(wù)器當(dāng)前正在使用的二進(jìn)制日志文件及至備份這一刻為止二進(jìn)制日志事件的位置。
(3)xtrabackup_binary —— 備份中用到的xtrabackup的可執(zhí)行文件；
(4)backup-my.cnf —— 備份時(shí)用到的配置選項(xiàng)信息，也就是配置文件中關(guān)于mysqld的相關(guān)文件配置；
(5) xtrabackup_logfile —— 非文本文件是xtrabackup本身的日志文件；
4.4、準(zhǔn)備一個(gè)完全備份
一般情況下，在備份完成后，數(shù)據(jù)尚且不能用于恢復(fù)操作，因?yàn)閭浞莸臄?shù)據(jù)中可能會(huì)包含尚未提交的事務(wù)或已經(jīng)提交但尚未同步至數(shù)據(jù)文件中的事務(wù)。因此，此時(shí)數(shù)據(jù)文件仍處于不一致狀態(tài)。“準(zhǔn)備”的主要作用正是通過回滾未提交的事務(wù)及同步已經(jīng)提交的事務(wù)至數(shù)據(jù)文件從而使得數(shù)據(jù)文件處于一致性狀態(tài)。
innobakupex命令的–apply-log選項(xiàng)可用于實(shí)現(xiàn)上述功能。如下面的命令：
|
1
2
3
4
5
6
7
|
[root@stu18 ~]# innobackupex -apply-log /innobackup/2013-08-14_07-04-49/#############如果執(zhí)行正確，其最后輸出的幾行信息通常如下################xtrabackup: starting shutdown with innodb_fast_shutdown = 1130814 7:39:33 InnoDB: Starting shutdown...130814 7:39:37 InnoDB: Shutdown completed; log sequence number 1606156130814 07:39:37 innobackupex: completed OK! |
4.5、模擬數(shù)據(jù)庫崩潰實(shí)現(xiàn)完全恢復(fù)
（1）模擬崩潰
|
1
2
3
4
5
6
|
[root@stu18 ~]# service mysqld stop[root@stu18 ~]# cd /mydata/data/[root@stu18 data]# rm -rf * |
（2）從完全備份中恢復(fù)數(shù)據(jù)（謹(jǐn)記：在恢復(fù)數(shù)據(jù)之前千萬不可初始化數(shù)據(jù)庫和啟動(dòng)服務(wù)）
innobackupex命令的–copy-back選項(xiàng)用于執(zhí)行恢復(fù)操作，其通過復(fù)制所有數(shù)據(jù)相關(guān)的文件至mysql服務(wù)器DATADIR目錄中來執(zhí)行恢復(fù)過程。innobackupex通過backup-my.cnf來獲取DATADIR目錄的相關(guān)信息。
|
1
2
3
4
5
6
7
8
9
10
|
[root@stu18 ~]# innobackupex --copy-back /innobackup/2013-08-14_07-04-49/#############如果執(zhí)行正確，其最后輸出的幾行信息通常如下################innobackupex: Starting to copy InnoDB log filesinnobackupex: in '/innobackup/2013-08-14_07-04-49'innobackupex: back to original InnoDB log directory '/mydata/data'innobackupex: Copying '/innobackup/2013-08-14_07-04-49/ib_logfile0' to '/mydata/data'innobackupex: Copying '/innobackup/2013-08-14_07-04-49/ib_logfile1' to '/mydata/data'innobackupex: Finished copying back files.130814 07:58:22 innobackupex: completed OK! |
（3）當(dāng)數(shù)據(jù)恢復(fù)至數(shù)據(jù)目錄以后，還需要確保所有數(shù)據(jù)文件的屬主和屬組均為正確的用戶，如mysql，否則，在啟動(dòng)mysqld之前還需要事先修改數(shù)據(jù)文件的屬主和屬組。
|
1
|
# chown -R mysql:mysql /mydata/data/ |
（4）啟動(dòng)服務(wù)器登陸查看恢復(fù)完成。
|
1
2
|
[root@stu18 data]# service mysqld start |
注意：每一次恢復(fù)完成之后一定要重新做一次完全備份工作！！
4.6、使用innobackupex進(jìn)行增量備份
說明：每個(gè)InnoDB的頁面都會(huì)包含一個(gè)LSN信息，每當(dāng)相關(guān)的數(shù)據(jù)發(fā)生改變，相關(guān)的頁面的LSN就會(huì)自動(dòng)增長。這正是InnoDB表可以進(jìn)行增量備份的基礎(chǔ)，即innobackupex通過備份上次完全備份之后發(fā)生改變的頁面來實(shí)現(xiàn)。
第一次改動(dòng)數(shù)據(jù)實(shí)現(xiàn)增量備份
實(shí)現(xiàn)增量備份可以使用下面的命令進(jìn)行：
|
1
2
|
[root@stu18 data]# innobackupex --user=root --password=mypass --incremental /innobackup --incremental-basedir=/innobackup/2013-08-14_08-14-12/ |
其中，/innobackup指的是完全備份所在的目錄，此命令執(zhí)行結(jié)束后，innobackupex命令會(huì)在/backup目錄中創(chuàng)建一個(gè)新的以時(shí)間命名的目錄以存放所有的增量備份數(shù)據(jù)。–incremental-basedir是指向上一次完全備份所在的目錄。
第二次改動(dòng)數(shù)據(jù)進(jìn)行增量備份：
|
1
2
|
[root@stu18 ~]# innobackupex --user=root --password=mypass --incremental /innobackup --incremental-basedir=/innobackup/2013-08-14_08-29-05/ |
第二次增量備份的執(zhí)行命令和第一次大致相同，只有其–incremental-basedir應(yīng)該指向上一次的增量備份所在的目錄。
第三次改動(dòng)數(shù)據(jù)還未進(jìn)行增量備份
|
1
|
mysql> delete from coc where id=14; |
4.7、使用innobackupex基于完全+增量+二進(jìn)制日志恢復(fù)數(shù)據(jù)
（1）由于筆者這里將二進(jìn)制日志和數(shù)據(jù)文件寫在了同一個(gè)文件目錄下所以在模擬數(shù)據(jù)庫崩潰前必須先復(fù)制出二進(jìn)制日志文件，所以建議看客們將數(shù)據(jù)目錄和二進(jìn)制目錄分開存放，不要和筆者一樣犯如此二的錯(cuò)誤。方法如下：
前提是在剛剛建立服務(wù)器尚未啟動(dòng)服務(wù)器之前做如下操作；
|
1
2
3
4
5
6
7
8
|
mkdir /mybinlog#建立一目錄用于存放二進(jìn)制日志chown mysql:mysql /mybinlog#更改權(quán)限vim /etc/my.cnf #修改配置文件log-bin=/mybinlog/mysql-bin#二進(jìn)制日志目錄及文件名前綴，添加之 |
好了言歸正傳復(fù)制二進(jìn)制日志文件：
|
1
2
|
[root@stu18 data]# cp mysql-bin.000001/innobackup/ |
（2）模擬服務(wù)器崩潰
|
1
2
3
4
5
6
|
[root@stu18 ~]# service mysqld stop[root@stu18 ~]# cd /mydata/data/[root@stu18 data]# rm -rf * |
（3）準(zhǔn)備備份
首先注意“準(zhǔn)備”增量備份與整理完全備份有著一些不同，尤其要注意的是：
1)需要在每個(gè)備份(包括完全和各個(gè)增量備份)上，將已經(jīng)提交的事務(wù)進(jìn)行“重放”。“重放”之后，所有的備份數(shù)據(jù)將合并到完全備份上。
2)基于所有的備份將未提交的事務(wù)進(jìn)行“回滾”。
完全備份“準(zhǔn)備”
|
1
2
|
[root@stu18 ~]# innobackupex --apply-log --redo-only/innobackup/2013-08-14_08-14-12/ |
第一次增量備份“準(zhǔn)備”也就是說將第一次增量備份合并到了完全備份中
|
1
2
|
[root@stu18 ~]# innobackupex --apply-log--redo-only /innobackup/2013-08-14_08-14-12/--incremental-dir=/innobackup/2013-08-14_08-29-05/ |
第二次增量備份“準(zhǔn)備”也就是說將第二次增量備份也合并到了完全備份中
|
1
2
|
[root@stu18 ~]# innobackupex --apply-log--redo-only /innobackup/2013-08-14_08-14-12/ --incremental-dir=/innobackup/2013-08-14_09-08-39/ |
其中 –redo-only是只將已提交的事務(wù)同步到數(shù)據(jù)文件中，未提交的事務(wù)日志不在進(jìn)行回滾了。
（4）恢復(fù)數(shù)據(jù)（基于innobackupex基于完全+增量）
|
1
2
|
[root@stu18 ~]# innobackupex --copy-back/innobackup/2013-08-14_08-14-12/ |
（5）更改屬組屬主
|
1
2
3
4
|
[root@stu18 ~]# cd /mydata/data/[root@stu18 data]# chown -R mysql:mysql * |
（6）啟動(dòng)查看
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
[root@stu18 ~]# mysql -uroot -pmypasmysql> select * from coc;+----+---------+----------+| ID | ClassID | CourseID |+----+---------+----------+| 1| 1 | 2 || 2| 1 | 5 || 3| 2 | 2 || 4| 2 | 6 || 5| 3 | 1 || 6| 3 | 7 || 7| 4 | 5 || 8| 4 | 2 || 9| 5 | 1 || 10 | 5 | 9 || 11 | 6 | 3 || 12 | 6 | 4 || 13 | 7 | 4 || 14 | 7 | 3 |+----+---------+----------+14 rows in set (0.00 sec) |
結(jié)果顯示數(shù)據(jù)正確完整，但是第三次的改動(dòng)信息未生效。
（7）基于二進(jìn)制日志實(shí)現(xiàn)數(shù)據(jù)恢復(fù)
查看最后一次增量備份二進(jìn)制日志所在的位置：
|
1
2
3
4
5
|
[root@stu18 data]# cd /innobackup/2013-08-14_09-08-39/[root@stu18 2013-08-14_09-08-39]# cat xtrabackup_binlog_infomysql-bin.000001 780 |
查看二進(jìn)制日志文件將未備份數(shù)據(jù)的二進(jìn)制日志導(dǎo)出
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
[root@stu18 innobackup]# mysqlbinlog mysql-bin.000001# at 780#130814 9:20:19 server id 1 end_log_pos 851 Query thread_id=7 exec_time=0 error_code=0SET TIMESTAMP=1376443219/*!*/;BEGIN/*!*/;# at 851#130814 9:20:19 server id 1 end_log_pos 944 Query thread_id=7 exec_time=0 error_code=0SET TIMESTAMP=1376443219/*!*/;delete from coc where id=14/*!*/;# at 944#130814 9:20:19 server id 1 end_log_pos 1016 Query thread_id=7 exec_time=0 error_code=0SET TIMESTAMP=1376443219/*!*/;COMMIT/*!*/;DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;[root@stu18 innobackup]# mysqlbinlog --start-position=780 mysql-bin.000001 > ./all.sql #導(dǎo)出數(shù)據(jù) |
恢復(fù)數(shù)據(jù)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
[root@stu18 ~]# mysql -uroot –pmypassmysql> SET SQL_LOG_BIN=0; #關(guān)閉二進(jìn)制日志mysql> source /innobackup/all.sql #導(dǎo)入數(shù)據(jù)mysql> SET SQL_LOG_BIN=1; #開啟二進(jìn)制日志mysql> select * from coc; #查看數(shù)據(jù)，恢復(fù)完成+----+---------+----------+| ID | ClassID | CourseID |+----+---------+----------+| 1 | 1 | 2 || 2 | 1 | 5 || 3 | 2 | 2 || 4 | 2 | 6 || 5 | 3 | 1 || 6 | 3 | 7 || 7 | 4 | 5 || 8 | 4 | 2 || 9 | 5 | 1 || 10 | 5 | 9 || 11 | 6 | 3 || 12 | 6 | 4 || 13 | 7 | 4 |+----+---------+----------+13 rows in set (0.00 sec) |
這種備份恢復(fù)方式完全以熱備的形式實(shí)現(xiàn)完全備份和增量備份和二進(jìn)制日志還原數(shù)據(jù)，并且恢復(fù)速度也很快，是最佳的備份恢復(fù)方式！！
總結(jié)：以上三種備份恢復(fù)都是可以基于二進(jìn)制日志文件進(jìn)行的，因而體現(xiàn)出了二進(jìn)制日志的重要性，從而映射出了日志的重要性；所以學(xué)習(xí)查看使用日志文件是學(xué)習(xí)Mysql的重中之重！
