mysql數(shù)據(jù)庫備份

在當(dāng)今數(shù)據(jù)驅(qū)動的時代，數(shù)據(jù)庫作為企業(yè)和個人數(shù)據(jù)存儲與管理的核心，其安全性至關(guān)重要。數(shù)據(jù)一旦丟失，可能會給企業(yè)帶來巨大的經(jīng)濟(jì)損失和聲譽(yù)損害，因此，數(shù)據(jù)庫備份成為保障數(shù)據(jù)安全的關(guān)鍵環(huán)節(jié)。

MySQL 作為一款廣泛使用的開源關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，深入了解其備份相關(guān)知識，對數(shù)據(jù)庫管理員和開發(fā)者而言都十分必要。

數(shù)據(jù)備份的重要性

數(shù)據(jù)是企業(yè)的核心資產(chǎn)，它包含了業(yè)務(wù)運(yùn)營、客戶信息、交易記錄等關(guān)鍵內(nèi)容。數(shù)據(jù)備份是應(yīng)對數(shù)據(jù)丟失風(fēng)險的重要手段，它能夠在數(shù)據(jù)庫遭遇硬件故障、軟件錯誤、人為誤操作、惡意攻擊（如勒索軟件）等情況時，快速恢復(fù)數(shù)據(jù)，確保業(yè)務(wù)的連續(xù)性。

例如，一家電商企業(yè)如果因?yàn)閿?shù)據(jù)庫崩潰而丟失了用戶訂單和支付記錄，不僅會導(dǎo)致客戶不滿，還可能面臨法律糾紛和經(jīng)濟(jì)賠償。而定期進(jìn)行數(shù)據(jù)備份，就可以在災(zāi)難發(fā)生后，將數(shù)據(jù)庫恢復(fù)到正常狀態(tài)，減少損失。

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

從物理與邏輯角度分類

• 物理備份：物理備份是對數(shù)據(jù)庫文件（如數(shù)據(jù)文件、日志文件等）的直接復(fù)制。這種備份方式直接操作數(shù)據(jù)庫的物理文件，備份速度快，恢復(fù)時也能快速還原數(shù)據(jù)，因?yàn)樗苯訉⑽募?fù)制回原來的位置。但它與特定的數(shù)據(jù)庫管理系統(tǒng)和操作系統(tǒng)相關(guān)，移植性較差。?
• 邏輯備份：邏輯備份是將數(shù)據(jù)庫中的數(shù)據(jù)以 SQL 語句或特定格式導(dǎo)出，例如使用mysqldump工具生成的 SQL 文件。邏輯備份文件可讀性強(qiáng)，可以方便地查看和編輯其中的數(shù)據(jù)，并且具有較好的移植性，可以在不同的數(shù)據(jù)庫環(huán)境中導(dǎo)入。但邏輯備份和恢復(fù)的速度相對較慢，因?yàn)樗枰馕龊蛨?zhí)行 SQL 語句。

從數(shù)據(jù)庫的備份策略角度分類

• 完全備份：完全備份是對整個數(shù)據(jù)庫進(jìn)行完整的備份，包含了數(shù)據(jù)庫中的所有數(shù)據(jù)和對象。這種備份方式可以獨(dú)立進(jìn)行恢復(fù)，恢復(fù)過程簡單直接。但由于每次都要備份全部數(shù)據(jù)，備份所需時間長，占用存儲空間大。?
• 增量備份：增量備份只備份自上次備份（可以是完全備份或上一次增量備份）以來發(fā)生變化的數(shù)據(jù)。增量備份的優(yōu)點(diǎn)是備份速度快，占用空間小，因?yàn)樗惶幚碜兓臄?shù)據(jù)。但恢復(fù)時需要依次應(yīng)用多個備份文件，恢復(fù)過程相對復(fù)雜。?
• 差異備份：差異備份備份自上次完全備份以來發(fā)生變化的數(shù)據(jù)。與增量備份相比，差異備份在恢復(fù)時只需要上次完全備份和本次差異備份兩個文件，恢復(fù)過程相對簡單，但備份數(shù)據(jù)量比增量備份大。

