mysqldump與innobackupex備份過程你知多少（一）

沃趣科技  羅小波

---

導語

1、先看mysqldump
1.1. mysqldump備份過程解讀
1.2. mysqldump備份過程中的關鍵步驟
      1.2.1. FLUSH TABLES和FLUSH TABLES WITH READ LOCK的區別
      1.2.2. 修改隔離級別的作用
      1.2.3. 使用WITH CONSISTENT SNAPSHOT子句的作用
      1.2.4. 使用savepoint來設定回滾點的作用
1.3. mysqldump有什麼坑嗎？
      1.3.1. 坑一
      1.3.2. 坑二
      1.3.3. 有辦法改善這這些問題嗎？
2、現在看innobackupex
2.1. innobackupex備份過程解讀
2.2. innobackupex為什麼需要這麼做
2.3. innobackupex有什麼坑嗎？
3、總  結

導  讀

想必搞資料庫的都知道：

• mysqldump優點：mysqldump的優點就是邏輯備份，把資料生成sql形式儲存，在單庫，單表資料遷移，備份恢復等場景方便，sql形式的備份檔案通用，也方便在不同資料庫之間移植。對於innodb表可以線上備份。

• mysqldump缺點：mysqldump是單執行緒，資料量大的時候，備份時間長，甚至有可能在備份過程中非事務表長期鎖表對業務造成影響(sql形式的備份恢復時間也比較長)。mysqldump備份時會查詢所有的資料，這可能會把記憶體中的熱點資料刷掉

• innobackupex優點：物理備份可以繞過mysql server層，加上本身就是檔案系統級別的備份，備份速度塊，恢復速度快，可以線上備份，支援併發備份，支援加密傳輸，支援備份限速

• innobackupex缺點：要提取部分庫表資料比較麻煩，不能按照基於時間點來恢復資料，並且不能遠端備份，只能本地備份，增量備份的恢復也比較麻煩。如果使用innobackupex的全備+binlog增量備份就可以解決基於時間點恢復的問題。

要檢視備份過程中這倆備份工具都對資料庫做了什麼操作，想必大家都知道：可以開啟general_log來查。那麼問題來了，general_log輸出的資訊都代表什麼？如果不這樣做會怎樣？這兩個備份工具會不會有什麼平時被忽略的坑？請看下文分析，也許……你會發現原來之前對這倆備份工具好像也不是那麼瞭解!

環境資訊

• 伺服器配置： 
  * CPU：4 vcpus 
  * 記憶體：4G 
  * 磁碟：250G SAS 
  * 網路卡：Speed: 1000Mb/s

• 作業系統：CentOS release 6.5 (Final)

• 資料庫版本：MySQL 5.7.17

• xtrabackup版本：2.4.4

• 主從IP（文中一些演示步驟需要用到主備複製架構）： 
  * 主庫：192.168.2.111（以下稱為A庫） 
  * 從庫：192.168.2.121（以下稱為B庫）

• 資料庫關鍵配置引數 
  * 主庫：雙一，log_slave_updates，log-bin，binlog_rows_query_log_events=ON，server-id=3306111，gtid_mode=ON，enforce_gtid_consistency=ON，auto_increment_increment=2，auto_increment_offset=1 
  * 備庫：雙一，log_slave_updates，log-bin，binlog_rows_query_log_events=ON，server-id=3306121，gtid_mode=ON，enforce_gtid_consistency=ON，auto_increment_increment=2，auto_increment_offset=2

• 測試庫表建立(這裡在同一個庫下建立兩個表，一個表為innodb引擎，一個為myisam引擎)

1、先看mysqldump

1.1.mysqldump備份過程解讀

通常，使用mysqldump備份期間，為了使得資料庫中加鎖時間儘量短，會使用--single-transaction選項來開啟一個一致性快照事務，為了使得備份期間能夠獲得一個與資料一致的binlog pos點，會使用--master-data選項，現在登入A庫主機，使用這倆選項執行備份演示。

• 先在資料庫中開啟general_log：

• 使用mysqldump備份（使用strace捕獲執行過程中的呼叫棧），這裡緊以備份測試庫luoxiaobo為例進行演示：

• 備份完成之後，檢視general_log中的內容（去掉了一些無用資訊）：

檢視strace抓取的呼叫棧資訊，限於篇幅，詳見為知筆記連結：

