MYSQL之binlog规复数据

守听  论坛元老 | 2024-8-18 00:31:31 | 来自手机 | 显示全部楼层 | 阅读模式
打印 上一主题 下一主题
楼主

主题 2131|帖子 2131|积分 6393

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。

您需要 登录 才可以下载或查看，没有账号？立即注册

x
话说谁还没删错/改错过数据是吧，一旦删错了，我们真的要跑路吗？今天我们来玩一玩binlog，看看是怎么规复数据的。

起首我们先了解下binlog基本概念：
binlog是一个二进制格式的文件，用于记录用户对数据库更新的SQL语句信息，例如：更改数据库表和更改内容的SQL语句都会记录到binlog里，但是不会记录SELECT和SHOW这类操作。

既然他会记录sql，那我们是不是把需要规复数据的sql拿出来就好了？话不多说，进入正题。
由于我用的是容器，所以要先辈入mysql容器
  1. sudo docker exec -it 842405d4726a /bin/bash
复制代码
进入容器并进入mysql下令行客户端
  1. sudo docker exec -it 842405d4726a mysql -u root -p
复制代码
 查询是否开启binlog，如果没开启，那下面就不消玩了...
  1. mysql> SHOW VARIABLES LIKE 'log_bin';
  2. +---------------+-------+
  3. | Variable_name | Value |
  4. +---------------+-------+
  5. | log_bin       | ON    |
  6. +---------------+-------+
  7. 1 row in set (0.00 sec)
复制代码
查询binlog地点目录（/var/lib/mysql是目录，binlog是文件名前缀）
  1. mysql> SHOW VARIABLES LIKE 'log_bin_basename';
  2. +------------------+-----------------------+
  3. | Variable_name    | Value                 |
  4. +------------------+-----------------------+
  5. | log_bin_basename | /var/lib/mysql/binlog |
  6. +------------------+-----------------------+
  7. 1 row in set (0.02 sec)
复制代码
当前 MySQL 实例上所有的二进制日志文件
  1. mysql> show master logs;
  2. +---------------+-----------+-----------+
  3. | Log_name      | File_size | Encrypted |
  4. +---------------+-----------+-----------+
  5. | binlog.000011 |      6119 | No        |
  6. | binlog.000012 |       157 | No        |
  7. +---------------+-----------+-----------+
  8. 2 rows in set (0.01 sec)
复制代码
  1. Flush logs  （看场景使用，这里我只是做个记录，知道有这么一个命令）：
  2. 刷新日志，此刻开始产生一个新编号的binlog文件，后面的操作都会存到这个新的binlog文件中
复制代码
查看当前 MySQL 主服务器的二进制日志信息
  1. mysql> SHOW MASTER STATUS;
  2. +---------------+----------+--------------+------------------+-------------------+
  3. | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  4. +---------------+----------+--------------+------------------+-------------------+
  5. | binlog.000012 |      157 |              |                  |                   |
  6. +---------------+----------+--------------+------------------+-------------------+
  7. 1 row in set (0.00 sec)
复制代码
表明：
File 列表现当前使用的二进制日志文件名为 binlog.000012
Position 列表现当前二进制日志文件的位置为 157。(下面叫 位置点/pos点，规复数据的时候要用到)
由上得知，我们的binlog文件名叫binlog.000012，位置在/var/lib/mysql目录下。
我们去这个目录下去瞅一瞅去
  1. bash-4.4# cd /var/lib/mysql
  2. bash-4.4# ls
  3. '#ib_16384_0.dblwr'   auto.cnf        ca-key.pem        ib_buffer_pool   mysql.ibd        private_key.pem   server-key.pem   undo_001
  4. '#ib_16384_1.dblwr'   binlog.000011   ca.pem          ibdata1     mysql.sock        public_key.pem    shiro     undo_002
  5. '#innodb_redo'        binlog.000012   client-cert.pem   ibtmp1     performance_schema   ruoyi      
  6. '#innodb_temp'        binlog.index    client-key.pem    mysql     pl_code_builder      server-cert.pem   sys
  7. bash-4.4#
复制代码
找到了binlog.000012就好办了，接下来就是查看内里存的是什么东西了。
这里大家留意一下，由于MySQL的binlog文件是以二进制格式存储的，这意味着它们不是纯文本文件，不能使用 vi、cat 等文本编辑器和查看器直接查看其内容。为了查看二进制日志文件的内容，我们需要使用 MySQL 提供的专用工具 mysqlbinlog。
我们用mysqlbinlog来查看一下
  2. bash-4.4# mysqlbinlog /var/lib/mysql/binlog.000012
  3. bash: mysqlbinlog: command not found
复制代码
提示我们没有找到mysqlbinlog下令
由于 MySQL 容器默认只安装了 MySQL 服务器，而没有预装 MySQL 客户端工具。在这种环境下，我们可以自己安装 MySQL 客户端工具及 mysqlbinlog。
（这里我走了不少弯路，推荐大家yum快速安装）
这里我选择了用yum方式安装，我们先安装yum
  1. bash-4.4# microdnf install yum
复制代码
安装完yum，安装 MySQL 客户端工具
  1. bash-4.4# yum install -y mysql
复制代码
如果报错了先移除辩论的包，然后再实行【yum install -y mysql】
  1. bash-4.4# yum remove mysql-community-server-minimal
复制代码
验证安装：
  1. bash-4.4# mysqlbinlog --version
  2. mysqlbinlog  Ver 8.0.36 for Linux on aarch64 (Source distribution)
复制代码
然后我们就可以查看binlog了
  1. bash-4.4# mysqlbinlog /var/lib/mysql/binlog.000012
  2. bash-4.4# mysqlbinlog /var/lib/mysql/binlog.000012|grep "Update"
