目錄
一、理論
1.數(shù)據(jù)備份
2.完全備份與恢復(fù)
3.完全備份與恢復(fù)應(yīng)用
4.增量備份與恢復(fù)
5.增量備份與恢復(fù)應(yīng)用
6.使用腳本備份
7.日志管理
二、實(shí)驗(yàn)
1.完全備份與恢復(fù)
2.增量備份與恢復(fù)
3.使用腳本備份
三、問題
1.mysqldump報(bào)錯(cuò)
四、總結(jié)
一、理論
1.數(shù)據(jù)備份
（1）重要性
①?備份的主要目的是災(zāi)難恢復(fù)
②?在生產(chǎn)環(huán)境中，數(shù)據(jù)的安全性至關(guān)重要
③?任何數(shù)據(jù)的丟失都可能產(chǎn)生嚴(yán)重的后果
④?造成數(shù)據(jù)丟失的原因：
? ? ?1）程序錯(cuò)誤
? ? ?2）人為操作錯(cuò)誤
? ? ?3）運(yùn)算錯(cuò)誤
? ? ?4）磁盤故障
? ? ?5）災(zāi)難（如火災(zāi)、地震）和盜竊
（2）分類
①從物理與邏輯的角度
? ?1）物理備份： 對數(shù)據(jù)庫操作系統(tǒng)的物理文件（如數(shù)據(jù)文件、日志文件等）的備份。
按照備份時(shí)數(shù)據(jù)庫的運(yùn)行狀態(tài)，可以分為三種：
表1 物理備份方法
| 分類 | 數(shù)據(jù)庫狀態(tài) | 數(shù)據(jù)庫操作 |
| 冷備份（脫機(jī)備份） | 停庫、停服務(wù) | 不可讀不可寫，下線 |
| 溫備份：數(shù)據(jù)庫鎖定表格 | 不停庫、不停服務(wù)，會(huì)鎖表 | 可讀不可寫 |
| 熱備份（聯(lián)機(jī)備份） | 不停庫、不停服務(wù)，不會(huì)鎖表 | 可讀可寫 |
? ? ?2）邏輯備份：對數(shù)據(jù)庫邏輯組件（如：表等數(shù)據(jù)庫對象）的備份
②?從數(shù)據(jù)庫的備份策略角度
表2 備份策略角度分類
| 類型 | 功能 |
| 完全備份 | 每次對數(shù)據(jù)庫進(jìn)行完整的備份 |
| 差異備份 | 備份自從上次完全備份之后被修改過的文件 |
| 增量備份 | 只有在上次完全備份或者增量備份后被修改的文件才會(huì)被備份 |
針對上述三種備份方案，恢復(fù)數(shù)據(jù)方法：
# 1、全量備份的數(shù)據(jù)恢復(fù)
只需找出指定時(shí)間點(diǎn)的那一個(gè)備份文件即可，即只需要找到一個(gè)文件即可
# 2、差異備份的數(shù)據(jù)恢復(fù)
需要先恢復(fù)第一次備份的結(jié)果，然后再恢復(fù)最近一次差異備份的結(jié)果，即需要找到兩個(gè)文件
# 3、增量備份的數(shù)據(jù)恢復(fù)
需要先恢復(fù)第一次備份的結(jié)果，然后再依次恢復(fù)每次增量備份，直到恢復(fù)到當(dāng)前位置，即需要找到一條備份鏈
綜上，對比三種備份方案
1、占用空間：全量 > 差異 > 增量
2、恢復(fù)數(shù)據(jù)過程的復(fù)雜程度：增量 > 差異 > 全量（3）常用備份方法
針對不同的場景下, 應(yīng)該制定不同的備份策略對數(shù)據(jù)庫進(jìn)行備份：
表3 備份方法
| 備份策略 | 針對場景 |
| 直接cp,tar復(fù)制數(shù)據(jù)庫文件 | 如果數(shù)據(jù)量較小, 可以使用第一種方式, 直接復(fù)制數(shù)據(jù)庫文件 |
| mysqldump+復(fù)制BIN LOGS | 如果數(shù)據(jù)量還行, 可以使用第二種方式, 先使用mysqldump對數(shù)據(jù)庫進(jìn)行完全備份, 然后定期備份BINARY LOG達(dá)到增量備份的效果 |
| lvm2快照+復(fù)制BIN LOGS | 如果數(shù)據(jù)量一般, 而又不過分影響業(yè)務(wù)運(yùn)行, 可以使用第三種方式, 使用lvm2的快照對數(shù)據(jù)文件進(jìn)行備份, 而后定期備份BINARY LOG達(dá)到增量備份的效果 |
| xtrabackup+復(fù)制BIN LOGS** | 如果數(shù)據(jù)量很大, 而又不過分影響業(yè)務(wù)運(yùn)行, 可以使用第四種方式, 使用xtrabackup進(jìn)行完全備份后, 定期使用xtrabackup進(jìn)行增量備份或差異備份 |
? ? ?①?物理冷備： （完全備份）
? ? ? ? 備份時(shí)數(shù)據(jù)庫處于關(guān)閉狀態(tài)，直接打包數(shù)據(jù)庫文件
? ? ? ? 備份速度快，恢復(fù)時(shí)也是最簡單的
? ? ?②?專用備份工具mydump或mysqlhotcopy （完全備份，邏輯備份）
? ? ? ? mysqldump常用的邏輯備份工具 （導(dǎo)出為sql腳本）
? ? ? ? mysqlhotcopy僅擁有備份MyISAM和ARCHIVE表
? ? ?③?啟用二進(jìn)制日志進(jìn)行增量備份 （增量備份）
? ? ? ?進(jìn)行增量備份，需要刷新二進(jìn)制日志
? ? ?④??第三方工具備份
? ? ? ?免費(fèi)的MySQL熱備份軟件Percona XtraBackup
? ? （阿里云的工具：dts，支持熱遷移）
（4）mysqldump命令
① 語法
mysqldump -h 服務(wù)器 -u用戶名 -p密碼 選項(xiàng)與參數(shù) > 備份文件.sql②選項(xiàng)與參數(shù)
1、-A/--all-databases 所有庫
2、-B/--databases bbs db1 db2 多個(gè)數(shù)據(jù)庫
3、db1 數(shù)據(jù)庫名
4、db1 t1 t2 db1數(shù)據(jù)庫的表t1、t2
5、-F 備份的同時(shí)刷新binlog
6、-R 備份存儲(chǔ)過程和函數(shù)數(shù)據(jù)（如果開發(fā)寫了函數(shù)和存儲(chǔ)過程，就備，沒寫就不備）
7、--triggers 備份觸發(fā)器數(shù)據(jù)（現(xiàn)在都是開發(fā)寫觸發(fā)器）
8、-E/--events 備份事件調(diào)度器
9、-d 僅表結(jié)構(gòu)
10、-t 僅數(shù)據(jù)
11、--master-data=1 備份文件中 change master語句是沒有注釋的，默認(rèn)為1
用于已經(jīng)制作好了主從，現(xiàn)在想擴(kuò)展一個(gè)從庫的時(shí)候使用
如此備份，擴(kuò)展添加從庫時(shí)導(dǎo)入備份文件后
便不需要再加mater_pos了
change matser to
master_host='10.0.0.111'
master_user='rep'
master_password=123
master_log_pos=120
master_log_file='master-bin.000001'
12、--master-data=2 備份文件中 change master語句是被注釋的
13、--lock-all-tables 備份過程中所有表從頭鎖到尾，簡單粗暴
在mysqldump導(dǎo)出的整個(gè)過程中以read方式鎖住數(shù)據(jù)庫中所有表，類似 flush tables with read lock 的全局鎖），
這是一個(gè)全局讀鎖，只允許讀不允許寫，以此保證數(shù)據(jù)一致性。
比如當(dāng)前數(shù)據(jù)庫有如下schema：
information_schema（不會(huì)導(dǎo)出）
mysql
performance_schema（不會(huì)導(dǎo)出）
sys（不會(huì)導(dǎo)出）
test
test1
test2
那么我們在使用mysqldump導(dǎo)出時(shí)：
mysqldump --lock-all-tables --set-gtid-purged=on -AER > test.sql
指定--lock-all-tables參數(shù)，那么從一開始就對整個(gè)mysql實(shí)例加global read lock鎖。
這整個(gè)全局讀鎖會(huì)一直持續(xù)到導(dǎo)出結(jié)束。
所以在這個(gè)過程中，數(shù)據(jù)庫實(shí)際嚴(yán)格處于read only狀態(tài)。
所以導(dǎo)出的數(shù)據(jù)庫在數(shù)據(jù)一致性上是被嚴(yán)格保證的，也就是數(shù)據(jù)是一致性的。
由于這個(gè)參數(shù)會(huì)將數(shù)據(jù)庫置于read only狀態(tài)（也相當(dāng)于不可使用狀態(tài)），所以默認(rèn)不加該參數(shù)。
這相當(dāng)于脫機(jī)備份的感覺，所以生產(chǎn)數(shù)據(jù)庫的備份策略上，也很少使用該參數(shù)。
該參數(shù)本身默認(rèn)off,但使用該參數(shù)的話，也會(huì)自動(dòng)將 --single-transaction 及 --lock-tables 參數(shù)置于 off 狀態(tài),他們是互斥的。
對于支持事務(wù)的表例如InnoDB和BDB，推薦使用--single-transaction選項(xiàng)，因?yàn)樗静恍枰i定表
14、--single-transaction： 快照備份 （搭配--master-data可以做到熱備）
保證各個(gè)表具有數(shù)據(jù)一致性快照。
指定 --single-transaction 參數(shù)，那么導(dǎo)出過程中只能保證每個(gè)表的數(shù)據(jù)一致性（利用多版本特性實(shí)現(xiàn)，目前只能針對InnoDB事務(wù)表）。
比如有一個(gè)大表，mysqldump對該表的導(dǎo)出需要1分鐘，那么在這1分鐘的過程中，該表時(shí)可以被正常訪問的。
（正常訪問包括增刪改查，但是alter table等對表結(jié)構(gòu)發(fā)生更改的語句要被掛起。）
mysqldump能夠保證從開始對該表進(jìn)行導(dǎo)出，一直到對該表的導(dǎo)出結(jié)束，該表的數(shù)據(jù)都是開始的一致性數(shù)據(jù)快照狀態(tài)。
所以該參數(shù)明顯不能保證各個(gè)表之間的數(shù)據(jù)一致性（特別是外鍵約束的父表和子表之間）。
但是該參數(shù)能夠讓數(shù)據(jù)庫處于可使用（就是應(yīng)用感覺數(shù)據(jù)庫可用）狀態(tài)，相當(dāng)于聯(lián)機(jī)備份，所以被經(jīng)常使用。
該參數(shù)默認(rèn)off。
15、--lock-tables：如果是備份所有庫，那么備份到某個(gè)庫時(shí)只鎖某個(gè)庫，其他庫可寫，而--lock-all-tables是從始自終都全都鎖定
保證各個(gè)schema具有數(shù)據(jù)一致性快照。
指定 --lock-tables 參數(shù)，那么在導(dǎo)出過程中能夠保證各個(gè)schema的數(shù)據(jù)一致性。
比如導(dǎo)出 cms 庫（該庫有155張表）時(shí)：
mysqldump --lock-tables --set-gtid-purged=off -ER -B cms>test.sql
從命令開始，就對 cms 庫的155張表加類似 lock table xxx read 的讀鎖。
這會(huì)導(dǎo)致在導(dǎo)出整個(gè)cms庫的過程中，cms庫實(shí)際上整體處于read only狀態(tài)。
但是如果我們指定如下命令：
mysqldump --lock-tables --set-gtid-purged=on -AER >test.sql
來導(dǎo)出全部mysql庫，那么當(dāng)導(dǎo)出cms庫的過程中，其他 schema 實(shí)際上是可以被正常訪問的。
這個(gè)正常訪問就是可以接受所有合法的sql語句。
所以該參數(shù)只能保證各個(gè)schema自己的數(shù)據(jù)一致性快照。
該參數(shù)默認(rèn)on。
#==========完整語句
mysqldump -uroot -pEgon@123 -A -E -R --triggers --master-data=2 --single-transaction > /backup/full.sql
#====文件太大時(shí)可以壓縮 gzip ，但是gzip不屬于mysql獨(dú)有的命令，可以利用管道
mysqldump -uroot -pEgon@123 -A -E -R --triggers --master-data=2 --single-transaction | gzip > /tmp/full$(date +%F).sql.gz
#====導(dǎo)出時(shí)壓縮了，導(dǎo)入時(shí)需要解壓，可以使用zcat命令，很方便
zcat /backup/full$(date +%F).sql.gz | mysql -uroot -p123（5）binlog內(nèi)容定位固定的點(diǎn)
===> 1、grep過濾
===> 2、檢查事件：依據(jù)End_log_pos的提示，來確定某一個(gè)事件的起始位置與結(jié)束位置
mysql> show binlog events in 'mybinlog.000001';
如果事件很多，可以分段查看
mysql> show binlog events in 'mybinlog.000001' limit 0,30;
mysql> show binlog events in 'mybinlog.000001' limit 30,30;
mysql> show binlog events in 'mybinlog.000001' limit 60,30;
===> 3、利用mysqlbinlog命令
生產(chǎn)中很多庫，只有一個(gè)庫的表被刪除，我不可能把所有的庫都導(dǎo)出來篩選，因?yàn)槟菢幼觔inlog內(nèi)容很多，辨別復(fù)雜度高，我們可以利用
[root@egon mysql]# mysqlbinlog -d db1 --start-position=123 --stop-position=154 mybinlog.000001 --base64-output=decode-rows -vvv | grep -v 'SET'
參數(shù)解釋：
1）-d 參數(shù)接庫名
mysqlbinlog -d database --base64-output=decode-rows -vvv mysql-bin.000002
2）--base64-output 顯示模式
3）-vvv 顯示詳細(xì)信息（6）物理備份之Xtrabackup
①簡介
Xtrabackup是由percona提供的mysql數(shù)據(jù)庫備份工具，據(jù)官方介紹，這也是世界上惟一一款開源的能夠?qū)nnodb和xtradb數(shù)據(jù)庫進(jìn)行熱備的工具。
②?特點(diǎn)
? ?1）備份過程快速、可靠；
? ?2）備份過程不會(huì)打斷正在執(zhí)行的事務(wù)；
? ?3）能夠基于壓縮等功能節(jié)約磁盤空間和流量；
? ?4）自動(dòng)實(shí)現(xiàn)備份檢驗(yàn)；
? ?5）還原速度快；
③使用xtrabackup使用InnoDB能夠發(fā)揮其最大功效, 并且InnoDB的每一張表必須使用單獨(dú)的表空間, 我們需要在配置文件中添加?innodb_file_per_table = ON?來開啟
④安裝
版本選擇
mysql 5.7以下版本，可以采用percona xtrabackup 2.4版本
mysql 8.0以上版本，可以采用percona xtrabackup 8.0版本，xtrabackup8.0也只支持mysql8.0以上的版本
比如，接觸過一些金融行業(yè)，mysql版本還是多采用mysql 5.7，當(dāng)然oracle官方對于mysql 8.0的開發(fā)支持力度日益加大，新功能新特性迭代不止。生產(chǎn)環(huán)境采用mysql 8.0的版本比例會(huì)日益增加。（7）存儲(chǔ)引擎支持備份情況
| 備份類型 | MyISAM | InnoDB |
| 熱備 | × | √ |
| 溫備 | √ | √ |
| 冷備 | √ | √ |
2.完全備份與恢復(fù)
（1）概念
完全備份是對整個(gè)數(shù)據(jù)庫、數(shù)據(jù)庫結(jié)構(gòu)和文件結(jié)構(gòu)的備份；
保存的是備份完成時(shí)刻的數(shù)據(jù)庫；
是差異備份與增量備份的基礎(chǔ)。
（2）優(yōu)缺點(diǎn)
①優(yōu)點(diǎn)
備份與恢復(fù)操作簡單方便。
②缺點(diǎn)
數(shù)據(jù)存在大量的重復(fù)；
占用大量的備份空間；
備份與恢復(fù)時(shí)間長。
（3）完全備份方法
? ? ? 1）物理冷備份與恢復(fù)
- 關(guān)閉MySQL數(shù)據(jù)庫
- 使用tar命令直接打包數(shù)據(jù)庫文件夾
- 直接替換現(xiàn)有MySQL目錄即可
? ? ?2）mysqldump備份與恢復(fù)
- MySQL自帶的備份工具，可方便實(shí)現(xiàn)對MySQL的備份
- 可以將指定的庫、表導(dǎo)出為SQL腳本
- 使用命令mysq|導(dǎo)入備份的數(shù)據(jù)
3.完全備份與恢復(fù)應(yīng)用
（1）物理冷備份
①完全備份
先關(guān)閉數(shù)據(jù)庫，之后打包備份
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 /var/lib/mysql #打包數(shù)據(jù)庫文件。 /usr/local/mysql/data 為編譯安裝數(shù)據(jù)庫文件存放目錄， /var/lib/mysql 為yum安裝數(shù)據(jù)庫文件存放目錄。
?
cd /backup/ #切換到備份目錄
ls #查看目錄內(nèi)容
tar tf mysql_all_2023-07-17.tar.xz #查看tar包內(nèi)的文件
②完全恢復(fù)
將數(shù)據(jù)庫遷移到另一臺(tái)主機(jī)，測試完全恢復(fù)。
#主機(jī)A，使用scp命令將tar包傳給另一臺(tái)主機(jī)B
scp /backup/mysql_all_2023-07-17.tar.xz 192.168.204.11:/opt
?
##主機(jī)B的操作##
systemctl stop mysqld #關(guān)閉mysql
cd /opt/
mkdir /opt/bak/var/lib #創(chuàng)建備份目錄
tar Jxf mysql_all_2023-07-17.tar.xz -C /opt/bak/ #將tar包解壓到備份目錄
cd /opt/bak/ #切換到tar包的解壓目錄
\cp -af mysql/ /var/lib/mysql #將data目錄復(fù)制到/var/lib/mysql/目錄下，覆蓋原有文件
?
systemctl start mysqld #啟動(dòng)mysql
mysql -u root -p #登錄數(shù)據(jù)庫查看
show databases;
use school;
show tables;
select * from student;
（2）邏輯備份（使用mysqldump工具）
①mysqldump完全備份
1）完全備份一個(gè)或多個(gè)完整的庫（包括庫中所有的表）
mysqldump -uroot -p[密碼] --databases 庫名1 [庫名2].. >/備份路徑/備份文件名.sql#導(dǎo)出的就是數(shù)據(jù)庫腳本文件
?
示例:
mysqldump -u root -p --databases school > /opt/mysql_bak/school.sql #完全備份一個(gè)庫
mysqldump -u root -p --databases mysql school > /opt/mysql_bak/mysql-school.sql #完全備份多個(gè)庫，school庫和mysql庫
2）完全備份MySQL服務(wù)器中所有的庫（包括庫中所有的表）
mysqldump -u root -p[密碼] --all-databases > / 備份路徑/備份文件名.sql
?
示例:
mysqldump -u root -p --all-databases > /opt/mysql_bak/all.sql #完全備份所有的庫
3）完全備份指定庫中的部分表
注意：只備份表，sql語句中只有對表的操作，沒有對庫的操作?；謴?fù)時(shí)需要人為確認(rèn)庫存在。
mysqldump -u root -p[密碼] [-d] 庫名 表名1 [表名2] ... > /備份路徑/備份文件名.sql
#使用“-d"選項(xiàng)，說明只保存數(shù)據(jù)庫的表結(jié)構(gòu)
#不使用“-d”選項(xiàng)，說明表數(shù)據(jù)也進(jìn)行備份
示例：
mysqldump -u root -p school student > /opt/mysql_bak/school_student.sql
#完全備份school庫中的student表
4）查看備份文件
備份文件中保存的是sql語句。即以sql語句的形式，把庫、表結(jié)構(gòu)、表數(shù)據(jù)保存下來。
cd /opt/mysql_bak
cat school.sql | grep -v "^--" |grep -v "^/" |grep -v "^$"
②?mysqldump完全恢復(fù)
先啟動(dòng)mysql
systemctl start mysqld
1）恢復(fù)數(shù)據(jù)庫
先刪除數(shù)據(jù)庫，之后進(jìn)行恢復(fù)。
##刪除數(shù)據(jù)庫zzq好帥##
mysql -u root -p -e 'drop database school;'
#"-e"選項(xiàng)，用于指定連接MySQL后執(zhí)行的命令，命令執(zhí)行完后自動(dòng)退出
?
mysql -u root -p -e 'SHOW DATABASES;' #查看當(dāng)前有哪些數(shù)據(jù)庫
?
##恢復(fù)數(shù)據(jù)庫zzq好帥##
mysql -u root -p < /opt/mysql_bak/school.sql #重定向輸入庫文件
?
mysql -u root -p -e 'SHOW DATABASES;' #查看當(dāng)前有哪些數(shù)據(jù)庫
2）恢復(fù)數(shù)據(jù)表
當(dāng)備份文件中只包含表的備份，而不包含創(chuàng)建的庫的語句時(shí)，執(zhí)行導(dǎo)入操作時(shí)必須指定庫名，且目標(biāo)庫必須存在。
##備份school庫中的student表##
mysqldump -uroot -p school student > /opt/mysql_bak/school_student.sql
?
##刪除school庫中的student表##
mysql -u root -p -e 'drop table school.student;'
?
mysql -u root -p -e 'show tables from school;' #查看school庫中的表，已無student表
?
##恢復(fù)school庫中的school表##
mysql -u root -p school < /opt/mysql_bak/school_student.sql #重定向?qū)雮浞菸募?，必須指定庫名，且目?biāo)庫必須存在
?
mysql -u root -p -e 'show tables from school;'
4.增量備份與恢復(fù)
（1）概念
是自上一次備份后增加/變化的文件或者內(nèi)容。
（2）特點(diǎn)
沒有重復(fù)數(shù)據(jù)，備份量不大，時(shí)間短；
恢復(fù)需要上次完全備份及完全備份之后所有的增量備份才 能恢復(fù)，而且要對所有增量備份進(jìn)行逐個(gè)反推恢復(fù)。
（3）增量備份方法
①MySQL沒有提供直接的增量備份方法
②可通過MySQL提供的二進(jìn)制日志間接實(shí)現(xiàn)增量備份
③MySQL二進(jìn)制日志對備份的意義：
? ? 1）二進(jìn)制日志保存了所有更新或者可能更新數(shù)據(jù)庫的操作
? ? 2）二進(jìn)制日志在啟動(dòng)MySQL服務(wù)器后開始記錄，并在文件達(dá)到 max_binlog_size所設(shè)置的大小或者接收到flush logs命令后重新 創(chuàng)建新的日志文件
? ? 3）只需定時(shí)執(zhí)行flush logs方法重新創(chuàng)建新的日志，生成二進(jìn)制文 件序列，并及時(shí)把這些日志保存到安全的地方就完成了一個(gè)時(shí)間 段的增量備份
?
（4）增量恢復(fù)方法
①?一般恢復(fù)
將所有備份的二進(jìn)制日志內(nèi)容全部恢復(fù)
②基于位置恢復(fù)
數(shù)據(jù)庫在某一時(shí)間點(diǎn)可能既有錯(cuò)誤的操作也有正確的操作；
可以基于精準(zhǔn)的位置跳過錯(cuò)誤的操作。
③基于時(shí)間點(diǎn)恢復(fù)
跳過某個(gè)發(fā)生錯(cuò)誤的時(shí)間點(diǎn)實(shí)現(xiàn)數(shù)據(jù)恢復(fù)。
5.增量備份與恢復(fù)應(yīng)用
（1）增量備份
①開啟二進(jìn)制日志功能
vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin #開啟二進(jìn)制日志。如果使用相對路徑，則保存在/var/lib/mysql目錄下
binlog_format = MIXED #可選，指定二進(jìn)制日志(binlog)的記錄格式為MIXED
server-id = 1
?
systemctl restart mysqld
ls -l /usr/bin/mysql*
?
?
-------------- 以下是注釋 -----------------
#二進(jìn)制日志(binlog)有3種不同的記錄格式:
STATEMENT (基于SQL語句)、ROW(基于行)、MIXED(混合模式)，默認(rèn)格式是STATEMENT
?
STATEMENT (基于SQL語句)：記錄修改的sql語句。高并發(fā)的情況下，記錄操作的sql語句時(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。?
②可每周對數(shù)據(jù)庫或表進(jìn)行完全備份
mysqldump -u root -p school student > /bak/school_student_$(date +%F).sql
mysqldump -u root -p --databases school > /bak/school_$(date +%F).sql
③可每天進(jìn)行增量備份操作，生成新的二進(jìn)制日志文件（例如mysql-bin.000002）
mysqladmin -u root -p flush-logs
④插入新數(shù)據(jù)，以模擬數(shù)據(jù)的增加或變更
use school;
INSERT INTO student VALUES(6, 'zhouliu',16, 666666, 'super member');
⑤再次生成新的二進(jìn)制日志文件（例如mysql -bin.000003）
mysqladmin -u root -p flush-logs
?
#之前的步驟4的數(shù)據(jù)庫操作會(huì)保存到mysql-bin.000002文件中，之后數(shù)據(jù)庫數(shù)據(jù)再發(fā)生變化則保存在mysql-bin.00003文件中
⑥查看二進(jìn)制日志文件的內(nèi)容
cp /var/lib/mysql/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)容
（2）增量恢復(fù)
①一般恢復(fù)
將所有備份的二進(jìn)制日志內(nèi)容全部恢復(fù)。
1）模擬丟失更改的數(shù)據(jù)的恢復(fù)步驟
use school;
delete from student where name='zhouliu'; #刪除今天新增加的數(shù)據(jù)
?
#增量恢復(fù)（今天新增加的數(shù)據(jù)記錄保存在mysql-bin.000002日志中）
mysqlbinlog --no-defaults /bak/mysql-bin.000002 | mysql -u root -p
?
mysql -u root -p -e 'select * from school.student;' #查看表中數(shù)據(jù)
2）模擬丟失表中所有數(shù)據(jù)的恢復(fù)步驟
mysql -u root -p -e 'drop table school.student;' #刪除整個(gè)zzq帥表
mysql -u root -p -e 'show tables from school;' #查看zzq好帥庫中的表
?
#先完全恢復(fù)歷史數(shù)據(jù)
mysql -u root -p school < /bak/school_student_2023-07-17.sql
#再進(jìn)行增量恢復(fù)今天新增的數(shù)據(jù)（mysql-bin.000002日志中保存了今天新增加的數(shù)據(jù)記錄）
mysqlbinlog --no-defaults /bak/mysql-bin.000002 | mysql -u root -p
?
mysql -u root -p -e 'select * from school.student;' #查看表中數(shù)據(jù)
②斷點(diǎn)恢復(fù)
向zzq帥表中插入3條數(shù)據(jù)，之后刷新二進(jìn)制日志，移動(dòng)前一個(gè)日志：
use yuji;
INSERT INTO student VALUES(7, 'guoqi',17,777777,'fuzhou', 'gold member');
INSERT INTO student VALUES(8, 'xueba',18,888888,'shenyang', 'silver member');
INSERT INTO student VALUES(9, 'hujiu',19,999999,'changchun', 'super member');
?
mysqladmin -u root -p 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 -u root -p
#從某一個(gè)位置點(diǎn)開始恢復(fù)，一直到日志結(jié)尾
?
mysqlbinlog --no-defaults --stop-position='位置點(diǎn)' 文件名 | mysql -u root -p
#從日志開頭，一直恢復(fù)到某一個(gè)位置點(diǎn)前結(jié)束
?
mysqlbinlog --no-defaults --start-position='xxx'--stop-position='位置點(diǎn)' 文件名 | mysql -u root -p
#從某一個(gè)位置點(diǎn)開始恢復(fù)，一直到某一個(gè)位置點(diǎn)前結(jié)束
示例1：
只想恢復(fù)"xueba"和"hujiu"（即恢復(fù)最后兩行，第8~9行）
use school;
delete from student where id between 7 and 9; #刪除id為7到9的數(shù)據(jù)記錄
?
cat /bak/binlog.txt #查看二進(jìn)制文件內(nèi)容，找到'xueba'的位置點(diǎn)
?
#從'xueba'的位置點(diǎn)3496一直恢復(fù)到結(jié)尾
mysqlbinlog --no-defaults --start-position='3496' /bak/mysql-bin.000003 | mysql -u root -p
?
mysql -u root -p -e 'select * from school.student;' #查看表數(shù)據(jù)
示例2：
只想恢復(fù)"王政"（即只恢復(fù)第5行）
use school;
delete from student where id between 7 and 9; #刪除id為7到9的數(shù)據(jù)記錄
?
cat /bak/binlog.txt #查看二進(jìn)制文件內(nèi)容，找到'guoqi'后面的位置點(diǎn)
?
#從頭開始恢復(fù)，在"guoqi"的SQL語句后面截止
mysqlbinlog --no-defaults --stop-position='3496' /bak/mysql-bin.000003 | mysql -u root -p
?
mysql -u root -p -e 'select * from school.student;' #查看表數(shù)據(jù)
示例3：只想恢復(fù)"李嘉欣"（即只恢復(fù)第6行）
use school;
delete from student where id between 7 and 9; #刪除id為7到9的數(shù)據(jù)記錄
?
cat /bak/binlog.txt #查看二進(jìn)制文件內(nèi)容，找到"xueba"的位置點(diǎn)
?
#從“xueba”前面的位置點(diǎn)開始恢復(fù)，一直到"xueba"后面的位置點(diǎn)
mysqlbinlog --no-defaults --start-position='3496' --stop-position='3827' /bak/mysql-bin.000003 | mysql -u root -p
?
mysql -u root -p -e 'select * from school.student;' #查看表數(shù)據(jù)
2）基于時(shí)間點(diǎn)的斷點(diǎn)恢復(fù)
注意：日期必須是"yyyy-mm-dd"的格式。
use school;
delete from student where id between 7 and 9; #刪除id為7到9的數(shù)據(jù)記錄
?
cat /bak/binlog.txt #查看二進(jìn)制文件內(nèi)容，找到"xueba"的位置點(diǎn)
?
#從“xueba”前面的位置點(diǎn)開始恢復(fù)，一直到"xueba"后面的位置點(diǎn)
mysqlbinlog --no-defaults --start-position='3496' --stop-position='3827' /bak/mysql-bin.000003 | mysql -u root -p
?
mysql -u root -p -e 'select * from school.student;' #查看表數(shù)據(jù)
示例：
只想恢復(fù)"xueba（即只恢復(fù)第8行）
use school;
delete from student where id between 7 and 9; #刪除id為7到9的數(shù)據(jù)記錄
?
cat /bak/binlog.txt #查看二進(jìn)制文件內(nèi)容，找到"李嘉欣"的時(shí)間點(diǎn)
?
#從“李嘉欣”前面的時(shí)間點(diǎn)開始恢復(fù)，一直到"李嘉欣"后面的時(shí)間點(diǎn)
mysqlbinlog --no-defaults --start-datetime='2023-07-17 19:16:21' --stop-datetime='2023-07-17 19:17:01' /bak/mysql-bin.000003 | mysql -u root -p
?
mysql -u root -p -e 'select * from school.student;' #查看表數(shù)據(jù)
6.使用腳本備份
（1）方法
①使用腳本每周三進(jìn)行一次完全備份，之后每天進(jìn)行增量備份。
如果數(shù)據(jù)量很大，可以一周做2次完全備份，比如星期一和星期四各一次。
②增量備份減少磁盤空間的占用，但恢復(fù)起來比較麻煩。需要一個(gè)一個(gè)恢復(fù)。
③一般使用crontab在凌晨進(jìn)行備份。
（2）獲取最新的二進(jìn)制文件名稱
查看索引文件，可獲取最新的二進(jìn)制文件。
tail -1 /var/lib/mysql/mysql-bin.index
或者
sed -n '$p' /var/lib/mysql/mysql-bin.index
（3）完全備份腳本（每周三完全備份一次）
vim /opt/mysqlquan.sh #編寫腳本
#!/bin/bash
mysqldump -u root -p123456 --all-databases > /bak/all_$(date +%F).sql
?
chmod 700 /opt/mysqlquan.sh
crontab -e #定時(shí)任務(wù)執(zhí)行腳本
0 1 * * 3 /opt/mysqlquan.sh #每周三凌晨1點(diǎn)備份一次
（4）增量備份腳本（每天凌晨3點(diǎn)備份一次）
vim /opt/binlog.sh
#!/bin/bash
#使用sed命令打印二進(jìn)制日志文件最后一行內(nèi)容作為filename變量的值
filename=$(sed -n '$p' /var/lib/mysql/mysql-bin.index | awk -F '/' '{print $2}')
#將二進(jìn)制日志移動(dòng)到備份目錄下，并將移動(dòng)后的文件名稱加上日期
mv /var/lib/mysql/data/$filename /bak/binlog_$(date +%F)
#生成新的二進(jìn)制日志文件
mysqladmin -uroot -p123456 flush-logs
?
chmod 700 /opt/binlog.sh
crontab -e
0 1 * * 3 /opt/mysqlquan.sh #完全備份，每周三凌晨1點(diǎn)備份一次
0 3 * * * /opt/binlog.sh #增量備份，每天凌晨3點(diǎn)備份一次
7.日志管理
（1）MySQL常用日志類型及開啟
①?錯(cuò)誤日志
指定日志的保存位置和文件名
log-error=/usr/local/mysql/data/mysql_error.log
#用來記錄當(dāng)MySQL啟動(dòng)、停止或運(yùn)行時(shí)發(fā)生的錯(cuò)誤信息，默認(rèn)已開啟②通用查詢?nèi)罩?/p>
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
#用來記錄MySQL的所有連接和語句，默認(rèn)是關(guān)閉的③二進(jìn)制日志（binlog)
log-bin=mysql-bin
或
log_bin=mysql-bin
#用來記錄所有當(dāng)MySQL啟動(dòng)、停止或運(yùn)行時(shí)發(fā)送的錯(cuò)誤信息，默認(rèn)是關(guān)閉的④慢查詢?nèi)罩?/p>
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5
systemctl restart mysqld
#用來記錄所有執(zhí)行時(shí)間超過long_query_time秒的語句，可以找到哪些查詢語句執(zhí)行時(shí)間長，以便于優(yōu)化，默認(rèn)是關(guān)閉的（2）查看日志狀態(tài)
①查看通用查詢?nèi)罩臼欠耖_啟
mysql -u root -p
show variables like 'general%'; ②查看二進(jìn)制日志是否開啟
show variables like 'log_bin%';③查看慢查詢?nèi)展δ苁欠耖_啟
show variables like '%slow%'; 查看慢查詢時(shí)間設(shè)置
show variables like 'long_query_time';在數(shù)據(jù)庫中設(shè)置開啟慢查詢的方法
set global slow_query_log=ON;
該方法重啟服務(wù)失效二、實(shí)驗(yàn)
1.完全備份與恢復(fù)
（1）物理冷備份
①完全備份
查看數(shù)據(jù)庫
查看表
先關(guān)閉數(shù)據(jù)庫，之后打包備份
②完全恢復(fù)
將數(shù)據(jù)庫遷移到另一臺(tái)主機(jī)，測試完全恢復(fù)。
?切換到另一臺(tái)主機(jī)B：
?
登錄數(shù)據(jù)庫查看
查看數(shù)據(jù)表
?
?查看表內(nèi)容
（2）邏輯備份（使用mysqldump工具）
①mysqldump完全備份
1）完全備份一個(gè)或多個(gè)完整的庫（包括庫中所有的表）
2）完全備份MySQL服務(wù)器中所有的庫（包括庫中所有的表）
3）完全備份指定庫中的部分表
注意：只備份表，sql語句中只有對表的操作，沒有對庫的操作?；謴?fù)時(shí)需要人為確認(rèn)庫存在。
示例：
?
4）查看備份文件
備份文件中保存的是sql語句。即以sql語句的形式，把庫、表結(jié)構(gòu)、表數(shù)據(jù)保存下來。
②?mysqldump完全恢復(fù)
先啟動(dòng)mysql
1）恢復(fù)數(shù)據(jù)庫
先刪除數(shù)據(jù)庫，之后進(jìn)行恢復(fù)。
?查看數(shù)據(jù)庫（school已刪除）
?重定向?qū)霂煳募⒉榭矗╯chool已恢復(fù)）
2）恢復(fù)數(shù)據(jù)表
當(dāng)備份文件中只包含表的備份，而不包含創(chuàng)建的庫的語句時(shí)，執(zhí)行導(dǎo)入操作時(shí)必須指定庫名，且目標(biāo)庫必須存在。
?重啟
2.增量備份與恢復(fù)
（1）增量備份
①?開啟二進(jìn)制日志功能
②可每周對數(shù)據(jù)庫或表進(jìn)行完全備份
③可每天進(jìn)行增量備份操作，生成新的二進(jìn)制日志文件（例如mysql-bin.000002）
?④插入新數(shù)據(jù)，以模擬數(shù)據(jù)的增加或變更
⑤再次生成新的二進(jìn)制日志文件（例如mysql -bin.000003）
?之前的步驟4的數(shù)據(jù)庫操作會(huì)保存到mysql-bin.000002文件中，之后數(shù)據(jù)庫數(shù)據(jù)再發(fā)生變化則保存在mysql-bin.00003文件中
⑥查看二進(jìn)制日志文件的內(nèi)容
（2）增量恢復(fù)
①一般恢復(fù)
將所有備份的二進(jìn)制日志內(nèi)容全部恢復(fù)。
1）模擬丟失更改的數(shù)據(jù)的恢復(fù)步驟
?刪除新增數(shù)據(jù)‘zhouliu’
?增量恢復(fù)，被刪除的數(shù)據(jù)已恢復(fù)
2）模擬丟失表中所有數(shù)據(jù)的恢復(fù)步驟
刪除student表
?完全恢復(fù)歷史數(shù)據(jù)
?再進(jìn)行增量恢復(fù)今天新增的數(shù)據(jù)（mysql-bin.000002日志中保存了今天新增加的數(shù)據(jù)記錄）
?表中數(shù)據(jù)已恢復(fù)
②斷點(diǎn)恢復(fù)
向student表中插入3條數(shù)據(jù)，之后刷新二進(jìn)制日志，移動(dòng)前一個(gè)日志：
?刷新日志生成新的二進(jìn)制日志，將前一個(gè)日志復(fù)制到/bak/目錄下
?將二進(jìn)制文件內(nèi)容重定向到binlog.txt文件中
?
1）基于位置的斷點(diǎn)恢復(fù)
示例1：
只想恢復(fù)"xueba"和"hujiu"（即恢復(fù)最后兩行，第8~9行）
?刪除id為7到9的數(shù)據(jù)記錄
查看二進(jìn)制文件內(nèi)容，找到'xueba'的位置點(diǎn)
?從'xueba'的位置點(diǎn)3496一直恢復(fù)到結(jié)尾,查看表數(shù)據(jù)
示例2：
只想恢復(fù)"guoqi"（即只恢復(fù)第7行）
?從頭開始恢復(fù)，在"guoqi"的SQL語句后面截止 ,查看表數(shù)據(jù)
示例3：只想恢復(fù)"xueba"（即只恢復(fù)第8行）
?
2）基于時(shí)間點(diǎn)的斷點(diǎn)恢復(fù)
注意：日期必須是"yyyy-mm-dd"的格式。
示例：
恢復(fù)"hujiu"（恢復(fù)第9行）
?
3.使用腳本備份
（1）完全備份腳本
vim /opt/mysqlquan.sh #編寫腳本
#!/bin/bash
mysqldump -u root -p123456 --all-databases > /bak/all_$(date +%F).sql
?
chmod 700 /opt/mysqlquan.sh
crontab -e #定時(shí)任務(wù)執(zhí)行腳本
0 1 * * 3 /opt/mysqlquan.sh #每周三凌晨1點(diǎn)備份一次
（2）增量備份腳本
vim /opt/binlog.sh
#!/bin/bash
#使用sed命令打印二進(jìn)制日志文件最后一行內(nèi)容作為filename變量的值
filename=$(sed -n '$p' /var/lib/mysql/mysql-bin.index | awk -F '/' '{print $2}')
#將二進(jìn)制日志移動(dòng)到備份目錄下，并將移動(dòng)后的文件名稱加上日期
mv /var/lib/mysql/$filename /bak/binlog_$(date +%F)
#生成新的二進(jìn)制日志文件
mysqladmin -uroot -p123456 flush-logs
?
chmod 700 /opt/binlog.sh
crontab -e
0 1 * * 3 /opt/mysqlquan.sh #完全備份，每周三凌晨1點(diǎn)備份一次
0 3 * * * /opt/binlog.sh #增量備份，每天凌晨3點(diǎn)備份一次
?
?
三、問題
1.mysqldump報(bào)錯(cuò)
（1）故障描述
在對MySQL數(shù)據(jù)庫備份時(shí)，執(zhí)行mysqldump命令，報(bào)錯(cuò)如下：mysqldump: Got error: 2002: Can’t connect to local MySQL server through sock
?（2）原因分析
上述報(bào)錯(cuò)已指明是因?yàn)閙ysql的socket接口連接不對/socket文件讀取異常，導(dǎo)致socket無法連接所致。
1）確認(rèn)當(dāng)前MySQL的socket：
2）當(dāng)沒有服務(wù)運(yùn)行時(shí)，啟動(dòng)mysqld服務(wù)
3）當(dāng)有服務(wù)運(yùn)行時(shí),用正確的socket文件連接mysql，再次執(zhí)行mysqdump命令：
mysqldump -u root -p --databases school socket=/var/lib/mysql/mysql.sock > /opt/mysql_bak/school.sql
四、總結(jié)
數(shù)據(jù)庫備份可以分為物理備份和邏輯備份。物理備份又可以成為冷備份（脫機(jī)備份）、熱備份（連接備份）和溫備份。
從數(shù)據(jù)庫的備份策略角度來看，備份又可分為完全備份、差異備份和增量備份。
全量備份的數(shù)據(jù)恢復(fù)：只需找出指定時(shí)間點(diǎn)的那一個(gè)備份文件即可，即只需要找到一個(gè)文件即可；
差異備份的數(shù)據(jù)恢復(fù)：需要先恢復(fù)第一次備份的結(jié)果，然后再恢復(fù)最近一次差異備份的結(jié)果，即需要找到兩個(gè)文件；
增量備份的數(shù)據(jù)恢復(fù)：需要先恢復(fù)第一次備份的結(jié)果，然后再依次恢復(fù)每次增量備份，直到恢復(fù)到當(dāng)前位置，即需要找到一條備份鏈；
綜上，對比三種備份方案：
占用空間：全量 > 差異 > 增量
恢復(fù)數(shù)據(jù)過程的復(fù)雜程度：增量 > 差異 > 全量
?文章來源：http://www.zghlxwxcb.cn/news/detail-577598.html
?文章來源地址http://www.zghlxwxcb.cn/news/detail-577598.html
到了這里，關(guān)于數(shù)據(jù)庫應(yīng)用：MySQL備份與恢復(fù)的文章就介紹完了。如果您還想了解更多內(nèi)容，請?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
