mysql全量之增量備份與恢復(fù)方式
mysql數(shù)據(jù)庫備份
在當(dāng)今數(shù)據(jù)驅(qū)動的時代,數(shù)據(jù)庫作為企業(yè)和個人數(shù)據(jù)存儲與管理的核心,其安全性至關(guān)重要。數(shù)據(jù)一旦丟失,可能會給企業(yè)帶來巨大的經(jīng)濟損失和聲譽損害,因此,數(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ù)運營、客戶信息、交易記錄等關(guān)鍵內(nèi)容。數(shù)據(jù)備份是應(yīng)對數(shù)據(jù)丟失風(fēng)險的重要手段,它能夠在數(shù)據(jù)庫遭遇硬件故障、軟件錯誤、人為誤操作、惡意攻擊(如勒索軟件)等情況時,快速恢復(fù)數(shù)據(jù),確保業(yè)務(wù)的連續(xù)性。
例如,一家電商企業(yè)如果因為數(shù)據(jù)庫崩潰而丟失了用戶訂單和支付記錄,不僅會導(dǎo)致客戶不滿,還可能面臨法律糾紛和經(jīng)濟賠償。而定期進行數(shù)據(jù)備份,就可以在災(zāi)難發(fā)生后,將數(shù)據(jù)庫恢復(fù)到正常狀態(tài),減少損失。
數(shù)據(jù)庫備份類型
從物理與邏輯角度分類
- 物理備份:物理備份是對數(shù)據(jù)庫文件(如數(shù)據(jù)文件、日志文件等)的直接復(fù)制。這種備份方式直接操作數(shù)據(jù)庫的物理文件,備份速度快,恢復(fù)時也能快速還原數(shù)據(jù),因為它直接將文件復(fù)制回原來的位置。但它與特定的數(shù)據(jù)庫管理系統(tǒng)和操作系統(tǒng)相關(guān),移植性較差。?
- 邏輯備份:邏輯備份是將數(shù)據(jù)庫中的數(shù)據(jù)以 SQL 語句或特定格式導(dǎo)出,例如使用mysqldump工具生成的 SQL 文件。邏輯備份文件可讀性強,可以方便地查看和編輯其中的數(shù)據(jù),并且具有較好的移植性,可以在不同的數(shù)據(jù)庫環(huán)境中導(dǎo)入。但邏輯備份和恢復(fù)的速度相對較慢,因為它需要解析和執(zhí)行 SQL 語句。
從數(shù)據(jù)庫的備份策略角度分類
- 完全備份:完全備份是對整個數(shù)據(jù)庫進行完整的備份,包含了數(shù)據(jù)庫中的所有數(shù)據(jù)和對象。這種備份方式可以獨立進行恢復(fù),恢復(fù)過程簡單直接。但由于每次都要備份全部數(shù)據(jù),備份所需時間長,占用存儲空間大。?
- 增量備份:增量備份只備份自上次備份(可以是完全備份或上一次增量備份)以來發(fā)生變化的數(shù)據(jù)。增量備份的優(yōu)點是備份速度快,占用空間小,因為它只處理變化的數(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ù)庫可用性要求不高的場景。在進行物理冷備份前,需要確保數(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è)置各種選項,如添加鎖表、忽略錯誤等。它適用于各種 MySQL 版本,使用廣泛。?mysqlhotcopy
:mysqlhotcopy是一個快速的物理備份工具,它只能在數(shù)據(jù)庫運行在 MyISAM 存儲引擎時使用。mysqlhotcopy通過復(fù)制數(shù)據(jù)庫文件來實現(xiàn)備份,并且可以在數(shù)據(jù)庫運行時進行備份,不會阻塞數(shù)據(jù)庫的讀寫操作。但由于其對存儲引擎的限制,使用場景相對較窄。
通過啟用二進制日志進行增量備份
MySQL 的二進制日志記錄了數(shù)據(jù)庫的所有更改操作,啟用二進制日志后,可以利用它進行增量備份。通過定期備份二進制日志文件,并結(jié)合完全備份,可以實現(xiàn)基于時間點的恢復(fù)。
這種方式能夠在數(shù)據(jù)丟失時,將數(shù)據(jù)庫恢復(fù)到指定的時間點,最大程度地減少數(shù)據(jù)損失。
通過第三方工具備份
除了 MySQL 自帶的備份工具,還有許多第三方備份工具可供選擇,如 XtraBackup、Backup,Exec 等。
這些工具通常提供了更強大的功能,如圖形化界面、自動化備份策略、數(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"選項是用于指定連接 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ù)
增量備份的特點
增量備份只備份變化的數(shù)據(jù),因此備份速度快,占用空間小,適合對數(shù)據(jù)變化頻繁的數(shù)據(jù)庫進行備份。但增量備份的恢復(fù)過程依賴于之前的備份文件,需要按照順序依次應(yīng)用多個備份文件,增加了恢復(fù)的復(fù)雜性和時間成本。
mysql二進制日志對備份的意義
MySQL 的二進制日志記錄了數(shù)據(jù)庫的所有更改操作,包括數(shù)據(jù)的插入、更新、刪除等。通過分析二進制日志文件,可以獲取到數(shù)據(jù)庫在某個時間段內(nèi)的所有變化,從而實現(xiàn)基于時間點的恢復(fù)。在進行增量備份時,結(jié)合二進制日志文件,可以確保備份數(shù)據(jù)的完整性和一致性。
列:log-bin=/usr/local/mysql/mysql-bin,然后重啟MySQL服務(wù)就可以在指定路徑下查看二進制日志文件了。默認(rèn)情況下,二進制日志文件的擴展又是一個六位的數(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ù)
在進行增量恢復(fù)時,首先需要恢復(fù)上次的完全備份,然后依次應(yīng)用后續(xù)的增量備份文件和二進制日志文件。
例如,先恢復(fù)完全備份文件,然后按照時間順序應(yīng)用增量備份 1、增量備份 2,最后通過二進制日志文件將數(shù)據(jù)庫恢復(fù)到指定的時間點。
msyql企業(yè)備份案例
一般恢復(fù)
假設(shè)企業(yè)的數(shù)據(jù)庫因為硬件故障導(dǎo)致數(shù)據(jù)丟失,首先使用上次的完全備份文件進行恢復(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ù)異常,此時可以通過二進制日志文件定位到該 SQL 語句執(zhí)行的位置,然后從上次完全備份開始,應(yīng)用備份文件和二進制日志文件直到該位置,實現(xiàn)基于位置的恢復(fù),避免錯誤操作帶來的影響擴散。
- 格式(恢復(fù)到指定位置)mysqlbinlog --stop-position='操作 id'二進制日志|mysql-u用月戶名-p 密碼
- 格式(從指定位置開始恢復(fù)) mysqlbinlog--start-position='操作 id'二進制日志|mysql-u用戶名-p密碼
基于時間點恢復(fù)
當(dāng)企業(yè)需要將數(shù)據(jù)庫恢復(fù)到某個特定的時間點時,如誤刪除數(shù)據(jù)前的時刻,可以利用二進制日志文件和備份文件實現(xiàn)基于時間點的恢復(fù)。
首先恢復(fù)完全備份,然后根據(jù)時間戳應(yīng)用增量備份和二進制日志文件,將數(shù)據(jù)庫恢復(fù)到指定的時間點,最大程度地減少數(shù)據(jù)丟失。
- 格式(從日志開頭截止到某個時間點的恢復(fù))
- mysqlbinlog [--no-defaults] --stop-datetime='年-月-日小時:分鐘:私進制日志|mysql-u用戶名-p密碼
- 格式(從某個時間點到日志結(jié)尾的恢復(fù))
- mysqlbinlog [--no-defaults] --start-datetime='年-月-日小時:分鐘:秒二進制日志|mysql-u用戶名-p密碼
- 格式(從某個時間點到某個時間點的恢復(fù))
- mysqlbinlog [--no-defaults] --start-datetime='年-月-日小時:分鐘:秒--stop-datetime='年-月-日小時:分鐘:秒'二進制日志|mysql-u用戶名-p密碼
制定企業(yè)備份策略的思路
制定企業(yè)備份策略需要綜合考慮多個因素,包括數(shù)據(jù)的重要性、業(yè)務(wù)的連續(xù)性要求、備份和恢復(fù)的時間成本、存儲成本等。對于關(guān)鍵業(yè)務(wù)數(shù)據(jù)庫,應(yīng)采用高頻次的完全備份和增量備份相結(jié)合的方式,以確保數(shù)據(jù)的安全性和快速恢復(fù)能力。同時,要定期測試備份的有效性和恢復(fù)過程,避免在實際需要恢復(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)更新時進行備份。
- 在數(shù)據(jù)庫壓力小的時間段進行備份,如一周一次完全備份每天進行增量備份。
- 中小公司,完全備份一般一天一次即可。
- 大公司可每周進行一次完全備份,每天進行一次增量備份
- 盡量為企業(yè)實現(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并進行恢復(fù)全量等操作
在 MySQL 配置文件中啟用 GTID,添加配置項gtid_mode=ON和enforce_gtid_consistency=ON。啟用 GTID 后,在進行全量恢復(fù)時,可以更方便地確定事務(wù)的執(zhí)行順序,確?;謴?fù)的數(shù)據(jù)一致性。例如,在從庫故障后恢復(fù)時,通過 GTID 可以快速定位到主庫上的事務(wù)位置,從正確的位置開始復(fù)制數(shù)據(jù),提高恢復(fù)效率和準(zhǔ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ù)庫運行時進行物理備份,支持 InnoDB 和 XtraDB 存儲引擎。以下是一個 XtraBackup 應(yīng)用在恢復(fù)操作中的案例:?
TypeScript
取消自動換行復(fù)制
innobackupex --apply-log /backup/full_backup? innobackupex --copy-back /backup/full_backup?
--apply-log選項用于準(zhǔn)備備份數(shù)據(jù),使其可以用于恢復(fù);--copy-back選項用于將備份數(shù)據(jù)復(fù)制到數(shù)據(jù)庫的數(shù)據(jù)目錄中。最后,啟動 MySQL 服務(wù),數(shù)據(jù)庫即可恢復(fù)到備份時的狀態(tài)。?
如果需要基于時間點進行恢復(fù),可以結(jié)合二進制日志文件。在進行全量備份后,繼續(xù)備份二進制日志文件。在恢復(fù)時,先進行全量恢復(fù),然后通過二進制日志文件將數(shù)據(jù)庫恢復(fù)到指定的時間點,實現(xiàn)更精確的數(shù)據(jù)恢復(fù)。?
總之,MySQL 數(shù)據(jù)庫備份是保障數(shù)據(jù)安全的重要環(huán)節(jié),通過了解不同的備份類型、方法和恢復(fù)操作,以及制定合理的備份策略,企業(yè)可以有效地保護數(shù)據(jù),確保業(yè)務(wù)的連續(xù)性和穩(wěn)定性。同時,合理運用 GTID 和 XtraBackup 等工具,可以進一步提高備份和恢復(fù)的效率與可靠性。
全量備份:執(zhí)行以下命令進行全量備份
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í)行以下命令進行增量備份
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í)行以下命令進行第二次增量備份
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ù)目錄進行備份,執(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)驗,希望能給大家一個參考,也希望大家多多支持腳本之家。
相關(guān)文章
Mysql創(chuàng)建視圖中文亂碼如何修改docker里的配置
這篇文章主要介紹了Mysql創(chuàng)建視圖中文亂碼如何修改docker里的配置,本文給大家介紹的非常詳細(xì),對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友參考下吧2023-10-10對于mysql的query_cache認(rèn)識的誤區(qū)
一直以來,對于mysql的query_cache,在網(wǎng)上就流行著這樣的說法,對于mysql的query_cache鍵值就是mysql的query,所以,如果在query中有任何的不同,包括多了個空格,都會導(dǎo)致mysql認(rèn)為是不同的查詢2012-03-03SQL查詢之字段是逗號分隔開的數(shù)組如何查詢匹配數(shù)據(jù)問題
這篇文章主要介紹了SQL查詢之字段是逗號分隔開的數(shù)組如何查詢匹配數(shù)據(jù)問題,具有很好的參考價值,希望對大家有所幫助。如有錯誤或未考慮完全的地方,望不吝賜教2023-03-03windows10系統(tǒng)安裝mysql-8.0.13(zip安裝) 的教程詳解
這篇文章主要介紹了windows10安裝mysql-8.0.13(zip安裝) 的教程,非常不錯,具有一定的參考借鑒價值,需要的朋友可以參考下2018-11-11