复制代码
  1. bash-4.4# mysqlbinlog /var/lib/mysql/binlog.000012
  2. # The proper term is pseudo_replica_mode, but we use this compatibility alias
  3. # to make the statement usable on server versions 8.0.24 and older.
  4. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
  5. /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
  6. DELIMITER /*!*/;
  7. # at 4
  8. #240719 15:36:06 server id 1  end_log_pos 126 CRC32 0xfedff2b0   Start: binlog v 4, server v 8.0.32 created 240719 15:36:06 at startup
  9. # Warning: this binlog is either in use or was not closed properly.
  10. ROLLBACK/*!*/;
  11. BINLOG '
  12. 5oeaZg8BAAAAegAAAH4AAAABAAQAOC4wLjMyAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
  13. AAAAAAAAAAAAAAAAAADmh5pmEwANAAgAAAAABAAEAAAAYgAEGggAAAAICAgCAAAACgoKKioAEjQA
  14. CigAAbDy3/4=
  15. '/*!*/;
  16. # at 126
  17. #240719 15:36:06 server id 1  end_log_pos 157 CRC32 0x82f4e7e8   Previous-GTIDs
  18. # [empty]
  19. # at 157
  20. #240720  6:08:11 server id 1  end_log_pos 236 CRC32 0x35450d0b   Anonymous_GTID  last_committed=0  sequence_number=1  rbr_only=no  original_committed_timestamp=1721455691656110  immediate_commit_timestamp=1721455691656110  transaction_length=314
  21. # original_commit_timestamp=1721455691656110 (2024-07-20 06:08:11.656110 UTC)
  22. # immediate_commit_timestamp=1721455691656110 (2024-07-20 06:08:11.656110 UTC)
  23. /*!80001 SET @@session.original_commit_timestamp=1721455691656110*//*!*/;
  24. /*!80014 SET @@session.original_server_version=80032*//*!*/;
  25. /*!80014 SET @@session.immediate_server_version=80032*//*!*/;
  26. SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
  27. # at 236
  28. #240720  6:08:11 server id 1  end_log_pos 471 CRC32 0xeadf8f66   Query  thread_id=9  exec_time=0  error_code=0  Xid = 44
  29. SET TIMESTAMP=1721455691/*!*/;
  30. SET @@session.pseudo_thread_id=9/*!*/;
  31. SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
  32. SET @@session.sql_mode=1168113696/*!*/;
  33. SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
  34. /*!\C utf8mb4 *//*!*/;
  35. SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
  36. SET @@session.lc_time_names=0/*!*/;
  37. SET @@session.collation_database=DEFAULT/*!*/;
  38. /*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
  39. /*!80016 SET @@session.default_table_encryption=0*//*!*/;
  40. /* ApplicationName=DBeaver 23.3.3 - Main */ CREATE SCHEMA `yeYingXuan`
  41. DEFAULT CHARACTER SET utf8mb4
  42. DEFAULT COLLATE utf8mb4_0900_ai_ci
  43. /*!*/;
  44. # at 471
  45. #240720  6:09:01 server id 1  end_log_pos 550 CRC32 0x4104bf16   Anonymous_GTID  last_committed=1  sequence_number=2  rbr_only=no  original_committed_timestamp=1721455741084309  immediate_commit_timestamp=1721455741084309  transaction_length=382
  46. # original_commit_timestamp=1721455741084309 (2024-07-20 06:09:01.084309 UTC)
  47. # immediate_commit_timestamp=1721455741084309 (2024-07-20 06:09:01.084309 UTC)
  48. /*!80001 SET @@session.original_commit_timestamp=1721455741084309*//*!*/;
  49. /*!80014 SET @@session.original_server_version=80032*//*!*/;
  50. /*!80014 SET @@session.immediate_server_version=80032*//*!*/;
  51. SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
  52. # at 550
  53. #240720  6:09:01 server id 1  end_log_pos 853 CRC32 0xa4e21747   Query  thread_id=9  exec_time=0  error_code=0  Xid = 55
  54. SET TIMESTAMP=1721455741/*!*/;
  55. /*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
  56. /* ApplicationName=DBeaver 23.3.3 - Main */ CREATE TABLE yeYingXuan.`user` (
  57.   id INT auto_increment NOT NULL,
  58.   CONSTRAINT user_pk PRIMARY KEY (id)
  59. )
  60. ENGINE=InnoDB
  61. DEFAULT CHARSET=utf8mb4
  62. COLLATE=utf8mb4_0900_ai_ci
  63. /*!*/;
  64. # at 853
  65. #240720  6:09:22 server id 1  end_log_pos 932 CRC32 0xd6cc2d85   Anonymous_GTID  last_committed=2  sequence_number=3  rbr_only=no  original_committed_timestamp=1721455762869115  immediate_commit_timestamp=1721455762869115  transaction_length=269
  66. # original_commit_timestamp=1721455762869115 (2024-07-20 06:09:22.869115 UTC)
  67. # immediate_commit_timestamp=1721455762869115 (2024-07-20 06:09:22.869115 UTC)
  68. /*!80001 SET @@session.original_commit_timestamp=1721455762869115*//*!*/;
  69. /*!80014 SET @@session.original_server_version=80032*//*!*/;
  70. /*!80014 SET @@session.immediate_server_version=80032*//*!*/;
  71. SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
  72. # at 932
  73. #240720  6:09:22 server id 1  end_log_pos 1122 CRC32 0x9665ccf6   Query  thread_id=9  exec_time=0  error_code=0  Xid = 66
  74. SET TIMESTAMP=1721455762/*!*/;
  75. /*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
  76. /* ApplicationName=DBeaver 23.3.3 - Main */ ALTER TABLE yeYingXuan.`user` ADD name varchar(100) NULL
  77. /*!*/;
  78. # at 1122
  79. #240720  6:09:22 server id 1  end_log_pos 1201 CRC32 0xd8c2e651   Anonymous_GTID  last_committed=3  sequence_number=4  rbr_only=no  original_committed_timestamp=1721455762877721  immediate_commit_timestamp=1721455762877721  transaction_length=259
  80. # original_commit_timestamp=1721455762877721 (2024-07-20 06:09:22.877721 UTC)
  81. # immediate_commit_timestamp=1721455762877721 (2024-07-20 06:09:22.877721 UTC)
  82. /*!80001 SET @@session.original_commit_timestamp=1721455762877721*//*!*/;
  83. /*!80014 SET @@session.original_server_version=80032*//*!*/;
  84. /*!80014 SET @@session.immediate_server_version=80032*//*!*/;
  85. SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
  86. # at 1201
  87. #240720  6:09:22 server id 1  end_log_pos 1381 CRC32 0x156c6bda   Query  thread_id=9  exec_time=0  error_code=0  Xid = 69
  88. SET TIMESTAMP=1721455762/*!*/;
  89. /*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
  90. /* ApplicationName=DBeaver 23.3.3 - Main */ ALTER TABLE yeYingXuan.`user` ADD age INT NULL
  91. /*!*/;
  92. # at 1381
  93. #240720  6:10:22 server id 1  end_log_pos 1460 CRC32 0x08d41c89   Anonymous_GTID  last_committed=4  sequence_number=5  rbr_only=yes  original_committed_timestamp=1721455822097710  immediate_commit_timestamp=1721455822097710  transaction_length=298
  94. /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
  95. # original_commit_timestamp=1721455822097710 (2024-07-20 06:10:22.097710 UTC)
  96. # immediate_commit_timestamp=1721455822097710 (2024-07-20 06:10:22.097710 UTC)
  97. /*!80001 SET @@session.original_commit_timestamp=1721455822097710*//*!*/;
  98. /*!80014 SET @@session.original_server_version=80032*//*!*/;
  99. /*!80014 SET @@session.immediate_server_version=80032*//*!*/;
  100. SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
  101. # at 1460
  102. #240720  6:10:22 server id 1  end_log_pos 1531 CRC32 0xec33d8e3   Query  thread_id=9  exec_time=0  error_code=0
  103. SET TIMESTAMP=1721455822/*!*/;
  104. BEGIN
  105. /*!*/;
  106. # at 1531
  107. #240720  6:10:22 server id 1  end_log_pos 1596 CRC32 0xff0fa400   Table_map: `yeYingXuan`.`user` mapped to number 102
  108. # has_generated_invisible_primary_key=0
  109. # at 1596
  110. #240720  6:10:22 server id 1  end_log_pos 1648 CRC32 0xcf459f50   Write_rows: table id 102 flags: STMT_END_F
  112. BINLOG '
  113. zlSbZhMBAAAAQQAAADwGAAAAAGYAAAAAAAEACnllWWluZ1h1YW4ABHVzZXIAAwMPAwKQAQYBAQAC
  114. A/z/AACkD/8=
  115. zlSbZh4BAAAANAAAAHAGAAAAAGYAAAAAAAEAAgAD/wABAAAABgDlvKDkuIkSAAAAUJ9Fzw==
  116. '/*!*/;
  117. # at 1648
  118. #240720  6:10:22 server id 1  end_log_pos 1679 CRC32 0x3443872d   Xid = 82
  119. COMMIT/*!*/;
  120. # at 1679
  121. #240720  6:10:22 server id 1  end_log_pos 1758 CRC32 0x7896626b   Anonymous_GTID  last_committed=5  sequence_number=6  rbr_only=yes  original_committed_timestamp=1721455822100724  immediate_commit_timestamp=1721455822100724  transaction_length=298
  122. /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
  123. # original_commit_timestamp=1721455822100724 (2024-07-20 06:10:22.100724 UTC)
  124. # immediate_commit_timestamp=1721455822100724 (2024-07-20 06:10:22.100724 UTC)
  125. /*!80001 SET @@session.original_commit_timestamp=1721455822100724*//*!*/;
  126. /*!80014 SET @@session.original_server_version=80032*//*!*/;
  127. /*!80014 SET @@session.immediate_server_version=80032*//*!*/;
  128. SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
  129. # at 1758
  130. #240720  6:10:22 server id 1  end_log_pos 1829 CRC32 0xb8962d85   Query  thread_id=9  exec_time=0  error_code=0
  131. SET TIMESTAMP=1721455822/*!*/;
  132. BEGIN
  133. /*!*/;
  134. # at 1829
  135. #240720  6:10:22 server id 1  end_log_pos 1894 CRC32 0xda95d477   Table_map: `yeYingXuan`.`user` mapped to number 102
  136. # has_generated_invisible_primary_key=0
  137. # at 1894
  138. #240720  6:10:22 server id 1  end_log_pos 1946 CRC32 0x32b8c70c   Write_rows: table id 102 flags: STMT_END_F
  140. BINLOG '
  141. zlSbZhMBAAAAQQAAAGYHAAAAAGYAAAAAAAEACnllWWluZ1h1YW4ABHVzZXIAAwMPAwKQAQYBAQAC
  142. A/z/AHfUldo=
  143. zlSbZh4BAAAANAAAAJoHAAAAAGYAAAAAAAEAAgAD/wACAAAABgDmnY7lm5sTAAAADMe4Mg==
  144. '/*!*/;
  145. # at 1946
  146. #240720  6:10:22 server id 1  end_log_pos 1977 CRC32 0x7cae582d   Xid = 84
  147. COMMIT/*!*/;
  148. # at 1977
  149. #240720  6:10:22 server id 1  end_log_pos 2056 CRC32 0xff6c3b51   Anonymous_GTID  last_committed=6  sequence_number=7  rbr_only=yes  original_committed_timestamp=1721455822102188  immediate_commit_timestamp=1721455822102188  transaction_length=298
  150. /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
  151. # original_commit_timestamp=1721455822102188 (2024-07-20 06:10:22.102188 UTC)
  152. # immediate_commit_timestamp=1721455822102188 (2024-07-20 06:10:22.102188 UTC)
  153. /*!80001 SET @@session.original_commit_timestamp=1721455822102188*//*!*/;
  154. /*!80014 SET @@session.original_server_version=80032*//*!*/;
  155. /*!80014 SET @@session.immediate_server_version=80032*//*!*/;
  156. SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
  157. # at 2056
  158. #240720  6:10:22 server id 1  end_log_pos 2127 CRC32 0x0872f115   Query  thread_id=9  exec_time=0  error_code=0
  159. SET TIMESTAMP=1721455822/*!*/;
  160. BEGIN
  161. /*!*/;
  162. # at 2127
  163. #240720  6:10:22 server id 1  end_log_pos 2192 CRC32 0x6c869972   Table_map: `yeYingXuan`.`user` mapped to number 102
  164. # has_generated_invisible_primary_key=0
  165. # at 2192
  166. #240720  6:10:22 server id 1  end_log_pos 2244 CRC32 0x76a38f1f   Write_rows: table id 102 flags: STMT_END_F
  168. BINLOG '
  169. zlSbZhMBAAAAQQAAAJAIAAAAAGYAAAAAAAEACnllWWluZ1h1YW4ABHVzZXIAAwMPAwKQAQYBAQAC
  170. A/z/AHKZhmw=
  171. zlSbZh4BAAAANAAAAMQIAAAAAGYAAAAAAAEAAgAD/wADAAAABgDnjovkupQUAAAAH4+jdg==
  172. '/*!*/;
  173. # at 2244
  174. #240720  6:10:22 server id 1  end_log_pos 2275 CRC32 0xecc79b63   Xid = 86
  175. COMMIT/*!*/;
  176. # at 2275
  177. #240720  6:10:22 server id 1  end_log_pos 2354 CRC32 0x12a444bc   Anonymous_GTID  last_committed=7  sequence_number=8  rbr_only=yes  original_committed_timestamp=1721455822103661  immediate_commit_timestamp=1721455822103661  transaction_length=298
  178. /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
  179. # original_commit_timestamp=1721455822103661 (2024-07-20 06:10:22.103661 UTC)
  180. # immediate_commit_timestamp=1721455822103661 (2024-07-20 06:10:22.103661 UTC)
  181. /*!80001 SET @@session.original_commit_timestamp=1721455822103661*//*!*/;
  182. /*!80014 SET @@session.original_server_version=80032*//*!*/;
  183. /*!80014 SET @@session.immediate_server_version=80032*//*!*/;
  184. SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
  185. # at 2354
  186. #240720  6:10:22 server id 1  end_log_pos 2425 CRC32 0x5d981940   Query  thread_id=9  exec_time=0  error_code=0
  187. SET TIMESTAMP=1721455822/*!*/;
  188. BEGIN
  189. /*!*/;
  190. # at 2425
  191. #240720  6:10:22 server id 1  end_log_pos 2490 CRC32 0x939ba98a   Table_map: `yeYingXuan`.`user` mapped to number 102
  192. # has_generated_invisible_primary_key=0
  193. # at 2490
  194. #240720  6:10:22 server id 1  end_log_pos 2542 CRC32 0x34da618d   Write_rows: table id 102 flags: STMT_END_F
  196. BINLOG '
  197. zlSbZhMBAAAAQQAAALoJAAAAAGYAAAAAAAEACnllWWluZ1h1YW4ABHVzZXIAAwMPAwKQAQYBAQAC
  198. A/z/AIqpm5M=
  199. zlSbZh4BAAAANAAAAO4JAAAAAGYAAAAAAAEAAgAD/wAEAAAABgDotbXlha0VAAAAjWHaNA==
  200. '/*!*/;
  201. # at 2542
  202. #240720  6:10:22 server id 1  end_log_pos 2573 CRC32 0x5d240951   Xid = 88
  203. COMMIT/*!*/;
  204. # at 2573
  205. #240720  6:10:59 server id 1  end_log_pos 2652 CRC32 0xeaa53dae   Anonymous_GTID  last_committed=8  sequence_number=9  rbr_only=yes  original_committed_timestamp=1721455859183393  immediate_commit_timestamp=1721455859183393  transaction_length=325
  206. /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
  207. # original_commit_timestamp=1721455859183393 (2024-07-20 06:10:59.183393 UTC)
  208. # immediate_commit_timestamp=1721455859183393 (2024-07-20 06:10:59.183393 UTC)
  209. /*!80001 SET @@session.original_commit_timestamp=1721455859183393*//*!*/;
  210. /*!80014 SET @@session.original_server_version=80032*//*!*/;
  211. /*!80014 SET @@session.immediate_server_version=80032*//*!*/;
  212. SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
  213. # at 2652
  214. #240720  6:10:59 server id 1  end_log_pos 2732 CRC32 0x763aed44   Query  thread_id=9  exec_time=0  error_code=0
  215. SET TIMESTAMP=1721455859/*!*/;
  216. BEGIN
  217. /*!*/;
  218. # at 2732
  219. #240720  6:10:59 server id 1  end_log_pos 2797 CRC32 0x983ab39f   Table_map: `yeYingXuan`.`user` mapped to number 102
  220. # has_generated_invisible_primary_key=0
  221. # at 2797
  222. #240720  6:10:59 server id 1  end_log_pos 2867 CRC32 0x858f3008   Update_rows: table id 102 flags: STMT_END_F
  224. BINLOG '
  225. 81SbZhMBAAAAQQAAAO0KAAAAAGYAAAAAAAEACnllWWluZ1h1YW4ABHVzZXIAAwMPAwKQAQYBAQAC
  226. A/z/AJ+zOpg=
  227. 81SbZh8BAAAARgAAADMLAAAAAGYAAAAAAAEAAgAD//8ABAAAAAYA6LW15YWtFQAAAAAEAAAABgDo
  228. tbXlha0cAAAACDCPhQ==
  229. '/*!*/;
  230. # at 2867
  231. #240720  6:10:59 server id 1  end_log_pos 2898 CRC32 0x4eb8b581   Xid = 102
  232. COMMIT/*!*/;
  233. SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
  234. DELIMITER ;
  235. # End of log file
  236. /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
  237. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
  238. bash-4.4#
