mysql中利用mysqldump命令備份還原資料庫

一、常用操作:

備份整個資料庫

格式：
mysqldump -h主機名稱  -P連接埠 -u使用者名稱 -p密碼 (–database) 資料庫名 > 檔案名稱.sql
mysqldump -h{hostname} -P{port} -u{username} -p{password} {databasename} > {backupfile.sql}
例如：
 
代碼如下：

代碼如下	複製代碼
mysqldump -hlocalhost -P3306 -uzhuchao -p123456 db_test > backfile1.sql

備份MySQL資料庫為帶刪除表的格式
備份MySQL資料庫為帶刪除表的格式，能夠讓該備份覆蓋已有資料庫而不需要手動刪除原有資料庫。
格式：mysqldump -–add-drop-table -u{username} -p{password} {databasename} > {backfile.sql}
例如：
 
代碼如下：

代碼如下	複製代碼
mysqldump -–add-drop-table –uzhuchao -p123456 db_test > backfile2.sql

直接將MySQL資料庫壓縮備份
格式：mysqldump -h{hostname} -u{username} -p{password} {databasename} | gzip > {backfile.sql.gz}
例如：
 
代碼如下：

代碼如下	複製代碼
mysqldump –hlocalhost –uzhuchao –p123456 db_test1 | gzip > backfile3.sql.gz

備份MySQL資料庫某個(些)表
格式：mysqldump -h主機名稱  -P連接埠 -u使用者名稱 -p密碼 (–tables | –quick) 資料庫名 表名1 (表名2 …) > 檔案名稱.sql (括弧中的可預設)。
mysqldump -h{hostname} (-P{port}) -u{user} -p{password} (–tables | –quick) {databasename} {table1} {table2} > {backfile.sql}
例如：
 
代碼如下：

代碼如下	複製代碼
mysqldump -hlocalhost -uzhuchao -p123456 db_test tbl_test > backfile4-1.sql mysqldump -hlocalhost -P3306 -uzhuchao -p123456 db_test tbl_test > backfile4-2.sql mysqldump -hlocalhost -P3306 -uzhuchao -p123456 --quick db_test tbl_test > backfile4-3.sql mysqldump -hlocalhost -P3306 -uzhuchao -p123456 --tables db_test tbl_test1 tbl_test2 > backfile4-4.sql

同時備份多個MySQL資料庫
格式：mysqldump -h{hostname} (-P{port}) -u{username} -p{password} –databases {databasename1} {databasename2} {databasename3} > multibackfile.sql
例如：
 
代碼如下：

代碼如下	複製代碼
mysqldump -hlocalhost -uzhuchao -p123456 –databases db_test1 db_test2 db_test3 > multibackfile.sql

僅僅備份資料庫結構
格式：mysqldump –no-data –databases {databasename1} {databasename2} > {structurebackfile.sql}
例如：
 
代碼如下：

代碼如下	複製代碼
mysqldump –no-data –databases db_test1 db_test2 > structurebackfile.sql

備份伺服器上所有資料庫
格式：mysqldump –all-databases > allbackupfile.sql
 
========================================================================
 
還原MySQL資料庫的命令
格式：mysql -h{hostname} -u{username} -p{password} {databasename} < {backfile.sql}
例如：
 
代碼如下：

代碼如下	複製代碼
mysql -hlocalhost -uroot -p123456 db_test4 < back_file1.sql

還原壓縮的MySQL資料庫
格式：gunzip < {backfile.sql.gz} | mysql –u{username} –p{password} {databasename}
例如：
 
代碼如下：

代碼如下	複製代碼
gunzip < backfile.sql.gz | mysql –uzhuchao –p123456 db_test5

將資料庫轉移到新伺服器
mysqldump –u{username} –p{password} {databasename} | mysql –host=*.*.*.* –C {databasename}
 
二、其他：
1、如果連接埠為預設的3306時，可省略 -P {連接埠號碼} 這一項。
 
2、命令列格式中 { } 中的內容都是變數
主機名稱 : {hostname}
端   口：{port}    （一般預設3306，可預設）
使用者名稱：{user} {username} （如root）
密   碼：{password}
資料庫名 ：{databasename}
表   名：{table}  {table1}  {table2}
檔案名稱：{backfile.sql}

相關知識：

本文討論 MySQL 的備份和恢複機制，以及如何維護資料表，包括最主要的兩種表類型：MyISAM 和 Innodb，文中設計的 MySQL 版本為 5.0.22。

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

1、mysqldump

1.1 備份

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

現在來講一下 mysqldump 的一些主要參數：

–compatible=name

