MySQL数据备份
一、 MySQL数据损坏类型
1.1、物理损坏
磁盘损坏:
硬件，磁道坏，dd,格式化
文件损坏: 数据文件损坏，redo损坏
1.2、逻辑损坏
drop
delete
truncate
update
二、DBA运维人员在备份、恢复的职责
2.1 、设计备份、容灾策略
2.1.1、 备份策略:
备份工具选择
备份周期设计
备份监控方法
2.1.2、 容灾策略
备份:用什么 备份。
架构:
高可用，演示从库，灾备库
2.2、定期的备份、容灾检查
备份软件----->带库
每周北京---d-->天津货运
2.3、定期的故障恢复演练
2.4、数据损坏时的快速且准确恢复
2.5、数据迁移工作
三、MySQL常用备份工具
3.1 逻辑备份方式
mysqldump (MDP ) 重点重点重点
repl ication
mydumper ( 自行扩展)
load data in file (自行扩展)
3.2物理备份方式
MySQL Enterprise Backup (企业版)
Percona Xtrabackup ( PBK, XBK ) 重点重点重点
四、mysqldump应用
最终语法:
mysqldump -uroot -p123 --master-data=2 --single-transaction -R -E --triggers -A --max-allowed-packet=64M > /data/backup/full.sql
4.1、介绍
逻辑备份工具。备份的是SQL语句。
4.2、备份方式:
4.2.1、 InnoDB表
可以采取快照备份的方式。
开启一个独立的事务，获取当前最新的--致性快照。
将快照数据，放在临时表中，转换成SQL (Create database, Create table，insert) ，保存到sq1文件中。
4.2.2、非InnoDB表
需要锁表备份。触发FTWRL, 全局锁表。转换成SQL (Create database, Create table，insert) ，保存到sq1文件中。
4.3 、mysqldump的核心参数
4.3.1、连接参数
-P 端口
-h ip
-P 密码
-S scokt那玩意----可省略
4.3.2、备份参数
-A：全备
# 准备下备份目录
mkdir /data/backup
chown -R mysql.mysql /data/backup/
# 完整命令
mysqldump -uroot -p123 -S /tmp/mysql.sock -A > /data/backup/full.sql
-B：备份单库或者多库
# 备份一个或者多个
mysqldump -uroot -p123 -B school gtdb > /data/backup/BB.sql
备份单表或者多表
mysqldump -uroot -p123 school course student > /data/backup/CC.sql
参数：固定写法
school #库名
course studen #school 库下的表名
-F 备份的时候切割一次日志
mysqldump -uroot -p123 -F school course student > /data/backup/CC.sql
面试题：
验证一下:
以下两个命令的备份结果区别?
mysqldump -uroot -p123 -B world >/data/backup/db1.sql
create database world;
use world;
--------------------------------------------------------------------------------------
以下命令，应用时，world库不存在，需要手工创建，并且use到world库下再恢复。
mysqldump -uroot -p123 world > /data/backup/db2.sql
4.3.3、高级备份参数
4.3.3.1、--master-data=2
区别：1和2的区别就是一个注释一个注释，有一行记录
--master-data=2
--master-data=1
功能：
1.备份时自动记录binlog信息
2.自动锁表和解锁
3.配合single transaction 可以减少锁表时间?
场景:
每周日23:00 全备，周1-6 binlog备份。所有备份是完整的。
周三时，有一个核心运维人员进行了删库操作。
恢复全备到周日23:00的
所有需要binlog恢复
痛点: binlog的截取
起点查找比较困难:
方法一:备份开始时，切割日志。-F
方法二:备份开始时，自动记录日志文件信息，--master-data=2
终点: drop之前的位置点。
用法：
# 备份语句加上--master-data=2即可，那种备份方式都行
mysqldump -uroot -p123 --master-data=2 -A > /data/backup/full.sql
4.3.3.2、--single-transaction
作用：
对于InnoDB引擎表备份时，开启一个独立事务，获取一致性快照，进行备份。
用法：
mysqldump -uroot -p123 --master-data=2 --single-transaction -A > /data/backup/full.sql
4.3.3.3、-R -E --trigger
-R # 备份存储过程及函数
--triggers # 备份触发器
-E # 备份事件
用法：
mysqldump -uroot -p123 --master-data=2 --single-transaction -R -E --triggers -A > /data/backup/full.sql
4.3.3.4、--max-allowed-packet=
作用：
表过大，加上这个参数就行，64不够就128----
用法：
mysqldump -uroot -p123 --master-data=2 --single-transaction -R -E --triggers -A --max-allowed-packet=64M > /data/backup/full.sql
4.4、数据恢复案例
4.4.1、案例场景：
基础环境:
Centos 7.6 + MySQL 5.7.28 , LNMT网站业务上数据量100G,每天5-10M数据增长。
备份策略: mysqldump每天全备， binlog定时 备份。
故障模拟:
周三 上午10点数据故障，例如:核心业务库被误删除。
恢复思路:
1.挂维护页。
2.找测试库。
3.恢复周二全备。
4.截取周二全备---> 周三上午10点误删除之前的binlog, 并恢复
5.测试业务功能正常
6.恢复业务:
方案1:故障库导回到原生产
方案2:直接用测试库称当生产，先跑着。
4.4.2、数据准备：
create database dumpdb charset utf8mb4;
use dumpdb;
create table t1 (id int);
begin;
insert into t1 values(1),(2),(3);
insert into t1 values(11),(22),(33);
commit;
begin;
insert into t1 values(4),(5),(6);
insert into t1 values(44),(55),(66);
commit;
4.4.3、模拟周二晚上全备
mysqldump -uroot -p123 --master-data=2 --single-transaction -R -E --triggers -A --max-allowed-packet=64M > /data/backup/full_`date +%F`.sql
4.4.4、模拟周三白天的数据变化
create database mdp charset utf8mb4;
use mdp;
create table t2 (id int);
insert into t2 values(1),(2),(3);
insert into t2 values(11),(22),(33);
commit;
4.4.5、模拟数据破坏
# 因为还没到晚上11点，此时的数据没备份，所以只能手动恢复
drop database mdp;
4.4.6、数据恢复！
1、检查全备，找到全备使用时候的二进制文件（截取起点用）
[root@master backup]# grep ^"-- CHANGE MASTER TO MASTER_LOG_FILE" full_2020-11-15.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=1270;
2、恢复全备
tzh>set sql_log_bin=0; #5.7以后不用加
tzh>source /data/backup/full_2020-11-15.sql;
3、截取起点
起点：
[root@master backup]# grep ^"-- CHANGE MASTER TO MASTER_LOG_FILE" full_2020-11-15.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=1270;
终点:
# 看看现在在使用那个日志文件
tzh>show master status;
# 查看详细信息找到dorp前的gtid
tzh>show binlog events in 'mysql-bin.000006';
| mysql-bin.000006 | 1958 | 2023 | SET @@SESSION.GTID_NEXT= 'f9511c39-1e28-11eb-8a8e-000c29af70c3:16' |
| mysql-bin.000006 | 2023 | 2112 | drop database mdp
position截取：
起点：MASTER_LOG_POS=1270
终点：MASTER_LOG_POS=2023
命令：
mysqlbinlog --skip-gtids --start-position=1270 --stop-position=2023 /data/mysql/binlog/mysql-bin.000006 > /data/backup/pos.sql
恢复：
source /data/backup/pos.sql
验证:
tzh>show databases;
tzh>select * from mdp.t2; #跟插入时的数据一样，成功恢复！nice！
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 11 |
| 22 |
| 33 |
+------+
------------------------------------------------------------------------
# 再次drop掉mdp库，模拟另一个方法，此时的日志是不写入日志文件的set sql_log_bin=0;了
查看建库时候使用的日志文件找到gtid
tzh>show binlog events in 'mysql-bin.000006';
| mysql-bin.000006 | 1270 | 1335 | SET @@SESSION.GTID_NEXT= 'f9511c39-1e28-11eb-8a8e-000c29af70c3:13' |
| mysql-bin.000006 | 1335 | 1442 | create database mdp charset utf8mb4
gtid截取：
起点：f9511c39-1e28-11eb-8a8e-000c29af70c3:13
终点：f9511c39-1e28-11eb-8a8e-000c29af70c3:16-1 得减1，要不然又drop了
命令：
mysqlbinlog --skip-gtids --include-gtids='f9511c39-1e28-11eb-8a8e-000c29af70c3:13-15' /data/mysql/binlog/mysql-bin.000006 > /data/backup/gtid.sql
恢复：
source /data/backup/gtid.sql
验证:
tzh>show databases;
tzh>select * from mdp.t2; #跟插入时的数据一样，成功恢复！nice！
最后：
tzh>set sql_log_bin=1; #5.7以后不用加
4.5、mysqldump总结
（在生产中恢复要谨慎，恢复会删除重复的表）
参数:
-u P
-S -h -P
-A -B
- master-data=2 --single-transaction -R -E --triggers --max allowed packet=64
选择场景:
优点:可读性比较强，压缩比，节省空间，不需要下载安装。
缺点:备份时间相对较长。恢复时间长。
数据量较少，建议mysqldump.100G以内。
数据量巨大:分布式架构，数据量较大时候，可以采用分布式备份。也可以选择mysqldump
五、percona Xtrbackup
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
yum -y install percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm
5.2、介绍
物理备份工具.拷贝数据文件．InnoDB表:
热备份：业务正常发生的时候，影响较小的备份方式。
1. checkpoint，将已提交的数据页刷新到磁盘。记录一个LSN。
2．拷贝InnoDB表相关的文件（ibdatal , frm ibd..)
3．备份期间产生新的数据变化的redo也会备份走。
非工nnoDB表:
温备份：锁表备份。
1.FTWRL ，触发全局锁。
2.拷贝非工nnoDB表的数据
3.解锁
再次统计LSN,写入到专用文件。记录二进制日志位置记录下来。
所有备份文件统一存放在一个目录下。
5.3、应用
5.3.1、使用该工具前提条件
(1）数据库启动
(2）能连上数据库
# my.cnf文件中指定Xtrbackup要连接的sock号，也可以加参数指定
[client]
socket=/ tmp/ mysql.sock
(3)默认会读取[mysqld]---> datadir=
(4)属于服务端工具
5.3.2、全备
[root@master backup]# innobackupex --user=root --password=123 /data/backup
[root@db01 backup]# innobackupex --user=root --password=123 --no-timestamp /data/backup/full_`date +%F`
全备查看:
[root@master ~]# cd /data/backup/
[root@master backup]# ll
total 804
drwxr-x--- 12 root root 328 Nov 17 07:04 2020-11-17_07-04-36
[root@master backup]# cd 2020-11-17_07-04-36/
[root@master 2020-11-17_07-04-36]# ll
total 536628
-rw-r----- 1 root root 500 Nov 17 07:04 backup-my.cnf
drwxr-x--- 2 root root 48 Nov 17 07:04 bindb
drwxr-x--- 2 root root 48 Nov 17 07:04 dumpdb
drwxr-x--- 2 root root 104 Nov 17 07:04 gtdb
-rw-r----- 1 root root 423 Nov 17 07:04 ib_buffer_pool
-rw-r----- 1 root root 12582912 Nov 17 07:04 ibdata1
-rw-r----- 1 root root 536870912 Nov 17 07:04 ibdata2
drwxr-x--- 2 root root 48 Nov 17 07:04 mdp
drwxr-x--- 2 root root 4096 Nov 17 07:04 mysql
drwxr-x--- 2 root root 8192 Nov 17 07:04 performance_schema
drwxr-x--- 2 root root 160 Nov 17 07:04 school
drwxr-x--- 2 root root 8192 Nov 17 07:04 sys
drwxr-x--- 2 root root 20 Nov 17 07:04 test
-rw-r----- 1 root root 63 Nov 17 07:04 xtrabackup_binlog_info
-rw-r----- 1 root root 113 Nov 17 07:04 xtrabackup_checkpoints
-rw-r----- 1 root root 539 Nov 17 07:04 xtrabackup_info
-rw-r----- 1 root root 2560 Nov 17 07:04 xtrabackup_logfile
drwxr-x--- 2 root root 48 Nov 17 07:04 zabbix
文件分析：
xtrabackup_binlog_info ：
# 备份起点日志,binlig的截取起点
[root@master 2020-11-17_07-04-36]# cat xtrabackup_binlog_info
mysql-bin.000008 194 f9511c39-1e28-11eb-8a8e-000c29af70c3:1-16
xtrabackup_checkpoints：
# 记录备份类型、lsn，方便做增量备份
[root@master 2020-11-17_07-04-36]# cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 4222237
last_lsn = 4222246
compact = 0
recover_binlog_info = 0
xtrabackup_info :
# 备份总信息
5.3.3、备份恢复步骤
# 模拟数据丢失
pkill mysqld
# 全备恢复必须是空目录
rm -rf /data/mysql/*
# 备份处理：perpare
redo 前滚，undo回滚、模仿CRS过程
[root@master backup]# innobackupex --apply-log /data/backup/full_2020-11-17/
# 数据恢复
cp -r /data/backup/full_2020-11-17/* /data/mysql/
chown -R mysql:mysql /data/mysql/
/etc/init.d/mysqld start
5.4、增量备份
增量备份原理图解：
5.4.1、模拟xbk增量备份
- 周三的from_lsn—9==一定等于=周二的last_lsn (说明备份成功)
- 同一次备份：to_lsn+9=last_lsn=说明本次备份没有发生数据的变化
（1）增量备份的方式，是基于上一次备份进行增量。
（2）增量备份无法单独恢复。必须基于全备进行恢复。
（3）所有增量必须要按顺序合并到全备中。
（1）删掉原来备份
略.
（2）全备（周日）
[root@db01 backup]# innobackupex --user=root --password=123 --no-timestamp /data/backup/full_`date +%F` >&/tmp/xbk_full.log
（3）模拟周一数据变化
create database cs charset utf8;
use cs
create table t1 (id int);
insert into t1 values(1),(2),(3);
commit;
（4）第一次增量备份（周一）
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/data/backup/full_2020-11-17/ /data/backup/inc1 &>/tmp/inc1.log
参数说明：
--incremental 开启增量备份开关
--incremental-basedir 增量备份参照物（上一天的）
/data/backup/inc1 增量备份路径
（5）模拟周二数据
create table t2 (id int);
insert into t2 values(1),(2),(3);
commit;
（6）周二增量,切忌--incremental-basedir要是前一天的数据目录
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/data/backup/inc1 /data/backup/inc2 &>/tmp/inc2.log
（7）模拟周三数据变化
create table t3 (id int);
insert into t3 values(1),(2),(3);
commit;
drop database cs;
5.4.2、恢复到周三误drop之前的数据状态
恢复思路：
1. 挂出维护页，停止当天的自动备份脚本
2. 检查备份：周日full+周一inc1+周二inc2，周三的完整二进制日志
3. 进行备份整理（细节），截取关键的二进制日志（从备份——误删除之前）
4. 测试库进行备份恢复及日志恢复
5. 应用进行测试无误，开启业务
6. 此次工作的总结
增量备份恢复逻辑(恢复到周三)：
恢复过程：
1. 检查备份
1afe8136-601d-11e9-9022-000c2928f5dd:7-9
- 周三的from_lsn—9==一定等于=周二的last_lsn (说明备份成功)
- 同一次备份：to_lsn+9=last_lsn=说明本次备份没有发生数据的变化
-------------------------------------------------------------
2. 备份整理（apply-log）+合并备份（full+inc1+inc2）
# 参数说明
--apply-log 只合并redo日志，且最后一次合并不能加！！！
# 四步操作：
(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
#起点：最后一次备份记录的d6caac23-2863-11eb-9bba-000c29af70c3:1-9，就是起点，恢复什么数据都是拿这个做起点
[root@master backup]# cat /data/backup/inc2/xtrabackup_binlog_info
mysql-bin.000002 2263 d6caac23-2863-11eb-9bba-000c29af70c3:1-9,
f9511c39-1e28-11eb-8a8e-000c29af70c3:1-16
#终点：找到drop时间的gtid，如果是rm就是末尾，不用指定gtid终点
| mysql-bin.000002 | 2112 | Gtid 2177 | SET @@SESSION.GTID_NEXT= 'd6caac23-2863-11eb-9bba-000c29af70c3:9'
| mysql-bin.000002 | 2177 | Query 2263 | drop database cs
[root@db01 inc2]# mysqlbinlog --skip-gtids --include-gtids='d6caac23-2863-11eb-9bba-000c29af70c3:1-8' /data/mysql/binlog/mysql-bin.000002 >/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
拓展:
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