复制代码
是不是看完一脸懵逼，只能大概看出在什么时间做了什么操作，而具体操作的数据却看不出来，加上verbose我们再试试。
  1. bash-4.4# mysqlbinlog --verbose /var/lib/mysql/binlog.000012
复制代码
  1. bash-4.4# mysqlbinlog --verbose /var/lib/mysql/binlog.000012
  4. # The proper term is pseudo_replica_mode, but we use this compatibility alias# to make the statement usable on server versions 8.0.24 and older./*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;# at 4#240719 15:36:06 server id 1  end_log_pos 126 CRC32 0xfedff2b0   Start: binlog v 4, server v 8.0.32 created 240719 15:36:06 at startup# Warning: this binlog is either in use or was not closed properly.ROLLBACK/*!*/;BINLOG '5oeaZg8BAAAAegAAAH4AAAABAAQAOC4wLjMyAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAADmh5pmEwANAAgAAAAABAAEAAAAYgAEGggAAAAICAgCAAAACgoKKioAEjQACigAAbDy3/4='/*!*/;# at 126#240719 15:36:06 server id 1  end_log_pos 157 CRC32 0x82f4e7e8   Previous-GTIDs# [empty]# at 157#240720  6:08:11 server id 1  end_log_pos 236 CRC32 0x35450d0b   Anonymous_GTID  last_committed=0  sequence_number=1  rbr_only=no  original_committed_timestamp=1721455691656110  immediate_commit_timestamp=1721455691656110  transaction_length=314# original_commit_timestamp=1721455691656110 (2024-07-20 06:08:11.656110 UTC)# immediate_commit_timestamp=1721455691656110 (2024-07-20 06:08:11.656110 UTC)/*!80001 SET @@session.original_commit_timestamp=1721455691656110*//*!*/;/*!80014 SET @@session.original_server_version=80032*//*!*/;/*!80014 SET @@session.immediate_server_version=80032*//*!*/;SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;# at 236#240720  6:08:11 server id 1  end_log_pos 471 CRC32 0xeadf8f66   Query  thread_id=9  exec_time=0  error_code=0  Xid = 44SET TIMESTAMP=1721455691/*!*/;SET @@session.pseudo_thread_id=9/*!*/;SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;SET @@session.sql_mode=1168113696/*!*/;SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;/*!\C utf8mb4 *//*!*/;SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;SET @@session.lc_time_names=0/*!*/;SET @@session.collation_database=DEFAULT/*!*/;/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;/*!80016 SET @@session.default_table_encryption=0*//*!*/;/* ApplicationName=DBeaver 23.3.3 - Main */ CREATE SCHEMA `yeYingXuan`DEFAULT CHARACTER SET utf8mb4DEFAULT COLLATE utf8mb4_0900_ai_ci/*!*/;# at 471#240720  6:09:01 server id 1  end_log_pos 550 CRC32 0x4104bf16   Anonymous_GTID  last_committed=1  sequence_number=2  rbr_only=no  original_committed_timestamp=1721455741084309  immediate_commit_timestamp=1721455741084309  transaction_length=382# original_commit_timestamp=1721455741084309 (2024-07-20 06:09:01.084309 UTC)# immediate_commit_timestamp=1721455741084309 (2024-07-20 06:09:01.084309 UTC)/*!80001 SET @@session.original_commit_timestamp=1721455741084309*//*!*/;/*!80014 SET @@session.original_server_version=80032*//*!*/;/*!80014 SET @@session.immediate_server_version=80032*//*!*/;SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;# at 550#240720  6:09:01 server id 1  end_log_pos 853 CRC32 0xa4e21747   Query  thread_id=9  exec_time=0  error_code=0  Xid = 55SET TIMESTAMP=1721455741/*!*/;/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;/* ApplicationName=DBeaver 23.3.3 - Main */ CREATE TABLE yeYingXuan.`user` (  id INT auto_increment NOT NULL,  CONSTRAINT user_pk PRIMARY KEY (id))ENGINE=InnoDBDEFAULT CHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci/*!*/;# at 853#240720  6:09:22 server id 1  end_log_pos 932 CRC32 0xd6cc2d85   Anonymous_GTID  last_committed=2  sequence_number=3  rbr_only=no  original_committed_timestamp=1721455762869115  immediate_commit_timestamp=1721455762869115  transaction_length=269# original_commit_timestamp=1721455762869115 (2024-07-20 06:09:22.869115 UTC)# immediate_commit_timestamp=1721455762869115 (2024-07-20 06:09:22.869115 UTC)/*!80001 SET @@session.original_commit_timestamp=1721455762869115*//*!*/;/*!80014 SET @@session.original_server_version=80032*//*!*/;/*!80014 SET @@session.immediate_server_version=80032*//*!*/;SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;# at 932#240720  6:09:22 server id 1  end_log_pos 1122 CRC32 0x9665ccf6   Query  thread_id=9  exec_time=0  error_code=0  Xid = 66SET TIMESTAMP=1721455762/*!*/;/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;/* ApplicationName=DBeaver 23.3.3 - Main */ ALTER TABLE yeYingXuan.`user` ADD name varchar(100) NULL/*!*/;# at 1122#240720  6:09:22 server id 1  end_log_pos 1201 CRC32 0xd8c2e651   Anonymous_GTID  last_committed=3  sequence_number=4  rbr_only=no  original_committed_timestamp=1721455762877721  immediate_commit_timestamp=1721455762877721  transaction_length=259# original_commit_timestamp=1721455762877721 (2024-07-20 06:09:22.877721 UTC)# immediate_commit_timestamp=1721455762877721 (2024-07-20 06:09:22.877721 UTC)/*!80001 SET @@session.original_commit_timestamp=1721455762877721*//*!*/;/*!80014 SET @@session.original_server_version=80032*//*!*/;/*!80014 SET @@session.immediate_server_version=80032*//*!*/;SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;# at 1201#240720  6:09:22 server id 1  end_log_pos 1381 CRC32 0x156c6bda   Query  thread_id=9  exec_time=0  error_code=0  Xid = 69SET TIMESTAMP=1721455762/*!*/;/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;/* ApplicationName=DBeaver 23.3.3 - Main */ ALTER TABLE yeYingXuan.`user` ADD age INT NULL/*!*/;# at 1381#240720  6:10:22 server id 1  end_log_pos 1460 CRC32 0x08d41c89   Anonymous_GTID  last_committed=4  sequence_number=5  rbr_only=yes  original_committed_timestamp=1721455822097710  immediate_commit_timestamp=1721455822097710  transaction_length=298/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;# original_commit_timestamp=1721455822097710 (2024-07-20 06:10:22.097710 UTC)# immediate_commit_timestamp=1721455822097710 (2024-07-20 06:10:22.097710 UTC)/*!80001 SET @@session.original_commit_timestamp=1721455822097710*//*!*/;/*!80014 SET @@session.original_server_version=80032*//*!*/;/*!80014 SET @@session.immediate_server_version=80032*//*!*/;SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;# at 1460#240720  6:10:22 server id 1  end_log_pos 1531 CRC32 0xec33d8e3   Query  thread_id=9  exec_time=0  error_code=0SET TIMESTAMP=1721455822/*!*/;BEGIN/*!*/;# at 1531#240720  6:10:22 server id 1  end_log_pos 1596 CRC32 0xff0fa400   Table_map: `yeYingXuan`.`user` mapped to number 102# has_generated_invisible_primary_key=0# at 1596#240720  6:10:22 server id 1  end_log_pos 1648 CRC32 0xcf459f50   Write_rows: table id 102 flags: STMT_END_FBINLOG 'zlSbZhMBAAAAQQAAADwGAAAAAGYAAAAAAAEACnllWWluZ1h1YW4ABHVzZXIAAwMPAwKQAQYBAQACA/z/AACkD/8=zlSbZh4BAAAANAAAAHAGAAAAAGYAAAAAAAEAAgAD/wABAAAABgDlvKDkuIkSAAAAUJ9Fzw=='/*!*/;### INSERT INTO `yeYingXuan`.`user`### SET###   @1=1###   @2='张三'###   @3=18# at 1648#240720  6:10:22 server id 1  end_log_pos 1679 CRC32 0x3443872d   Xid = 82COMMIT/*!*/;# at 1679#240720  6:10:22 server id 1  end_log_pos 1758 CRC32 0x7896626b   Anonymous_GTID  last_committed=5  sequence_number=6  rbr_only=yes  original_committed_timestamp=1721455822100724  immediate_commit_timestamp=1721455822100724  transaction_length=298/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;# original_commit_timestamp=1721455822100724 (2024-07-20 06:10:22.100724 UTC)# immediate_commit_timestamp=1721455822100724 (2024-07-20 06:10:22.100724 UTC)/*!80001 SET @@session.original_commit_timestamp=1721455822100724*//*!*/;/*!80014 SET @@session.original_server_version=80032*//*!*/;/*!80014 SET @@session.immediate_server_version=80032*//*!*/;SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;# at 1758#240720  6:10:22 server id 1  end_log_pos 1829 CRC32 0xb8962d85   Query  thread_id=9  exec_time=0  error_code=0SET TIMESTAMP=1721455822/*!*/;BEGIN/*!*/;# at 1829#240720  6:10:22 server id 1  end_log_pos 1894 CRC32 0xda95d477   Table_map: `yeYingXuan`.`user` mapped to number 102# has_generated_invisible_primary_key=0# at 1894#240720  6:10:22 server id 1  end_log_pos 1946 CRC32 0x32b8c70c   Write_rows: table id 102 flags: STMT_END_FBINLOG 'zlSbZhMBAAAAQQAAAGYHAAAAAGYAAAAAAAEACnllWWluZ1h1YW4ABHVzZXIAAwMPAwKQAQYBAQACA/z/AHfUldo=zlSbZh4BAAAANAAAAJoHAAAAAGYAAAAAAAEAAgAD/wACAAAABgDmnY7lm5sTAAAADMe4Mg=='/*!*/;### INSERT INTO `yeYingXuan`.`user`### SET###   @1=2###   @2='李四'###   @3=19# at 1946#240720  6:10:22 server id 1  end_log_pos 1977 CRC32 0x7cae582d   Xid = 84COMMIT/*!*/;# at 1977#240720  6:10:22 server id 1  end_log_pos 2056 CRC32 0xff6c3b51   Anonymous_GTID  last_committed=6  sequence_number=7  rbr_only=yes  original_committed_timestamp=1721455822102188  immediate_commit_timestamp=1721455822102188  transaction_length=298/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;# original_commit_timestamp=1721455822102188 (2024-07-20 06:10:22.102188 UTC)# immediate_commit_timestamp=1721455822102188 (2024-07-20 06:10:22.102188 UTC)/*!80001 SET @@session.original_commit_timestamp=1721455822102188*//*!*/;/*!80014 SET @@session.original_server_version=80032*//*!*/;/*!80014 SET @@session.immediate_server_version=80032*//*!*/;SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;# at 2056#240720  6:10:22 server id 1  end_log_pos 2127 CRC32 0x0872f115   Query  thread_id=9  exec_time=0  error_code=0SET TIMESTAMP=1721455822/*!*/;BEGIN/*!*/;# at 2127#240720  6:10:22 server id 1  end_log_pos 2192 CRC32 0x6c869972   Table_map: `yeYingXuan`.`user` mapped to number 102# has_generated_invisible_primary_key=0# at 2192#240720  6:10:22 server id 1  end_log_pos 2244 CRC32 0x76a38f1f   Write_rows: table id 102 flags: STMT_END_FBINLOG 'zlSbZhMBAAAAQQAAAJAIAAAAAGYAAAAAAAEACnllWWluZ1h1YW4ABHVzZXIAAwMPAwKQAQYBAQACA/z/AHKZhmw=zlSbZh4BAAAANAAAAMQIAAAAAGYAAAAAAAEAAgAD/wADAAAABgDnjovkupQUAAAAH4+jdg=='/*!*/;### INSERT INTO `yeYingXuan`.`user`### SET###   @1=3###   @2='王五'###   @3=20# at 2244#240720  6:10:22 server id 1  end_log_pos 2275 CRC32 0xecc79b63   Xid = 86COMMIT/*!*/;# at 2275#240720  6:10:22 server id 1  end_log_pos 2354 CRC32 0x12a444bc   Anonymous_GTID  last_committed=7  sequence_number=8  rbr_only=yes  original_committed_timestamp=1721455822103661  immediate_commit_timestamp=1721455822103661  transaction_length=298/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;# original_commit_timestamp=1721455822103661 (2024-07-20 06:10:22.103661 UTC)# immediate_commit_timestamp=1721455822103661 (2024-07-20 06:10:22.103661 UTC)/*!80001 SET @@session.original_commit_timestamp=1721455822103661*//*!*/;/*!80014 SET @@session.original_server_version=80032*//*!*/;/*!80014 SET @@session.immediate_server_version=80032*//*!*/;SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;# at 2354#240720  6:10:22 server id 1  end_log_pos 2425 CRC32 0x5d981940   Query  thread_id=9  exec_time=0  error_code=0SET TIMESTAMP=1721455822/*!*/;BEGIN/*!*/;# at 2425#240720  6:10:22 server id 1  end_log_pos 2490 CRC32 0x939ba98a   Table_map: `yeYingXuan`.`user` mapped to number 102# has_generated_invisible_primary_key=0# at 2490#240720  6:10:22 server id 1  end_log_pos 2542 CRC32 0x34da618d   Write_rows: table id 102 flags: STMT_END_FBINLOG 'zlSbZhMBAAAAQQAAALoJAAAAAGYAAAAAAAEACnllWWluZ1h1YW4ABHVzZXIAAwMPAwKQAQYBAQACA/z/AIqpm5M=zlSbZh4BAAAANAAAAO4JAAAAAGYAAAAAAAEAAgAD/wAEAAAABgDotbXlha0VAAAAjWHaNA=='/*!*/;### INSERT INTO `yeYingXuan`.`user`### SET###   @1=4###   @2='赵六'###   @3=21# at 2542#240720  6:10:22 server id 1  end_log_pos 2573 CRC32 0x5d240951   Xid = 88COMMIT/*!*/;# at 2573#240720  6:10:59 server id 1  end_log_pos 2652 CRC32 0xeaa53dae   Anonymous_GTID  last_committed=8  sequence_number=9  rbr_only=yes  original_committed_timestamp=1721455859183393  immediate_commit_timestamp=1721455859183393  transaction_length=325/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;# original_commit_timestamp=1721455859183393 (2024-07-20 06:10:59.183393 UTC)# immediate_commit_timestamp=1721455859183393 (2024-07-20 06:10:59.183393 UTC)/*!80001 SET @@session.original_commit_timestamp=1721455859183393*//*!*/;/*!80014 SET @@session.original_server_version=80032*//*!*/;/*!80014 SET @@session.immediate_server_version=80032*//*!*/;SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;# at 2652#240720  6:10:59 server id 1  end_log_pos 2732 CRC32 0x763aed44   Query  thread_id=9  exec_time=0  error_code=0SET TIMESTAMP=1721455859/*!*/;BEGIN/*!*/;# at 2732#240720  6:10:59 server id 1  end_log_pos 2797 CRC32 0x983ab39f   Table_map: `yeYingXuan`.`user` mapped to number 102# has_generated_invisible_primary_key=0# at 2797#240720  6:10:59 server id 1  end_log_pos 2867 CRC32 0x858f3008   Update_rows: table id 102 flags: STMT_END_FBINLOG '81SbZhMBAAAAQQAAAO0KAAAAAGYAAAAAAAEACnllWWluZ1h1YW4ABHVzZXIAAwMPAwKQAQYBAQACA/z/AJ+zOpg=81SbZh8BAAAARgAAADMLAAAAAGYAAAAAAAEAAgAD//8ABAAAAAYA6LW15YWtFQAAAAAEAAAABgDotbXlha0cAAAACDCPhQ=='/*!*/;### UPDATE `yeYingXuan`.`user`### WHERE###   @1=4###   @2='赵六'###   @3=21### SET###   @1=4###   @2='赵六'###   @3=28# at 2867#240720  6:10:59 server id 1  end_log_pos 2898 CRC32 0x4eb8b581   Xid = 102COMMIT/*!*/;SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;DELIMITER ;# End of log file/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;bash-4.4#
