1防嗡、備份類型
熱備
在數(shù)據(jù)庫正常業(yè)務(wù)時(shí)所意，備份數(shù)據(jù)淮逊，并且能夠一致性恢復(fù)
冷備
關(guān)閉數(shù)據(jù)庫業(yè)務(wù)，數(shù)據(jù)庫沒有任何變更的情況下扶踊，進(jìn)行備份數(shù)據(jù)
溫備
鎖表備份泄鹏，只能查詢不能恢復(fù)
2、備份方式
2.1 邏輯備份
基于SQL語句進(jìn)行備份
常用工具：mysqldump秧耗，mysqlbinlog
2.2 物理備份
基于磁盤文件備份
常用工具：xtrabackup
3备籽、備份策略
全備：全庫備份
增量：備份變化的數(shù)據(jù)
備份周期：定期備份
4、邏輯備份工具
4.1 mysqldump
1分井、mysqldump適合小于100G以下的數(shù)據(jù)量
2车猬、mysqldump原生態(tài)不支持增量，必須配合binlog實(shí)
現(xiàn)增量
3尺锚、對(duì)于大數(shù)據(jù)量的數(shù)據(jù)庫備份诈唬，從架構(gòu)上拆分，分別進(jìn)行備份
mysqldump優(yōu)點(diǎn)：
1.備份可讀性強(qiáng)
2.文本形式備份缩麸，壓縮比較高
缺點(diǎn)：恢復(fù)時(shí)間長(zhǎng)
1) 通用參數(shù)
-u -p -S -h -p
2) 備份專用參數(shù)
* -A 全備參數(shù)
mysqldump -uroot -p -A > /backup/full.sql
* -B 指定備份多個(gè)單庫铸磅，系統(tǒng)相關(guān)的庫不用備，只備份生產(chǎn)相關(guān)庫;適合于跨版本遷移
mysqldump -uroot -p -B oldboy world student >/backup/bak.sql
* 針對(duì)表的備份杭朱；備份world庫下的city,country表
mysqldump -uroot -p world city country >/backup/bak1.sql
* 加-B與不加-B參數(shù)的區(qū)別：是加-B多出創(chuàng)建語句和use語句阅仔；不加-B則沒有，這種備份恢復(fù)時(shí)弧械，必須庫事先存在八酒，并且use進(jìn)庫，才能source恢復(fù)
3) 特殊參數(shù)使用
-R 備份存儲(chǔ)過程及函數(shù)
--triggers 備份觸發(fā)器
-E 備份事件
mysqldump -uroot -p -A -R --triggers >/backup/full.sql
-F 刷新的binlog日志刃唐；有多少個(gè)生產(chǎn)庫羞迷，就會(huì)刷新多少個(gè)新的binlog日志；備份時(shí)加入 -F參數(shù)画饥，滾動(dòng)新的binlog日志衔瓮，新的binlog的起點(diǎn)，就是下次備份起點(diǎn)抖甘；非必須
--master-data=2 以注釋的形式热鞍，保存?zhèn)浞輹r(shí)時(shí)間點(diǎn)的binlog的狀態(tài)信息（binlog文件名和位置點(diǎn)）
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000035', MASTER_LOG_POS=194;
--single-transaction 支持innodb存儲(chǔ)引擎熱備功能，此參數(shù)自動(dòng)判斷：在不加--single-transaction ，啟動(dòng)所有表的溫備份薇宠，所有表都鎖定
對(duì)innodb可以不鎖表熱備偷办，對(duì)非innodb表可以實(shí)現(xiàn)自動(dòng)鎖表功能。
--max-allowed-packet=64M //設(shè)置備份包允許的最大數(shù)據(jù)量大小澄港，非必須
mysqldump -uroot -p123 -A --master-data=2 --single-transaction -R -E --triggers --max_allowed_packet=64M >/data/backup/full.sql
例句1：mysqldump -uroot -p -A -R --triggers --master-data=2 --single-transaction >/backup/full.sql
功能：
（1） 以注釋的形式椒涯，保存?zhèn)浞蓍_始時(shí)間點(diǎn)的binlog的狀態(tài)信息
（2）對(duì)innodb可以不鎖表熱備
（3）對(duì)非innodb表可以實(shí)現(xiàn)自動(dòng)鎖表功能
例句2：mysqldump -uroot -p -A -R --triggers --master-data=2 --single-transaction |gzip >/backup/all_$(date +%F-%T).sql.gz
適合多引擎混合備份命令；
恢復(fù)：
gunzip /backup/all_2020-02-03-16:34:13.sql.gz
進(jìn)入數(shù)據(jù)庫
drop database world;
set sql_log_bin=0;
source /backup/all_2020-02-03-16:34:13.sql
注意：盡量不要在生產(chǎn)庫直接進(jìn)行恢復(fù)們可以先找一個(gè)測(cè)試庫回梧，驗(yàn)證沒問題废岂，把問題數(shù)據(jù)導(dǎo)出并導(dǎo)入生產(chǎn)庫。
報(bào)錯(cuò)：mysqldump 備份時(shí)漂辐，報(bào)錯(cuò)socket文件路徑錯(cuò)誤
解決辦法：做軟連接
ln -s 源文件 目標(biāo)文件
數(shù)據(jù)量級(jí)大問題，gzip最大包為4M棕硫，超過4M報(bào)錯(cuò)髓涯；
解決方法：服務(wù)端參數(shù)只進(jìn)不出，修改沒有用哈扮；加上允許數(shù)據(jù)包的大小參數(shù)：--max_allowed_packet=128M纬纪。
mysqldump -uroot -p -A -R --triggers --master-data=2 --single-transaction --max_allowed_packet=128M |gzip >/backup/all_$(date +%F-%T).sql.gz
4.2 面試題
你們公司數(shù)據(jù)量多大？
小數(shù)據(jù)量：30G-50G都可以滑肉。
互聯(lián)網(wǎng)公司 數(shù)據(jù)量不要超過100G包各。
zabbix除外，數(shù)據(jù)量較大靶庙。備份多長(zhǎng)時(shí)間问畅？
mysqldump 5分鐘25G
10分鐘、20分鐘六荒、半個(gè)小時(shí)护姆；備份時(shí)長(zhǎng)的主要因素是IO，sata磁盤IO速度在80M左右掏击，固態(tài)盤更塊卵皂。
備份多時(shí)間，恢復(fù)大概也要多長(zhǎng)時(shí)間砚亭。什么工具備份灯变？
mysqldump
可以簡(jiǎn)單說一下備份策略，每天全備捅膘，每小時(shí)增備添祸。
xtrabackup備份時(shí)間快于mysqldump
4.3 備份恢復(fù)案例
正在運(yùn)行的網(wǎng)站系統(tǒng)，mysql數(shù)據(jù)庫寻仗，數(shù)據(jù)量25G膝捞，日業(yè)務(wù)增量10-15M。
備份方式：每天23：00，計(jì)劃任務(wù)調(diào)用mysqldump執(zhí)行全備腳本蔬咬。
故障時(shí)間：上午10點(diǎn)鲤遥，誤刪除一個(gè)表。
恢復(fù)思路：
- 掛出維護(hù)界面
- 找測(cè)試庫
- 恢復(fù)全備到測(cè)試庫
- 截取二進(jìn)制日志到誤刪除時(shí)間點(diǎn)林艘，恢復(fù)到測(cè)試庫
起點(diǎn)：master-data=2盖奈，找備份文件，獲取到日志名字和位置號(hào)
終點(diǎn)：分析最后一個(gè)binlog狐援，找到誤刪除事件位置點(diǎn) - 驗(yàn)證數(shù)據(jù)钢坦，將故障表導(dǎo)出，導(dǎo)入會(huì)產(chǎn)生庫
- 開啟業(yè)務(wù)
模擬故障：
1啥酱、模擬全備
mysqldump -uroot -p -A -R --triggers --master-data=2 --single-transaction >/backup/all.sql
2爹凹、模擬業(yè)務(wù)
create database baidu charset utf8;
use baidu;
create table t1(id int);
insert into t1 values(1),(2),(3);
insert into t1 values(11),(12),(13);
update t1 set id=10 where id=1;
3、模擬10點(diǎn)數(shù)據(jù)誤刪除
drop database world;
drop database baidu;
4镶殷、準(zhǔn)備恢復(fù)
查看備份文件：
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000013', MASTER_LOG_POS=819739;
截取二進(jìn)制日志：
# 查看當(dāng)前binlog文件
show master status;
# 找到drop之前的位置點(diǎn)
show binlog events in 'mysql-bin.000013';
| mysql-bin.000013 | 820513 | Xid | 6 | 820544 | COMMIT /* xid=770 */
| mysql-bin.000013 | 820544 | Query | 6 | 820639 | drop database world
| mysql-bin.000013 | 820639 | Query | 6 | 820734 | drop database baidu
# 生產(chǎn)中查看最后10行方法：
show binlog events in 'mysql-bin.000013' limit 14,10;
5禾酱、截取binlog日志
mysqlbinlog --start-position=819739 --stop-position=820544 /data/mysql/mysql-bin.000013 >/backup/binlog.sql
6、開始恢復(fù)
關(guān)閉binlog日志記錄
set sql_log_bin=0;
恢復(fù)全備
soure /backup/all.sql
恢復(fù)增備
source /backup/binlog.sql
恢復(fù)記錄
set sql_log_bin=1;
5 物理備份工具
XBK(Percona Xtrabackup)
MEB(MySQL Enterprise Backup)
Xtrabackup優(yōu)點(diǎn)：
1.類似于直接cp數(shù)據(jù)文件绘趋，不需要管邏輯結(jié)構(gòu)颤陶，相對(duì)來說性能較高
缺點(diǎn)：
2.可讀性差
3.壓縮比低，需要更多磁盤空間
建議：>100G<TB
5.1 安裝
安裝依賴包
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev
yum -y install percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
5.2 備份實(shí)現(xiàn)原理
- 對(duì)于非innodb引擎陷遮，鎖定表滓走，直接拷貝表的數(shù)據(jù)文件
- 對(duì)于innodb引擎，立即觸發(fā)checkpoint動(dòng)作帽馋，將內(nèi)存數(shù)據(jù)頁刷寫到磁盤的動(dòng)作拘泞；拷貝此時(shí)的數(shù)據(jù)文件（LSN=1000）昔榴，在此過程中產(chǎn)生的日志文件(to_lsn=1000 redo last_lsn=1020 和 undo)也進(jìn)行備份
熱備：備份某時(shí)間點(diǎn)的數(shù)據(jù)地同時(shí)节沦，將redo和undo也一并備份走了（備份過程中產(chǎn)生的變化也也備份）玻粪，將來恢復(fù)的時(shí)候會(huì)應(yīng)用redo和undo，實(shí)現(xiàn)一致性地恢復(fù)项秉，前提是支持事務(wù)地存儲(chǔ)引擎绣溜。
5.3 全備
只有全備和增備，沒有指定庫娄蔼、表備份
innobackupex --user=root --password=123 /backup
1怖喻、備份內(nèi)容介紹
xtrabackup_binlog_info //備份時(shí)間點(diǎn)二進(jìn)制日志的信息
xtrabackup_checkpoints //記錄備份信息
cat xtrabackup_checkpoints
backup_type = full-backuped //全備
from_lsn = 0 //從哪開始
to_lsn = 4600060 //結(jié)束
last_lsn = 4600060
compact = 0
recover_binlog_info = 0
xtrabackup_info //詳細(xì)顯示
xtrabackup_logfile //redo的日志文件，二進(jìn)制格式
2岁诉、備份到指定目錄
--no-timestamp //取消時(shí)間戳
- 備份到指定目錄full
innobackupex --user=root --password=123 --no-timestamp /backup/full
5.4 恢復(fù)全備
注意：恢復(fù)的路徑必須是空的锚沸，mysql不需要啟動(dòng)
停數(shù)據(jù)庫
pkill mysqld刪數(shù)據(jù)庫
rm -rf /application/mysql/data/*準(zhǔn)備恢復(fù)，使用redo前滾涕癣，使用undo回滾哗蜈，保證數(shù)據(jù)一致性；
innobackupex --apply-log /backup/full/恢復(fù)數(shù)據(jù)
第一種：將full目錄里的文件拷貝到 /application/mysql/data/
cp -a * /application/mysql/data/
第二種：innobackupex --copy-back /backup/full/
修改權(quán)限
chown -R mysql. /application/mysql/data/*啟動(dòng)數(shù)據(jù)庫
5.5 增量備份
增量備份介紹
基于上一次備份的增量備份，XBK需要將增量合并到全備中才能恢復(fù)距潘。
優(yōu)點(diǎn)：是節(jié)約空間和時(shí)間
缺點(diǎn)：依賴全備和上一次備份增量備份策略設(shè)計(jì)及實(shí)現(xiàn)
# 全備
innobackupex --user=root --password=123 --no-timestamp /backup/full
# 周一模擬數(shù)據(jù)
create database full charset utf8;
use full
create table t1(id int);
insert into t1 values(1),(2),(3);
commit;
# 周一增量備份
innobackupex --user=root --password=123 --no-timestamp --incremental-basedir=/backup/full --incremental /backup/inc1
--incremental-basedir=/backup/full //上次備份的路徑
--incremental //打開增量備份的功能
# 周二數(shù)據(jù)模擬
create database inc1 charset utf8;
use inc1
create table t2(id int);
insert into t2 values(4),(5),(6);
commit;
# 周二增量備份
innobackupex --user=root --password=123 --no-timestamp --incremental-basedir=/backup/inc1 --incremental /backup/inc2
# 查看三個(gè)備份的記錄信息
cat /backup/full/xtrabackup_checkpoints /backup/inc1/xtrabackup_checkpoints /backup/inc2/xtrabackup_checkpoints
==================================
backup_type = full-prepared
from_lsn = 0 //起點(diǎn)
to_lsn = 4600060
last_lsn = 4600060 //終點(diǎn)
compact = 0
recover_binlog_info = 0
==================================
backup_type = incremental
from_lsn = 4600060 //起點(diǎn)
to_lsn = 4615842
last_lsn = 4615842 //終點(diǎn)
compact = 0
recover_binlog_info = 0
=================================
backup_type = incremental
from_lsn = 4615842 //起點(diǎn)
to_lsn = 4636357
last_lsn = 4636357 /終點(diǎn)
compact = 0
recover_binlog_info = 0
- 恢復(fù)準(zhǔn)備
--apply-log //redo和undo都應(yīng)用炼列，檢查
--redo-only //只應(yīng)用redo；最后一次增量不加音比，其它都要加 redo-only
恢復(fù)準(zhǔn)備：
innobackupex --apply-log --redo-only /backup/full
第一次增量合并并準(zhǔn)備俭尖，full的終點(diǎn)與inc1的終點(diǎn)一致
innobackupex --apply-log --redo-only --incremental-dir=/backup/inc1 /backup/full
第二次增量合并并準(zhǔn)備，full的終點(diǎn)與inc2的終點(diǎn)一致
innobackupex --apply-log --incremental-dir=/backup/inc2 /backup/full
全備再次準(zhǔn)備
innobackupex --apply-log /backup/full/
-
恢復(fù)數(shù)據(jù)
pkill mysqld //停數(shù)據(jù)庫
cd /application/mysql/data/
rm -rf * //刪除數(shù)據(jù)
innobackupex --copy-back /backup/full/ //拷貝回?cái)?shù)據(jù)
5.6 故障案例
背景：
某大型網(wǎng)站洞翩，mysql數(shù)據(jù)庫稽犁，數(shù)據(jù)量500G，每日更新量100M-200M
備份策略：xtrabackup骚亿，每周日23：00進(jìn)行全備已亥，周一到周六23：00進(jìn)行增量備份。
故障場(chǎng)景：
周三下午2點(diǎn)出現(xiàn)數(shù)據(jù)庫意外刪除表操作来屠。思路
1虑椎、周日全備+周一增備+周二增備
2、進(jìn)行備份準(zhǔn)備
3的妖、找到測(cè)試庫绣檬，使用備份恢復(fù)數(shù)據(jù)足陨，目前數(shù)據(jù)狀態(tài)：周二晚上23：00時(shí)間點(diǎn)
4嫂粟、截取周二備份時(shí)間點(diǎn)到周三誤刪除時(shí)間點(diǎn)之前的二進(jìn)制日志
起點(diǎn)：備份中會(huì)記錄
終點(diǎn)：分析日志文件事件
5、恢復(fù)二進(jìn)制日志墨缘，導(dǎo)出故障表星虹，導(dǎo)入回生產(chǎn)庫。恢復(fù)準(zhǔn)備
(1) 全備：
rm -rf /backup/*
innobackupex --user=root --password=123 --no-timestamp /backup/full
(2) 模擬周一數(shù)據(jù)
create database day1 charset utf8;
use day1
create table t1(id int);
insert into t1 values(1),(2),(3);
commit;
(3) 周一的增量備份
innobackupex --user=root --password=123 --no-timestamp --incremental-basedir=/backup/full --incremental /backup/inc1
(4) 模擬周二數(shù)據(jù)
create database day2 charset utf8;
use day2
create table t2(id int);
insert into t2 values(1),(2),(3);
commit;
(5) 周二的增量備份
innobackupex --user=root --password=123 --no-timestamp --incremental-basedir=/backup/inc1 --incremental /backup/inc2
(6) 周三的數(shù)據(jù)變化
create database day3 charset utf8;
use day3
create table t3(id int);
insert into t3 values(1),(2),(3);
commit;
(7) 模擬下午2點(diǎn)的故障
drop table t1;恢復(fù)數(shù)據(jù)
思考問題：
如果誤刪除表只有1M镊讼，以上方案是否合適宽涌？
找到建表語句，將數(shù)據(jù)導(dǎo)回蝶棋；建個(gè)空表卸亮，將空表的ibd文件刪除，把備份的ibd改個(gè)權(quán)限玩裙，import進(jìn)去兼贸。
# 恢復(fù)單獨(dú)表
drop table city;
create table city like city_bak;
alter table city discard tablespace;
cp /backup/full/world/city.ibd /application/mysql/data/world/
chown -R mysql.mysql /application/mysql/data/world/city.ibd
alter table city import tablespace;
5.7 面試題
1、介紹以下邏輯備份和物理備份的不同點(diǎn)吃溅？它是怎么實(shí)現(xiàn)的溶诞？
邏輯備份：基于SQL語句的備份
物理備份：基于磁盤的數(shù)據(jù)文件的備份
2、mysqldump 和 xtrabackup 熱備是怎么實(shí)現(xiàn)的决侈？
mysqldump：對(duì)于innodb表螺垢，開啟一個(gè)專門的備份事務(wù)，基于時(shí)間點(diǎn)的快照進(jìn)行熱備。
xtrabackup：備份某時(shí)間點(diǎn)的數(shù)據(jù)的同時(shí)枉圃，將redo和undo也一并備份走了（備份過程中產(chǎn)生的變化也也備份）功茴，在恢復(fù)時(shí)模擬CSR過程，將數(shù)據(jù)和日志恢復(fù)到一致狀態(tài)讯蒲，即可恢復(fù)狀態(tài)痊土。前提是支持事務(wù)地存儲(chǔ)引擎。
3墨林、備份參數(shù)的使用
4赁酝、DBA運(yùn)維人員在備份、恢復(fù)的職責(zé)
2.1設(shè)計(jì)備份旭等、容災(zāi)策略
2.2 定期的備份酌呆、容災(zāi)檢查
2.3 定期的故障恢復(fù)演練
2.4 數(shù)據(jù)損壞時(shí)的快速且準(zhǔn)確恢復(fù)
2.5 數(shù)據(jù)遷移工作
5、以下兩個(gè)命令的備份結(jié)果區(qū)別 搔耕？
mysqldump -uroot -p123 -B world >/data/backup/db1.sql
-B 庫備份隙袁，備份時(shí)把庫的建庫語句以及use庫的語句加進(jìn)去
mysqldump -uroot -p123 world >/data/backup/db2.sql
不加-B針對(duì)表級(jí)別備份，備份庫下所有表弃榨，不包含建庫語句
應(yīng)用時(shí)菩收，如果world庫不存在，需要手工創(chuàng)建鲸睛，并且use到world庫下再恢復(fù)娜饵。
