二进制日志

2021-01-22

二进制日志

二进制日志包含描述数据库更改（例如表创建操作或表数据更改）的“事件”。它还包含针对可能进行了更改的语句的事件。还包含有关每个语句花费该更新数据多长时间的信息。

目的

  • 复制源服务器上的二进制日志提供了要发送到副本的数据更改的记录。源将其二进制日志中包含的事件发送到其副本，副本将执行这些事件以对进行与源相同的数据更改。
  • 某些数据恢复操作需要使用。在还原备份后，执行备份后记录的二进制日志中的事件。

二进制日志记录

  • 在未提交的事务中，更改事务表的所有更新（UPDATE， DELETE或 INSERT）都会InnoDB被缓存，直到服务器接收到一条COMMIT语句。mysqld在COMMIT执行之前将整个事务写入二进制日志
  • 当处理事务的线程启动时，它将为缓冲区语句分配一个缓冲区。如果语句大于binlog_cache_size值，则线程将打开一个临时文件来存储事务。当线程结束时，将删除临时文件
  • 如果使用InnoDB表，MySQL服务器处理一条COMMIT 语句，它将按顺序将许多准备好的事务写入二进制日志，然后将此事务提交到InnoDB。如果服务器在这两个操作之间意外退出，则事务将在重新启动时回滚，但仍存在于二进制日志中。

日志文件

mysqld在二进制日志基本名称后附加数字扩展名以生成二进制日志文件名。每次发生以下任何事件，服务器都会在系列中创建一个新文件：

  • 服务器已启动或重新启动
  • 服务器刷新日志
  • 当前日志文件达到max_binlog_size的大小

mysqld还创建了一个二进制日志索引文件，其中包含二进制日志文件的名称。默认情况下，该名称与二进制日志文件具有相同的基本名称，扩展名为 '.index'。

配置

# 文件地址，默认为host_name-bin
log-bin=/var/log/mysql-bin-log

# 二进制日志索引文件的名称，默认为host_name-bin.index
log-bin-index=/var/log/mysql-bin-log.index

# 二进制日志格式；在MySQL 5.7.7之前，默认格式为 STATEMENT。在MySQL 5.7.7和更高版本中，默认值为ROW
binlog_format=ROW

# 在事务期间要保存对二进制日志的更改的高速缓存的大小。该值必须是4096（字节）的倍数。如果您经常使用大型事务，则可以增加缓存大小以获得更好的性能
binlog_cache_size=

# 二进制日志的高速缓存大小，以保存事务期间发出的非事务性语句。该值必须是4096的倍数。如果您在事务期间经常使用大型非事务性语句，则可以增加此缓存的大小以获得更好的性能
binlog_stmt_cache_size=

# 日志文件自动删除的天数。默认值为0，表示不自动删除
expire_logs_days=

# 日志大小，超过时将生成新日志文件。最小值为4096字节。最大值和默认值为1GB。
max_binlog_size=

日志格式

ROW(基于行的日志)

源将事件写入二进制日志，这些事件指示如何影响各个表行

STATEMENT(基于语句的日志)

在某些更改时仍然会使用基于语句的格式。例如所有DDL（数据定义语言）语句

MIXED(混合的日志)

  • 默认情况下使用基于语句的日志记录，但是在某些情况下，日志记录模式会自动切换为基于行
  • InnoDB表事务隔离级别为READ COMMITTED或READ UNCOMMITTED时只能使用基于行的格式记录日志。将日志更改为STATEMENT时InnoDB不能执行insert操作

服务器在以下情况下自动从基于语句的记录切换为基于行的记录

  • 使用DML语句更新NDBCLUSTER表时
  • 当函数包含UUID()
  • 当一个或多个带有AUTO_INCREMENT列的表被更新并且触发器或存储的函数被调用时。这是如果binlog_format=STATEMENT会生成警告
  • 当视图主体需要基于行的复制时，创建视图的语句也将使用它。例如，当创建视图的语句使用该UUID()函数时
  • 当涉及对可加载函数的调用时
  • 如果一条语句按行记录，并且执行该语句的会话中有临时表，则按行记录将用于所有后续语句（访问临时表的语句除外），直到该会话使用的所有临时表都被删除为止
  • 使用FOUND_ROWS()或ROW_COUNT()
  • 使用USER()， CURRENT_USER()或CURRENT_USER
  • 当一条语句引用一个或多个系统变量时,使用以下系统变量时不会导致日志记录格式切换
    • auto_increment_increment,auto_increment_offset,character_set_client,character_set_connection,character_set_database,character_set_server,collation_connection,collation_database,collation_server,foreign_key_checks,identity,last_insert_id,lc_time_names,pseudo_thread_id,sql_auto_is_null,time_zone,timestamp,unique_checks
  • 当涉及的表是mysql数据库中的日志表时 
  • 使用LOAD_FILE()功能

