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语句的情况。

2.shell的方式解析binlog文件

通过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*/;

3.通过binlog2sql解析SQL语句

 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的安装及使用

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