1 基础概念
- 1.1 备份策略的设计
- 1.2 检查备份的可用性
2 逻辑备份之mysqldump
3 物理备份之XBK
4 迁移（5.6.44—>5.7）
- 4.1 安装5.6的环境
- 4.2 迁移5.6数据到5.7

1 基础概念

1.1 备份策略的设计

1、备份周期
根据数据量，以及各个公司自身特点，无强制标准
2、备份工具

mysqldump
XBK(PBK) Percona XtraBackup,
MEB(MySQL Enterprise BACKUP MEB) 这个是mysql企业版的备份工具
Mysqlbinlog

3、备份方式
逻辑备份：
全备 mysqldump，mysqldump只能做全备
增量 binlog(flush logs，cp)
物理备份：
全备：XBK
增量：XBK ，XBK的增量备份维护比较麻烦，在生产中一般还是采用binlog做增量

1.2 检查备份的可用性

检查可用性
crontab -l ——> 查看定时任务
备份脚本 ——> 查看备份脚本内容
备份路径 ——> 根据备份脚本，找到备份的路径
看备份日志，检查备份文件(大小，内容）
定期的恢复演练
比如铁路运输局会每个月进行恢复演练
数据恢复
只要备份和日志是完整的，恢复到故障之前的时间点，且时间要快速
数据迁移
mysql —> mysql
其他 —> mysql
mysql —> 其他
备份的类型
热备：对于业务影响最小，只有InnoDB支持，在业务运行过程中进行备份
温备：长时间锁表备份，MyISAM的特点
冷备：业务关闭情况下的备份

2 逻辑备份之mysqldump

2.1 备份参数

2.1.1 数据库链接参数

-u
-p
-S
-h
-p

2.1.2 基础备份参数

-A ——全备
]# mysqldump -uroot -pabc123.. -A >/backup/full.sql
-B ——对指定库备份
]# mysqldump -uroot -pabc123.. -B world wordpress >/backup/db.sql
库表 ——对指定库下的表进行备份
]# mysqldump -uroot -pabc123.. world city country >/backup/tab.sql

2.1.3 特殊备份参数

2.1.3.1 高级参数

以下三个参数是高级数据库开发的部分，在备份时默认跟上即可，保证可用性
-R 备份存储过程和函数，有的话就备，没有就跳过
-E 备份事件
—triggers 触发器

2.1.3.2 着重理解参数

—master-data=2
参数作用
此参数较为重要，跟上此参数后，在完成全备的那一刻会在binlog日志中打上一行标记，以便在截取binlog日志时，可以快速的定位到position号
1）记录备份时刻的binlog信息
2）自动锁表
不加 —single-transaction参数时，锁表，温备份
加了 —single-transcation参数时，对于InnoDB表不锁表备份（快照备份），但是对非innodb引擎无效
—single-transaction
对于InnoDB的表，进行一致性快照备份，不锁表，只对快照的内容进行备份，因此对业务不会阻塞，严格来讲不叫热备，因为mysqldump只是备份快照之前的数据，对在备份过程中产生的新的数据变化没有进行备份。

2.1.3.3 扩展参数

—set-gtid-purge=AUTO/ON 在构建主从时，必须使用AUTO/ON **
—set-gtid-purge=OFF 仅是做普通的本机备份恢复时，可以添加 **
注意：备份时可干脆不使用此参数，因为默认就是auto，其本质来讲，就是是否记录gtid号信息，因为构建主从时必须要使用gtid。虽对截取binlog日志时有影响，只需使用mysqlbinlog —skip-gtids即可，即导出时忽略gtid信息。
—maxallowed_packet=128M 控制的是备份时传输数据包的大小
完整备份命令：mysqldump -uroot -pabc123.. -A -R —max-allowed_packet=128M —triggers —set-gtid-purged=OFF —master-data=2 —single-transcation|gzip >/backup/full$(date +%F).sql.gz

mysql> select concat(“alter table “,table_schema,” “,table_name,” discard tablespace;”) from information_schema.tables where table_schema=’world’ into outfile ‘/tmp/discard.sql’
ERROR 1290 (HY000): The MySQL server is running with the —secure-file-priv option so it cannot execute this statement
mysql>select * from city into outfile ‘/tmp/city.csv’; 将数据导出以csv格式，通常在做异构平台迁移时会做此操作
出现上述错误，是因为into outfile会默认检查导出文件路径的安全性，如需解决，需要在my.cnf配置文件中添加如下参数secure-file-priv=

2.2 恢复案例1

