MySQL8数据备份恢复

一、分类及规划

1.1 介绍

数据是非常重要的资产，需要做好备份，以及恢复测试。

可以按照多个维度来进行分类：

1.2 分类

1.2.1 按照数据的备份范围（备份周期）

  • 全量备份，所有数据的备份
  • 增量备份，上一次备份操作，到本次备份时间点之间的数据操作，一般使用 binlog 来备份恢复
  • 差异备份，上一次全量备份，到本次备份时间点之间的数据操作

1.2.2 按照数据的实现方式（备份内容形态）

  • 逻辑备份，mysqldump 工具实现的SQL语句导出，可读性高，备份慢，跨系统迁移时可使用

  • 物理备份，直接拷贝数据库文件，二进制日志，备份快，占用更多存储空间

  • 远程备份，mysql8新加的特性，不稳定，clone plugin

1.2.3 按照该数据对业务的影响

  • 热备份，备份时，数据库读写操作都不受影响，innodb 存储引擎的数据库可以热备
  • 温备份，备份时，数据库写操作受影响，myisam 存储引擎的数据库需要锁表温备
  • 冷备份，备份时，数据库是停机状态

1.3 RTO和RPO

RTO，Recovery Time Objective 从故障开始，到业务恢复所需的时间

RPO，Recovery Point Objective 在故障后，丢失的数据的，时间长度

1.4 日常备份的管理

  • 确保硬盘空间充足
  • 确保自动化脚本正确运行，备份文件存在
  • 定期进行恢复演练

1.5 规划

主机名 ip 用途 版本
mysql101 192.168.10.101 mysqldump centos7 mysql8.0.44
mysql102 192.168.10.102 xtrabackup centos7 mysql8.0.44
mysql103 192.168.10.103 binlog恢复 centos7 mysql8.0.44

二、mysqldump 的备份恢复及原理

2.1 概述

mysqldump 是MySQL官方自带的备份工具，是逻辑备份，会导出成SQL语句。

备份命令

1
mysqldump -u用户名 -p'密码' -S sock文件路径 备份的内容 一系列参数 > 备份出的sql文件
1
2
3
4
5
6
7
8
9
10
11
mysqldump -u用户名 \
-p'密码' \
-S sock文件路径 \
-A \
-E \
-R \
--triggers \
--master-data=2 \
--single-transaction \
--set-gtid-purged=on \
> ./all_3306_`date +%Y%m%d`.sql
  • -A 备份所有的数据库
  • -B 备份指定的数据库
  • -E 备份事件
  • -R 备份存储过程和函数
  • --triggers 备份触发器
  • --master-data=2 备份时记录position 号和 gtid 号
  • --single-transaction 触发一致性备份
  • --set-gtid-purged=on 主从环境下，需要使用此参数

2.2 mysqldump 备份原理

之前对mysqldump 有误解，以为整个过程都存在写锁，实际学习后发现只有很短的时间里有写锁。

mysqldump 的本质是执行一系列的SQL语句：

  • 关闭所有的表 FLUSH TABLES;
  • 设置全局读锁，写操作会被阻塞。 FLUSH TABLES WITH READ LOCK
  • 设置会话的事务隔离级别为RR SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
  • 开启一个事务 START TRANSACTION
  • 获取当前正在使用的二进制日志文件，获取position 号 SHOW MASTER STATUS
  • 释放全局读锁 UNLOCK TABLES;
  • 查看建库语句 SHOW CREATE DATABASE 数据库名称
  • 设置回滚点 SAVEPOINT 回滚点名称sp
  • 查看建表语句 SHOW CREATE TABLES 表名
  • 读取表内容 SELECT * FROM 表名
  • 回到回滚点 ROLLBACK to
  • 继续读取下一张表，直到结束
  • 释放掉回滚点 RELEASE SAVEPOINT 回滚点sp

问题：假设凌晨三点做一次备份，四点才备份结束，备份的数据包括3点到4点之间的各种操作吗？

答： 不包括，只包括那个设置全局读锁FLUSH TABLES WITH READ LOCK 操作之前的数据

2.3 不同备份策略案例

2.3.1 备份所有的数据库

1
2
3
4
5
6
7
8
9
10
mysqldump -u用户名 \
-p'密码' \
-S sock文件路径 \
-A \
-E \
-R \
--triggers \
--master-data=2 \
--single-transaction \
> ./all_3306_`date +%Y%m%d`.sql
1
2
3
4
5
6
7
8
9
10
mysqldump -uroot \
-p'Root21..' \
-h 127.0.0.1 \
-A \
-E \
-R \
--triggers \
--master-data=2 \
--single-transaction \
> ./all_3306_`date +%Y%m%d`.sql

2.3.2备份指定的数据库