常見的備份方法

物理冷備份

物理冷備份是在數(shù)據(jù)庫處于關(guān)閉狀態(tài)下，直接復(fù)制數(shù)據(jù)庫的物理文件。

這種方式適用于對數(shù)據(jù)庫可用性要求不高的場景。在進(jìn)行物理冷備份前，需要確保數(shù)據(jù)庫服務(wù)已停止，以保證數(shù)據(jù)的一致性。

備份完成后，可以將文件存儲在安全的存儲設(shè)備中，如磁盤陣列或云存儲。

專用的備份工具musqldump或mysqlhotcopy

• mysqldump：mysqldump是 MySQL 自帶的邏輯備份工具，它通過執(zhí)行 SQL 語句將數(shù)據(jù)庫中的數(shù)據(jù)和結(jié)構(gòu)導(dǎo)出為 SQL 文件。mysqldump可以靈活地指定備份的數(shù)據(jù)庫、表，還可以設(shè)置各種選項(xiàng)，如添加鎖表、忽略錯誤等。它適用于各種 MySQL 版本，使用廣泛。?
• mysqlhotcopy：mysqlhotcopy是一個快速的物理備份工具，它只能在數(shù)據(jù)庫運(yùn)行在 MyISAM 存儲引擎時使用。mysqlhotcopy通過復(fù)制數(shù)據(jù)庫文件來實(shí)現(xiàn)備份，并且可以在數(shù)據(jù)庫運(yùn)行時進(jìn)行備份，不會阻塞數(shù)據(jù)庫的讀寫操作。但由于其對存儲引擎的限制，使用場景相對較窄。

通過啟用二進(jìn)制日志進(jìn)行增量備份

MySQL 的二進(jìn)制日志記錄了數(shù)據(jù)庫的所有更改操作，啟用二進(jìn)制日志后，可以利用它進(jìn)行增量備份。通過定期備份二進(jìn)制日志文件，并結(jié)合完全備份，可以實(shí)現(xiàn)基于時間點(diǎn)的恢復(fù)。

這種方式能夠在數(shù)據(jù)丟失時，將數(shù)據(jù)庫恢復(fù)到指定的時間點(diǎn)，最大程度地減少數(shù)據(jù)損失。

通過第三方工具備份

除了 MySQL 自帶的備份工具，還有許多第三方備份工具可供選擇，如 XtraBackup、Backup，Exec 等。

這些工具通常提供了更強(qiáng)大的功能，如圖形化界面、自動化備份策略、數(shù)據(jù)壓縮等，可以滿足不同用戶的需求。

數(shù)據(jù)庫完全備份操作

物理冷備份與恢復(fù)

備份數(shù)據(jù)庫

備份數(shù)據(jù)庫：首先停止 MySQL 服務(wù)，在 Linux 系統(tǒng)下可以使用命令service mysql stop。然后將數(shù)據(jù)庫的數(shù)據(jù)目錄（通常是/var/lib/mysql）復(fù)制到備份目錄中，例如使用命令cp -r /var/lib/mysql /backup/mysql_backup。

創(chuàng)建一個/backup 目錄作為備份數(shù)據(jù)存儲路徑,使用tar創(chuàng)建備份文件。

[root@localhost ^]# systemctl stop mysqld
[root@localhost~]# mkdir /backup
[root@localhost~]# tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/
[root@localhost~]# 1s -1 /backup/

恢復(fù)數(shù)據(jù)庫

恢復(fù)數(shù)據(jù)庫：先停止 MySQL 服務(wù)，刪除當(dāng)前的數(shù)據(jù)目錄內(nèi)容，然后將備份目錄中的數(shù)據(jù)文件復(fù)制回原來的數(shù)據(jù)目錄，最后啟動 MySQL 服務(wù)。

執(zhí)行下面操作將數(shù)據(jù)庫文件/usr/local/mysql/data/轉(zhuǎn)移至bak 目錄下,模擬故障。

