沃趣科技  羅小波

---

導(dǎo)語(yǔ)

成都創(chuàng)新互聯(lián)主要從事網(wǎng)站設(shè)計(jì)制作、成都做網(wǎng)站、網(wǎng)頁(yè)設(shè)計(jì)、企業(yè)做網(wǎng)站、公司建網(wǎng)站等業(yè)務(wù)。立足成都服務(wù)永清,十多年網(wǎng)站建設(shè)經(jīng)驗(yàn),價(jià)格優(yōu)惠、服務(wù)專(zhuān)業(yè),歡迎來(lái)電咨詢(xún)建站服務(wù):13518219792

1、先看MySQLdump
1.1. mysqldump備份過(guò)程解讀
1.2. mysqldump備份過(guò)程中的關(guān)鍵步驟
      1.2.1. FLUSH TABLES和FLUSH TABLES WITH READ LOCK的區(qū)別
      1.2.2. 修改隔離級(jí)別的作用
      1.2.3. 使用WITH CONSISTENT SNAPSHOT子句的作用
      1.2.4. 使用savepoint來(lái)設(shè)置回滾點(diǎn)的作用
1.3. mysqldump有什么坑嗎？
      1.3.1. 坑一
      1.3.2. 坑二
      1.3.3. 有辦法改善這這些問(wèn)題嗎？
2、現(xiàn)在看innobackupex
2.1. innobackupex備份過(guò)程解讀
2.2. innobackupex為什么需要這么做
2.3. innobackupex有什么坑嗎？
3、總  結(jié)

導(dǎo)  讀

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

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

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

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

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

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

環(huán)境信息

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

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

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

• xtrabackup版本：2.4.4

• 主從IP（文中一些演示步驟需要用到主備復(fù)制架構(gòu)）： 
  * 主庫(kù)：192.168.2.111（以下稱(chēng)為A庫(kù)） 
  * 從庫(kù)：192.168.2.121（以下稱(chēng)為B庫(kù)）

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

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

1、先看mysqldump

1.1.mysqldump備份過(guò)程解讀

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

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

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

DNSKiarzh0eAmdiclS8EPKIh45V5BfWGN8uNj1Q/0">

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

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

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

上面的strace信息是不是看起來(lái)和general_log中的信息很像?。恳?yàn)間eneral_log中記錄的就是mysqldump發(fā)送過(guò)去的sql語(yǔ)句：

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

1.2. mysqldump備份過(guò)程中的關(guān)鍵步驟

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

• FLUSH TABLES

• 強(qiáng)制關(guān)閉所有正在使用的表，并刷新查詢(xún)緩存，從查詢(xún)緩存中刪除所有查詢(xún)緩存結(jié)果，類(lèi)似RESET QUERY CACHE語(yǔ)句的行為

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

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

• FLUSH TABLES WITH READ LOCK

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

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

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

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

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

• 執(zhí)行該語(yǔ)句需要RELOAD和LOCK TABLES權(quán)限

• 該語(yǔ)句僅適用于基表（持久表），不適用于臨時(shí)表，會(huì)自動(dòng)忽略，另外在對(duì)視圖使用該語(yǔ)句使會(huì)報(bào)錯(cuò)。

• 與LOCK TABLES語(yǔ)句類(lèi)似，在使用該語(yǔ)句對(duì)某表加鎖之后，再同一個(gè)會(huì)話中開(kāi)啟一個(gè)事務(wù)時(shí)，會(huì)被自動(dòng)解鎖

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

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

1.2.2. 修改隔離級(jí)別的作用

為什么要執(zhí)行SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ語(yǔ)句呢？因?yàn)楹罄m(xù)需要使用START TRANSACTION /!40100 WITH CONSISTENT SNAPSHOT語(yǔ)句開(kāi)啟一個(gè)一致性事務(wù)快照，根據(jù)事務(wù)一致性讀要求，一致性事務(wù)快照只支持RR隔離級(jí)別，在其他隔離級(jí)別下執(zhí)行語(yǔ)句START TRANSACTION /!40100 WITH CONSISTENT SNAPSHOT會(huì)報(bào)如下警告信息：

限于篇幅，本文將分期進(jìn)行推送，下一篇"mysqldump與innobackupex備份過(guò)程你知多少（二）"我們將接著介紹"mysqldump備份過(guò)程中的關(guān)鍵步驟"之"使用WITH CONSISTENT SNAPSHOT子句的作用"與"使用savepoint來(lái)設(shè)置回滾點(diǎn)的作用"，精彩內(nèi)容不容錯(cuò)過(guò)，敬請(qǐng)期待??！

當(dāng)前名稱(chēng)：mysqldump與innobackupex備份過(guò)程你知多少（一）
文章鏈接：http://jinyejixie.com/article38/pspjsp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供全網(wǎng)營(yíng)銷(xiāo)推廣、App開(kāi)發(fā)、軟件開(kāi)發(fā)、App設(shè)計(jì)、網(wǎng)站設(shè)計(jì)公司、網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶(hù)投稿、用戶(hù)轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)