建站
咨詢
售后
建站咨詢
新聞中心
這里有您想知道的互聯(lián)網(wǎng)營(yíng)銷解決方案
MySQLDump詳解，你學(xué)會(huì)了嗎？
在進(jìn)行數(shù)據(jù)庫(kù)備份的時(shí)候主要分為了邏輯備份和物理備份這兩種方式。在數(shù)據(jù)遷移和備份恢復(fù)中使用mysqldump將數(shù)據(jù)生成sql進(jìn)行保存是最常用的方式之一。
專注于為中小企業(yè)提供成都網(wǎng)站設(shè)計(jì)、網(wǎng)站建設(shè)服務(wù),電腦端+手機(jī)端+微信端的三站合一,更高效的管理,為中小企業(yè)米脂免費(fèi)做網(wǎng)站提供優(yōu)質(zhì)的服務(wù)。我們立足成都，凝聚了一批互聯(lián)網(wǎng)行業(yè)人才，有力地推動(dòng)了數(shù)千家企業(yè)的穩(wěn)健成長(zhǎng)，幫助中小企業(yè)通過網(wǎng)站建設(shè)實(shí)現(xiàn)規(guī)模擴(kuò)充和轉(zhuǎn)變。
本文將圍繞著mysqldump的使用，工作原理，以及對(duì)于InnoDB和MyISAM兩種不同引擎如何實(shí)現(xiàn)數(shù)據(jù)一致性這三個(gè)方面進(jìn)行介紹。
一.mysqldump 簡(jiǎn)介
mysqldump是MySQL自帶的邏輯備份工具。
它的備份原理是通過協(xié)議連接到 MySQL數(shù)據(jù)庫(kù)，將需要備份的數(shù)據(jù)查詢出來，
將查詢出的數(shù)據(jù)轉(zhuǎn)換成對(duì)應(yīng)的insert語(yǔ)句，當(dāng)我們需要還原這些數(shù)據(jù)時(shí)，
只要執(zhí)行這些insert語(yǔ)句，即可將對(duì)應(yīng)的數(shù)據(jù)還原。
二.備份的命令
2.1命令的格式
1.mysqldump [選項(xiàng)] 數(shù)據(jù)庫(kù)名 [表名] > 腳本名
2.mysqldump [選項(xiàng)] --數(shù)據(jù)庫(kù)名 [選項(xiàng) 表名] > 腳本名
3.mysqldump [選項(xiàng)] --all-databases [選項(xiàng)] > 腳本名
2.2選項(xiàng)說明
|
參數(shù)名 |
縮寫 |
含義 |
|
--host |
-h |
服務(wù)器IP地址 |
|
--port |
-P |
服務(wù)器端口號(hào) |
|
--user |
-u |
MySQL 用戶名 |
|
--pasword |
-p |
MySQL 密碼 |
|
--databases |
指定要備份的數(shù)據(jù)庫(kù) | |
|
--all-databases |
備份mysql服務(wù)器上的所有數(shù)據(jù)庫(kù) | |
|
--compact |
壓縮模式，產(chǎn)生更少的輸出 | |
|
--comments |
添加注釋信息 | |
|
--complete-insert |
輸出完成的插入語(yǔ)句 | |
|
--lock-tables |
備份前，鎖定所有數(shù)據(jù)庫(kù)表 | |
|
--no-create-db/--no-create-info |
禁止生成創(chuàng)建數(shù)據(jù)庫(kù)語(yǔ)句 | |
|
--force |
當(dāng)出現(xiàn)錯(cuò)誤時(shí)仍然繼續(xù)備份操作 | |
|
--default-character-set |
指定默認(rèn)字符集 | |
|
--add-locks |
備份數(shù)據(jù)庫(kù)表時(shí)鎖定數(shù)據(jù)庫(kù)表 |
三.還原的命令
3.1系統(tǒng)行命令
mysqladmin -uroot -p create db_name
mysql -uroot -p db_name < /backup/mysqldump/db_name.db
注：在導(dǎo)入備份數(shù)據(jù)庫(kù)前，db_name如果沒有，是需要?jiǎng)?chuàng)建的； 而且與db_name.db中數(shù)據(jù)庫(kù)名是一樣的才可以導(dǎo)入。
3.2source方式
mysql > use db_name;
mysql > source /backup/mysqldump/db_name.db;
四.mysqldump實(shí)現(xiàn)的原理
4.1備份流程如下
1.調(diào)用FWRL（flush tables with read lock）,全局禁止讀寫
2.開啟快照讀，獲取此期間的快照（僅僅對(duì)innodb起作用）
3.備份非innodb表數(shù)據(jù)（*.frm,*.myi,*.myd等）
4.非innodb表備份完畢之后，釋放FTWRL
5.逐一備份innodb表數(shù)據(jù)
6.備份完成
4.2執(zhí)行mysqldump，分析備份日志
# 執(zhí)行語(yǔ)句
[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
# 因?yàn)殚_啟了--master-data=2，這時(shí)就需要flush tables with read lock鎖住全庫(kù)，
記錄當(dāng)時(shí)的master_log_file和master_log_pos點(diǎn)
這里有一個(gè)疑問？
執(zhí)行flush tables操作，并加一個(gè)全局讀鎖，那么以上兩個(gè)命令貌似是重復(fù)的，
為什么不在第一次執(zhí)行flush tables操作的時(shí)候加上鎖呢？
簡(jiǎn)而言之，是為了避免較長(zhǎng)的事務(wù)操作造成FLUSH TABLES WITH READ LOCK操作遲遲得不到
鎖，但同時(shí)又阻塞了其它客戶端操作。
第三步：
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
# --single-transaction參數(shù)的作用，設(shè)置事務(wù)的隔離級(jí)別為可重復(fù)讀，
即REPEATABLE READ，這樣能保證在一個(gè)事務(wù)中所有相同的查詢讀取到同樣的數(shù)據(jù)，
也就大概保證了在dump期間，如果其他innodb引擎的線程修改了表的數(shù)據(jù)并提交，
對(duì)該dump線程的數(shù)據(jù)并無影響，然而這個(gè)還不夠，還需要看下一條
第四步：
START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
# 獲取當(dāng)前數(shù)據(jù)庫(kù)的快照，這個(gè)是由mysqldump中--single-transaction決定的。
# WITH CONSISTENT SNAPSHOT能夠保證在事務(wù)開啟的時(shí)候，第一次查詢的結(jié)果就是
事務(wù)開始時(shí)的數(shù)據(jù)A，即使這時(shí)其他線程將其數(shù)據(jù)修改為B，查的結(jié)果依然是A。簡(jiǎn)而言之，就是開啟事務(wù)并對(duì)所有表執(zhí)行了一次SELECT操作，這樣可保證備份時(shí)，
在任意時(shí)間點(diǎn)執(zhí)行select * from table得到的數(shù)據(jù)和
執(zhí)行START TRANSACTION WITH CONSISTENT SNAPSHOT時(shí)的數(shù)據(jù)一致。
【注意】，WITH CONSISTENT SNAPSHOT只在RR隔離級(jí)別下有效。
第五步：
SHOW MASTER STATUS
# 這個(gè)是由--master-data決定的，記錄了開始備份時(shí)，binlog的狀態(tài)信息，
包括MASTER_LOG_FILE和MASTER_LOG_POS
這里需要特別區(qū)分一下master-data和dump-slave
master-data:
--master-data=2表示在dump過程中記錄主庫(kù)的binlog和pos點(diǎn)，并在dump文件中注釋掉這一行；
--master-data=1表示在dump過程中記錄主庫(kù)的binlog和pos點(diǎn)，并在dump文件中不注釋掉這一行，即恢復(fù)時(shí)會(huì)執(zhí)行；
dump-slave
--dump-slave=2表示在dump過程中，在從庫(kù)dump，mysqldump進(jìn)程也要在從庫(kù)執(zhí)行，
記錄當(dāng)時(shí)主庫(kù)的binlog和pos點(diǎn)，并在dump文件中注釋掉這一行；
--dump-slave=1表示在dump過程中，在從庫(kù)dump，mysqldump進(jìn)程也要在從庫(kù)執(zhí)行，
記錄當(dāng)時(shí)主庫(kù)的binlog和pos點(diǎn)，并在dump文件中不注釋掉這一行；
第六步：
UNLOCK TABLES
# 釋放鎖。
五.mysqldump對(duì)InnoDB和MyISAM兩種存儲(chǔ)引擎進(jìn)行備份的差異。
5.1對(duì)于支持事務(wù)的引擎如InnoDB，參數(shù)上是在備份的時(shí)候加上 –single-transaction 保證數(shù)據(jù)一致性
–single-transaction 實(shí)際上通過做了下面兩個(gè)操作 ：
① 在開始的時(shí)候把該 session 的事務(wù)隔離級(jí)別設(shè)置成 repeatable read ；
② 然后啟動(dòng)一個(gè)事務(wù)（執(zhí)行 begin ），備份結(jié)束的時(shí)候結(jié)束該事務(wù)（執(zhí)行 commit ）
有了這兩個(gè)操作，在備份過程中，該 session 讀到的數(shù)據(jù)都是啟動(dòng)備份時(shí)的數(shù)據(jù)（同一個(gè)點(diǎn)）?？梢岳斫鉃閷?duì)于 InnoDB 引擎來說加了該參數(shù)，備份開始時(shí)就已經(jīng)把要備份的數(shù)據(jù)定下來了，
備份過程中的提交的事務(wù)時(shí)是看不到的，也不會(huì)備份進(jìn)去。
5.2對(duì)于不支持事務(wù)的引擎如MyISAM，只能通過鎖表來保證數(shù)據(jù)一致性，這里分兩種情況：
1）導(dǎo)出全庫(kù)：加 –lock-all-tables 參數(shù)，這會(huì)在備份開始的時(shí)候啟動(dòng)一個(gè)全局讀鎖
（執(zhí)行 flush tables with read lock），其他 session 可以讀取但不能更新數(shù)據(jù)，
備份過程中數(shù)據(jù)沒有變化，所以最終得到的數(shù)據(jù)肯定是完全一致的；
2）導(dǎo)出單個(gè)庫(kù)：加 –lock-tables 參數(shù)，這會(huì)在備份開始的時(shí)候鎖該庫(kù)的所有表，
其他 session 可以讀但不能更新該庫(kù)的所有表，該庫(kù)的數(shù)據(jù)一致；
分享文章：MySQLDump詳解，你學(xué)會(huì)了嗎？
分享網(wǎng)址：http://www.5511xx.com/article/dhopcop.html
