官场小说排行榜,小说阅读网

新聞中心

這里有您想知道的互聯(lián)網(wǎng)營銷解決方案

MySQLDump詳解，你學(xué)會了嗎？

在進行數(shù)據(jù)庫備份的時候主要分為了邏輯備份和物理備份這兩種方式。在數(shù)據(jù)遷移和備份恢復(fù)中使用mysqldump將數(shù)據(jù)生成sql進行保存是最常用的方式之一。

專注于為中小企業(yè)提供成都網(wǎng)站設(shè)計、網(wǎng)站建設(shè)服務(wù),電腦端+手機端+微信端的三站合一,更高效的管理,為中小企業(yè)米脂免費做網(wǎng)站提供優(yōu)質(zhì)的服務(wù)。我們立足成都，凝聚了一批互聯(lián)網(wǎng)行業(yè)人才，有力地推動了數(shù)千家企業(yè)的穩(wěn)健成長，幫助中小企業(yè)通過網(wǎng)站建設(shè)實現(xiàn)規(guī)模擴充和轉(zhuǎn)變。

本文將圍繞著mysqldump的使用，工作原理，以及對于InnoDB和MyISAM兩種不同引擎如何實現(xiàn)數(shù)據(jù)一致性這三個方面進行介紹。

一.mysqldump 簡介

mysqldump是MySQL自帶的邏輯備份工具。
它的備份原理是通過協(xié)議連接到 MySQL數(shù)據(jù)庫，將需要備份的數(shù)據(jù)查詢出來，
將查詢出的數(shù)據(jù)轉(zhuǎn)換成對應(yīng)的insert語句，當(dāng)我們需要還原這些數(shù)據(jù)時，
只要執(zhí)行這些insert語句，即可將對應(yīng)的數(shù)據(jù)還原。

二.備份的命令

2.1命令的格式

1.mysqldump [選項] 數(shù)據(jù)庫名 [表名] > 腳本名
2.mysqldump [選項] --數(shù)據(jù)庫名 [選項 表名] > 腳本名
3.mysqldump [選項] --all-databases [選項]  > 腳本名

2.2選項說明

參數(shù)名	縮寫	含義
--host	-h	服務(wù)器IP地址
--port	-P	服務(wù)器端口號
--user	-u	MySQL 用戶名
--pasword	-p	MySQL 密碼
--databases		指定要備份的數(shù)據(jù)庫
--all-databases		備份mysql服務(wù)器上的所有數(shù)據(jù)庫
--compact		壓縮模式，產(chǎn)生更少的輸出
--comments		添加注釋信息
--complete-insert		輸出完成的插入語句
--lock-tables		備份前，鎖定所有數(shù)據(jù)庫表
--no-create-db/--no-create-info		禁止生成創(chuàng)建數(shù)據(jù)庫語句
--force		當(dāng)出現(xiàn)錯誤時仍然繼續(xù)備份操作
--default-character-set		指定默認(rèn)字符集
--add-locks		備份數(shù)據(jù)庫表時鎖定數(shù)據(jù)庫表

三.還原的命令

3.1系統(tǒng)行命令

mysqladmin -uroot -p create db_name 
mysql -uroot -p  db_name < /backup/mysqldump/db_name.db

注：在導(dǎo)入備份數(shù)據(jù)庫前，db_name如果沒有，是需要創(chuàng)建的； 而且與db_name.db中數(shù)據(jù)庫名是一樣的才可以導(dǎo)入。

3.2source方式

mysql > use db_name;
mysql > source /backup/mysqldump/db_name.db;

四.mysqldump實現(xiàn)的原理

4.1備份流程如下

1.調(diào)用FWRL（flush tables with read lock）,全局禁止讀寫
2.開啟快照讀，獲取此期間的快照（僅僅對innodb起作用）
3.備份非innodb表數(shù)據(jù)（*.frm,*.myi,*.myd等）
4.非innodb表備份完畢之后，釋放FTWRL
5.逐一備份innodb表數(shù)據(jù)
6.備份完成

4.2執(zhí)行mysqldump，分析備份日志

#  執(zhí)行語句
[root@localhost backup]# mysqldump -uroot -proot -h127.0.0.1 --all-databases --single-transaction --routines --events --triggers --master-data=2 --hex-blob --default-character-set=utf8mb4 --flush-logs --quick > all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

[root@localhost ~]# tail -f  /var/lib/mysql/localhost.log
第一步：
  FLUSH /*!40101 LOCAL */ TABLES
# 這里是刷新表


第二步：
  FLUSH TABLES WITH READ LOCK
