前言

Hi,大家好,我是麥洛,今天我們聊聊MySQL的備份和恢復(fù),在下面文章中,你會(huì)了解到MySQL常見的備份類型,以及基于mysqldump命令在日常開發(fā)中如何做MySQL數(shù)據(jù)庫以及表的備份和恢復(fù)。

一.為什么要做備份?

• 在數(shù)據(jù)丟失的情況下快速恢復(fù)數(shù)據(jù)。
• 作為實(shí)驗(yàn)數(shù)據(jù)的來源：
• 將生產(chǎn)數(shù)據(jù)拷貝一份到到本地測試庫用于測試,不影響生產(chǎn)數(shù)據(jù)
• 升級數(shù)據(jù)庫版本時(shí),需要將生產(chǎn)數(shù)據(jù)拷貝到本地,然后在本地測試是否兼容,找出潛在不安全性

二 ?數(shù)據(jù)庫備份的類型

1 物理備份(原始備份)與邏輯備份

1.1 ?物理備份

物理備份指將數(shù)據(jù)庫內(nèi)容的目錄和數(shù)據(jù)文件、日志文件及配置文件的原始副本全部備份,適用于大型重要的數(shù)據(jù)庫在出現(xiàn)問題時(shí)需要快速恢復(fù)的場景。

1.1.1 物理備份方法具有以下特征

• 物理備份包括數(shù)據(jù)庫目錄和文件的精確副本。通常這是 MySQL 數(shù)據(jù)目錄的全部或部分副本。
• 物理備份方法比邏輯備份方法快，因?yàn)樗鼈冎簧婕拔募?fù)制而不設(shè)計(jì)文件轉(zhuǎn)換。
• 物理備份可以在 MySQL 服務(wù)器停止時(shí)執(zhí)行備份。如果服務(wù)器正在運(yùn)行，則需要執(zhí)行適當(dāng)?shù)逆i定，以便服務(wù)器在備份期間不更改數(shù)據(jù)庫內(nèi)容。MySQL Enterprise Backup（MySQL 提供企業(yè)級備份和恢復(fù)。它可以在多種平臺上提供熱備份、在線備份和非阻塞備份）可以自動(dòng)鎖定需要備份的表。
• 除了數(shù)據(jù)文件之外，備份還可以包括任何相關(guān)文件，如日志或配置文件。
• 物理備份工具包括 mysqlbackup （MySQL Enterprise Backup 組件的一部分），或 MyISAM 表的文件系統(tǒng)命令(如 cp、 scp、 tar、 rsync)。

1.1.2 物理備份恢復(fù)

• MySQL Enterprise Backup 恢復(fù) InnoDB 和其他已備份的表
• ndb_restore還原恢復(fù) NDB 表(NDB引擎)
• 使用文件系統(tǒng)命令復(fù)制的文件可以使用文件系統(tǒng)命令復(fù)制回原來的位置

1.2 邏輯備份

邏輯備份是指將數(shù)據(jù)庫的邏輯結(jié)構(gòu)(CREATE DATABASE、 CREATE TABLE 語句)和內(nèi)容(INSERT 語句或帶分隔符的文本文件)備份。它適用于小量的數(shù)據(jù)，我們可以直接編輯備份的數(shù)據(jù),修改表結(jié)構(gòu)或者內(nèi)容,然后在不同的服務(wù)器上面重新構(gòu)建數(shù)據(jù);

1.2.1 邏輯備份方法具有以下特征

• 邏輯備份是通過查詢 MySQL 服務(wù)器獲取數(shù)據(jù)庫表結(jié)構(gòu)和表內(nèi)容信息來完成的。
• 邏輯備份比物理備份方法慢，因?yàn)榉?wù)器必須訪問數(shù)據(jù)庫的信息并將其轉(zhuǎn)換為邏輯格式。如果輸出是在客戶端寫的，服務(wù)器還必須將其發(fā)送到客戶端側(cè),網(wǎng)絡(luò)傳輸也是影響的一方面。
• 邏輯備份是在運(yùn)行 MySQL 服務(wù)器的情況下執(zhí)行的。服務(wù)器不需要停機(jī)。
• 邏輯備份工具包括 mysqldump 程序和 SELECT... INTO OUTFILE 語句。
• 備份不包括日志或配置文件。
• 要還原邏輯備份，可以使用 mysql 客戶端(Navicat)處理 sql 格式的轉(zhuǎn)儲文件。若要加載帶分隔符的文本文件，請使用 loaddata 語句或 mysqlimport 客戶端。

2 在線備份和離線備份

