一、數(shù)據(jù)備份的重要性
-
備份的主要目的是災(zāi)難恢復(fù)。
-
在生產(chǎn)環(huán)境中，數(shù)據(jù)的安全性至關(guān)重要。
-
任何數(shù)據(jù)的丟失都可能產(chǎn)生嚴(yán)重的后果。
-
造成數(shù)據(jù)丟失的原因：
- 程序錯(cuò)誤
- 人為操作錯(cuò)誤
- 運(yùn)算錯(cuò)誤
- 磁盤故障
- 災(zāi)難（如火災(zāi)、地震）和盜竊
二、數(shù)據(jù)庫(kù)備份的分類和備份策略
2.1 數(shù)據(jù)庫(kù)備份的分類
1）物理備份
物理備份：對(duì)數(shù)據(jù)庫(kù)操作系統(tǒng)的物理文件（如數(shù)據(jù)文件、日志文件等）的備份。
物理備份方法：
- 冷備份(脫機(jī)備份) ：是在關(guān)閉數(shù)據(jù)庫(kù)的時(shí)候進(jìn)行的
- 熱備份(聯(lián)機(jī)備份) ：數(shù)據(jù)庫(kù)處于運(yùn)行狀態(tài)，依賴于數(shù)據(jù)庫(kù)的日志文件
- 溫備份：數(shù)據(jù)庫(kù)鎖定表格（不可寫入但可讀）的狀態(tài)下進(jìn)行備份操作
2）邏輯備份
邏輯備份：對(duì)數(shù)據(jù)庫(kù)邏輯組件（如：表等數(shù)據(jù)庫(kù)對(duì)象）的備份。
- 即以sql語(yǔ)句的形式，把庫(kù)、表結(jié)構(gòu)、表數(shù)據(jù)保存下來(lái)
2.2 數(shù)據(jù)庫(kù)的備份策略
- 完全備份（全量備份）：每次對(duì)數(shù)據(jù)庫(kù)進(jìn)行完整的備份。
- 差異備份：備份自從上次完全備份之后被修改過(guò)的文件。
- 增量備份：只有在上次完全備份或者增量備份后被修改的文件才會(huì)被備份。
?
三、常見的備份方法
物理冷備： （完全備份）
- 備份時(shí)數(shù)據(jù)庫(kù)處于關(guān)閉狀態(tài)，直接打包數(shù)據(jù)庫(kù)文件
- 備份速度快，恢復(fù)時(shí)也是最簡(jiǎn)單的
專用備份工具mydump或mysqlhotcopy （完全備份，邏輯備份）
- mysqldump常用的邏輯備份工具 （導(dǎo)出為sql腳本）
- mysqlhotcopy僅擁有備份MyISAM和ARCHIVE表
啟用二進(jìn)制日志進(jìn)行增量備份 （增量備份）
- 進(jìn)行增量備份，需要刷新二進(jìn)制日志
第三方工具備份
- 免費(fèi)的MySQL熱備份軟件Percona XtraBackup
（阿里云的工具：dts，支持熱遷移）
四、MySQL完全備份介紹
4.1 什么是完全備份
- 完全備份是對(duì)整個(gè)數(shù)據(jù)庫(kù)、數(shù)據(jù)庫(kù)結(jié)構(gòu)和文件結(jié)構(gòu)的備份
- 保存的是備份完成時(shí)刻的數(shù)據(jù)庫(kù)
- 是差異備份與增量備份的基礎(chǔ)
4.2 完全備份的優(yōu)缺點(diǎn)
優(yōu)點(diǎn)：
- 備份與恢復(fù)操作簡(jiǎn)單方便
缺點(diǎn)：
- 數(shù)據(jù)存在大量的重復(fù)
- 占用大量的備份空間
- 備份與恢復(fù)時(shí)間長(zhǎng)
4.3 完全備份的方法
1）物理冷備份與恢復(fù)
- 關(guān)閉MySQL數(shù)據(jù)庫(kù)
- 使用tar命令直接打包數(shù)據(jù)庫(kù)文件夾
- 直接替換現(xiàn)有MySQL目錄即可
2）mysqldump備份與恢復(fù)
- MySQL自帶的備份工具，可方便實(shí)現(xiàn)對(duì)MySQL的備份
- 可以將指定的庫(kù)、表導(dǎo)出為SQL腳本
- 使用命令mysq|導(dǎo)入備份的數(shù)據(jù)
?
五、完全備份與恢復(fù) 操作演示
5.1 物理冷備份
5.1.1 完全備份
先關(guān)閉數(shù)據(jù)庫(kù)，之后打包備份
?systemctl stop mysqld ? ? ? ? #先關(guān)閉服務(wù)
?mkdir /backup/ ? ? ? ? ? ? ? ?#創(chuàng)建備份目錄
??
?rpm -q xz ? ? ? ? ? ? ? ? ? ? #使用xz工具進(jìn)行壓縮，檢查xz工具是否已安裝
?yum install xz -y ? ? ? ? ? ? #如果沒安裝，可以先yum安裝
?tar Jcf /backup/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data ?#打包數(shù)據(jù)庫(kù)文件。/usr/local/mysql/data 為數(shù)據(jù)庫(kù)文件存放目錄
??
?cd /backup/ ? ? ? ?#切換到備份目錄
?ls ? ? ? ? ? ? ? ? #查看目錄內(nèi)容
?tar tf mysql_all_2022-06-05.tar.xz ? ?#查看tar包內(nèi)的文件
?
5.1.2 完全恢復(fù)
將數(shù)據(jù)庫(kù)遷移到另一臺(tái)主機(jī)zy2，測(cè)試完全恢復(fù)。
?#主機(jī)A，使用scp命令將tar包傳給另一臺(tái)主機(jī)B
scp /backup/mysql_all_2023-05-09.tar.xz 192.168.126.22:/opt
??
?##主機(jī)B的操作##
?systemctl stop mysqld ? ? #關(guān)閉mysql
?cd /opt/
tar Jxf mysql_all_2023-05-09.tar.xz
cd /usr/local/mysql/
mkdir bak
mv data bak
cd /opt/usr/local/mysql/
mv data /usr/local/mysql/
??
?systemctl start mysqld ? ? ? #啟動(dòng)mysql
?mysql -u root -p ? ? ? ? ? ? #登錄數(shù)據(jù)庫(kù)查看
?show databases;
?use ky;
?show tables;
?select * from t1;
?
?
5.2 邏輯備份（使用mysqldump工具）
mysqldump是常用的邏輯備份工具。
mysqldump可以將指定的庫(kù)、表導(dǎo)出為SQL腳本。
5.2.1 mysqldump完全備份
1）完全備份一個(gè)或多個(gè)完整的庫(kù)（包括庫(kù)中所有的表）
?mysqldump -uroot -p[密碼] --databases 庫(kù)名1 [庫(kù)名2].. >/備份路徑/備份文件名.sql#導(dǎo)出的就是數(shù)據(jù)庫(kù)腳本文件
??
?示例:
?mysqldump -uroot -pabc123 --databases ky > /opt/ky_bak/ky.sql ? ?#完全備份一個(gè)庫(kù)yuji
?mysqldump -uroot -pabc123 --databases ky market > /opt/ky_bak/ky-market.sql ? #完全備份多個(gè)庫(kù)，ky庫(kù)和market庫(kù)
?2) 完全備份MySQL服務(wù)器中所有的庫(kù)（包括庫(kù)中所有的表）
?mysqldump -u root -p[密碼] --all-databases > / 備份路徑/備份文件名.sql
??
?示例:
?mysqldump -u root -p --all-databases > /opt/ky_bak/all.sql ? #完全備份所有的庫(kù)
?3）完全備份指定庫(kù)中的部分表
注意：只備份表，sql語(yǔ)句中只有對(duì)表的操作，沒有對(duì)庫(kù)的操作?；謴?fù)時(shí)需要人為確認(rèn)庫(kù)存在。
mysqldump -u root -p[密碼] [-d] 庫(kù)名 表名1 [表名2] ... > /備份路徑/備份文件名.sql
#使用“-d"選項(xiàng)，說(shuō)明只保存數(shù)據(jù)庫(kù)的表結(jié)構(gòu)
#不使用“-d”選項(xiàng)，說(shuō)明表數(shù)據(jù)也進(jìn)行備份
mysqldump -uroot -pabc123 ky t1 member > /opt/ky_bak/t1_member.sql
#完全備份ky庫(kù)中的t1表和member表
4）查看備份文件?
備份文件中保存的是sql語(yǔ)句。即以sql語(yǔ)句的形式，把庫(kù)、表結(jié)構(gòu)、表數(shù)據(jù)保存下來(lái)。
cd /opt/ky_bak
cat ky.sql | grep -v "^--" |grep -v "^/" |grep -v "^$"
5.2.2 mysqldump完全恢復(fù)?
先啟動(dòng)mysql:systemctl start mysqld
1）恢復(fù)數(shù)據(jù)庫(kù)
先刪除數(shù)據(jù)庫(kù)，之后進(jìn)行恢復(fù)。
?##刪除數(shù)據(jù)庫(kù)yuji##
?mysql -u root -p -e 'drop database ky;'
?#"-e"選項(xiàng)，用于指定連接MySQL后執(zhí)行的命令，命令執(zhí)行完后自動(dòng)退出
??
?mysql -u root -p -e 'SHOW DATABASES;' ? ? ? ? #查看當(dāng)前有哪些數(shù)據(jù)庫(kù)
??
?##恢復(fù)數(shù)據(jù)庫(kù)yuji##
?mysql -u root -p < /opt/ky_bak/ky.sql ? ? #重定向輸入庫(kù)文件
??
?mysql -u root -p -e 'SHOW DATABASES;' ? ? ? #查看當(dāng)前有哪些數(shù)據(jù)庫(kù)
?
2）恢復(fù)數(shù)據(jù)表
當(dāng)備份文件中只包含表的備份，而不包含創(chuàng)建的庫(kù)的語(yǔ)句時(shí)，執(zhí)行導(dǎo)入操作時(shí)必須指定庫(kù)名，且目標(biāo)庫(kù)必須存在。
?##備份ky庫(kù)中的class表##
?mysqldump -uroot -p ky t1 > /opt/ky_bak/ky_t1.sql
??
?##刪除ky庫(kù)中的t1表##
?mysql -u root -p -e 'drop table ky.t1;'
??
?mysql -u root -p -e 'show tables from ky;' ? ? #查看ky庫(kù)中的表，已無(wú)t1表
??
?##恢復(fù)ky庫(kù)中的t1表##
?mysql -u root -p ky < /opt/ky_bak/ky_t1.sql ? #重定向?qū)雮浞菸募仨氈付◣?kù)名，且目標(biāo)庫(kù)必須存在
??
?mysql -u root -p -e 'show tables from ky;'
?
六、MySQL增量備份與恢復(fù)?
6.1 MySQL增量備份介紹
使用mysqldump進(jìn)行完全備份存在的問題
- 備份數(shù)據(jù)中有重復(fù)數(shù)據(jù)
- 備份時(shí)間與恢復(fù)時(shí)間過(guò)長(zhǎng)
增量備份是什么：
- 是自上一次備份后增加/變化的文件或者內(nèi)容
增量備份的特點(diǎn)
- 沒有重復(fù)數(shù)據(jù)，備份量不大，時(shí)間短
- 恢復(fù)需要上次完全備份及完全備份之后所有的增量備份才 能恢復(fù)，而且要對(duì)所有增量備份進(jìn)行逐個(gè)反推恢復(fù)
6.2 MySQL增量備份的方法
-
MySQL沒有提供直接的增量備份方法
-
可通過(guò)MySQL提供的二進(jìn)制日志間接實(shí)現(xiàn)增量備份
-
MySQL二進(jìn)制日志對(duì)備份的意義
- 二進(jìn)制日志保存了所有更新或者可能更新數(shù)據(jù)庫(kù)的操作
- 二進(jìn)制日志在啟動(dòng)MySQL服務(wù)器后開始記錄，并在文件達(dá)到 max_binlog_size所設(shè)置的大小或者接收到flush logs命令后重新 創(chuàng)建新的日志文件
- 只需定時(shí)執(zhí)行flush logs方法重新創(chuàng)建新的日志，生成二進(jìn)制文 件序列，并及時(shí)把這些日志保存到安全的地方就完成了一個(gè)時(shí)間 段的增量備份
6.3 MySQL數(shù)據(jù)庫(kù)增量恢復(fù)
-
一般恢復(fù)
- 將所有備份的二進(jìn)制日志內(nèi)容全部恢復(fù)
-
基于位置恢復(fù)
- 數(shù)據(jù)庫(kù)在某一時(shí)間點(diǎn)可能既有錯(cuò)誤的操作也有正確的操作
- 可以基于精準(zhǔn)的位置跳過(guò)錯(cuò)誤的操作
-
基于時(shí)間點(diǎn)恢復(fù)
- 跳過(guò)某個(gè)發(fā)生錯(cuò)誤的時(shí)間點(diǎn)實(shí)現(xiàn)數(shù)據(jù)恢復(fù)
七、增量備份與恢復(fù) 操作演示
7.1 增量備份
1、開啟二進(jìn)制日志功能
vim /etc/my.cnf
?[mysqld]
?log-bin=mysql-bin ? #開啟二進(jìn)制日志。如果使用相對(duì)路徑，則保存在/usr/local/mysql/data/目錄下
?binlog_format = MIXED ? ? #可選，指定二進(jìn)制日志(binlog)的記錄格式為MIXED混合模式
?server-id = 1
??
?systemctl restart mysqld
ls /usr/local/mysql//data #查看是否生成二進(jìn)制日志文件
??
??
?-------------- 以下是注釋 -----------------
?#二進(jìn)制日志(binlog)有3種不同的記錄格式:
STATEMENT (基于SQL語(yǔ)句)、ROW(基于行)、MIXED(混合模式)，默認(rèn)格式是STATEMENT
??
?STATEMENT (基于SQL語(yǔ)句)：記錄修改的sql語(yǔ)句。高并發(fā)的情況下，記錄操作的sql語(yǔ)句時(shí)可能順序會(huì)有錯(cuò)誤，導(dǎo)致恢復(fù)數(shù)據(jù)時(shí)，數(shù)據(jù)丟失或有誤差。效率高，但數(shù)據(jù)可能有誤差。
??
?ROW(基于行)：記錄每一行數(shù)據(jù)，準(zhǔn)確，但恢復(fù)時(shí)效率低。
??
?MIXED(混合模式)：正常情況下使用STATEMENT，高并發(fā)的情況下會(huì)智能地切換到ROW。
?2、可每周對(duì)數(shù)據(jù)庫(kù)或表進(jìn)行完全備份
mkdir bak
mysqldump -u root -p ky t1 > /bak/ky_t1_$(date +%F).sql
mysqldump -u root -p --databases ky > /bak/ky_$(date +%F).sql
3、可每天進(jìn)行增量備份操作，生成新的二進(jìn)制日志文件（例如mysql-bin.000002）
mysqladmin -u root -p flush-logs
4、插入新數(shù)據(jù)，以模擬數(shù)據(jù)的增加或變更
use ky;
insert into t1 values(7,'fff',20);
insert into t1 values(8,'ggg',20);
?
5、再次生成新的二進(jìn)制日志文件（例如mysql -bin.000003）
mysqladmin -u root -p flush-logs
?
#之前的步驟4的數(shù)據(jù)庫(kù)操作會(huì)保存到mysql-bin.000002文件中，之后數(shù)據(jù)庫(kù)數(shù)據(jù)再發(fā)生變化則保存在mysql-bin.00003文件中
?6、查看二進(jìn)制日志文件的內(nèi)容
?cp /usr/local/mysql/data/mysql-bin.000002 /bak/
?mysqlbinlog --no-defaults --base64-output=decode-rows -v /bak/mysql-bin.000002
??
?#--base64-output=decode-rows：使用64位編碼機(jī)制去解碼并按行讀取
?#-v：顯示詳細(xì)內(nèi)容
7.2 增量恢復(fù)
-
一般恢復(fù)
- 將所有備份的二進(jìn)制日志內(nèi)容全部恢復(fù)
-
基于位置恢復(fù)
- 數(shù)據(jù)庫(kù)在某一時(shí)間點(diǎn)可能既有錯(cuò)誤的操作也有正確的操作
- 可以基于精準(zhǔn)的位置跳過(guò)錯(cuò)誤的操作
-
基于時(shí)間點(diǎn)恢復(fù)
- 跳過(guò)某個(gè)發(fā)生錯(cuò)誤的時(shí)間點(diǎn)實(shí)現(xiàn)數(shù)據(jù)恢復(fù)
7.2.1 一般恢復(fù)
將所有備份的二進(jìn)制日志內(nèi)容全部恢復(fù)。
1、模擬丟失更改的數(shù)據(jù)的恢復(fù)步驟。
?use ky;
?delete from t1 where id=3; ? #刪除今天新增加的兩條數(shù)據(jù)
?delete from t1 where id=4;
??
?#增量恢復(fù)（今天新增加的兩條數(shù)據(jù)記錄保存在mysql-bin.000002日志中）
?mysqlbinlog --no-defaults /bak/mysql-bin.000002 | mysql -u root -p
??
?mysql -u root -p -e 'select * from ky.t1;' ? #查看表中數(shù)據(jù)
?2、模擬丟失表中所有數(shù)據(jù)的恢復(fù)步驟。
?mysql -u root -p -e 'drop table ky.t1;' ? #刪除整個(gè)t1表
?mysql -u root -p -e 'show tables from ky;' ? #查看ky庫(kù)中的表
??
?#先完全恢復(fù)歷史數(shù)據(jù)
?mysql -uroot -pabc123 ky < /bak/ky_t1_2023-05-09.sql ?
?#再進(jìn)行增量恢復(fù)今天新增的2條數(shù)據(jù)（mysql-bin.000002日志中保存了今天新增加的兩條數(shù)據(jù)記錄）
?mysqlbinlog --no-defaults /bak/mysql-bin.000002 | mysql -u root -p
??
?mysql -uroot -pabc123 -e 'select * from ky.t1;' ? #查看表中數(shù)據(jù)
?
7.2.2 斷點(diǎn)恢復(fù)
向class表中插入4條數(shù)據(jù)，之后刷新二進(jìn)制日志，移動(dòng)前一個(gè)日志：
?
use ky;
insert into t1 values(9,'qqq',21);
insert into t1 values(10,'www',22);
?mysqladmin -uroot -pabc123 flush-logs; ? ? ? ?#刷新日志，生成新的二進(jìn)制日志
?cp /usr/local/mysql/data/mysql-bin.000003 /bak/ ? #將前一個(gè)日志復(fù)制到/bak/目錄下
??
?mysqlbinlog --no-defaults --base64-output=decode-rows -v /bak/mysql-bin.000003 > /bak/binlog.txt ?#將二進(jìn)制文件內(nèi)容重定向到binlog.txt文件中
?1）基于位置的斷點(diǎn)恢復(fù)
?mysqlbinlog --no-defaults --start-position='位置點(diǎn)' 文件名 | mysql -uroot -pabc123
?#從某一個(gè)位置點(diǎn)開始恢復(fù)，一直到日志結(jié)尾
??
?mysqlbinlog --no-defaults --stop-position='位置點(diǎn)' 文件名 | mysql -uroot -pabc123
?#從日志開頭，一直恢復(fù)到某一個(gè)位置點(diǎn)前結(jié)束
??
?mysqlbinlog --no-defaults --start-position='xxx'--stop-position='位置點(diǎn)' 文件名 | mysql -u root -pabc123
?#從某一個(gè)位置點(diǎn)開始恢復(fù)，一直到某一個(gè)位置點(diǎn)前結(jié)束
use ky;
?delete from t1 where id between 9 and 10; #刪除id為9到10的數(shù)據(jù)記錄
??
?cat /bak/binlog.txt ? ?#查看二進(jìn)制文件內(nèi)容，找到id=9的位置點(diǎn)
??
?#從id=9的位置點(diǎn)3308一直恢復(fù)到結(jié)尾
?mysqlbinlog --no-defaults --start-position='3308' /bak/mysql-bin.000003 | mysql -uroot -pabc123
??
?mysql -uroot -pabc123 -e 'select * from ky.t1;' #查看表數(shù)據(jù)
?
#從頭開始恢復(fù)，在"id=9"的SQL語(yǔ)句后面截止
mysqlbinlog --no-defaults --stop-position='3308' /bak/mysql-bin.000003 | mysql -uroot -pabc123
#恢復(fù)某一段數(shù)據(jù)
mysqlbinlog --no-defaults --start-position='位置節(jié)點(diǎn)號(hào)' --stop-position='位置節(jié)點(diǎn)號(hào)' /bak/mysql-bin.000003 | mysql -uroot -pabc123?同樣基于時(shí)間點(diǎn)的斷點(diǎn)恢復(fù)，注意：日期必須是"yyyy-mm-dd hh:mm:ss"的格式
?mysqlbinlog --no-defaults --start-datetime='yyyy-mm-dd hh:mm:ss' 文件名 | mysql -uroot -pabc123
?#從某一個(gè)時(shí)間點(diǎn)開始恢復(fù)，一直到日志結(jié)尾
??
?mysqlbinlog --no-defaults --stop-datetime='yyyy-mm-dd hh:mm:ss' 文件名 | mysql -uroot -pabc123
?#從日志開頭，一直恢復(fù)到某一個(gè)時(shí)間點(diǎn)前結(jié)束
??
?mysqlbinlog --no-defaults --start-datetime='yyyy-mm-dd hh:mm:ss' --stop-datetime'yyyy-mm-dd hh:mm:ss' 文件名 | mysql -uroot -pabc123
?#從某一個(gè)時(shí)間點(diǎn)開始恢復(fù)，一直到某一個(gè)位置點(diǎn)前結(jié)束
八、使用腳本備份
-
使用腳本每周三進(jìn)行一次完全備份，之后每天進(jìn)行增量備份。
- 如果數(shù)據(jù)量很大，可以一周做2次完全備份，比如星期一和星期四各一次。
-
增量備份減少磁盤空間的占用，但恢復(fù)起來(lái)比較麻煩。需要一個(gè)一個(gè)恢復(fù)。
-
一般使用crontab在凌晨進(jìn)行備份。文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-762762.html
獲取最新的二進(jìn)制文件名稱
查看索引文件，可獲取最新的二進(jìn)制文件文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-762762.html
tail -1 /usr/local/mysql/data/mysql-bin.index
或者
sed -n '$p' /usr/local/mysql/data/mysql-bin.index完全備份腳本（每周三完全備份一次）
?vim /opt/mysqlquan.sh ? ? ?#編寫腳本
?#!/bin/bash
?mysqldump -uroot -pabc123 --all-databases > /bak/all_$(date +%F).sql
??
?chmod 700 /opt/mysqlquan.sh ? ?
?crontab -e ? ? ? ? ? ? ? ? ? ? ? ?#定時(shí)任務(wù)執(zhí)行腳本
?00 1 * * 3 /opt/mysqlquan.sh ? ? #每周三凌晨1點(diǎn)備份一次
增量備份腳本（每天凌晨3點(diǎn)備份一次）
?vim /opt/binlog.sh
?#!/bin/bash
?#使用sed命令打印二進(jìn)制日志文件最后一行內(nèi)容作為filename變量的值
?filename=$(sed -n '$p' /usr/local/mysql/data/mysql-bin.index | awk -F '/' '{print $2}')
?
#將二進(jìn)制日志移動(dòng)到備份目錄下，并將移動(dòng)后的文件名稱加上日期
?mv /usr/local/mysql/data/$filename /bak/binlog_$(date +%F)
?
#生成新的二進(jìn)制日志文件
?mysqladmin -uroot -pabc123 flush-logs
??
?chmod 700 /opt/binlog.sh
?crontab -e
?00 1 * * 3 ? /opt/mysqlquan.sh ? ?#完全備份，每周三凌晨1點(diǎn)備份一次 ?
?00 3 * * * ? /opt/binlog.sh ? ? ? #增量備份，每天凌晨3點(diǎn)備份一次
到了這里，關(guān)于MySQL數(shù)據(jù)庫(kù)的備份與恢復(fù)的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