• http://5d096a11.wiz03.com/share/s/1t2mEh0a-kl_2c2NZ33kSiac3oxBB40tGQNY2L6Z_M2LtLbG

上面的strace資訊是不是看起來和general_log中的資訊很像啊？因為general_log中記錄的就是mysqldump傳送過去的sql語句：

• 從上面general_log和strace資訊對比我們可以知道，strace資訊代表了mysqldump程式對資料庫程式傳送了哪些請求資訊，general_log代表了資料庫中所有的客戶端sql請求操作記錄，這就是大家熟知的mysqldump備份過程中的關鍵步驟，那麼。。問題來了，mysqldump備份過程中為什麼需要這些 步驟？不這麼做會怎樣？下面對這些步驟逐一使用演示步驟進行詳細解釋

1.2. mysqldump備份過程中的關鍵步驟

1.2.1. FLUSH TABLES和FLUSH TABLES WITH READ LOCK的區別

• FLUSH TABLES

• 強制關閉所有正在使用的表，並重新整理查詢快取，從查詢快取中刪除所有查詢快取結果，類似RESET QUERY CACHE語句的行為

• 在MySQL 5.7官方文件描述中，當有表正處於LOCK TABLES … READ語句加鎖狀態時，不允許使用FLUSH TABLES語句（另外一個會話執行FLUSH TABLES會被阻塞），如果已經使用LOCK TABLES … READ語句對某表加讀鎖的情況下要對另外的表執行重新整理，可以在另外一個會話中使用FLUSH TABLES tbl_name … WITH READ LOCK語句（稍後會講到）

• 注意： 
  * 如果一個會話中使用LOCK TABLES語句對某表加了表鎖，在該表鎖未釋放前，那麼另外一個會話如果執行FLUSH TABLES語句會被阻塞 
  * 如果一個會話正在執行DDL語句，那麼另外一個會話如果執行FLUSH TABLES 語句會被阻塞 
  * 如果一個會話正在執行DML大事務（DML語句正在執行，資料正在發生修改，而不是使用lock in share mode和for update語句來顯式加鎖），那麼另外一個會話如果執行FLUSH TABLES語句會被阻塞

• FLUSH TABLES WITH READ LOCK

• 關閉所有開啟的表，並使用全域性讀鎖鎖定整個例項下的所有表。此時，你可以方便地使用支援快照的檔案系統進行快照備份，備份完成之後，使用UNLOCK TABLES語句釋放鎖。

• FLUSH TABLES WITH READ LOCK語句獲取的是一個全域性讀鎖，而不是表鎖，因此表現行為不會像LOCK TABLES和UNLOCK TABLES語句，LOCK TABLES和UNLOCK TABLES語句在與事務混搭時，會出現一些相互影響的情況，如下： 
  * 如果有表使用了LOCK TABLES語句加鎖，那麼開啟一個事務會造成該表的表鎖被釋放（注意是任何表的表鎖，只要存在表鎖都會被釋放，另外，必須是同一個會話中操作才會造成這個現象），就類似執行了UNLOCK TABLES語句一樣，但使用FLUSH TABLES WITH READ LOCK語句加全域性讀鎖，開啟一個事務不會造成全域性讀鎖被釋放 
  * 如果你開啟了一個事務，然後在事務內使用LOCK TABLES語句加鎖和FLUSH TABLES WITH READ LOCK語句加全域性讀鎖（注意，是對任何表加表鎖，只要使用了LOCK TABLES），會造成該事務隱式提交 
  * 如果你開啟了一個事務，然後在事務內使用UNLOCK TABLES語句，無效 
  * 官方文件中還有一句："如果有表使用LOCK TABLES語句加表鎖，在使用UNLOCK TABLES語句解鎖時會造成該表的所有事務隱式提交"，個人認為這是理論上的說法，或者說本人能力有限，暫未想到可能會造成這種情況的原因，因為實際上使用LOCK TABLES語句語句時，開啟一個事務會造成自動解鎖（前面已經提到過），而如果在事務內使用LOCK TABLES語句會造成事務隱式提交（前面已經提到過），所以實際上不可能出現在事務內使用UNLOCK TABLES語句解鎖LOCK TABLES語句的情況，而如果是使用FLUSH TABLES WITH READ LOCK語句，如果執行該語句之前存在LOCK TABLES加的表鎖，則FLUSH TABLES WITH READ LOCK語句發生阻塞，如果是已經執行FLUSH TABLES WITH READ LOCK語句，LOCK TABLES語句發生阻塞，不會再有任何的表鎖和互斥鎖能夠被獲取到（新的非select和show的請求都會被阻塞）。所以不可能出現UNLOCK TABLES語句解鎖時造成隱式提交

