小桥流水78

这个屌丝很懒，什么也没留下！

热门标签

MySQL备份恢复_mysql 主从备份 --single-transaction --set-gtid-purged=

作者：小桥流水78 | 2024-08-16 17:56:15

踩

mysql 主从备份 --single-transaction --set-gtid-purged=on

备份类型

热备（Hot Backup）：在数据库正常业务时直接备份数据，并且能够一致性恢复（只能是Innodb），对正在运行的数据库没有任何影响。
温备（Warm Backup）：锁表备份，只能查询不能修改（MyISAM），影响到写入操作。
冷备（Cold Backup）：关闭数据库业务，在数据库没有任何变更的情况下，进行备份数据。

备份方式及工具

逻辑备份工具

基于SQL语句进行备份

mysqldump
mysqlbinlog

物理备份工具

基于磁盘数据文件备份

xtrabackup（XBK）：percona公司
MySQL Enterprise Backup（MEB）

逻辑备份与物理备份比较

mysqldump（MDP）

优点：

不需要下载安装，MySQL自带
备份出来的是SQL，文本格式，可读性高，便于备份处理
压缩比较高，节省备份的磁盘空间

缺点：

依赖数据库存储引擎，需要从磁盘把数据读出，然乎后换成SQL进行转储，比较耗费资源，数据量大的话效率会很低。

建议：

100G以内的数据量级，可以使用mysqldump
100G-TB的数量级，建议使用xtrabackup
超过TB及以上，可以配合分布式系统选择mysqldump（磁盘空间限制）

xtrabackup（XBK）

优点：

类似于直接cp数据文件，不涉及逻辑结构，相对来说性能较高

缺点：

可读性差
压缩比低，需要更多的磁盘空间

备份策略

备份方式
全量备份：备份所有数据
增量备份：备份变化的数据
逻辑备份 = mysqldump + mysqlbinlog
物理备份 = xtrabackup_full + xtrabackup_incr + binlog 或者 xtrabackup_full + binlog
备份周期
根据数据量设计备份周期，如：周日全备，周一至周六增量备份

备份工具使用

mysqldump

客户端通用参数

-u, --user=name User for login if not current user.
-p, --password[=name] Password to use when connecting to server.
-h, --host=name Connect to host.
-P, --port=# Port number to use for connection.
-S, --socket=name The socket file to use for connection.

本地备份

mysqldump -uroot -p -S /tmp/mysql.sock  ...
1

远程备份

mysqldump -uroot -p  -h ip -P3306 ...
1

备份专用参数

-A 全备参数

[root@rhel7 ~]# mkdir -p /data/backup
[root@rhel7 ~]# mysqldump -uroot -p -A >/data/backup/full.sql
Enter password: 
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 
[root@rhel7 ~]# ll /data/backup/
total 772
-rw-r--r--. 1 root root 787250 Oct  1 15:47 full.sql
1
2
3
4
5
6
7

查看备份数据

[root@rhel7 ~]# vim /data/backup/full.sql 

-- MySQL dump 10.13  Distrib 5.7.20, for linux-glibc2.12 (x86_64)
--
-- Host: localhost    Database: 
-- ------------------------------------------------------
-- Server version       5.7.20-log
 
 以上为备份数据头部信息（重要，恢复时需要通过头部信息识别）
 
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
...
1
2
3
4
5
6
7
8
9
10
11
12
13

补充：

常规备份要加 --set-gtid-purged=OFF，解决备份时的警告（gtid从头开始记录）

[root@rhel7 ~]# mysqldump -uroot -p123456 --set-gtid-purged=OFF -A >/data/backup/full.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
1
2

构建主从复制环境时，需要设置为on

[root@rhel7 ~]# mysqldump -uroot -p123456 -A --set-gtid-purged=ON >/backup/full.sql
1

-B db1 db2 db3 备份多个单库
例如：备份mysql、user、data库的数据

mysqldump -uroot -p123456 -B mysql user data --set-gtid-purged=OFF >/data/backup/db.sql
1

