MySQL 數據備份

来源:https://www.cnblogs.com/heyongshen/archive/2023/12/07/17874375.html
-Advertisement-
Play Games

備份類型 常見的備份有冷備份、溫備份、熱備份，還有什麼物理備份、邏輯備份、增量備份、差異備份等等。 冷備份： 需要服務停止，在備份期間不能進行讀和寫操作。 溫備份： 讀操作可執行；但寫操作不可執行 熱備份： 讀和寫都可以正常進行，不影響數據備份 邏輯備份： 導出資料庫中的數據和對象定義為標準 SQL ...


備份類型

常見的備份有冷備份、溫備份、熱備份，還有什麼物理備份、邏輯備份、增量備份、差異備份等等。

冷備份： 需要服務停止，在備份期間不能進行讀和寫操作。

溫備份： 讀操作可執行；但寫操作不可執行

熱備份： 讀和寫都可以正常進行，不影響數據備份

邏輯備份： 導出資料庫中的數據和對象定義為標準 SQL 語句保存在文本文件中。這些 SQL 語句可以重新執行來恢複數據庫。

物理備份： 直接複製資料庫的文件，包括數據文件、索引文件和日誌文件等。它是對資料庫文件系統層面的直接拷貝。

MySQL 備份內容

用戶信息： 例如MySQL的賬號信息，賬號的許可權等。這些都是存放再mysql這個資料庫中的。

業務數據： 相關的業務資料庫

配置文件： MySQL服務的相關配置

MySQL 物理備份

一種方法是將mysql服務停止後，將整個數據目錄進行拷貝或者打包，例如利用tar、cp、rsync等工具

還有一種方法就是利用專門的物理備份工具，例如xtrabackup就是一個非常好用的物理備份工具。

xtrabackup 介紹

xtrabackup是 Percona 開發的一個資料庫物理備份工具，100%開源。主要用來執行MySQL、Percona Server 和 MariaDB 資料庫的備份和恢復操作。

官方文檔：https://docs.percona.com/percona-xtrabackup/innovation-release/

xtrabackup具有以下特點：

1、非常適合大型資料庫以及需要快速備份還原和需要支持災難級備份的場景。

2、支持增量備份、對備份數據進行壓縮、數據加密等功能

3、xtrabackup主要是用來操作innodb這種存儲引擎的數據，且整個過程是非阻塞的。在備份過程中，對資料庫的讀寫操作可以繼續進行，適合需要7*24小時運行的環境

4、正是因為xtrabackup是直接操作資料庫文件和日誌，所以對資料庫的內部結構（如數據字典、redo log 格式）高度敏感。當這些內部結構在資料庫的新版本中發生變化時，XtraBackup 需要更新以適應這些更改。並且高版本不能向下相容低版本。

xtrabackup 安裝

安裝的時候需要先確認需要備份的資料庫版本，然後再參考官方文檔下載合適的 xtrabackup 版本才行。上面也說了，MySQL不同版本的文件結構有差別，所以造成不能高版本相容低版本的問題。

例如：mysql的版本是5.7.x，那麼就需要下載 2.4版本的xtrabackup，8.0版本的xtrabackup不能備份mysql5.7。

xtrabackup 備份數據

備份整個資料庫

XtraBackup 是直接備份物理文件的工具，但是也需要連接到 MySQL。因為需要知道MySQL的一些配置（如 datadir、innodb_data_home_dir、innodb_log_group_home_dir 等），通過這些配置來確定需要備份的文件。

還有就是讀取二進位日誌位置來確保備份可以用於點恢復操作。連接MySQL的參數和mysql命令行工具是一樣的。

1、創建備份目錄

需要執行備份操作的過程中，具有對這個目錄的讀和寫許可權，所以可以直接以root用戶身份執行。

mkdir /home/ehigh/mysql_bak
2、執行備份操作

通過 --backup 選項可以執行全備份。

例如：

sudo xtrabackup --backup --host="192.168.15.12" --user="root" --password="eHIGH2014" --port=3306 --datadir="/var/lib/mysql" --target-dir="/home/ehigh/mysql_bak"
3、數據恢復前的準備

通過 --prepare 選項讓 xtrabackup完成一些數據恢復前的準備工作，因為執行物理備份時，XtraBackup 複製的是資料庫文件的實時狀態，包括那一刻的所有數據文件和日誌文件。

這個狀態下可能會有未完成的事務等等。通過這個操作可以將這些未完成的事務回滾。保證恢復時資料庫的一致性。

