ToB企服应用市场:ToB评测及商务社交产业平台

标题: MYSQL之binlog规复数据 [打印本页]
作者: 守听    时间: 2024-8-18 00:31
标题: MYSQL之binlog规复数据
话说谁还没删错/改错过数据是吧，一旦删错了，我们真的要跑路吗？今天我们来玩一玩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企服之家，中国第一个企服评测及商务社交产业平台。



欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/) Powered by Discuz! X3.4