MySQL資料庫備份工具mysqldump的使用（轉）

說明：MySQL中InnoDB和MyISAM類型資料庫，這個工具最新版本好像都已經支援了，以前可能存在于MyISAM的隻能隻用冷備份方式的說法。

備份指定庫：

mysqldump -h127.0.0.1 -uroot -p --lock-tables --events --triggers --routines --flush-logs --master-data=2 --databases db1 db2 db3 > ./db.sql      

備份所有庫：

mysqldump -uroot -p -hlocalhost --lock-all-tables --all-databases > /home/db.sql      

mysqldump指定條件備份：

mysqldump -u使用者名 -p密碼 -h主機 資料庫 a -w "sql條件" --lock-all-tables > 路徑      

案例：

mysqldump -uroot -p -hlocalhost db1 a -w "id in (select id from b)" --lock-all-tables > ./db.sql      

mysqldump恢複指定庫：

mysqldump -u使用者名 -p密碼 -h主機 資料庫 < 路徑      

mysql -uroot -p db1 < /home/db.sql      

恢複所有庫：

mysqldump -uroot -p -h 127.0.0.1 --all-databases < /home/db.sql      

參數詳解

• --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 表：

mysqldump -uroot -p "--default-character-set=utf8 --opt --extended-insert=false" --triggers -R --hex-blob -x db_name > db_name.sql      

使用以下 SQL 來備份 Innodb 表：

mysqldump -uroot -p "--default-character-set=utf8 --opt --extended-insert=false" --triggers -R --hex-blob --single-transaction db_name > db_name.sql      

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

mysqldump -uroot -p "--default-character-set=utf8 --opt --master-data=1" --single-transaction --flush-logs db_name > db_name.sql      

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

參考：

https://lvtao.net/database/938.html（以上内容轉自此篇文章）