复制代码
表明：
BEGIN 为开始，以 COMMIT 或者 ROLLBACK 竣事，中间包罗实际的 SQL 操作。
大家这样看的是不是就比力清晰，可以明确的看出在谁人时间对哪条数据做了处理，而且可以看出处理前的数据与处理后的数据。

                                    数据备份与规复
备份数据库：
规复数据前把我们如今的库做个备份，这里我记录了【备份所有库】、【备份指定库】、【备份指定表】的方法，大家按实际场景使用。
备份所有库：
  1. bash-4.4# mysqldump -u root -p --all-databases > /bak/all_databases_backup.sql
复制代码
备份指定库：
  1. bash-4.4# mysqldump -u root -p yeYingXuan > /bak/databases_backup.sql
复制代码
备份指定表：
  1. bash-4.4# mysqldump -uroot -p yeYingXuan user > /bak/usre.sql
复制代码
规复备份所有库数据
  1. mysql -uroot -p < /bak/all_databases_backup.sql
复制代码
规复备份指定库数据
  1. mysql -uroot -p yeYingXuan < /bak/databases_backup.sql
复制代码
表明：yeYingXuan = 要规复的库
规复备份指定表数据
  1. mysql -uroot -p yeYingXuan</bak/usre.sql
复制代码
表明：
-u 指定登录MySQL的用户名为root。
-p：表现输入密码，有密码后面跟密码，-p后没有空格，例如：-p1234
yeYingXuan：要备份的库。
user > /bak/usre.sql ：将user表备份到文件bak目录到usre.sql文件中


