[MySQL] – 史上最全的MySQL備份方法

來源：http://www.dabaoku.com/jiaocheng/biancheng/mysql/200911072934.shtml

 

本人曾經
用過的備份方式有:mysqldump、mysqlhotcopy、BACKUP TABLE 、SELECT INTO
OUTFILE，又或者備份二進位日誌（binlog），還可以是直接拷貝資料檔案和相關的設定檔。MyISAM
表是儲存成檔案的形式，因此相對比較容易備份，上面提到的幾種方法都可以使用。Innodb 所有的表都儲存在同一個資料檔案 ibdata1
中（也可能是多個檔案，或者是獨立的資料表空間檔案），相對來說比較不好備份，免費的方案可以是拷貝資料檔案、備份 binlog，或者用
mysqldump。

 

 

1.mysqldump備份

 

mysqldump 是採用SQL層級的備份機制，它將資料表導成 SQL 指令檔，在不同的 MySQL 版本之間升級時相對比較合適，這也是最常用的備份方法。

樣本:mysqldump -uroot -p database table > /home/jobs/back.sql

mysqldump也可做增量備份,mysqldump相關參數網上較多,就不在此一一贅述了

 

2.mysqlhotcopy備份

 

mysqlhotcopy 是一個 PERL 程式。它使用 LOCK TABLES、FLUSH
TABLES 和 cp 或 scp
來快速備份資料庫。它是備份資料庫或單個表的最快的途徑，但它只能運行在資料庫檔案（包括資料表定義檔案、資料檔案、索引檔案）所在的機器上。
mysqlhotcopy 只能用於備份 MyISAM，並且只能運行在 類Unix 和 NetWare 系統上。

mysqlhotcopy 支援一次性拷貝多個資料庫，同時還支援正則表達。

樣本： root#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=root
-p=123456 database /tmp (把資料庫目錄 database 拷貝到 /tmp
下)root#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=root -p=123456
db_name_1 ... db_name_n /tmproot#/usr/local/mysql/bin/mysqlhotcopy
-h=localhost -u=root -p=123456 db_name./regex/
/tmp更詳細的使用方法請查看手冊，或者調用下面的命令來查看 mysqlhotcopy 的協助：

perldoc /usr/local/mysql/bin/mysqlhotcopy注意，想要使用 mysqlhotcopy，必須要有
SELECT、RELOAD(要執行 FLUSH TABLES) 許可權，並且還必須要能夠有讀取 datadir/db_name 目錄的許可權。

 

還原mysqlhotcopy 備份出來的是整個資料庫目錄，使用時可以直接拷貝到 mysqld
指定的 datadir (在這裡是 /usr/local/mysql/data/)目錄下即可，同時要注意許可權的問題，如下例： root#cp
-rf db_name /usr/local/mysql/data/root#chown -R nobody:nobody
/usr/local/mysql/data/ (將 db_name 目錄的屬主改成 mysqld 運行使用者)

 

 

3.SQL 文法備份

3.1 備份BACKUP TABLE 文法其實和 mysqlhotcopy
的工作原理差不多，都是鎖表，然後拷貝資料檔案。它能實現線上備份，但是效果不理想，因此不推薦使用。它只拷貝表結構檔案和資料檔案，不同時拷貝索引文
件，因此恢複時比較慢。例子： BACK TABLE tbl_name TO '/tmp/db_name/';注意，必須要有 FILE
許可權才能執行本SQL，並且目錄 /tmp/db_name/ 必須能被 mysqld 使用者可寫，匯出的檔案不能覆蓋已經存在的檔案，以避免安全問題。

恢複用 BACKUP TABLE 方法備份出來的檔案，可以運行 RESTORE TABLE 語句來恢複資料表。例子： RESTORE TABLE FROM '/tmp/db_name/';許可權要求類似上面所述。

 

