MySQL mysqldump 原理淺析

標籤：mysqldump single master

百度百科這麼解釋：把整個資料庫裝載到一個單獨的文字檔中。這個檔案包含有所有重建您的資料庫所需要的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 選項。

本文出自 “營運邦” 部落格，謝絕轉載！

MySQL mysqldump 原理淺析