而且執行這個操作是不會影響到現有數據的，因為這個操作僅針對備份的數據進行處理，只會處理備份目錄中的備份文件，例如應用重做日誌和回滾未完成的事務。

sudo xtrabackup --prepare --target-dir="/home/ehigh/mysql_bak"
4、進行數據恢復

(1)停止服務。避免在恢複數據時發生數據損壞。

sudo systemctl stop mysql.service

(2)清空數據目錄。

sudo rm -rf /var/lib/mysql

mv /var/lib/mysql /var/lib/mysql-bak

mkdir /var/lib/mysql

chown -R mysql:mysql /var/lib/mysql

(3)執行數據恢復
執行數據恢復一種方法是直接使用cp或者rsync工具將備份目錄下的所有文件拷貝到mysql數據目錄中，還有種方法就是通過 --copy-back 或 --move-back 選項從備份目錄將數據恢復到原始 MySQL 數據目錄。

這兩個參數的區別如下：

  • --copy-back：將備份數據複製回 MySQL 的數據目錄。這個過程不會刪除或更改備份目錄中的原始備份文件

  • --move-back：將備份數據移動（而非複製）回 MySQL 的數據目錄，移動過後備份目錄中就不存在備份文件了

sudo xtrabackup --copy-back  --target-dir=/home/ehigh/mysql_bak  --datadir=/var/lib/mysql

（4）更改文件許可權
將MySQL數據目錄下的文件許可權改為mysql

sudo chown -R /var/lib/mysql

備份整個資料庫並壓縮數據

xtrabackup不同版本支持的壓縮演算法不同，具體信息可以看官方文檔確認。通過 --compress選項即可再備份數據的時候，將數據進行壓縮。

1、備份數據

例如：

sudo xtrabackup --backup --host="192.168.15.12" --user="root" --password="eHIGH2014" --port=3306 --compress --compress-threads=4   --datadir="/var/lib/mysql" --target-dir="/home/ehigh/mysql_bak_c"
2、恢複數據前先解壓縮

在備份數據的時候xtrabackup會通過自帶的一些壓縮演算法庫文件將文件進行壓縮，但是解壓的時候需要在系統上安裝對應的工具才行。

通過 --decompress 選項即可完成解壓

sudo xtrabackup --decompress --target-dir=/home/ehigh/mysql_bak_c
3、恢復前的準備

通過 --prepare 選項完成恢復前的準備，具體操作和上面一樣的

4、數據恢復

（1）停止MySQL服務

（2）清空MySQL數據目錄

（3）使用 xtrabackup 恢複數據

（4）修改MySQL數據目錄中文件的屬主和屬組

備份單個資料庫

XtraBackup 備份單個資料庫的操作比較特殊，因為 XtraBackup 主要設計用於備份整個 MySQL 伺服器的。備份單個庫用mysqldump就行了。

MySQL 邏輯備份

就是將資料庫中的數據備份為SQL語句，將這些SQL語句存放在一個文件中，恢複數據的時候直接執行這個文件裡面的SQL即可重現數據。

mysqldump

mysqldump是mysql服務自帶的一個工具，mysqldump可以跨平臺使用且使用簡單，非常適用於小到中等規模的資料庫。

預設情況下，mysqldump 工具將生成的 SQL 語句輸出到標準輸出（stdout）。如果直接運行 mysqldump 命令而不進行任何重定向，它會在命令行界面中顯示 SQL 語句。

備份整個資料庫

mysqldump -u 用戶名 -p 用戶密碼  database_name > backup.sql

通過這種方法備份整個資料庫時，會生成一個包含所有表結構和數據的 SQL 文件，但不會包含 CREATE DATABASE 語句。即恢複數據的時候需要手動將對應的資料庫創建好，然後再將數據導入到該資料庫中。

如果需要生成一個帶 CREATE DATABASE 語句 的sql文件，加一個 --databases 參數就可以了。

備份多個資料庫

如果需要一次性備份多個指定的資料庫，可以通過 mysqldump 的 --databases 選項來實現。還可以用 -B 參數，-B參數效果和 --databases 一樣的。

mysqldump -u user_name -p password --databases db1 db2 db3 > backup.sql

備分所有資料庫

如果想將所有資料庫進行備份的話，通過 --all-databases 選項可以實現，也可以用 -A 參數，效果是一樣的