恢复案例
1、背景环境:
正在运行的网站系统，mysql-5.7.20数据库，数据量50G，日业务增量1-5M。
2、备份策略
每天23:00点,计划任务调用mysqldump执行全备脚本
3、故障时间点
年底故障演练:模拟周三上午10点误删除数据库，并进行恢复．
4、恢复思路:
  1)停业务，避免数据的二次伤害
  2)找一个临时库,恢复周二23: 00全备
  3)截取周二23: 00---周三10点误删除之间的binlog，恢复到临时库
  4)测试可用性和完整性
5、两种方法
  5.1方法一:直接使用临时库顶替原生产库，前端应用割接到新库
  5.2方法二:将误删除的表导出，导入到原生产库
6、开启业务
处理结果: 经过20分钟的处理,最终业务恢复正常

2.2.1 恢复前环境准备

1、准备数据

create database backup;
use backup
create table t1 (id int);
insert into t1 values(1),(2),(3),(4),(5);
commit;

2、模拟每天数据备份，全备

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

3、模拟备份时间点后一段时间的数据变化

use backup
insert into t1 values(11),(22),(33),(44),(55);
commit;
create table t2 (id int);
insert into t2 values(66),(77),(88),(99),(100);
commit;

4、模式故障，删除库

drop database backup;

2.2.2 恢复过程

1、准备临时的数据库（多实例3307）
]# systemctl start mysqld-3307
2、截取二进制日志
截取从全备到发生故障这一段时间的二进制日志
]# cd /backup
]# gunzip full_2022-02-14.sql.gz
查看binlog日志文件，找到起始位置

分析binlog日志文件，找到故障发生前一个events事件的position号
show binlog events in ‘mysql-bin.000002’;

截图日志
因为binlog日志开启了gtid，因此导出时建议忽略原gtid号信息，所以加了—skip-gtids参数
]# mysqlbinlog —skip-gtids —start-position=763 —stop-position=1549 /data/mysql/instance01/mysql-bin.000002 > /backup/backup-bin.sql
3、恢复备份到临时库

mysql -S /data/mysql/instance-3307/mysql.sock
set sql_log_bin=0;
source /backup/full_2022-02-14.sql
source /backup/backup-bin.sql
set sql_log_bin=1;

4、将故障表导出并恢复到生产库

mysqldump -S /data/mysql/instance-3307/mysql.sock -B backup > /backup/backup.sql
mysql -uroot -pabc123..
set sql_log_bin=0;
source /backup/backup.sql;
set sql_log_bin=1;

2.3 恢复案例2

1、创建一个数据库，oldboy
2、在oldboy下创建一张表t1
3、插入5行任意数据
4、全备
5、插入2行数据，任意修改3行数据，删除1行数据
6、删除所有数据
7、在t1中又插入5行数据，修改3行数据
需求，跳过第6步恢复表数据

3 物理备份之XBK

3.1 安装

1、安装阿里云的epel源

wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo

2、安装依赖文件

yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libdev rsync perl perl-Digest-MD5
wget ftp://rpmfind.net/linux/atrpms/el6-x86_64/atrpms/stable/libev-4.04-2.el6.x86_64.rpm
rpm -ivh libev-4.04-2.el6.x86_64.rpm

3、下载并安装XBK安装包

wget https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.18/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.18-1.el7.x86_64.rpm
rpm -ivh percona-xtrabackup-24-2.4.18-1.el7.x86_64.rpm

3.2 使用

类似与cp命令进行文件拷贝
备份核心理念
1、针对非InnoDB，需要进行锁表备份，copy所有的非innoDB表文件
2、针对InnoDB表，立即触发CKPT（check point刷写脏页到磁盘），copy所有InnoDB表相关的文件(ibdata1,ibd,frm)，并且将备份过程中产生的新的数据变化的部分redo一起备走
3、在恢复时，XBK会调用InnoDB引擎的CSR过程，将数据和redo的LSN追平，然后进行一致性恢复
XBK全备和恢复体验

innobackupex --user=root --password=abc123.. --port=3306 --no-timestamp --socket=/tmp/mysql.sock /backup/xbk-full/
innobackupex --apply-log /backup/xbk-full/

备份产生的文件

重点文件介绍
1）xtrabackup_binlog_info
记录备份时刻的二进制日志信息，可以作为binlog截取的起点,还有gtid号

2）xtrabackup_checkpoints
from ：备份中包含的LSN号的起点，全备是0 ，增量：上次备份的结束位置
to ：ckpt时的LSN
Last-9 ：备份结束时的LSN，下次增量备份的起使位置
to_lsn跟last_lsn默认会相差9个数，属于xbk预留的，表示备份过程中没有新的数据变化

3.3 增量备份与恢复实操

备份策略：周一到周六增量备份，周日全备
1）清空历史备份数据
]# rm -rf /backup/xbk-full/
2）模拟数据

create database full charset utf8mb4;
use full;
create table t1(id int);
insert into t1 values(1),(2),(3);
commit;

