資料庫學習之十一：mysql 備份恢復

来源:https://www.cnblogs.com/cuiyongchao007/archive/2020/05/08/12853481.html
-Advertisement-
Play Games

十一、mysql 備份恢復 課程大綱 運維工作的核心簡單概括就兩件事： 第一個是保護公司的數據。 第二個是讓網站能7 24小時提供服務(用戶體驗)。 1、備份的類型 冷備份：關閉數據、停止業務 溫備份：枷鎖備份 熱備份：線上備份，不會影響業務。 2、備份方式 邏輯備份： 基於sql語句的備份： ①m ...


十一、mysql 備份恢復

課程大綱

1、備份的原因

2、備份的方式

3、備份的工具

4、mysqldump備份工具的詳解

5、mysqldump+mysqlbinlog實現增量備份

6、企業級備份策略及恢復案例

7、xtrabackup備份恢復實戰

運維工作的核心簡單概括就兩件事：

第一個是保護公司的數據。

第二個是讓網站能7*24小時提供服務(用戶體驗)。

1、備份的類型

冷備份：關閉數據、停止業務

溫備份：枷鎖備份

熱備份：線上備份，不會影響業務。

2、備份方式

邏輯備份：

基於sql語句的備份：

①mysqldump--》建庫，建表，數據插入

②基於二進位日誌：資料庫的所有變化類的操作。

③基於複製的備份：將二進位日誌實時傳遞到另一臺機器並且恢復。

物理備份：

①xtrabackup 進行物理備份

②拷貝數據文件（冷備）

3、備份工具：

①mysqldump

mysql原生自帶很好用的邏輯備份工具

②mysqlbinlog（根據始末position位置進行截取備份）

實現binlog備份的原生態命令

③xtrabackup

percona公司開發的性能很高的物理備份工具

mysqldump備份工具優缺點：

優點：邏輯備份工具，都是sql語句，都是文本格式，便於查看和編輯，便於壓縮。

缺點：備份較慢，效率低。

mysqldump參數介紹：

-u -p -S -h -P

-A, 全庫備份

例子：

[root@centos6-kvm3 ~]# mysqldump -uroot -poldboy123 -A >/backup/mysqlfull.sql

單庫備份：

-B,增加建庫（create）及’use庫‘的語句，在將來恢復時，不需要手工建庫和use庫。

-B 選項還可以實現，同時備份多個庫，備份到同一個文件中。（空格隔開）

[root@centos6-kvm3 ~]# mysqldump -uroot -poldboy123 -B lufei >/backup/mysqllufei.sql

[root@centos6-kvm3 ~]# mysqldump -uroot -poldboy123 lufei >/backup/mysqllufei1.sql

不加-B，恢復時候需要先創建庫，use庫下再進行恢復。

不加-B ,去備份他庫下的一個單表的意思。

生成環境下，也要加的額外參數：

-R:備份存儲過程和函數數據。

--triggers :備份觸發器數據。

-F，--flush-logs ：刷新binlog日誌，為了方便將來二進位日誌截取時的起點。

mysqldump -uroot -poldboy -A -F >/backup/mysqlfull.sql

--master-data={1|2} :告訴你備份時刻的binlog位置，一般我們選擇2，以註釋的方式記錄二進位日誌的位置。

mysql> show master status;

鎖表：適合所有引擎（myisam,innodb）

-x,--lock-all-tables

-l,--lock-tables

--single-transaction 對innodb引擎進行熱備

[root@centos6-kvm3 ~]# mysqldump -uroot -poldboy123 -A --master-data=2 --single-transaction >/backup/mysqlfull.sql

通過快照的方式實現熱備。

壓縮備份：

[root@centos6-kvm3 ~]# mysqldump -uroot -poldboy123 -A -R --triggers --master-data=2 --single-transaction | gzip >/backup/mysqlfull_$(date +%F).sql

4、mysqldump+binlog企業恢復實戰

使用source 命令進行恢復
set sql_log_bin=0;（臨時關閉二進位日誌，防止恢復操作記錄到二進位日誌中）
source /opt/xxx.sql;
企業實例：
背景環境：
正在運行的網站系統，mysql資料庫，數據量25G，日業務增量10-15M。
備份方式：
每天23:00點，計劃任務調用mysqldump執行全備腳本。
故障時間點：
上午10點，誤刪除了一個表。
如何恢復？
思路：
①斷開業務，防止對資料庫二次傷害，掛出維護頁面。
②搭建備用庫，恢復全備。
③截取昨天晚上23:00之後到上午10點誤刪除操作之前的二進位日誌。
④恢復到備用庫，驗證數據可用性和完整性。
⑤兩種恢復前端應用。
	5.1備用庫導出誤刪除的表，導入到生產庫，開啟業務。
	5.2直接將應用切割刀備用庫，替代生產庫，開啟業務。
