在备份恢复中的职责
- 备份策略的设计
1）备份周期
根据数据量（数据量小，每天全备一次）
2）备份工具
mysqldump (MDP)：逻辑备份（库、表）
XBK (PBK) percona Xtrabackup：适合大数据量 物理备份（数据文件、日志文件）
MEB(MySQL Enterprise BACKUP MEB)：oracle公司的收费版本
mysqlbinlog：二进制备份(用于没有通过MDP和XBK备份的数据）
3）备份方式
逻辑备份：
全备 mysqldump 建议每周一次
增量 binlog (flush logs ,cp) 每天增量
物理备份：
全备：XBK
增量：XBK
- 检查备份可用性
crontab -l ----->
备份脚本 ----->
备份路径 -----> 备份到RAID5
看备份日志，检查备份文件(大小，内容)
- 定期的恢复演练
开启测试环境，还原数据库，连接前台业务。
- 数据恢复
只要备份和日志是完整的，恢复到故障之前的时间点(快速)
- 数据迁移
操作系统不同的迁移
mysql -> mysql
其他 -> mysql
mysql -> 其他（mango）非关系型
备份的介绍
- 备份的策略
按数据量（50-80G），小数据量每天全备；大数据量（1T以上），周日全备，其余增备。
- 备份的工具
mysqldump、xtrabackup
- 备份类型
热备：对于业务影响最小 InnoDB（备份快照，不会锁表，业务正常进行）
温备：长时间锁表备份 MyISAM（业务正在使用表的时候，就会锁表，不能备份，等业务完成才能备份）
冷备：业务关闭情况下备份 （物理备份）
mysqldump
- 连接数据库
-u用户
-p 密码
-S 安全套接字
-h 主机
-P 端口号
- 基础备份参数
导入world.sql数据库
mkdir /backup
-A 全库(等于 --all-databases)
mysqldump -uroot -p123.com -A > /backup/full.sql
-B 单库或多个单库
mysqldump -uroot -p123.com -B world > /backup/world.sql
库 表
mysqldump -uroot -p123.com world city > /backup/city.sql
- 特殊备份参数
-R 存储过程和函数
-E 事件
--triggers 触发器
--master-data=2
(值为1：change master to 语句可以被slave直接执行；值为2：change master会被注释)
--single-transaction
对于InnoDB的表，进行一致性快照备份，不锁表；不加本参数，执行温备份
- 扩展参数
在构建主从时，使用AUTO/ON（保证一致性）
--set-gtid-purged=AUTO/ON
仅是做普通的本机备份恢复时，可以添加
--set-gtid-purged=OFF
SET @@GLOBAL.GTID_PURGED='aa648280-a6a6-11e9-949f-000c294a1b3b:1-11';
--max_allowed_packet=128M 控制的是备份时传输数据包的大小.(默认max_allowed_packet变量为16MB)
mysqldump -uroot -p123 -A -R --max_allowed_packet=128M --triggers --set-gtid-purged=OFF --master-data=2 --single-transaction|gzip > /backup/full_$(date +%F).sql.gz
恢复案例
- 背景环境
正在运行的网站系统，mysql-5.7.20 数据库，数据量50G，日业务增量3-10M。（中小库）
- 备份策略
每天23:00点，计划任务调用mysqldump执行全备脚本
- 故障时间点
年底故障演练，模拟周三上午10点误删除数据库
- 思路
- 停业务，挂维护页（网站维护中......），避免数据的二次伤害
- 找一个临时库，恢复周二23：00全备
- 截取周二23：00 --- 周三10点误删除之间的binlog，恢复到临时库
- 测试可用性和完整性
- 方法
方法一：直接使用临时库顶替原生产库，前端应用割接到新库
方法二：将误删除的表导出，导入到原生产库
6.开启业务
处理结果：经过20分钟的处理，最终业务恢复正常
- 故障模拟演练
1）准备环境
开启二进制日志
vim /etc/my.cnf
添加：
log_bin=/data/binlog/mysql-bin
创建目录并授权
mkdir -p /data/binlog
chown -R mysql.mysql /data
开启GTID
vim /etc/my.cnf
添加：
gtid-mode=on
enforce-gtid-consistency=true
重启MySQL
systemctl restart mysqld
确认二进制日志开启
show master status;
创建数据库和表并写入数据
create database backup;
use backup;
create table t1 (id int);
insert into t1 values(1),(2),(3);
2）周二23：00全备
mysqldump -uroot -p123.com -A -R --triggers --set-gtid-purged=OFF --master-data=2 --single-transaction > /backup/full_$(date +%F).sql
3）模拟周二 23：00到周三 10点之间数据变化
use backup;
insert into t1 values(11),(22),(33);
create table t2 (id int);
insert into t2 values(11),(22),(33);
查看二进制日志
show master status;
4）模拟故障，删除表(只是模拟，不代表生产操作)
drop database backup;
show master status;
show binlog events in 'mysql-bin.000001';
- 恢复过程
1）准备临时数据库（多实例3307）
mkdir -p /data/3307/data
cat > /data/3307/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
log_error=/data/3307/mysql.log
port=3307
server_id=7
log_bin=/data/3307/mysql-bin
EOF
mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/usr/local/mysql
cd /etc/systemd/system/
cp mysqld.service mysqld3307.service
vim mysqld3307.service
修改：
chown -R mysql.mysql /data/*
systemctl start mysqld3307
netstat -anpt | grep mysql
mysql -S /data/3307/mysql.sock
select @@server_id;
mysql -uroot -p123.com
select @@server_id;
2）准备备份
准备全备
cd /backup
截取增量数据的二进制日志
show binlog events in 'mysql-bin.000001';
mysqlbinlog --skip-gtids --include-gtids='b28c2383-b476-11ef-9640-000c2900ee94:4-6' /data/binlog/mysql-bin.000001 > /tmp/a.sql
3）恢复备份到临时库
复制当前会话
会话2：
mysql -S /data/3307/mysql.sock
set sql_log_bin=0;
恢复全备
source /backup/full_2024-12-09.sql
恢复二进制日志
source /tmp/a.sql
set sql_log_bin=1;
4）将临时库导出并恢复到生产
会话2：
mysqldump -S /data/3307/mysql.sock -B backup > /backup/bak.sql
会话1：
set sql_log_bin=0;
source /backup/bak.sql
set sql_log_bin=1;
查看数据是否恢复
use backup;
select * from t1;
select * from t2;
