\n| \u63a8\u8350\u7a0b\u5ea6<\/td>\n | \u4e0d\u63a8\u8350\u751f\u4ea7\u4f7f\u7528<\/td>\n | \u5f3a\u70c8\u63a8\u8350<\/td>\n | \u4e00\u822c\u4e0d\u63a8\u8350<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n \u7ed3\u8bba\u5f88\u660e\u786e：\u9664\u975e\u6709\u7279\u6b8a\u5386\u53f2\u517c\u5bb9\u539f\u56e0，\u5426\u5219\u751f\u4ea7\u73af\u5883\u8bf7\u9009\u62e9 ROW \u683c\u5f0f\u3002\u672c\u6587\u540e\u7eed\u7684\u89e3\u6790\u4e0e\u5b9e\u64cd\u5747\u4ee5 ROW \u683c\u5f0f\u4e3a\u57fa\u51c6\u3002<\/p>\n \u4e09\u3001Binlog \u89e3\u6790\u524d\u7684\u51c6\u5907\u5de5\u4f5c<\/h3>\n\u89e3\u6790 Binlog \u4e4b\u524d，\u9700\u8981\u5148\u786e\u8ba4\u6570\u636e\u5e93\u662f\u5426\u5df2\u7ecf\u6b63\u786e\u5f00\u542f Binlog，\u5e76\u4e86\u89e3\u76f8\u5173\u914d\u7f6e\u3002\u5f88\u591a\u521d\u7ea7\u95ee\u9898\u90fd\u6e90\u4e8e Binlog \u6ca1\u6709\u5f00\u542f\u6216\u683c\u5f0f\u4e0d\u5bf9\u3002<\/p>\n 3.1 \u67e5\u770b Binlog \u662f\u5426\u5f00\u542f<\/h4>\nSHOW VARIABLES LIKE 'log_bin';<\/p>\n \u5982\u679c\u8fd4\u56de Value = ON，\u8bf4\u660e\u5df2\u7ecf\u5f00\u542f；\u5982\u679c\u4e3a OFF，\u9700\u8981\u4fee\u6539\u914d\u7f6e\u5e76\u91cd\u542f MySQL\u3002<\/p>\n 3.2 \u6838\u5fc3\u914d\u7f6e\u53c2\u6570<\/h4>\n[mysqld]
\nserver-id = 1
\nlog_bin = \/var\/lib\/mysql\/mysql-bin
\nbinlog_format = ROW
\nbinlog_row_image = FULL
\nexpire_logs_days = 7
\nmax_binlog_size = 512M
\nsync_binlog = 1<\/p>\n \u5404\u53c2\u6570\u542b\u4e49\u5982\u4e0b：<\/p>\n \n- server-id：\u670d\u52a1\u5668\u552f\u4e00\u6807\u8bc6，\u4e3b\u4ece\u590d\u5236\u65f6\u6bcf\u53f0\u673a\u5668\u5fc5\u987b\u4e0d\u540c，\u7f3a\u5c11\u6b64\u53c2\u6570 Binlog \u4e0d\u4f1a\u88ab\u8bb0\u5f55\u3002<\/li>\n
- log_bin：\u6307\u5b9a Binlog \u6587\u4ef6\u8def\u5f84\u524d\u7f00，\u5f00\u542f\u540e\u4f1a\u751f\u6210\u5f62\u5982 mysql-bin.000001 \u7684\u6587\u4ef6\u3002<\/li>\n
- binlog_format：\u63a8\u8350\u8bbe\u7f6e\u4e3a ROW\u3002<\/li>\n
- binlog_row_image：FULL \u8bb0\u5f55\u5b8c\u6574\u7684\u524d\u540e\u955c\u50cf，\u9002\u5408\u505a\u7cbe\u786e\u6062\u590d\u548c\u5ba1\u8ba1；MINIMAL \u53ea\u8bb0\u5f55\u53d8\u66f4\u5217，\u53ef\u7701\u7a7a\u95f4\u4f46\u4f1a\u4e22\u5931\u90e8\u5206\u5ba1\u8ba1\u4fe1\u606f\u3002<\/li>\n
- expire_logs_days：Binlog \u81ea\u52a8\u6e05\u7406\u5929\u6570，\u751f\u4ea7\u73af\u5883\u5efa\u8bae 7～14 \u5929，\u914d\u5408\u5907\u4efd\u7b56\u7565\u4f7f\u7528\u3002<\/li>\n
- max_binlog_size：\u5355\u4e2a Binlog \u6587\u4ef6\u5927\u5c0f\u4e0a\u9650，\u8d85\u8fc7\u540e\u81ea\u52a8\u6eda\u52a8\u751f\u6210\u65b0\u6587\u4ef6\u3002<\/li>\n
- sync_binlog：\u63a7\u5236 Binlog \u843d\u76d8\u9891\u7387，1 \u8868\u793a\u6bcf\u6b21\u63d0\u4ea4\u90fd\u540c\u6b65\u843d\u76d8，\u6700\u5b89\u5168\u4f46\u6027\u80fd\u7565\u4f4e，\u8d44\u91d1\u4ea4\u6613\u7b49\u6838\u5fc3\u7cfb\u7edf\u5efa\u8bae\u8bbe\u7f6e\u4e3a 1\u3002<\/li>\n<\/ul>\n
3.3 \u67e5\u770b\u5df2\u5b58\u5728\u7684 Binlog \u6587\u4ef6<\/h4>\nSHOW BINARY LOGS;<\/p>\n \u8fd4\u56de\u7ed3\u679c\u4f1a\u5217\u51fa\u6240\u6709 Binlog \u6587\u4ef6\u540d\u53ca\u5927\u5c0f\u3002\u6b64\u5916，SHOW MASTER STATUS \u53ef\u4ee5\u67e5\u770b\u5f53\u524d\u6b63\u5728\u5199\u5165\u7684 Binlog \u6587\u4ef6\u53ca\u504f\u79fb\u4f4d\u7f6e\u3002<\/p>\n SHOW MASTER STATUS;<\/p>\n \u8fd9\u4e09\u6761\u547d\u4ee4\u662f\u65e5\u5e38\u6392\u67e5\u65f6\u4f7f\u7528\u9891\u7387\u6700\u9ad8\u7684\u201c\u4e09\u677f\u65a7\u201d，\u5efa\u8bae\u719f\u7ec3\u638c\u63e1\u3002<\/p>\n \u56db\u3001\u6838\u5fc3\u5de5\u5177 mysqlbinlog \u5b8c\u5168\u6307\u5357<\/h3>\nmysqlbinlog \u662f MySQL \u5b98\u65b9\u63d0\u4f9b\u7684 Binlog \u89e3\u6790\u5de5\u5177，\u901a\u5e38\u968f MySQL \u5ba2\u6237\u7aef\u4e00\u8d77\u53d1\u5e03\u3002\u5b83\u662f\u547d\u4ee4\u884c\u573a\u666f\u4e0b\u6700\u76f4\u63a5\u3001\u6700\u53ef\u9760\u7684\u89e3\u6790\u624b\u6bb5\u3002<\/p>\n 4.1 \u6700\u57fa\u672c\u7684\u89e3\u6790\u65b9\u5f0f<\/h4>\nmysqlbinlog \/var\/lib\/mysql\/mysql-bin.000001<\/p>\n \u8fd9\u6761\u547d\u4ee4\u4f1a\u4ee5\u4f2a SQL \u7684\u5f62\u5f0f\u6253\u5370 Binlog \u5185\u5bb9\u3002\u5bf9\u4e8e STATEMENT \u683c\u5f0f，\u4f60\u80fd\u76f4\u63a5\u770b\u5230\u539f\u59cb\u7684 SQL \u8bed\u53e5；\u4f46\u5bf9\u4e8e ROW \u683c\u5f0f，\u9ed8\u8ba4\u8f93\u51fa\u4e2d\u884c\u4e8b\u4ef6\u53ea\u662f\u4e00\u4e32 base64 \u7f16\u7801\u7684\u4e8c\u8fdb\u5236\u5185\u5bb9，\u51e0\u4e4e\u65e0\u6cd5\u76f4\u63a5\u9605\u8bfb，\u5fc5\u987b\u52a0\u4e0a\u89e3\u7801\u53c2\u6570\u3002<\/p>\n 4.2 \u89e3\u6790 ROW \u683c\u5f0f\u4e8b\u4ef6<\/h4>\nmysqlbinlog –base64-output=DECODE-ROWS -v \/var\/lib\/mysql\/mysql-bin.000001<\/p>\n \u5176\u4e2d：<\/p>\n \n- –base64-output=DECODE-ROWS：\u628a ROW \u4e8b\u4ef6\u7684 base64 \u5185\u5bb9\u89e3\u7801\u663e\u793a\u3002<\/li>\n
- -v（verbose）：\u8f93\u51fa\u884c\u6570\u636e\u65f6\u6a21\u62df SQL \u8bed\u53e5，\u4ee5\u6ce8\u91ca\u5f62\u5f0f\u5c55\u793a\u53d8\u66f4\u7684\u524d\u540e\u503c，\u4fbf\u4e8e\u9605\u8bfb\u3002<\/li>\n
- -vv：\u66f4\u8be6\u7ec6\u5730\u663e\u793a\u5217\u540d\u3001\u7c7b\u578b\u4ee5\u53ca\u65e7\u503c\/\u65b0\u503c\u7684\u5bf9\u7167\u3002<\/li>\n<\/ul>\n
\u4f8b\u5982，\u4e00\u6761 UPDATE \u5728 -vv \u6a21\u5f0f\u4e0b\u4f1a\u663e\u793a\u7c7b\u4f3c\u5982\u4e0b\u5185\u5bb9：<\/p>\n ### UPDATE `test`.`users`
\n### WHERE
\n### @1=100
\n### @2='tom'
\n### @3=25
\n### SET
\n### @1=100
\n### @2='tom'
\n### @3=26<\/p>\n \u8fd9\u91cc @N \u8868\u793a\u8be5\u8868\u7684\u7b2c N \u5217，WHERE \u90e8\u5206\u4e3a\u65e7\u503c，SET \u90e8\u5206\u4e3a\u65b0\u503c\u3002\u8fd9\u79cd\u8f93\u51fa\u5bf9\u4e8e\u6570\u636e\u5ba1\u8ba1\u548c\u6062\u590d\u975e\u5e38\u76f4\u89c2\u3002<\/p>\n 4.3 \u6309\u65f6\u95f4\u8303\u56f4\u8fc7\u6ee4<\/h4>\n\u5904\u7406\u8bef\u5220\u6062\u590d\u65f6，\u901a\u5e38\u53ea\u9700\u8981\u89e3\u6790\u67d0\u4e2a\u65f6\u95f4\u6bb5\u7684\u4e8b\u4ef6\u3002\u53ef\u4ee5\u4f7f\u7528\u8d77\u59cb\u65f6\u95f4\u548c\u7ed3\u675f\u65f6\u95f4\u8fc7\u6ee4：<\/p>\n mysqlbinlog –start-datetime="2026-08-30 10:00:00" \\\\
\n –stop-datetime="2026-08-30 11:00:00" \\\\
\n –base64-output=DECODE-ROWS -v \\\\
\n \/var\/lib\/mysql\/mysql-bin.000001<\/p>\n 4.4 \u6309\u4f4d\u7f6e\u8fc7\u6ee4<\/h4>\n\u5982\u679c\u5df2\u7ecf\u901a\u8fc7 SHOW MASTER STATUS \u6216\u4e8b\u4ef6\u5217\u8868\u786e\u5b9a\u4e86\u8d77\u6b62\u504f\u79fb\u91cf，\u53ef\u4ee5\u7528 –start-position \u548c –stop-position \u7cbe\u786e\u5b9a\u4f4d：<\/p>\n mysqlbinlog –start-position=1234 –stop-position=5678 \\\\
\n –base64-output=DECODE-ROWS -v \\\\
\n \/var\/lib\/mysql\/mysql-bin.000001<\/p>\n 4.5 \u6309\u6570\u636e\u5e93\u548c\u8868\u8fc7\u6ee4<\/h4>\nmysqlbinlog –database=test \\\\
\n –tables=users,orders \\\\
\n –base64-output=DECODE-ROWS -v \\\\
\n \/var\/lib\/mysql\/mysql-bin.000001<\/p>\n \u6ce8\u610f：–database \u53ea\u80fd\u6839\u636e\u9ed8\u8ba4\u5e93\u8fc7\u6ee4，\u5bf9\u4e8e\u8de8\u5e93\u64cd\u4f5c\u7528\u9014\u6709\u9650；–tables \u5728\u8f83\u65b0\u7248\u672c\u4e2d\u652f\u6301\u6307\u5b9a\u8868\u540d，\u80fd\u8fdb\u4e00\u6b65\u7f29\u5c0f\u8303\u56f4\u3002<\/p>\n 4.6 \u8fdc\u7a0b\u89e3\u6790<\/h4>\nmysqlbinlog \u4e5f\u652f\u6301\u8fde\u63a5\u8fdc\u7a0b MySQL \u5b9e\u4f8b\u76f4\u63a5\u8bfb\u53d6：<\/p>\n mysqlbinlog –read-from-remote-server \\\\
\n –host=192.168.1.100 –port=3306 \\\\
\n –user=root –password –raw \\\\
\n mysql-bin.000001<\/p>\n \u914d\u5408 –raw \u53c2\u6570\u53ef\u4ee5\u76f4\u63a5\u628a\u8fdc\u7a0b Binlog \u62c9\u53d6\u4e3a\u672c\u5730\u6587\u4ef6，\u4fbf\u4e8e\u79bb\u7ebf\u5206\u6790\u3002\u4e0d\u8fc7\u751f\u4ea7\u73af\u5883\u901a\u5e38\u4e0d\u5efa\u8bae\u76f4\u8fde\u751f\u4ea7\u5e93\u8bfb\u53d6 Binlog，\u5efa\u8bae\u5148\u628a\u6587\u4ef6\u62f7\u8d1d\u5230\u5206\u6790\u673a\u518d\u89e3\u6790\u3002<\/p>\n 4.7 \u5c06\u89e3\u6790\u7ed3\u679c\u5e94\u7528\u5230\u6570\u636e\u5e93<\/h4>\n\u89e3\u6790\u540e\u5982\u679c\u5e0c\u671b\u628a\u4e8b\u4ef6\u91cd\u653e\u5230\u76ee\u6807\u5e93，\u53ef\u4ee5\u76f4\u63a5\u7ba1\u9053\u7ed9 mysql \u5ba2\u6237\u7aef：<\/p>\n mysqlbinlog –base64-output=DECODE-ROWS -v \\\\
\n \/var\/lib\/mysql\/mysql-bin.000001 | \\\\
\n mysql -h \u76ee\u6807\u4e3b\u673a -u \u7528\u6237\u540d -p \u76ee\u6807\u5e93<\/p>\n \u8fd9\u79cd\u65b9\u5f0f\u5e38\u7528\u4e8e\u6570\u636e\u6062\u590d\u4e2d\u7684\u201c\u6b63\u5411\u91cd\u653e\u201d\u573a\u666f\u3002\u4f46\u9700\u8981\u6ce8\u610f\u8fc7\u6ee4\u6389\u8bef\u64cd\u4f5c\u672c\u8eab\u7684\u4e8b\u4ef6，\u5426\u5219\u4f1a\u628a\u9519\u8bef\u64cd\u4f5c\u91cd\u65b0\u6267\u884c\u4e00\u904d\u3002<\/p>\n \u4e94\u30015 \u79cd\u5178\u578b\u4e1a\u52a1\u573a\u666f\u4e0e\u5b9e\u64cd\u6b65\u9aa4<\/h3>\n\u672c\u7ae0\u662f\u5168\u6587\u7684\u6838\u5fc3，\u9010\u4e00\u62c6\u89e3 5 \u79cd\u6700\u5e38\u89c1\u7684 Binlog \u89e3\u6790\u5e94\u7528\u573a\u666f，\u5e76\u7ed9\u51fa\u53ef\u843d\u5730\u7684\u5b9e\u64cd\u6b65\u9aa4\u3002\u6240\u6709\u64cd\u4f5c\u5747\u4ee5\u542f\u7528 ROW \u683c\u5f0f\u7684 MySQL 8.0 \u4e3a\u6f14\u793a\u73af\u5883\u3002<\/p>\n \u573a\u666f\u4e00：\u8bef\u5220\u9664\u6570\u636e\u540e\u7684\u7cbe\u786e\u6062\u590d<\/h4>\n\u8fd9\u662f DBA \u548c\u5f00\u53d1\u6700\u5e38\u9047\u5230\u7684\u7d27\u6025\u4e8b\u6545\u3002\u5047\u8bbe\u67d0\u4f4d\u540c\u4e8b\u5728 10:30 \u5de6\u53f3\u8bef\u6267\u884c\u4e86 DELETE FROM orders WHERE status = 'draft'，\u5220\u9664\u4e86\u5927\u91cf\u4e0d\u5e94\u8be5\u5220\u9664\u7684\u8349\u7a3f\u8ba2\u5355，\u9700\u8981\u5c3d\u5feb\u6062\u590d\u3002<\/p>\n \u7b2c\u4e00\u6b65：\u786e\u8ba4\u8bef\u64cd\u4f5c\u65f6\u95f4\u70b9\u4e0e Binlog \u8303\u56f4\u3002\u5148\u67e5\u770b\u5f53\u524d\u6b63\u5728\u5199\u5165\u7684 Binlog \u6587\u4ef6：<\/p>\n SHOW MASTER STATUS;<\/p>\n \u5047\u8bbe\u7ed3\u679c\u4e3a mysql-bin.000008\u3002\u5982\u679c\u8bef\u64cd\u4f5c\u53d1\u751f\u5728\u66f4\u65e9\u7684\u6587\u4ef6，\u9700\u8981\u901a\u8fc7 SHOW BINARY LOGS \u627e\u5230\u8986\u76d6\u8be5\u65f6\u95f4\u6bb5\u7684\u6587\u4ef6\u3002<\/p>\n \u7b2c\u4e8c\u6b65：\u89e3\u6790\u8be5\u65f6\u95f4\u6bb5\u7684 ROW \u4e8b\u4ef6\u3002<\/p>\n mysqlbinlog –start-datetime="2026-08-30 10:25:00" \\\\
\n –stop-datetime="2026-08-30 10:35:00" \\\\
\n –database=shop –tables=orders \\\\
\n –base64-output=DECODE-ROWS -vv \\\\
\n \/var\/lib\/mysql\/mysql-bin.000008 > delete_events.sql<\/p>\n \u7b2c\u4e09\u6b65：\u4ece\u8f93\u51fa\u4e2d\u63d0\u53d6\u88ab\u5220\u9664\u884c\u7684\u65e7\u503c\u3002ROW \u683c\u5f0f\u4e0b，DELETE \u4e8b\u4ef6\u4f1a\u4ee5 ### DELETE FROM \u548c ### WHERE \u7684\u5f62\u5f0f\u5c55\u793a\u88ab\u5220\u9664\u884c\u7684\u5b8c\u6574\u5b57\u6bb5\u503c\u3002\u4f8b\u5982：<\/p>\n ### DELETE FROM `shop`.`orders`
\n### WHERE
\n### @1=20260830001
\n### @2='draft'
\n### @3=88.50
\n### @4='2026-08-29 18:20:00'<\/p>\n \u7b2c\u56db\u6b65：\u5c06\u65e7\u503c\u8f6c\u6362\u4e3a INSERT \u8bed\u53e5\u3002\u53ef\u4ee5\u624b\u5de5\u6574\u7406，\u4e5f\u53ef\u4ee5\u7528\u811a\u672c\u81ea\u52a8\u751f\u6210\u3002\u5bf9\u4e8e\u6279\u91cf\u573a\u666f，\u63a8\u8350\u7528 awk \u6216 python \u811a\u672c\u89e3\u6790\u6ce8\u91ca\u884c\u5e76\u62fc\u88c5 INSERT，\u6700\u540e\u5bfc\u5165\u6570\u636e\u5e93：<\/p>\n mysql -h \u6062\u590d\u76ee\u6807\u5e93 -u root -p shop < restore_insert.sql<\/p>\n \u5173\u952e\u8981\u70b9：<\/p>\n \n- \u6062\u590d\u524d\u5148\u786e\u8ba4\u76ee\u6807\u5e93\u6570\u636e\u72b6\u6001，\u907f\u514d\u91cd\u590d\u63d2\u5165\u5bfc\u81f4\u4e3b\u952e\u51b2\u7a81，\u5fc5\u8981\u65f6\u4f7f\u7528 INSERT IGNORE \u6216\u5148\u5907\u4efd\u5f53\u524d\u6570\u636e\u3002<\/li>\n
- \u5982\u679c ROW \u4e8b\u4ef6\u65f6\u95f4\u8de8\u5ea6\u8f83\u957f，\u5efa\u8bae\u6309\u4f4d\u7f6e（position）\u800c\u4e0d\u662f\u65f6\u95f4\u8fc7\u6ee4，\u56e0\u4e3a\u65f6\u95f4\u7cbe\u5ea6\u4e3a\u79d2，\u53ef\u80fd\u9057\u6f0f\u540c\u4e00\u79d2\u5185\u7684\u5176\u4ed6\u4e8b\u4ef6\u3002<\/li>\n
- \u751f\u4ea7\u73af\u5883\u5e94\u4f18\u5148\u57fa\u4e8e\u5168\u91cf\u5907\u4efd + Binlog \u589e\u91cf\u505a Point-in-Time Recovery（PITR），\u800c\u4e0d\u662f\u5355\u9760\u624b\u5de5\u62fc SQL\u3002<\/li>\n<\/ul>\n
\u573a\u666f\u4e8c：\u4e3b\u4ece\u590d\u5236\u5ef6\u8fdf\u4e0e\u9519\u8bef\u6392\u67e5<\/h4>\n\u4e3b\u4ece\u590d\u5236\u51fa\u73b0\u5ef6\u8fdf\u6216\u4e2d\u65ad\u65f6，\u901a\u8fc7\u89e3\u6790 Binlog \u53ef\u4ee5\u5feb\u901f\u5b9a\u4f4d\u201c\u5361\u4f4f\u7684\u4e8b\u4ef6\u201d\u4ee5\u53ca\u5bfc\u81f4\u95ee\u9898\u7684 SQL\u3002\u5178\u578b\u75c7\u72b6\u662f\u4ece\u5e93\u7684 Seconds_Behind_Master \u6301\u7eed\u589e\u5927，\u6216\u8005 SQL \u7ebf\u7a0b\u62a5\u9519\u505c\u6b62\u3002<\/p>\n \u7b2c\u4e00\u6b65：\u67e5\u770b\u4ece\u5e93\u590d\u5236\u72b6\u6001\u3002<\/p>\n SHOW SLAVE STATUS\\\\G<\/p>\n \u91cd\u70b9\u5173\u6ce8 Slave_IO_Running\u3001Slave_SQL_Running\u3001Last_Errno\u3001Last_Error，\u4ee5\u53ca Relay_Master_Log_File \u548c Exec_Master_Log_Pos\u3002\u5982\u679c SQL \u7ebf\u7a0b\u62a5\u9519，\u9519\u8bef\u4fe1\u606f\u901a\u5e38\u4f1a\u6307\u51fa\u5177\u4f53\u539f\u56e0，\u4f8b\u5982\u4e3b\u952e\u51b2\u7a81\u3001\u5b57\u6bb5\u4e0d\u5b58\u5728\u7b49\u3002<\/p>\n \u7b2c\u4e8c\u6b65：\u6839\u636e\u9519\u8bef\u4fe1\u606f\u5b9a\u4f4d\u4e3b\u5e93\u5bf9\u5e94\u7684 Binlog \u4f4d\u7f6e\u3002\u590d\u5236\u51fa\u9519\u65f6，\u4e3b\u5e93\u5bf9\u5e94\u7684\u4e8b\u4ef6\u5c31\u4f4d\u4e8e Relay_Master_Log_File \u6307\u5b9a\u7684\u6587\u4ef6\u4e2d，\u7ed3\u5408 Exec_Master_Log_Pos \u5373\u53ef\u5b9a\u4f4d\u3002\u5047\u8bbe\u9519\u8bef\u65e5\u5fd7\u663e\u793a\u4e3b\u952e\u51b2\u7a81\u53d1\u751f\u5728 mysql-bin.000010 \u7684 position 54321 \u9644\u8fd1：<\/p>\n mysqlbinlog –start-position=54200 –stop-position=54500 \\\\
\n –base64-output=DECODE-ROWS -vv \\\\
\n \/var\/lib\/mysql\/mysql-bin.000010<\/p>\n \u7b2c\u4e09\u6b65：\u5206\u6790\u76ee\u6807\u4e8b\u4ef6\u3002\u5982\u679c\u53d1\u73b0\u662f\u4e00\u6761 INSERT \u5728\u4ece\u5e93\u6267\u884c\u65f6\u4e3b\u952e\u51b2\u7a81，\u8bf4\u660e\u4ece\u5e93\u5df2\u7ecf\u5b58\u5728\u8be5\u4e3b\u952e\u6570\u636e，\u5f88\u53ef\u80fd\u662f\u4ece\u5e93\u88ab\u4eba\u5de5\u4fee\u6539\u8fc7，\u6216\u4e4b\u524d\u672a\u5f00\u542f read_only \u5bfc\u81f4\u8bef\u5199\u5165\u3002<\/p>\n \u7b2c\u56db\u6b65：\u9009\u62e9\u5904\u7406\u7b56\u7565\u3002\u5e38\u89c1\u505a\u6cd5\u5305\u62ec：<\/p>\n \n- \u5982\u679c\u4e3b\u5e93\u6570\u636e\u6b63\u786e\u4e14\u4ece\u5e93\u591a\u51fa\u7684\u6570\u636e\u662f\u810f\u6570\u636e，\u53ef\u5728\u4ece\u5e93\u5220\u9664\u51b2\u7a81\u884c\u540e，\u8ba9\u5b83\u7ee7\u7eed\u6267\u884c\u51fa\u9519\u7684\u4e8b\u4ef6\u3002<\/li>\n
- \u5982\u679c\u9700\u8981\u8df3\u8fc7\u9519\u8bef，\u53ef\u4ee5\u4f7f\u7528 SET GLOBAL sql_slave_skip_counter = 1; \u8df3\u8fc7\u5f53\u524d\u4e8b\u52a1，\u4f46\u8df3\u8fc7\u540e\u53ef\u80fd\u9020\u6210\u6570\u636e\u4e0d\u4e00\u81f4，\u5fc5\u987b\u8c28\u614e\u8bc4\u4f30\u3002<\/li>\n
- \u66f4\u7a33\u59a5\u7684\u65b9\u6cd5\u662f\u505c\u6b62\u590d\u5236，\u628a\u4ece\u5e93\u6062\u590d\u5230\u4e3b\u5e93\u7684\u4e00\u81f4\u5feb\u7167，\u91cd\u65b0\u5f00\u542f\u590d\u5236\u3002<\/li>\n<\/ul>\n
\u5173\u952e\u8981\u70b9：\u6392\u67e5\u590d\u5236\u5ef6\u8fdf\u65f6，\u9664\u4e86\u770b Binlog \u5185\u5bb9，\u8fd8\u8981\u5173\u6ce8\u4e3b\u5e93\u5199\u5165\u538b\u529b\u3001\u4ece\u5e93\u786c\u4ef6\u6027\u80fd\u3001\u7f51\u7edc\u5e26\u5bbd\u4ee5\u53ca\u5927\u4e8b\u52a1\u7684\u5f71\u54cd\u3002\u4e00\u4e2a\u957f\u65f6\u95f4\u672a\u63d0\u4ea4\u7684\u5927\u4e8b\u52a1\u5373\u4f7f\u53ea\u5305\u542b\u4e00\u6761 SQL，\u4e5f\u4f1a\u5728\u63d0\u4ea4\u65f6\u628a\u5927\u91cf ROW \u4e8b\u4ef6\u5199\u5165 Binlog，\u5bfc\u81f4\u4ece\u5e93\u91cd\u653e\u65f6\u95f4\u53d8\u957f\u3002<\/p>\n \u573a\u666f\u4e09：\u6570\u636e\u53d8\u66f4\u5ba1\u8ba1\u4e0e\u5408\u89c4\u8ffd\u8e2a<\/h4>\n\u5728\u91d1\u878d\u3001\u652f\u4ed8\u7b49\u5f3a\u76d1\u7ba1\u7cfb\u7edf\u4e2d，\u9700\u8981\u56de\u7b54\u201c\u67d0\u6761\u8bb0\u5f55\u5728\u4ec0\u4e48\u65f6\u95f4\u88ab\u8c01\u6539\u6210\u4e86\u4ec0\u4e48\u503c\u201d\u3002ROW \u683c\u5f0f\u7684 Binlog \u53ef\u4ee5\u5929\u7136\u652f\u6301\u8fd9\u79cd\u5ba1\u8ba1\u9700\u6c42，\u56e0\u4e3a\u5b83\u8bb0\u5f55\u4e86\u6bcf\u884c\u6570\u636e\u53d8\u66f4\u7684\u524d\u540e\u955c\u50cf\u3002<\/p>\n \u7b2c\u4e00\u6b65：\u786e\u5b9a\u8981\u5ba1\u8ba1\u7684\u8868\u548c\u65f6\u95f4\u8303\u56f4\u3002\u4f8b\u5982\u8981\u5ba1\u8ba1 account.balance \u8868\u5728 8 \u6708 30 \u65e5\u5168\u5929\u7684\u6240\u6709\u53d8\u66f4\u3002<\/p>\n \u7b2c\u4e8c\u6b65：\u89e3\u6790\u5e76\u8f93\u51fa\u4e3a\u7ed3\u6784\u5316\u6570\u636e\u3002\u867d\u7136 mysqlbinlog \u7684 verbose \u8f93\u51fa\u53ef\u8bfb，\u4f46\u5e76\u4e0d\u9002\u5408\u81ea\u52a8\u5ba1\u8ba1\u3002\u63a8\u8350\u4f7f\u7528\u652f\u6301\u7ed3\u6784\u5316\u7684\u89e3\u6790\u5e93\u6216\u4e2d\u95f4\u4ef6，\u6bd4\u5982 Canal\u3001Maxwell\u3001Debezium，\u6216\u8005 Java \u7684 mysql-binlog-connector-java \u5e93\u3002\u4e0b\u9762\u662f\u4f7f\u7528 mysqlbinlog \u5feb\u901f\u5bfc\u51fa\u4ee5\u4f9b\u811a\u672c\u5206\u6790\u7684\u547d\u4ee4：<\/p>\n mysqlbinlog –start-datetime="2026-08-30 00:00:00" \\\\
\n –stop-datetime="2026-08-30 23:59:59" \\\\
\n –database=account –tables=balance \\\\
\n –base64-output=DECODE-ROWS -vv \\\\
\n \/var\/lib\/mysql\/mysql-bin.000011 > audit_balance.log<\/p>\n \u7b2c\u4e09\u6b65：\u7f16\u5199\u89e3\u6790\u811a\u672c\u63d0\u53d6\u5b57\u6bb5\u3002\u4ee5 Python \u4e3a\u4f8b，\u53ef\u4ee5\u9010\u884c\u626b\u63cf ### @N=\u503c \u8fd9\u6837\u7684\u6ce8\u91ca\u884c，\u7ed3\u5408\u8868\u7ed3\u6784\u5143\u6570\u636e\u8fd8\u539f\u5b57\u6bb5\u540d：<\/p>\n import re
\ndef parse_events(log_file):
\nevent = None
\nwith open(log_file, encoding="utf-8") as f:
\nfor line in f:
\nif line.startswith("### UPDATE") or line.startswith("### DELETE") or line.startswith("### INSERT"):
\nevent = {"type": line.split()[1], "rows": []}
\nelif line.startswith("###") and "@" in line and event:
\nm = re.findall(r"@(\\\\d+)=(.+)", line)
\nif m:
\nevent["rows"].append({int(idx): val for idx, val in m})
\nreturn event
\nevents = parse_events("audit_balance.log")
\nprint(events)<\/p>\n \u7b2c\u56db\u6b65：\u5173\u8054\u64cd\u4f5c\u8005\u4fe1\u606f\u3002Binlog \u672c\u8eab\u4e0d\u8bb0\u5f55\u201c\u54ea\u4e2a\u7528\u6237\u6267\u884c\u4e86\u64cd\u4f5c\u201d，\u53ea\u8bb0\u5f55\u4f1a\u8bdd\u7684 thread_id\u3002\u8981\u5b8c\u6574\u5ba1\u8ba1，\u9700\u8981\u540c\u65f6\u5f00\u542f\u5ba1\u8ba1\u63d2\u4ef6（\u5982 Percona Audit Plugin \u6216 MySQL Enterprise Audit），\u6216\u8005\u4ece\u5e94\u7528\u5c42\u65e5\u5fd7\u4e0e\u4f1a\u8bdd\u4fe1\u606f\u4e2d\u5173\u8054 thread_id \u4e0e\u7528\u6237\u8eab\u4efd\u3002\u751f\u4ea7\u73af\u5883\u7684\u5408\u89c4\u5ba1\u8ba1\u901a\u5e38\u662f\u201cBinlog \u53d8\u66f4\u8f68\u8ff9 + \u5ba1\u8ba1\u63d2\u4ef6\u8eab\u4efd\u4fe1\u606f\u201d\u7684\u7ec4\u5408\u65b9\u6848\u3002<\/p>\n \u5173\u952e\u8981\u70b9：\u5ba1\u8ba1\u9700\u6c42\u5bf9 binlog_row_image=FULL \u662f\u521a\u9700，\u5982\u679c\u4e4b\u524d\u914d\u7f6e\u6210 MINIMAL，\u5c06\u65e0\u6cd5\u8fd8\u539f\u672a\u53d8\u66f4\u5b57\u6bb5\u7684\u5b8c\u6574\u503c，\u5ba1\u8ba1\u4ef7\u503c\u5927\u6253\u6298\u6263\u3002<\/p>\n \u573a\u666f\u56db：\u5c06 MySQL \u589e\u91cf\u540c\u6b65\u5230 Kafka \/ Elasticsearch<\/h4>\n\u6570\u636e\u4e2d\u53f0\u7ecf\u5e38\u9700\u8981\u628a MySQL \u7684\u5728\u7ebf\u53d8\u66f4\u5b9e\u65f6\u540c\u6b65\u5230 Kafka\u3001Elasticsearch\u3001\u6570\u4ed3\u6216\u7f13\u5b58\u7cfb\u7edf\u3002\u81ea\u7814\u4e00\u4e2a\u7a33\u5b9a\u3001\u4f4e\u5ef6\u8fdf\u7684\u540c\u6b65\u7ba1\u9053\u6210\u672c\u5f88\u9ad8，\u56e0\u6b64\u4e1a\u754c\u666e\u904d\u91c7\u7528\u57fa\u4e8e Binlog \u89e3\u6790\u7684\u4e2d\u95f4\u4ef6\u3002\u4e0b\u9762\u662f\u4e09\u79cd\u4e3b\u6d41\u65b9\u6848\u53ca\u5176\u5b9e\u64cd\u8981\u70b9\u3002<\/p>\n \u65b9\u6848 A：Canal\u3002\u963f\u91cc\u5f00\u6e90\u7684 MySQL Binlog \u589e\u91cf\u8ba2\u9605\u4e0e\u6d88\u8d39\u7ec4\u4ef6，\u6a21\u62df MySQL Slave \u7684\u4ea4\u4e92\u534f\u8bae，\u5411\u4e3b\u5e93\u8bf7\u6c42 Binlog \u5e76\u89e3\u6790，\u5c06\u53d8\u66f4\u63a8\u5230\u4e0b\u6e38\u3002Canal \u90e8\u7f72\u65f6\u9700\u8981\u5728 MySQL \u4e0a\u5f00\u542f Binlog \u5e76\u6388\u6743\u4e00\u4e2a\u590d\u5236\u8d26\u53f7：<\/p>\n CREATE USER 'canal'@'%' IDENTIFIED BY 'canal';
\nGRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%';
\nFLUSH PRIVILEGES;<\/p>\n \u968f\u540e\u5728 Canal \u7684 instance.properties \u4e2d\u914d\u7f6e\u6e90\u5e93\u5730\u5740\u3001\u8d26\u53f7\u4ee5\u53ca\u8981\u76d1\u542c\u7684\u5e93\u8868，\u542f\u52a8\u540e\u53d8\u66f4\u4e8b\u4ef6\u4f1a\u4ee5 Canal \u534f\u8bae\u8f93\u51fa，\u5b98\u65b9\u63d0\u4f9b\u4e86 Java \u5ba2\u6237\u7aef\u793a\u4f8b，\u53ef\u4ee5\u65b9\u4fbf\u5730\u6d88\u8d39\u5e76\u5199\u5165 Kafka\u3002<\/p>\n \u65b9\u6848 B：Maxwell\u3002\u57fa\u4e8e Zendesk \u5f00\u6e90\u7684\u8f7b\u91cf\u7ea7 Binlog \u89e3\u6790\u5668，\u7279\u70b9\u662f\u8f93\u51fa JSON \u683c\u5f0f，\u5929\u7136\u9002\u914d Kafka\u3002\u5b83\u4ee5 row \u4e3a\u5355\u4f4d\u8f93\u51fa：<\/p>\n {
\n "database": "shop",
\n "table": "orders",
\n "type": "insert",
\n "ts": 1785456000,
\n "data": {"id": "20260830001", "status": "draft", "amount": 88.5}
\n}<\/p>\n Maxwell \u652f\u6301\u914d\u7f6e Kafka \u7684 bootstrap servers \u548c topic，\u5c06\u4e0d\u540c\u5e93\u8868\u7684\u53d8\u66f4\u8def\u7531\u5230\u4e0d\u540c topic，\u975e\u5e38\u9002\u5408\u4e8b\u4ef6\u9a71\u52a8\u67b6\u6784\u3002<\/p>\n \u65b9\u6848 C：Debezium\u3002\u57fa\u4e8e Kafka Connect \u7684 CDC（Change Data Capture）\u67b6\u6784，\u751f\u6001\u4e0e Kafka \u7ed3\u5408\u6700\u6df1\u3002\u5b83\u901a\u8fc7 Kafka Connect \u63d2\u4ef6\u6355\u83b7 MySQL Binlog，\u5c06\u6bcf\u6b21\u53d8\u66f4\u8f93\u51fa\u4e3a\u5305\u542b before \u548c after \u7ed3\u6784\u7684\u6d88\u606f，\u5e76\u7ef4\u62a4 offset \u4ee5\u786e\u4fdd\u6545\u969c\u6062\u590d\u540e\u4e0d\u4e22\u4e0d\u91cd\u3002<\/p>\n \u5b9e\u64cd\u6b65\u9aa4\u6458\u8981：<\/p>\n - \u786e\u8ba4 MySQL \u5f00\u542f Binlog（ROW \u683c\u5f0f），\u5e76\u521b\u5efa\u5177\u5907\u590d\u5236\u6743\u9650\u7684\u8d26\u53f7\u3002<\/li>\n
- \u90e8\u7f72\u5e76\u914d\u7f6e\u9009\u5b9a\u7684\u540c\u6b65\u7ec4\u4ef6，\u6307\u5b9a\u6e90\u5e93\u3001\u76d1\u542c\u8868\u3001\u8f93\u51fa\u76ee\u6807（Kafka\u3001ES \u7b49）\u3002<\/li>\n
- \u5148\u505a\u4e00\u6b21\u5168\u91cf\u5feb\u7167\u540c\u6b65，\u518d\u542f\u52a8\u589e\u91cf\u6d88\u8d39，\u4e8c\u8005\u8854\u63a5\u540e\u5b8c\u6210\u521d\u59cb\u4e00\u81f4\u6027\u3002<\/li>\n
- \u9a8c\u8bc1\u6d88\u606f\u7684 before\/after \u5b57\u6bb5\u4e0e\u4e1a\u52a1\u8868\u7ed3\u6784\u4e00\u81f4，\u5e76\u5904\u7406\u5b57\u6bb5\u7c7b\u578b\u6620\u5c04（\u5982 DECIMAL\u3001DATETIME\u3001TINYINT(1) \u7684 Java \u7c7b\u578b\u8f6c\u6362）\u3002<\/li>\n
- \u76d1\u63a7\u6d88\u8d39\u5ef6\u8fdf\u3001offset \u63d0\u4ea4\u548c\u5f02\u5e38\u91cd\u8bd5，\u4fdd\u8bc1\u6700\u7ec8\u4e00\u81f4\u6027\u3002<\/li>\n
\u5173\u952e\u8981\u70b9：\u8fd9\u7c7b\u540c\u6b65\u7ba1\u9053\u7684\u6838\u5fc3\u6311\u6218\u4e0d\u5728\u89e3\u6790\u672c\u8eab，\u800c\u5728\u5168\u91cf\/\u589e\u91cf\u8854\u63a5\u3001\u5927\u4e8b\u52a1\u62c6\u5206\u3001\u4e0b\u6e38\u5e42\u7b49\u5199\u5165\u548c\u6545\u969c\u6062\u590d\u3002\u8bbe\u8ba1\u65f6\u5fc5\u987b\u660e\u786e\u81f3\u5c11\u4e00\u6b21\u6295\u9012\u8bed\u4e49，\u5e76\u5728\u4e0b\u6e38\u505a\u597d\u5e42\u7b49\u53bb\u91cd\u3002<\/p>\n \u573a\u666f\u4e94：\u4f7f\u7528\u95ea\u56de\u5de5\u5177\u56de\u6eda\u8bef\u64cd\u4f5c\u7684 UPDATE \/ DELETE<\/h4>\n\u573a\u666f\u4e00\u4ecb\u7ecd\u7684\u662f\u201c\u6b63\u5411\u6062\u590d\u201d，\u5373\u6839\u636e DELETE \u4e8b\u4ef6\u7684\u65e7\u503c\u91cd\u65b0 INSERT \u56de\u6765\u3002\u95ea\u56de（Flashback）\u5219\u662f\u201c\u9006\u5411\u751f\u6210\u201d，\u5373\u6839\u636e UPDATE\/DELETE \u4e8b\u4ef6\u81ea\u52a8\u751f\u6210\u76f8\u53cd\u7684 SQL \u6765\u64a4\u9500\u64cd\u4f5c，\u6bd4\u5982\u5c06 UPDATE \u7684\u65b0\u503c\u6362\u56de\u65e7\u503c，\u5c06 DELETE \u53d8\u6210 INSERT\u3002\u8fd9\u79cd\u65b9\u6cd5\u7279\u522b\u9002\u5408 UPDATE \u8bef\u64cd\u4f5c，\u56e0\u4e3a\u6b63\u5411\u6062\u590d\u5f88\u96be\u62fc\u51fa\u5b8c\u6574 UPDATE \u6d88\u9664\u5f71\u54cd\u3002<\/p>\n \u7b2c\u4e00\u6b65：\u786e\u8ba4\u8bef\u64cd\u4f5c\u4e8b\u4ef6\u7684\u8303\u56f4\u3002\u4e0e\u573a\u666f\u4e00\u76f8\u540c，\u5148\u786e\u5b9a\u8bef\u64cd\u4f5c\u53d1\u751f\u7684\u65f6\u95f4\u70b9\u4e0e\u5bf9\u5e94\u7684 Binlog \u6587\u4ef6\u53ca position\u3002<\/p>\n \u7b2c\u4e8c\u6b65：\u4f7f\u7528\u95ea\u56de\u5de5\u5177\u751f\u6210\u56de\u6eda SQL\u3002MySQL \u5b98\u65b9\u5e76\u672a\u5185\u7f6e\u95ea\u56de\u5de5\u5177，\u4f46\u53ef\u4ee5\u4f7f\u7528\u793e\u533a\u65b9\u6848\u3002\u4f8b\u5982\u7f8e\u56e2\u5f00\u6e90\u7684 MyFlash，\u4ee5\u53ca\u57fa\u4e8e mysqlbinlog \u7684 binlog2sql（\u4e00\u4e2a\u6d41\u884c\u7684 Python \u5de5\u5177）\u3002\u4ee5 binlog2sql \u4e3a\u4f8b：<\/p>\n # \u6b63\u5411\u89e3\u6790：\u67e5\u770b\u8bef\u64cd\u4f5c\u5f71\u54cd\u7684\u884c
\npython binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'\u5bc6\u7801' \\\\
\n –start-file='mysql-bin.000012' –start-datetime='2026-08-30 11:00:00' \\\\
\n –stop-datetime='2026-08-30 11:05:00' -d shop -t orders
\n\u751f\u6210\u95ea\u56de SQL：\u52a0 -B \u53c2\u6570\u53cd\u89e3\u51fa\u56de\u6eda\u8bed\u53e5
\npython binlog2sql.py -B -h127.0.0.1 -P3306 -uroot -p'\u5bc6\u7801'
\n–start-file='mysql-bin.000012' –start-datetime='2026-08-30 11:00:00'
\n–stop-datetime='2026-08-30 11:05:00' -d shop -t orders > rollback.sql<\/p>\n \u7b2c\u4e09\u6b65：\u5ba1\u6838\u56de\u6eda SQL \u540e\u6267\u884c\u3002\u751f\u6210\u7684 rollback.sql \u4f1a\u5305\u542b\u4e0e\u8bef\u64cd\u4f5c\u76f8\u53cd\u7684\u8bed\u53e5\u3002\u4f8b\u5982\u539f\u64cd\u4f5c\u662f UPDATE orders SET amount = 100 WHERE id = 1，\u95ea\u56de\u5de5\u5177\u4f1a\u6839\u636e ROW \u4e8b\u4ef6\u7684\u65e7\u503c\u751f\u6210 UPDATE orders SET amount = 88.5 WHERE id = 1\u3002\u6267\u884c\u524d\u52a1\u5fc5\u4eba\u5de5\u5ba1\u6838：<\/p>\n mysql -h \u6062\u590d\u76ee\u6807\u5e93 -u root -p shop < rollback.sql<\/p>\n \u7b2c\u56db\u6b65：\u6821\u9a8c\u6062\u590d\u7ed3\u679c\u3002\u5bf9\u6bd4\u8bef\u64cd\u4f5c\u524d\u540e\u6570\u636e，\u786e\u8ba4\u5173\u952e\u884c\u5df2\u6062\u590d\u5230\u8bef\u64cd\u4f5c\u524d\u7684\u503c\u3002\u6ce8\u610f\u95ea\u56de\u53ea\u5bf9\u5df2\u8bb0\u5f55\u5728 Binlog \u4e2d\u7684\u53d8\u66f4\u6709\u6548，\u5982\u679c\u76ee\u6807\u884c\u5728\u8bef\u64cd\u4f5c\u4e4b\u540e\u53c8\u88ab\u5176\u4ed6\u4e8b\u52a1\u4fee\u6539\u8fc7，\u56de\u6eda\u53ef\u80fd\u8986\u76d6\u6b63\u5e38\u53d8\u66f4，\u6240\u4ee5\u5fc5\u987b\u5c3d\u5feb\u6267\u884c\u3001\u63a7\u5236\u7ade\u4e89\u3002<\/p>\n \u5173\u952e\u8981\u70b9：\u95ea\u56de\u662f\u5e94\u6025\u624b\u6bb5\u800c\u975e\u4e07\u80fd\u836f，\u5b83\u4f9d\u8d56 ROW \u683c\u5f0f\u548c binlog_row_image=FULL，\u5e76\u4e14\u8981\u6c42\u8bef\u64cd\u4f5c\u540e\u6ca1\u6709\u5176\u4ed6\u5e76\u53d1\u4e8b\u52a1\u4fee\u6539\u540c\u4e00\u6279\u884c\u3002\u5bf9\u4e8e\u6570\u636e\u91cf\u5de8\u5927\u7684\u8bef\u64cd\u4f5c，\u95ea\u56de SQL \u53ef\u80fd\u975e\u5e38\u5e9e\u5927，\u6267\u884c\u524d\u8981\u8bc4\u4f30\u5bf9\u6027\u80fd\u7684\u5f71\u54cd，\u5efa\u8bae\u5206\u6279\u63d0\u4ea4\u3002<\/p>\n \u516d\u3001\u7f16\u7a0b\u65b9\u5f0f\u89e3\u6790 Binlog<\/h3>\n\u547d\u4ee4\u884c\u5de5\u5177\u9002\u5408\u6392\u67e5\u548c\u4e00\u6b21\u6027\u7684\u6062\u590d\u4efb\u52a1，\u4f46\u5728\u5b9e\u65f6\u8ba1\u7b97\u3001\u6570\u636e\u7ba1\u9053\u7b49\u573a\u666f\u4e2d，\u9700\u8981\u4ee5\u7f16\u7a0b\u65b9\u5f0f\u6301\u7eed\u8ba2\u9605\u5e76\u89e3\u6790 Binlog\u3002Java \u751f\u6001\u4e2d，mysql-binlog-connector-java \u662f\u4f7f\u7528\u6700\u5e7f\u6cdb\u7684\u8f7b\u91cf\u7ea7\u5e93\u4e4b\u4e00，\u5b83\u5b9e\u73b0\u4e86 MySQL \u590d\u5236\u534f\u8bae，\u53ef\u4ee5\u76f4\u63a5\u50cf Slave \u4e00\u6837\u62c9\u53d6 Binlog \u6d41\u3002<\/p>\n \u7b2c\u4e00\u6b65：\u5f15\u5165\u4f9d\u8d56\u3002<\/p>\n <dependency>
\n <groupId>com.github.shyiko<\/groupId>
\n <artifactId>mysql-binlog-connector-java<\/artifactId>
\n <version>0.29.2<\/version>
\n<\/dependency><\/p>\n \u7b2c\u4e8c\u6b65：\u6838\u5fc3\u8ba2\u9605\u4ee3\u7801\u3002<\/p>\n import com.github.shyiko.mysql.binlog.BinaryLogClient;
\nimport com.github.shyiko.mysql.binlog.event.*;
\nimport com.github.shyiko.mysql.binlog.event.deserialization.EventDeserializer;
\npublic class BinlogParserDemo {
\npublic static void main(String[] args) throws Exception {
\n BinaryLogClient client = new BinaryLogClient("192.168.1.100", 3306, "canal", "canal");
\nEventDeserializer deserializer = new EventDeserializer();
\ndeserializer.setCompatibilityMode(
\n EventDeserializer.CompatibilityMode.DATE_AND_TIME_AS_LONG,
\n EventDeserializer.CompatibilityMode.CHAR_AND_BINARY_AS_BYTE_ARRAY
\n);
\nclient.setEventDeserializer(deserializer);
\nclient.registerEventListener(event -&gt; {
\nEventData data = event.getData();
\nif (data instanceof WriteRowsEventData) {
\nWriteRowsEventData writeData = (WriteRowsEventData) data;
\nSystem.out.println("INSERT into " + writeData.getTableId());
\nfor (Object[] row : writeData.getRows()) {
\nfor (Object column : row) {
\nSystem.out.print(column + "\\\\t");
\n}
\nSystem.out.println();
\n}
\n} else if (data instanceof UpdateRowsEventData) {
\nUpdateRowsEventData updateData = (UpdateRowsEventData) data;
\nSystem.out.println("UPDATE rows = " + updateData.getRows().size());
\n} else if (data instanceof DeleteRowsEventData) {
\nDeleteRowsEventData deleteData = (DeleteRowsEventData) data;
\nSystem.out.println("DELETE rows = " + deleteData.getRows().size());
\n}
\n});
\nclient.connect();
\n}
\n}<\/p>\n \u7b2c\u4e09\u6b65：\u89e3\u6790\u5b57\u6bb5\u540d\u4e0e\u8868\u7ed3\u6784\u3002\u4e8b\u4ef6\u4e2d\u7684\u884c\u6570\u636e\u9ed8\u8ba4\u4ee5 Object[] \u65b9\u5f0f\u8fd4\u56de，\u4e0b\u6807\u4e0e\u8868\u7684\u5217\u987a\u5e8f\u5bf9\u5e94\u3002\u5982\u679c\u8981\u8fd8\u539f\u5b57\u6bb5\u540d，\u9700\u8981\u67e5\u8be2 information_schema \u83b7\u53d6\u8868\u7ed3\u6784，\u6216\u8005\u4f7f\u7528 TableMapEventData \u4e2d\u7684\u8868\u4fe1\u606f\u3002\u751f\u4ea7\u73af\u5883\u4e2d\u901a\u5e38\u7ed3\u5408\u7f13\u5b58\u673a\u5236，\u907f\u514d\u6bcf\u6b21\u4e8b\u4ef6\u90fd\u67e5\u5143\u6570\u636e\u3002<\/p>\n \u7b2c\u56db\u6b65：\u5904\u7406\u4f4d\u70b9\u4fdd\u5b58\u3002\u5b9e\u65f6\u540c\u6b65\u7cfb\u7edf\u5fc5\u987b\u5728\u6d88\u8d39\u6210\u529f\u540e\u4fdd\u5b58 Binlog \u6587\u4ef6\u540d\u4e0e position（\u5373 offset），\u4ee5\u4fbf\u91cd\u542f\u540e\u65ad\u70b9\u7eed\u4f20\u3002\u53ef\u4ee5\u901a\u8fc7 Event \u7684 header \u83b7\u53d6\u5f53\u524d\u4e8b\u4ef6\u7684 position，\u5e76\u843d\u76d8\u5230\u672c\u5730\u6587\u4ef6\u6216 Kafka offset \u4e2d\u3002<\/p>\n \u5173\u952e\u8981\u70b9：\u81ea\u7814\u89e3\u6790\u5668\u9700\u8981\u5904\u7406\u7684\u7ec6\u8282\u8f83\u591a，\u5305\u62ec\u8fde\u63a5\u9274\u6743\u3001\u534a\u540c\u6b65\u590d\u5236\u3001\u5927\u4e8b\u52a1\u62c6\u89e3\u3001\u5b57\u6bb5\u7c7b\u578b\u6620\u5c04（\u5c24\u5176\u662f DATETIME\u3001DECIMAL\u3001BIT）\u3001\u4ee5\u53ca\u5f02\u5e38\u91cd\u8fde\u540e\u7684\u4f4d\u70b9\u6062\u590d\u3002\u9664\u975e\u56e2\u961f\u6709\u5f88\u5f3a\u7684\u5b9a\u5236\u9700\u6c42，\u5426\u5219\u4f18\u5148\u9009\u7528\u6210\u719f\u7684 Canal \/ Maxwell \/ Debezium \u800c\u975e\u76f4\u63a5\u88f8\u5199\u3002<\/p>\n \u4e03\u3001Binlog \u89e3\u6790\u7684\u6027\u80fd\u4f18\u5316\u4e0e\u6ce8\u610f\u4e8b\u9879<\/h3>\n\u5728\u5b9e\u9645\u751f\u4ea7\u73af\u5883\u4e2d，Binlog \u89e3\u6790\u4f1a\u9762\u4e34\u65e5\u5fd7\u91cf\u5927\u3001\u5b9e\u65f6\u6027\u8981\u6c42\u9ad8\u3001\u8d44\u6e90\u6d88\u8017\u7b49\u591a\u91cd\u538b\u529b，\u4ee5\u4e0b\u7ecf\u9a8c\u503c\u5f97\u91cd\u89c6\u3002<\/p>\n 7.1 \u63a7\u5236 Binlog \u4f53\u79ef<\/h4>\n\n- \u4f18\u5148\u4f7f\u7528 ROW \u683c\u5f0f，\u4f46\u6309\u9700\u8bbe\u7f6e binlog_row_image\u3002\u5982\u679c\u4e0d\u9700\u8981\u5b8c\u6574\u5ba1\u8ba1，\u53ef\u8bbe\u7f6e MINIMAL \u51cf\u5c11\u65e5\u5fd7\u91cf\u3002<\/li>\n
- \u8bbe\u7f6e\u5408\u7406\u7684 expire_logs_days，\u907f\u514d Binlog \u65e0\u9650\u81a8\u80c0\u8017\u5c3d\u78c1\u76d8，\u540c\u65f6\u4e0e\u5907\u4efd\u7b56\u7565\u5bf9\u9f50，\u4fdd\u8bc1\u53ef\u6062\u590d\u7a97\u53e3\u3002<\/li>\n
- \u907f\u514d\u9891\u7e41\u65e0\u8c13\u7684\u5c0f\u4e8b\u52a1，\u6279\u91cf\u64cd\u4f5c\u80fd\u5728\u4e00\u5b9a\u7a0b\u5ea6\u4e0a\u964d\u4f4e\u6bcf\u884c\u53d8\u66f4\u7684\u5e73\u5747\u65e5\u5fd7\u5f00\u9500\u3002<\/li>\n
- \u5bf9\u5927\u8868\u505a DDL \u6216\u6279\u91cf UPDATE \u65f6，\u63d0\u524d\u8bc4\u4f30 Binlog \u589e\u91cf，\u5fc5\u8981\u65f6\u9519\u5cf0\u6267\u884c\u3002<\/li>\n<\/ul>\n
7.2 \u89e3\u6790\u4fa7\u4f18\u5316<\/h4>\n\n- \u8fdc\u7a0b\u62c9\u53d6 Binlog \u4f1a\u589e\u52a0\u4e3b\u5e93\u7f51\u7edc\u548c CPU \u538b\u529b，\u5efa\u8bae\u4f7f\u7528\u72ec\u7acb\u7684 Binlog \u8ba2\u9605\u8d26\u53f7，\u5e76\u5c3d\u91cf\u5728\u4ece\u5e93\u6216\u4e13\u95e8\u7684\u89e3\u6790\u673a\u4e0a\u6d88\u8d39\u3002<\/li>\n
- \u4f7f\u7528\u6d41\u5f0f\u89e3\u6790\u800c\u975e\u4e00\u6b21\u6027\u52a0\u8f7d\u5927\u6587\u4ef6，\u964d\u4f4e\u5185\u5b58\u5cf0\u503c\u3002<\/li>\n
- \u6309 position \u800c\u975e\u65f6\u95f4\u8fc7\u6ee4，position \u662f\u5b57\u8282\u7cbe\u786e\u7684，\u65f6\u95f4\u5219\u53ef\u80fd\u56e0\u79d2\u7ea7\u7cbe\u5ea6\u5bfc\u81f4\u8fb9\u754c\u95ee\u9898\u3002<\/li>\n
- \u89e3\u6790\u7ed3\u679c\u9700\u8981\u6301\u4e45\u5316\u65f6，\u6279\u91cf\u5199\u5165\u4e0b\u6e38，\u51cf\u5c11\u5c0f IO \u6b21\u6570\u3002<\/li>\n<\/ul>\n
7.3 \u5b89\u5168\u4e0e\u89c4\u8303<\/h4>\n |