1
2
3
4
5
6
7
8
9
10
11
mysqldump -u用户名 \
-p'密码' \
-S sock文件路径 \
-B 数据库1 数据库2 \
-E \
-R \
--triggers \
--master-data=2 \
--single-transaction \
--set-gtid-purged=on \
> ./mysql_d1_3306_`date +%Y%m%d`.sql

2.3.3 备份指定的表

1
2
3
4
5
6
7
8
9
10
mysqldump -u用户名 \
-p'密码' \
-S sock文件 \
数据库名 表名1 表名2 \
-E \
-R \
--triggers \
--master-data=2 \
--single-transaction \
> ./数据库_表名_3306_`date +%Y%m%d`.sql

2.4 恢复

  1. 登录
1
mysql -uroot -p'Root21..'
  1. 查看和关闭 binlog 的读写
1
show variables like '%sql_log_bin%';
1
set session sql_log_bin=0;
  1. 登录后导入数据
1
mysql -uroot -p'Root21..'
1
source ./all_3306_xxxxxx.sql

2.5 SQL案例

1
mysql -uroot -p'Root21..'
1
2
3
4
5
6
7
8
-- 登录 MySQL
create database gtid_test;
use gtid_test;
create table t1 (id int primary key, name varchar(20));

-- 插入几条“老数据”
insert into t1 values (1, 'old_data_1');
insert into t1 values (2, 'old_data_2');
1
2
3
4
5
6
7
8
-- 回到 MySQL，写入“新数据”
use gtid_test;
insert into t1 values (3, 'new_data_3');
insert into t1 values (4, 'new_data_4');

-- 搞点破坏，切一下日志，让 Binlog 变成新的文件（模拟真实环境）
flush logs; 
insert into t1 values (5, 'new_data_5');

查询

1
2
use gtid_test;
select * from gtid_test.t1;

三、xtrabackup 备份恢复

xtrabackup 有几个版本，2.4用来备份mysql5.7 及其他几个5.X 版本。对于innodb 存储引擎的数据库，可以热备，不锁表。不过官网已经推荐使用8.0 的数据库。

3.1 版本 8.0 的安装

官网安装教程

  1. 下载，Percona-XtraBackup-8.0.35-31 是最后支持centos7的版本。
    下载地址
1
wget https://downloads.percona.com/downloads/Percona-XtraBackup-8.0/Percona-XtraBackup-8.0.35-31/binary/redhat/7/x86_64/percona-xtrabackup-80-8.0.35-31.1.el7.x86_64.rpm
  1. 本地安装
1
yum localinstall percona-xtrabackup-80-8.0.35-31.1.el7.x86_64.rpm
  1. 验证
1
xtrabackup --version

3.2 全量备份实验

innobackupex 是很多教程的做法，不过这个命令已经被废弃，有些版本会软链接到了 xtrabackup 

1
2
3
[root@centos7 ~]# ll /usr/bin/innobackupex
lrwxrwxrwx. 1 root root 10 Oct 28 14:20 /usr/bin/innobackupex -> xtrabackup
[root@centos7 ~]#

常见参数

  • --backup
  • -S 指定socket 文件进行本地连接
  • --prepare 创建准备备份
  • --apply-log-only
  • --host 指定MySQL的host
  • --port 指定MySQL的port
  • --password 指定MySQL的密码
  • --defaults-file 指定 my.cnf，必须作为第一个参数
  • --datadir 指定MySQL的数据目录，没有的话会自动创建
  • --target-dir= 指定的备份文件夹，

3.2.1 环境准备

1
mysql -uroot -p'Root21..'
1
2
3
4
5
6
7
8
9
# 创建数据库
create database dbbackuptest;
use dbbackuptest
# 创建表
create table t1(id int primary key auto_increment, \
                name varchar(10), age int)engine=innodb;
# 插入数据
insert into t1(name,age) values('alice', 20), ('bob', 22), ('tom', 19);
select * from dbbackuptest.t1;
1
drop database dbbackuptest;

3.2.2 全量备份及恢复

备份

备份在指定目录/backup下

  1. 创建指定目录
1
mkdir /backup
  1. 使用网络登录，创建一个全量备份
1
2
3
4
5
6
7
xtrabackup \
--defaults-file=/etc/my.cnf \
--host 127.0.0.1 \
--user=root \
--password='Root21..' \
--backup \
--target-dir=/backup/fulltest_local/

恢复

删除dbbackuptest 库后，恢复。以本地备份的为例。

1
mysql -uroot -p'Root21..'
1
drop database dbbackuptest;
  1. 准备备份文件
1
xtrabackup --prepare --target-dir=/backup/fulltest_local/
  1. 清空数据目录
1
systemctl stop mysqld
1
rm -rf /var/lib/mysql
  1. 将备份的所有数据复制到，数据目录datadir 下，不指定配置文件的话，会恢复到/var/lib/mysql
