備份類型

熱備
在數(shù)據(jù)庫(kù)正常業(yè)務(wù)時(shí),備份數(shù)據(jù),并且能夠一致性恢復(fù)（只能是innodb）
對(duì)業(yè)務(wù)影響非常小
溫備
鎖表備份,只能查詢不能修改（myisam）
影響到寫(xiě)入操作
冷備
關(guān)閉數(shù)據(jù)庫(kù)業(yè)務(wù),數(shù)據(jù)庫(kù)沒(méi)有任何變更的情況下,進(jìn)行備份數(shù)據(jù).
業(yè)務(wù)停止

備份工具

邏輯備份工具

基于SQL語(yǔ)句進(jìn)行備份
mysqldump （100G內(nèi)使用）
mysqlbinlog

物理備份工具

基于磁盤(pán)數(shù)據(jù)文件備份
xtrabackup(XBK) ：percona 第三方
MySQL Enterprise Backup（MEB）收費(fèi)

備份方式：
全備:全庫(kù)備份粱栖，備份所有數(shù)據(jù)
增量:備份變化的數(shù)據(jù)
邏輯備份=mysqldump+mysqlbinlog
物理備份=xtrabackup_full+xtrabackup_incr+binlog或者xtrabackup_full+binlog
備份周期:
根據(jù)數(shù)據(jù)量設(shè)計(jì)備份周期
比如：周日全備婿脸，周1-周6增量

邏輯備份工具實(shí)踐

-A 全庫(kù)備份
# 補(bǔ)充:
1.常規(guī)備份是要加 --set-gtid-purged=OFF,解決備份時(shí)的警告
 [root@db01 ~]# mysqldump -uroot -p123 -A  --set-gtid-purged=OFF  >/backup/full.sql
 2.構(gòu)建主從時(shí),做的備份,不需要加這個(gè)參數(shù)
[root@db01 ~]# mysqldump -uroot -p123 -A    --set-gtid-purged=ON >/backup/full.sql
-B 備份單個(gè)庫(kù)或多個(gè)庫(kù)
mysqldump -B mysql gtid --set-gtid-purged=OFF >/data/backup/b.sql
備份單個(gè)或多個(gè)表
world數(shù)據(jù)庫(kù)下的city,country表
mysqldump -uroot -p world city country >/backup/bak1.sql

必加參數(shù)（1）
-R 同時(shí)備份存儲(chǔ)過(guò)程和函數(shù)
-E 同時(shí)備份EVENT
--triggers 同時(shí)備份觸發(fā)器
必加參數(shù)（1）
--master-data=2
以注釋的形式,保存?zhèn)浞蓍_(kāi)始時(shí)間點(diǎn)的binlog的狀態(tài)信息

mysqldump -uroot -p  -A  -R --triggers --master-data=2   >/back/world.sql
[root@db01 ~]# grep 'CHANGE' /backup/world.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000035', MASTER_LOG_POS=194;

功能：
（1）在備份時(shí)，會(huì)自動(dòng)記錄钝尸，二進(jìn)制日志文件名和位置號(hào)
0 默認(rèn)值
1  以change master to命令形式惑惶，可以用作主從復(fù)制
2  以注釋的形式記錄蒸眠，備份時(shí)刻的文件名+postion號(hào)
（2） 自動(dòng)鎖表
（3）如果配合--single-transaction修赞，只對(duì)非InnoDB表進(jìn)行鎖表備份前翎，InnoDB表進(jìn)行“熱“”備稚配，實(shí)際上是實(shí)現(xiàn)快照備份。


--single-transaction
innodb 存儲(chǔ)引擎開(kāi)啟熱備(快照備份)功能       
master-data可以自動(dòng)加鎖
（1）在不加--single-transaction 港华，啟動(dòng)所有表的溫備份道川，所有表都鎖定
（1）加上--single-transaction ,對(duì)innodb進(jìn)行快照備份,對(duì)非innodb表可以實(shí)現(xiàn)自動(dòng)鎖表功能
例子6: 備份必加參數(shù)
mysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=OFF >/data/backup/full.sql


--set-gtid-purged=auto
auto , on
off 
使用場(chǎng)景:
1. --set-gtid-purged=OFF,可以使用在日常備份參數(shù)中.
mysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=OFF >/data/backup/full.sql
2. auto , on:在構(gòu)建主從復(fù)制環(huán)境時(shí)需要的參數(shù)配置
mysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=ON >/data/backup/full.sql

-F（不常用） 在備份開(kāi)始時(shí),刷新一個(gè)新binlog日志
例子5:
mysqldump -uroot -p  -A  -R --triggers -F >/bak/full.sql


--max-allowed-packet=#  備份大表報(bào)錯(cuò)時(shí)可用
mysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=OFF --max-allowed-packet=256M >/data/backup/full.sql

 --max-allowed-packet=# 
