经验首页 前端设计 程序设计 Java相关 移动开发 数据库/运维 软件/图像 大数据/云计算 其他经验
当前位置：技术经验 » 数据库/运维 » MySQL » 查看文章
MySQL系列：binlog日志详解（参数、操作、GTID、优化、故障演练）
来源：cnblogs　　作者：Code技术分享　　时间：2023/11/1 9:02:35　　对本文有异议

目录

简介

文档：https://dev.mysql.com/doc/refman/5.7/en/binary-log.html

binlog日志包含有关修改数据库内容的 SQL 语句的信息。此信息以描述修改的“事件”的形式存储

作用

主从复制必须依赖于binlog日志
备份恢复必须依赖于binlog日志

系统参数

--log_bin

启用binlog日志记录

Command-Line Format --log-bin=file_name
Type File name

binlog日志文件的默认位置是数据目录

启用binlog日志记录后，服务器会将所有更改数据的语句记录到binlog日志中，用于备份和复制。binlog日志是具有基本名称和数字扩展名的文件序列。

如果您为--log-bin 选项提供值，则该值将用作日志序列的基本名称。服务器通过将数字后缀添加到基本名称来按顺序创建binlog日志文件。在 MySQL 5.7 中，基本名称默认为 host_name-bin，使用主机名。建议您指定一个基本名称，以便您可以继续使用相同的binlog日志文件名，而不管默认名称的更改。

设置此选项会导致 log_bin系统变量设置为ON（或1），而不是基本名称。binlog日志文件基本名称和任何指定的路径都可用作 log_bin_basename系统变量。

  1. (root@localhost) [(none)]> select @@log_bin;
  2. +-----------+
  3. | @@log_bin |
  4. +-----------+
  5. |         1 |
  6. +-----------+
  7. 1 row in set (0.00 sec)
  9. (root@localhost) [(none)]> select @@log_bin_basename;
  10. +-----------------------------------+
  11. | @@log_bin_basename                |
  12. +-----------------------------------+
  13. | /data/mysql/3306/binlog/mysql-bin |
  14. +-----------------------------------+
  15. 1 row in set (0.00 sec)

--server_id

指定服务器 ID

Command-Line Format --server-id=#
System Variable server_id
Scope Global
Dynamic Yes
Type Integer
Default Value 0
Minimum Value 0
Maximum Value 4294967295

在 MySQL 5.7 中， server_id如果启用了binlog日志记录，则必须指定，否则不允许启动服务器。

  1. (root@localhost) [(none)]> select @@server_id;
  2. +-------------+
  3. | @@server_id |
  4. +-------------+
  5. |           1 |
  6. +-------------+
  7. 1 row in set (0.00 sec)

--binlog_format

设置binlog日志记录格式

Command-Line Format --binlog-format=format
System Variable binlog_format
Scope Global, Session
Dynamic Yes
Type Enumeration
Default Value ROW
Valid Values MIXED STATEMENT ROW

在 MySQL 5.7.7 之前，默认格式是 STATEMENT. 在 MySQL 5.7.7 及更高版本中，默认值为ROW

  • STATEMENT：（5.7.7以前）SBR(statement based replication)，语句模式原封不动的记录当前DML
  • ROW：(5.7.7或更高 默认值) RBR(ROW based replication) ：记录数据行的变化(用户看不懂，需要工具分析)
  • MIXED:（混合）MBR(mixed based replication)模式 ：以上两种模式的混合

--sync-binlog（双一标准）

控制 MySQL 服务器将binlog日志同步到磁盘的频率

Command-Line Format --sync-binlog=#
System Variable sync_binlog
Scope Global
Dynamic Yes
Type Integer
Default Value 1
Minimum Value 0
Maximum Value 4294967295
  • 0：禁用 MySQL 服务器将binlog日志同步到磁盘。相反，MySQL 服务器依赖操作系统不时将binlog日志刷新到磁盘，就像它对任何其他文件所做的那样。此设置提供了最佳性能，但如果发生电源故障或操作系统崩溃，服务器可能已提交尚未同步到binlog日志的事务。
  • 1：在提交事务之前启用binlog日志到磁盘的同步。这是最安全的设置，但由于磁盘写入次数增加，可能会对性能产生负面影响。在电源故障或操作系统崩溃的情况下，binlog日志中丢失的事务仅处于准备状态。这允许自动恢复例程回滚事务，从而保证不会从binlog日志中丢失事务。
  • N, 其中是 0 或 1 以外的值：在收集到binlog日志提交组N后，将binlog日志同步到磁盘 。N在电源故障或操作系统崩溃的情况下，服务器可能已经提交了尚未刷新到binlog日志的事务。由于磁盘写入次数增加，此设置可能会对性能产生负面影响。较高的值会提高性能，但会增加数据丢失的风险。

--gtid-mode(gtid)

是否启用gtid

Command-Line Format --gtid-mode=MODE
System Variable gtid_mode
Scope Global
Dynamic Yes
Type Enumeration
Default Value OFF
Valid Values OFF``OFF_PERMISSIVE``ON_PERMISSIVE``ON

gtid-mode可取值：

  • OFF：不支持GTID事务，生成的是匿名事务，slave节点也只能应用匿名事务

  • OFF_PERMISSIVE：生成的是匿名事务，slave节点可以应用匿名事务和GTID事务

  • ON_PERMISSIVE：生成的是GTID事务，slave节点可以应用匿名事务和GTID事务（此步骤操作完成后，master节点 的binlog日志就会变成GTID模式）

  • ON：支持GTID事务，生成的是GTID事务，slave节点也只能应用GTID事务

--enforce-gtid-consistency(gtid)

是否允许违反gtid一致性

Command-Line Format --enforce-gtid-consistency[=value]
System Variable enforce_gtid_consistency
Scope Global
Dynamic Yes
Type Enumeration
Default Value OFF
Valid Values OFF``ON``WARN

enforce-gtid-consistency可选值：

  • OFF：允许所有事务违反 GTID 一致性。
  • ON: 不允许任何事务违反 GTID 一致性。
  • WARN：允许所有事务违反 GTID 一致性，但在这种情况下会生成警告

--enforce-gtid-consistency 没有值的设置被解释为将值设置为 ON。该变量还具有多个值的文本别名：0=OFF=FALSE, 1=ON=TRUE, 2=WARN

--expire-logs-day（优化参数）

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

Command-Line Format --expire-logs-days=#
System Variable expire_logs_days
Scope Global
Dynamic Yes
Type Integer
Default Value 0
Minimum Value 0
Maximum Value 99
Unit days

--binlog_cache_size（优化参数）

在事务期间保存binlog日志更改的缓存大小

Command-Line Format --binlog-cache-size=#
System Variable binlog_cache_size
Scope Global
Dynamic Yes
Type Integer
Default Value 32768
Minimum Value 4096
Maximum Value (64-bit platforms) 18446744073709547520
Maximum Value (32-bit platforms) 4294963200
Unit bytes
Block Size 4096

