二进制日志（binlog）

作用

  • 数据恢复：用于基于时间点的恢复。通过 binlog 可以恢复在备份后到数据丢失之间的所有操作。

  • 复制：在主从复制（Master-Slave Replication）中，主服务器将数据修改记录写入 binlog，从服务器读取这些日志并重放，从而保持数据一致性。

特点

  • 记录所有对数据库进行修改的 SQL 语句。

  • 以事件的形式记录（每个事件对应一个 SQL 语句）。

  • 可以用于增量备份。

开启 binlog 功能及基本操作

找到 MySQL 的配置文件，在文件中添加一行 log_bin = mysql-bin 。

文件通常为：/etc/mysql/my.cnf

[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录# 切记此处一定要用双斜杠
basedir=D:\\DevPackTool\\mysql-8.0.22-winx64 
# 设置mysql数据库的数据的存放目录
datadir=D:\\DevPackTool\\mysql-8.0.22-winx64\\data
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=UTF8MB4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
#开启binlog
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
expire_logs_days = 7
server-id=1  
[mysql]
# 设置mysql客户端默认字符集
default-character-set=UTF8MB4
 
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=UTF8MB4

  • log_bin：指定 binlog 文件的路径。

  • binlog_format：binlog 的格式，可以是 ROW、STATEMENT 或 MIXED。

  • server_id：服务器 ID，必须是唯一的。

  • expire_logs_days：binlog 文件自动删除的天数。

重启MySQL

sudo systemctl restart mysql

使用 Binlog 恢复数据库

备份当前数据库（假设数据库名为 mydb）：

mysqldump -u root -p --all-databases --single-transaction --flush-logs --master-data=2 > full_backup.sql

记录当前的 binlog 位置：

在备份文件 full_backup.sql 的开头部分，可以找到类似如下的信息：

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;

记下 MASTER_LOG_FILE 和 MASTER_LOG_POS 的值。

进行恢复操作：

  • 首先，恢复全量备份：

mysql -u root -p < full_backup.sql

  • 其次，从 binlog 文件中恢复后续的数据变化：

mysqlbinlog --start-position=154 --stop-position=END /var/log/mysql/mysql-bin.000001 | mysql -u root -p

其中，--start-position 是上面记录的 MASTER_LOG_POS 值，/var/log/mysql/mysql-bin.000001 是记录的 MASTER_LOG_FILE 路径。你也可以恢复多个 binlog 文件，直到恢复到你想要的时间点为止。

  • 恢复到 2023-07-23 10:00:00 之前的所有操作。

mysqlbinlog --stop-datetime="2023-07-23 10:00:00" /var/log/mysql/mysql-bin.000001 | mysql -u root -p

  • 继续恢复后续的 binlog 文件：

mysqlbinlog --start-datetime="2023-07-23 10:00:00" /var/log/mysql/mysql-bin.000002 | mysql -u root -p