binlog数据规复：
实行的sql都捞出来了，那么接下来就该最紧张的一步，怎么规复数据呢？
这里我模拟了几种场景，希望有能帮到大家。
方法一（把数据拿出来写sql进行规复）：
对某一条实行了错误的更新/删除操作：这种规复是最简单的，我们直接在binlog找到这条数据的操作记录，找到原数据的值，然后实行新增/更新操作即可。
例如：
  1. bash-4.4# mysqlbinlog --verbose /var/lib/mysql/binlog.000012
  4. | grep -A 50 '2024-07-20 06:17' |more# original_commit_timestamp=1721456269222116 (2024-07-20 06:17:49.222116 UTC)# immediate_commit_timestamp=1721456269222116 (2024-07-20 06:17:49.222116 UTC)/*!80001 SET @@session.original_commit_timestamp=1721456269222116*//*!*/;/*!80014 SET @@session.original_server_version=80032*//*!*/;/*!80014 SET @@session.immediate_server_version=80032*//*!*/;SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;# at 2977#240720  6:17:49 server id 1  end_log_pos 3048 CRC32 0xf40a16d8   Query  thread_id=12  exec_time=0  error_code=0SET TIMESTAMP=1721456269/*!*/;BEGIN/*!*/;# at 3048#240720  6:17:49 server id 1  end_log_pos 3113 CRC32 0x170c9683   Table_map: `yeYingXuan`.`user` mapped to number 102# has_generated_invisible_primary_key=0# at 3113#240720  6:17:49 server id 1  end_log_pos 3165 CRC32 0xb8e8f644   Delete_rows: table id 102 flags: STMT_END_FBINLOG 'jVabZhMBAAAAQQAAACkMAAAAAGYAAAAAAAEACnllWWluZ1h1YW4ABHVzZXIAAwMPAwKQAQYBAQACA/z/AIOWDBc=jVabZiABAAAANAAAAF0MAAAAAGYAAAAAAAEAAgAD/wADAAAABgDnjovkupQUAAAARPbouA=='/*!*/;### DELETE FROM `yeYingXuan`.`user`### WHERE###   @1=3###   @2='王五'###   @3=20# at 3165#240720  6:17:49 server id 1  end_log_pos 3196 CRC32 0x064e38f9   Xid = 139COMMIT/*!*/;SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;DELIMITER ;# End of log file/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;bash-4.4#
