MySQL技術筆記-備份與恢復完全指南

目錄

前言

一、備份概述

（一）備份方式

（二）備份策略

二、物理備份及恢復

（一）備份操作

（二）恢復操作

三、邏輯備份及恢復

（一）邏輯備份

（二）邏輯恢復

（三）mysqldump局限性

四、環境準備

（一）軟件介紹?編輯

（二）安裝軟件

六、增量備份與恢復

（一）增量備份

首次完全備份

增量備份

（二）增量恢復

準備全量備份?

合并增量備份

恢復數據

七、差異備份與恢復

（一）差異備份

首次完全備份

差異備份

（二）差異恢復

準備全量備份

合并差異備份

恢復數據

八、數據實時備份與恢復

（一）binlog日志管理

日志介紹

啟用bin-log

創建新日志文件

?日志文件常用管理命令

（二）備份數據

備份單個庫

備份所有庫

（三）恢復數據

恢復所有數據

恢復部分數據

（四）注意事項

前言

  • 如果有需要了解MySQL的基礎使用，如查詢和表管理的可以參考MySQL技術筆記-從基礎配置到高級查詢的全棧實戰指南、MySQL技術筆記-表結構設計與管理的完整指南

一、備份概述

（一）備份方式

  • 按照數據庫服務狀態分類
類型說明優點缺點
冷備份在數據庫服務停止狀態下進行備份，直接復制數據文件數據一致性強，備份過程簡單服務中斷，影響業務連續性
熱備份在數據庫服務運行狀態下進行備份，無需停機不影響業務運行，適合高可用場景數據可能存在短暫不一致，備份復雜度較高
溫備份在數據庫運行狀態下進行的備份操作，但備份過程中會短暫阻塞數據庫的寫操作，以保證數據一致性（mysqldump）無需停止數據庫服務，通過短暫阻塞寫操作，確保備份數據的一致性，適用于數據一致性要求較高、寫入壓力適中的業務系統寫入壓力較大的業務可能因阻塞而產生延遲
  • 按照備份的數據分類
類型說明優點缺點
物理備份直接復制數據庫的物理文件（如?.ibd、.frm?文件）恢復速度快，適合大數據量跨平臺兼容性差，備份文件體積大
邏輯備份導出數據庫中的數據為 SQL 腳本（如?mysqldump）跨平臺兼容性好，適合數據遷移備份和恢復速度較慢，依賴 SQL 解析

（二）備份策略

  • 備份系統的三要素
要素全稱含義說明
BWBackup Window（備份窗口）在一個工作周期內，系統可用于執行備份操作的時間范圍備份窗口越短，對業務影響越小
RPORecovery Point Objective（恢復點目標）在發生故障時，企業可接受的最大數據丟失量RPO 越小，數據丟失越少
RTORecovery Time Objective（恢復時間目標）在發生故障時，企業可接受的最長系統恢復時間RTO 越短，業務中斷時間越短
  • 災備系統建設國際標準（SHARE 78）
級別描述恢復能力適用場景
0無備份，無恢復能力無法恢復不建議
1本地磁帶備份恢復時間長小型企業
2遠程磁帶備份恢復時間較短中型企業
3遠程磁盤備份恢復時間更短大型企業
4遠程熱備中心快速恢復高可用需求
5實時數據復制數據幾乎無丟失金融、電商等關鍵業務
6實時復制 + 完整IT基礎設施快速切換數據中心級容災
7實時復制 + 完整IT基礎設施 + 應用零中斷最高級別容災

二、物理備份及恢復

