MySQL 8 核心日志与备份恢复详解

1.3 过期时间

查看系统变量：

SHOW VARIABLES LIKE '%binlog_expire_logs_seconds%';

结果示例：

+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| binlog_expire_logs_seconds | 2592000 |
+----------------------------+---------+

在配置文件 my.cnf 中配置日志文件的过期时间，默认为 2592000 秒 (30 天)，过期后会自动删除。

# MySQL 服务器配置
[mysqld]
# 二进制日志过期时间
binlog_expire_logs_seconds=2592000

1.4 日志格式

• statement： 记录的是 SQL 语句本身，即实际执行的 SQL 语句。
  • 优点： 仅记录 SQL 语句，占用空间少。
  • 缺点： 某些情况下可能导致主从数据不一致，例如使用非确定性函数 (如 NOW() 或 RAND()) 时，主从服务器执行结果可能不同。
• row： 记录的是每一行数据的变更情况，即具体哪些行被修改以及修改后的值。
  • 优点： 数据一致性高，避免了非确定性函数的问题，适用于复杂的复制场景。
  • 缺点： 日志文件较大，尤其是批量操作时，会记录大量行变更信息。
• mixed： 结合了 statement 和 row 格式的优点。默认情况下使用 statement 格式记录，但在某些可能导致不一致的场景下自动切换为 row 格式。
  • 优点： 平衡了日志大小和数据一致性。
  • 缺点： 仍需注意某些特殊情况下可能存在的一致性问题。

推荐使用 row 格式。

1.5 刷盘策略

binlog 日志文件的刷盘策略可以通过 sync_binlog 系统变量来设置。

• sync_binlog=0： 由操作系统决定刷盘时机。性能最高，但宕机时可能丢失较多 binlog 数据。
• sync_binlog=1(推荐)： 每次事务提交时都会刷盘，确保 binlog 不丢失。安全性最高，但性能较低。
• sync_binlog=N(N>1)： 每 N 次事务提交后刷盘一次。平衡性能与安全性。

1.6 mysqlbinlog 工具

简介： mysqlbinlog 是 MySQL 自带的二进制日志解析工具，用于查看和管理 MySQL 的二进制日志文件 (binlog)。

主要功能：

• 查看二进制日志内容： 将二进制日志转换为可读的文本格式。
• 过滤日志事件： 按时间、位置或数据库名筛选特定日志记录。
• 生成 SQL 脚本： 将日志内容还原为 SQL 语句，用于数据恢复。
• 远程解析： 支持解析远程 MySQL 服务器的二进制日志。

position：每条日志都以 # at 开始，后面的数字表示该条日志记录的事件在文件中的偏移量。 timestamp：事件发生的时间戳。 server id：服务器标识。 end_log_pos：下一个事件在文件中的偏移量 (等于当前事件的结束偏移量 +1)。 type：事件类型 (Query)。 thread_id：执行事件的线程 id。 exec_time：执行事件花费的时间。 error_code：错误码 (0 表示没有错误)。

1.6.1 准备数据

-- 建库
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE testdb;

-- 建表
CREATE TABLE t1 (
  id BIGINT NOT NULL,
  name VARCHAR(20) NOT NULL
);

-- 写入
INSERT INTO t1 (id, name) VALUES (101,'user101');
INSERT INTO t1 (id, name) VALUES (102,'user102');
INSERT INTO t1 (id, name) VALUES (103,'user103');
INSERT INTO t1 (id, name) VALUES (104,'user104');
INSERT INTO t1 (id, name) VALUES (105,'user105');
INSERT INTO t1 (id, name) VALUES (106,'user106');

-- 更新
UPDATE t1 SET name = 'person101' WHERE id = 101;
UPDATE t1 SET name = 'person102' WHERE id = 102;
UPDATE t1 SET name = 'person103' WHERE id = 103;

-- 删除
DELETE FROM t1 WHERE id = 104;
DELETE FROM t1 WHERE id = 105;
DELETE FROM t1 WHERE id = 106;

1.6.2 查看 binlog

mysqlbinlog --no-defaults --database=testdb --base64-output=decode-rows -vv --start-position=421 --stop-position=4626 binlog.000001