复制代码
可以看到删除的数据id=3，name=王五，age=20，那么我们直接新增sql规复即可。
  1. INSERT INTO `user` (id,`name`,age) VALUES (3,'王五',20)
复制代码

方法二和方法三要用的时间点和位置点/pos点，这里先给大家介绍下在哪看。

方法二（根据时间点区间规复）：
我们模拟新增一条name=小龙女的数据，然后删除再规复。

然后删除该条数据，查看binlog日志。
  1. bash-4.4# mysqlbinlog --verbose /var/lib/mysql/binlog.000012
复制代码
  1. # at 5989
  2. #240720  8:06:13 server id 1  end_log_pos 6052 CRC32 0xd6df0c13   Table_map: `yeYingXuan`.`user` mapped to number 178
  3. # has_generated_invisible_primary_key=0
  4. # at 6052
  5. #240720  8:06:13 server id 1  end_log_pos 6107 CRC32 0x76a8609f   Write_rows: table id 178 flags: STMT_END_F
  7. BINLOG '
  8. 9W+bZhMBAAAAPwAAAKQXAAAAALIAAAAAAAEACnllWWluZ1h1YW4ABHVzZXIAAwMPAwKQAQYBAQAC
  9. AeATDN/W
  10. 9W+bZh4BAAAANwAAANsXAAAAALIAAAAAAAEAAgAD/wAFAAAACQDlsI/pvpnlpbMeAAAAn2Codg==
  11. '/*!*/;
  12. ### INSERT INTO `yeYingXuan`.`user`
  13. ### SET
  14. ###   @1=5
  15. ###   @2='小龙女'
  16. ###   @3=30
  17. # at 6107
  18. #240720  8:06:13 server id 1  end_log_pos 6138 CRC32 0xb06e6629   Xid = 2486
  19. COMMIT/*!*/;
  20. # at 6138
  21. #240720  8:08:04 server id 1  end_log_pos 6217 CRC32 0xabe69750   Anonymous_GTID  last_committed=20  sequence_number=21  rbr_only=yes  original_committed_timestamp=1721462884639790  immediate_commit_timestamp=1721462884639790  transaction_length=309
  22. /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
  23. # original_commit_timestamp=1721462884639790 (2024-07-20 08:08:04.639790 UTC)
  24. # immediate_commit_timestamp=1721462884639790 (2024-07-20 08:08:04.639790 UTC)
  25. /*!80001 SET @@session.original_commit_timestamp=1721462884639790*//*!*/;
  26. /*!80014 SET @@session.original_server_version=80032*//*!*/;
  27. /*!80014 SET @@session.immediate_server_version=80032*//*!*/;
  28. SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
  29. # at 6217
  30. #240720  8:08:04 server id 1  end_log_pos 6298 CRC32 0x2f166d0e   Query  thread_id=55  exec_time=0  error_code=0
  31. SET TIMESTAMP=1721462884/*!*/;
  32. BEGIN
  33. /*!*/;
  34. # at 6298
  35. #240720  8:08:04 server id 1  end_log_pos 6361 CRC32 0xa5403440   Table_map: `yeYingXuan`.`user` mapped to number 178
  36. # has_generated_invisible_primary_key=0
  37. # at 6361
  38. #240720  8:08:04 server id 1  end_log_pos 6416 CRC32 0x4e634334   Delete_rows: table id 178 flags: STMT_END_F
  40. BINLOG '
  41. ZHCbZhMBAAAAPwAAANkYAAAAALIAAAAAAAEACnllWWluZ1h1YW4ABHVzZXIAAwMPAwKQAQYBAQAC
  42. AeBANECl
  43. ZHCbZiABAAAANwAAABAZAAAAALIAAAAAAAEAAgAD/wAFAAAACQDlsI/pvpnlpbMeAAAANENjTg==
  44. '/*!*/;
  45. ### DELETE FROM `yeYingXuan`.`user`
  46. ### WHERE
  47. ###   @1=5
  48. ###   @2='小龙女'
  49. ###   @3=30
  50. # at 6416
  51. #240720  8:08:04 server id 1  end_log_pos 6447 CRC32 0x72e5d1de   Xid = 2492
  52. COMMIT/*!*/;
  53. SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
  54. DELIMITER ;
  55. # End of log file
  56. /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
  57. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