--max_binlog_cache_size（优化参数）

如果一个事务需要超过这么多字节的内存，服务器会生成一个多语句事务需要超过 'max_binlog_cache_size' 字节的存储错误。最小值为 4096。可能的最大值为 16EB（艾字节）。最大推荐值为4GB；这是因为 MySQL 目前无法处理大于 4GB 的binlog日志位置。

Command-Line Format --max-binlog-cache-size=#
System Variable max_binlog_cache_size
Scope Global
Dynamic Yes
Type Integer
Default Value 18446744073709547520
Minimum Value 4096
Maximum Value 18446744073709547520
Unit bytes
Block Size 4096

--max_binlog_size（优化参数）

命令行格式 --max-binlog-size=#
系统变量 max_binlog_size
范围 全球的
动态的 是的
类型 整数
默认值 1073741824
最小值 4096
最大值 1073741824
单元 字节
块大小 4096

如果写入binlog日志导致当前日志文件大小超过此变量的值，则服务器轮换binlog日志（关闭当前文件并打开下一个文件）。最小值为 4096 字节。最大值和默认值为 1GB。

事务以一个块的形式写入binlog日志，因此永远不会在多个binlog日志之间拆分。因此，如果您有大事务，您可能会看到大于 max_binlog_size.

如果max_relay_log_size为 0，则该值也 max_binlog_size适用于中继日志。

sql_log_bin

此变量控制是否为当前会话启用日志记录到binlog日志（假设binlog日志本身已启用）。默认值为 ON。要为当前会话禁用或启用binlog日志记录，请将会话 sql_log_bin变量设置为 OFF或ON。

System Variable sql_log_bin
Scope Session
Dynamic Yes
Type Boolean
Default Value ON

日志操作

  • DDL ：原封不动的记录当前DDL(statement语句方式)
  • DCL ：原封不动的记录当前DCL(statement语句方式)
  • DML ：只记录已经提交的事务DML

开启日志

  1. mkdir /data/mysql/3306/binlog
  2. chown -R mysql.mysql /data/mysql/3306/binlog
  3. vim /etc/my.cnf
  4. server_id=1                                          
  5. log_bin=/data/mysql/3306/binlog
  6. binlog_format=row

查看日志的开启情况

  1. show variables like '%log_bin%';
  1. (root@localhost) [(none)]> show variables like '%log_bin%';
  2. +---------------------------------+-----------------------------------------+
  3. | Variable_name                   | Value                                   |
  4. +---------------------------------+-----------------------------------------+
  5. | log_bin                         | ON                                      |
  6. | log_bin_basename                | /data/mysql/3306/binlog/mysql-bin       |
  7. | log_bin_index                   | /data/mysql/3306/binlog/mysql-bin.index |
  8. | log_bin_trust_function_creators | OFF                                     |
  9. | log_bin_use_v1_row_events       | OFF                                     |
  10. | sql_log_bin                     | ON                                      |
  11. +---------------------------------+-----------------------------------------+
  12. 6 rows in set (0.00 sec)

查看一共多少个binlog

  1.  show binary logs;
  1. (root@localhost) [(none)]> show binary logs;
  2. +------------------+-----------+
  3. | Log_name         | File_size |
  4. +------------------+-----------+
  5. | mysql-bin.000001 |      1255 |
  6. | mysql-bin.000002 |       217 |
  7. | mysql-bin.000003 |       217 |
  8. | mysql-bin.000004 |       217 |
  9. | mysql-bin.000005 |       217 |
  10. | mysql-bin.000006 |       217 |
  11. | mysql-bin.000007 |       217 |
  12. | mysql-bin.000008 |       217 |
  13. | mysql-bin.000009 |       217 |
  14. | mysql-bin.000010 |       217 |
  15. | mysql-bin.000011 |      3873 |
  16. +------------------+-----------+
  17. 11 rows in set (0.00 sec)

查看mysql正在使用的日志文件

  1. show master status;
  1. (root@localhost) [(none)]> show master status;
  2. +------------------+----------+--------------+------------------+-------------------------------------------+
  3. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
  4. +------------------+----------+--------------+------------------+-------------------------------------------+
  5. | mysql-bin.000014 |      194 |              |                  | a469ece3-10cd-11ed-98cd-525400d5deea:1-32 |
  6. +------------------+----------+--------------+------------------+-------------------------------------------+
  7. 1 row in set (0.00 sec)

event查看

  1. show binlog events in ''
  1. (root@localhost) [(none)]> flush logs;
  2. Query OK, 0 rows affected (0.02 sec)
  4. (root@localhost) [(none)]> show master status;
  5. +------------------+----------+--------------+------------------+-------------------------------------------+
  6. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
  7. +------------------+----------+--------------+------------------+-------------------------------------------+
  8. | mysql-bin.000012 |      194 |              |                  | a469ece3-10cd-11ed-98cd-525400d5deea:1-25 |
  9. +------------------+----------+--------------+------------------+-------------------------------------------+
  10. 1 row in set (0.00 sec)
  12. (root@localhost) [(none)]> show binlog events in 'mysql-bin.000012';
  13. +------------------+-----+----------------+-----------+-------------+-------------------------------------------+
  14. | Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                      |
  15. +------------------+-----+----------------+-----------+-------------+-------------------------------------------+
  16. | mysql-bin.000012 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.36-log, Binlog ver: 4     |
  17. | mysql-bin.000012 | 123 | Previous_gtids |         1 |         194 | a469ece3-10cd-11ed-98cd-525400d5deea:1-25 |
  18. +------------------+-----+----------------+-----------+-------------+-------------------------------------------+
  19. 2 rows in set (0.00 sec)
  21. (root@localhost) [(none)]> create database binlogTest;
  22. Query OK, 1 row affected (0.00 sec)
  24. (root@localhost) [(none)]> use binlogTest;
  25. Database changed
  26. (root@localhost) [binlogTest]> create table t1(id int);
  27. Query OK, 0 rows affected (0.02 sec)
  29. (root@localhost) [binlogTest]> show binlog events in 'mysql-bin.000012';
  30. +------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
  31. | Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                               |
  32. +------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
  33. | mysql-bin.000012 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.36-log, Binlog ver: 4                              |
  34. | mysql-bin.000012 | 123 | Previous_gtids |         1 |         194 | a469ece3-10cd-11ed-98cd-525400d5deea:1-25                          |
  35. | mysql-bin.000012 | 194 | Gtid           |         1 |         259 | SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:26' |
  36. | mysql-bin.000012 | 259 | Query          |         1 |         371 | create database binlogTest                                         |
  37. | mysql-bin.000012 | 371 | Gtid           |         1 |         436 | SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:27' |
  38. | mysql-bin.000012 | 436 | Query          |         1 |         545 | use `binlogtest`; create table t1(id int)                          |
  39. +------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
  40. 6 rows in set (0.00 sec)

