MySQL BinLog

MySQL Binlog 简介

Binlog（Binary Log，二进制日志）是 MySQL 数据库中用于记录数据库更改操作的一种日志文件。它主要记录了数据库的 数据变更事件（如 INSERT、UPDATE、DELETE 等操作）以及某些 DDL（数据定义语言，如 CREATE、ALTER、DROP 等）操作。Binlog 是 MySQL 高可用性和数据恢复的核心组件之一，常用于以下场景：

主从复制：主库将 binlog 传输到从库，从库通过重放 binlog 实现数据同步。
数据恢复：结合全量备份，binlog 可以实现增量恢复，恢复到某个时间点或某个事务。
数据分析：用于审计或分析数据库的操作历史。

Binlog 的工作原理

当 MySQL 的事务提交时，数据库会将变更操作记录到 binlog 文件中。
Binlog 文件以二进制格式存储，内容包括事件的时间戳、SQL 操作、事务信息等。
Binlog 是按顺序写入的，文件会根据大小或时间进行切割，形成多个 binlog 文件（如 mysql-bin.000001, mysql-bin.000002）。

Binlog 的格式

MySQL 支持三种 binlog 格式：

Statement 模式：记录 SQL 语句本身，文件较小，但可能因函数（如 NOW()）导致主从不一致。
Row 模式：记录每行数据的实际变化，文件较大，但保证主从数据一致性，适合复杂查询。
Mixed 模式：混合模式，根据操作类型自动选择 Statement 或 Row 格式，兼顾效率和一致性。

配置 binlog 格式的方法：

SET GLOBAL binlog_format = 'ROW'; -- 设置为 Row 模式

启用 Binlog

要使用 binlog，需要在 MySQL 配置文件（通常是 my.cnf 或 my.ini）中启用：

[mysqld]
log_bin = mysql-bin
binlog_format = ROW
server_id = 1 -- 每个实例需要唯一 ID，主从复制时必须配置

启用后，MySQL 会生成 binlog 文件，通常位于数据目录下。

查看和管理 Binlog

查看 binlog 文件列表：
```
SHOW BINARY LOGS;
```
查看当前正在写入的 binlog：
```
SHOW MASTER STATUS;
```
查看 binlog 内容：

使用 mysqlbinlog 工具解析 binlog 文件：
```
mysqlbinlog --verbose mysql-bin.000001
```

清理 binlog：

binlog 文件会占用磁盘空间，可定期清理：

PURGE BINARY LOGS TO 'mysql-bin.000010'; -- 删除指定文件之前的 binlog
PURGE BINARY LOGS BEFORE '2025-08-01 00:00:00'; -- 删除指定时间前的 binlog

MySQL 备份与回档

备份和回档是数据库管理中至关重要的操作，用于防止数据丢失和恢复到特定状态。结合 binlog，可以实现 全量备份 和 增量恢复。

备份类型

全量备份：备份数据库的完整数据，通常使用工具如 mysqldump 或 xtrabackup。
增量备份：通过 binlog 记录数据库变更，结合全量备份实现增量恢复。
逻辑备份：导出 SQL 语句（如 mysqldump），适合小规模数据库。
物理备份：直接复制数据库文件（如 xtrabackup），速度快，适合大规模数据库。

常用备份工具

mysqldump：
- 逻辑备份工具，生成可执行的 SQL 脚本。
- 示例：
```
mysqldump -u root -p --databases mydb > mydb_backup.sql
```
- 优点：跨版本、跨平台兼容性好。
- 缺点：备份和恢复速度较慢，适合中小型数据库。
xtrabackup（Percona XtraBackup）：
- 物理备份工具，适合大规模数据库。
- 支持热备份（不锁表）、增量备份。
- 示例：
```
xtrabackup --backup --target-dir=/backup/
```

备份策略

定期全量备份：例如每周进行一次全量备份。
增量备份：每天或更频繁地保存 binlog 文件。
备份存储：将备份文件存储到远程服务器或云存储，确保安全性。
验证备份：定期测试备份文件是否可用。

数据回档（恢复）

数据回档通常结合全量备份和 binlog 实现 Point-in-Time Recovery (PITR)，即恢复到某一特定时间点。

恢复步骤：

恢复全量备份：

如果使用 mysqldump：
```
mysql -u root -p mydb < mydb_backup.sql
```

如果使用 xtrabackup：

xtrabackup --copy-back --target-dir=/backup/

确定恢复的时间点或位置：
- 找到需要恢复到的时间点或 binlog 位置（position）。
- 使用 mysqlbinlog 查看 binlog 事件：
```
mysqlbinlog --start-datetime="2025-08-20 10:00:00" --stop-datetime="2025-08-20 12:00:00" mysql-bin.000001 > events.sql
```
应用 binlog：
- 将解析出的 binlog 事件应用到数据库：
```
mysql -u root -p < events.sql
```
验证数据：
- 检查数据完整性和一致性，确保恢复成功。

示例场景：恢复到某时间点

假设数据库在 2025-08-20 12:00:00 发生误操作，需要恢复到该时间点： 1. 恢复最近的全量备份（例如 2025-08-19 的备份）。 2. 使用 mysqlbinlog 提取 2025-08-19 到 2025-08-20 12:00:00 的 binlog 事件。 3. 执行提取的 SQL 语句，完成恢复。