（一）備份操作

  • 使用cp命令備份 
    cp -r /var/lib/mysql /備份目錄/mysql.bak
  • 使用tar命令備份 
    tar -zcf /root/mysql.tar.gz /var/lib/mysql/*

（二）恢復操作

  • 使用cp命令恢復 
    cp -r 備份目錄/mysql.bak /var/lib/mysql/
  • 使用tar命令恢復 
    tar -zxf /root/mysql.tar.gz -C /var/lib/mysql/
  • 修改文件所有權 
    chown -R mysql:mysql /var/lib/mysql  # 確保MySQL對目錄和目錄下的文件有讀權限

三、邏輯備份及恢復

（一）邏輯備份

  • 備份操作 
    mysqldump -uroot -p密碼 庫名 > /目錄/xxx.sql
  • 庫名表示方式：
    • 庫名 表名列表：備份1張表或多張表所有數據。
    • -B 庫名列表：備份1個庫或多個庫所有數據。
    • --all-databases或-A：備份1臺服務器所有數據。
  • 示例： 
    mysqldump -uroot -ptest -A > /bakdir/all_backup.sql

（二）邏輯恢復

  • 恢復操作 
    mysql -uroot -p密碼 [庫名] < /目錄/名.sql
  • 示例： 
    mysqldump -uroot -pa test users > /test_users.sql
mysqldump -uroot -pa -B test > /test.sql
mysql -uroot -pa test < /test_users.sql
mysql -uroot -pa < /test.sql
  • 注意：
    • 恢復庫時庫名可以省略。
    • 備份和恢復時，數據庫服務必須是運行狀態。
    • 覆蓋恢復數據時需謹慎操作，確保數據安全。

（三）mysqldump局限性

  • 物理備份缺點：
    • 跨平臺性差。
    • 備份時間長、冗余備份、浪費存儲空間。
    • ?物理備份通常只能恢復整個數據庫實例，無法單獨恢復某個庫或表，靈活性不如邏輯備份。
  • mysqldump備份缺點：
    • ?恢復效率低，導入大數據量時耗時較長?。
    • 備份速度較慢，尤其在大數據量場景下性能不佳??。
    • 鎖表，備份過程中數據插入和更新操作被阻塞。
    • ?不支持增量備份，僅支持全量邏輯備份?。
    • 對于大規模數據庫，備份文件體積較大，占用存儲資源?。
    • ?備份過程中可能對數據庫性能產生影響?。

四、環境準備

（一）軟件介紹

  • Percona XtraBackup：一款強大的在線熱備份工具，備份過程中不鎖庫表，適合生產環境。由專業組織Percona提供（改進MySQL分支）。

（二）安裝軟件

  • 安裝依賴 
    yum -y install perl-DBD-MySQL
  • 解壓軟件 
    tar -xf percona-xtrabackup-8.0.26-18-Linux-x86_64.glibc2.12-minimal.tar.gz
mv percona-xtrabackup-8.0.26-18-Linux-x86_64.glibc2.12 /usr/local/percona
  • 配置環境變量 
    echo 'export PATH=/usr/local/percona/bin:$PATH' >> /etc/bashrc
source /etc/bashrc

五、增量備份與恢復

（一）增量備份

首次完全備份

xtrabackup --host=127.0.0.1 --user=用戶名 --password=密碼 --backup --target-dir=備份目錄 --datadir=數據庫目錄
#完全備份就是每次進行備份都只執行該操作
  • 示例：

xtrabackup --host=127.0.0.1 --user=root --password=test --backup --target-dir=/basement --datadir=/var/lib/mysql

增量備份

xtrabackup --host=127.0.0.1 --user=用戶名 --password=密碼 --backup --target-dir=備份目錄 --incremental-basedir=參考目錄 --datadir=數據庫目錄
  • 示例： 
    xtrabackup --host=127.0.0.1 --user=root --password=test --backup --target-dir=/inc1 --incremental-basedir=/basement --datadir=/var/lib/mysql

（二）增量恢復

準備全量備份?

xtrabackup --prepare --apply-log-only --target-dir=/完全備份目錄 
#--apply-log-only 參數，確保未提交的事務不被回滾，以便后續合并增量備份。
  • 示例：
xtrabackup --prepare --apply-log-only --target-dir=/basement

合并增量備份

xtrabackup --prepare --apply-log-only \
--target-dir=/完全備份目錄 \
--incremental-dir=/增量備份目錄
#將增量備份的數據合并到全量備份目錄中，確保數據一致性。
#如果有多個增量備份需要將每個增量備份按照先后順序和完全備份進行合并
  • 示例：
xtrabackup --prepare --apply-log-only \
--target-dir=/basement \
--incremental-dir=/inc1

恢復數據

xtrabackup --copy-back --target-dir=/完全備份目錄
#--copy-back 將準備好的備份數據復制回 MySQL 的數據目錄。
  • 示例：
xtrabackup --copy-back --target-dir=/basement
  • 注意事項：
    • 執行恢復前，務必清空 MySQL 數據目錄（如 /var/lib/mysql），避免數據沖突。
    • 恢復完成后，需修改數據目錄權限為 mysql:mysql，并重啟 MySQL 服務。 
      chown -R mysql:mysql /var/lib/mysql
systemctl restart mysqld

六、差異備份與恢復

（一）差異備份

首次完全備份

xtrabackup --host=127.0.0.1 --user=用戶名 --password=密碼 --backup --target-dir=/備份目錄 --datadir=/數據庫目錄
  • 示例： 
    xtrabackup --host=127.0.0.1 --user=root --password=test --backup --target-dir=/fullbackup --datadir=/var/lib/mysql

差異備份

xtrabackup --host=127.0.0.1 --user=用戶名 --password=密碼 --backup --target-dir=/備份目錄 --incremental-basedir=/完全備份目錄 --datadir=/數據庫目錄
#每次差異備份都是以上次全量或差異備份為基礎
  • 示例： 
    xtrabackup --host=127.0.0.1 --user=root --password=test --backup --target-dir=/diff_1 --incremental-basedir=/fullbackup --datadir=/var/lib/mysql

（二）差異恢復

準備全量備份

xtrabackup --prepare --apply-log-only --target-dir=/完全備份目錄
  • 示例： 
    xtrabackup --prepare --apply-log-only --target-dir=/fullbackup

合并差異備份

xtrabackup --prepare --apply-log-only --target-dir=/完全備份目錄 --incremental-dir=/最后一次差異備份
#只要和最后一次差異備份合并即可，因為最后一次差異備份包括之前所有差異備份的數據
  • 示例： 
    xtrabackup --prepare --apply-log-only \
--target-dir=/fullbackup \
--incremental-dir=/diff_1

恢復數據

xtrabackup --copy-back --target-dir=/完全備份目錄
  • 示例： 
    xtrabackup --copy-back --target-dir=/fullbackup
  • 注意事項：
    • 執行恢復前，務必清空 MySQL 數據目錄（如 /var/lib/mysql），避免數據沖突。
    • 恢復完成后，需修改數據目錄權限為 mysql:mysql，并重啟 MySQL 服務。 
      chown -R mysql:mysql /var/lib/mysql
systemctl restart mysqld

七、數據實時備份與恢復

（一）binlog日志管理

日志介紹

  • 什么是binlog日志：
    • 又稱“二進制日志”，是MySQL服務生成的日志文件之一。
    • 記錄除查詢語句（SELECT、SHOW等）以外的所有SQL命令，包括INSERT、UPDATE、DELETE、CREATE、ALTER、DROP等。
    • 主要用于數據恢復、數據復制以及主從同步。
    • 啟用binlog是實現MySQL主從復制的必要條件。

啟用bin-log

  • 日志默認啟用：日志默認存儲在/var/lib/mysql/目錄下。
  • 自定義日志
  • 示例： 
    mkdir /mylog            # 創建目錄
vim /etc/my.cnf.d/mysql-server.cnf  #修改配置
#添加
[mysqld]
log-bin=/mylog/mysql-binchown mysql:mysql /mylog  # 修改目錄的所有者為mysql
setenforce 0            # 關閉selinux
systemctl restart mysqld  # 重啟服務

創建新日志文件

  • 默認容量：Binlog日志文件默認1G容量，超出后會自動創建新日志。
  • 手動創建： 
    FLUSH LOGS;
或者
systemctl restart mysqld

?日志文件常用管理命令

  • 查看正在使用的日志 
    SHOW MASTER STATUS;
  • 查看已有的日志文件 
    SHOW BINARY LOGS;
  • 刪除編號之前的日志 
    PURGE MASTER LOGS TO "日志名";
  • 示例： 
    PURGE MASTER LOGS TO "mysql-bin.000004";
  • 刪除所有日志，重新創建日志 
    RESET MASTER;
  • 查看日志文件內容 
    SHOW BINLOG EVENTS IN "日志文件名";
  • ?示例： 
    SHOW BINLOG EVENTS IN "mysql-000004";

（二）備份數據

備份單個庫

mysqldump -uroot -p密碼 --flush-logs -B test > /single.sql

備份所有庫

mysqldump -uroot -p密碼 --flush-logs -A > /all.sql

（三）恢復數據

恢復所有數據

mysqlbinlog /目錄/文件名 | mysql -uroot -p密碼 #查看文件全部內容并通過管道導入數據庫中
  • 示例： 
    mysqlbinlog /mylog/mysql-bin.000001 | mysql -uroot -ptest

恢復部分數據

mysqlbinlog --start-datetime="開始時間" --stop-datetime="結束時間" /目錄/文件名 | mysql -uroot -p密碼
  • 示例： 
    mysqlbinlog --start-datetime="2025-06-01 00:00:00" --stop-datetime="2025-07-10 23:59:59" /mylog/mysql-bin.000001 | mysql -uroot -ptest

（四）注意事項

  • 備份時：
    • 確保備份目錄有足夠的空間。
    • 定期檢查備份文件的完整性和可用性。
  • 恢復時：
    • 確保恢復操作不會影響生產環境。
    • 在恢復前備份當前數據庫狀態，以防恢復失敗。
    • 確保恢復后的數據一致性。

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/news/914374.shtml
繁體地址，請注明出處：http://hk.pswp.cn/news/914374.shtml
英文地址，請注明出處：http://en.pswp.cn/news/914374.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！

相關文章

SpringBoot或OpenFeign中 Jackson 配置參數名蛇形、小駝峰、大駝峰、自定義命名

SpringBoot或OpenFeign中 Jackson 配置參數名蛇形、小駝峰、大駝峰、自定義命名

SpringBoot或OpenFeign中 Jackson 配置參數名蛇形、小駝峰、大駝峰、自定義命名 前言 在調用外部接口時&#xff0c;對方給出的接口文檔中&#xff0c;入參參數名一會大寫加下劃線&#xff0c;一會又是駝峰命名。 示例如下&#xff1a; {"MOF_DIV_CODE": "xx…
閱讀更多...
uni-app 途徑站點組件開發與實現分享

uni-app 途徑站點組件開發與實現分享

在移動應用開發中&#xff0c;涉及到出行、物流等場景時&#xff0c;途徑站點的展示是一個常見的需求。本文將為大家分享一個基于 uni-app 開發的途徑站點組件&#xff0c;該組件能夠清晰展示路線中的各個站點信息&#xff0c;包括站點名稱、到達時間、是否已到達等狀態&#x…
閱讀更多...
kotlin中集合的用法

kotlin中集合的用法

從一個實際應用看起以下kotlin中代碼語法正確嗎 var testBeanAIP0200()var testList:List<AIP0200> ArrayList()testList.add(testBean)這段Kotlin代碼存在語法錯誤&#xff0c;主要問題在于&#xff1a;List<AIP0200> 是Kotlin中的不可變集合接口&#xff0c;不能…
閱讀更多...
深入理解 Java Map 與 Set

深入理解 Java Map 與 Set

文章目錄前言1. 搜索樹1.1 什么是搜索樹1.2 查找1.3 插入1.4 刪除情況一&#xff1a;cur 沒有子節點&#xff08;即為葉子節點&#xff09;情況二&#xff1a;cur 只有一個子節點&#xff08;只有左子樹或右子樹&#xff09;情況三&#xff1a;cur 有兩個子節點&#xff08;左右…
閱讀更多...
excel如何只保留前幾行

excel如何只保留前幾行

方法一&#xff1a;手動刪除多余行 選中你想保留的最后一行的下一行&#xff08;比如你只保留前10行&#xff0c;那選第11行&#xff09;。按住 Shift Ctrl ↓&#xff08;Windows&#xff09;或 Shift Command ↓&#xff08;Mac&#xff09;&#xff0c;選中從第11行到最…
閱讀更多...
實時連接，精準監控：風丘科技數據遠程顯示方案提升試驗車隊管理效率

實時連接，精準監控：風丘科技數據遠程顯示方案提升試驗車隊管理效率

風丘科技推出的數據遠程實時顯示方案更好地滿足了客戶對于試驗車隊遠程實時監控的需求&#xff0c;并真正實現了試驗車隊的遠程管理。隨著新的數據記錄儀軟件IPEmotion RT和相應的跨平臺顯示解決方案的引入&#xff0c;讓我們的客戶端不僅可在線訪問記錄器系統狀態&#xff0c;…
閱讀更多...
灰盒級SOA測試工具Parasoft SOAtest重新定義端到端測試

灰盒級SOA測試工具Parasoft SOAtest重新定義端到端測試

還在為脆弱的測試環境、強外部依賴和低效的測試復用拖慢交付而頭疼&#xff1f;尤其在銀行、醫療、制造等關鍵領域&#xff0c;傳統的端到端測試常因環境不穩、接口難模擬、用例難共享而舉步維艱。 灰盒級SOA測試工具Parasoft SOAtest以可視化編排簡化復雜測試流程&#xff0c…
閱讀更多...
OKHttp 核心知識點詳解

OKHttp 核心知識點詳解

OKHttp 核心知識點詳解 一、基本概念與架構 1. OKHttp 簡介 類型&#xff1a;高效的HTTP客戶端特點&#xff1a; 支持HTTP/2和SPDY&#xff08;多路復用&#xff09;連接池減少請求延遲透明的GZIP壓縮響應緩存自動恢復網絡故障2. 核心組件組件功能OkHttpClient客戶端入口&#…
閱讀更多...
從“被動巡檢”到“主動預警”：塔能物聯運維平臺重構路燈管理模式

從“被動巡檢”到“主動預警”：塔能物聯運維平臺重構路燈管理模式

從以往的‘被動巡檢’轉變至如今的‘主動預警’&#xff0c;塔能物聯運維平臺對路燈管理模式展開了重新構建。城市路燈屬于極為重要的市政基礎設施范疇&#xff0c;它的實際運行狀態和市民出行安全以及城市形象有著直接且緊密的關聯。不過呢&#xff0c;傳統的路燈管理模式當下…
閱讀更多...
10. 常見的 http 狀態碼有哪些

10. 常見的 http 狀態碼有哪些

總結 1xx: 正在處理2xx: 成功3xx: 重定向&#xff0c;302 重定向&#xff0c;304 協商緩存4xx: 客戶端錯誤&#xff0c;401 未登錄&#xff0c;403 沒權限&#xff0c;404 資源不存在5xx: 服務器錯誤常見的 HTTP 狀態碼詳解 HTTP 狀態碼&#xff08;HTTP Status Code&#xff0…
閱讀更多...
springBoot對接第三方系統

springBoot對接第三方系統

yml文件 yun:ip: port: username: password: controller package com.ruoyi.web.controller.materials;import com.ruoyi.common.core.controller.BaseController; import com.ruoyi.common.core.domain.AjaxResult; import com.ruoyi.materials.service.IYunService; import o…
閱讀更多...
【PTA數據結構 | C語言版】車廂重排

【PTA數據結構 | C語言版】車廂重排

本專欄持續輸出數據結構題目集&#xff0c;歡迎訂閱。 文章目錄題目代碼題目 一列掛有 n 節車廂&#xff08;編號從 1 到 n&#xff09;的貨運列車途徑 n 個車站&#xff0c;計劃在行車途中將各節車廂停放在不同的車站。假設 n 個車站的編號從 1 到 n&#xff0c;貨運列車按照…
閱讀更多...
量子計算能為我們做什么？

量子計算能為我們做什么？

科技公司正斥資數十億美元投入量子計算領域&#xff0c;盡管這項技術距離實際應用還有數年時間。那么&#xff0c;未來的量子計算機將用于哪些方面&#xff1f;為何眾多專家堅信它們會帶來顛覆性變革&#xff1f; 自 20 世紀 80 年代起&#xff0c;打造一臺利用量子力學獨特性質…
閱讀更多...
BKD 樹（Block KD-Tree）Lucene

BKD 樹（Block KD-Tree）Lucene

BKD 樹&#xff08;Block KD-Tree&#xff09;是 Lucene 用來存儲和快速查詢 **多維數值型數據** 的一種磁盤友好型數據結構&#xff0c;可以把它想成&#xff1a;> **“把 KD-Tree 分塊壓縮后落到磁盤上&#xff0c;既能做磁盤順序讀&#xff0c;又能像內存 KD-Tree 一樣做…
閱讀更多...
【Mysql作業】

【Mysql作業】

第一次作業要求1.首先打開Windows PowerShell2.連接到MYSQL服務器3.執行以下SQL語句&#xff1a;-- 創建數據庫 CREATE DATABASE mydb6_product;-- 使用數據庫 USE mydb6_product;-- 創建employees表 CREATE TABLE employees (id INT PRIMARY KEY,name VARCHAR(50) NOT NULL,ag…
閱讀更多...
（C++）STL：list認識與使用全解析

（C++）STL：list認識與使用全解析

本篇基于https://cplusplus.com/reference/list/list/講解 認識 list是一個帶頭結點的雙向循環鏈表翻譯總結&#xff1a; 序列容器&#xff1a;list是一種序列容器&#xff0c;允許在序列的任何位置進行常數時間的插入和刪除操作。雙向迭代&#xff1a;list支持雙向迭代&#x…
閱讀更多...
Bash函數詳解

Bash函數詳解

目錄**1. 基礎函數****2. 參數處理函數****3. 文件操作函數****4. 日志與錯誤處理****5. 實用工具函數****6. 高級函數技巧****7. 常用函數庫示例****總結&#xff1a;Bash 函數核心要點**1. 基礎函數 1.1 定義與調用 可以自定義函數名稱&#xff0c;例如將greet改為yana。?…
閱讀更多...
Python爬蟲實戰：研究rows庫相關技術

Python爬蟲實戰：研究rows庫相關技術

1. 引言 在當今數字化時代，互聯網上存在著大量有價值的表格數據，這些數據以 HTML 表格、CSV、Excel 等多種格式存在。然而，由于數據源的多樣性和不規范性，表格結構往往存在復雜表頭、合并單元格、不規則數據行等問題，給數據的自動化處理帶來了巨大挑戰。 傳統的數據處理工…
閱讀更多...
通過同態加密實現可編程隱私和鏈上合規

通過同態加密實現可編程隱私和鏈上合規

1. 引言 2023年9月28日&#xff0c;a16z 的加密團隊發布了 Nakamoto Challenge&#xff0c;列出了區塊鏈中需要解決的最重要問題。尤其是其中的第四個問題格外引人注意&#xff1a;“合規的可編程隱私”&#xff0c;因為Zama團隊已經在這方面積極思考了一段時間。本文提出了使…
閱讀更多...
封裝---統一封裝處理頁面標題

封裝---統一封裝處理頁面標題

一.采用工具來實現(setPageTitle.ts)多個頁面中用更統一的方式設置 document.title&#xff0c;可以封裝一個工具函數:在utils目錄下新建文件:setPageTitle.ts如果要在每個頁面設置相同的網站標志可以使用下面的appNameconst appName: string import.meta.env.VITE_APP_NAMEex…
閱讀更多...
最新文章

Copyright @ 2022~2023