Mysql-Day6

1. Binlog是什么？有哪几种格式？推荐使用哪种，为什么

Binlog（Binary Log）是数据库（如 MySQL）中用于记录所有对数据库内容进行更改的操作日志。它以二进制格式存储，主要作用包括：

• 数据恢复：在数据库发生故障时，可以通过 Binlog 恢复丢失的数据。

• 主从复制：MySQL 主库通过 Binlog 向从库传输数据变更，实现主从同步。

• 审计和调试：可用于分析数据库的修改历史，便于排查问题或进行安全审计。

Binlog 的三种格式

1. STATEMENT 基于语句的格式

   • 记录 SQL 语句本身，而不是实际的数据变化。

   • 优点：

     • 日志量小，节省磁盘空间。

   • 缺点：

     • 部分非确定性语句（如 NOW()、UUID()）可能导致主从不一致。

     • 不支持基于行级别的审计。

   • 适用场景：

     • 对日志大小敏感且操作都是确定性语句的系统。

2. ROW 基于行的格式

   • 记录每一行数据的实际变化（例如某列的值从 A 变成 B）。

   • 优点：

     • 精确记录数据变化，保证主从一致性。

     • 支持所有 SQL 操作，包括非确定性函数。

   • 缺点：

     • 日志体积大，尤其在批量更新时。

   • 适用场景：

     • 要求高可靠性和精确复制的系统。

3. MIXED 混合模式

   • 默认使用 STATEMENT 格式，但在某些情况下自动切换为 ROW 格式（例如遇到不确定语句时）。

   • 优点：

     • 在性能与可靠性之间取得平衡。

   • 缺点：

     • 实现机制较复杂，日志结构不如纯 ROW 或纯 STATEMENT 明确。

   • 适用场景：

     • 兼顾性能和准确性的通用系统。

推荐使用哪种格式？

推荐使用 ROW 模式，原因如下：

1. 确保主从一致性

   • ROW 格式直接记录每行数据的变化，避免了因执行 SQL 语句导致的不确定性问题（比如触发器、自增字段、随机函数等），从而保障主从复制的准确性。

2. 支持更细粒度的数据审计

   • 如果需要追踪某个具体字段的变化情况，ROW 格式可以直接提供这些信息，而 STATEMENT 格式只能看到 SQL 语句，无法还原具体的行级变化。

3. 适用于现代应用场景

   • 在金融、电商、日志审计等对数据一致性要求较高的场景中，ROW 模式能更好地满足业务需求。

4. 兼容 GTID 和并行复制

   • MySQL 的 GTID（全局事务标识符）和并行复制功能对 ROW 模式的兼容性更好，有助于提升复制效率和容错能力。

注意：由于 ROW 模式会产生较大的日志文件，建议结合日志清理策略（如定期删除旧日志）和磁盘监控机制来管理存储资源。

2. 对数据库的操作是先执行，还是先写入binlog中？为什么

数据库操作的执行顺序是 先写入 binlog（Binary Log），然后再执行。这种顺序是为了保证数据库的 持久性和一致性，特别是在主从复制和数据恢复等场景中非常重要。

为什么先写入 binlog 再执行？

1. 确保事务的持久性

• 在 MySQL 中，binlog 是事务日志的一部分（虽然不是 redo log，但与事务持久性密切相关）。

• 如果操作先执行而没有记录到 binlog，就可能出现以下问题：

  • 数据库崩溃后无法通过 binlog 恢复该操作；

  • 主从复制时，从库可能丢失该操作，导致主从不一致。

2. 支持主从复制的一致性

• 主库在执行 SQL 或更改操作前，会将这些变更记录到 binlog。

• 从库通过读取主库的 binlog 来重放（replay）这些操作，从而实现数据同步。

• 如果先执行再写入 binlog，可能会出现：

  • 主库执行成功但未写入 binlog；

  • 从库无法同步这个操作，导致数据不一致。

3. 与 InnoDB 的 redo log 协调工作