详细内容查看

  1. mysqlbinlog --base64-output=decode-rows -vv /data/binlog/mysql-bin.0000013

gtid

介绍

https://dev.mysql.com/doc/refman/5.7/en/replication-gtids.html

GTID(Global Transaction ID)是对于一个已提交事务的编号，并且是一个全局唯一的编号。GTID实际上是由UUID+TID组成的。其中UUID是一个MySQL实例的唯一标识，保存在mysql数据目录下的auto.cnf文件里。TID代表了该实例上已经提交的事务数量，并且随着事务提交单调递增。
例如:

  1. a469ece3-10cd-11ed-98cd-525400d5deea:31
  1. [root->mcode-server->/data/mysql/3306/data]# cat auto.cnf 
  2. [auto]
  3. server-uuid=a469ece3-10cd-11ed-98cd-525400d5deea

开启gtid

  1. vim /etc/my.cnf
  2. gtid-mode=on
  3. enforce-gtid-consistency=on
  4. systemctl restart mysqld

查看gtid开启情况

  1. show variables like '%gtid%';
  1. (root@localhost) [(none)]> show variables like '%gtid%';
  2. +----------------------------------+-------------------------------------------+
  3. | Variable_name                    | Value                                     |
  4. +----------------------------------+-------------------------------------------+
  5. | binlog_gtid_simple_recovery      | ON                                        |
  6. | enforce_gtid_consistency         | ON                                        |
  7. | gtid_executed_compression_period | 1000                                      |
  8. | gtid_mode                        | ON                                        |
  9. | gtid_next                        | AUTOMATIC                                 |
  10. | gtid_owned                       |                                           |
  11. | gtid_purged                      | a469ece3-10cd-11ed-98cd-525400d5deea:1-31 |
  12. | session_track_gtids              | OFF                                       |
  13. +----------------------------------+-------------------------------------------+
  14. 8 rows in set (0.00 sec)

mysqlbinlog

  1. [root->mcode-server->/data/mysql/3306/binlog]# mysqlbinlog --help
  3. -?, --help                      # 显示帮助信息并退出
  4. --base64-output=name            # binlog输出语句的base64解码 分为三类：默认是值auto ,仅打印base64编码的需要的信息，如row-based 事件和事件的描述
  5.                                   信息。never 仅适用于不是row-based的事件 decode-rows   配合--verbose选项一起使用解码行事件到带注释的伪SQL语句
  6. --bind-address=name             # 绑定的IP地址
  7. --character-sets-dir=name       # 字符集文件的目录
  8. -d, --database=name             # 仅列出此数据库的条目（仅限本地日志）
  9. --rewrite-db=name               # 将行事件重写为指向，以便将其应用于新数据库
  10. -#, --debug[=#]                 # 输出debug信息，用于调试。默认值为：d:t,/tmp/mysqldump.trace
  11. --debug-check                   # 当程序退出时打印一些调试信息
  12. --debug-info                    # 当程序退出时打印调试信息和内存和CPU使用统计信息
  13. --default-auth=name             # 要使用的默认身份验证客户端插件
  14. -D, --disable-log-bin           # 禁用binlog日志，若开启--to-last-log并发送输出文件到相同的mysql server。这种方式避免无限循环。在规避数据库崩
  15.                                   溃恢复数据的时候有用。注意：需要super权限来使用此选项
  16. -F, --force-if-open             # 若binlog非正常关闭，强制开启binlog，默认是on可使用--skip-force-if-open关闭
  17. -f, --force-read                # 强制读取未知的binlog事件
  18. -H, --hexdump                   # 使用十六进制和ASCII码导出输出的信息
  19. -h, --host=name                 # 获取binlog的服务名
  20. -i, --idempotent                # 通知服务器使用幂等模式应用行事件
  21. -l, --local-load=name           # 准备LOAD DATA INFILE的本地临时文件指定目录
  22. -o, --offset=#                  # 跳过前n个条目
  23. -p, --password[=name]           # 连接到服务器的密码
  24. --plugin-dir=name               # 客户端插件的目录
  25. -P, --port=#                    # 用于连接的端口，0表示默认值。端口使用的优先级：my.cnf，$ MYSQL_TCP_PORT,/etc/services，内置默认值(3306)
  26. --protocol=name                 # 用于连接的协议(tcp, socket, pipe, memory)
  27. -R, --read-from-remote-server   # 从MySQL服务器读取binlog日志，是read-from-remote-master = BINLOG-DUMP-NON-GTIDS的别名。
  28. --read-from-remote-master=name  
  29. --raw                           # 配合参数-R一起使用,输出原始的binlog数据而不是SQL语句
  30. -r, --result-file=name          # 输出指定的文件，和--raw一起使用，此时是数据文件的前缀
  31. --secure-auth                   # 如果客户端使用旧的（4.1.1之前的）协议，则拒绝连接到服务器
  32. --server-id=#                   # 提取给定id的服务器创建的binlog条目                
  33. --server-id-bits=#              # 设置server-id中的有效位数
  34. --set-charset=name              # 添加'SET NAMES character_set' 到输出
  35. -s, --short-form                # 仅适用于常规查询，没有额外的信息和row-based事件信息。仅用于测试，不使用于生产环境。如果你想抑制
  36.                                   base64-output，考虑使用--base64-output = never代替
  37. -S, --socket=name               # 连接时使用的socket文件
  38. --ssl-mode=name                 # SSL连接模式
  39. --ssl-ca=name                   # PEM格式的CA文件
  40. --ssl-capath=name               # CA目录
  41. --ssl-cert=name                 # PEM格式的X509证书
  42. --ssl-cipher=name               # 要使用的SSL密码
  43. --ssl-key=name                  # PEM格式的X509密钥
  44. --ssl-crl=name                  # 证书吊销列表
  45. --ssl-crlpath=name              # 证书吊销列表路径
  46. --tls-version=name              # 要使用的TLS版本，允许值为：tlsv1、tlsv1.1
  47. --start-datetime=name           # binlog文件读取的起始时间点，可接受datetime和timestamp类型，格式2004-12-25 11:25:56
  48. -j, --start-position=#          # 在位置等于或大于 的第一个事件处开始读取binlog日志 *`N`*
  49. --stop-datetime=name            # binlog文件读取的结束时间点
  50. --stop-never                    # 等待来自服务器的更多数据，而不是在最后一个日志结束时停止。隐式地设置--to-last-log ，但不是在最后一个日志结
  51.                                   束时停止而是继续等待直到服务器断开连接
  52. --stop-never-slave-server-id=#  # 从服务器server_id使用--read-from-remote-server --stop-never。该选项不能和--connection-server-id一起使用
  53. --connection-server-id=#        # 从服务器server_id使用--read-from-remote-server,该选项不能和--stop-never-slave-server-id一起使用
  54. --stop-position=#               # 
  55. -t, --to-last-log               # 和-r一起使用，不会在请求的binlog结尾处停止，而是继续打印，直到mysql服务器的最后一个binlog结束。如果将输出发
  56.                                   送到同一个MySQL服务器，可能会导致无休止的循环
  57. -u, --user=name                 # 连接到服务器用户名
  58. -v, --verbose                   # 重新构建伪SQL语句的行信息输出，-v -v会增加列类型的注释信息
  59. -V, --version                   # 打印版本信息
  60. --open-files-limit=#            # 打开文件的限制，用于保留文件描述符以供此程序使用
  61. -c, --verify-binlog-checksum    # 验证binlog的事件信息
  62. --binlog-row-event-max-size=#   # 指定基于行的binlog的大小，改值必须是256的倍数
  63. --skip-gtids                    # 不要保留全局事务标识符，而是让服务器像执行新事务一样执行这些事务。
  64. --include-gtids=name            # 打印提供了全局事务标识符的事件
  65. --exclude-gtids=name            # 打印所有事件，但提供全局事务标识符的事件除外