它告訴 mysqldump，匯出的資料將和哪種資料庫或哪箇舊版本的 MySQL 伺服器相相容。值可以為 ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options 等，要使用幾個值，用逗號將它們隔開。當然了，它並不保證能完全相容，而是盡量相容。

–complete-insert，-c

匯出的資料採用包含欄位名的完整 INSERT 方式，也就是把所有的值都寫在一行。這麼做能提高插入效率，但是可能會受到 max_allowed_packet 參數的影響而導致插入失敗。因此，需要謹慎使用該參數，至少我不推薦。

–default-character-set=charset

指定匯出資料時採用何種字元集，如果資料表不是採用預設的 latin1 字元集的話，那麼匯出時必須指定該選項，否則再次匯入資料後將產生亂碼問題。

–disable-keys

告訴 mysqldump 在 INSERT 語句的開頭和結尾增加 /*!40000 ALTER TABLE table DISABLE KEYS */; 和 /*!40000 ALTER TABLE table ENABLE KEYS */; 語句，這能大大提高插入語句的速度，因為它是在插入完所有資料後才重建索引的。該選項只適合 MyISAM 表。

–extended-insert = true|false

預設情況下，mysqldump 開啟 –complete-insert 模式，因此不想用它的的話，就使用本選項，設定它的值為 false 即可。

–hex-blob

使用十六進位格式匯出二進位字串欄位。如果有位元據就必須使用本選項。影響到的欄位類型有 BINARY、VARBINARY、BLOB。

–lock-all-tables，-x

在開始匯出之前，提交請求鎖定所有資料庫中的所有表，以保證資料的一致性。這是一個全域讀鎖，並且自動關閉 –single-transaction 和 –lock-tables 選項。

–lock-tables

它和 –lock-all-tables 類似，不過是鎖定當前置出的資料表，而不是一下子鎖定全部庫下的表。本選項只適用於 MyISAM 表，如果是 Innodb 表可以用 –single-transaction 選項。

–no-create-info，-t

只匯出資料，而不添加 CREATE TABLE 語句。

–no-data，-d

不匯出任何資料，只匯出資料庫表結構。

–opt

這隻是一個快捷選項，等同於同時添加 –add-drop-tables –add-locking –create-option –disable-keys –extended-insert –lock-tables –quick –set-charset 選項。本選項能讓 mysqldump 很快的匯出資料，並且匯出的資料能很快導回。該選項預設開啟，但可以用 –skip-opt 禁用。注意，如果運行 mysqldump 沒有指定 –quick 或 –opt 選項，則會將整個結果集放在記憶體中。如果匯出大資料庫的話可能會出現問題。

–quick，-q

該選項在匯出大表時很有用，它強制 mysqldump 從伺服器查詢取得記錄直接輸出而不是取得所有記錄後將它們緩衝到記憶體中。

–routines，-R

匯出預存程序以及自訂函數。

–single-transaction

該選項在匯出資料之前提交一個 BEGIN SQL語句，BEGIN 不會阻塞任何應用程式且能保證匯出時資料庫的一致性狀態。它只適用於事務表，例如 InnoDB 和 BDB。

本選項和 –lock-tables 選項是互斥的，因為 LOCK TABLES 會使任何掛起的事務隱含提交。

要想匯出大表的話，應結合使用 –quick 選項。

–triggers

同時匯出觸發器。該選項預設啟用，用 –skip-triggers 禁用它。

其他參數詳情請參考手冊，我通常使用以下 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 來備份 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

另外，如果想要實現線上備份，還可以使用 –master-data 參數來實現，如下：

代碼如下	複製代碼
/usr/local/mysql/bin/mysqldump -uyejr -pyejr –default-character-set=utf8 –opt –master-data=1 –single-transaction –flush-logs db_name > db_name.sql

它只是在一開始的瞬間請求鎖表，然後就重新整理binlog了，而後在匯出的檔案中加入CHANGE MASTER 語句來指定當前備份的binlog位置，如果要把這個檔案恢複到slave裡去，就可以採用這種方法來做。

1.2 還原

用 mysqldump 備份出來的檔案是一個可以直接倒入的 SQL 指令碼，有兩種方法可以將資料匯入。

直接用 mysql 用戶端

例如：

代碼如下	複製代碼
/usr/local/mysql/bin/mysql -uyejr -pyejr db_name < db_name.sql

用 SOURCE 文法

其實這不是標準的 SQL 文法，而是 mysql 用戶端提供的功能，例如：

代碼如下	複製代碼
SOURCE /tmp/db_name.sql;

這裡需要指定檔案的絕對路徑，並且必須是 mysqld 運行使用者(例如 nobody)有許可權讀取的檔案