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