一、Binary log简介

1.1.Binary log官方描述

The binary log contains “events” that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows), unless row-based logging is used. The binary log also contains information about how long each statement took that updated data. The binary log has two important purposes:

For replication, the binary log on a replication source server provides a record of the data changes to be sent to replicas. The source sends the information contained in its binary log to its replicas, which reproduce those transactions to make the same data changes that were made on the source. See Section 17.2, “Replication Implementation”.
Certain data recovery operations require use of the binary log. After a backup has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup. See Section 7.5, “Point-in-Time (Incremental) Recovery”.

Binary log 包含了描述数据变化的事件,例如表创建操作、表数据变化操作(新增、更新、删除). 还包含了可能造成变化的事件(如 delete操作,但是无匹配行,即没有删除任何记录),除非使用row模式. Binary Log 还包含了sql执行的时间.

Binary Log主要有以下用途:

复制功能. 当源数据库的记录有变化时,会以binary log的形式发送给该数据库的副本.副本根据这些事务操作的binary log信息,进行与源数据库相同的操作,保证数据一致
数据恢复: 当使用数据备份恢复数据时,需要在执行完备份后,重新执行备份后在binary log中记录的事件,使得数据库达到最新的数据状态.

有以下点,需要注意:

binary log并不会记录select 、show这些不会改变数据的操作. 如果要想记录所有的操作可以使用general log.
binary log有可能不完成,当数据库突然发生意外如宕机、断电等,有可能造成一条binary log的事件记录不完整.
1. 所以当恢复时只会回读完整事件的数据,并删除不完整的事件
sql语句中的密码信息将会以明文的形式存储在binary log中
开启binary log可能会轻微降低数据库性能,但是相对可以通过binary log进行复制、数据恢复,这点影响不重要
binary log功能默认是不开启的

1.2.Binary log 格式

binary log 提供三种数据格式:

statement: 记录当前发生的sql语句
row: 记录哪些行受到的影响,变化是什么.
Mixed: 混合日志.默认情况下基于statement形式记录事件,但是在某些情况下,会切换到row模式记录事件.

1.2.1.如何开启Binary Log和设置

Binary log默认是关闭的,开启需要在my.cnf文件配置如下:

[mysqld]
log-bin=mysql-bin
server-id=1
binlog-format=Row

含义:

log-bin:binary log的名称,后缀会从000001开始,当前log的全称为mysql-bin.000001.
server-id:当前mysql 服务器id ,一定需要配置.
binlog-format : binary log事件格式

命令行开启:

SET SQL_LOG_BIN=1 命令开启
SET SQL_LOG_BIN=0 命令关闭

如何查看binary log,因为binary log是二进制的,需要借助工具才能查看,mysql 默认提供的工具是:/usr/bin/mysqlbinlog命令,使用方式如下:

/usr/bin/mysqlbinlog mysql-bin.000001 

//以下为一个事件
BEGIN
/*!*/;
# at 294
#220628 15:39:12 server id 1  end_log_pos 352 CRC32 0xd84698fe  Table_map: `test_db`.`t_user_0` mapped to number 108
# at 352
#220628 15:39:12 server id 1  end_log_pos 436 CRC32 0x5a9818d0  Update_rows: table id 108 flags: STMT_END_F

BINLOG '
oCC7YhMBAAAAOgAAAGABAAAAAGwAAAAAAAEAB3Rlc3RfZGIACHRfdXNlcl8wAAMICA8CLAEG/phG
2A==
oCC7Yh8BAAAAVAAAALQBAAAAAGwAAAAAAAEAAgAD///4AQAAAAAAAAAAAECC9gJhCgYAdGVzdF8x
+AEAAAAAAAAAAABAgvYCYQoEAHRlc3TQGJha
'/*!*/;
# at 436
#220628 15:39:12 server id 1  end_log_pos 467 CRC32 0x0aa9b75f  Xid = 28
COMMIT/*!*/;
# at 467
#220628 17:13:17 server id 1  end_log_pos 532 CRC32 0xb0bf69e7  Anonymous_GTID  last_committed=1        sequence_number=2       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 532
#220628 17:13:17 server id 1  end_log_pos 607 CRC32 0x0d3ee587  Query   thread_id=5     exec_time=0     error_code=0
SET TIMESTAMP=1656436397/*!*/;

1.2.1.1.其他参数

sync_binlog

sync_binlog=1

定义binlog日志同步到硬盘的规则。取值范围为0~N

0
当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让操作系统的Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。这个是性能最好的
1或者N
当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

binlog_cache_size

为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存。默认值：32768，即32K。如果经常有大事务操作可以将这个值调大一点点.

max_binlog_cache_size

一个事务可用的最大cache，否则会报错。该变量的默认值是16EB，非常非常大，但是官方文档说的是最大值推荐4GB。还请有经验的人留下看法。

max_binlog_size

定义了binlog的文件大小，超过这个大小会关闭当前文件然后打开下一个，最大值是1GB。不会严格按照这个大小来切割，因为会优先保证一个事务不会分割到多个binlog中。

1.2.2.statement格式

在statement格式下,每一条修改数据的sql都会被记录到binary log中,副本在复制时通过sql进程解析master传来的执行过的相同sql进行执行.

优点:

不需要记录每一行的数据变化,减少binary log的日志量,节约IO,提高性能.

缺点:

可能会造成主从数据不一致问题.例如sql中包含time = now()或time = uuid()这种函数时,直接执行sql,会造成不一致的结果.

1.2.3.row模式

row模式是从MySQL 5.1.5.版本引入的,它不记录具体的sql,而是记录每一条数据的变化情况.

优点:

能够准确的记录数据的变化

缺点:

日志量太大.会带来IO性能问题.

二、复制的原理

复制是MySQL数据库提供的一种高可用高性能的解决方案.总体来说,复制分为以下三步:

主服务器(master) 把数据变更记录到binary log中
从服务器(slave)把主服务器的binary log复制到自己的中继日志(relay log)中
从服务器重做中继日志中的日志,把更改应用到自己的数据库上,以达到数据的最终一致性

在这里插入图片描述

从服务器建议设置read-only选项,保证从服务器的数据只来源于主服务器.

三、数据恢复

Binary log除了可以用于主从复制,还可以通过它完成point-in-time的数据恢复工作.

当需要数据恢复时,步骤如下:

将某一个时刻的全量数据备份加载到当前数据库
通过执行Flush logs命令,生成一个新的binary log文件,然后备份之前的日志文件
执行mysqlbinlog 日志文件 --start-position=起始位置 --stop-position=结束位置 > test.sql
然后在当前数据库执行test.sql

原文链接：https://blog.csdn.net/qq_28136919/article/details/125512930