在 MySQL 服務(wù)器運(yùn)行期間進(jìn)行備份，稱為在線備份。反之服務(wù)器停止時(shí)進(jìn)行備份。稱為離線備份。通常也可以稱為“熱備份”和“冷備份” ;

1 在線備份方法具有以下特征

• 在線備份對其他客戶端的干擾較小，這些客戶端可以在備份期間連接到 MySQL 服務(wù)器，并且可以訪問數(shù)據(jù)。
• 必須注意施加適當(dāng)?shù)逆i定，防止備份期間數(shù)據(jù)修改導(dǎo)致備份數(shù)據(jù)不完整。MySQL Enterprise Backup產(chǎn)品會(huì)自動(dòng)執(zhí)行這種鎖定。

2 離線備份方法具有以下特征

• 客戶端可能會(huì)受到負(fù)面影響，因?yàn)榉?wù)器在備份期間不可用。由于這個(gè)原因，這些備份數(shù)據(jù)通常是從從服務(wù)器上（主從復(fù)制）獲取的，當(dāng)然是在不影響可用性的情況下離線執(zhí)行。
• 備份過程更簡單，因?yàn)椴淮嬖诳蛻舳嘶顒?dòng)干擾的可能性。

在備份期間，客戶端能夠在備份數(shù)據(jù)時(shí)讀取數(shù)據(jù)。恢復(fù)修改數(shù)據(jù)并且不只是讀取數(shù)據(jù)，因此在還原數(shù)據(jù)時(shí)必須阻止客戶端訪問數(shù)據(jù)。

3 本地備份與遠(yuǎn)程備份

本地備份指在運(yùn)行 MySQL 服務(wù)器的同一臺主機(jī)上執(zhí)行備份操作，而遠(yuǎn)程備份是在另一臺主機(jī)上執(zhí)行備份操作。對于某些類型的備份，即使備份輸出是在服務(wù)器上，但可能是從遠(yuǎn)程主機(jī)啟動(dòng)備份。

• mysqldump可以連接到本地或遠(yuǎn)程服務(wù)器。
• SELECT ... INTO OUTFILE 可以從本地或遠(yuǎn)程客戶端主機(jī)啟動(dòng) SELECT... INTO OUTFILE，但是輸出文件是在服務(wù)器主機(jī)上創(chuàng)建的。

更多內(nèi)容閱讀：mysqldump ：https://dev.mysql.com/doc/refman/5.7/en/mysqldump.htmlSELECT ... INTO OUTFILE ：https://dev.mysql.com/doc/refman/5.7/en/select-into.html

4 完全備份與增量備份

完全備份指備份 MySQL 服務(wù)器在給定時(shí)間點(diǎn)管理的所有數(shù)據(jù)。增量備份指備份給定時(shí)間范圍內(nèi)(從一個(gè)時(shí)間點(diǎn)到另一個(gè)時(shí)間點(diǎn))對數(shù)據(jù)所做的更改。Mysql 有不同的方法來執(zhí)行完全備份，如前面所述。通過啟用 MySQL 服務(wù)器的binlog二進(jìn)制日志(服務(wù)器用于記錄數(shù)據(jù)更改) ，可以進(jìn)行增量備份。

5 完全恢復(fù)與時(shí)間點(diǎn)(增量)恢復(fù)

完全恢復(fù)還原備份的所有數(shù)據(jù)。這會(huì)將服務(wù)器的數(shù)據(jù)恢復(fù)到備份時(shí)的狀態(tài)。如果該狀態(tài)不是當(dāng)前的，可以在完全恢復(fù)之后進(jìn)行增量恢復(fù)，從而使服務(wù)器達(dá)到更新的狀態(tài)。

增量恢復(fù)是對給定時(shí)間跨度內(nèi)所做的更改進(jìn)行恢復(fù)。這也稱為時(shí)間點(diǎn)恢復(fù)。時(shí)點(diǎn)恢復(fù)基于binlog二進(jìn)制日志，通常在完全恢復(fù)備份文件之后進(jìn)行，然后將寫入二進(jìn)制日志文件中的數(shù)據(jù)更改作為增量恢復(fù)應(yīng)用于重做數(shù)據(jù)修改，并將服務(wù)器帶到所需的時(shí)間點(diǎn)。

三 . 使用mysqldump進(jìn)行備份

1備份SQL格式的數(shù)據(jù)

首先,我們看看我們的演示數(shù)據(jù)

1.1 備份所有數(shù)據(jù)庫