--base64-output=name

binlog输出语句的base64解码 分为三类：

auto(默认) ,仅打印base64编码的需要的信息，如row-based 事件和事件的描述信息。

never 仅适用于不是row-based的事件

decode-rows 配合--verbose选项一起使用解码行事件到带注释的伪SQL语句

  1. mysqlbinlog --base64-output=never mysql-bin.000012
  2. mysqlbinlog --base64-output=decode-rows -v  mysql-bin.000012

-d,--database=name

仅列出此数据库的条目（仅限本地日志）

  1. mysqlbinlog -d binlogTest  mysql-bin.000012

-r,--result-file=name

将文本输出写入的文件

  1. mysqlbinlog mysql-bin.000012 -r ./result.sql

--start-datetime=name

binlog文件读取的起始时间点，可接受datetime和timestamp类型，格式2004-12-25 11:25:56

  1. mysqlbinlog --start-datetime='2022-08-19 14:10-24'  mysql-bin.000012

--stop-datetime=name

binlog文件读取的结束时间点

  1. mysqlbinlog --stop-datetime='2022-08-19 14:10-24'  mysql-bin.000012

-j,--start-position=#

在位置等于或大于 的第一个事件处开始读取binlog日志 N

  1.  mysqlbinlog --start-position=1410 mysql-bin.000012 --base64-output=decode-rows -v

--stop-position=#

在位置等于或大于 的第一个事件处停止读取binlog日志 N

  1. mysqlbinlog --start-position=1410 --stop-position=1488 mysql-bin.000012 --base64-output=decode-rows -v

-v,--verbose

重构行事件并将它们显示为注释的 SQL 语句。如果此选项被给出两次（通过传入“-vv”或“--verbose --verbose”），则输出包括用于指示列数据类型和一些元数据的注释，以及行查询日志事件（如果已配置）。

  1. mysqlbinlog mysql-bin.000012 --base64-output=decode-rows -vv

--include-gtids=name

仅显示 中列出的组 gtid_set

  1. mysqlbinlog mysql-bin.000012 --include-gtids='a469ece3-10cd-11ed-98cd-525400d5deea:1-31' --exclude-gtids='a469ece3-10cd-11ed-98cd-525400d5deea:31'

--exclude-gtids=name

不显示 中列出的任何组 gtid_set

  1. mysqlbinlog mysql-bin.000012 --include-gtids='a469ece3-10cd-11ed-98cd-525400d5deea:1-31' --exclude-gtids='a469ece3-10cd-11ed-98cd-525400d5deea:31'

--skip-gtids

不要在输出转储文件中包含binlog日志文件中的 GTID

不要保留全局事务标识符，而是让服务器像执行新事务一样执行这些事务

开启GTID后,MySQL恢复binlog时,重复GTID的事务不会再执行了,想恢复就得采用此参数

  1. mysqlbinlog --skip-gtids mysql-bin.000012 >./dump.sql
  2. mysqlbinlog --skip-gtids  --include-gtids='a469ece3-10cd-11ed-98cd-525400d5deea:1-31' --exclude-gtids='a469ece3-10cd-11ed-98cd-525400d5deea:31'  mysql-bin.000012 >./dump.sql

其他操作

自动清理日志

企业建议,至少保留两个全备周期+1的binlog

  1. (root@localhost) [(none)]> select @@expire_logs_days;
  2. +--------------------+
  3. | @@expire_logs_days |
  4. +--------------------+
  5. |                  7 |
  6. +--------------------+
  7. 1 row in set (0.00 sec)

临时生效,重启后失效

  1. (root@localhost) [(none)]> set global expire_logs_days=30;
  2. Query OK, 0 rows affected (0.00 sec)
  3. (root@localhost) [(none)]> show variables like 'expire%';
  4. +------------------+-------+
  5. | Variable_name    | Value |
  6. +------------------+-------+
  7. | expire_logs_days | 30    |
  8. +------------------+-------+
  9. 1 row in set (0.00 sec)

永久生效

  1. my.cnf
  2. expire_logs_days=15

手工清理日志

1.清理三天前的数据

  1. (root@localhost) [(none)]> purge binary logs before now() - interval 3 day;
  2. Query OK, 0 rows affected (0.00 sec)

2.清理mysql-bin.0000013之前的

  1. (root@localhost) [(none)]> purge binary logs to 'mysql-bin.000013';
  2. Query OK, 0 rows affected (0.00 sec)

注意:不要手工 rm binlog文件

3.reset master

  1. (root@localhost) [db1]> reset master;
  2. Query OK, 0 rows affected (0.01 sec)
  4. (root@localhost) [db1]> show master status;
  5. +------------------+----------+--------------+------------------+-------------------+
  6. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  7. +------------------+----------+--------------+------------------+-------------------+
  8. | mysql-bin.000001 |      154 |              |                  |                   |
  9. +------------------+----------+--------------+------------------+-------------------+
  10. 1 row in set (0.00 sec)

主从关系中，主库执行此操作，主从环境必崩

滚动日志

1.flush logs

  1. (root@localhost) [(none)]> show master status;
  2. +------------------+----------+--------------+------------------+-------------------------------------------+
  3. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
  4. +------------------+----------+--------------+------------------+-------------------------------------------+
  5. | mysql-bin.000013 |      356 |              |                  | a469ece3-10cd-11ed-98cd-525400d5deea:1-32 |
  6. +------------------+----------+--------------+------------------+-------------------------------------------+
  7. 1 row in set (0.00 sec)
  9. (root@localhost) [(none)]> flush logs;
  10. Query OK, 0 rows affected (0.02 sec)
  12. (root@localhost) [(none)]> show master status;
  13. +------------------+----------+--------------+------------------+-------------------------------------------+
  14. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
  15. +------------------+----------+--------------+------------------+-------------------------------------------+
  16. | mysql-bin.000014 |      194 |              |                  | a469ece3-10cd-11ed-98cd-525400d5deea:1-32 |
  17. +------------------+----------+--------------+------------------+-------------------------------------------+
  18. 1 row in set (0.00 sec)

