MySQL备份和恢复（参数讲解）
# innodb核心参数
innodb_flush_log_at_trx_commit=1
innodb_flush_method=o_direct
innodb_flush_log_at_trx_commit=0
innodb_flush_method=fsync
备份恢复
1.在备份恢复中的职责
1.1 备份策略的设计
（1）备份周期：根据数据量
（2）备份工具：mysqldump(MDP) XBK(PBK) percona Xtrabackup, MEB(MySQL Enterprise BACKUP MEB),mysqlbinlog
（3）备份的方式:
逻辑：
全备：mysqldump
增量：binlig(flush logs,cp)
物理备份：XBK
全备：XBK
增量：XBK
1.2 检查备份可用性
crontab -l --->
备份路径 --->
看备份路径 --->
看备份日志，检查备份文件（大小，内容）
1.3 定期的恢复演练
1.4 数据恢复
只要备份和日志是完整的，恢复到故障之前的时间点（快速）
1.5 数据迁移
操作系统不同的迁移
mysql -> mysql （升级迁移）
其他 -> mysql（异构平台迁移）
mysql -> 其他
2. 备份的介绍
2.1 备份类型
热备：在数据库正常业务时，备份数据，并且能够一致性恢复（只能是innodb） 对业务影响非常小 使用 Innodb
温备：锁表备份，只能查询不能修改（myisam）影响到写入操作 使用Mysaim
冷备：关闭数据库业务，数据库没有任何变更的情况下，进行备份数据 业务停止
2.2 备份方式及工具介绍
3.mysqldump
3.1 连接数据库
-u
-p
-s
-h
p
3.2 基础备份参数
-A 全备 # --all-databases
mysqldump -uroot -p123 -A >/backup/full.sql # 全备 备份逻辑是将数据库的数据从磁盘上拿出来，然后将数据转换为sql语句，写入到文件
-B # 导出几个数据库。参数后面所有名字参量都被看作数据库名。
mysqldump -uroot -p123 -B world oldguo wordpress >/backup/db.sql
库、表 # 对单张库的表进行备份
mysqldump -uroot -p123 world city >/backup/tab.sql
3.3 特殊备份参数
-R # 导出存储过程以及自定义函数 --routines
-E # 导出事件
--triggers # 导出触发器。该选项默认启用，用--skip-triggers禁用它。
--master-data=2 # (1) 记录备份时刻的binlog信息（2）自动锁表 ：1.不加 --single-transaction，温备份 2.加了--single-transaction,对于InnoDB表不锁表备份（快照备份）
每天全备 mysqldump 60G
周三，下午2点，数据库损坏
恢复思路？
1.恢复周二全备
2.截取全备到损坏之前的binlog
--single-transaction
对于InnoDB的表，进行一致性快照备份，不锁表
4. 恢复案例
4.1背景环境：
正在运行的网站系统，mysql-5.7.20数据库，数据量50G，日业务增量1-5M。
4.2 备份策略：
每天23：00点，计划任务调用mysqldump执行全备脚本
4.3 故障时间点：
年底故障演练：模拟周三上午10点误删除数据库。
4.4 思路：
1.停业务，挂维护页，避免数据的二次伤害
2.找一个临时库，恢复周二23：00全备
3.截取周二23：00----周三10点误删除之间的binlog，恢复到临时库
4. 测试可用性和完整性
5.
5.1 方法一：直接使用临时库顶替原生产库，前端应用割接到新库
5.2 方法二：将误删除的表导出，导入到原生产库
6. 开启业务
处理结果：经过20分钟的处理，最终业务恢复正常
4.5 故障模拟演练
4.5.1 准备数据
create database backup;
use backup
create table t1 (id int);
insert into t1 values(1),(2),(3);
commit;
rm -rf /backup/*
4.5.2 周二 23：00全备
mysqldump -uroot -p123 -A -R --triggers --set-gtid-purged=OFF --master-data=2 --single-transation|gzip > /backup/full $(date +%F).sql.gz
4.5.3 模拟周二 23：00到周三 10点之间数据变化
use back up
insert into t1 values(11),(22),(33);
commit;
create table t2 (id int);
insert into t2 values(11),(22),(33);
4.5.4 模拟故障，删除表（只是模拟，不代表生产操作）
drop database backup;
4.6 恢复过程
4.6.1 准备临时数据库（多实例3307）
systemctl start mysqld3307
4.6.2 准备备份
(1)准备全备：
cd /backup
gunzip full_2021_03_27.sql.gz
(2)截取二进制日志
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000036',MAStER_LOG_POS=793;
mysqlbinlog --skip-gtids --start-position=753 --stop-position=1519 mysql-bin.000002 > /backup/bin.sql
mysqlbinlog --skip-gtids --include-gtids='3ca79ab5-3e4d-11e9-a709-00c293b577e:6-7'
/data/binlog/mysql-bin.000036 > /backup/bin.sql
4.6.3 恢复备份到临时库
mysql -S /data/3307/mysql.sock
set sql_log_bin=0;
source /backup/full_2021-03-27/mysql.sql
source /backup/bin.sql
4.6.4 将故障表导出并恢复到生产
mysqldump -S /data/3307/mysql.sock -B backup > /backup/t1.sql
mysql -uroot -p123
set sql_log_bin=0
source /backup/bak.sql;
练习：
1.创建一个数据库database1 # 确保binlog日志处于开启状态
2. 在database1下创建一张表t1
3.插入5行任意数据
insert into t1 values(1),(2),(3),(4),(5)
commit;
4.全备
mysqldump -uroot -p123 --master-data=2 --single-transaction -R -E --triggers > /backup/full.sql
5.插入两行数据，任意修改3行数据，
insert into t1 values(6),(7),(8)
commit;
update t1 set id=10 where id>5
6.删除所有数据
delete from t1 where id=5;
commit;
delete from t1
commit;
insert into t1 values(1),(2),(3),(4),(5)
commit;
update t1 set id=10 where id>2
commit;
7.再t1中又插入5行新数据，修改3行数据
需求，跳过第六步恢复表数据 # 1~5步全备，加上第7步的截取
'''
SET @@GLOBAL.GTID_PURGED='aa648280-a6a6-11e9-949f-000c294a1b3b:1-11'
-- CHANGE MASTER TO MASTER_LOG_FILE='MYSQL-BIN.000002',MASTER_LOG_POS=2396;
'''
mysqlbinlog --skip-gtids --include-gtid='aa648280-a6a6-11e9-949f-000c294a1b3b:12-17' --exclude-gtids='aa648280-a6a6-11e9-949f-000c294a1b3b:15' /data/binlog/mysql-bin.000002 > /backup/bin.sql
set sql_log_bin=0;
source /backup/full.sql
source /backup/bin.sql
use database1;
select * from t1
'''
扩展参数
-set-gtid-purged=AUTO/ON # 在构建主从时，使用AUTO/ON
-set-gtid-purged=OFF # 仅是做普通的本机备份恢复时，可以添加；这样在全备份的文件中就不会记录这一行
SET @@GLOBAL.GTID_PURGED='aa648280-a6a6-11e9-949f-000c294a1b3b:1-11'
--max_allowed_packet=128M 控制的是备份时传输数据包的大小
mysqldump -uroot -p123 -A -B --max_allowed_packet=128M --triggers --set-gtid-purged=OFF --master-data=2 --single-transaction|gzip > /back/full_$(date +%F).sql.gz
'''
物理备份-XBK
安装依赖包：
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
下载软件并安装：
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.12-1.e17.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.e16.x86_64.rpm
yum -y install percona-xtrackup-24-2.4.4-1.e17.x86_64.rpm
innobackuppex使用
# 备份核心理念
1. 针对非InnoDB，进行锁表备份，copy所有的非innoDB表文件
2. 针对InnoDB表，立即出发CKPT，copy所有InnoDB表相关的文件（ibdata1,ibd,frm）。并且将备份过程中产生新的数据变化的部分redo一起备份走
3.在恢复时，xbk会调用InnoDB引擎的CSR过程，将数据和redo的LSN追平，然乎偶进行一致性恢复。
