MySQL數據庫數據備份和恢複詳解

我要評論 2009/08/11 19:54:05 來源：綠色資源網（wǎng）編輯：佚名 [大中小] 評論:0 點擊:494次

本文討（tǎo）論 MySQL 的備份和恢複機製，以（yǐ）及如何維護數據表，包括最主要的兩種表類型：MyISAM 和 Innodb，文中（zhōng）設計的 MySQL 版（bǎn）本為（wéi） 5.0.22。

目前 MySQL 支持的免費備份工（gōng）具有：mysqldump、mysqlhotcopy，還可以用 SQL 語法進行備份：BACKUP TABLE 或者 SELECT INTO OUTFILE，又或者備份二進製日誌（binlog），還可以是直（zhí）接拷貝數據文件和相關（guān）的配置文（wén）件。MyISAM 表是保存成文件的形（xíng）式，因此相對比較容易備份，上麵提到的幾種方法都（dōu）可以使用。Innodb 所有的表都保存（cún）在同一個數據文件 ibdata1 中（也可能（néng）是多個（gè）文件，或者（zhě）是（shì）獨立的表空間文件（jiàn）），相對來說比較不好備份，免費的方案可以是拷貝（bèi）數據文件、備（bèi）份 binlog，或者（zhě）用 mysqldump。

1、mysqldump

1.1 備份

mysqldump 是采用SQL級別的備份機製，它將數據表導成 SQL 腳本文（wén）件，在不同的 MySQL 版本之間升級（jí）時相（xiàng）對比較合適，這也是（shì）最常用的備份方法。現在來講一下 mysqldump 的一些主（zhǔ）要參數：

--compatible=name 它告訴 mysqldump，導（dǎo）出（chū）的數據將和哪種數據庫或哪個舊（jiù）版本的 MySQL 服務器相兼容（róng）。值（zhí）可以為 ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options 等，要（yào）使用幾個值，用逗號將它們隔開。當然了，它並不保證能完全兼（jiān）容，而是盡量兼容。

--complete-insert，-c 導出的數（shù）據采（cǎi）用（yòng）包含字（zì）段名的完整 INSERT 方式，也就是把所有的值都寫在一行。這麽做能提高插入效率，但是可能會受到 max_allowed_packet 參數（shù）的影響而導致插入失（shī）敗。因此（cǐ），需要謹慎使用該參數，至少我不推薦。

--default-character-set=charset 指定導（dǎo）出數據時采用何種字符集，如果數據表（biǎo）不是采用默認的 latin1 字符集的話，那麽導出時（shí）必須指定該選項，否則再次導入數據後將產生亂碼（mǎ）問題。

--disable-keys 告訴 mysqldump 在 INSERT 語句的開頭和結（jié）尾增加 /*！40000 ALTER TABLE table DISABLE KEYS */; 和 /*！40000 ALTER TABLE table ENABLE KEYS */; 語句，這能大大提（tí）高插入語句的速度（dù），因為它是在插（chā）入完（wán）所（suǒ）有數據後才重建索引的。該選項隻適合 MyISAM 表。

--extended-insert = true　false 默認情況下，mysqldump 開啟 --complete-insert 模式，因此（cǐ）不想用它的（de）的話，就使用本選項，設（shè）定它（tā）的值為（wéi） false 即可。

--hex-blob 使用十六進製格式導出二進製字（zì）符串字段。如（rú）果有二進（jìn）製（zhì）數據就必須使用本選項。影響到的字段（duàn）類型有 BINARY、VARBINARY、BLOB。

--lock-all-tables，-x 在開始導出之前，提交（jiāo）請求鎖定所有（yǒu）數據庫（kù）中的所有表，以（yǐ）保證數據的一致性。這是一（yī）個全局讀鎖，並且自動關閉（bì） --single-transaction 和 --lock-tables 選項。

--lock-tables 它和 --lock-all-tables 類似，不過是（shì）鎖定當前導（dǎo）出的數據表，而不是一（yī）下子鎖定全部庫下的表。本（běn）選（xuǎn）項隻（zhī）適用於 MyISAM 表，如果（guǒ）是 Innodb 表可以用 --single-transaction 選項（xiàng）。

--no-create-info，-t 隻導出數據（jù），而不（bú）添加 CREATE TABLE 語句。

--no-data，-d 不導出（chū）任何數據，隻導出數據庫表結構。

--opt 這隻是一個（gè）快捷選項，等同於同時添加 --add-drop-tables --add-locking --create-option --disable-keys --extended-insert --lock-tables --quick --set-charset 選（xuǎn）項。本選項能讓 mysqldump 很快的導出數據，並（bìng）且導出的數據能很快導回。該選項默認開啟，但可以用 --skip-opt 禁（jìn）用。注意，如果運行 mysqldump 沒有指定 --quick 或 --opt 選項，則會將整個結果集放在內存中。如果導出大數據（jù）庫的話可能會出現問題（tí）。

--quick，-q 該選項在導出（chū）大表時（shí）很有用，它強製 mysqldump 從服務器查詢取得記錄直接輸出（chū）而不是取得所有記錄後將它們緩存到內存中。

--routines，-R 導出存儲過（guò）程以及自定義函數。

--single-transaction 該選項在導出數據之（zhī）前提交（jiāo）一個 BEGIN SQL語（yǔ）句，BEGIN 不會（huì）阻塞任何應用程序且能（néng）保證（zhèng）導出時（shí）數（shù）據庫的一致性狀態。它隻適（shì）用於事（shì）務表，例如 InnoDB 和 BDB。本選項（xiàng）和 --lock-tables 選項是互（hù）斥的，因為 LOCK TABLES 會使任（rèn）何掛起的事務隱含提交。要想導出大表的話，應結合（hé）使用 --quick 選（xuǎn）項。