2.重启mysql也会自动滚动一个新的

3.日志文件达到指定大小时也会滚动一个新的，通过max_binlog_size参数控制，备份时,加入参数也可以自动滚动

  1. (root@localhost) [(none)]> select @@max_binlog_size;
  2. +-------------------+
  3. | @@max_binlog_size |
  4. +-------------------+
  5. |         536870912 |
  6. +-------------------+
  7. 1 row in set (0.00 sec)
  1. my.cnf
  2. max_binlog_size=512M

优化

  1. binlog_cache_size = 2M #为每个session 分配的内存，在事务过程中用来存储binlog日志的缓存, 提高记录bin-log的效率。没有什么大事务，dml也不是很频繁的情况下可以设置小一点，如果事务大而且多，dml操作也频繁，则可以适当的调大一点。前者建议是--1M，后者建议是：即 2--4M
  2. max_binlog_cache_size = 8M #表示的是binlog 能够使用的最大cache 内存大小
  3. max_binlog_size= 512M #指定binlog日志文件的大小，如果当前的日志大小达到max_binlog_size，还会自动创建新的binlog日志。你不能将该变量设置为大于1GB或小于4096字节。默认值是1GB。在导入大容量的sql文件时，建议关闭sql_log_bin，否则硬盘扛不住，而且建议定期做删除。
  4. expire_logs_days = 30 #定义了mysql清除过期日志的时间。binlog日志自动删除的天数。默认值为0,表示“没有自动删除”。

binlog_cache_size = 2M

为每个session 分配的内存，在事务过程中用来存储binlog日志的缓存, 提高记录bin-log的效率。没有什么大事务，dml也不是很频繁的情况下可以设置小一点，如果事务大而且多，dml操作也频繁，则可以适当的调大一点。前者建议是--1M，后者建议是：即 2--4M

max_binlog_cache_size = 8M

表示的是binlog 能够使用的最大cache 内存大小

max_binlog_size= 1G

指定binlog日志文件的大小，如果当前的日志大小达到max_binlog_size，还会自动创建新的binlog日志。你不能将该变量设置为大于1GB或小于4096字节。默认值是1GB。在导入大容量的sql文件时，建议关闭sql_log_bin，否则硬盘扛不住，而且建议定期做删除

expire_logs_days = 30

定义了mysql清除过期日志的时间。binlog日志自动删除的天数。默认值为0,表示“没有自动删除”。

综合参数

  1. log_bin=/data/mysql/3306/binlog/mysql-bin
  2. binlog_format=row
  3. server-id=1
  4. gtid-mode=on
  5. enforce-gtid-consistency=on
  6. binlog_cache_size=2M
  7. max_binlog_cache_size=8M
  8. max_binlog_size=1G
  9. expire_logs_days=30
  10. # 双1标准
  11. sync_binlog=1

故障演练

创建了一个库 db1, 导入了表t1 ,t1表中录入了很多数据
一个开发人员,drop database db;
没有备份,日志都在.怎么恢复?
思路:找到建库语句到删库之前所有的日志,进行恢复.(开启了GTID模式)

  1. (root@localhost) [(none)]> drop database if exists db1 ;
  2. Query OK, 0 rows affected, 1 warning (0.00 sec)
  4. (root@localhost) [(none)]> create database db1 charset utf8;  
  5. Query OK, 1 row affected (0.00 sec)
  7. (root@localhost) [(none)]> use db1;
  8. Database changed
  9. (root@localhost) [db1]> create table t1(id int);
  10. Query OK, 0 rows affected (0.03 sec)
  12. (root@localhost) [db1]> insert into t1 values(1),(2),(3);
  13. Query OK, 3 rows affected (0.00 sec)
  14. Records: 3  Duplicates: 0  Warnings: 0
  16. (root@localhost) [db1]> insert into t1 values(4),(5),(6);
  17. Query OK, 3 rows affected (0.00 sec)
  18. Records: 3  Duplicates: 0  Warnings: 0
  20. (root@localhost) [db1]> commit;
  21. Query OK, 0 rows affected (0.00 sec)
  23. (root@localhost) [db1]> update t1 set id=30 where id=3;
  24. Query OK, 1 row affected (0.01 sec)
  25. Rows matched: 1  Changed: 1  Warnings: 0
  27. (root@localhost) [db1]> commit;
  28. Query OK, 0 rows affected (0.00 sec)
  30. (root@localhost) [db1]> delete from t1 where id=4;
  31. Query OK, 1 row affected (0.00 sec)
  33. (root@localhost) [db1]> commit;
  34. Query OK, 0 rows affected (0.00 sec)
  36. (root@localhost) [db1]> insert into t1 values(7),(8),(9);
  37. Query OK, 3 rows affected (0.00 sec)
  38. Records: 3  Duplicates: 0  Warnings: 0
  40. (root@localhost) [db1]> commit;
  41. Query OK, 0 rows affected (0.00 sec)
  43. (root@localhost) [db1]> drop database db1;
  44. Query OK, 1 row affected (0.01 sec)
  46. ========================
  47. (0) drop database if exists db1 ;
  48. (1) create database db1 charset utf8;     
  49. (2) use db1;
  50. (3) create table t1 (id int);
  51. (4) insert into t1 values(1),(2),(3);
  52. (5) insert into t1 values(4),(5),(6);
  53. (6) commit
  54. (7) update t1 set id=30 where id=3;
  55. (8) commit;
  56. (9) delete from t1 where id=4;
  57. (10)commit;
  58. (11)insert into t1 values(7),(8),(9);
  59. (12)commit;
  60. (13)drop database db1;
  61. ========================
  62. drop database if exists db1 ;
  63. create database db1 charset utf8; 
  64. use db1;
  65. create table t1 (id int);
  66. insert into t1 values(1),(2),(3);
  67. insert into t1 values(4),(5),(6);
  68. commit;
  69. update t1 set id=30 where id=3;
  70. commit;
  71. delete from t1 where id=4;
  72. commit;
  73. insert into t1 values(7),(8),(9);
  74. commit;
  75. drop database db1;
  76. =======
  77. 运行以上语句，模拟故障场景
  78. 需求：将数据库恢复到以下状态（提示第9步和第13步是误操作，其他都是正常操作）

