MySQL管理與最佳化(20)：備份與恢複

標籤：style   http   color   io   os   使用   ar   for   檔案   

備份與恢複：

• 備份使得資料庫的中的資料更加高效和安全。

備份/恢複策略：

進行備份或恢複時需要考慮的一些因素：

• 確定要備份的表的儲存引擎是事務性還是非事務性，兩種不同儲存引擎備份方式在處理資料一致性方面是不太一樣的。
• 確實使用全量備份還是增量備份。全備份的優點是備份保持最新備份，恢複的時候可以花費更少的時間；缺點是如果資料量過大，將花費很多的時間，並對系統造成較長時間的壓力。增量備份則恰恰相反，只需備份每天的增量日誌，備份時間少，對負載壓力下；缺點是恢複的時候需要全備份加上次備份到故障前的所有日誌，恢復會長些。
• 可以考慮採取複製的方法來做異地備份，但複製不能代替備份，它對資料庫的誤操作也是無能為力。
• 要定期做備份，備份的周期要充分考慮系統可以承受的恢復。備份要在系統負載較小的時候進行。
• 確保MySQL開啟log-bin選項，有了BINLOG，MySQL才可以在必要的時候做完整恢複，或基於時間點的恢複，或基於位置的恢複。
• 要經常做備份恢複測試，確保備份是有效，並且是可以恢複的。

邏輯備份和恢複：

• 邏輯備份可以針對不同的儲存引擎，而使用相同的方法來備份；而物理備份對於不同的儲存引擎會有不同的方法。

備份：

• MySQL中的邏輯備份將資料庫中的資料備份為一個文字檔，備份的檔案可以查看和編輯。
• 我們可以是使用mysqldump工具實現備份，如：

-- 備份所有資料庫mysqldump -uroot -p --all-database > all.sql-- 備份資料庫testmysqldump -uroot -p test > test.sql-- 備份資料庫test下的表empmysqldump -uroot -p test emp > test_emp.sql-- 備份資料庫test下的表emp, deptmysqldump -uroot -p test emp dept > test_emp_dept.sql-- mysqldump --help可查看更多選項

• 為了保證資料備份的一致性，對於MyISAM表備份時需要加上-l參數，對於InnoDB可採用--single-transaction選項。

完全恢複：

• 同樣，我們可以使用mysqldump實現簡單的恢複：

-- 恢複某個資料庫mysql -uroot -p db_name < bakfile-- 上面的恢複可能不完整，還需要將備份後執行的日誌進行重做mysqlbinlog binlog-file | mysql -uroot -p db_name

基於時間點恢複：

• MySQL恢複中分為完全恢複和非完全恢複，非完全恢複分為基於時間點的恢複和基於位置的恢複。
• 基於時間點的恢複：

-- 若上午10點發生了誤操作，可以用下面的語句進行恢複mysqlbinlog --stop-date="2014-10-06 9:59:59" bin_log_file | mysql -uroot -p****-- 跳過10點的誤操作，再恢複mysqlbinlog --start-date="2014-10-06 10:00:01" bin_log_file | mysql -uroot -p****

基於位置恢複：

-- 儲存某時間段內的日誌mysqlbinlog --start-date="2014-10-06 12:10:20" --stop-date="2014-10-06 12:15:00" bin_log_file > temp_file-- 越過某些位置的日誌，進行恢複，如跳過1000~2000位置的日誌mysqlbinlog --stop-position="1000" bin_log_file | mysql -uroot -p****mysqlbinlog --start-position="2000" bin_log_file | mysql -uroot -p****

物理備份和恢複：

• 物理備份又分為冷備份和熱備份兩種，和邏輯備份相比，優點是備份和恢複的速度更快。

冷備份：

• 停掉MySQL服務，在作業系統層級恢複MySQL的資料檔案；重啟MySQL服務，使用mysqlbinlog工具恢複備份以來所有的BINLOG。

熱備份：

• 熱備份需要針對不同儲存引擎，主要是MyISAM和InnoDB。
• MyISAM儲存引擎:

-- 1. 使用mysqlhotcopy工具mysqlhotcopy -u root -p **** db_name /path/to/new_directory-- 2. 手動鎖表複製flush tables for read;-- 複製資料檔案到備份目錄

• InnoDB儲存引擎:

可以參考收費工具ibbackup，http://dev.mysql.com/doc/mysql-enterprise-backup/3.7/en/ihb-meb-compatibility.html

表的匯入匯出： 匯出：

• 有時我們需要對資料庫表進行匯出，以：

        1. 作為Excel顯示；

        2. 為了節省備份空間；

        3. 為了快速載入資料，LOAD DATA的載入速度比普通的SQL載入快20倍以上。

• 可以有兩種辦法來實現：

-- 使用SELECT ... INTO OUTFILE ...SELECT * FROM table_name INTO OUTFILE ‘file_name‘ [option];

其中option選項：

NOTE: SELECT ... INTO OUTFILE ...產生的輸出檔案如果已存在，將會建立失敗，不會覆蓋原檔案。

第2種方法是用mysqldump匯出：

mysqldump -u username -T target_dir db_name table_name [option]

其中option選項:

匯入：

• 使用LOAD DATA INFILE:

LOAD DATA [LOCAL] INFILE ‘file_name‘ INTO TABLE table_name [option]

其中option選項如下：

• 使用mysqlimport:

mysqlimport -u root -p*** [--LOCAL] db_name file_name [option]

其中option:

NOTE: 如果匯入和匯出是跨平台操作的(Windows和Linux)，那麼要注意設定參數line-terminated-by，Windows上設定為line-terminated-by=‘\r\n‘，Linux上設定line-terminated-by=‘\n‘。

不吝指正。

MySQL管理與最佳化(20)：備份與恢複