模擬故障並恢復：
1、原始數據：
mysql> create database oldboy;
mysql> use oldboy
mysql> create table t1 (id int,name varchar(20));
mysql> insert into t1 values (1,'zhang3');
mysql> insert into t1 values (2,'li4');
mysql> insert into t1 values (3,'wang5');
mysql> commit;
2、模擬前一天晚上23:00全備
mysqldump  -A  -R --triggers --master-data=2 --single-transaction |gzip >/backup/all_$(date +%F).sql.gz

3、模擬白天（23:00-10:00）業務對數據的修改
mysql> insert into t1 values (4,'zhang33');
mysql> insert into t1 values (5,'li44');
mysql> insert into t1 values (6，'wang54');
mysql> commit;
4、模擬故障
drop table t1;
5、恢復
（1）準備全備，並獲取到備份文件中的binlog的截取起點
gunzip all_2018-04-04.sql.gz
	-- CHANGE MASTER TO MASTER_LOG_FILE='my-bin.000004', MASTER_LOG_POS=731;
（2）截取二進位日誌
mysqlbinlog --start-position=731  --stop-position=1126 /data/binlog/my-bin.000004 >/backup/binlog.sql
-----
 show binlog events in 'my-bin.000004';  ----》drop之前的position為1126 
-----
(3)恢復全備+binlog
set sql_log_Bin=0;
source /backup/all_2018-04-04.sql;
source /backup/binlog.sql

5、xtrabackup 介紹

Xtrabackup物理備份工具
percona公司的備份工具，性能比較高。物理備份工具。
特點：
物理備份工具，在同級數據量基礎上，都要比邏輯備份性能要好的多。
特別是在數據量比較大的時候，體現的更加明顯。
備份方式：
	1、拷貝數據文件
	2、拷貝數據頁
備份原理（innodb）：
	1、對於innodb表，可以實現熱備
		（1）在數據還有修改操作的時刻，直接將數據文件中的數據頁備份
		此時，備份走的數據對於當前mysql來講是不一致。
		（2） 將備份過程中的redo和undo一併備走。
		（3）為了恢復的時候，只要保證備份出來的數據頁LSN能和redo LSN匹配，
			將來恢復的就是一致的數據。redo應用和undo的應用。
	2、對與myisam表，實現自動鎖表拷貝文件。

Xtrabackup軟體安裝：

1、安裝
wget -O /etc/yum.repos.d/epel.repo  http://mirrors.aliyun.com/repo/epel-6.repo
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
yum -y install percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

2、全備備份並恢復
mkdir /server/backup  -p

--user=
--password=
--socket=
--no-timestamp 

全備例子：
innobackupex     /server/backup/

innobackupex     --no-timestamp  /server/backup/full

全備恢復例子：

1、恢複數據前的準備(合併xtabackup_log_file和備份的物理文件)
innobackupex --apply-log --use-memory=32M /server/backup/full/

2、模擬故障
停庫:
pkill mysqld
破壞數據：
cd /application/mysql/data
\rm -rf *

