mysqlbinlog文件解析为对应的SQL语句详解

公司数仓中对于订单表这种实事表的数据因为是通过flinkCDC同步mysql的中的业务数据，其中有数据出现数仓和业务库中的数据偏差，需要通过解析原始mysql中的binlog文件来确定数据不一致的原因在哪里。

1.SQL语句查询

 show variables like 'log_%';

查看是否开启binlog等binlog的相关信息

show binary logs;

查看对应的binlog文件

 show binlog events in 'mysql-bin.000001';

查看对应的binlog文件中所包含的事件

可以看到对于直接使用mysql中SQL语句的方式查询binlog文件并不能有效的解析出来需要的执行SQL语句的情况。

通过shell的方式解析binlog文件也是比较不能获取想要的执行SQL语句的内容，具体执行的shell语句如下：

mysqlbinlog --no-defaults --base64-output=decode-rows -vv \Users\admin\Desktop\mysql-bin.002768

解析出来的内容没有太能看到具体的执行内容，所以当时也就放弃了这种方式

### INSERT INTO psi_dossier_db.p_sku_warehouse_stock_settle_daily
### SET
###   @1=1013368 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='1000000002' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
###   @3='01' /* VARSTRING(48) meta=48 nullable=0 is_null=0 */
###   @4='00' /* VARSTRING(48) meta=48 nullable=1 is_null=0 */
###   @5='03010001' /* VARSTRING(128) meta=128 nullable=0 is_null=0 */
###   @6='2022-03-07' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
###   @7='BEGIN_DATE' /* VARSTRING(256) meta=256 nullable=0 is_null=0 */
###   @8=-10.0000 /* DECIMAL(19,4) meta=4868 nullable=1 is_null=0 */
###   @9=-39.00 /* DECIMAL(19,2) meta=4866 nullable=1 is_null=0 */
###   @10=0.0000 /* DECIMAL(19,4) meta=4868 nullable=1 is_null=0 */
###   @11='2022-03-08 01:00:14.938000' /* DATETIME(6) meta=6 nullable=1 is_null=0 */
###   @12=NULL /* VARSTRING(256) meta=256 nullable=1 is_null=1 */
###   @13=NULL /* DATETIME(6) meta=6 nullable=1 is_null=1 */
###   @14=NULL /* VARSTRING(256) meta=256 nullable=1 is_null=1 */
###   @15='2022-03-08 01:00:14.940357' /* DATETIME(6) meta=6 nullable=0 is_null=0 */
### INSERT INTO psi_dossier_db.p_sku_warehouse_stock_settle_daily
### SET
###   @1=1013369 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='1000000002' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
###   @3='01' /* VARSTRING(48) meta=48 nullable=0 is_null=0 */
###   @4='00' /* VARSTRING(48) meta=48 nullable=1 is_null=0 */
###   @5='03010001' /* VARSTRING(128) meta=128 nullable=0 is_null=0 */
###   @6='2022-03-07' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
###   @7='END_DATE' /* VARSTRING(256) meta=256 nullable=0 is_null=0 */
###   @8=-10.0000 /* DECIMAL(19,4) meta=4868 nullable=1 is_null=0 */
###   @9=-39.00 /* DECIMAL(19,2) meta=4866 nullable=1 is_null=0 */
###   @10=3.9000 /* DECIMAL(19,4) meta=4868 nullable=1 is_null=0 */
###   @11='2022-03-08 01:00:14.938000' /* DATETIME(6) meta=6 nullable=1 is_null=0 */
###   @12=NULL /* VARSTRING(256) meta=256 nullable=1 is_null=1 */
###   @13=NULL /* DATETIME(6) meta=6 nullable=1 is_null=1 */
###   @14=NULL /* VARSTRING(256) meta=256 nullable=1 is_null=1 */
###   @15='2022-03-08 01:00:14.940357' /* DATETIME(6) meta=6 nullable=0 is_null=0 */
at 34978
#220308  1:00:14 server id 1  end_log_pos 35009 CRC32 0x9a1137a8        Xid = 107115639
COMMIT/*!*/;
at 35009
#220308  1:00:14 server id 1  end_log_pos 35056 CRC32 0x8280e716        Rotate to mysql-bin.002769  pos: 4
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*/;