3.2 SELECT INTO OUTFILE 則是把資料匯出來成為普通的文字檔，可以自訂欄位間隔的方式，方便處理這些資料。例子：
SELECT INTO OUTFILE '/tmp/db_name/tbl_name.txt' FROM tbl_name;注意，必須要有
FILE 許可權才能執行本SQL，並且檔案 /tmp/db_name/tbl_name.txt 必須能被 mysqld
使用者可寫，匯出的檔案不能覆蓋已經存在的檔案，以避免安全問題。

用 SELECT INTO OUTFILE 方法備份出來的檔案，可以運行 LOAD DATA INFILE 語句來恢複資料表。例子： LOAD
DATA INFILE '/tmp/db_name/tbl_name.txt' INTO TABLE
tbl_name;許可權要求類似上面所述。倒入資料之前，資料表要已經存在才行。如果擔心資料會發生重複，可以增加 REPLACE
關鍵字來替換已有記錄或者用 IGNORE 關鍵字來忽略他們。

 

4.啟用二進位日誌(binlog)

 

採用 binlog 的方法相對來說更靈活，省心省力，而且還可以支援增量備份。

啟用 binlog 時必須要重啟 mysqld。首先，關閉 mysqld，開啟 my.cnf，加入以下幾行：

server-id = 1

log-bin = binlog

log-bin-index = binlog.index

然後啟動 mysqld 就可以了。運行過程中會產生 binlog.000001 以及 binlog.index，前面的檔案是 mysqld
記錄所有對資料的更新操作，後面的檔案則是所有 binlog 的索引，都不能輕易刪除。關於 binlog 的資訊請查看手冊。

需要備份時，可以先執行一下 SQL 陳述式，讓 mysqld 終止對當前 binlog
的寫入，就可以把檔案直接備份，這樣的話就能達到增量備份的目的了： FLUSH LOGS;如果是備份複製系統中的從伺服器，還應該備份
master.info 和 relay-log.info 檔案。

備份出來的 binlog 檔案可以用 MySQL 提供的工具 mysqlbinlog 來查看，如：
/usr/local/mysql/bin/mysqlbinlog /tmp/binlog.000001該工具允許你顯示指定的資料庫下的所有
SQL 陳述式，並且還可以限定時間範圍，相當的方便，詳細的請查看手冊。

恢複時，可以採用類似以下語句來做到： /usr/local/mysql/bin/mysqlbinlog /tmp/binlog.000001
| mysql -uyejr -pyejr db_name把 mysqlbinlog 輸出的 SQL 陳述式直接作為輸入來執行它。

如果你有閒置機器，不妨採用這種方式來備份。由於作為 slave 的機器效能要求相對不是那麼高，因此成本低，用低成本就能實現增量備份而且還能分擔一部分資料查詢壓力，何樂而不為呢？

 

5.拷貝檔案

 

直接備份資料檔案相較前幾種方法，備份資料檔案最為直接、快速、方便，缺點是基本上不能實現增量備份。

為了保證資料的一致性，需要在靠背檔案前，執行以下 SQL 陳述式： FLUSH TABLES WITH READ
LOCK;也就是把記憶體中的資料都重新整理到磁碟中，同時鎖定資料表，以保證拷貝過程中不會有新的資料寫入。這種方法備份出來的資料恢複也很簡單，直接拷貝回
原來的資料庫目錄下即可。

注意，對於 Innodb 類型表來說，還需要備份其記錄檔，即 ib_logfile* 檔案。因為當 Innodb 表損壞時，就可以依靠這些記錄檔來恢複。

 

6.利用rsync備份

rsync作為同步工具也可以用來做備份,但要設定管理員端和用戶端

樣本rsync -vzrtopg --progress --delete root@192.168.1.3::root /tmp/

相關rsync配置可參考http://fanqiang.chinaunix.net/a6/b7/20010908/1305001258.html

缺點是rsync是根據檔案修改時間做的增量備份,所以備份資料庫都是全備,並且配置比較麻煩.