• no-defaults：禁止读取默认配置文件 (如 my.cnf)，确保命令执行不受配置文件参数干扰。
• database：限定只显示与指定数据库相关的日志记录，过滤其他库的操作。
• base64-output=decode-rows：对二进制日志中的行事件 (row 格式日志) 进行 Base64 解码，否则这类事件会以 Base64 编码形式显示。
• vv：双倍详细模式 (verbose level 2)，输出最完整的日志信息。
• start-position：从二进制日志文件的指定位置开始解析。
• stop-position：在二进制日志文件的指定位置停止解析。

1.6.3 数据恢复

通过日志文件恢复数据：

mysqlbinlog --no-defaults --skip-gtids=true --start-position=234 --stop-position=4470 binlog.000001 | mysql -uroot -p -h127.0.0.1 -P3306

删除数据库：

DROP DATABASE testdb;

2. 数据备份与恢复

2.1 分类

2.1.1 按数据存储形式划分

• 备份文件中包含 SQL 语句，稍加修改就可以在不同数据库系统上执行。
• 可以备份整个数据库或特定的数据库对象 (使用 DATABASE 关键字创建的对象)。
• 备份和恢复的速度比物理备份慢。
• 通常不能跨数据库厂商进行备份与恢复。
• 可以非常快速地备份和恢复大型数据库，因为不需要解析 SQL 语句，直接复制文件即可。

物理备份： 直接复制数据库的物理文件 (如数据文件、日志文件)。这种备份方法不涉及数据库的逻辑结构，而是直接在文件系统层面上复制数据库的存储结构 (相当于 Windows 系统的 Ctrl + C)。

逻辑备份： 备份数据库的逻辑结构和数据内容 (如 SQL 语句、导出文件)，与物理存储无关。例如 MySQL 的 mysqldump。

2.1.2 按数据库运行状态划分

• 冷备份 (离线备份)： 需停止数据库服务后备份，备份过程中数据库服务不可用。
  • 影响业务连续性。
  • 技术实现较简单，不需要考虑数据一致性和并发控制。
  • 不会对数据库性能产生影响。
• 热备份 (在线备份)： 在数据库运行时进行备份，备份过程中数据库服务依然可用。
  • 备份数据是系统当前最新的。
  • 不影响业务连续性。
  • 在高负载情况下，可能对数据库性能产生一定影响。
  • 技术实现复杂，需要考虑数据一致性和并发控制。
• 温备份： 介于冷备份和热备份之间的一种备份方式，数据库在备份过程中部分可用或者处于只读模式。
  • 业务中断时间较少。

2.1.3 按备份数据范围划分

差异备份： 仅备份自上次全量备份以来所有变化的数据。

增量备份： 仅备份自上次备份后变化的数据块。

全量备份： 备份整个数据库或文件系统的所有数据，包括所有的文件、数据库表和配置文件等。不依赖于其他备份，可用独立恢复数据。

2.2 mysqldump

2.2.1 工具介绍

作用： 它是 MySQL 数据库系统提供的命令行工具，用于逻辑备份和数据导出。它生成包含 SQL 语句的文本文件，可用于重建数据库结构和数据。

核心功能：

• 备份数据库： 导出表结构、数据、存储过程、触发器等。
• 恢复数据： 通过导入 SQL 文件恢复数据库状态。
• 跨版本兼容： 导出的 SQL 文件可在不同 MySQL 版本间迁移。

2.2.2 示例

• 导入数据

查看数据：

USE testdb;
SELECT * FROM t1;

登录 MySQL 客户端导入 SQL 文件：

SOURCE /backup/mysql/dump.sql;

在命令行通过 MySQL 客户端工具直接恢复：

mysql -uroot -p < /backup/mysql/dump.sql

删除数据库：

DROP DATABASE testdb;

查看导出的 SQL 文件：

cat /backup/mysql/dump.sql

导出：

mkdir /backup/mysql
mysqldump -uroot -p -h127.0.0.1 -P3306 -B testdb > /backup/mysql/dump.sql

2.2.3 数据一致性问题

• mysqldump 备份数据时的执行流程如下：
  • 连接数据库。
  • 收集需要备份的数据。
  • 对所有待备份表加读锁。
  • 生成数据插入语句。
  • 释放锁。