注意事项

开启 binlog：恢复到特定时间点必须启用 binlog。
事务一致性：使用 Row 格式的 binlog 确保数据一致性。
备份验证：定期测试备份和恢复流程，确保可靠性。
权限管理：确保备份和恢复操作由授权用户执行。
磁盘空间：binlog 和备份文件可能占用大量空间，需定期清理。

总结

Binlog 是 MySQL 的核心功能，用于记录数据变更，支持主从复制和数据恢复。
备份分为全量备份和增量备份，常用工具包括 mysqldump 和 xtrabackup。
回档通过全量备份和 binlog 实现点对点恢复（PITR），能精确恢复到指定时间点。
合理配置 binlog 和备份策略可以大幅提升数据库的可靠性和可恢复性。

mysql docker-compose样例

docker pull mysql:latest

version: '3'
services:
  mysql:
    image: mysql:latest
    container_name: mysql-container
    environment:
      MYSQL_ROOT_PASSWORD: password
      # MYSQL_DATABASE: mydb
      MYSQL_USER: username
      MYSQL_PASSWORD: password
    volumes:
      - mysql-data:/var/lib/mysql
    ports:
      - "3306:3306"
    command: --log-bin=mysql-bin --server-id=1 --binlog-format=STATEMENT
volumes:
  mysql-data:

root@ser745692301841:/dev_dir/docker# ls
docker-compose.yml
root@ser745692301841:/dev_dir/docker# docker-compose up -d

root@ser745692301841:/dev_dir/docker# docker volume ls
DRIVER    VOLUME NAME
local     docker_mysql-data
root@ser745692301841:/dev_dir/docker# docker volume inspect docker_mysql-data
[
    {
        "CreatedAt": "2025-08-22T01:16:19Z",
        "Driver": "local",
        "Labels": {
            "com.docker.compose.project": "docker",
            "com.docker.compose.version": "1.29.2",
            "com.docker.compose.volume": "mysql-data"
        },
        "Mountpoint": "/var/lib/docker/volumes/docker_mysql-data/_data",
        "Name": "docker_mysql-data",
        "Options": null,
        "Scope": "local"
    }
]

root@ser745692301841:/dev_dir/docker# docker ps -a
CONTAINER ID   IMAGE          COMMAND                  CREATED         STATUS         PORTS                               NAMES
67d01f13e776   mysql:latest   "docker-entrypoint.s…"   3 minutes ago   Up 3 minutes   0.0.0.0:3306->3306/tcp, 33060/tcp   mysql-container

root@ser745692301841:/dev_dir/docker# docker-compose down
Stopping mysql-container ... done
Removing mysql-container ... done
Removing network docker_default
root@ser745692301841:/dev_dir/docker# docker ps -a
CONTAINER ID   IMAGE     COMMAND   CREATED   STATUS    PORTS     NAMES

root@ser745692301841:/dev_dir/docker# docker-compose up -d 
Creating network "docker_default" with the default driver
Creating mysql-container ... done
root@ser745692301841:/dev_dir/docker# docker ps -a
CONTAINER ID   IMAGE          COMMAND                  CREATED         STATUS         PORTS                               NAMES
d64819718025   mysql:latest   "docker-entrypoint.s…"   4 seconds ago   Up 3 seconds   0.0.0.0:3306->3306/tcp, 33060/tcp   mysql-container

root@ser745692301841:/dev_dir/docker# docker exec -it mysql-container bash
bash-5.1# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 9.3.0 MySQL Community Server - GPL

Copyright (c) 2000, 2025, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW VARIABLES LIKE 'binlog_format';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.023 sec)

mysql>

mysql> CREATE DATABASE mydb;
Query OK, 1 row affected (0.007 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.011 sec)

mysql> use mydb;
Database changed
mysql> CREATE TABLE users (    id INT AUTO_INCREMENT PRIMARY KEY,    username VARCHAR(50) NOT NULL UNIQUE,    email VARCHAR(100) NOT NULL UNIQUE,    password VARCHAR(255) NOT NULL,    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.028 sec)

mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| users          |
+----------------+
1 row in set (0.005 sec)

mysql> desc users;
+------------+--------------+------+-----+-------------------+-------------------+
| Field      | Type         | Null | Key | Default           | Extra             |
+------------+--------------+------+-----+-------------------+-------------------+
| id         | int          | NO   | PRI | NULL              | auto_increment    |
| username   | varchar(50)  | NO   | UNI | NULL              |                   |
| email      | varchar(100) | NO   | UNI | NULL              |                   |
| password   | varchar(255) | NO   |     | NULL              |                   |
| created_at | timestamp    | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+------------+--------------+------+-----+-------------------+-------------------+
5 rows in set (0.005 sec)

mysql> show create table users;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                              |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.020 sec)

mysql> SHOW BINARY LOGS;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 |       181 | No        |
| mysql-bin.000002 |       736 | No        |
+------------------+-----------+-----------+
2 rows in set (0.001 sec)

mysql> SHOW VARIABLES LIKE 'log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                            |
+---------------------------------+--------------------------------+
4 rows in set (0.009 sec)

binlog文件是bin格式不能使用cat和vim查看，需要使用mysql提供的mysqlbinlog

sh-5.1# mysqlbinlog  mysql-bin.000002 > mysql-bin.000002.txt