查看当前binlog文件

  1. show master status;
  1. (root@localhost) [(none)]> show master status;
  2. +------------------+----------+--------------+------------------+-------------------------------------------+
  3. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
  4. +------------------+----------+--------------+------------------+-------------------------------------------+
  5. | mysql-bin.000014 |     2126 |              |                  | a469ece3-10cd-11ed-98cd-525400d5deea:1-41 |
  6. +------------------+----------+--------------+------------------+-------------------------------------------+
  7. 1 row in set (0.00 sec)

查看当前binlog events

  1. show binlog events in 'mysql-bin.000014 '
  1. (root@localhost) [(none)]> show binlog events in 'mysql-bin.000014';
  2. +------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
  3. | Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                                               |
  4. +------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
  5. | mysql-bin.000014 |    4 | Format_desc    |         1 |         123 | Server ver: 5.7.36-log, Binlog ver: 4                              |
  6. | mysql-bin.000014 |  123 | Previous_gtids |         1 |         194 | a469ece3-10cd-11ed-98cd-525400d5deea:1-32                          |
  7. | mysql-bin.000014 |  194 | Gtid           |         1 |         259 | SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:33' |
  8. | mysql-bin.000014 |  259 | Query          |         1 |         352 | drop database if exists db1                                        |
  9. | mysql-bin.000014 |  352 | Gtid           |         1 |         417 | SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:34' |
  10. | mysql-bin.000014 |  417 | Query          |         1 |         521 | create database db1 charset utf8                                   |
  11. | mysql-bin.000014 |  521 | Gtid           |         1 |         586 | SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:35' |
  12. | mysql-bin.000014 |  586 | Query          |         1 |         681 | use `db1`; create table t1(id int)                                 |
  13. | mysql-bin.000014 |  681 | Gtid           |         1 |         746 | SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:36' |
  14. | mysql-bin.000014 |  746 | Query          |         1 |         817 | BEGIN                                                              |
  15. | mysql-bin.000014 |  817 | Table_map      |         1 |         861 | table_id: 109 (db1.t1)                                             |
  16. | mysql-bin.000014 |  861 | Write_rows     |         1 |         911 | table_id: 109 flags: STMT_END_F                                    |
  17. | mysql-bin.000014 |  911 | Xid            |         1 |         942 | COMMIT /* xid=66 */                                                |
  18. | mysql-bin.000014 |  942 | Gtid           |         1 |        1007 | SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:37' |
  19. | mysql-bin.000014 | 1007 | Query          |         1 |        1078 | BEGIN                                                              |
  20. | mysql-bin.000014 | 1078 | Table_map      |         1 |        1122 | table_id: 109 (db1.t1)                                             |
  21. | mysql-bin.000014 | 1122 | Write_rows     |         1 |        1172 | table_id: 109 flags: STMT_END_F                                    |
  22. | mysql-bin.000014 | 1172 | Xid            |         1 |        1203 | COMMIT /* xid=67 */                                                |
  23. | mysql-bin.000014 | 1203 | Gtid           |         1 |        1268 | SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:38' |
  24. | mysql-bin.000014 | 1268 | Query          |         1 |        1339 | BEGIN                                                              |
  25. | mysql-bin.000014 | 1339 | Table_map      |         1 |        1383 | table_id: 109 (db1.t1)                                             |
  26. | mysql-bin.000014 | 1383 | Update_rows    |         1 |        1429 | table_id: 109 flags: STMT_END_F                                    |
  27. | mysql-bin.000014 | 1429 | Xid            |         1 |        1460 | COMMIT /* xid=69 */                                                |
  28. | mysql-bin.000014 | 1460 | Gtid           |         1 |        1525 | SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:39' |
  29. | mysql-bin.000014 | 1525 | Query          |         1 |        1596 | BEGIN                                                              |
  30. | mysql-bin.000014 | 1596 | Table_map      |         1 |        1640 | table_id: 109 (db1.t1)                                             |
  31. | mysql-bin.000014 | 1640 | Delete_rows    |         1 |        1680 | table_id: 109 flags: STMT_END_F                                    |
  32. | mysql-bin.000014 | 1680 | Xid            |         1 |        1711 | COMMIT /* xid=71 */                                                |
  33. | mysql-bin.000014 | 1711 | Gtid           |         1 |        1776 | SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:40' |
  34. | mysql-bin.000014 | 1776 | Query          |         1 |        1847 | BEGIN                                                              |
  35. | mysql-bin.000014 | 1847 | Table_map      |         1 |        1891 | table_id: 109 (db1.t1)                                             |
  36. | mysql-bin.000014 | 1891 | Write_rows     |         1 |        1941 | table_id: 109 flags: STMT_END_F                                    |
  37. | mysql-bin.000014 | 1941 | Xid            |         1 |        1972 | COMMIT /* xid=73 */                                                |
  38. | mysql-bin.000014 | 1972 | Gtid           |         1 |        2037 | SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:41' |
  39. | mysql-bin.000014 | 2037 | Query          |         1 |        2126 | drop database db1                                                  |
  40. +------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
  41. 35 rows in set (0.00 sec)

