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

導語

1、先看mysqldump
1.1. mysqldump備份過程解讀
1.2. mysqldump備份過程中的關鍵步驟
      1.2.1. FLUSH TABLES和FLUSH TABLES WITH READ LOCK的區(qū)別
      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、現(xiàn)在看innobackupex
2.1. innobackupex備份過程解讀
2.2. innobackupex為什么需要這么做
2.3. innobackupex有什么坑嗎？
3、總  結

導  讀

想必搞數(shù)據(jù)庫的都知道：

• mysqldump優(yōu)點：mysqldump的優(yōu)點就是邏輯備份，把數(shù)據(jù)生成sql形式保存，在單庫，單表數(shù)據(jù)遷移，備份恢復等場景方便，sql形式的備份文件通用，也方便在不同數(shù)據(jù)庫之間移植。對于innodb表可以在線備份。

• mysqldump缺點：mysqldump是單線程，數(shù)據(jù)量大的時候，備份時間長，甚至有可能在備份過程中非事務表長期鎖表對業(yè)務造成影響(sql形式的備份恢復時間也比較長)。mysqldump備份時會查詢所有的數(shù)據(jù)，這可能會把內(nèi)存中的熱點數(shù)據(jù)刷掉

• innobackupex優(yōu)點：物理備份可以繞過mysql server層，加上本身就是文件系統(tǒng)級別的備份，備份速度塊，恢復速度快，可以在線備份，支持并發(fā)備份，支持加密傳輸，支持備份限速

• innobackupex缺點：要提取部分庫表數(shù)據(jù)比較麻煩，不能按照基于時間點來恢復數(shù)據(jù)，并且不能遠程備份，只能本地備份，增量備份的恢復也比較麻煩。如果使用innobackupex的全備+binlog增量備份就可以解決基于時間點恢復的問題。

要查看備份過程中這倆備份工具都對數(shù)據(jù)庫做了什么操作，想必大家都知道：可以打開general_log來查。那么問題來了，general_log輸出的信息都代表什么？如果不這樣做會怎樣？這兩個備份工具會不會有什么平時被忽略的坑？請看下文分析，也許……你會發(fā)現(xiàn)原來之前對這倆備份工具好像也不是那么了解!

環(huán)境信息

• 服務器配置： 
  * CPU：4 vcpus 
  * 內(nèi)存：4G 
  * 磁盤：250G SAS 
  * 網(wǎng)卡：Speed: 1000Mb/s

• 操作系統(tǒng)：CentOS release 6.5 (Final)

• 數(shù)據(jù)庫版本：MySQL 5.7.17

• xtrabackup版本：2.4.4

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

• 數(shù)據(jù)庫關鍵配置參數(shù) 
  * 主庫：雙一，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

• 測試庫表創(chuàng)建(這里在同一個庫下創(chuàng)建兩個表，一個表為innodb引擎，一個為myisam引擎)

1、先看mysqldump

1.1.mysqldump備份過程解讀

通常，使用mysqldump備份期間，為了使得數(shù)據(jù)庫中加鎖時間盡量短，會使用--single-transaction選項來開啟一個一致性快照事務，為了使得備份期間能夠獲得一個與數(shù)據(jù)一致的binlog pos點，會使用--master-data選項，現(xiàn)在登錄A庫主機，使用這倆選項執(zhí)行備份演示。

• 先在數(shù)據(jù)庫中打開general_log：

• 使用mysqldump備份（使用strace捕獲執(zhí)行過程中的調(diào)用棧），這里緊以備份測試庫luoxiaobo為例進行演示：

DNSKiarzh0eAmdiclS8EPKIh45V5BfWGN8uNj1Q/0?wx_fmt=png">

• 備份完成之后，查看general_log中的內(nèi)容（去掉了一些無用信息）：

查看strace抓取的調(diào)用棧信息，限于篇幅，詳見為知筆記鏈接：

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

上面的strace信息是不是看起來和general_log中的信息很像??？因為general_log中記錄的就是mysqldump發(fā)送過去的sql語句：

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

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