3、恢復
cp -a /server/backup/full/*  /application/mysql/data

或者

innobackupex --copy-back  /server/backup/full/   

註意：恢復時，要確認數據路徑是空的，並且資料庫是停掉的

chown -R mysql.mysql /application/mysql/data

啟動：
/etc/init.d/mysqld start

mysql -e "select * from oldboy.t1"

6、xtrabackup 實現增量備份及故障恢復

xtrabackup 實現增量備份及故障恢復

周日全備，周一到周六做增量

1、周日全備：
mkdir /backup/full
innobackupex --user=root --password=123 --no-timestamp /backup/full/
2、模擬數據變化（周一數據變化）
。 
。
。
3、第一增量（周一晚上增量）：
innobackupex --user=root --password=123 --incremental --no-timestamp --incremental-basedir=/backup/full/ /backup/inc1
4、模擬數據變化（周二數據變化）
。
。
。
5、第二次增量（周二晚上增量）：
innobackupex --user=root --password=123 --incremental --no-timestamp --incremental-basedir=/backup/inc1 /backup/inc2
6、模擬數據損壞

n多的操作。。。。。
周三上午10:00時刻,刪除t1表


7、恢複數據：
innobackupex --apply-log  --redo-only /backup/full
innobackupex --apply-log  --redo-only --incremental-dir=/backup/inc1 /backup/full
innobackupex --apply-log  --incremental-dir=/backup/inc2 /backup/full
innobackupex --apply-log  /backup/full

7、xtrabackup生產恢復案例：

背景：
1、xtrabackup備份策略每周日，full全備
2、xtrabackup周一到周六，inc1-inic6
3、總數據量200G
4、周三上午10點誤刪除表t1，數據量1G左右
5、周二晚上inc2備份完成之後到周三上午10點又做了很多操作

如何將資料庫恢復到t1表誤刪除之前狀態？

思路：
	1、停業務，掛維護頁
	2、找備用庫
	3、合併full+inc1+inc2
	4、截取周二晚上inc2備份後到周三上午10點，t1表刪除之前的binlog日誌
	5、將合併後的full+截取的binlog恢復到備用庫
	（ 根據備份日誌最後的position，再根據show binlog events in 'my-bin.000004';獲取最後的position）
	6、驗證數據可用性和完整性
	7、使用備用庫替代生產庫使用或者將t1表導出並導入回生產庫
	8、業務恢復

-----------------
-----------------
思考：以上恢復策略是否可以優化？
為了恢復1G表，需要將整個全備恢復，有必要嗎？有什麼好的解決辦法？
單獨恢復一個表：

drop table t1;
create table t1 (id int,name varchar(20));
alter table t1 discard tablespace;

cd /application/mysql/data/oldboy
cp /backup/full/oldboy/t1.ibd  ./

chown  -R mysql.mysql *
alter table t1 import tablespace;

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

-Advertisement-
Play Games
更多相關文章
  • 自動化運維工具Ansible之Roles測驗詳解
    Ansible Roles 詳解與實戰案例 主機規劃 添加用戶賬號 說明： 1、 運維人員使用的登錄賬號； 2、 所有的業務都放在 /app/ 下「yun用戶的家目錄」，避免業務數據亂放； 3、 該用戶也被 ansible 使用，因為幾乎所有的生產環境都是禁止 root 遠程登錄的（因此該 yun ...
  • HC32F003C4PA GPIO Output
    1、打開啟動文件，找到並跳轉至SystemInit函數 1 void SystemInit(void) 2 { 3 stc_clk_systickcfg_t stcCfg; 4 5 // TODO load trim from flash 6 //hcr 4MHz manual trim. 7 Cl ...
  • MacBook鍵盤鎖定、按鍵失效、無反應等問題
    [toc] 原文鏈接： "joselynzhao·CSDN·MacBook鍵盤鎖定、按鍵失效、無反應等問題" MacBook按鍵無反應 最近被ubuntu虛擬機折騰得夠嗆，關機之後再啟動就各種問題 於是我瞎百度了一波，各種快捷鍵一陣瞎按。 結果就是，我發現的鍵盤失靈了，按什麼鍵都沒有反應。 相應“重 ...
  • 企業級虛擬化實戰之KVM——虛擬機遷移
    遷移概述 系統的遷移是指把源主機上的 操作系統 和 應用程式 移動到目的主機，並且能夠在目的主機上正常 運行 在沒有虛擬機的時代，物理機之間的遷移依靠的是系統備份和恢復技術。在源主機上實時備份操作系統和應用程式的狀態，然後把存儲介質連接到目標主機上，最後在目標主機上恢復系統。隨著虛擬機技術的發展，系 ...
  • 快速升級Zabbix 5.0 版本
    Zabbix 5.0 增加了很多新功能，如：垂直菜單、隱藏菜單、用戶界面中的測試項目、限制代理檢查、查找並替換預處理步驟 ES7支持等等...快來部署體驗一把嘗鮮體驗 Zabbix 5.0 吧 升級須知 升級要求 PHP版本 PHP版本已從最低的5.4.0 升級到 7.2.0 資料庫版本 MySQL ...
  • Linux環境變數
    在bash shell中，環境變數分為兩類： 全局變數 局部變數 全局環境變數 全局環境變數對於shell會話和所有生成的子shell都是可見的。例如 : 局部環境變數 局部環境變數只能在定義它們的進程中可見。Linux也定義了標準的局部環境變數。 使用 命令可以顯示該進程的所有環境變數，包括 局部 ...
  • MySQL資料庫備份與恢復
    [TOC] 前言 1.備份數據的意義 運維工作的核心簡單概括起來就是兩件事：第一個是保護公司的數據，第二個是讓網站能夠7 24小時提供服務。 雖然這兩件事情都很重要，但是相比較而言，丟失一部分數據和讓網站7 24小時提供服務，哪個更重要呢？ 對於絕大多數企業來講，失去數據就相當於失去商機，失去產品， ...
  • 第五天MYSQL
    2020 5/9 十九、分頁查詢 應用場景：當要顯示的數據，一頁顯示不全，需要分頁提交sql請求 語法：(執行順序已標出) SELECT 查詢列表 ⑦每執行一步都會生成一個虛擬的結果集 FROM 表名 ① [join type join 表2 ② on 連接條件 ③ where 篩選條件 ④ gro ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類