• 注： 
  * FLUSH TABLES WITH READ LOCK語句不會阻塞日誌表的寫入，例如：查詢日誌，慢查詢日誌等 
  * FLUSH TABLES WITH READ LOCK語句與XA協議不相容 
  * 如果一個會話中使用LOCK TABLES語句對某表加了表鎖，在該表鎖未釋放前，那麼另外一個會話如果執行FLUSH TABLES WITH READ LOCK語句會被阻塞，而如果資料庫中lock_wait_timeout引數設定時間太短，mysqldump將會因為執行FLUSH TABLES WITH READ LOCK語句獲取全域性讀鎖超時而導致備份失敗退出 
  * 如果一個會話正在執行DDL語句，那麼另外一個會話如果執行FLUSH TABLES WITH READ LOCK語句會被阻塞，如果資料庫中lock_wait_timeout引數設定時間太短，mysqldump將會因為執行FLUSH TABLES WITH READ LOCK語句獲取全域性讀鎖超時而導致備份失敗退出 
  * 如果一個會話正在執行DML大事務（DML語句正在執行，資料正在發生修改，而不是使用lock in share mode和for update語句來顯式加鎖），那麼另外一個會話如果執行FLUSH TABLES WITH READ LOCK語句會被阻塞，如果資料庫中lock_wait_timeout引數設定時間太短，mysqldump將會因為執行FLUSH TABLES WITH READ LOCK語句獲取全域性讀鎖超時而導致備份失敗退出

• FLUSH TABLES tbl_name [，tbl_name] … WITH READ LOCK

• 重新整理表並獲取指定表的讀鎖。該語句首先獲取表的獨佔MDL鎖，所以需要等待該表的所有事務提交完成。然後重新整理該表的表快取，重新開啟表，獲取表讀鎖（類似LOCK TABLES … READ），並將MDL鎖從獨佔級別降級為共享。在該語句獲取表讀鎖、降級MDL鎖之後，其他會話可以讀取該表，但不能修改表資料及其表結構。

• 執行該語句需要RELOAD和LOCK TABLES許可權

• 該語句僅適用於基表（持久表），不適用於臨時表，會自動忽略，另外在對檢視使用該語句使會報錯。

• 與LOCK TABLES語句類似，在使用該語句對某表加鎖之後，再同一個會話中開啟一個事務時，會被自動解鎖

• MySQL5.7官方文件描述說：這種新的變體語法能夠使得只針對某一個表加讀鎖的同時還能夠同時重新整理這個表，這解決了某表使用LOCK TABLES … READ語句加讀鎖時，需要重新整理表不能使用FLUSH TABLES語句的問題，此時可以使用FLUSH TABLES tbl_name [，tbl_name] … WITH READ LOCK語句代替，但是，官方描述不太清晰，實測在同一個會話中使用LOCK TABLES … READ語句加讀鎖時，不允許執行該語句（無論操作表是否是同一張表），會報錯：ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction，但是如果在不同的會話中，那麼，如果表不相同，允許執行，表相同，則FLUSH TABLES tbl_name [，tbl_name] … WITH READ LOCK語句發生等待

• 該語句同一個會話重複執行時，無論是否同一個表，都會報錯：ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transactio，如果是不同會話不同表則允許執行，但是表相同則發生等待

1.2.2. 修改隔離級別的作用

為什麼要執行SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ語句呢？因為後續需要使用START TRANSACTION /!40100 WITH CONSISTENT SNAPSHOT語句開啟一個一致性事務快照，根據事務一致性讀要求，一致性事務快照只支援RR隔離級別，在其他隔離級別下執行語句START TRANSACTION /!40100 WITH CONSISTENT SNAPSHOT會報如下警告資訊：

限於篇幅，本文將分期進行推送，下一篇"mysqldump與innobackupex備份過程你知多少（二）"我們將接著介紹"mysqldump備份過程中的關鍵步驟"之"使用WITH CONSISTENT SNAPSHOT子句的作用"與"使用savepoint來設定回滾點的作用"，精彩內容不容錯過，敬請期待！！

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28218939/viewspace-2142068/，如需轉載，請註明出處，否則將追究法律責任。