MySQL的备份恢复

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.1 备份的策略

2.2 备份的工具

2.3 备份类型

热备 : 对于业务影响最小   InnoDB

温备 : 长时间锁表备份     MyISAM

冷备 : 业务关闭情况下备份 

2.4. mysqldump  

2.4.1 连接数据库 

-u     -->用户

-p     -->密码

-S     --> scoker文件路径

-h     -->IP地址

P      --> 端口

2.4.2 基础备份参数

-A 

同参数--all-databases，备份所有库，并在备份文件中生成创建库的语句及use语句。这样方便恢复时不用创建指定数据库

[root@db01 backup]# mysqldump -uroot -p123 -A  >/backup/full.sql

-B

此参数用于指定多个数据库，同-A参数，生成创建库的语句及use语句。

[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

2.4.3 特殊备份参数

-R 存储过程和函数

-E 事件

--triggers 触发器

--master-data=2     

(1) 记录备份时刻的binlog信息

(2) 自动锁表

不加--single-transaction ,温备份

加了--single-transaction,对于InnoDB表不锁表备份(快照备份)

--single-transaction 

对于InnoDB的表,进行一致性快照备份,不锁表.

3.1 背景环境：

正在运行的网站系统，mysql-5.7.20 数据库，数据量50G，日业务增量1-5M。

3.2 备份策略：

每天23:00点，计划任务调用mysqldump执行全备脚本

3.3 故障时间点：

年底故障演练:模拟周三上午10点误删除数据库.

3.4 思路：

1、停业务，挂维护页,避免数据的二次伤害

2、找一个临时库，恢复周二23：00全备

3、截取周二23：00  --- 周三10点误删除之间的binlog，恢复到临时库

4、测试可用性和完整性

5、 

    5.1 方法一：直接使用临时库顶替原生产库，前端应用割接到新库

    5.2 方法二：将误删除的表导出，导入到原生产库

6、开启业务

处理结果：经过20分钟的处理，最终业务恢复正常

3.5 故障模拟演练

3.5.1 准备数据

mysql> create database backup;
mysql> use backup
mysql> create table t1 (id int);
mysql> insert into t1 values(1),(2),(3);
mysql>  commit;
[root@db01 backup]# rm -rf /backup/*

3.5.2 周二 23：00全备

[root@db01 backup]#  mysqldump -uroot -p123 -A  -R  --triggers --set-gtid-purged=OFF --master-data=2  --single-transaction|gzip > /backup/full_$(date +%F).sql.gz

3.5.3 模拟周二 23：00到周三 10点之间数据变化

mysql> use backup
mysql> insert into t1 values(11),(22),(33);
mysql> commit;
mysql> create table t2 (id int);
mysql> insert into t2 values(11),(22),(33);
mysql> commit;

3.5.4 模拟故障,删除表(只是模拟，不代表生产操作)

mysql> drop database backup;

3.6 恢复过程

3.6.1 准备临时数据库（多实例3307）

[root@db01 backup]# systemctl start mysqld3307

3.6.2 准备备份

（1）准备全备：

[root@db01 backup]# cd backup

[root@db01 backup]# gunzip full_2018-10-14.sql.gz 

（2）截取二进制日志

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

753

1519 

[root@db01 backup]# mysqlbinlog --skip-gtids   --start-position=753 --stop-position=1519  /data/binlog/mysql-bin.000002 >/backup/bin.sql

3.6.3 恢复备份到临时库

[root@db01 backup]# mysql -S data/3307/mysql.sock
mysql> set sql_log_bin=0;
mysql> source /backup/full_2019-07-15.sql
mysql> source /backup/bin.sql

3.6.4 将故障表导出并恢复到生产

[root@db01 backup]#  mysqldump   -S data/3307/mysql.sock -B  backup  >/backup/bak.sql
[root@db01 backup]#  mysql -uroot -p123 
mysql> set sql_log_bin=0
mysql> source /backup/bak.sql;

1、创建一个数据库 oldboy

2、在oldboy下创建一张表t1

3、插入5行任意数据

4、全备

5、插入两行数据，任意修改3行数据，删除1行数据

6、删除所有数据

7、再t1中又插入5行新数据，修改3行数据

需求，跳过第六步恢复表数据

6. 扩展参数  ***

在构建主从时,使用AUTO/ON

mysql> --set-gtid-purged=AUTO/ON

仅是做普通的本机备份恢复时,可以添加

--set-gtid-purged=OFF  

mysql >  SET @@GLOBAL.GTID_PURGED='aa648280-a6a6-11e9-949f-000c294a1b3b:1-11';

--max_allowed_packet=128M  控制的是备份时传输数据包的大小.

[root@db01 backup]#  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

7. 物理备份-XBK

7.1 安装依赖包：

[root@db01 backup]# wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
[root@db01 backup]# yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev

7.2 下载软件并安装

[root@db01 backup]# 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
[root@db01 backup]# 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
[root@db01 backup]# yum -y install percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm

7.3 innobackupex 使用 

7.3.1 备份核心理念

1. 针对非InnoDB,进行锁表备份,copy所有的非innoDB表文件

2. 针对InnoDB表,立即触发CKPT,copy所有InnoDB表相关的文件(ibdata1,ibd,frm).

并且将备份过程中产生,新的数据变化的部分redo一起备份走

3. 在恢复时,xbk会调用InnoDB引擎的CSR过程,将数据和redo的LSN追平,然后进行一致性恢复.

7.3.2 备份过程

(1) 全备

[root@db01 backup]# innobackupex  --user=root --password=123 --no-timestamp /backup/full

(2) 利用全备进行恢复

1. kill 进程服务

[root@db01 ~]# pkill mysqld

2. 删除data目录

[root@db01 ~]# \rm -rf /data/mysql/data/*

3. 恢复

[root@db01 ~]# innobackupex --apply-log /backup/full/

4. 复制文件到data目录

[root@db01 full]# cp -a /backup/full/* /data/mysql/data/

5. 将文件属组权限修改成mysql

[root@db01 full]# chown -R mysql.mysql /data/mysql/data/*

6. 启动服务

[root@db01 full]# /etc/init.d/mysqld start