史上最全的MYSQL備份方法

本人曾經用過的備份方式有: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，加入以下幾行：

　　然後啟動 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配置可參考

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