备份单个表或多个表（无需参数）
例如：备份world库下的city表和country表
```
mysqldump -uroot -p123456 world city country >/backup/table.sql
1
```
例如：对整个数据库下除了sys，performance，information，对其他数据库下的所有表进行单独备份

select concat(“mysqldump -uroot -p123456 -S /tmp/mysql.sock “,table_schema,” “,table_name,” >/data/backup/”,table_schema,"_",table_name,".sql") from information_schema.tables where table_schema not in (‘sys’,‘information_schema’,‘performance_schema’) into outfile ‘/tmp/mysqldump.sh’;

高级参数应用

-R -E --triggers

生产环境备份必须添加的参数：
-R, --routines Dump stored routines (functions and procedures).备份存储过程及函数
–triggers Dump triggers for each dumped table.备份触发器
-E, --events Dump events.备份事件

mysqldump -uroot -p123456 -A -R -E --triggers > /data/backup/full.sql
1

-F

-F, --flush-logs Flush logs file in server before starting dump.在备份开始前刷新一个新binlog

mysql [(none)]>show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1846 |
| mysql-bin.000002 |       194 |
| mysql-bin.000003 |       217 |
| mysql-bin.000004 |       194 |
+------------------+-----------+
4 rows in set (0.13 sec)


[root@rhel7 ~]# mysqldump -uroot -p123456 -A -E --triggers -R -F> /data/backup/full.sql


mysql [(none)]>show binary logs;    --可见每一个库都会刷新一个binlog
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1846 |
| mysql-bin.000002 |       194 |
| mysql-bin.000003 |       217 |
| mysql-bin.000004 |       241 |
| mysql-bin.000005 |       241 |
| mysql-bin.000006 |       241 |
| mysql-bin.000007 |       241 |
| mysql-bin.000008 |       241 |
| mysql-bin.000009 |       241 |
| mysql-bin.000010 |       194 |
+------------------+-----------+
10 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