设置日志格式

# 修改当前回话
mysql> SET SESSION binlog_format = 'STATEMENT';
mysql> SET SESSION binlog_format = 'ROW';
mysql> SET SESSION binlog_format = 'MIXED';

# 全局修改
mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';

日志记录规则

  • 直接更改mysql数据库表中的数据的数据操作语句将导致记录日志，如：INSERT、UPDATE、DELETE、REPLACE、DO、LOAD DATA、SELECT和TRUNCATE TABLE
  • 间接改变mysql数据库的语句都被记录为语句，无论binlog_format的值是什么，如：GRANT, REVOKE, SET PASSWORD, RENAME USER, CREATE(除了CREATE TABLE…SELECT)， ALTER(所有形式)和DROP(所有形式)
  • CREATE TABLE ... SELECT是数据定义和数据操作的组合。该CREATE TABLE 部分使用语句格式记录，SELECT部分根据binlog_format的值记录

二进制日志内容

  • 第一行
    • at后面的数字表示二进制日志文件中事件的文件偏移量或起始位置
  • 第二行
    • 以日期和时间开头，该日期和时间指示该语句在事件起源的服务器上何时启动。对于复制，此时间戳会传播到副本服务器。
    • server id是事件起源的服务器的server_id值。
    • end_log_pos指示下一个事件的开始位置（即，当前事件的结束位置+ 1）。
    • thread_id指示哪个线程执行了事件。
    • exec_time是在源服务器上执行事件所花费的时间。在副本上，它是副本上结束执行时间减去源上开始执行时间的差。差异可作为复制滞后于源的指标。
    • error_code指示执行事件的结果。零表示没有错误发生。

mysqlbinlog

以文本格式显示二进制日志内容，对于基于语句的日志记录，事件信息包括SQL语句，在其上执行该语句的服务器的ID，该语句在执行时的时间戳，所花费的时间等等。对于基于行的日志记录，该事件指示行更改，而不是SQL语句。

mysqlbinlog [options] log_file ... 
#读取并写入到文件
mysqlbinlog [options] log_file ...  > test.txt

分页查看二进制日志

mysqlbinlog binlog_files | more

读取二进制日志

指定连接属性查看

mysqlbinlog -h host_name -P port_num -u user_name -p [password] --protocol=TCP binlog_files

mysqlbinlog -S path binlog_files

# MySQL服务器的主机名
-h host_name
# 端口号
-P port_num
# 用户名
-u user_name
# 密码
-p[password]
# 连接到服务器的传输协议
--protocol={TCP|SOCKET|PIPE|MEMORY}

# 使用套接字连接本地服务器
-S path

根据时间获取二进制日志数据

mysqlbinlog --start-datetime=datetime --stop-datetime=datetime binlog_files

# datetime值相对于运行mysqlbinlog的计算机上的本地时区

根据事件位置获取二进制日志数据

mysqlbinlog --start-position=N --stop-position=N binlog_files

# 仅命令行中的第一个日志有效

输出指定数据库的日志

mysqlbinlog --database=db_name binlog_files

/*
对于基于语句的日志记录
1、显示use db_name语句后面的修改语句
2、显示CREATE DATABASE、ALTER DATABASE和DROP DATABASE语句
*/

/*
对于基于行的日志记录
只显示更改了属于db_name的表的条目
*/

日志中的GTID处理

# 不显示gtid_set中列出的任何组
mysqlbinlog --exclude-gtids=gtid_set binlog_files
# 只显示gtid_set中列出的任何组
mysqlbinlog --include-gtids=gtid_set binlog_files
# 不在输出中显示任何GTID，从包含GTID的日志写入转储文件时需要
mysqlbinlog --skip-gtids[=(true|false)] binlog_files

仅显示日志中包含的语句，而不显示其他信息或基于行的事件。这仅用于测试，不应在生产系统中使用

mysqlbinlog --short-form binlog_files

指定服务器