mysqldump -u user_name -p password --all-databases > backup.sql

備份單個數據表

如果只想要備份單張表，只需要在資料庫的後面指定表名就行了。

mysqldump -u user_name -p password database_name table_name > backup.sql

備份多個數據表

在資料庫的後面指定多個表名就行了。

mysqldump -u user_name -p password database_name tb1 tb2 tb3 > backup.sql

只備份表結構，不備份數據

僅備份資料庫結構（例如，表定義、視圖、存儲過程等），但不包含任何實際數據，可以使用 --no-data 選項。

mysqldump -u user_name -p password --no-data database_name table_name > backup.sql

只備份數據，不備份表結構

只想備份數據而不包括表結構，可以使用 --no-create-info 選項

mysqldump -u user_name -p password --no-create-info database_name table_name > backup.sql

備份表的一部分數據

如果需要備份一個數據表中的部分數據，通過 -where 選項來指定符合特定條件的數據。

例如：備份id < 1000 的數據

mysqldump -u user_name -p password --databases db1 tables1  --where="id < 1000" > users_backup.sql

備份存儲過程和函數

使用 --routines 選項可以確保存儲過程和函數被包含在備份中，也可以使用簡寫的-R參數。

mysqldump -u user_name -p --no-create-info --no-data --no-create-db --routines --skip-triggers database_name > routines_backup.sql

打開備份的sql文件，我們可以看到 /*!50003 ... */ 這是MySQL的一種特殊註釋模式，稱為“條件註釋”或“版本控制註釋”。這些註釋內容實際上會被 MySQL 執行。