[root@localhost `]# mkdir bak
[root@localhost ^]# mv/usr/local/mysql/data//root/bak/
[root@localhost ^]# mkdir restore

[root@localhost ^]# tar zxf /backup/mysql_all-2025-03-22. tar. gz -C restore/
[root@localhost ^]# mv restore/usr/local/mysql/data/ /usr/llocal/mysql/
[root@localhost ^]# systemctl start mysqld

mysqldump備份與恢復(fù)

備份數(shù)據(jù)庫

備份數(shù)據(jù)庫：使用命令mysqldump -u [用戶名] -p [數(shù)據(jù)庫名] > backup_file.sql，輸入密碼后即可將指定數(shù)據(jù)庫備份為 SQL 文件。

例如

mysqldump -u root -p mydatabase > mydatabase_backup.sql。

查看備份文件

查看備份文件：可以使用文本編輯器打開備份的 SQL 文件，查看其中的 SQL 語句和數(shù)據(jù)。

通過 mysqldump工具導(dǎo)出的SQL腳本是文本文件,其中"/*......./部分或以"一"開頭的行表示注釋信息。使用 grep、less、cat等文本工具可以查看腳本內(nèi)容。例如,執(zhí)行以下操作可以過濾出test.sql腳本中的數(shù)據(jù)庫操作都

[root@localhost ^]# grep -v ^--" test.sql | grep -v "^/" | grep -v
?REATE DATABASE /*!32312 IF NOT EXISTS*/ 'test`
/*!40100 DEFAULT
CHARACTER SET utf8mb3 */ /*!80016 DEFAULT ENCRYPTICN='N' */:
USE `test`;
DROP TABLE IF EXISTS
employee";
CREATE TABLE employee
id` int NOT NULL AUTO_INCREMENT,

恢復(fù)數(shù)據(jù)庫

恢復(fù)數(shù)據(jù)庫：先創(chuàng)建一個空的數(shù)據(jù)庫，然后使用命令mysql -u [用戶名] -p [數(shù)據(jù)庫名] < backup_file.sql，輸入密碼后即可將備份數(shù)據(jù)導(dǎo)入到新創(chuàng)建的數(shù)據(jù)庫中。

例如,執(zhí)行以下操作可以從備份文件mysql-user.sql中將表導(dǎo)入test庫。其中"-e"選項(xiàng)是用于指定連接 MySQL后執(zhí)行的命令,命令執(zhí)行完后自動退出。

[root@localhost ^]# mysql -u root -p test < mysql-user.sq1
Enter password:
[root@localhost ^]# mysql -u root -p -e 'SHOW TABLESFROM test;

Enter password:

Tables_in_test|
user

mysql增量備份與恢復(fù)

增量備份的特點(diǎn)

增量備份只備份變化的數(shù)據(jù)，因此備份速度快，占用空間小，適合對數(shù)據(jù)變化頻繁的數(shù)據(jù)庫進(jìn)行備份。但增量備份的恢復(fù)過程依賴于之前的備份文件，需要按照順序依次應(yīng)用多個備份文件，增加了恢復(fù)的復(fù)雜性和時間成本。

mysql二進(jìn)制日志對備份的意義

MySQL 的二進(jìn)制日志記錄了數(shù)據(jù)庫的所有更改操作，包括數(shù)據(jù)的插入、更新、刪除等。通過分析二進(jìn)制日志文件，可以獲取到數(shù)據(jù)庫在某個時間段內(nèi)的所有變化，從而實(shí)現(xiàn)基于時間點(diǎn)的恢復(fù)。在進(jìn)行增量備份時，結(jié)合二進(jìn)制日志文件，可以確保備份數(shù)據(jù)的完整性和一致性。

列：log-bin=/usr/local/mysql/mysql-bin,然后重啟MySQL服務(wù)就可以在指定路徑下查看二進(jìn)制日志文件了。默認(rèn)情況下,二進(jìn)制日志文件的擴(kuò)展又是一個六位的數(shù)字,如mysql-bin.0000001。

[root@localhost ^]# vim /etc/my.cnf
[mysqld]
log-bin=/usr/local/mysql/data/mysql-bin

binlog_format = MIXED
server-id=1
[root@localhost^]# systemctl restart mysqld

[root@localhost ^]# ls -1 /usr/local/mysql/data/mysql-bin.*

mysql增量恢復(fù)

在進(jìn)行增量恢復(fù)時，首先需要恢復(fù)上次的完全備份，然后依次應(yīng)用后續(xù)的增量備份文件和二進(jìn)制日志文件。

例如，先恢復(fù)完全備份文件，然后按照時間順序應(yīng)用增量備份 1、增量備份 2，最后通過二進(jìn)制日志文件將數(shù)據(jù)庫恢復(fù)到指定的時間點(diǎn)。

msyql企業(yè)備份案例

一般恢復(fù)

假設(shè)企業(yè)的數(shù)據(jù)庫因?yàn)橛布收蠈?dǎo)致數(shù)據(jù)丟失，首先使用上次的完全備份文件進(jìn)行恢復(fù)。如果完全備份之后還有增量備份，再依次應(yīng)用增量備份文件，使數(shù)據(jù)庫恢復(fù)到接近故障發(fā)生時的狀態(tài)。

這種恢復(fù)方式適用于大多數(shù)常規(guī)的數(shù)據(jù)丟失情況，能夠快速恢復(fù)數(shù)據(jù)庫的基本功能。

格式：mysqlbinlog[--no-defaults]增量備份文件 | mysql-u用戶名-p密碼

基于位置恢復(fù)

在某些情況下，企業(yè)可能需要將數(shù)據(jù)庫恢復(fù)到特定的操作位置。例如，發(fā)現(xiàn)某個錯誤的 SQL 語句執(zhí)行后導(dǎo)致數(shù)據(jù)異常，此時可以通過二進(jìn)制日志文件定位到該 SQL 語句執(zhí)行的位置，然后從上次完全備份開始，應(yīng)用備份文件和二進(jìn)制日志文件直到該位置，實(shí)現(xiàn)基于位置的恢復(fù)，避免錯誤操作帶來的影響擴(kuò)散。

• 格式（恢復(fù)到指定位置）mysqlbinlog --stop-position='操作 id'二進(jìn)制日志|mysql-u用月戶名-p 密碼
• 格式（從指定位置開始恢復(fù)） mysqlbinlog--start-position='操作 id'二進(jìn)制日志|mysql-u用戶名-p密碼

基于時間點(diǎn)恢復(fù)

當(dāng)企業(yè)需要將數(shù)據(jù)庫恢復(fù)到某個特定的時間點(diǎn)時，如誤刪除數(shù)據(jù)前的時刻，可以利用二進(jìn)制日志文件和備份文件實(shí)現(xiàn)基于時間點(diǎn)的恢復(fù)。

首先恢復(fù)完全備份，然后根據(jù)時間戳應(yīng)用增量備份和二進(jìn)制日志文件，將數(shù)據(jù)庫恢復(fù)到指定的時間點(diǎn)，最大程度地減少數(shù)據(jù)丟失。

• 格式（從日志開頭截止到某個時間點(diǎn)的恢復(fù)）
• mysqlbinlog [--no-defaults] --stop-datetime='年-月-日小時:分鐘:私進(jìn)制日志|mysql-u用戶名-p密碼
• 格式（從某個時間點(diǎn)到日志結(jié)尾的恢復(fù)）
• mysqlbinlog [--no-defaults] --start-datetime='年-月-日小時:分鐘:秒二進(jìn)制日志|mysql-u用戶名-p密碼
• 格式（從某個時間點(diǎn)到某個時間點(diǎn)的恢復(fù))
• mysqlbinlog [--no-defaults] --start-datetime='年-月-日小時:分鐘:秒--stop-datetime='年-月-日小時:分鐘:秒'二進(jìn)制日志|mysql-u用戶名-p密碼

制定企業(yè)備份策略的思路

制定企業(yè)備份策略需要綜合考慮多個因素，包括數(shù)據(jù)的重要性、業(yè)務(wù)的連續(xù)性要求、備份和恢復(fù)的時間成本、存儲成本等。對于關(guān)鍵業(yè)務(wù)數(shù)據(jù)庫，應(yīng)采用高頻次的完全備份和增量備份相結(jié)合的方式，以確保數(shù)據(jù)的安全性和快速恢復(fù)能力。同時，要定期測試備份的有效性和恢復(fù)過程，避免在實(shí)際需要恢復(fù)時出現(xiàn)問題。此外，還應(yīng)考慮將備份數(shù)據(jù)存儲在不同的地理位置，以防止自然災(zāi)害等不可抗力因素導(dǎo)致備份數(shù)據(jù)也丟失。

需要注意的是,不是備份完就萬事大吉,最好確認(rèn)備份是否可用,所以備份之后的恢復(fù)測試是非常有必要的。同時備份時間也要靈活調(diào)整,如

• 數(shù)據(jù)更新頻繁,則應(yīng)該頻繁地備份。
• 數(shù)據(jù)的重要性,在有適當(dāng)更新時進(jìn)行備份。
• 在數(shù)據(jù)庫壓力小的時間段進(jìn)行備份,如一周一次完全備份每天進(jìn)行增量備份。
• 中小公司,完全備份一般一天一次即可。
• 大公司可每周進(jìn)行一次完全備份,每天進(jìn)行一次增量備份
• 盡量為企業(yè)實(shí)現(xiàn)主從復(fù)制架構(gòu),以增加數(shù)據(jù)的可用性。

mysql的GTID

GTID（Global Transaction Identifier）是 MySQL 5.6 版本引入的新特性，它為每個事務(wù)分配一個唯一的標(biāo)識符，用于標(biāo)識事務(wù)在整個復(fù)制環(huán)境中的唯一性。GTID 使得復(fù)制和恢復(fù)操作更加簡單和可靠，它可以確保在主從復(fù)制環(huán)境中，事務(wù)的執(zhí)行順序和一致性。

配置GTID并進(jìn)行恢復(fù)全量等操作

在 MySQL 配置文件中啟用 GTID，添加配置項(xiàng)gtid_mode=ON和enforce_gtid_consistency=ON。啟用 GTID 后，在進(jìn)行全量恢復(fù)時，可以更方便地確定事務(wù)的執(zhí)行順序，確?；謴?fù)的數(shù)據(jù)一致性。例如，在從庫故障后恢復(fù)時，通過 GTID 可以快速定位到主庫上的事務(wù)位置，從正確的位置開始復(fù)制數(shù)據(jù)，提高恢復(fù)效率和準(zhǔn)確性。

通過下面的實(shí)驗(yàn),了解基于gtid的增量備份和恢復(fù)

配置my.cnf開啟gtid

[root@localhost ^]# vim /etc/my.cnf
[mysqld]
gtid_mode = ON
enforce_gtid_consistency=ON

[root@localhost ^]# systemctl restart mysqld

[root@localhost ^]# mysql -uroot -p
mysql> SHOW GLOBAL VARIABLES LIKEgtid_mode';

創(chuàng)建基本測試庫、表、數(shù)據(jù)

mysql>resetmaster;
mysql> show master status;

xtrabackup應(yīng)用在恢復(fù)操作中的案例

XtraBackup 是一款開源的 MySQL 熱備份工具，它可以在數(shù)據(jù)庫運(yùn)行時進(jìn)行物理備份，支持 InnoDB 和 XtraDB 存儲引擎。以下是一個 XtraBackup 應(yīng)用在恢復(fù)操作中的案例：?

TypeScript

取消自動換行復(fù)制

innobackupex --apply-log /backup/full_backup?
innobackupex --copy-back /backup/full_backup?

--apply-log選項(xiàng)用于準(zhǔn)備備份數(shù)據(jù)，使其可以用于恢復(fù)；--copy-back選項(xiàng)用于將備份數(shù)據(jù)復(fù)制到數(shù)據(jù)庫的數(shù)據(jù)目錄中。最后，啟動 MySQL 服務(wù)，數(shù)據(jù)庫即可恢復(fù)到備份時的狀態(tài)。?

如果需要基于時間點(diǎn)進(jìn)行恢復(fù)，可以結(jié)合二進(jìn)制日志文件。在進(jìn)行全量備份后，繼續(xù)備份二進(jìn)制日志文件。在恢復(fù)時，先進(jìn)行全量恢復(fù)，然后通過二進(jìn)制日志文件將數(shù)據(jù)庫恢復(fù)到指定的時間點(diǎn)，實(shí)現(xiàn)更精確的數(shù)據(jù)恢復(fù)。?

總之，MySQL 數(shù)據(jù)庫備份是保障數(shù)據(jù)安全的重要環(huán)節(jié)，通過了解不同的備份類型、方法和恢復(fù)操作，以及制定合理的備份策略，企業(yè)可以有效地保護(hù)數(shù)據(jù)，確保業(yè)務(wù)的連續(xù)性和穩(wěn)定性。同時，合理運(yùn)用 GTID 和 XtraBackup 等工具，可以進(jìn)一步提高備份和恢復(fù)的效率與可靠性。

全量備份：執(zhí)行以下命令進(jìn)行全量備份

xtrabackup --defaults-file=/etc/my.cnf --user=root --password=123456 --backup --target-dir=/root/backup/full

模擬數(shù)據(jù)變更：在數(shù)據(jù)庫中創(chuàng)建一個測試表并插入一些數(shù)據(jù)

CREATE TABLE test_table (id INT PRIMARY KEY, name VARCHAR(20));
INSERT INTO test_table VALUES (1, 'John'),(2, 'Alice');

增量備份：執(zhí)行以下命令進(jìn)行增量備份

xtrabackup --defaults-file=/etc/my.cnf --user=root --password=123456 --incremental --incremental-basedir=/root/backup/full --target-dir=/root/backup/incr1

再次模擬數(shù)據(jù)變更：向測試表中插入新的數(shù)據(jù)。

INSERT INTO test_table VALUES (3, 'Bob');

再次增量備份：執(zhí)行以下命令進(jìn)行第二次增量備份

xtrabackup --defaults-file=/etc/my.cnf --user=root --password=123456 --incremental --incremental-basedir=/root/backup/incr1 --target-dir=/root/backup/incr2

模擬數(shù)據(jù)丟失：誤刪除測試表

DROP TABLE test_table;

恢復(fù)操作

停止數(shù)據(jù)庫服務(wù)執(zhí)行systemctl stop mysqld命令

備份當(dāng)前數(shù)據(jù)目錄和 binlog 文件：將當(dāng)前的數(shù)據(jù)目錄進(jìn)行備份，執(zhí)行mv /var/lib/mysql /var/lib/mysql.bak命令。同時，備份 binlog 文件，使用cp /var/lib/mysql/mysql - bin.* /path/to/binlog_backup/命令，將 binlog 文件復(fù)制到指定的備份目錄

準(zhǔn)備全量備份：執(zhí)行以下命令準(zhǔn)備全量備份

xtrabackup --prepare --apply-log-only --target-dir=/root/backup/full

應(yīng)用增量備份：按照順序應(yīng)用增量備份。先應(yīng)用第一次增量備份：

xtrabackup --prepare --apply-log-only --target-dir=/root/backup/full --incremental-dir=/root/backup/incr1

再應(yīng)用第二次增量備份（最后一個增量備份不需要--apply - log - only參數(shù)）

xtrabackup --prepare --target-dir=/root/backup/full --incremental-dir=/root/backup/incr2

將數(shù)據(jù)恢復(fù)到 MySQL 數(shù)據(jù)目錄：執(zhí)行以下命令將恢復(fù)后的數(shù)據(jù)復(fù)制回 MySQL 的數(shù)據(jù)目錄

xtrabackup --copy-back --target-dir=/root/backup/full

執(zhí)行systemctl start mysqld命令啟動 MySQL 服務(wù)，查詢測試表，確認(rèn)數(shù)據(jù)是否恢復(fù)成功。

總結(jié)

以上為個人經(jīng)驗(yàn)，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論