--triggers 同時導出（chū）觸發器（qì）。該選項默認啟用，用 --skip-triggers 禁（jìn）用它（tā）。

其他參數詳（xiáng）情請（qǐng）參考手冊，我通常使用以下 SQL 來備份 MyISAM 表：

/usr/local/mysql/bin/mysqldump -uyejr -pyejr

--default-character-set=utf8 --opt --extended

-insert=false \--triggers -R --hex-blob -x db_name

》 db_name.sql

使用以下 SQL 來備（bèi）份 Innodb 表：

/usr/local/mysql/bin/mysqldump -uyejr -pyejr --default

-character-set=utf8 --opt --extended-insert=

false \--triggers -R --hex-blob --single-transaction db_name

》 db_name.sql

1.2 還原

用 mysqldump備（bèi）份出來的文（wén）件是一個可以直接倒入的 SQL 腳本，有兩種方法可以將數（shù）據導入。

直接用 mysql 客戶端例如：

/usr/local/mysql/bin/mysql -uyejr -pyejr db_name 《 db_name.sql

用 SOURCE 語法其實這不是標準的 SQL 語法，而是 mysql 客戶端提（tí）供的功能，例如： SOURCE /tmp/db_name.sql;

這裏需要指定文件的絕對路徑（jìng），並且必須是（shì） mysqld 運行用戶（例如 nobody）有權（quán）限讀（dú）取的（de）文件。

2、 mysqlhotcopy

2.1 備（bèi）份

mysqlhotcopy 是一（yī）個 PERL 程（chéng）序，最初由Tim Bunce編寫。它使用 LOCK TABLES、FLUSH TABLES 和 cp 或 scp 來快速備份數據庫。它是備份數據庫或單個表的最快的途徑，但它（tā）隻（zhī）能運行在數據庫文件（包括（kuò）數據表定義文（wén）件、數據文件、索引文件）所在的機（jī）器（qì）上。mysqlhotcopy 隻能用於備份 MyISAM，並且隻能運行在類Unix 和 NetWare 係統上。

mysqlhotcopy 支持一次性拷貝多個數據庫，同時還（hái）支持正則表達。以下是幾個例（lì）子：

root#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr db_name /tmp

（把數據庫目錄 db_name 拷貝到（dào） /tmp 下（xià））

root#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr db_name_1 。..

db_name_n /tmproot#/usr/local/mysql/bin/mysqlhotcopy -h

=localhost -u=yejr -p=yejr db_name./regex/ /tmp

更詳細的使用方法請查看手冊，或者（zhě）調用下（xià）麵的命令來查（chá）看 mysqlhotcopy 的幫助：

perldoc /usr/local/mysql/bin/mysqlhotcopy

注意，想要使用 mysqlhotcopy，

必須要有 SELECT、RELOAD（要執行 FLUSH TABLES）權（quán）限（xiàn），並且還必須要能夠有讀取 datadir/db_name 目（mù）錄的權限。

2.2 還原

mysqlhotcopy備份出來（lái）的是整個數據（jù）庫目錄，使（shǐ）用時可（kě）以直接拷貝到 mysqld 指定的 datadir （在這裏是 /usr/local/mysql/data/）目錄下即可，同時要注意權限的（de）問題，如下例：

root#cp -rf db_name /usr/local/mysql/data/root#chown -R nobody:nobody /usr/local/mysql/data/

（將 db_name 目錄的屬主改成（chéng） mysqld 運（yùn）行用戶）

3、 SQL 語法備份

3.1 備份

BACKUP TABLE 語法（fǎ）其實和 mysqlhotcopy 的工作（zuò）原（yuán）理差不多，都是鎖表（biǎo），然後拷貝數據文件。它（tā）能實（shí）現在線備份，但是效果不理（lǐ）想，因此不推薦使用。它隻拷貝表結構文件和數據文（wén）件（jiàn），不同時拷貝索引文件，因此恢（huī）複時比較（jiào）慢。例子：

BACK TABLE tbl_name TO ‘/tmp/db_name/’;

注意，必（bì）須要有 FILE 權限才（cái）能執行本SQL，並且目錄 /tmp/db_name/ 必須能被 mysqld 用戶可（kě）寫，導出的文件不能覆（fù）蓋已經存在（zài）的文件，以避（bì）免安全問題。

SELECT INTO OUTFILE 則是（shì）把數（shù）據導出來成為普通的文本文件，可以自定義字（zì）段間隔的方式，方便處理這些數據。

例子：

SELECT INTO OUTFILE ‘/tmp/db_name/tbl_name.txt’ FROM tbl_name;

注意，必須要有（yǒu） FILE 權限才能執行（háng）本SQL，並且文件 /tmp/db_name/tbl_name.txt 必（bì）須能被 mysqld 用戶可寫，導出的文件不能覆蓋已經存在的文件，以（yǐ）避免安全問題。

3.2 恢複

用 BACKUP TABLE 方法備份出來的文件，可以運行 RESTORE TABLE 語句來恢複數據表。例子： RESTORE TABLE FROM ‘/tmp/db_name/’;權限要求類似上麵所述。

用 SELECT INTO OUTFILE 方法備份出（chū）來的文件，可以運行 LOAD DATA INFILE 語句來恢複數據表。例（lì）子：

LOAD DATA INFILE ‘/tmp/db_name/tbl_name.txt’ INTO TABLE tbl_name;

權限（xiàn）要求類似（sì）上麵（miàn）所（suǒ）述。倒入（rù）數（shù）據之前，數據表要已經（jīng）存在才行。如（rú）果擔心數據會發生重複（fù），可以（yǐ）增加 REPLACE 關鍵字來替換已有記錄或者用 IGNORE 關鍵字來忽略他們。

補充：