1
xtrabackup --copy-back --target-dir=/backup/fulltest_local/

--defaults-file 参数必须要放到第一个位置

1
2
3
4
xtrabackup \
--defaults-file=/etc/my.cnf \
--copy-back \
--target-dir=/backup/fulltest_local/
  1. 修改文件夹权限，mysql:mysql
1
chown mysql:mysql /var/lib/mysql -R
  1. 启动后登录查看
1
systemctl start mysqld
1
mysql -uroot -p'Root21..'
1
select * from dbbackuptest.t1;

3.3 增量备份实验

先准备一些库和表。然后做一次全备，三次增量备份。最后依次恢复，需要依赖最后的 binlog

3.3.1 环境准备

1
mysql -uroot -p'Root21..'
  1. 准备一些库和表
1
2
3
4
5
6
7
8
create database bkTest;
use bkTest

create table t1(id int primary key auto_increment, name varchar(10), age int, score int)engine=innodb;

insert into t1(name, age, score) values('alice', 20, 58);
insert into t1(name, age, score) values('bob', 23, 78);
insert into t1(name, age, score) values('tony', 19, 66);
  1. 查询数据库中数据
1
select * from bkTest.t1;

3.3.2 全备

备份到这个目录下。/backup/full/

1
2
3
4
5
6
7
xtrabackup \
--defaults-file=/etc/my.cnf \
--host 127.0.0.1 \
--user=root \
--password='Root21..' \
--backup \
--target-dir=/backup/full/

备份后插入数据

1
insert into t1(name, age, score) values('time', 21, 82);
  1. 查询
1
select * from bkTest.t1;
1
2
3
4
5
6
7
8
9
10
11
12
mysql> select * from bkTest.t1;
+----+-------+------+-------+
| id | name  | age  | score |
+----+-------+------+-------+
|  1 | alice |   20 |    58 |
|  2 | bob   |   23 |    78 |
|  3 | tony  |   19 |    66 |
|  4 | time  |   21 |    82 |
+----+-------+------+-------+
4 rows in set (0.00 sec)

mysql>

3.3.3 基于全备备份【第一次增量】

1
2
3
4
5
6
7
8
xtrabackup \
--defaults-file=/etc/my.cnf \
--host 127.0.0.1 \
--user=root \
--password='Root21..' \
--backup \
--target-dir=/backup/inc1/ \
--incremental-basedir=/backup/full/

备份后插入数据

1
insert into t1(name, age, score) values('souk', 18, 77);
  1. 查询
1
select * from bkTest.t1;
1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> select * from bkTest.t1;
+----+-------+------+-------+
| id | name  | age  | score |
+----+-------+------+-------+
|  1 | alice |   20 |    58 |
|  2 | bob   |   23 |    78 |
|  3 | tony  |   19 |    66 |
|  4 | time  |   21 |    82 |
|  5 | souk  |   18 |    77 |
+----+-------+------+-------+
5 rows in set (0.00 sec)

mysql>

3.3.4 基于第一次增量备份【第二次增量】

1
2
3
4
5
6
7
8
xtrabackup \
--defaults-file=/etc/my.cnf \
--host 127.0.0.1 \
--user=root \
--password='Root21..' \
--backup \
--target-dir=/backup/inc2/ \
--incremental-basedir=/backup/inc1/

备份后插入数据

1
insert into t1(name, age, score) values('cinese', 22, 59);
  1. 查询
1
select * from bkTest.t1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> select * from bkTest.t1;
+----+--------+------+-------+
| id | name   | age  | score |
+----+--------+------+-------+
|  1 | alice  |   20 |    58 |
|  2 | bob    |   23 |    78 |
|  3 | tony   |   19 |    66 |
|  4 | time   |   21 |    82 |
|  5 | souk   |   18 |    77 |
|  6 | cinese |   22 |    59 |
+----+--------+------+-------+
6 rows in set (0.00 sec)

mysql>

3.3.5 基于第二次增量备份【第三次增量】

1
2
3
4
5
6
7
8
xtrabackup \
--defaults-file=/etc/my.cnf \
--host 127.0.0.1 \
--user=root \
--password='Root21..' \
--backup \
--target-dir=/backup/inc3/ \
--incremental-basedir=/backup/inc2/

备份后插入数据

1
insert into t1(name, age, score) values('denylice', 18, 88);
  1. 查询
1
select * from bkTest.t1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select * from bkTest.t1;
+----+----------+------+-------+
| id | name     | age  | score |
+----+----------+------+-------+
|  1 | alice    |   20 |    58 |
|  2 | bob      |   23 |    78 |
|  3 | tony     |   19 |    66 |
|  4 | time     |   21 |    82 |
|  5 | souk     |   18 |    77 |
|  6 | cinese   |   22 |    59 |
|  7 | denylice |   18 |    88 |
+----+----------+------+-------+
7 rows in set (0.00 sec)

