MySQL 介紹完全備份與恢復

免費學習推薦：mysql視頻教程

文章目錄

• 前言
• 一、MySQL 完全備份
• • 1.數據庫備份方式精講
  • • 1.1 數據庫備份的重要性
    • 1.2 數據庫備份的分類
    • • 1.2.1 物理備份
      • 1.2.2 邏輯備份
      • 1.2.3 完全備份
      • 1.2.4 差異備份
      • 1.2.5 增量備份
      • 1.2.6 備份方式比較
    • 1.3 MySQL 完全備份概念解讀
  • 2.mysqldump 備份演練
  • • 2.1 使用 tar 打包文件夾備份
    • 2.2 使用 mysqldump 工具備份
• 二、MySQL 完全恢復
• • 1.恢復整庫操作
  • • 1.1 source 命令整庫恢復
    • 1.2 MySQL 命令整庫恢復
  • 2.恢復表操作
  • • 2.1 使用 source 命令恢復表
    • 2.2 使用 mysql 命令恢復表
  • 3.MySQL 備份思路

前言

• 隨著辦公自動化和電子商務的飛速發展，企業對信息系統的依賴性越來越高，數據庫作為信息系統的核心，擔當者重要的角色
• 數據庫備份，是在數據丟失的情況下，能及時恢復重要數據，防止數據丟失的一種重要手段
• 一個合理的數據庫備份方案，能夠在數據丟失時，有有效地恢復數據，而且也需要考慮技術實現難度和有效地利用資源

一、MySQL 完全備份

1.數據庫備份方式精講

1.1 數據庫備份的重要性

生產環境中，數據的安全性是至關重要的，任何數據的丟失都可能產生嚴重的后果

數據庫備份的重要性主要體現在：

1. 提高系統的高可用性和災難可恢復性，在數據庫系統崩潰時，沒有數據庫備份就沒法找到數據
2. 使用數據庫備份還原數據庫，是數據庫崩潰時提供數據恢復最小代價的最優方案，如果讓用戶重新添加數據，代價未免太大
3. 沒有數據就沒有一切，數據庫備份是一種防范災難的強力手段

使用數據庫的過程中，有多種原因造成數據的丟失：

1. 程序錯誤：指對數據庫操作的程序難免有些錯誤，造成數據丟失
2. 人為錯誤：指由于使用人員的誤操作造成的數據被破壞，還有可能是黑客對系統攻擊造成的數據丟失等
3. 計算機失敗：指運行數據庫的服務器操作系統或軟件損壞，有可能造成數據的損壞
4. 磁盤失敗：指硬盤等存儲數據的硬件設備，長時間運行后可能損壞，造成數
   據丟失
5. 災難（如火災、地震）和偷竊：指自然災害等的發生，有可能造成數據丟失

• 數據丟失會造成嚴重的經濟損失，如攜程網2015年5月28日數據庫崩潰，每小時損失大概106萬美元
• 所以在企業信息系統建設中，數據庫的備份管理是非常重要的內容

1.2 數據庫備份的分類

備份方式分很多種，從物理與邏輯的角度，備份可分為以下幾類：

1.2.1 物理備份

• 指對數據庫操作系統的物理文件（如數據文件、日志文件等）的備份
• 物理備份又可以分為脫機備份（冷備份）和聯機備份（熱備份）
• 冷備份（脫機備份）：在關閉數據庫時進行的備份操作，能夠較好地保證數據庫的完整性
• 熱備份（聯機備份）：在數據庫運行狀態中進行操作，這種備份方法依賴于數據庫的日志文件

1.2.2 邏輯備份

• 指對數據庫邏輯組件（如表等數據庫對象）的備份

從數據庫的備份策略角度來看，備份又可分為完全備份、差異備份和增量備份

1.2.3 完全備份

• 每次對數據進行完整的備份
• 可以備份整個數據庫，包含用戶表、系統表、索引、視圖和存儲過程等所有數據庫對象
• 但它需要花費更多的時間和空間，所以，做一次完全備份的周期要長些