复制代码

可以看到有一条新增的sql和一条删除的sql，新增的时间点是 240720  8:06:13，竣事的时间点是240720  8:06:13，只要我们规复的时间段包罗了这个时间段即可。接下来我们用时间点进行数据规复，
  1. bash
  2. -4.4
  3. # mysqlbinlog --start-datetime="2024-07-20 07:56:24" --stop-datetime="2024-07-20 08:07:13" /var/lib/mysql/binlog.000012 | mysql -uroot -p yeYingXuan
  4. Enter password:
  5. bash-4.4#
复制代码
表明：
--start-datetime=开始时间
--stop-datetime=竣事时间
/var/lib/mysql/binlog.000014 = 要规复到binlog文件。
-uroot =用户名为root。
-p=密码。
yeYingXuan=要规复到数据库。
查看数据已经乐成规复。


方法三：（按位置点规复）
我们模拟新作一条name=杨过的数据，然后删除后规复。

然后删除该条数据，查看binlog日志。
  1. bash-4.4# mysqlbinlog --verbose /var/lib/mysql/binlog.000012
复制代码
  1. # at 7287
  2. #240720  8:29:34 server id 1  end_log_pos 7366 CRC32 0xd9a43076   Anonymous_GTID  last_committed=24  sequence_number=25  rbr_only=yes  original_committed_timestamp=1721464174583630  immediate_commit_timestamp=1721464174583630  transaction_length=296
  3. /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
  4. # original_commit_timestamp=1721464174583630 (2024-07-20 08:29:34.583630 UTC)
  5. # immediate_commit_timestamp=1721464174583630 (2024-07-20 08:29:34.583630 UTC)
  6. /*!80001 SET @@session.original_commit_timestamp=1721464174583630*//*!*/;
  7. /*!80014 SET @@session.original_server_version=80032*//*!*/;
  8. /*!80014 SET @@session.immediate_server_version=80032*//*!*/;
  9. SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
  10. # at 7366
  11. #240720  8:29:34 server id 1  end_log_pos 7437 CRC32 0x68009279   Query  thread_id=53  exec_time=0  error_code=0
  12. SET TIMESTAMP=1721464174/*!*/;
  13. SET @@session.sql_mode=1168113696/*!*/;
  14. BEGIN
  15. /*!*/;
  16. # at 7437
  17. #240720  8:29:34 server id 1  end_log_pos 7500 CRC32 0xbfdc8e15   Table_map: `yeYingXuan`.`user` mapped to number 180
  18. # has_generated_invisible_primary_key=0
  19. # at 7500
  20. #240720  8:29:34 server id 1  end_log_pos 7552 CRC32 0x4e2f0591   Write_rows: table id 180 flags: STMT_END_F
  22. BINLOG '
  23. bnWbZhMBAAAAPwAAAEwdAAAAALQAAAAAAAEACnllWWluZ1h1YW4ABHVzZXIAAwMPAwKQAQYBAQAC
  24. AeAVjty/
  25. bnWbZh4BAAAANAAAAIAdAAAAALQAAAAAAAEAAgAD/wAGAAAABgDmnajov4cZAAAAkQUvTg==
  26. '/*!*/;
  27. ### INSERT INTO `yeYingXuan`.`user`
  28. ### SET
  29. ###   @1=6
  30. ###   @2='杨过'
  31. ###   @3=25
  32. # at 7552
  33. #240720  8:29:34 server id 1  end_log_pos 7583 CRC32 0x3d3aaba3   Xid = 2670
  34. COMMIT/*!*/;
  35. # at 7583
  36. #240720  8:30:43 server id 1  end_log_pos 7662 CRC32 0x3f31e9c6   Anonymous_GTID  last_committed=25  sequence_number=26  rbr_only=yes  original_committed_timestamp=1721464243592594  immediate_commit_timestamp=1721464243592594  transaction_length=296
  37. /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
  38. # original_commit_timestamp=1721464243592594 (2024-07-20 08:30:43.592594 UTC)
  39. # immediate_commit_timestamp=1721464243592594 (2024-07-20 08:30:43.592594 UTC)
  40. /*!80001 SET @@session.original_commit_timestamp=1721464243592594*//*!*/;
  41. /*!80014 SET @@session.original_server_version=80032*//*!*/;
  42. /*!80014 SET @@session.immediate_server_version=80032*//*!*/;
  43. SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
  44. # at 7662
  45. #240720  8:30:43 server id 1  end_log_pos 7733 CRC32 0x55267e82   Query  thread_id=53  exec_time=0  error_code=0
  46. SET TIMESTAMP=1721464243/*!*/;
  47. BEGIN
  48. /*!*/;
  49. # at 7733
  50. #240720  8:30:43 server id 1  end_log_pos 7796 CRC32 0x28dab411   Table_map: `yeYingXuan`.`user` mapped to number 180
  51. # has_generated_invisible_primary_key=0
  52. # at 7796
  53. #240720  8:30:43 server id 1  end_log_pos 7848 CRC32 0xaf6ae3b5   Delete_rows: table id 180 flags: STMT_END_F
  55. BINLOG '
  56. s3WbZhMBAAAAPwAAAHQeAAAAALQAAAAAAAEACnllWWluZ1h1YW4ABHVzZXIAAwMPAwKQAQYBAQAC
  57. AeARtNoo
  58. s3WbZiABAAAANAAAAKgeAAAAALQAAAAAAAEAAgAD/wAGAAAABgDmnajov4cZAAAAteNqrw==
  59. '/*!*/;
  60. ### DELETE FROM `yeYingXuan`.`user`
  61. ### WHERE
  62. ###   @1=6
  63. ###   @2='杨过'
  64. ###   @3=25
  65. # at 7848
  66. #240720  8:30:43 server id 1  end_log_pos 7879 CRC32 0xdcb2c099   Xid = 2675
  67. COMMIT/*!*/;
  68. SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
  69. DELIMITER ;
  70. # End of log file
  71. /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
  72. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
