細聊MySQL的備份與恢復

備份對于數據庫來說是相當重要的工作。如果數據庫在使用過程中出現了問題，比如系統崩潰、硬件故障或錯誤的刪除了數據。這時，如果我們進行了數據備份，就能比較方便的使數據庫恢復工作，并使我們的數據損失到最

備份對于數據庫來說是相當重要的工作。如果數據庫在使用過程中出現了問題，比如系統崩潰、硬件故障或錯誤的刪除了數據。這時，如果我們進行了數據備份，就能比較方便的使數據庫恢復工作，并使我們的數據損失到最小。下面，我從備份類型、備份方法及一些常用的例子來和大家詳細探討下數據庫的備份與恢復。

一、備份類別

1、物理備份與邏輯備份

物理備份用人話來形容就是復制數據庫的數據文件。如果我們需要備份名為test的數據庫，則我們可以將數據目錄下的test目錄復制到備份設備中。如果我們需要備份test庫下名為user的表，則可以根據不同的存儲引擎選取不同的數據文件來復制。如user是myisam，則可以復制user.frm，user.MYD，user.MYI文件。如user是innodb，則可以復制user.frm，user.ibd。其中.frm是包含數據結構的文件。.MYD是myisam引擎中包含具體數據的文件。.MYI是myisam引擎中包含索引內容的文件。.ibd是innodb引擎中包含具體數據的文件。

邏輯備份是將數據庫的結構對象及數據對象（所有的事件、觸發器、存儲過程等）轉化為SQL語句后再保存到備份文件中。所以邏輯備份與具體的操作平臺無關。

物理備份的特征：

1、它是直接復制二進制數據文件的，所以我們在恢復數據時只能原樣恢復，不能自定義修改數據文件再恢復。

2、物理備份比邏輯備份速度更快，因為沒有轉化SQL這一過程。

3、物理備份不支持細粒度的備份，如果你只是想備份某個表中的某一范圍的行，可能會難以辦到。

4、物理備份可能會丟失最新寫入到數據庫的數據。因為在物理備份時，可能會有新增的數據還駐留在內存中，沒有寫入硬盤。

5、物理備份對平臺有一定的要求，如果兩個操作系統的文件系統不一樣，有可能會不能恢復數據。所以要求盡可能在相同的操作系統及文件系統上實現數據的備份與恢復。

6、最好在服務器停止運行時執行物理備份，否則可能需要正確的加鎖策略來確保數據一致。

邏輯備份的特征：

1、通過將需備份的數據轉換成SQL來完成備份，所以在恢復數據時，我們可以通過修改SQL的方式來自定義恢復的內容，有更大的靈活性。

2、因為特征1的原因，所以在速度方面會比物理備份慢，因此不太適合大量數據的備份。

3、備份力度方面比物理備份更細，可以到行級。

4、不依賴具體的操作系統平臺。

5、備份時無需停止服務器。

2、在線備份與離線備份

在線備份是通過遠程客戶端連到服務器進行備份，如在客戶端使用導出工具導出SQL備份文件。在備份時應該在SQL上加合適的鎖防止數據不一致的情況。比如寫數據時應加寫鎖以防止導出數據集時發生數據不一致。

離線備份是在服務器停運的狀態下進行備份，因為此時不會有數據操作，所以它在實現上更簡單。

3、完全備份與增量備份

完全備份就是備份到目前為止的所有數據。增量備份只備份自上一次完全備份或增量備份到至今，期間有所變化的數據。實現完全備份的方法有很多，如使用邏輯備份或者物理備份都可以實現完全備份。完全備份在恢復時是最方便的，但在備份時通常需要耗費很長的時間，所以如果數據庫的數據量非常大，每天都執行完全備份是不現實的。應采用完全備份與增量備份相結合的策略。如在每個星期天執行一次完全備份，禮拜一到禮拜五實行增量備份。增量備份的特性與完全備份剛好相反，在備份時會非常的快捷方便，但在還原時會比較繁瑣。而且要實現增量備份，我們必須在服務器運行時開啟二進制日志。這樣我們在恢復時才能根據二進制日志來進行實時恢復。

二、備份方法

物理備份方法通常是用專門的MySQL備份工具或操作系統的相關指令進行的，這里強調一點，就是在進行物理復制的時候，在執行復制之前應給需要備份的表加讀鎖，并且需要將駐留在內存的索引信息寫入I/O，具體命令為FLUSH TABLES WITH READ LOCK。下面我主要介紹下MySQL普通版本下的備份方法。

使用mysqldump實現數據庫的邏輯備份。

mysqldump命令在mysql安裝目錄的bin目錄下。主要作用是導出SQL文件。具體語法格式如下：

