博客 / 詳情

返回

怎麼樣在MySQL上使用備份恢復

1.數據庫服務備份恢復操作

物理備份數據方式
#	拷貝數據庫數據目錄信息，實現物理備份（冷備）

步驟一：停止數據庫服務
/etc/init.d/mysqld stop

步驟二：將數據庫目錄進行拷貝/壓縮
cp -a /data/3306/data /backup/data-2026-03-05

步驟三：模擬數據庫損壞
drop database oldboy;
drop database school;
drop database xiaoA;


步驟四：修復數據庫中數據
/etc/init.d/mysqld stop
vim /etc/my.cnf
[mysqld]
datadir=加載備份數據目錄信息
/etc/init.d/mysqld start

rm -rf /data/3307/data/*
cp -a /backup/data-2026-03-05/* /data/3307/data
/etc/init.d/mysqld start

利用物理備份工具備份數據，實現物理熱備（熱備）（Xtrabackup）
物理備份工具鏈接:https://www.percona.com/downloads/
應用物理備份工具需要關注版本信息：
xbk	8.4版本	完成數據庫備份		mysql 8.4以上版本進行備份		
xbk	8.0版本	完成數據庫備份		mysql 8.0以上版本進行備份
xbk	2.4版本	完成數據庫備份		mysql 8.0以下版本進行備份

部署xbk工具並應用：
步驟一：解壓安裝部署備份工具
cd /usr/local/
tar xf percona-xtrabackup-8.0.35-34-Linux-x86_64.glibc2.28.tar.gz
ln -s percona-xtrabackup-8.0.35-34-Linux-x86_64.glibc2.28 xbk
vim /etc/profile
export PATH='$PATH:/usr/local/xbk/bin'
source /etc/profile

步驟二：應用備份工具備份數據
實現數據庫數據物理備份
mkdir -p /backup/xbk/{full,inc01} 
-- 一定確認好以上創建備份目錄是空目錄
xtrabackup --defaults-file=/data/3307/my80.cnf --host=10.0.0.51 --port=3307 --user=root --password=123456  --backup  --target-dir=/backup/xbk/full
成功備份後，會顯示以下信息
[Note] [MY-011825] [Xtrabackup] completed OK!

--defaults-file   -- 備份工具進行備份數據時，需要加載備份數據庫實例配置文件（配置文件存儲默認路徑，參數可省略）
--backup          -- 實現數據庫數據備份操作  
--target-dir      -- 將原有數據庫數據目錄信息備份到指定路徑中

模擬數據庫數據異常
drop database oldboy;
drop database school;
drop database xiaoA;

停止數據庫服務運行
/etc/init.d/mysqld stop
rm -rf /data/3307/data/*
rm -rf /data/3307/logs/* 

完成數據修復過程
xtrabackup --prepare --target-dir=/backup/xbk/full
內存數據恢復成功：[Note] [MY-011825] [Xtrabackup] completed OK!
-- 表示加載備份數據信息，實現將原有數據庫服務內存中數據恢復

xtrabackup --defaults-file=/data/3307/my80.cnf --copy-back --target-dir=/backup/xbk/full
磁盤數據恢復成功：[Note] [MY-011825] [Xtrabackup] completed OK!
chown -R mysql.mysql /data/3307/data
chown -R mysql.mysql /data/3307/log/
-- 表示加載備份數據信息，實現將原有數據庫服務磁盤中數據恢復（恢復原有數據目錄中數據文件）

/etc/init.d/mysqld start

實現數據庫數據物理備份

實現數據庫數據物理備份（全量+增量）

rm -rf /backup/xbk/{full,inc01}/*

步驟一：完成第一全量備份
xtrabackup --defaults-file=/data/3307/my80.cnf --host=10.0.0.51 --port=3307 --user=root --password=123456  --backup --parallel=4 --target-dir=/backup/xbk/full 

--parallel   利用此參數可以設置備份過程xbk進程對應線程信息，可以提高備份數據效率

步驟二：模擬進行增量數據創建
create database xkb;
create table t1 (id int,name char(10));
insert into t1 values (1,'xiaoA'),(2,'xiaoB');

步驟三：進行第一次增量備份
xtrabackup --defaults-file=/data/3307/my80.cnf --host=10.0.0.51 --port=3307 --user=root --password=123456 --backup --parallel=4 --target-dir=/backup/xbk/inc01  --incremental-basedir=/backup/xbk/full 

--incremental-basedir  表示用於做數據信息變化對比，將此目錄中數據作為對比對象（LSN），從而識別增量數據

擴展説明：如果還想進行第二次增量備份，如何執行備份操作
xtrabackup --defaults-file=/data/3307/my80.cnf --host=10.0.0.51 --port=3307 --user=root --password=123456 --backup --parallel=4 --target-dir=/backup/xbk/inc02  --incremental-basedir=/backup/xbk/inc01  

步驟四：模擬數據損壞
drop database oldboy;
drop database game;
drop database xkb;

完成數據修復過程（全量+增量）
步驟五：完成數據恢復操作
pkill mysqld
rm -rf /backup/data-2026-03-05/*
rm -rf /data/3307/log/*

xtrabackup --prepare  --apply-log-only  --target-dir=/backup/xbk/full
-- 加載全量目錄中數據信息
xtrabackup --prepare  --apply-log-only  --target-dir=/backup/xbk/full --incremental-dir=/backup/xbk/inc01
--apply-log-only   簡單理解：表示可以將全量和增量數據有效整合，保證內存中數據恢復完整
-- 將增量數據信息和全量數據信息合併整合

xtrabackup --prepare  --target-dir=/backup/xbk/full
-- 將內存中數據進行恢復
xtrabackup --defaults-file=/data/3307/my80.cnf --copy-back  --target-dir=/backup/xbk/full
-- 將磁盤中數據進行恢復

chown -R mysql.mysql /backup/data-2026-03-05/
chown -R mysql.mysql /data/3307/log/

步驟六：重新啓動數據庫服務檢查數據信息
show databases;

2.數據庫服務架構環境部署

（1）.實現數據庫主從同步架構

作用01：可以實現數據信息實時備份保存
作用02：可以實現高可用架構部署 
作用03：可以實現負載均衡架構部署

（2）.實現數據庫主從同步原理❗️❗️❗️

兩種方法介紹：

##########		1.詳細説明		##########
根據主從同步操作流程：
01.需要在主庫中進行全量備份數據和創建主從同步用户
02.在從庫中加載全量備份數據，然後再將主庫中增量數據做同步處理
03.在從庫中進行主從功能配置
	連接主庫配置：地址配置		端口配置	 用户信息	 密碼信息
	同步數據配置：配置需要加載binlog文件信息		加載位置點信息
	change master to ......
04.將配置信息會自動保存到數據庫master.info文件中
05.啓動主從同步功能		start slave;
	主從功能啓動成功，會在從庫出現兩個線程
	IO線程；加載master.info文件信息，和主庫建立連接
	SQl線程：空閒
06.主庫接收IO線程連接請求後，會創建主庫線程
	mysql dump:和從庫IO線程對接，維持主從會話/監控binlog日誌信息，將信息發送給IO線程
07.從庫接收主庫binlog日誌信息
	IO線程：將接收到的binlog日誌信息保存到relaylog日誌中（中繼日誌）
			在master.info文件中做binlog文件或位置點信息修改
	SQl線程：讀取relaylog日誌信息，並將日誌信息中的事務語句進行執行
			將relaylog日誌信息讀取的位置點，進行保存到relaylog.info文件中


##########		2.簡單介紹		##########
根據主從同步會應用進程和文件做介紹

實現主從同步原理需要應用三個線程和四個文件
#	------三個線程------
IO線程：	實現主從之間建立連接會話
		  負責接收binlog日誌事務信息/負責接收需要同步事務語句信息，會存儲信息到中繼日誌
		  負責修改master.info文件中同步位置點信息
		  
SQl線程：	讀取中繼日誌中事務語句信息，會將語句信息在從庫執行
		  負責修改relaylog.info文件中已經回放事務信息的位置點
		  
dump線程：	負責從庫維護會話建立
		  監控二進制日誌信息，並將變化信息發送給從庫

#	------四個文件------
binlog：	 記錄主庫中完成時誤操作信息（DDL/DCL/DML），存儲在主庫		
		  如何查看：show binlog events in 'xxx';
		  
relaylog：記錄主庫中完成事務操作信息（DDL/DCL/DML），存儲在從庫
		  show relaylog events in 'xxx';
		  
master.info：   記錄鏈接主庫的配置信息，還有同步數據的位置點信息

relaylog.info： 記錄中繼日誌被讀取的位置點信息

（3）.實現數據庫主從同步架構搭建

方式一：採用傳統方式實現主從搭建

環境準備：
10.0.0.51  db-01 主庫   數據庫服務實例 8.0.36
10.0.0.52  db-02 從庫   數據庫服務實例 8.0.36
10.0.0.53  db-03 從庫   數據庫服務實例 8.0.36

步驟一：主庫備份數據和創建主從同步用户
create user repl@'10.0.0.%' identified with mysql_native_password by '123456';
grant Replication slave on *.* to repl@'10.0.0.%';

mysqldump -uroot -p666666 -A --source-data >/backup/all-03-05.sql

scp -rp /backup/all-03-05.sql 10.0.0.122:/backup/
scp -rp /backup/all-03-05.sql 10.0.0.123:/backup/

步驟二：從庫進行主庫傳輸後全量數據恢復
mysql < /backup/all-03-05.sql
全量備份後位置點信息：binlog.000001  1380

步驟三：在從庫中進行主從功能配置
CHANGE MASTER TO
  MASTER_HOST='10.0.0.51',
  MASTER_PORT=3306, 
  MASTER_USER='repl',
  MASTER_PASSWORD='123456',
  MASTER_LOG_FILE='binlog.000001',
  MASTER_LOG_POS=1380;
-- 以上配置可以在mysql.slave_master_info 表中查看到配置信息

步驟四：激活主從同步數據功能
start slave;
-- 激活主從同步功能，就會在從庫中創建IO線程和SQL線程，主庫會創建dump線程

（4）.主從同步異常如何監控排查問題

show slave status\G  
-- 查看主從同步狀態信息，可以確認主從同步是否正常或異常

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
-- 表示主從同步正常，如果未顯示兩個Yes信息，表示主從同步異常

Last_IO_Errno: 0
Last_IO_Error: 
Last_SQL_Errno: 0
Last_SQL_Error: 
-- 當IO或SQL線程出現問題，以上信息中會顯示對應線程出現錯誤異常原因

Master_Log_File: binlog.000001
Read_Master_Log_Pos: 1568
-- 以上信息表示IO線程接收到事務位置點信息  
Relay_Master_Log_File: binlog.000001
Exec_Master_Log_Pos: 1568
-- 以上信息表示SQL線程執行的事務位置點信息

Seconds_Behind_Master: 0
-- 以上信息表示主從同步過程，是否有同步延遲情況

Replicate_Do_DB:           
Replicate_Ignore_DB:     
-- 設置SQL線程對於指定數據庫是否回放數據信息的白名單和黑名單設置

Replicate_Do_Table:              
Replicate_Ignore_Table: 
-- 設置SQL線程對於指定數據表是否回放數據信息的白名單和黑名單設置

Replicate_Wild_Do_Table:       
Replicate_Wild_Ignore_Table:
-- 實現SQL數據庫針對表的同步模糊設置，實現白名單和黑名單設置

總結

01 數據庫服務備份恢復
   物理備份恢復
   -- 複製方式備份恢復（冷備）
   -- 工具方式備份恢復（熱備-xbk）
   全量數據備份與全量數據恢復/增量數據備份與增量數據恢復
02 數據庫服務主從同步
   數據庫主從同步原理（重要） 根據主從建立流程敍述主從原理/根據主從建立涉及到3線程和4文件做描述
   數據庫主從同步實踐        利用傳統方式實現主從同步
   數據庫主從狀態查看（監控） 關注線程狀態 數據同步延遲情況
數據庫
user avatar
0 位用戶收藏了這個故事！

發佈 評論

Some HTML is okay.