复制代码
可以看到新增数据的位置点是7437到7583，接下来我们用位置点规复数据
  1. bash-4.4# mysqlbinlog --start-position=7437 --stop-position=7583 /var/lib/mysql/binlog.000012 > /bak/binlog_statements.sql
  2. bash-4.4# mysql -u root -p < binlog_statements.sql
  3. Enter password:
  4. bash-4.4#
复制代码
 表明：

1、将要规复的数据添加到/bak目录到binlog_statements.sql文件中
  1. bash-4.4# mysqlbinlog --start-position=2898 --stop-position=3196 /var/lib/mysql/binlog.000012 > /bak/binlog_statements.sql
复制代码
--start-position=开始pos点
--stop-position=竣事pos点
/var/lib/mysql/binlog.000014 = 要规复到binlog文件。
/bak/binlog_statements.sql=生成到bak目录下的binlog_statements.sql文件中。
2、将生成的 SQL 文件（binlog_statements.sql）导入到 MySQL 数据库中实行，以应用这段时间范围内的变动。
  2. bash-4.4# mysql -u root -p < binlog_statements.sql
  3. Enter password:
  4. bash-4.4#
复制代码
看数据已经乐成规复。

大家可以动手试试，真遇到告急环境，以备不时之需。此外，下面留言功能已开启，如果大家有任何疑问、发起或想分享的经验，都欢迎在这里留言。

 


免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
回复

使用道具 举报

0 个回复

倒序浏览
返回列表

快速回复

高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 or 立即注册

本版积分规则

守听

论坛元老
这个人很懒什么都没写!

楼主热帖

标签云

渠道 国产数据库 集成商 AI 运维 CIO 存储 服务器
微信订阅号
微信服务号
微信客服
小程序
H5
关于我们 商务合作 网站地图
©Copyright 2022-2024 杭州祥升科技有限公司 版权所有 浙ICP备20004199号
快速回复 返回顶部 返回列表