MySQL mysqldump 原理淺析

百度百科這麼解釋：把整個資料庫裝載到一個單獨的文本檔案中。這個檔案包含有所有重建您的資料庫所需要的SQL指令。這個指令取得所有的模式（Schema，後面有解釋）并且将其轉換成DDL文法（CREATE語句，即資料庫定義語句），取得所有的資料、并且從這些資料中建立INSERT語句

mysqldump的大概流程整理如下（白話）：

1：連接配接資料庫，校驗賬戶，密碼，IP

2：進入INFORMATION_SCHEMA庫，擷取要備份的資料庫的資訊，包含存儲過程，視圖，表

3：進入INFORMATION_SCHEMA庫，擷取每個表的字段名稱，字段類型等資訊

4：查詢每個表的資料，select SQL_NO_CACHE from tbname

5：拼接成DDL SQL

6：寫入備份檔案

幾個重要的參數一定要明白

有一點需要說明就是single-transaction級别比鎖大 因為加上-sing** 就不會用--X

--master-data[=#]   This causes the binary log position and filename to be

                      appended to the output. If equal to 1, will print it as a

                      CHANGE MASTER command; if equal to 2, that command will

                      be prefixed with a comment symbol. This option will turn

                      --lock-all-tables on, unless --single-transaction is

                      specified too (in which case a global read lock is only

                      taken a short time at the beginning of the dump; don't

                      forget to read about --single-transaction below). In all

                      cases, any action on logs will happen at the exact moment

                      of the dump. Option automatically turns --lock-tables  off.

這個參數會運作--lock-all-tables，将master的binlog和postion資訊寫入SQL檔案的頭部，除非結合--single-transaction（但并不是說就完全的不會鎖表了，執行的時候也會添加短暫的全局讀鎖）

--single-transaction

該選項在導出資料之前送出一個 BEGIN SQL語句，BEGIN 不會阻塞任何應用程式且能保證導出時資料庫的一緻性狀态。它隻适用于事務表，例如 InnoDB 和 BDB。

本選項和 --lock-tables 選項是互斥的，因為 LOCK TABLES 會使任何挂起的事務隐含送出。

要想導出大表的話，應結合使用 --quick 選項

--skip-opt

遇到過好幾次mysqldump出來的備份不能導入，同樣的庫如果mysqldump --skip-opt是可以導入的，分析兩個不同的備份檔案，發現--skip-opt是一條條的insert語句，但是--opt一個表隻有1條insert語句，這樣有可能表資料太多導緻溢出，可以适當調整

my.cnf中的table_cache值盡量避免這種情況，生産伺服器中我設定的是table_cache=512，記憶體2G，問題解決。--opt導入的速度比--skip-opt要快點，畢竟是優化的，呵呵。table_cache還涉及到mysql的優化，要結合實際應用來調整。

--opt 

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

在開始導出之前，送出請求鎖定所有資料庫中的所有表，以保證資料的一緻性。這是一個全局讀鎖，并且自動關閉 --single-transaction 和 --lock-tables 選項。

--lock-tables 

它和 --lock-all-tables 類似，不過是鎖定目前導出的資料表，而不是一下子鎖定全部庫下的表。本選項隻适用于 MyISAM 表，如果是 Innodb 表可以用 --single-transaction 選項。

本文轉自 aklaus 51CTO部落格，原文連結:http://blog.51cto.com/aklaus/1677766