• InnoDB 使用 redo log 来确保事务的原子性和持久性；

• 而 binlog 是用于逻辑层面的日志，用于复制和恢复；

• 两者的写入顺序由 MySQL 的 两阶段提交机制（Two-Phase Commit, 2PC） 控制：

  • Prepare 阶段：写入 redo log，并标记为 prepare 状态；

  • Commit 阶段：写入 binlog，并标记为 commit 状态；

• 这样可以确保即使在崩溃恢复时，也能根据 binlog 和 redo log 保持一致性。

3. 如果刚写入binlog，数据库出现异常，没有写入就重启了，会发生什么事情

如果 刚写入 binlog，但数据库操作尚未执行就发生异常并重启，MySQL 会根据 崩溃恢复机制 和 事务日志（redo log） 来判断如何处理这个未完成的操作。

情况分析

正常流程中 binlog 与 redo log 的协作：

1. 开始事务；

2. 写入 binlog（逻辑日志）；

3. 执行 SQL 操作，修改数据页，并写入 redo log（物理日志）；

4. 提交事务，标记为 commit 状态；

5. 数据最终落盘（由后台线程异步完成）。

如果在步骤 2 完成后、步骤 3 之前发生崩溃（即：binlog 已写入但未执行实际操作）：

此时会出现以下情况：

• binlog 中记录了该事务；

• InnoDB 的 redo log 没有记录该事务的变更；

• 数据库重启后，InnoDB 引擎会进行崩溃恢复；

• 在恢复过程中，MySQL 会检查 binlog 和 redo log 的一致性；

• 因为 redo log 中没有该事务的信息，MySQL 会认为该事务 未提交成功；

• 因此，在崩溃恢复时，该事务会被 回滚 或 忽略。

4. 随着时间的推移，binglog越来越大怎么操作

随着数据库的运行，binlog（二进制日志）会不断增长，尤其是在高并发写入、频繁更新的场景下。如果不进行管理，可能会导致：

• 磁盘空间被占满；

• 主从同步延迟增加；

• 数据恢复和备份耗时变长；

• 影响数据库性能。

解决方案

1. 设置 binlog 过期时间自动清理

MySQL 支持自动删除过期的 binlog 文件。

-- 设置 binlog 自动过期时间为 7 天
SET GLOBAL expire_logs_days = 7;

-- 永久生效：修改 my.cnf 或 mysqld.cnf 配置文件
[mysqld]
expire_logs_days=7

• 该配置只影响基于时间的清理，不考虑是否已被从库读取；

• 如果主从延迟较大，建议适当延长保留时间。

2. 手动清理指定范围的 binlog

如果你需要立即清理某些旧的 binlog 文件，可以使用以下命令：

-- 查看当前所有 binlog 文件
SHOW BINARY LOGS;

-- 清理指定文件之前的 binlog（不包括该文件）
PURGE BINARY LOGS TO 'mysql-bin.000010';

-- 清理指定时间点之前的 binlog
PURGE BINARY LOGS BEFORE '2025-07-17 00:00:00';

5. 如何强制创建新的binlog文件，这个操作有什么实际用途

在 MySQL 中，可以使用如下命令强制创建一个新的 binlog 文件：

FLUSH BINARY LOGS;

执行该命令后，MySQL 会立即关闭当前正在写入的 binlog 文件，并打开一个新的文件开始记录后续的日志。

6. 如何手动清理binlog（只保留当前使用的，删除其他的），自动清理需要如何配置。

一、手动清理 binlog（保留当前使用的，删除其他）

查看当前 binlog 状态：

SHOW BINARY LOGS;

输出示例：

+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 | 123456    |
| mysql-bin.000002 | 789012    |
| mysql-bin.000003 | 100       |
+------------------+-----------+

其中 mysql-bin.000003 是当前正在写入的 binlog 文件。

手动清理命令（删除除当前外的所有 binlog）：

PURGE BINARY LOGS TO 'mysql-bin.000003';

7. binlog 的生命周期和清理机制是什么？如何设置自动过期时间