The maximum packet length to send to or receive from server.

模擬故障并恢復(fù)

1.全備
[root@mysql ~]# mysqldump -uroot -psongjianjiang123 -A -E -R --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF > /data/backup/full.sql


 -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=473;



2.模擬刪庫(kù)恢復(fù)
初始化
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
啟動(dòng)數(shù)據(jù)庫(kù)
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
set sql_log_bin=0
source 
set sql_log_bin=1

flush privileges;

二進(jìn)制日志恢復(fù)

10. MySQL物理備份工具-xtrabackup(XBK、Xbackup)
    10.1安裝
    10.1.1 安裝依賴包：
    wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
    yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev
    10.1.2 下載軟件并安裝
    wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm

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.el7.x86_64.rpm
10.2、備份命令介紹:
xtrabackup
innobackupex ******
10.3 備份方式——物理備份
（1）對(duì)于非Innodb表（比如 myisam）是冒萄，鎖表cp數(shù)據(jù)文件臊岸，屬于一種溫備份。
（2）對(duì)于Innodb的表（支持事務(wù)的）宦言，不鎖表扇单，拷貝數(shù)據(jù)頁(yè)，最終以數(shù)據(jù)文件的方式保存下來(lái)奠旺，把一部分redo和undo一并備走蜘澜，屬于熱備方式。
面試題： xbk 在innodb表備份恢復(fù)的流程
0响疚、xbk備份執(zhí)行的瞬間,立即觸發(fā)ckpt,已提交的數(shù)據(jù)臟頁(yè),從內(nèi)存刷寫(xiě)到磁盤(pán),并記錄此時(shí)的LSN號(hào)
1鄙信、備份時(shí)，拷貝磁盤(pán)數(shù)據(jù)頁(yè)忿晕，并且記錄備份過(guò)程中產(chǎn)生的redo和undo一起拷貝走,也就是checkpoint LSN之后的日志
2装诡、在恢復(fù)之前，模擬Innodb“自動(dòng)故障恢復(fù)”的過(guò)程践盼，將redo（前滾）與undo（回滾）進(jìn)行應(yīng)用
3鸦采、恢復(fù)過(guò)程是cp 備份到原來(lái)數(shù)據(jù)目錄下
10.4、innobackupex使用
10.4.1 全備
[root@db01 backup]# innobackupex --user=root --password=123 /data/backup
自主定制備份路徑名
[root@db01 backup]# innobackupex --user=root --password=123 --no-timestamp /data/backup/full
備份集中多出來(lái)的文件：
-rw-r----- 1 root root 24 Jun 29 09:59 xtrabackup_binlog_info
-rw-r----- 1 root root 119 Jun 29 09:59 xtrabackup_checkpoints
-rw-r----- 1 root root 489 Jun 29 09:59 xtrabackup_info
-rw-r----- 1 root root 2560 Jun 29 09:59 xtrabackup_logfile

xtrabackup_binlog_info ：（備份時(shí)刻的binlog位置）
[root@db01 full]# cat xtrabackup_binlog_info
mysql-bin.000003 536749
79de40d3-5ff3-11e9-804a-000c2928f5dd:1-7
記錄的是備份時(shí)刻咕幻，binlog的文件名字和當(dāng)時(shí)的結(jié)束的position渔伯，可以用來(lái)作為截取binlog時(shí)的起點(diǎn)。

xtrabackup_checkpoints ：
backup_type = full-backuped
from_lsn = 0 上次所到達(dá)的LSN號(hào)(對(duì)于全備就是從0開(kāi)始,對(duì)于增量有別的顯示方法)
to_lsn = 160683027 備份開(kāi)始時(shí)間(ckpt)點(diǎn)數(shù)據(jù)頁(yè)的LSN
last_lsn = 160683036 備份結(jié)束后肄程，redo日志最終的LSN
compact = 0
recover_binlog_info = 0
（1）備份時(shí)刻锣吼，立即將已經(jīng)commit過(guò)的，內(nèi)存中的數(shù)據(jù)頁(yè)刷新到磁盤(pán)(CKPT).開(kāi)始備份數(shù)據(jù)蓝厌，數(shù)據(jù)文件的LSN會(huì)停留在to_lsn位置玄叠。
（2）備份時(shí)刻有可能會(huì)有其他的數(shù)據(jù)寫(xiě)入，已備走的數(shù)據(jù)文件就不會(huì)再發(fā)生變化了拓提。
（3）在備份過(guò)程中读恃，備份軟件會(huì)一直監(jiān)控著redo的undo，如果一旦有變化會(huì)將日志也一并備走代态，并記錄LSN到last_lsn寺惫。
從to_lsn ----》last_lsn 就是，備份過(guò)程中產(chǎn)生的數(shù)據(jù)變化.
10.4.2 全備的恢復(fù)
準(zhǔn)備備份（Prepared）
將redo進(jìn)行重做胆数，已提交的寫(xiě)到數(shù)據(jù)文件肌蜻，未提交的使用undo回滾掉。模擬了CSR的過(guò)程
[root@db01 ~]# innobackupex --apply-log /backup/full
恢復(fù)備份
前提：
1必尼、被恢復(fù)的目錄是空
2蒋搜、被恢復(fù)的數(shù)據(jù)庫(kù)的實(shí)例是關(guān)閉
systemctl stop mysqld
創(chuàng)建新目錄

