1 MySQL的备份设计
1.1 备份策略的设计
备份周期，可以根据数量来进行选择
备份工具： 可以选择mysql软件自带的命令mysqldump 或者 XBK工具
备份方式： 全备 增量
1.2 检查备份的可用性
crontab -l :检查定时任务中是否有备份的脚本
查看备份日志
1.3 定期的恢复演练
1.4 数据恢复
主要备份和日志是完整的，恢复到故障之前的时间点(快速的)
1.5 数据的迁移
mysql ----> mysql 其他------->mysql mysql ------>其他
2.备份的介绍
2.1 备份类型： 热备 冷备 温备
3. mysqldump (逻辑备份) ：官方出的备份工具
优点：
1.不需要下载安装；2,备份出来的是SQL，文本格式，便于备份处理：3，压缩比较高，节省备份的磁盘空间
缺点：
1.依赖数据库引擎，需要从磁盘把数据读出，然后转换成SQL进行转储，比较耗费资源，数据量大的话效率较低，
2.它属于单线程备份
3.建议100G以内的数据量级，可以使用mysqldump，超过1TB以上，我们可能选择的是xbk工具(物理备份)，配合分布式的系统
mysqldump语法：
Dumping structure and contents of MySQL databases and tables.
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
基础参数：
-A # 备份全库 >>mysqldump -uroot -p123 -A >/tmp/full.sql
-B # 备份某个特定的库 >>mysqldump -uroot -p123 -B city(库名) wordpress(库名) >/tmp/db.sql
库名 表名 #备份某个库中的某一个表 >> mysqldump -uroot -p123 world(库名) test(表名) test1(表名)>/backup/tab.sql
特殊备份参数：
-R ：存储过程和函数
-E ：事件
--trigger：触发器
--master-data=2 ###以注释的形式，保存备份开始时间点的binlog的状态信息
-F ###在备份开始时，刷新一个新的binlog日志
功能：
（1）记录备份时刻的binlog信息
（2）自动锁表
不加 --single-transaction : 温备份
加了 --singel-transaction :对于innodb表不锁表，innodb存储引擎开始热备(快照备份)功能
--single-transaciton：该参数默认是不开启的，因此在备份时候一定要加上该参数
扩展参数：
##关于该参数的解释
--set-gtid-purged[=name]
Add 'SET @@GLOBAL.GTID_PURGED' to the output. Possible values for this option are ON, OFF and AUTO. If ON is used and GTIDs are not enabled on the server, an error is generated. If OFF is used, this option does nothing. If AUTO is used and GTIDs are enabled on the server, 'SET @@GLOBAL.GTID_PURGED' is added to the output. If GTIDs are disabled, AUTO does nothing. If no value is supplied then the default (AUTO) value will be considered.
--set-gtid-purged=AUTO/ON (做主从复制时一定要用ON或AUTO，默认是auto)
--set-gtid-purged=OFF (仅是做普通的本机备份恢复时，可以添加)
--max_allowed_packet=128M #控制的是备份是传输数据包的大小
4. 案例(关于mysqldump)
场景：正在运行的网站系统，mysql-5.7.20数据库，数据量50G,日业务增量1-5M，每天23:00,计划任务调用mysqldump执行全备脚本
模拟故障时间点：年底故障演练:模拟周三上午10点删除数据库，并进行恢复
恢复思路：
1.停业务，避免数据的二次伤害
2.找一个临时库，恢复周二23:00全备
3.截取周二23:00 -- 周三10点误删除之间的binlog,恢复临时库
4.测试可用性和完整性
5.方式一： 直接使用临时库顶替原生产库，前端应用割接到新库
方式二: 将误删除的表导出，导入到原生产库中
模拟演练：
1.准备数据 create database backup; use backup; create table t1(id int); insert into t1 values(1),(2),(3) commit;
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
3. 模拟周二23:00到周三10点之间数据变化
use backup;
insert into t1(11),(12),(13);
commit;
create table t2 (id int);
insert into t2 values(11),(22),(33);
4.删库(模拟)
drop backup
5.利用gtid 恢复周二23:00到宕机时刻的数据
可以查看命令： show binlog events in '当前正在使用的二进制日志'，从中找出需要的数据所对应的GTID号
备份命令： mysqlbinlog --skip-gtids --include-gtids='62b6a13b-19b2-11eb-a0b7-00163e2ce7ef:6-7' --exclude-gtids='9a85ae81-0d17-11eb-9975-00163e1430bc:6' mysql-bin.000005>/bakup/bin.sql
参数说明：
--include-gtids 截取指定的gtid
--exclude-gtids 排除指定的gtid
--skip-gtids 跳过gtid的幂等性机制的检查，即截取日志的时候不带有gtid的信息
6开始恢复
set sql_log_bin=0
source /backup/full_xxxx.sql
source /bakup/bin.sql
set sql_log_bin=1
5. mysqlpump
这个也是官网出的备份工具，它相比mysqldump而言，它是多线程进行备份的，而且在未来有取代mysqldump的趋势，
- 是多线程，但是只能是到表级别，对于一张表来说，还是单线程的
- mysqlpump有默认的队列(default),队列下面可以有N个线程去备份数据库/数据库下的表
- mysqlpump可以开对个队列(对应不同的库/表)，然后每个队列设置不同的线程数，进行并发备份。
重要参数：
mysqlpump参数常用的参数同mysqldump类似，以下参数和并发相关
1. --default-parallelism=# 线程数，默认开2个线程进行并发备份的
2. --parallel-schema=name 哪些数据库进行并发备份
mysqlpump --single-transaction --databases employees -S /tmp/mysql.sock_58 >employess_pump_1.sql
#第一条语句什么都不加表示并发线程数为2
mysqlpump -S /tmp/mysql.sock_58 --single-transaction --parallel-schemas=2:employees --parallel-schemas=4:dbt3 -B employees dbt3 > backup.sql
# --parallel-schemas=2:employees 表示备份employees库使用2个线程
# --parallel-schemas=4:dbt3 表示备份dbt3库使用4个线程
# -B employees dbt3 表示指定备份 employees 和 dbt3这两个库
mysqlpump比mysqldump快的原因处理具有并发性之外，mysqlpump 会先插入数据，再建立索引，而mysqldump是在建立表的时候就把索引给加上了，所以mysqldump在导入数据的时候也比mysqldump要快。
补充：
mysqlpump直接支持压缩功能，支持LZ4和ZLIB(ZLIB压缩比相对较高，但是速度较慢)
mysqlpump -S /tmp/mysql.sock --single-transaction --compress-output=lz4 burn_test > burn_test_2.sql #恢复： 先解压在导入 #zlib_decompress #lz4_decompress >>lz4_decommpress backup.sql.lz4 backup.sql ##先解压 >> mysql<backup.sql #mysqlpump导入数据是单线程的
6.mydumper
- 由mysql开源社区开发的一块针对mysql备份的工具
- C语言开发
- 多线程备份，多线程恢复
- 快速文件压缩
- 以守护进程对的工作方式，定时快照和连续的二进制日志
- 开源
- 备份出来的是多个文件
具体用法可以参考mydumper --help 查看一些参数的意义
#备份全库 mydumper -u root -p 'xxxxxDB2015!@#' -o /mnt/backup/ #备份mysqlhqdb数据库： mydumper -u root -p 'xxxxxDB2015!@#' -B mysqlhqdb -o /mnt/backup/ #备份多张表(tableA,tableB)： mydumper -u root -p 'xxxxxDB2015!@#' -B mysqlhqdb -T tableA,tableB -o /mnt/backup/ #备份tableA表的数据，不备份表结构 mydumper -u root -p 'xxxxxDB2015!@#' -B mysqlhqdb -T tableA -m -o /mnt/backup/ #备份tableA表的数据，并进行压缩 mydumper -u root -p 'xxxxxDB2015!@#' -B mysqlhqdb -T tableA -c -o /mnt/backup/ #还原mysqlhqdb库： myloader -u root -p 'xxxxxDB2015!@#' -B mysqlhqdb -d /mnt/backup/ #还原tableA表 myloader -u root -p 'xxxxxDB2015!@#' -B mysqlhqdb -o tableA -d /mnt/backup/
mydumper：
-o:表示将备份文输出到某个路径下面
myloader:
-o:表示恢复是如果数据库已存在就覆盖
7. mysql之物理备份(Xtrabakup)
优点：
1.类似于直接cp数据文件，不需要管理逻辑结构，相对来说新能较高
缺点：
1.可读性较差，2压缩比低，需要更多磁盘空间
建议：
100G<数据量级<1TB
5.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 ###安装需要的依赖库 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 ####这个是centos6版本下的XBK软件 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 ###这个是centos7版本下的XBK软件 yum -y install percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm ###执行这个命令就开始安装
5.2 使用说明：
1. Xtrabackup 这个备份命令一般只针对innodb存储引擎表进行备份，在生产上用的很少
2. innobackupex 这个可以备份其它存储引擎表(包含innodb表)，在生产上用的很多 ******
（1）对于非Innodb表（比如 myisam）是，锁表cp数据文件，属于一种温备份。
（2）对于Innodb的表（支持事务的），不锁表，拷贝数据页，最终以数据文件的方式保存下来，把一部分redo和undo一并备走，属于热备方式。工作原理：Xbk在innodb表备份恢复的流程
0、xbk备份执行的瞬间,立即触发ckpt,已提交的数据脏页,从内存刷写到磁盘,并记录此时的LSN号 1、备份时，拷贝磁盘数据页，并且记录备份过程中产生的redo和undo一起拷贝走,也就是checkpoint LSN之后的日志 2、在恢复之前，模拟Innodb“自动故障恢复”的过程，将redo（前滚）与undo（回滚）进行应用 3、恢复过程是cp 备份到原来数据目录下
备份开始时首先会开启一个后台检测进程，实时检测mysq redo的变化，一旦发现有新的日志写入，立刻将日志记入后台日志文件xtrabackup_log中，之后复制innodb的数据文件一系统表空间文件ibdatax，
复制结束后，将执行flush tables with readlock,然后复制.frm MYI MYD等文件，最后执行unlock tables,最终停止xtrabackup_log
xtrabackup备份原理详解请看阿里数据库内核报告：http://mysql.taobao.org/monthly/2016/03/07/
innobackupex使用
[root@db01 backup]# innobackupex --defaluts-file=/data/mysql/3308/my.cnf --user=root --password=123 --no-timestamp /tmp/xbk_full.sql #####全备(备份整个数据库)
备份后产生的文件：
-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 ：（备份时刻的binlog位置） [root@db01 full]# cat xtrabackup_binlog_info mysql-bin.000003 536749 79de40d3-5ff3-11e9-804a-000c2928f5dd:1-7 记录的是备份时刻，binlog的文件名字和当时的结束的position，可以用来作为截取binlog时的起点。 xtrabackup_checkpoints ： backup_type = full-backuped from_lsn = 0 上次所到达的LSN号(对于全备就是从0开始,对于增量有别的显示方法) to_lsn = 160683027 备份开始时间(ckpt)点数据页的LSN last_lsn = 160683036 备份结束后，redo日志最终的LSN compact = 0 recover_binlog_info = 0 （1）备份时刻，立即将已经commit过的，内存中的数据页刷新到磁盘(CKPT).开始备份数据，数据文件的LSN会停留在to_lsn位置。 （2）备份时刻有可能会有其他的数据写入，已备走的数据文件就不会再发生变化了。 （3）在备份过程中，备份软件会一直监控着redo的undo，如果一旦有变化会将日志也一并备走，并记录LSN到last_lsn。 从to_lsn ----》last_lsn 就是，备份过程中产生的数据变化.
5.3 全备的恢复：
innobackupex -H172.17.94.xx -P3308 -uroot -pxxx --apply-log /tmp/xbk_full.sql 第一步 这步的目的是将redo进行重做，已经提交的的写到数据文件，未提交的使用undo回滚掉，模拟CSR过程
systemctl stop mysqld3308 第二步 停止数据库实例
innobackupex -H172.17.94.xx -P3308 -uroot -pxxx --defaults-file=/data/mysql/3308/my.cnf --coppy-back /tmp/xbk_full.sql 第三步 拷贝数据文件到数据目录中
chown -R mysql.mysql /data/mysql/3308/data 第四步 这里是将存放数据文件的数据目录的权限改为mysql.mysql
systemctl start mysqld3308 第五步 启动数据实例
5.4 innobackupex 增量备份
（1）增量备份的方式，是基于上一次备份进行增量。 （2）增量备份无法单独恢复。必须基于全备进行恢复。 （3）所有增量必须要按顺序合并到全备中。
过程：
（1）删掉原来备份 略. （2）全备（周日） [root@db01 backup]# innobackupex --defaults-file=/data/3307/mysql.sock --user=root --password=123 --no-timestamp /backup/full >&/tmp/xbk_full.log （3）模拟周一数据变化 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 --defaults-file=/data/3307/mysql.sock --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full /backup/inc1 &>/tmp/inc1.log （5）模拟周二数据 db01 [cs]>create table t2 (id int); db01 [cs]>insert into t2 values(1),(2),(3); db01 [cs]>commit; （6）周二增量 innobackupex --defaults-files=/data/3307/mysql.sock --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/inc1 /backup/inc2 &>/tmp/inc2.log （7）模拟周三数据变化 db01 [cs]>create table t3 (id int); db01 [cs]>insert into t3 values(1),(2),(3); db01 [cs]>commit; db01 [cs]>drop database cs;
恢复：
恢复思路： 1. 挂出维护页，停止当天的自动备份脚本 2. 检查备份：周日full+周一inc1+周二inc2，周三的完整二进制日志 3. 进行备份整理（细节），截取关键的二进制日志（从备份——误删除之前） 4. 测试库进行备份恢复及日志恢复 5. 应用进行测试无误，开启业务 6. 此次工作的总结
恢复过程：
1. 检查备份 1afe8136-601d-11e9-9022-000c2928f5dd:7-9 2. 备份整理（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 3. 截取周二 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 4. 进行恢复 [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
补充一下：
思考:在之前的项目案例中,如果误删除的表只有10M,而备份有500G,该如何快速恢复误删除表? 提示： 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;
2.从mysqldump全备中获取库和表的备份
1、获得表结构 # sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `city`/!d;q' full.sql>createtable.sql 2、获得INSERT INTO 语句，用于数据的恢复 # grep -i 'INSERT INTO `city`' full.sqll >data.sql & 3.获取单库的备份 # sed -n '/^-- Current Database: `world`/,/^-- Current Database: `/p' all.sql >world.sql
停掉数据库实例