一、binlog 生命周期

1. 创建阶段

• 每当一个事务提交时，如果启用了 binlog，MySQL 会将该事务的操作写入当前的 binlog 文件中。

• 当前正在写入的 binlog 文件称为“active”状态。

2. 使用阶段

• 主库：用于主从复制，从库通过读取主库的 binlog 来同步数据；

• 从库：用于 GTID 检查、故障恢复等；

• 数据恢复：DBA 可以使用 mysqlbinlog 工具回放 binlog 恢复数据。

3. 归档阶段

• 当执行 FLUSH BINARY LOGS; 或达到文件大小限制（由 max_binlog_size 控制），MySQL 会关闭当前 binlog 文件并创建新的文件。

• 此时旧的 binlog 文件进入“closed”状态，等待被清理或归档。

4. 清理阶段

• Binlog 文件可以手动删除（PURGE BINARY LOGS）或自动清理（基于时间或空间策略）；

• 如果配置了 expire_logs_days，MySQL 会在指定天数后自动删除过期的 binlog 文件。

自动清理配置（生产环境推荐）

修改 MySQL 配置文件（如 /etc/my.cnf 或 /etc/mysql/my.cnf）：

[mysqld]
# 设置 binlog 自动过期时间为 7 天
expire_logs_days = 7

# 单个 binlog 文件最大大小（默认 1GB）
max_binlog_size = 100M

• expire_logs_days=7：保留最近 7 天的日志；

• max_binlog_size=100M：每个 binlog 文件最大 100MB，超过后自动轮转；

• 这两个参数配合使用可有效控制磁盘占用。

重启 MySQL 生效（视环境而定）：

sudo systemctl restart mysql

8. 如何通过 mysqlbinlog 工具解析 binlog 内容？如何只解析某一个时间段的 binlog

mysqlbinlog 是 MySQL 提供的用于解析 binlog 文件内容的命令行工具。它能将二进制格式的日志转换为可读性强的 SQL 语句或事件信息，常用于：

• 数据恢复；

• 审计追踪；

• 主从复制问题排查。

一、基本使用方法

查看 binlog 文件内容（例如 mysql-bin.000001）：

mysqlbinlog mysql-bin.000001

注意：执行该命令前请确保你已经退出 MySQL 命令行（不要在 mysql> 中直接运行）。

二、只解析某一个时间段的 binlog 内容

你可以通过 --start-datetime 和 --stop-datetime 参数指定时间范围来过滤日志内容。

示例：解析 2025-07-23 14:00:00 到 2025-07-23 16:00:00 的 binlog

mysqlbinlog \
  --start-datetime="2025-07-23 14:00:00" \
  --stop-datetime="2025-07-23 16:00:00" \
  mysql-bin.000001 > filtered_binlog.log

参数说明：

9. 配置文件中确保开启了binlog，并且使用ROW格式。

在/etc/mysql/mysql.conf.d/mysqld.cnf中加入以下：

[mysqld]

# 启用 binlog，并指定文件名前缀
log-bin=mysql-bin

# 设置 binlog 格式为 ROW
binlog-format=ROW

# 可选：自动清理过期日志（保留 7 天）
expire_logs_days=7

# 可选：每个 binlog 文件最大大小（100MB）
max_binlog_size=100M

# 可选：同步写入磁盘，提高可靠性（适用于主库）
sync_binlog=1

# InnoDB 相关推荐配置
innodb_flush_log_at_trx_commit=1

随后重启

sudo systemctl restart mysql

10. 对数据库进行全量备份，使用之前创建的任意表格新增3条记录。

要对数据库进行全量备份，并使用之前创建的任意表格新增 3 条记录，可以按照以下步骤操作。

一、新增 3 条记录

假设之前创建了如下表：

CREATE DATABASE IF NOT EXISTS testdb;
USE testdb;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);

现在插入 3 条记录：

USE testdb;

INSERT INTO users (name, email) VALUES
('Alice', 'alice@example.com'),
('Bob', 'bob@example.com'),
('Charlie', 'charlie@example.com');