查看binlog详细内容

  1. mysqlbinlog --base64-output=decode-rows -vv --start-datetime='2022-08-22 09:00' --stop-datetime='2022-08-22 12:00'  -d db1  mysql-bin.000014  >/tmp/result.sql
  1. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
  2. /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
  3. DELIMITER /*!*/;
  4. # at 4
  5. #220819 15:57:58 server id 1  end_log_pos 123 CRC32 0xc117f37e 	Start: binlog v 4, server v 5.7.36-log created 220819 15:57:58
  6. # Warning: this binlog is either in use or was not closed properly.
  7. # at 194
  8. #220822 11:13:31 server id 1  end_log_pos 259 CRC32 0xcb8bcdca 	GTID	last_committed=0	sequence_number=1	rbr_only=no
  9. SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:33'/*!*/;
  10. # at 259
  11. #220822 11:13:31 server id 1  end_log_pos 352 CRC32 0xd19a4e55 	Query	thread_id=24	exec_time=0	error_code=0
  12. SET TIMESTAMP=1661138011/*!*/;
  13. SET @@session.pseudo_thread_id=24/*!*/;
  14. SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
  15. SET @@session.sql_mode=1436549152/*!*/;
  16. SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
  17. /*!\C utf8mb4 *//*!*/;
  18. SET @@session.character_set_client=224,@@session.collation_connection=224,@@session.collation_server=224/*!*/;
  19. SET @@session.lc_time_names=0/*!*/;
  20. SET @@session.collation_database=DEFAULT/*!*/;
  21. drop database if exists db1
  22. /*!*/;
  23. # at 352
  24. #220822 11:13:47 server id 1  end_log_pos 417 CRC32 0xaf5fe918 	GTID	last_committed=1	sequence_number=2	rbr_only=no
  25. SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:34'/*!*/;
  26. # at 417
  27. #220822 11:13:47 server id 1  end_log_pos 521 CRC32 0x91172a9a 	Query	thread_id=24	exec_time=0	error_code=0
  28. SET TIMESTAMP=1661138027/*!*/;
  29. create database db1 charset utf8
  30. /*!*/;
  31. # at 521
  32. #220822 11:14:07 server id 1  end_log_pos 586 CRC32 0xd73afa06 	GTID	last_committed=2	sequence_number=3	rbr_only=no
  33. SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:35'/*!*/;
  34. # at 586
  35. #220822 11:14:07 server id 1  end_log_pos 681 CRC32 0x39129c09 	Query	thread_id=24	exec_time=0	error_code=0
  36. use `db1`/*!*/;
  37. SET TIMESTAMP=1661138047/*!*/;
  38. create table t1(id int)
  39. /*!*/;
  40. # at 681
  41. #220822 11:14:40 server id 1  end_log_pos 746 CRC32 0x7c89b1d8 	GTID	last_committed=3	sequence_number=4	rbr_only=yes
  42. /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
  43. SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:36'/*!*/;
  44. # at 746
  45. #220822 11:14:40 server id 1  end_log_pos 817 CRC32 0x6f5f3852 	Query	thread_id=24	exec_time=0	error_code=0
  46. SET TIMESTAMP=1661138080/*!*/;
  47. BEGIN
  48. /*!*/;
  49. # at 817
  50. #220822 11:14:40 server id 1  end_log_pos 861 CRC32 0xd0d79c66 	Table_map: `db1`.`t1` mapped to number 109
  51. # at 861
  52. #220822 11:14:40 server id 1  end_log_pos 911 CRC32 0xcb1cbbdf 	Write_rows: table id 109 flags: STMT_END_F
  53. ### INSERT INTO `db1`.`t1`
  54. ### SET
  55. ###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
  56. ### INSERT INTO `db1`.`t1`
  57. ### SET
  58. ###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
  59. ### INSERT INTO `db1`.`t1`
  60. ### SET
  61. ###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
  62. # at 911
  63. #220822 11:14:40 server id 1  end_log_pos 942 CRC32 0x2ca37ce3 	Xid = 66
  64. COMMIT/*!*/;
  65. # at 942
  66. #220822 11:15:00 server id 1  end_log_pos 1007 CRC32 0xd70a75bd 	GTID	last_committed=4	sequence_number=5	rbr_only=yes
  67. /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
  68. SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:37'/*!*/;
  69. # at 1007
  70. #220822 11:15:00 server id 1  end_log_pos 1078 CRC32 0x29ea80b8 	Query	thread_id=24	exec_time=0	error_code=0
  71. SET TIMESTAMP=1661138100/*!*/;
  72. BEGIN
  73. /*!*/;
  74. # at 1078
  75. #220822 11:15:00 server id 1  end_log_pos 1122 CRC32 0xdeb8ca92 	Table_map: `db1`.`t1` mapped to number 109
  76. # at 1122
  77. #220822 11:15:00 server id 1  end_log_pos 1172 CRC32 0xffa6a19b 	Write_rows: table id 109 flags: STMT_END_F
  78. ### INSERT INTO `db1`.`t1`
  79. ### SET
  80. ###   @1=4 /* INT meta=0 nullable=1 is_null=0 */
  81. ### INSERT INTO `db1`.`t1`
  82. ### SET
  83. ###   @1=5 /* INT meta=0 nullable=1 is_null=0 */
  84. ### INSERT INTO `db1`.`t1`
  85. ### SET
  86. ###   @1=6 /* INT meta=0 nullable=1 is_null=0 */
  87. # at 1172
  88. #220822 11:15:00 server id 1  end_log_pos 1203 CRC32 0x7307f602 	Xid = 67
  89. COMMIT/*!*/;
  90. # at 1203
  91. #220822 11:17:21 server id 1  end_log_pos 1268 CRC32 0xa8d53a45 	GTID	last_committed=5	sequence_number=6	rbr_only=yes
  92. /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
  93. SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:38'/*!*/;
  94. # at 1268
  95. #220822 11:17:21 server id 1  end_log_pos 1339 CRC32 0x9156e38d 	Query	thread_id=24	exec_time=0	error_code=0
  96. SET TIMESTAMP=1661138241/*!*/;
  97. BEGIN
  98. /*!*/;
  99. # at 1339
  100. #220822 11:17:21 server id 1  end_log_pos 1383 CRC32 0x6efec1fd 	Table_map: `db1`.`t1` mapped to number 109
  101. # at 1383
  102. #220822 11:17:21 server id 1  end_log_pos 1429 CRC32 0xa15c3e47 	Update_rows: table id 109 flags: STMT_END_F
  103. ### UPDATE `db1`.`t1`
  104. ### WHERE
  105. ###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
  106. ### SET
  107. ###   @1=30 /* INT meta=0 nullable=1 is_null=0 */
  108. # at 1429
  109. #220822 11:17:21 server id 1  end_log_pos 1460 CRC32 0x3beb5cd2 	Xid = 69
  110. COMMIT/*!*/;
  111. # at 1460
  112. #220822 11:17:46 server id 1  end_log_pos 1525 CRC32 0xd33b8151 	GTID	last_committed=6	sequence_number=7	rbr_only=yes
  113. /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
  114. SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:39'/*!*/;
  115. # at 1525
  116. #220822 11:17:46 server id 1  end_log_pos 1596 CRC32 0x52fe1a6e 	Query	thread_id=24	exec_time=0	error_code=0
  117. SET TIMESTAMP=1661138266/*!*/;
  118. BEGIN
  119. /*!*/;
  120. # at 1596
  121. #220822 11:17:46 server id 1  end_log_pos 1640 CRC32 0x28d68e80 	Table_map: `db1`.`t1` mapped to number 109
  122. # at 1640
  123. #220822 11:17:46 server id 1  end_log_pos 1680 CRC32 0x75166daf 	Delete_rows: table id 109 flags: STMT_END_F
  124. ### DELETE FROM `db1`.`t1`
  125. ### WHERE
  126. ###   @1=4 /* INT meta=0 nullable=1 is_null=0 */
  127. # at 1680
  128. #220822 11:17:46 server id 1  end_log_pos 1711 CRC32 0xc2ec60fc 	Xid = 71
  129. COMMIT/*!*/;
  130. # at 1711
  131. #220822 11:18:11 server id 1  end_log_pos 1776 CRC32 0xaac0949c 	GTID	last_committed=7	sequence_number=8	rbr_only=yes
  132. /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
  133. SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:40'/*!*/;
  134. # at 1776
  135. #220822 11:18:11 server id 1  end_log_pos 1847 CRC32 0xb6ba1980 	Query	thread_id=24	exec_time=0	error_code=0
  136. SET TIMESTAMP=1661138291/*!*/;
  137. BEGIN
  138. /*!*/;
  139. # at 1847
  140. #220822 11:18:11 server id 1  end_log_pos 1891 CRC32 0x5955c15b 	Table_map: `db1`.`t1` mapped to number 109
  141. # at 1891
  142. #220822 11:18:11 server id 1  end_log_pos 1941 CRC32 0xabb399cc 	Write_rows: table id 109 flags: STMT_END_F
  143. ### INSERT INTO `db1`.`t1`
  144. ### SET
  145. ###   @1=7 /* INT meta=0 nullable=1 is_null=0 */
  146. ### INSERT INTO `db1`.`t1`
  147. ### SET
  148. ###   @1=8 /* INT meta=0 nullable=1 is_null=0 */
  149. ### INSERT INTO `db1`.`t1`
  150. ### SET
  151. ###   @1=9 /* INT meta=0 nullable=1 is_null=0 */
  152. # at 1941
  153. #220822 11:18:11 server id 1  end_log_pos 1972 CRC32 0x339d37ab 	Xid = 73
  154. COMMIT/*!*/;
  155. # at 1972
  156. #220822 11:18:32 server id 1  end_log_pos 2037 CRC32 0xb9a33d03 	GTID	last_committed=8	sequence_number=9	rbr_only=no
  157. SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:41'/*!*/;
  158. # at 2037
  159. #220822 11:18:32 server id 1  end_log_pos 2126 CRC32 0xa621a531 	Query	thread_id=24	exec_time=0	error_code=0
  160. SET TIMESTAMP=1661138312/*!*/;
  161. drop database db1
  162. /*!*/;
  163. SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
  164. DELIMITER ;
  165. # End of log file
  166. /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
  167. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
  1. # at 1460
  2. #220822 11:17:46 server id 1  end_log_pos 1525 CRC32 0xd33b8151 	GTID	last_committed=6	sequence_number=7	rbr_only=yes
  3. /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
  4. SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:39'/*!*/;
  5. # at 1525
  6. #220822 11:17:46 server id 1  end_log_pos 1596 CRC32 0x52fe1a6e 	Query	thread_id=24	exec_time=0	error_code=0
  7. SET TIMESTAMP=1661138266/*!*/;
  8. BEGIN
  9. /*!*/;
  10. # at 1596
  11. #220822 11:17:46 server id 1  end_log_pos 1640 CRC32 0x28d68e80 	Table_map: `db1`.`t1` mapped to number 109
  12. # at 1640
  13. #220822 11:17:46 server id 1  end_log_pos 1680 CRC32 0x75166daf 	Delete_rows: table id 109 flags: STMT_END_F
  14. ### DELETE FROM `db1`.`t1`
  15. ### WHERE
  16. ###   @1=4 /* INT meta=0 nullable=1 is_null=0 */
  17. # at 1680
  18. #220822 11:17:46 server id 1  end_log_pos 1711 CRC32 0xc2ec60fc 	Xid = 71
  19. COMMIT/*!*/;
  1. COMMIT/*!*/;
  2. # at 1972
  3. #220822 11:18:32 server id 1  end_log_pos 2037 CRC32 0xb9a33d03 	GTID	last_committed=8	sequence_number=9	rbr_only=no
  4. SET @@SESSION.GTID_NEXT= 'a469ece3-10cd-11ed-98cd-525400d5deea:41'/*!*/;
  5. # at 2037
  6. #220822 11:18:32 server id 1  end_log_pos 2126 CRC32 0xa621a531 	Query	thread_id=24	exec_time=0	error_code=0
  7. SET TIMESTAMP=1661138312/*!*/;
  8. drop database db1
  9. /*!*/;