# 因為開啟了--master-data=2，這時就需要flush tables with read lock鎖住全庫，
  記錄當(dāng)時的master_log_file和master_log_pos點
  這里有一個疑問？
  執(zhí)行flush tables操作，并加一個全局讀鎖，那么以上兩個命令貌似是重復(fù)的，
  為什么不在第一次執(zhí)行flush tables操作的時候加上鎖呢？
  簡而言之，是為了避免較長的事務(wù)操作造成FLUSH TABLES WITH READ LOCK操作遲遲得不到
  鎖，但同時又阻塞了其它客戶端操作。
  
  
第三步：
  SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
#  --single-transaction參數(shù)的作用，設(shè)置事務(wù)的隔離級別為可重復(fù)讀，
  即REPEATABLE READ，這樣能保證在一個事務(wù)中所有相同的查詢讀取到同樣的數(shù)據(jù)，
  也就大概保證了在dump期間，如果其他innodb引擎的線程修改了表的數(shù)據(jù)并提交，
  對該dump線程的數(shù)據(jù)并無影響，然而這個還不夠，還需要看下一條


第四步：
  START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
# 獲取當(dāng)前數(shù)據(jù)庫的快照，這個是由mysqldump中--single-transaction決定的。
# WITH CONSISTENT SNAPSHOT能夠保證在事務(wù)開啟的時候，第一次查詢的結(jié)果就是
  事務(wù)開始時的數(shù)據(jù)A，即使這時其他線程將其數(shù)據(jù)修改為B，查的結(jié)果依然是A。簡而言之，就是開啟事務(wù)并對所有表執(zhí)行了一次SELECT操作，這樣可保證備份時，
  在任意時間點執(zhí)行select * from table得到的數(shù)據(jù)和
  執(zhí)行START TRANSACTION WITH CONSISTENT SNAPSHOT時的數(shù)據(jù)一致。
 【注意】，WITH CONSISTENT SNAPSHOT只在RR隔離級別下有效。

第五步：
  SHOW MASTER STATUS
# 這個是由--master-data決定的，記錄了開始備份時，binlog的狀態(tài)信息，
  包括MASTER_LOG_FILE和MASTER_LOG_POS

這里需要特別區(qū)分一下master-data和dump-slave
master-data:
--master-data=2表示在dump過程中記錄主庫的binlog和pos點，并在dump文件中注釋掉這一行；
--master-data=1表示在dump過程中記錄主庫的binlog和pos點，并在dump文件中不注釋掉這一行，即恢復(fù)時會執(zhí)行；
dump-slave
--dump-slave=2表示在dump過程中，在從庫dump，mysqldump進程也要在從庫執(zhí)行，
  記錄當(dāng)時主庫的binlog和pos點，并在dump文件中注釋掉這一行；
--dump-slave=1表示在dump過程中，在從庫dump，mysqldump進程也要在從庫執(zhí)行，
  記錄當(dāng)時主庫的binlog和pos點，并在dump文件中不注釋掉這一行；
 
第六步：
  UNLOCK TABLES
# 釋放鎖。

五.mysqldump對InnoDB和MyISAM兩種存儲引擎進行備份的差異。

5.1對于支持事務(wù)的引擎如InnoDB，參數(shù)上是在備份的時候加上 –single-transaction 保證數(shù)據(jù)一致性

–single-transaction 實際上通過做了下面兩個操作 ：

① 在開始的時候把該 session 的事務(wù)隔離級別設(shè)置成 repeatable read ；

② 然后啟動一個事務(wù)（執(zhí)行 begin ），備份結(jié)束的時候結(jié)束該事務(wù)（執(zhí)行 commit ）

有了這兩個操作，在備份過程中，該 session 讀到的數(shù)據(jù)都是啟動備份時的數(shù)據(jù)（同一個點）。可以理解為對于 InnoDB 引擎來說加了該參數(shù)，備份開始時就已經(jīng)把要備份的數(shù)據(jù)定下來了，
備份過程中的提交的事務(wù)時是看不到的，也不會備份進去。

5.2對于不支持事務(wù)的引擎如MyISAM，只能通過鎖表來保證數(shù)據(jù)一致性，這里分兩種情況：

1）導(dǎo)出全庫：加 –lock-all-tables 參數(shù)，這會在備份開始的時候啟動一個全局讀鎖 
  （執(zhí)行 flush tables with read lock），其他 session 可以讀取但不能更新數(shù)據(jù)，
   備份過程中數(shù)據(jù)沒有變化，所以最終得到的數(shù)據(jù)肯定是完全一致的；

2）導(dǎo)出單個庫：加 –lock-tables 參數(shù)，這會在備份開始的時候鎖該庫的所有表，
   其他 session 可以讀但不能更新該庫的所有表，該庫的數(shù)據(jù)一致；

當(dāng)前題目：MySQLDump詳解，你學(xué)會了嗎？
本文地址：http://m.fisionsoft.com.cn/article/dhopcop.html