mysql>

3.3.6 恢复

3.3.6.1 准备
  1. 准备全量备份
1
xtrabackup --prepare --apply-log-only --target-dir=/backup/full
  1. 将第一次增量应用到全量备份上
1
xtrabackup --prepare --apply-log-only --target-dir=/backup/full --incremental-dir=/backup/inc1
  1. 将第二次增量应用到全量备份上
1
xtrabackup --prepare --apply-log-only --target-dir=/backup/full --incremental-dir=/backup/inc2
  1. 将第三次增量应用到全量备份上
1
xtrabackup --prepare --apply-log-only --target-dir=/backup/full --incremental-dir=/backup/inc3
  1. 再次准备全量备份
1
xtrabackup --prepare --target-dir=/backup/full
3.3.6.2 恢复备份的数据
  1. 关闭数据库
1
systemctl stop mysqld
  1. 迁移 binlog
1
2
3
4
# 假设数据目录在 /var/lib/mysql
mkdir -p /tmp/binlog_rescue/
cp /var/lib/mysql/binlog.* /tmp/binlog_rescue/
# 哪怕是做错了，原来的还在
  1. 清空数据目录
1
rm -rf /var/lib/mysql
  1. 恢复数据，必须指定--defaults-file 参数，不指定的话，会恢复到 /var/lib/mysql 下
1
2
3
4
xtrabackup \
--defaults-file=/etc/my.cnf \
--copy-back \
--target-dir=/backup/full/
  1. 修改权限后启动
1
chown mysql.mysql /var/lib/mysql -R
1
systemctl start mysqld
  1. 登陆后验证
1
mysql -uroot -p'Root21..'
1
select * from bkTest.t1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> select * from bkTest.t1;
+----+--------+------+-------+
| id | name   | age  | score |
+----+--------+------+-------+
|  1 | alice  |   20 |    58 |
|  2 | bob    |   23 |    78 |
|  3 | tony   |   19 |    66 |
|  4 | time   |   21 |    82 |
|  5 | souk   |   18 |    77 |
|  6 | cinese |   22 |    59 |
+----+--------+------+-------+
6 rows in set (0.00 sec)

mysql>
3.3.6.3 利用binlog 恢复所有的日志

最终恢复了六条数据。最后一次增量备份后的数据，需要使用binlog 日志恢复。

查看最后一次增量备份的二进制日志信息

1
cat /backup/inc3/xtrabackup_binlog_info
1
2
3
[root@centos7 logs]# cat /backup/inc3/xtrabackup_binlog_info
binlog.000014	157
[root@centos7 logs]#

截取157 之后的日志

1
mysqlbinlog --base64-output=decode-rows -vv binlog.000014
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
........

# at 157
#260105 14:45:37 server id 1  end_log_pos 236 CRC32 0x91d6d745 	Anonymous_GTID	last_committed=0	sequence_number=1	rbr_only=yes	original_committed_timestamp=1767595537550993	immediate_commit_timestamp=1767595537550993	transaction_length=304
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1767595537550993 (2026-01-05 14:45:37.550993 CST)
# immediate_commit_timestamp=1767595537550993 (2026-01-05 14:45:37.550993 CST)
/*!80001 SET @@session.original_commit_timestamp=1767595537550993*//*!*/;
/*!80014 SET @@session.original_server_version=80044*//*!*/;
/*!80014 SET @@session.immediate_server_version=80044*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 236
#260105 14:45:37 server id 1  end_log_pos 313 CRC32 0xca1d2e3c 	Query	thread_id=16	exec_time=0	error_code=0
SET TIMESTAMP=1767595537/*!*/;
SET @@session.pseudo_thread_id=16/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
BEGIN
/*!*/;
# at 313

........
1
mysqlbinlog --start-position=157 /tmp/binlog_rescue/binlog.000014 > incc.sql
  1. 登录
1
mysql -uroot -p'Root21..'
  1. 查看和关闭 binlog 的读写
1
show variables like '%sql_log_bin%';
1
set session sql_log_bin=0;
1
source ./incc.sql

验证，成功恢复数据

1
select * from bkTest.t1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select * from bkTest.t1;
+----+----------+------+-------+
| id | name     | age  | score |
+----+----------+------+-------+
|  1 | alice    |   20 |    58 |
|  2 | bob      |   23 |    78 |
|  3 | tony     |   19 |    66 |
|  4 | time     |   21 |    82 |
|  5 | souk     |   18 |    77 |
|  6 | cinese   |   22 |    59 |
|  7 | denylice |   18 |    88 |
+----+----------+------+-------+
7 rows in set (0.00 sec)

mysql>
3.3.6.4 总结

还是拼写，好多时候会搞混参数，拼写错误等等。要注意。