1.2.4 差異備份

• 備份那些自從上次完全備份之后被修改過的文件，即只備份數據庫部分的內容
• 它比最初的完全備份小，因為只包含自上次完全備份以來所改變的數據庫
• 它的優點是存儲和恢復速度快

1.2.5 增量備份

• 只有那些在上次完全備份或者增量備份后被修改的文件才會被備份

1.2.6 備份方式比較

• 完全備份每次都是把整個數據庫中的內容進行備份
• 差異備份在第1次添加內容后備份，即備份新添加的內容；第2次添加內容后備份，把第1次添加的內容也進行了備份，它是根據完全備份時的狀態決定的
• 差異備份每次都是把完全備份之后所有的修改進行備份，而不管之前是否做過差異備份
• 增量備份只備份每次新修改的內容，第1次創建表3，就對表3備份，第2次添加表4后，因為之前已經備份過表3，所以只備份表4的內容

1.3 MySQL 完全備份概念解讀

• MySQL 的備份方式主要有完全備份與增量備份
• 完全備份是對整個數據庫的備份、數據庫結構和文件結構的備份，保存的是備份完成時刻的數據庫，是增量備份的基礎
• 完全備份的優點是備份與恢復操作簡單方便，缺點是數據存在大量重復，占用大量的備份空間，備份的時間長
• 在生產環境中，這兩種備份方式都會使用，需要制定合理高效的方案達到備份數據的目的，避免數據丟失造成嚴重的后果

2.mysqldump 備份演練

• MySQL 數據庫的備份可以采用兩種方式
• 因為數據庫實際上就是文件，直接打包數據庫文件夾，或者是使用專用備份工具 mysqldump 都可以進行備份工作

2.1 使用 tar 打包文件夾備份

• MySQL 的數據庫文件默認都是保存在安全目錄的 data 文件夾下，可以直接保存 data 文件夾，因為占用空間較大，可以使用data 打包壓縮進行保存
  

yum -y install xz#數據庫文件很大，可以使用壓縮率較大的xz格式壓縮，首選需要安裝xz壓縮格式工具tar Jcvf /opt/mysql-$(date +%F).tar.xz /usr/local/mysql/data/#對數據庫文件夾進行打包操作ls /opt#查看打包命令是否運行成功，已經生成了備份文件du -sh /opt/mysql-2021-02-05.tar.xz #對比打包前后的文件大小，可以看到壓縮的備份文件所占用空間很小tar -Jxvf /opt/mysql-2021-02-05.tar.xz -C /usr/local/mysql/data/systemctl restart mysql#如果數據庫文件損壞數據丟失，可以解壓縮文件，相當于數據恢復

2.2 使用 mysqldump 工具備份

• 上文中演示的是對 MySQL 整個數據庫的目錄進行壓縮的方式，是備份數據庫中所有的內容
• mysqldump 是 mysql 用于轉存儲數據庫的實用程序（自帶），它主要產生一個 SQL 腳本，其中包含從頭重新創建數據庫所必需的命令（如 CREATE、TABLE、INSERT等）
• 使用 mysqldump 可以更加靈活地控制備份的內容，比如某幾個表或庫都可以單獨備份

#開始之前，創建庫和表，用作例子mysql -uroot -p123123#輸入密碼進入數據庫，以上為我的密碼create database SCHOOL;#創建庫use SCHOOL;create table CLASS01 ( id int(2) not null auto_increment, name varchar(10) not null, sex char(5) not null, hobby varchar(10), primary key (id));#創建表結構#字段一：id，最大顯示長度2，不能為空，自動遞增從1開始#字段二：name，可變長度字段，10，不能為空#字段三：sex，固定長度5，不能為空#字段四：hobby，可變長度，最多10字符#字段四：主鍵（id）insert into CLASS01 values(1,'wangyi','man','reading');insert into CLASS01 values(2,'wanger','woman','singing');#插入2條數據select * from CLASS01;#檢查一下quit#退出數據庫