binlog2log是解析binlog文件的一个工具，应该有许多做DBA的使用过这个工具，在使用前需要先进行安装，可通过上面的连接进行具体的安装，安装好后可以通过下面的语句解析对应的binlog文件

python3 /data/software/test/binlog2sql-master/binlog2sql/binlog2sql.py -h 10.150.40.01 -u reay -p 4Md0BE6  -d order_db -t order_header --start-file mysql-bin.000001

然后就可以看到对应解析到的执行过的SQL语句了，从而可以分析每次执行对应的数据内容是什么

UPDATE order_db.order_header SET id=1292300000043030, tenant_id='1000000002', serial_number='', order_category='sale', order_type='normal', order_status='os.cancelled', title='《自卑与超越》   1件', product_count=1.00, customer_id='601625521185043183', customer_name='', customer_phone=NULL, customer_type=0, shop_id='500002', shop_name='零食角', payment_type=NULL, order_sub_type='pos', channel_id=4, deal_amt=34.00, paid_amt=0.00, p_paid_amt=0.00, f_paid_amt=0.00, p_pay_amt=34.00, f_pay_amt=0.00, p_lp_amt=34.00, p_sp_amt=34.00, total_promo_amt=0.00, p_promo_amt=0.00, f_amt=0.00, f_promo_amt=0.00, p_credit_pay_amt=0.00, f_credit_pay_amt=0.00, p_member_store_pay_amt=0.00, f_member_store_pay_amt=0.00, p_cash_pay_amt=0.00, p_change_amt=0.00, third_party_pay_rate=0.0000, third_party_pay_charge_amt=0.00, remark=NULL, internal_remark=NULL, reason='延时自动取消订单', images=NULL, first_buy_flag=0, source_order_id=NULL, latest_transition_order_id=NULL, current_transition_order_id=NULL, outer_order_id='1292300000043030', outer_order_type=0, province_id=310000, city_id=310000, device_code='418432721841307648', sales_clerk_id=3605, dis_member_id=NULL, guide_link_id=NULL, group_buy_type=NULL, refund_type=NULL, refund_state=NULL, refund_status=NULL, refund_creator=NULL, refund_stock_handle_type=NULL, ref_id=NULL, activity_id=NULL, refund_at=NULL, erp_order_at=NULL, complete_at=NULL, order_at='2022-03-11 17:57:14', pay_at=NULL, cancel_at='2022-03-11 18:02:14', created_at='2022-03-11 17:57:14', updated_at='2022-03-11 18:02:14', completed_by=NULL, refund_by=NULL, created_by='', updated_by='0', last_updated_at='2022-03-11 18:02:13.830230' WHERE id=1292300000043030 AND tenant_id='1000000002' AND serial_number='' AND order_category='sale' AND order_type='normal' AND order_status='os.waiting.pay' AND title='《自卑与超越》   1件' AND product_count=1.00 AND customer_id='601625521185043183' AND customer_name='' AND customer_phone IS NULL AND customer_type=0 AND shop_id='500002' AND shop_name='零食角' AND payment_type IS NULL AND order_sub_type='pos' AND channel_id=4 AND deal_amt=34.00 AND paid_amt=0.00 AND p_paid_amt=0.00 AND f_paid_amt=0.00 AND p_pay_amt=34.00 AND f_pay_amt=0.00 AND p_lp_amt=34.00 AND p_sp_amt=34.00 AND total_promo_amt=0.00 AND p_promo_amt=0.00 AND f_amt=0.00 AND f_promo_amt=0.00 AND p_credit_pay_amt=0.00 AND f_credit_pay_amt=0.00 AND p_member_store_pay_amt=0.00 AND f_member_store_pay_amt=0.00 AND p_cash_pay_amt=0.00 AND p_change_amt=0.00 AND third_party_pay_rate=0.0000 AND third_party_pay_charge_amt=0.00 AND remark IS NULL AND internal_remark IS NULL AND reason IS NULL AND images IS NULL AND first_buy_flag=0 AND source_order_id IS NULL AND latest_transition_order_id IS NULL AND current_transition_order_id IS NULL AND outer_order_id='1292300000043030' AND outer_order_type=0 AND province_id=310000 AND city_id=310000 AND device_code='418432721841307648' AND sales_clerk_id=3605 AND dis_member_id IS NULL AND guide_link_id IS NULL AND group_buy_type IS NULL AND refund_type IS NULL AND refund_state IS NULL AND refund_status IS NULL AND refund_creator IS NULL AND refund_stock_handle_type IS NULL AND ref_id IS NULL AND activity_id IS NULL AND refund_at IS NULL AND erp_order_at IS NULL AND complete_at IS NULL AND order_at='2022-03-11 17:57:14' AND pay_at IS NULL AND cancel_at IS NULL AND created_at='2022-03-11 17:57:14' AND updated_at='2022-03-11 17:57:14' AND completed_by IS NULL AND refund_by IS NULL AND created_by='' AND updated_by='' AND last_updated_at='2022-03-11 17:57:13.751431' LIMIT 1; #start 44593392 end 44594718 time 2022-03-11 18:02:13
INSERT INTO order_db.order_header(id, tenant_id, serial_number, order_category, order_type, order_status, title, product_count, customer_id, customer_name, customer_phone, customer_type, shop_id, shop_name, payment_type, order_sub_type, channel_id, deal_amt, paid_amt, p_paid_amt, f_paid_amt, p_pay_amt, f_pay_amt, p_lp_amt, p_sp_amt, total_promo_amt, p_promo_amt, f_amt, f_promo_amt, p_credit_pay_amt, f_credit_pay_amt, p_member_store_pay_amt, f_member_store_pay_amt, p_cash_pay_amt, p_change_amt, third_party_pay_rate, third_party_pay_charge_amt, remark, internal_remark, reason, images, first_buy_flag, source_order_id, latest_transition_order_id, current_transition_order_id, outer_order_id, outer_order_type, province_id, city_id, device_code, sales_clerk_id, dis_member_id, guide_link_id, group_buy_type, refund_type, refund_state, refund_status, refund_creator, refund_stock_handle_type, ref_id, activity_id, refund_at, erp_order_at, complete_at, order_at, pay_at, cancel_at, created_at, updated_at, completed_by, refund_by, created_by, updated_by, last_updated_at) VALUES (1292300000043040, '1000000002', '', 'sale', 'normal', 'os.waiting.pay', '《自卑与超越》   1件', 1.00, '601625521185043183', '', NULL, 0, '500002', '零食角', NULL, 'pos', 4, 34.00, 0.00, 0.00, 0.00, 34.00, 0.00, 34.00, 34.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.0000, 0.00, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, '1292300000043040', 0, 310000, 310000, '418432721841307648', 3605, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2022-03-11 18:03:06', NULL, NULL, '2022-03-11 18:03:06', '2022-03-11 18:03:06', NULL, NULL, '', '', '2022-03-11 18:03:06.519707'); #start 44616908 end 44617780 time 2022-03-11 18:03:06

对于更多的关于binlog2sql这个工具的使用介绍可以参考 binlog2sql的安装及使用

更多大数据，职场内容可关注 ” 迪答” 公众号

Original: https://blog.csdn.net/HD0do/article/details/123464126
Author: HD0do
Title: mysqlbinlog文件解析为对应的SQL语句详解