备份恢复（mysqldump）
1、在备份恢复中的职责
1.1 备份策略的设计
(1) 备份周期: 根据数据量. (2)备份工具: mysqldump (MDP) , XBK (PBK) percona Xtrabackup , MEB(MySQL Enterprise BACKUP MEB) ,mysqlbinlog (3)备份方式: 逻辑: 全备 mysqldump 增量 binlog (flush logs ,cp) 物理备份: 全备 : XBK 增量 : XBK
1.2 检查备份可用性
crontab -l -----> 备份脚本 -----> 备份路径 -----> 看备份日志,检查备份文件(大小,内容)
1.3 定期的恢复演练
1.4 数据恢复
只要备份和日志是完整的,恢复到故障之前的时间点(快速)
1.5 数据迁移 ***
操作系统不同的迁移 mysql -> mysql 其他 -> mysql mysql -> 其他
2、备份的介绍
2.1 备份的策略
2.2 备份的工具
2.3 备份类型
热备 : 对于业务影响最小 InnoDB 温备 : 长时间锁表备份 MyISAM 冷备 : 业务关闭情况下备份
3、mysqldump
3.1 连接数据库
-u -p -S -h P
3.2 基础备份参数命令
-A [root@db01 backup]# mysqldump -uroot -p123 -A >/backup/full.sql --数据库全备 -B [root@db01 backup]# mysqldump -uroot -p123 -B world oldguo wordpress >/backup/db.sql --库的全备 库 表 [root@db01 backup]# mysqldump -uroot -p123 world city country > /backup/tab.sql --库表全备
3.3 特殊备份参数
-R 存储过程和函数 -E 事件 --triggers 触发器
--master-data=2 * (1) 记录备份时刻的binlog信息 (2) 自动锁表 不加--single-transaction ,温备份 加了--single-transaction,对于InnoDB表不锁表备份(快照备份)
--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-transaction|gzip > /backup/full_$(date +%F).sql.gz
4.5.3 模拟周二 23：00到周三 10点之间数据变化 use backup insert into t1 values(11),(22),(33); commit; create table t2 (id int); insert into t2 values(11),(22),(33); commit;
4.5.4 模拟故障,删除表(只是模拟，不代表生产操作) drop database backup;
4.6 恢复过程
4.6.1 准备临时数据库（多实例3307） systemctl start mysqld3307 4.6.2 准备备份 （1）准备全备： cd /backup gunzip full_2018-10-14.sql.gz （2）截取二进制日志 -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=753; 753 1519 mysqlbinlog --skip-gtids --start-position=753 --stop-position=1519 /data/binlog/mysql-bin.000002 >/backup/bin.sql
4.6.3 恢复备份到临时库 mysql -S /data/3307/mysql.sock set sql_log_bin=0; source /backup/full_2019-07-15.sql source /backup/bin.sql
4.6.4 将故障表导出并恢复到生产 mysqldump -S /data/3307/mysql.sock -B backup >/backup/bak.sql mysql -uroot -p123 set sql_log_bin=0 source /backup/bak.sql;
5、练习：
1、创建一个数据库 oldboy 2、在oldboy下创建一张表t1 3、插入5行任意数据 4、全备 5、插入两行数据，任意修改3行数据，删除1行数据 6、删除所有数据 7、再t1中又插入5行新数据，修改3行数据
需求，跳过第六步恢复表数据
6、扩展参数 ***
在构建主从时,使用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 控制的是备份时传输数据包的大小.
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