基于position号恢复

  1. mysqlbinlog  --start-position=259 --stop-position=1460  --skip-gtids  -d db1   /data/mysql/3306/binlog/mysql-bin.000014 >/tmp/t1.sql
  2. mysqlbinlog  --start-position=1711 --stop-position=1972 --skip-gtids  -d db1   /data/mysql/3306/binlog/mysql-bin.000014 >/tmp/t2.sql
  4. mysql>
  5. set sql_log_bin=0;
  6. source /tmp/t1.sql;
  7. source /tmp/t2.sql;
  8. set sql_log_bin=1;

基于gtid恢复

  1. mysqlbinlog --include-gtids='a469ece3-10cd-11ed-98cd-525400d5deea:33-38' --skip-gtids -d db1 /data/mysql/3306/binlog/mysql-bin.000014 >/tmp/t1.sql
  2. mysqlbinlog --include-gtids='a469ece3-10cd-11ed-98cd-525400d5deea:40' --skip-gtids -d db1 /data/mysql/3306/binlog/mysql-bin.000014 >/tmp/t2.sql
  4. mysql>
  5. set sql_log_bin=0;
  6. source /tmp/t1.sql;
  7. source /tmp/t2.sql;
  8. set sql_log_bin=1;

原文链接：https://www.cnblogs.com/vic-tory/p/17798229.html

 友情链接：直通硅谷 　点职佳 　北美留学生论坛

MySQL热门文章

  • 大项目之网上书城（九）——订单Demo
  • CentOS7下MySQL服务启动失败原因及解决方法
  • MySQL从删库到跑路_高级（六）——索引
  • 55 道MySQL基础题
  • MySQL与PHP的基础与应用专题之自连接
  • 树形结构的菜单表设计与查询
  • MYSQL语句
  • 《高性能MySQL（第3版）》PDF免费下载
  • 《MySQL必知必会》知识汇总二
  • 【必知必会的MySQL知识】④DCL语言

    • MySQL推荐文章

  • 如何让SQL Server像MySQL一样拥有慢查询日志（Slow Query Log慢日志）
  • MySQL中为什么要使用索引合并(Index Merge)？
  • MySQL派生表合并优化的原理和实现
  • 使用EF 连接 数据库 SQLserver、MySql 实现 CodeFirst
  • 升级到 MySQL 8.4，MySQL 启动报错：io_setup() failed with EAGAIN
  • TiKV 源码分析之 PointGet
  • 异构数据源同步之数据同步 → DataX 使用细节
  • 一文搞懂 MySQL 日志
  • 浅析MySQL 8.0直方图原理
  • 分布式任务调度内的 MySQL 分页查询优化

    • 本站QQ群：前端 618073944 | Java 606181507 | Python 626812652 | C/C++ 612253063 | 微信 634508462 | 苹果 692586424 | C#/.net 182808419 | PHP 305140648 | 运维 608723728

    W3xue 的所有内容仅供测试，对任何法律问题及风险不承担任何责任。通过使用本站内容随之而来的风险与本站无关。
    关于我们  |  意见建议  |  捐助我们  |  报错有奖  |  广告合作、友情链接（目前9元/月）请联系QQ：27243702 沸活量
    皖ICP备17017327号-2　皖公网安备34020702000426号