mysql binlog同步数据

1. 什么是Binlog

MySQL的二进制日志（Binlog）是一种事务日志，用于记录对数据库的更改操作。
Binlog主要用于MySQL复制和恢复:

• 复制: 从库通过拉取主库的binlog实现主从数据一致
• 恢复: 通过重放binlog恢复数据丢失或误操作情况

1.1. Binlog的工作原理

在MySQL中，每个事务都会在提交后生成相应的Binlog记录。
MySQL服务器会为每个客户端连接创建一个线程，称为Binlog Dump线程，
负责将Binlog的内容传送给从服务器，用于数据复制。
Binlog可以在服务器的文件系统中持久化存储，保证了数据的持久性

1.2. Binlog的格式

MySQL支持多种Binlog格式，包括Statement(语句)、Row(行)和Mixed(混合)三种。不同格式在记录更改时的粒度和复制方式上有所不同：

Statement格式：记录SQL语句的执行内容，简单高效，但可能存在非确定性问题，如触发器的执行结果与主服务器不一致。
Row格式：记录每一行数据的变更，提供了更精确的复制，但产生的日志量较大。
Mixed格式：结合了Statement和Row格式的优点，MySQL根据具体的情况来选择合适的记录方式。

1.3开起binlog

要查看是否开启了binlog，可以通过以下方法:

在MySQL中查询变量:SHOW VARIABLES LIKE 'log_bin';
检查配置文件my.cnf中是否配置了log-bin参数
查看数据目录是否存在日志文件，默认名称类似mysql-bin.000001
如果未开启，可以配置my.cnf来开启binlog:

在my.cnf配置文件中[mysqld]加入以下配置，然后重启MySQL:
 

[mysqld]

log_bin=mysql-bin

binlog-format=Row

server-id = 1

2. 使用Binlog恢复数据

Binlog不仅用于数据复制，还可以用于数据恢复。当数据库发生误操作、数据损坏或数据丢失时，可以通过Binlog来还原数据。

2.1 导出Binlog文件

在需要恢复数据的MySQL服务器上，首先需要导出Binlog文件。可以使用以下命令关闭当前的Binlog文件并生成一个新的Binlog文件：

mysql> FLUSH LOGS;

2.2 使用mysqlbinlog工具

接下来，使用mysqlbinlog工具来解析Binlog文件，并将其中的SQL语句导出到一个文本文件中，可以使用以下命令：

mysqlbinlog binlog.000001 > binlog.sql

2.3 恢复数据

将导出的SQL语句文件binlog.sql导入到目标数据库中，即可还原数据：

mysql -u username -p -h hostname dbname < binlog.sql

3. 高级数据恢复技巧

除了基本的Binlog数据恢复外，还可以通过Binlog实现一些高级的数据恢复技巧：

3.1 Point-in-Time Recovery（PITR）

使用Binlog中的时间戳信息，可以实现在特定时间点之前恢复数据库数据，称为PITR恢复。通过在导出Binlog时指定时间戳参数，可以选择将数据库恢复到历史某个时间点的状态。

mysqlbinlog --start-datetime  "2023-07-30 17:00:00" mysql-bin.000001 > /home/mysql_backup/binlog_raw.sql

3.2 数据库对象过滤

在解析Binlog时，可以使用mysqlbinlog工具提供的参数来选择只恢复某些数据库、表或者特定类型的SQL语句，从而实现更加精细化的数据恢复。

mysqlbinlog --database sync_test mysql-bin.000001 > /home/mysql_backup/binlog_raw.sql

4.使用问题

在使用mysqlbinlog 查看binlog时 发现 报如下错误

../bin/mysqlbinlog mysql-bin.000003 >./out.sql
mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8'

原因是mysql自带的 mysqlbinlog工具无法识别binlog中的配置中的default-character-set=utf8这个指令。

解决办法 一

修改mysql配置文件
将配置 default-character-set=utf8mb4 修改为 character-set-server = utf8mb4
注意：此操作需要重启MySQL服务 线上不建议操作
解决办法二
更换打开的命令 使用如下命令打开
 

../bin/mysqlbinlog --no-defaults mysql-bin.000002

文件虽然打开了 但是乱码了

问题解决办法

../bin/mysqlbinlog --no-defaults --base64-output=decode-rows -v   mysql-bin.000002 

4.binlog删除

BinLogs删除

我们可以通过以下命令查看所有二进制文件的文件名称：

show binary logs;

MySQL的BinLog可以手工删除，也可以设置自动清理，手工删除有以下删除命令：

• purge binary logs to mysql-bin.000001：删除某个日志之前的所有二进制日志文件。这个命令会修改index中相关数据；
• purge binary logs before '2017-03-10 10:10:00'：清除某个时间点以前的二进制日志文件；
• purge master logs before date_sub( now( ), interval 7 day)：清除7天前的二进制日志文件；
• reset master：清除所有的二进制日志文件（当前不存在主从复制关系）；

自动清理可以通过设置expire_logs_days变量来启用，默认值为0,表示不启用过期自动删除功能，如果启用了自动清理功能，表示超出此天数的二进制日志文件将被自动删除，自动删除工作通常发生在MySQL启动时或FLUSH日志时。