[root@db01 backup]# mkdir /data/mysql1
數(shù)據(jù)授權(quán)
chown -R mysql.mysql /data/mysql1
恢復(fù)備份
[root@db01 full]# cp -a /backup/full/* /data/mysql1/
啟動(dòng)數(shù)據(jù)庫(kù)
vim /etc/my.cnf
datadir=/data/mysql1
[root@db01 mysql1]# chown -R mysql.mysql /data/mysql1
systemctl start mysqld
10.4.3 innobackupex 增量備份(incremental)
（1）增量備份的方式篡撵，是基于上一次備份進(jìn)行增量。
（2）增量備份無(wú)法單獨(dú)恢復(fù)豆挽。必須基于全備進(jìn)行恢復(fù)育谬。
（3）所有增量必須要按順序合并到全備中。
增量備份命令
（1）刪掉原來(lái)備份
略.
（2）全備（周日）
[root@db01 backup]# innobackupex --user=root --password --no-timestamp /backup/full >&/tmp/xbk_full.log
（3）模擬周一數(shù)據(jù)變化
db01 [(none)]>create database cs charset utf8;
db01 [(none)]>use cs
db01 [cs]>create table t1 (id int);
db01 [cs]>insert into t1 values(1),(2),(3);
db01 [cs]>commit;

（4）第一次增量備份（周一）
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full /backup/inc1 &>/tmp/inc1.log
（5）模擬周二數(shù)據(jù)
db01 [cs]>create table t2 (id int);
db01 [cs]>insert into t2 values(1),(2),(3);
db01 [cs]>commit;
（6）周二增量
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/inc1 /backup/inc2 &>/tmp/inc2.log
（7）模擬周三數(shù)據(jù)變化
db01 [cs]>create table t3 (id int);
db01 [cs]>insert into t3 values(1),(2),(3);
db01 [cs]>commit;
db01 [cs]>drop database cs;
恢復(fù)到周三誤drop之前的數(shù)據(jù)狀態(tài)
恢復(fù)思路：

1. 掛出維護(hù)頁(yè)帮哈，停止當(dāng)天的自動(dòng)備份腳本
2. 檢查備份：周日f(shuō)ull+周一inc1+周二inc2膛檀，周三的完整二進(jìn)制日志
3. 進(jìn)行備份整理（細(xì)節(jié)），截取關(guān)鍵的二進(jìn)制日志（從備份——誤刪除之前）
4. 測(cè)試庫(kù)進(jìn)行備份恢復(fù)及日志恢復(fù)
5. 應(yīng)用進(jìn)行測(cè)試無(wú)誤娘侍，開(kāi)啟業(yè)務(wù)
6. 此次工作的總結(jié)
   恢復(fù)過(guò)程
7. 檢查備份
   1afe8136-601d-11e9-9022-000c2928f5dd:7-9
8. 備份整理（apply-log）+合并備份（full+inc1+inc2）
   (1) 全備的整理
   [root@db01 one]# innobackupex --apply-log --redo-only /data/backup/full
   (2) 合并inc1到full中
   [root@db01 one]# innobackupex --apply-log --redo-only --incremental-dir=/data/backup/inc1 /data/backup/full
   (3) 合并inc2到full中
   [root@db01 one]# innobackupex --apply-log --incremental-dir=/data/backup/inc2 /data/backup/full
   (4) 最后一次整理全備
   [root@db01 backup]# innobackupex --apply-log /data/backup/full
9. 截取周二 23:00 到drop 之前的 binlog
   [root@db01 inc2]# mysqlbinlog --skip-gtids --include-gtids='1afe8136-601d-11e9-9022-000c2928f5dd:7-9' /data/binlog/mysql-bin.000009 >/data/backup/binlog.sql
10. 進(jìn)行恢復(fù)
    [root@db01 backup]# mkdir /data/mysql/data2 -p
    [root@db01 full]# cp -a * /data/mysql/data2
    [root@db01 backup]# chown -R mysql. /data/*
    [root@db01 backup]# systemctl stop mysqld
    vim /etc/my.cnf
    datadir=/data/mysql/data2
    systemctl start mysqld
    Master [(none)]>set sql_log_bin=0;
    Master [(none)]>source /data/backup/binlog.sql