• 备份过程中可能会产生数据一致性问题：
  • mysqldump 在备份时默认情况下不会回滚未提交的事务，因此备份可能包含未提交的事务中的数据更改。
  • 如果在加读锁后发生事务回滚，备份结果可能包含部分事务的中间状态。

解决办法： 使用 --single-transaction，在事务中备份，使用 MVCC 获取一致性视图。

2.3 mysqlimport

2.3.1 工具介绍

作用： 它是 MySQL 数据库系统提供的命令行工具，用于高效地将文本文件数据导入到数据库表中。它是 LOAD DATA INFILE 语句的封装，适用于批量数据加载场景。

核心功能：

• 快速导入： 直接读取文件并加载到表，跳过 SQL 解析环节，性能优于逐行 INSERT。

2.3.2 示例

• 查看数据

导入：

mysqlimport -uroot -p testdb /var/lib/mysql-files/t1.txt

SELECT * FROM t1;

删除数据表：

DELETE FROM t1;

查看导出的文本文件： 导出：

SELECT * FROM t1 INTO OUTFILE '/var/lib/mysql-files/t1.txt';

查看 MySQL 允许导出的授权目录：

SHOW VARIABLES LIKE 'secure_file_priv';

2.4 Xtrabackup

2.4.1 工具介绍

作用： Xtrabackup 是由 Percona 开发的一款开源 MySQL 数据库备份工具。它通过热备份实现高性能的数据库备份与恢复，适用于大规模生产环境。

核心功能：

• 热备份： 在不中断数据库服务的情况下进行备份。
• 压缩与加密： 支持备份文件的压缩和加密，提升安全性和存储效率。
• 快速可靠： 备份速度快且可靠，同时会对备份的数据进行自动校验，确保备份数据的完整性。
• 性能影响小： 在备份过程中，Xtrabackup 对数据库的性能影响较小，不会增加太多的性能压力。

官网： Xtrabackup 下载网址

版本选择：

• XtraBackup 2.4： 支持 MySQL 5.1、5.5、5.6 和 5.7，但不支持 MySQL 8.0。
• XtraBackup 8.0： 专为 MySQL 8.0 设计，但早期版本 (如 8.0.12) 不支持 MySQL 8.0.20 及以上版本。若使用 MySQL 8.0.20 及以上版本，建议选择 XtraBackup 8.0.27-19 或更高版本。

查看 CPU 架构

查看 MySQL 版本

查看 Linux 系统版本

安装软件源

• 把在 Windows 系统上下载完毕的 percona-xtrabackup-80_8.0.35-34-1.noble_amd64.deb 文件上传至 Linux 服务器。

验证是否安装成功：

安装软件源：

# 安装软件源
dpkg -i percona-xtrabackup-80_8.0.35-34-1.noble_amd64.deb
# 更新源
apt update
# 安装 xtrabackup
apt install percona-xtrabackup-80
# 如果提示缺少依赖运行以下命令安装
apt-get install -f
# 更新源
apt update
# 安装 xtrabackup
apt install percona-xtrabackup-80

2.4.2 示例

• 默认情况下密码策略要求密码包含大小写字母、数字和特殊字符。
• xtrabackup --prepare 命令用于将备份数据转换为可恢复的数据库状态。该步骤对物理备份文件执行事务日志回放和回滚未提交事务，确保数据文件的一致性。

登录数据库验证数据：

USE testdb;
SELECT * FROM t1;

重启 MySQL 服务：

systemctl restart mysql

为恢复后的数据目录授权：

chown -R mysql:mysql /var/lib/mysql

数据恢复：

# 准备
xtrabackup --prepare --target-dir=/backup/mysql/full
# 数据恢复
xtrabackup --defaults-file=/etc/mysql/my.cnf --copy-back --parallel=2 --target-dir=/backup/mysql/full

创建数据目录同名的空目录： 移动或删除原来的数据目录： 备份数据文件：

xtrabackup --defaults-file=/etc/mysql/my.cnf --host=localhost --port=3306 --user=backup_user --password=123456Aa@@ --use-memory=1G --parallel=2 --backup --target-dir=/backup/mysql/full

查看需要备份的目录： 创建备份用户：

CREATE USER 'backup_user'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456Aa@@';
GRANT backup_admin, process, select, reload, locktables, replication client, event ON *.* TO 'backup_user'@'localhost';

查看备份目录：