验证是否插入成功：

SELECT * FROM users;

二、执行数据库全量备份

使用 mysqldump 工具进行全量备份：

mysqldump -u root -p --single-transaction --routines --triggers --all-databases > full_backup_$(date +%F).sql

• --single-transaction：适用于 InnoDB，保证一致性快照；

• --routines：备份存储过程；

• --triggers：备份触发器；

• 如果只备份某个数据库，替换 --all-databases 为 --databases your_db_name；

11. drop整个表，之后使用binlog还原表，包括新增的3条记录。

-- 删除表

DROP TABLE IF EXISTS Users;

使用以下命令查看当前 binlog 状态：

SHOW MASTER STATUS;

备份：

mysqlbinlog \
  --base64-output=DECODE-ROWS -v \
  --start-datetime="2025-07-23 10:00:00" \
  /var/lib/mysql/mysql-bin.000002 > users_restore.sql

数据恢复

mysql -u root -p < binlog.sql

12. 主从同步中的binlog与relaylog有什么关系

在MySQL主从同步架构中，binlog（二进制日志）和relaylog（中继日志）是实现数据同步的关键组件，二者存在密切的依赖关系：

1. 定义与作用

- binlog ：由主服务器生成，记录所有修改数据库数据的操作（如INSERT、UPDATE、DELETE等），以二进制格式存储 2 。它主要用于数据复制和灾难恢复，是主从同步的基础数据源。

- relaylog ：仅存在于从服务器，用于暂存从主服务器复制过来的binlog事件 2 。从服务器通过I/O线程获取主服务器的binlog，并将其写入本地relaylog，再由SQL线程读取relaylog并应用到从库 3 。

2. 数据流向关系

主服务器的binlog是relaylog的源头，二者通过主从复制链路形成"生成-传输-暂存-应用"的数据流：

1.

主库执行数据变更操作，记录到binlog；

2.

从库I/O线程请求并接收主库的binlog事件；

3.

从库将接收到的binlog事件写入本地relaylog；

4.

从库SQL线程读取relaylog并执行其中的SQL命令，实现数据同步 4 。

简言之，binlog是主库的"变更记录源"，relaylog是从库的"变更中转站"，二者协同实现主从数据一致性。

13. mysql主从同步时是如何保障数据一致性的

MySQL主从同步通过多种机制保障数据一致性，核心措施包括：

一、复制模式选择

1. 1.

异步复制 （默认）：主库提交事务后立即返回，不等待从库确认。性能最高但一致性最弱，可能因主库崩溃导致数据丢失 2 。

2. 2.

半同步复制 ：主库需等待至少一个从库确认接收binlog后才提交事务，平衡性能与一致性 2 。

3. 3.

全同步复制 ：主库等待所有从库应用完事务才返回，一致性最高但性能开销大。

二、核心技术保障

- 二进制日志（binlog） ：主库记录所有数据变更，确保变更可追溯 5 。

- 中继日志（relaylog） ：从库暂存主库binlog，实现异步应用 。

- 双线程协作 ：

- I/O线程：从主库拉取binlog并写入relaylog

- SQL线程：解析relaylog并执行SQL 5

三、一致性监控机制

1. 1.

状态检查 ：通过 SHOW SLAVE STATUS 查看关键指标：

- Slave_IO_Running / Slave_SQL_Running ：线程运行状态

- Seconds_Behind_Master ：从库落后时间（值越小一致性越好） 5 。

2. 2.

事件校验 ：主从通过binlog事件序号确保顺序执行，避免乱序 5 。

四、异常处理策略

- 自动重连 ：从库断开后自动重试连接主库

- 日志校验 ：通过binlog校验确保传输完整性

- 延迟阈值告警 ：当Seconds_Behind_Master超过阈值时触发警报

通过组合使用上述机制，MySQL可在性能与一致性间取得平衡，其中半同步复制是生产环境的常用选择。

14. 按照时间顺序，描述主从同步的所有步骤