# 仅显示给定服务器ID的服务器创建的事件
mysqlbinlog --server-id=id binlog_files
# 使用server_id的前N位匹配服务器，并显示对应事件
mysqlbinlog --server-id-bits=N binlog_files
# 指定要报告的服务器ID。可以避免与副本服务器或另一个mysqlbinlog进程的ID冲突
mysqlbinlog --stop-never-slave-server-id=id binlog_files

读取已打开或未关闭的日志

mysqlbinlog --force-if-open binlog_files

读取日志并忽略错误事件

mysqlbinlog --force-read binlog_files

指定基于行的日志文件大小，将行以大小分组；单位：字节；默认4GB，应该是256的倍数

mysqlbinlog --binlog-row-event-max-size=N binlog_files

读取日志时显示SQL语句

# 重建行事件并将其显示为带注释的SQL语句
mysqlbinlog --verbose binlog_files
mysqlbinlog -v binlog_files

# 输出包含指示列数据类型和某些元数据的注释，以及行查询日志事件（如果已配置）。
mysqlbinlog --verbose --verbose binlog_files
mysqlbinlog -vv binlog_files

禁用二进制日志记录，避免无限循环，在意外退出后恢复时，可避免重复记录的语句

mysqlbinlog --disable-log-bin binlog_files

测试服务器是否支持BINLOG_DUMP_NON_BLOCK连接

mysqlbinlog --connection-server-id=server_id binlog_files

# 阻塞模式下为1，非阻塞模式下为0

COM_BINLOG_DUMP/COM_BINLOG_DUMP_GTID

# 在源上被过滤掉不必要的事务，避免不必要的网络流量
mysqlbinlog --read-from-remote-master=BINLOG-DUMP-GTIDS --exclude-gtids binlog_files

/*
指定读取命令
--read-from-remote-master=type
COM_BINLOG_DUMP     由一个副本或mysqlbinlog用来初始化二进制日志读取
COM_BINLOG_DUMP_GTID
*/

将日志写入指定目录中的文件并自动调用LOAD DATA LOCAL导入数据，文件不会自动删除

mysqlbinlog --local-load=dir_name binlog_files

以二进制格式将二进制日志写入其他文件，可以用来实时备份二进制日志

mysqlbinlog --raw --stop-never --read-from-remote-server --result-file=name --to-last-log binlog_files

# 读取远程服务器日志
--read-from-remote-server
# 将日志以二进制形式写入其他文件
--raw
# 保持连接到服务器，否则当读取完日志后mysqlbinlog将会退出
--stop-never
# 指定日志写入文件的名称，与--raw一起使用为修改输出文件名的前缀
--result-file=name
# 不是从MySQL服务器请求的二进制日志的结尾处停止，而是继续打印直到最后一个二进制日志(当前日志)的结尾;如果将输出发送到同一服务器，可能会导致无限循环;要与--read-from-remote-server一起使用
--to-last-log

转换日志中的数据库名，以将数据导入到其他数据库

mysqlbinlog --rewrite-db="from_name->to_name" binlog_files

# 基于行的日志，将对行进行重写
# 基于语句的日志，将对USE子句进行重写
# 指定多以替换多个库名

恢复

二进制日志中数据表是指定在某个数据库下的，如果需要导入到其他数据需要修改数据库名

方法1：

shell> mysqlbinlog gbichot2-bin.000007 gbichot2-bin.000008 --rewrite-db="from_name->to_name" | mysql -u root -p

方法2：

shell> mysqlbinlog --rewrite-db="from_name->to_name" binlog.000001 >  /tmp/dump.sql
shell> mysql -u root -p -e "source /tmp/dump.sql"

备份

静态备份

-- 备份130，,131,132三个文件
mysqlbinlog --read-from-remote-server --host=host_name --raw
  binlog.000130 binlog.000131 binlog.000132

实时备份

-- 复制130文件，并保持连接状态以复制新事件
mysqlbinlog --read-from-remote-server --host=host_name --raw
  --stop-never binlog.000130

备份限制

如果连接丢失（例如，如果发生服务器重启或网络中断）， mysqlbinlog不会自动重新连接到MySQL服务器
在MySQL 5.7.19之前，mysqlbinlog不会获得所有已提交的事件，即使服务器配置了sync_binlog=1。这意味着某些最新事件可能会丢失。为确保mysqlbinlog查看最新事件，请在要备份的服务器上刷新二进制日志。
备份的延迟类似于复制副本的延迟

CATALOG
标签
mysql redis sqlserver
{/if}