mysqldump [options] db_name [tbl_name ...] 
mysqldump [options] --databases db_name ...
mysqldump [options] --all-databases

常用的可選參數如下：

以下是一個簡單的備份例子，如下所示：

/usr/local/mysql/bin/mysql -uroot -p123456 \
-h127.0.0.1 -e"flush tables with read lock” \
#將駐留在內存中的數據寫入表中并給所有表添加讀鎖

/usr/local/mysql/bin/mysqldump -uroot -p123456 \
-h127.0.0.1 --databases test --add-drop-database \
--add-drop-table --extended-insert --create-options > \
/usr/local/mysql/backup/backup.sql
#備份test庫到backup.sql文件。加了—databases后，會在備份文件里
生成創建數據庫CREATE DATABASE test;的聲明。否則，如果直接使用
mysqldump test > backup.sql命令則不會生成create database語句。
具體參數的運用可以參考上表，如果數據較大，我們可以使用—opt選項進行備份。

/usr/local/mysql/bin/mysql -uroot -p123456 \
-h127.0.0.1 -e"unlock tables”
#解除讀鎖

三、數據恢復方法

1、mysqldump恢復法

使用mysqldump備份的數據，通常在一個.sql文件中。要恢復數據，只用利用mysql客戶端執行備份文件即可。如：

/usr/local/mysql/bin/mysql -uroot -p123456 \
-h127.0.0.1 < /usr/local/mysql/backup/backup.sql

即可

2、二進制日志恢復法

使用此方法的前提是你的服務器是以—log-bin參數運行的，這樣服務器才會生成二進制日志。使用二進制日志恢復法需使用mysqlbinlog命令，此命令也在mysql安裝目錄的bin目錄下。恢復命令如下：

/usr/local/mysql/bin/mysqlbinlog xxx-bin.000001 \
| /usr/local/mysql/bin/mysql -uroot -p123456 -h127.0.0.1

諸如此類xxx-bin.000001的格式均為二進制日志文件。xxx-bin.000001里是以二進制存儲的對數據庫的所有操作，所以此命令執行后會將歷史上對數據庫的操作進行重做，以此達到恢復數據的目的。值得注意的是，如果你有多個二進制日志文件，如有xxx-bin.000001和xxx-bin.000002這兩個文件，如果按以下方式進行恢復將是不安全的：

/usr/local/mysql/bin/mysqlbinlog xxx-bin.000001 \
| /usr/local/mysql/bin/mysql -uroot -p123456 -h127.0.0.1
/usr/local/mysql/bin/mysqlbinlog xxx-bin.000002 \
| /usr/local/mysql/bin/mysql -uroot -p123456 -h127.0.0.1

這樣利用兩次客戶端連線去恢復數據是不安全的，如果在xxx-bin.000001內有一個生成臨時表的聲明，而在xxx-bin.000002內需要使用這個臨時表，則這樣操作將出現問題，執行mysqlbinlog xxx-bin.000002時將會找不到這個臨時表。所以正確的做法應該是：

/usr/local/mysql/bin/mysqlbinlog xxx-bin.000001 xxx-bin.000002 \
| /usr/local/mysql/bin/mysql -uroot -p123456 -h127.0.0.1

mysqlbinlog恢復數據的方法很靈活，可以通過時間點與位置點進行恢復如：

/usr/local/mysql/bin/mysqlbinlog —start-datetime=‘2014-01-20 8:00:00’\
—stop-datetime=‘2014-01-22 19:00:00' xxx-bin.000001\
| /usr/local/mysql/bin/mysql -uroot -p123456 -h127.0.0.1
#表示重做xxx-bin.000001文件里從2014-01-20 8:00:00到
2014-01-22 19:00:00這個時間范圍內的操作。

/usr/local/mysql/bin/mysqlbinlog --start-position=368315\
xxx-bin.000001 | /usr/local/mysql/bin/mysql\
-uroot -p123456 -h127.0.0.1
#表示重做xxx-bin.000001文件內從位置368315開始直到文件末尾的操作。

關于備份與恢復，一般采用完全備份和增量備份結合的方法。這樣可以在保證備份效率的基礎上達到實時恢復要求。比如讓服務器以—log-bin參數運行，然后每個禮拜天進行一次mysqldump。這樣，比如2014年12月7號23點59分完成的備份，2014年12月9日7點需要進行恢復。則首先運行mysql < backup.2014-12-7.sql，恢復到2014年12月7日23點59分前的數據。然后利用mysqlbinlog hostname-bin.000001 —start-datetime=‘2014-12-8 00:00:00’ | mysql 命令恢復之后的數據。

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com