1.2.1. FLUSH TABLES和FLUSH TABLES WITH READ LOCK的區(qū)別

• FLUSH TABLES

• 強制關閉所有正在使用的表，并刷新查詢緩存，從查詢緩存中刪除所有查詢緩存結果，類似RESET QUERY CACHE語句的行為

• 在MySQL 5.7官方文檔描述中，當有表正處于LOCK TABLES … READ語句加鎖狀態(tài)時，不允許使用FLUSH TABLES語句（另外一個會話執(zhí)行FLUSH TABLES會被阻塞），如果已經(jīng)使用LOCK TABLES … READ語句對某表加讀鎖的情況下要對另外的表執(zhí)行刷新，可以在另外一個會話中使用FLUSH TABLES tbl_name … WITH READ LOCK語句（稍后會講到）

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

• FLUSH TABLES WITH READ LOCK

• 關閉所有打開的表，并使用全局讀鎖鎖定整個實例下的所有表。此時，你可以方便地使用支持快照的文件系統(tǒng)進行快照備份，備份完成之后，使用UNLOCK TABLES語句釋放鎖。

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

• 注： 
  * FLUSH TABLES WITH READ LOCK語句不會阻塞日志表的寫入，例如：查詢?nèi)罩?，慢查詢?nèi)罩镜?nbsp;
  * FLUSH TABLES WITH READ LOCK語句與XA協(xié)議不兼容 
  * 如果一個會話中使用LOCK TABLES語句對某表加了表鎖，在該表鎖未釋放前，那么另外一個會話如果執(zhí)行FLUSH TABLES WITH READ LOCK語句會被阻塞，而如果數(shù)據(jù)庫中l(wèi)ock_wait_timeout參數(shù)設置時間太短，mysqldump將會因為執(zhí)行FLUSH TABLES WITH READ LOCK語句獲取全局讀鎖超時而導致備份失敗退出 
  * 如果一個會話正在執(zhí)行DDL語句，那么另外一個會話如果執(zhí)行FLUSH TABLES WITH READ LOCK語句會被阻塞，如果數(shù)據(jù)庫中l(wèi)ock_wait_timeout參數(shù)設置時間太短，mysqldump將會因為執(zhí)行FLUSH TABLES WITH READ LOCK語句獲取全局讀鎖超時而導致備份失敗退出 
  * 如果一個會話正在執(zhí)行DML大事務（DML語句正在執(zhí)行，數(shù)據(jù)正在發(fā)生修改，而不是使用lock in share mode和for update語句來顯式加鎖），那么另外一個會話如果執(zhí)行FLUSH TABLES WITH READ LOCK語句會被阻塞，如果數(shù)據(jù)庫中l(wèi)ock_wait_timeout參數(shù)設置時間太短，mysqldump將會因為執(zhí)行FLUSH TABLES WITH READ LOCK語句獲取全局讀鎖超時而導致備份失敗退出

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

• 刷新表并獲取指定表的讀鎖。該語句首先獲取表的獨占MDL鎖，所以需要等待該表的所有事務提交完成。然后刷新該表的表緩存，重新打開表，獲取表讀鎖（類似LOCK TABLES … READ），并將MDL鎖從獨占級別降級為共享。在該語句獲取表讀鎖、降級MDL鎖之后，其他會話可以讀取該表，但不能修改表數(shù)據(jù)及其表結構。

• 執(zhí)行該語句需要RELOAD和LOCK TABLES權限

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

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

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

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

1.2.2. 修改隔離級別的作用

為什么要執(zhí)行SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ語句呢？因為后續(xù)需要使用START TRANSACTION /!40100 WITH CONSISTENT SNAPSHOT語句開啟一個一致性事務快照，根據(jù)事務一致性讀要求，一致性事務快照只支持RR隔離級別，在其他隔離級別下執(zhí)行語句START TRANSACTION /!40100 WITH CONSISTENT SNAPSHOT會報如下警告信息：

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