1. 二进制日志
1.1 概述
作用: 二进制日志 (Binary Log) 以二进制格式存储,记录所有修改数据库数据的 SQL 语句 (如 INSERT、UPDATE、DELETE) 或事件 (如表结构变更)。
本文详细介绍了 MySQL 8 的核心日志机制与备份恢复方案。内容包括二进制日志的作用、配置、文件格式、过期策略及三种日志格式对比;讲解了 mysqlbinlog 工具的使用方法及数据恢复流程。此外,文章还阐述了数据备份的分类(物理/逻辑、冷热备、全量/增量),并分别演示了 mysqldump、mysqlimport 及 Xtrabackup 工具的具体操作命令与注意事项,重点强调了数据一致性的处理与权限配置。

作用: 二进制日志 (Binary Log) 以二进制格式存储,记录所有修改数据库数据的 SQL 语句 (如 INSERT、UPDATE、DELETE) 或事件 (如表结构变更)。
核心功能:
配置:
会话级配置: 在命令行客户端中设置变量 sql_log_bin,仅本次连接生效。
-- 1 -> 开启 -- 0 -> 关闭
SET SESSION sql_log_bin = 1;
SHOW VARIABLES LIKE '%sql_log_bin%';
结果示例:
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | ON |
+---------------+-------+
系统级配置: 使用 Vim 编辑器编辑 MySQL 的配置文件 (vim /etc/mysql/my.cnf),永久生效。

RESET MASTER 重置日志文件和索引文件为初始状态。

max_binlog_size 设置的上线 (默认值 1GB)。


查看系统变量:
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
推荐使用 row 格式。
binlog 日志文件的刷盘策略可以通过 sync_binlog 系统变量来设置。
简介: mysqlbinlog 是 MySQL 自带的二进制日志解析工具,用于查看和管理 MySQL 的二进制日志文件 (binlog)。
主要功能:
position:每条日志都以 # at 开始,后面的数字表示该条日志记录的事件在文件中的偏移量。 timestamp:事件发生的时间戳。 server id:服务器标识。 end_log_pos:下一个事件在文件中的偏移量 (等于当前事件的结束偏移量 +1)。 type:事件类型 (Query)。 thread_id:执行事件的线程 id。 exec_time:执行事件花费的时间。 error_code:错误码 (0 表示没有错误)。
-- 建库
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;
mysqlbinlog --no-defaults --database=testdb --base64-output=decode-rows -vv --start-position=421 --stop-position=4626 binlog.000001
通过日志文件恢复数据:
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;
DATABASE 关键字创建的对象)。物理备份: 直接复制数据库的物理文件 (如数据文件、日志文件)。这种备份方法不涉及数据库的逻辑结构,而是直接在文件系统层面上复制数据库的存储结构 (相当于 Windows 系统的 Ctrl + C)。

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

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

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

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

作用: 它是 MySQL 数据库系统提供的命令行工具,用于逻辑备份和数据导出。它生成包含 SQL 语句的文本文件,可用于重建数据库结构和数据。
核心功能:
查看数据:
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
解决办法: 使用 --single-transaction,在事务中备份,使用 MVCC 获取一致性视图。
作用: 它是 MySQL 数据库系统提供的命令行工具,用于高效地将文本文件数据导入到数据库表中。它是 LOAD DATA INFILE 语句的封装,适用于批量数据加载场景。
核心功能:
导入:
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';
作用: Xtrabackup 是由 Percona 开发的一款开源 MySQL 数据库备份工具。它通过热备份实现高性能的数据库备份与恢复,适用于大规模生产环境。
核心功能:
官网: 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 系统版本

安装软件源
验证是否安装成功:

安装软件源:
# 安装软件源
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
登录数据库验证数据:
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';
查看备份目录:


微信公众号「极客日志」,在微信中扫描左侧二维码关注。展示文案:极客日志 zeeklog
在线格式化和美化您的 SQL 查询(它支持各种 SQL 方言)。 在线工具,SQL 美化和格式化在线工具,online
解析 INSERT 等受限 SQL,导出为 CSV、JSON、XML、YAML、HTML 表格(见页内语法说明)。 在线工具,SQL 转 CSV/JSON/XML在线工具,online
CSV 与 JSON/XML/HTML/TSV/SQL 等互转,单页多 Tab。 在线工具,CSV 工具包在线工具,online
将字符串编码和解码为其 Base64 格式表示形式即可。 在线工具,Base64 字符串编码/解码在线工具,online
将字符串、文件或图像转换为其 Base64 表示形式。 在线工具,Base64 文件转换器在线工具,online
将 Markdown(GFM)转为 HTML 片段,浏览器内 marked 解析;与 HTML 转 Markdown 互为补充。 在线工具,Markdown 转 HTML在线工具,online