3）进行周日的全备
]# innobackupex —user=root —password=abc123.. —socket=/tmp/mysql.sock —no-timestamp /backup/xbk-full/
4）模拟周一的数据变化

create database inc1 charset utf8mb4;
use inc1;
create table t1(id int);
insert into t1 values(1),(2),(3);
commit;

5）进行周一的增量备份
]# innobackupex —user=root —password=abc123.. —no-timestamp —socket=/tmp/mysql.sock —incremental-basedir=/backup/xbk-full/ —incremental /backup/inc1/
说明：
—incremental-basedir=/backup/xbk-full 基于哪个备份进行增量备份
—incremental 开关，打开增量备份功能
/backup/inc1 增量备份的位置
6）检查备份的LSN

7）模拟周二的数据变化

create database inc2 charset utf8mb4;
use inc2;
create table t1(id int);
insert into t1 values(1),(2),(3);
commit;

8）模拟周二的增量
]# innobackupex —user=root —password=abc123.. —no-timestamp —socket=/tmp/mysql.sock —incremental-basedir=/backup/inc1 —incremental /backup/inc2
检查备份的LSN

9）周三的数据变化

create database inc3 charset utf8mb4;
use inc3;
create table t1(id int);
insert into t1 values(1),(2),(3);
commit;

10）模拟上午十点数据库崩溃
pkill mysqld
rm -rf /data/mysql/data/
11）恢复思路
a、停业务，挂维护页
b、查找所有的可用备份：full+inc1+inc2
c、binlog:inc2到故障时间点的binlog
d、如何恢复备份
e、验证数据
f、启业务，撤销维护页面
12）恢复前的准备
—redo-only：
a、整理全备
]# innobackupex —apply-log —redo-only /backup/full
b、本并inc1到full，并整理备份
]# innobackupex —apply-log —redo-only —incremental-dir=/backup/inc1 /backup/full
c、合并inc2到full，并整理备份
]# innobackupex —apply-log —incremental-dir=/backup/inc2 /backup/full
d、最后依次整理到full
]# innobackupex —apply-log /backup/full
13）截取二进制日志
起点：

终点：
]# mysqlbinlog /data/binlog/mysql-bin.000004
SET @@SESSION.GTID_NEXT= ‘9b72d814-5bc9-11eb-a144-000c2985474e:17’/!/;
截图日志：
]# mysqlbinlog —skip-gtids —include-gtids=’9b72d814-5bc9-11eb-a144-000c2985474e:15-17’ /data/binlog/mysql-bin.000004 >/backup/binlog.sql
14）恢复备份数据
[root@db01 ~]# cp -a /backup/full/ /data/mysql/data/
[root@db01 ~]# chown -R mysql.mysql /data/
[root@db01 ~]# /etc/init.d/mysqld start
mysql> set sql_log_bin=0;
mysql> source /backup/binlog.sql
15）验证数据
mysql> select from full.t1 ;
mysql> select from inc1.t1 ;
mysql> select from inc2.t1 ;
mysql> select from inc3.t1 ;

4 迁移（5.6.44—>5.7）

4.1 安装5.6的环境

1）创建必须的目录
]# mkdir /data/mysql/data/ /application/ /data/binlog -p
上传软件至application并解压
2）建用户改权限
]# useradd mysql -s /sbin/nologin
]# chown -R mysql.mysql /data /application
3)修改环境变量
]# vim /etc/profile
]# export PATH=/application/mysql/bin:$PATH
]# source /etc/profile
4)数据初始化
]# yum install -y libaio-devel
]# mysql_install_db —user=mysql —basedir=/application/mysql —datadir=/data/mysql/data
5)准备配置文件和启动脚本
]# vim /etc/my.cnf

[mysql]
basedir=/application/mysql
datadir=/data/mysql/data
server_id=99
user=mysql
log_bin=/data/binlog/mysql-bin
binlog_format=row
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock

启动数据库
]# cp /application/mysql/support-files/mysql.server /etc/init.d/mysqld
]# /etc/init.d/mysqld start
]# mysqladmin -uroot -p password abc123..

4.2 迁移5.6数据到5.7

1) 数据库备份
]# mysqldump -uroot -pabc123.. -A —master-data=2 —single-transcation -R -E —triggers >/tmp/full.sql
]# scp /tmp/full.sql 10.0.0.51:/data/3308
2) 准备5.7的数据库
]# systemctl start mysqld3308
]# mysql -S /data/3308/mysql.sock
mysql>source /data/3308/full.sql
]# mysql_upgrade -uroot -pabc123.. -S /data/3308/mysql.sock 将5.6相关的一些系统数据升级到新版本格式
3) binlog的持续追加
4）停止业务，恢复剩余的binlog
5）业务割接

MySQL 数据库

第10章-备份与恢复