#使用 mysqldump 對某些表進行完全備份，命令格式如下：mysqldump -u[用戶名] -p[密碼] [選項] [數據庫名] [數據表名] > /備份路徑/備份文件名.sql#導出的為數據庫腳本文件例：對庫SCHOOL中的表class進行備份至/opt/目錄下mysqldump -uroot -p[密碼] SCHOOL CLASS01 > /opt/test01.sql

#對某些庫進行完全備份mysqldump -uroot -p[密碼] [數據庫名] > /目錄/備份文件名.sql例：mysqldump -uroot -p123123 SCHOOL > /opt/test02#對多個庫進行完全備份mysqldump -uroot -p123123 --databases SCHOOL sys > /opt/test03.sql#對所有庫進行完全備份mysqldump -uroot -p123123 --all-databases > /opt/test04.sql#直接備份表結構mysqldump -uroot -p123123 -d SCHOOL CLASS01 > /opt/test05.sqlls /opt/#查看打包命令是否運行成功，已經生成了備份文件

cat /opt/test05.sql#分析 mysqldump 工具生成的備份文件的內容

• 上面使用了 -d 選項，說明只保存數據庫的表結構，且表中只有一個庫，可以看到先刪除了表（同名），再創建了它
• 刪除和創建都是最普通的 MySQL 命令，任何一條在 MySQL 中都是可以執行的語句，有了這些語句就可以創建出和現在的表結構相同的表
• 如果不使用 -d 選項，會把數據也進行備份，下面看看數據時如何保存的

cat /opt/test01

• 可以看到與使用 -d 參數的差別是最后出現了 insert into 語句，往數據庫中插入了兩條數據，也就是說，數據的備份是保存的插入語句操作
• 通過以上分析，很容易理解，備份的本質就是保存數據庫當前表結構的創建語句和現有數據的插入語句，有了它們就可以直接恢復數據庫的狀態

二、MySQL 完全恢復

• 前面介紹了數據庫的完全備份，使用 mysqldump 工具可以把數據的創建語句保存在備份的腳本文件中
• 當出現錯誤時，可以使用以下幾種方法恢復它們

1.恢復整庫操作

1.1 source 命令整庫恢復

mysql -uroot -p123123show databases;drop database SCHOOL;show databases;source /opt/test03.sql#之前備份過的，該備份為SCHOOL和sys兩個庫的完整備份#注意！選項得加“--databases”才是完整的備份，不然會恢復不了

1.2 MySQL 命令整庫恢復

quitmysql -uroot -p123123 -e 'drop database SCHOOL;'mysql -uroot -p123123 -e 'SHOW DATABASES;'mysql -uroot -p123123 < /opt/test03.sql   mysql -uroot -p123123 -e 'SHOW DATABASES;'

2.恢復表操作

• 恢復數據表同樣可以使用 source 命令和 mysql 命令操作

2.1 使用 source 命令恢復表

mysql -uroot -p123123use SCHOOL;show tables;drop table CLASS01;  source /opt/test01.sql#恢復表show tables;select * from CLASS01;#查看

2.2 使用 mysql 命令恢復表

quitmysql -uroot -p123123 -e 'SHOW TABLES FROM SCHOOL;'mysql -uroot -p123123 -e 'DROP TABLES SCHOOL.CLASS01;'mysql -uroot -p123123 -e 'SHOW TABLES FROM SCHOOL;'mysql -uroot -p123123 SCHOOL < /opt/test01.sqlmysql -uroot -p123123 -e 'SHOW TABLES FROM SCHOOL;'

3.MySQL 備份思路

• MySQL 需要定期實施備份，指定合適的備份計劃或策略，并嚴格遵守
• 除了進行完全備份，開啟 MySQL 服務器的日志功能也很重要，完全備份加上日志，可以對 MySQL 進行最大化還原
• 備份文件的名字還需鑰使用統一的易于理解的名稱，推薦使用庫名或表名加上時間的命名規則，在需要恢復數據庫時能很容易的定位到相應的所需備份文件

相關免費學習推薦：mysql數據庫(視頻)