–master-data[=#]

–master-data[=#] This causes the binary log position and filename to be appended to the output. If equal to 1, will print it as a CHANGE MASTER command; if equal to 2, that command will be prefixed with a comment symbol. This option will turn --lock-all-tables on, unless --single-transaction is specified too (in which case a global read lock is only taken a short time at the beginning of the dump; don’t forget to read about --single-transaction below). In all cases, any action on logs will happen at the exact moment of the dump. Option automatically turns --lock-tables off.

在备份时，会自动记录二进制日志文件名和位置号（最后一个事件的结束位置号）
自动锁表
配合–single-transaction使用，只对非InnoDB进行锁表备份，InnoDB表进行热备（实际是实现快照备份）

[root@rhel7 ~]# mysqldump -uroot -p123456 -A -E --triggers -R -F --master-data=2> /data/backup/full.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@rhel7 ~]# vim /data/backup/full.sql 

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000011', MASTER_LOG_POS=194;       
1
2
3
4
5

–single-transaction

–single-transaction Creates a consistent snapshot by dumping all tables in a single transaction. Innodb存储引擎开启热备（快照备份）功能
master-data可以自动锁表：

不加–single-transaction，启动所有表的温备份，所有表都锁定
加–single-transaction，对innodb进行快照备份，对非innodb表实现自动锁表功能

[root@rhel7 ~]# mysqldump -uroot -p123456 -A -E --triggers -R -F --master-data=2 --single-transaction> /data/backup/full.sql
1

–set-gtid-purged

–set-gtid-purged[=name] Add ‘SET @@GLOBAL.GTID_PURGED’ to the output. Possible values for this option are ON, OFF and AUTO.

常规备份要加 --set-gtid-purged=OFF，解决备份时的警告（gtid从头开始记录）

[root@rhel7 ~]# mysqldump -uroot -p123456 --set-gtid-purged=OFF -A >/data/backup/full.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
1
2

构建主从复制环境时需要的参数配置

[root@rhel7 ~]# mysqldump -uroot -p123456 -A --set-gtid-purged=ON >/backup/full.sql
1

–max-allow-packet=#

–max-allowed-packet=# The maximum packet length to send to or receive from server.最大数据包大小

mysqldump -uroot -p123456 -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=OFF --max-allowed-packet=256M >/data/backup/full.sql
1

压缩备份并添加时间戳

mysqldump -uroot -p123456 -A  -R  --triggers --master-data=2  --single-transaction|gzip > /backup/full_$(date +%F).sql.gz
mysqldump -uroot -p123456 -A  -R  --triggers --master-data=2  --single-transaction|gzip > /backup/full_$(date +%F-%T).sql.gz
1
2

mysqldump备份的恢复方式（在生产中恢复要谨慎，恢复会删除重复的表）

> set sql_log_bin=0;
> source /backup/full_2018-06-28.sql
1
2

注意：

mysqldump 在备份和恢复时都需要mysql实例启动为前提
一般数据量级100G以内，大约15-45分钟可以恢复，数据量级很大很大的时候（PB、EB）
mysqldump是覆盖形式恢复的方法

一般在同数据量级，物理备份要比逻辑备份速度快，逻辑备份的优势:

可读性强
压缩比很高

案例：模拟故障恢复

环境说明

每天全备
binlog日志完整
模拟白天数据变化
模拟下午两点删除数据库

需求说明

利用全备+binlog恢复到删库之前

故障模拟

模拟周一23：00的全备

[root@rhel7 ~]# mysqldump -uroot -p123456 -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF > /data/backup/full.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
1
2

模拟白天的数据变化

mysql [(none)]>create database day1 charset utf8;
Query OK, 1 row affected (0.12 sec)

mysql [(none)]>use day1;
Database changed
mysql [day1]>create table t1(id int);
Query OK, 0 rows affected (0.95 sec)

mysql [day1]>insert into t1 values(1),(2),(3);
Query OK, 3 rows affected (0.24 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql [day1]>commit;
Query OK, 0 rows affected (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

模拟磁盘损坏

[root@rhel7 mysql]# rm -rf /data/mysql
1

恢复思路

检查备份可用性

[root@localhost ~]# ll /data/backup/
total 764
-rw-r--r--. 1 root root 781346 Oct  1 20:04 full.sql
1
2
3

从备份中获取二进制日志位置

[root@localhost ~]# vim /data/backup/full.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=745;
1
2

根据日志位置截取需要的二进制日志

[root@localhost ~]# mysqlbinlog --start-position=745 --skip-gtids /data/binlog/mysql-bin.000001 > /data/backup/bin.sql
1

初始化数据库并启动

[root@localhost ~]# mkdir /data/mysql -p 
[root@localhost ~]# chown mysql.mysql /data/mysql
[root@localhost ~]# mysqld --initialize-insecure --user=mysql --datadir=/data/mysql --basedir=/app/mysql
1
2
3

恢复全备
```
> source /data/backup/full.sql
1
```
恢复二进制日志
```
> source /data/backup/bin.sql
1
```

案例：企业故障恢复（演练）

案例概述

背景环境

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

备份策略

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

故障时间

年底故障演练:模拟周三上午10点误删除数据库，并进行恢复.

处理思路

停业务，避免数据的二次伤害
找一个临时库，恢复周三23：00全备
截取周二23：00 — 周三10点误删除之间的binlog，恢复到临时库
测试可用性和完整性
方法一：直接使用临时库顶替原生产库，前端应用割接到新库
方法二：将误删除的表导出，导入到原生产库
开启业务

故障模拟

准备数据

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

周二23:00全备

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

-A：全备
-R：备份函数及存储过程
-E：备份事件
--triggers：备份触发器
--set-gtid-purged=OFF：：使得gtid从头开始记录
--master-data=2：记录二进制日志的名称和位置号，与--single-transaction连用，只对innodb引擎不进行锁表操作
--single-transaction：innodb存储引擎开启热备功能
1
2
3
4
5
6
7
8
9

模拟周二23:00到周三10:00之间的数据变化

mysql [backup]>insert into t1 values(11),(22),(33);
mysql [backup]>commit;
mysql [backup]>create table t2 (id int);
mysql [backup]>insert into t2 values(11),(22),(33);```
1
2
3
4

模拟故障：删库
```
mysql [backup]>drop database backup;
1
```

恢复过程

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

[root@localhost ~]# systemctl start mysqld3307.service
[root@localhost ~]# netstat -lntup|grep 3307
tcp6       0      0 :::3307                 :::*                    LISTEN      2187/mysqld 
1
2
3

准备备份

准备全备数据

[root@localhost ~]# cd /data/backup/
[root@localhost backup]# ls
full_2021-10-02.sql.gz
[root@localhost backup]# gunzip full_2021-10-02.sql.gz 
[root@localhost backup]# ls
full_2021-10-02.sql
1
2
3
4
5
6

截取二进制日志

# 查看全备时间点后的二进制日志position起点
[root@localhost backup]# vim full_2021-10-02.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=752;
# 查看drop语句的二进制日志position起点
[root@localhost backup]# mysql -e "show binlog events in 'mysql-bin.000007'" | grep -i "drop"mysql-bin.000007	1517	Query	6	1615	drop database backup
# 根据起点和终点截取二进制日志
[root@localhost backup]# mysqlbinlog --skip-gtids --start-position=752 --stop-position=1517 /data/binlog/mysql-bin.000007 > /data/backup/bin.sql
1
2
3
4
5
6
7

恢复备份到临时库

[root@localhost ~]# mysql -S /data/3307/mysql.sock

mysql [test]>set sql_log_bin=0;
mysql [test]>source /data/backup/full_2021-10-02.sql
mysql [test]>source /data/backup/bin.sql
1
2
3
4
5

将故障库导出并恢复生产

[root@localhost ~]# mysqldump -S /data/3307/mysql.sock -B backup > /data/backup/backup.sql

mysql [(none)]>source /data/backup/backup.sql
1
2
3

xtrabackup

XBK安装

下载

[root@localhost ~]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm
1

安装依赖包

[root@localhost ~]# yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev
1

安装XBK

[root@localhost ~]# yum -y install percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm
1

备份命令介绍

innobackupex 支持全量备份和增量备份

对于非Innodb表（比如 MyISAM），锁表，cp数据文件，属于一种温备份。
对于Innodb的表（支持事务），不锁表，cp数据页，最终以数据文件的方式保存下来，把一部分redo和undo一并备走，属于热备方式。

xtrabackup 在innodb表备份恢复的流程：

xtrabackupex备份命令执行的瞬间，立即触发ckpt（checkpoint），将已提交的数据脏页从内存刷写到磁盘，并记录此时的LSN号；
备份时，拷贝磁盘数据页，并且记录备份过程中产生的redo和undo一起拷贝走，也就是checkpoint LSN之后的日志；
在恢复之前，模拟Innodb自动故障恢复的过程，将redo（前滚）与undo（回滚）进行应用；
恢复过程就是 cp 备份到原来数据目录下。

innobackupex全量备份

全量备份

[root@localhost ~]# innobackupex --user=root  /data/backup>/tmp/xbk.log
1

innobackupex默认生成时间名称为备份文件，可以通过–no-timestamp自主定制备份路径名称。

[root@localhost ~]# ll /data/backup/
total 776
drwxr-x---. 7 root root    249 Oct  2 14:21 2021-10-02_14-21-47
[root@localhost 2021-10-02_14-21-47]# ll /data/backup/2021-10-02_14-21-47/
total 12340
drwxr-x---. 2 root root       76 Oct  2 14:21 backup
-rw-r-----. 1 root root      425 Oct  2 14:21 backup-my.cnf
-rw-r-----. 1 root root      638 Oct  2 14:21 ib_buffer_pool
-rw-r-----. 1 root root 12582912 Oct  2 14:21 ibdata1
drwxr-x---. 2 root root     4096 Oct  2 14:21 mysql
drwxr-x---. 2 root root     8192 Oct  2 14:21 performance_schema
drwxr-x---. 2 root root     8192 Oct  2 14:21 sys
drwxr-x---. 2 root root       54 Oct  2 14:21 test
-rw-r-----. 1 root root       22 Oct  2 14:21 xtrabackup_binlog_info
-rw-r-----. 1 root root      113 Oct  2 14:21 xtrabackup_checkpoints
-rw-r-----. 1 root root      454 Oct  2 14:21 xtrabackup_info
-rw-r-----. 1 root root     2560 Oct  2 14:21 xtrabackup_logfile
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

xtrabackup_binlog_info：备份时刻的二进制日志的文件名称、结束时刻的position号和gtid号（如果有的话）
```
[root@localhost 2021-10-02_14-21-47]# cat xtrabackup_binlog_info 
mysql-bin.000007	3977
1
2
```

xtrabackup_checkpoints

[root@localhost 2021-10-02_14-21-47]# cat xtrabackup_checkpoints 
backup_type = full-backuped     --备份类型
from_lsn = 0                    --上次到达的LSN号（对于全备就是从0开始，对于增量使用其他显示方法）
to_lsn = 3926797                --备份开始时间点（ckpt）数据页的LSN
last_lsn = 3926806              --备份结束后，redo日志最终的LSN
                                  to_lsn → last_lsn 就是备份过程中产生的数据变化（如果为9个表示没有变化）
compact = 0
recover_binlog_info = 0
1.备份时刻，立即将已经commit过的，内存中的数据页刷新到磁盘(CKPT)，开始备份数据，数据文件的LSN会停留在to_lsn位置。
2.备份时刻有可能会有其他的数据写入，已备走的数据文件就不会再发生变化。
3.在备份过程中，备份软件会一直监控着redo的undo，如果一旦有变化会将日志也一并备走，并记录LSN到last_lsn。																		
1
2
3
4
5
6
7
8
9
10
11

xtrabackup_info ：总体备份信息

xtrabackup_logfile：备份过程中产生的新的变化

[root@localhost 2021-10-02_14-21-47]# file xtrabackup_logfile 
xtrabackup_logfile: data
1
2

全备恢复

模拟故障

kill数据库服务
```
[root@localhost ~]# pkill mysqld
1
```

删除数据目录

[root@localhost ~]# rm -rf /data/mysql/
1

准备备份

redo进行重做，已提交的写到数据文件，未提交的使用undo回滚（模拟CSR过程）
```
[root@localhost ~]# innobackupex --apply-log /data/backup/2021-10-02_14-21-47
1
```

恢复备份

恢复备份前提：

被恢复的目录为空
被恢复的数据库实例处于关闭状态

恢复数据到原目录

恢复备份

[root@localhost mysql]# innobackupex --copy-back /data/backup/2021-10-02_14-21-47/
1

授权数据

[root@localhost ~]# chown -R mysql.mysql /data/mysql/
1

启动数据库

[root@localhost mysql]# systemctl start mysqld
1

恢复数据到新目录

创建新目录

[root@localhost ~]# mkdir /data/mysql1
1

恢复备份

[root@localhost ~]# cp -a /backup/full/* /data/mysql1/
1

授权数据

[root@localhost ~]# chown -R mysql.mysql /data/mysql1/
1

启动数据库

[root@localhost mysql]# systemctl start mysqld
1

innobackupex增量备份

增量备份的方式，是基于上一次备份进行增量
增量备份无法单独恢复。必须基于全备进行恢复
所有增量必须要按顺序合并到全备中

增量备份

周天全量备份

[root@localhost mysql]# innobackupex --user=root --no-timestamp /backup/full &>/tmp/xbk_full.log
1

模拟周一数据变化

mysql [(none)]>create database cs charset utf8;
mysql [(none)]>use cs
mysql [cs]>create table t1 (id int);
mysql [cs]>insert into t1 values(1),(2),(3);
mysql [cs]>commit;
1
2
3
4
5

第一次增量备份（周一）

[root@localhost mysql]# innobackupex --user=root --no-timestamp --incremental /data/backup/monday --incremental-basedir /backup/full &>/tmp/mon.log
--incremental：增量备份目录
--incremental-basedir：基于的全量备份目录
1
2
3

检查增量备份日志确定备份是否成功

[root@localhost ~]# tail -5 /tmp/mon.log 
211002 15:06:17 [00]        ...done
211002 15:06:17 [00] Writing xtrabackup_info
211002 15:06:17 [00]        ...done
xtrabackup: Transaction log of lsn (3933022) to (3933031) was copied.
211002 15:06:17 completed OK!
1
2
3
4
5
6

检查全量和增量的xtrabackup_checkpoints
在这里插入图片描述

模拟周二数据变化

mysql [cs]>create table t2 (id int);
mysql [cs]>insert into t2 values(1),(2),(3);
mysql [cs]>commit;
1
2
3

第二次增量备份（周二）
```
[root@localhost mysql]# innobackupex --user=root --no-timestamp --incremental /data/backup/tuesday --incremental-basedir /data/backup/monday &>/tmp/tues.log
1
```
注意：此时的–incremental-basedir 指向的是上一次增量备份；
检查增量备份日志备份是否成功；
检查全量和增量的xtrabackup_checkpoints。

模拟周三数据变化

mysql [cs]>create table t3 (id int);
mysql [cs]>insert into t3 values(1),(2),(3);
mysql [cs]>commit;
mysql [cs]>drop database cs;
1
2
3
4

增量恢复

恢复思路：

挂出维护页，停止当天的自动备份脚本
检查备份：周日full+周一incremental+周二incremental+周三完整的binlog日志
备份整理，截取关键的二进制日志（从备份到误删除之前）
测试库进行备份恢复以及日志恢复
应用进行测试无误，开启业务
工作总结

注意：

增量不能单独恢复，增量必须按照顺序合并到全备（LSN号码）
所有备份都必须要apply-log进行整理备份
部分备份只需要redo而不需要undo，防止LSN号接不上（–redo-only）

开始恢复：

没合并之前全备整理（–redo-only）

[root@localhost ~]# innobackupex --apply-log --redo-only /backup/full/
1

合并并整理monday的增量备份到full

[root@localhost ~]# innobackupex --apply-log --redo-only --incremental-dir=/data/backup/monday /backup/full

--incremental-dir：要合并的增量备份目录
1
2
3

检查是否合并成功（xtrabackup_checkpoints的last_lsn的号是否一致）
在这里插入图片描述

合并整理tuesday的增量备份到full

[root@localhost ~]# innobackupex --apply-log --incremental-dir=/data/backup/tuesday /backup/full    --最后一次增量备份，不需要使用--redo-only参数
1

全备的终极整理

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

截取周二增量备份后到周三 drop 之前的 binlog

# 查看position起点
[root@localhost ~]# cat /data/backup/tuesday/xtrabackup_binlog_info 
mysql-bin.000008	1156
# 查看position终点
[root@localhost ~]# mysql -e 'show binlog events in "mysql-bin.000008"' | grep -i "drop" 
mysql-bin.000008	1639	Query	6	1725	drop database cs
# 截取二进制日志
[root@localhost ~]# mysqlbinlog --skip-gtids --start-position=1156 --stop-position=1639 /data/binlog/mysql-bin.000008 > /data/backup/bin.sql
1
2
3
4
5
6
7
8

恢复数据

[root@localhost ~]# mkdir /data/mysql1 -p
[root@db01 full]# cp -a * /data/mysql1/
[root@db01 backup]# chown -R mysql.mysql  /data/*
[root@db01 backup]# systemctl start mysqld3307.service
mysql [(none)]>set sql_log_bin=0;
mysql [(none)]>source /data/backup/binlog.sql
1
2
3
4
5
6

运维在备份恢复的职责

设计备份策略：全量备份、增量备份、备份时间、自动备份
日常备份检查：备份数据存在性、备份空间剩余（眼见为实，自动化未必一定正确）
定期恢复演练：测试库演练，一个月/一季度/半年
故障恢复：通过现有备份，将数据库恢复到故障之前的时间点
数据迁移：尽可能缩短停机时间；要有回退方案
了解掌握：备份策略、备份工具及参数、备份时间、数据量级、热备实现方案等

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/小桥流水78/article/detail/989309