/*!50003 ，這裡的50003表示該命令需要的最低 MySQL 版本為 5.00.03，MySQL版本大於或等於這個版本的時候，註釋中的內容會被執行。

mysqldump 高級選項

在備份數據的過程中，可以添加一些選項來對備份進行優化：

1、優化備份速度：

如果備份一個數據量很大的庫或者表，mysqldump 預設會讀取整個表到記憶體中，然後寫入到備份文件。如果表非常大，這可能會消耗大量記憶體，並可能導致過度的記憶體使用甚至崩潰。

這個時候可以通過 --quick 選項，它是直接逐行讀取數據並寫入備份文件，顯著減少了一次性記憶體需求。從而加快備份速度。

2、主從複製環境：

預設情況下，備份數據文件的時候，是不會記錄當前二進位日誌位置的。使用 --master-data 時，mysqldump 會在 SQL 備份文件中添加一個 CHANGE MASTER TO 語句。

這個語句包含了備份時刻的二進位日誌文件名和位置（log file position）。這對於設置複製非常重要，因為它指明瞭從伺服器（slave）開始讀取主伺服器（master）二進位日誌的起始點。

--master-data有兩個值，預設值是2

  • --master-data=1：以非註釋形式包含 CHANGE MASTER TO 語句

  • --master-data=2：使CHANGE MASTER TO 語句以註釋形式添加，從而在不自動更改從伺服器配置的情況下提供必要的信息。

--master-data 選項經常結合 --flush-logs 選項一起使用，--flush-logs 會在備份開始前刷新 MySQL 伺服器的日誌，包括二進位日誌（binary log）。此時 MySQL 會關閉當前的二進位日誌文件並開始一個新的日誌文件。

說明：

--master-data 選項在 mysqldump 輸出的 CHANGE MASTER TO 語句中主要包含 MASTER_LOG_FILE 和 MASTER_LOG_POS 參數，這些參數指明瞭二進位日誌（binary log）的文件名和位置。

但是，這個語句並不包含完整的主伺服器（master）配置，如 master_host、master_user、master_password 等。

所以這個參數主要用於記錄備份時刻的二進位日誌位置，這對於配置從伺服器從正確的位置開始複製數據很重要，所以完整的數據配置可以自己手動修改這個sql文件實現。

3、innodb引擎表備份：

使用 --single-transaction 選項，mysqldump 會在開始備份之前啟動一個新的事務。所以備份會捕捉到事務開始時刻的資料庫狀態，並且在整個備份過程中保持這一狀態，即使後續對資料庫進行了更改。

4、mysql資料庫備份

mysql這個資料庫裡面存放了mysql的用戶和許可權信息，如果想讓備份文件被恢復到另一個 MySQL 伺服器上後，用戶和許可權的更改立即生效。需要加 --flush-privileges 選項

添加這個選項後，會在 SQL 備份文件中添加一條 FLUSH PRIVILEGES; 語句。當這個備份文件被導入到 MySQL 伺服器時，FLUSH PRIVILEGES; 語句會執行，從而重新載入許可權表。

這確保了任何用戶和許可權的更改（如新用戶的添加或許可權的修改）會立即生效。這樣就不用手動刷新許可權了。

5、mysam引擎備份
現在一般用的都是innodb這個存儲引擎，使用mysqldump這個工具的時候，innodb是支持熱備份的，而mysam只支持溫備份，即備份過程中能讀不能寫。

如果備份所有資料庫，通過 --lock-all-tables 選項或者 -x選項來加全局讀鎖，會鎖定所有資料庫的所有表。也會導致一個問題，數據量大時，可能會導致長時間無法併發訪問資料庫。

mysqldump -u user_name -p --lock-all-tables --all-databases > backup.sql

如果支持備份單個資料庫，可以用 -lock-tables 參數，這樣只會鎖定正在備份的資料庫中的表。備份操作不會影響到伺服器上其他資料庫中的表。

mysqldump -u user_name -p --lock-tables database_name > backup.sql

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • WPF應用框架中工作流模塊的介紹
    在前面的隨筆，我對我們開發的審批工作流做了不少的介紹，其中有包括WInform的、Vue+Element、Bootstrap Asp.net的，在各個框架上，我們都儘量爭取界面能夠一致化，以便客戶能夠在不同的前端上有相同的用戶體驗，並結合不同的前端特點，做了一些優化處理，本篇隨筆對WPF應用框架中工... ...
  • 第二單元 Http 概述
    1. C/S 與 B/S C/S結構系統是什麼 Client/Server結構(C/S結構)是大家熟知的客戶機和伺服器結構。它是軟體系統體繫結構，通過它可以充分利用兩端硬體環境的優勢，將任務合理分配到Client端和Server端來實現，降低了系統的通訊開銷 B/S結構系統是什麼 B/S結構（Bro ...
  • .net下優秀的日誌框架Serilog，你用上了嗎？強烈推薦
    在 .NET 開發中，Serilog 是一款廣受歡迎的日誌庫，它提供了強大的日誌記錄功能，具有豐富的特性和高度的可擴展性。Serilog 的優秀之處包括： 可擴展性: Serilog 可以輕鬆擴展以滿足不同的日誌記錄需求，例如日誌存儲、格式化和過濾。它支持各種插件和自定義擴展，讓你可以根據項目的具體 ...
  • Linux-02tmux和vim
    tmux教程 功能 分屏。 允許斷開Terminal連接後，繼續運行進程。 結構 // 一個tmux可以包含多個session，一個session可以包含多個window，一個window可以包含多個pane。 tmux: session 0: window 0: pane 0 pane 1 pan ...
  • MarkdownPad 文件訪問許可權受限導致軟體打開後不久閃退解決方法
    近幾天發現MarkdownPad有一些小問題，打開時會彈出以下報錯信息，告訴你打開文件的許可權不夠 解決方法如下： 1、複製報錯信息中的文件路徑'C:\Users\Administrator\AppData \Roaming\wyUpdate AU\ApricitySoftware-MarkdownP ...
  • 簡易電腦的搭建
    版本 Linux 6.5 背景 在學習cgroupv2的時候，想給子cgroup開啟cpu控制器結果失敗了： # 查看可以開啟哪些控制器 root@ubuntu-vm:/sys/fs/cgroup# cat cgroup.controllers cpuset cpu io memory hugetl ...
  • C-04.運算符(基礎)
    運算符 1.算術運算符 算術運算符主要用於數學運算，其可以連接運算符前後的兩個數值或表達式，對數值或表達式進行 +，-，*，/，%運算。 1.1 加法和減法運算符 mysql> SELECT 100,100 + 0,100 - 0,100 + 50,100 + 50 - 30,100 + 35.5, ...
  • SQL CREATE INDEX 語句- 提高資料庫檢索效率的關鍵步驟
    SQL CREATE INDEX 語句 SQL CREATE INDEX 語句用於在表中創建索引。 索引用於比其他方式更快地從資料庫中檢索數據。用戶無法看到索引，它們只是用於加速搜索/查詢。 註意： 使用索引更新表比不使用索引更新表需要更多的時間（因為索引也需要更新）。因此，只在經常進行搜索的列上創 ...
一周排行
    -Advertisement-
    Play Games
  • 實用接地氣的 .NET 微服務框架
    前言 微服務架構已經成為搭建高效、可擴展系統的關鍵技術之一，然而，現有許多微服務框架往往過於複雜，使得我們普通開發者難以快速上手並體驗到微服務帶了的便利。為瞭解決這一問題，於是作者精心打造了一款最接地氣的 .NET 微服務框架，幫助我們輕鬆構建和管理微服務應用。 本框架不僅支持 Consul 服務註 ...
  • 基於surging的木舟IOT平臺如何添加網路組件
    先看一下效果吧: 如果不會寫動畫或者懶得寫動畫，就直接交給Blend來做吧; 其實Blend操作起來很簡單，有點類似於在操作PS，我們只需要設置關鍵幀，滑鼠點來點去就可以了，Blend會自動幫我們生成我們想要的動畫效果. 第一步：要創建一個空的WPF項目 第二步：右鍵我們的項目，在最下方有一個，在B ...
  • Prism:框架介紹與安裝
    Prism:框架介紹與安裝 什麼是Prism? Prism是一個用於在 WPF、Xamarin Form、Uno 平臺和 WinUI 中構建鬆散耦合、可維護和可測試的 XAML 應用程式框架 Github https://github.com/PrismLibrary/Prism NuGet htt ...
  • 不可不知的WPF畫筆（Brush）
    在WPF中，屏幕上的所有內容，都是通過畫筆（Brush）畫上去的。如按鈕的背景色，邊框，文本框的前景和形狀填充。藉助畫筆，可以繪製頁面上的所有UI對象。不同畫筆具有不同類型的輸出（ 如：某些畫筆使用純色繪製區域，其他畫筆使用漸變、圖案、圖像或繪圖）。 ...
  • .NET 8 高併發微服務電商系統實戰
    前言 嗨，大家好！推薦一個基於 .NET 8 的高併發微服務電商系統，涵蓋了商品、訂單、會員、服務、財務等50多種實用功能。 項目不僅使用了 .NET 8 的最新特性，還集成了AutoFac、DotLiquid、HangFire、Nlog、Jwt、LayUIAdmin、SqlSugar、MySQL、 ...
  • 基於surging 如何利用peerjs進行語音視頻通話
    本文主要介紹攝像頭（相機）如何採集數據，用於類似攝像頭本地顯示軟體，以及流媒體數據傳輸場景如傳屏、視訊會議等。 攝像頭採集有多種方案，如AForge.NET、WPFMediaKit、OpenCvSharp、EmguCv、DirectShow.NET、MediaCaptre（UWP），網上一些文章以及 ...
  • [WPF]數據綁定時為何會出現StringFormat失效
    前言 Seal-Report 是一款.NET 開源報表工具，擁有 1.4K Star。它提供了一個完整的框架，使用 C# 編寫，最新的版本採用的是 .NET 8.0 。 它能夠高效地從各種資料庫或 NoSQL 數據源生成日常報表，並支持執行複雜的報表任務。 其簡單易用的安裝過程和直觀的設計界面，我們 ...
  • NET Core 多身份校驗與策略模式
    背景需求： 系統需要對接到XXX官方的API，但因此官方對接以及管理都十分嚴格。而本人部門的系統中包含諸多子系統，系統間為了穩定，程式間多數固定Token+特殊驗證進行調用，且後期還要提供給其他兄弟部門系統共同調用。 原則上：每套系統都必須單獨接入到官方，但官方的接入複雜，還要官方指定機構認證的證書 ...
  • C#自定義控制項—轉換開關
    本文介紹下電腦設備關機的情況下如何通過網路喚醒設備，之前電源S狀態 電腦Power電源狀態- 唐宋元明清2188 - 博客園 (cnblogs.com) 有介紹過遠程喚醒設備，後面這倆天瞭解多了點所以單獨加個隨筆 設備關機的情況下，使用網路喚醒的前提條件： 1. 被喚醒設備需要支持這WakeOnL ...
  • .NET 8.0 前後分離快速開發框架
    前言 大家好，推薦一個.NET 8.0 為核心，結合前端 Vue 框架，實現了前後端完全分離的設計理念。它不僅提供了強大的基礎功能支持，如許可權管理、代碼生成器等，還通過採用主流技術和最佳實踐，顯著降低了開發難度，加快了項目交付速度。 如果你需要一個高效的開發解決方案，本框架能幫助大家輕鬆應對挑戰，實 ...
所有分類