進(jìn)入MySQL安裝bin目錄下面執(zhí)行命令,接下來操作保持一致

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysqldump --all-databases --hex-blob >d:\alldatabases.sql

• --hex-blob ?:使用十六進(jìn)制符號轉(zhuǎn)儲二進(jìn)制字符串[解決導(dǎo)出中文亂碼問題]

結(jié)果:

1.2 備份特定數(shù)據(jù)庫

mysqldump --databases girls > d:\girls.sql

結(jié)果:

說明:

在單數(shù)據(jù)庫情況下，可以忽略以下 --databases選項(xiàng)

如果不帶此選項(xiàng),生成文件會(huì)不包含CREATE DATABASE

1.3 備份數(shù)據(jù)庫的特點(diǎn)表

mysqldump girls boys >d:\boys.sql

結(jié)果:

2.重載SQL格式的備份

2.1 不創(chuàng)建Database

如果備份文件是由mysqldump使用 --all-databases或 --databases選項(xiàng)創(chuàng)建的 ，則備份文件包含CREATE DATABASE和 USE語句，所以無需指定默認(rèn)數(shù)據(jù)庫就可以直接將數(shù)據(jù)重載;

首先,我們刪庫跑路..........

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql 

結(jié)果:

2.2 創(chuàng)建Database

備份文件不包含CREATE DATABASE和 USE語句 ，請首先創(chuàng)建數(shù)據(jù)庫（如有必要）：

#創(chuàng)建數(shù)據(jù)庫db1mysqladmin create db1

結(jié)果:(關(guān)閉數(shù)據(jù)庫連接,重復(fù)開啟,皆可以看到)

我們重新生成一個(gè)不不包含CREATE DATABASE和 USE語句的文件

mysqldump girls > d:\nodatabasegirls.sql

彩蛋:如果遇到這個(gè),就重啟下mysql服務(wù)吧,我也不知道為什么,哈哈

mysqldump: Got error: 1045: Access denied for user 'ODBC'@'localhost' (using password: NO) when trying to connect

向db1數(shù)據(jù)庫重載我們的數(shù)據(jù)

mysql db1

結(jié)果:

四.mysqldump的應(yīng)用場景

1 制作數(shù)據(jù)庫副本

shell> mysqldump db1 > dump.sqlshell> mysqladmin create db2shell> mysql db2 < dump.sql

2 將數(shù)據(jù)庫從一臺服務(wù)器復(fù)制到另一臺服務(wù)器

# 在服務(wù)器1上：shell> mysqldump --databases db1 > dump.sql# 在服務(wù)器2上：shell> mysql < dump.sql# 您可以--databases從 mysqldump命令中省略,然后自己創(chuàng)建數(shù)據(jù)庫# 在服務(wù)器1上：mysqldump db1 > dump.sql# 在服務(wù)器2上：shell> mysqladmin create db1shell> mysql db1 < dump.sql

3 轉(zhuǎn)儲表定義和內(nèi)容

# 不轉(zhuǎn)儲表數(shù)據(jù)shell> mysqldump --no-data test > dump-defs.sql#不輸出CREATE語句,只包含表數(shù)據(jù)shell> mysqldump --no-create-info test > dump-data.sql

4 使用mysqldump測試升級不兼容性

在考慮進(jìn)行MySQL升級時(shí)，應(yīng)將新版本與當(dāng)前生產(chǎn)版本分開安裝。然后，您可以從生產(chǎn)服務(wù)器中轉(zhuǎn)儲數(shù)據(jù)庫和表定義，并將它們加載到新服務(wù)器中，以驗(yàn)證它們是否正常。（這對于測試降級也很有用。）

#生產(chǎn)服務(wù)器shell> mysqldump --all-databases --no-data --routines --events > dump-defs.sql#升級服務(wù)器shell> mysql < dump-defs.sql

驗(yàn)證表定義正常后,在導(dǎo)入數(shù)據(jù)測試

#生產(chǎn)服務(wù)器shell> mysqldump --all-databases --no-create-info > dump-data.sql#升級服務(wù)器shell> mysql < dump-data.sql

這時(shí)候,可以進(jìn)行一些簡單查詢測試,觀察是否正常

五 總結(jié)

在本文中,我們只是簡單介紹了mysqldump的基礎(chǔ)操作,關(guān)于它的選項(xiàng)總共有113個(gè)，大家可以根據(jù)自己需要自行去官網(wǎng)https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html查閱學(xué)習(xí)

更多信息請關(guān)注公眾號：「軟件老王」，關(guān)注不迷路，軟件老王和他的IT朋友們，分享一些他們的技術(shù)見解和生活故事。