mysqlbinlog使用记录

首先要确认mysql启用了binlog功能。一般默认启用。

mysql> select @@log_bin;
+-----------+
| @@log_bin |
+-----------+
|         1 |
+-----------+

然后确认binlog目录

mysql> select @@log_bin_basename;
+----------------------------+
| @@log_bin_basename         |
+----------------------------+
| /data/mysql/data/mysql-bin |
+----------------------------+
1 row in set (0.20 sec)

再确认binlog格式，一般都是ROW

mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW             |
+-----------------+
1 row in set (0.24 sec)

查看下当前的binlog文件。最后一个。

mysql> show master status;
+------------------+-----------+--------------+------------------+------------------------------------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                              |
+------------------+-----------+--------------+------------------+------------------------------------------------+
| mysql-bin.000162 | 508947599 |              |                  | 8c6a1022-2488-11ee-823a-fa163e4f0fbf:1-1706820 |
+------------------+-----------+--------------+------------------+------------------------------------------------+
1 row in set (0.19 sec)

查看binlog清单。

mysql> show master logs;
+------------------+------------+-----------+
| Log_name         | File_size  | Encrypted |
+------------------+------------+-----------+
| mysql-bin.000097 | 1267374431 | No        |
| mysql-bin.000098 | 1078842954 | No        |
| mysql-bin.000099 | 1076402277 | No        |
| mysql-bin.000100 | 1074662461 | No        |
| mysql-bin.000101 | 1129645727 | No        |
| mysql-bin.000102 | 1073854117 | No        |
| mysql-bin.000103 | 1076120021 | No        |
| mysql-bin.000104 | 1163004975 | No        |
| mysql-bin.000105 | 1080999999 | No        |
| mysql-bin.000106 | 1123681007 | No        |
| mysql-bin.000107 | 1096492121 | No        |
| mysql-bin.000108 | 1080796650 | No        |
| mysql-bin.000109 | 1074606514 | No        |
| mysql-bin.000110 | 1094934164 | No        |
| mysql-bin.000111 | 1075325447 | No        |
| mysql-bin.000112 | 1204392658 | No        |
| mysql-bin.000113 | 1180372937 | No        |
| mysql-bin.000114 | 1196526678 | No        |
| mysql-bin.000115 | 1238177050 | No        |
| mysql-bin.000116 | 1074988755 | No        |
| mysql-bin.000117 | 1091344404 | No        |
| mysql-bin.000118 | 1076183240 | No        |
| mysql-bin.000119 | 1081950716 | No        |
| mysql-bin.000120 | 1109171293 | No        |
| mysql-bin.000121 | 1101011874 | No        |
| mysql-bin.000122 | 1218723442 | No        |
| mysql-bin.000123 | 1183060961 | No        |
| mysql-bin.000124 | 1106412574 | No        |
| mysql-bin.000125 | 1250171532 | No        |
| mysql-bin.000126 | 1078337881 | No        |
| mysql-bin.000127 | 1076340326 | No        |
| mysql-bin.000128 | 1073886411 | No        |
| mysql-bin.000129 | 1073771450 | No        |
| mysql-bin.000130 | 1073763024 | No        |
| mysql-bin.000131 | 1073754658 | No        |
| mysql-bin.000132 | 1073765124 | No        |
| mysql-bin.000133 | 1073758960 | No        |
| mysql-bin.000134 | 1167060782 | No        |
| mysql-bin.000135 | 1190829992 | No        |
| mysql-bin.000136 | 1096377082 | No        |
| mysql-bin.000137 | 1074835535 | No        |
| mysql-bin.000138 | 1097946487 | No        |
| mysql-bin.000139 | 1079454725 | No        |
| mysql-bin.000140 | 1073757655 | No        |
| mysql-bin.000141 | 1187881785 | No        |
| mysql-bin.000142 | 1096423858 | No        |
| mysql-bin.000143 | 1233191324 | No        |
| mysql-bin.000144 | 1080399415 | No        |
| mysql-bin.000145 | 1207148115 | No        |
| mysql-bin.000146 | 1211077714 | No        |
| mysql-bin.000147 | 1155530016 | No        |
| mysql-bin.000148 | 1141697366 | No        |
| mysql-bin.000149 | 1104053983 | No        |
| mysql-bin.000150 | 1181112005 | No        |
| mysql-bin.000151 | 1136589759 | No        |
| mysql-bin.000152 | 1077718940 | No        |
| mysql-bin.000153 | 1148381060 | No        |
| mysql-bin.000154 | 1185805639 | No        |
| mysql-bin.000155 | 1073741887 | No        |
| mysql-bin.000156 | 1104320103 | No        |
| mysql-bin.000157 | 1088572221 | No        |
| mysql-bin.000158 | 1076297985 | No        |
| mysql-bin.000159 | 1232823373 | No        |
| mysql-bin.000160 | 1213815833 | No        |
| mysql-bin.000161 | 1122061331 | No        |
| mysql-bin.000162 |  510032311 | No        |
+------------------+------------+-----------+

确认下binlog的文件

[iwhalecloud@apaas-mid1 data]$ lsapaas-mid1.log                  app_5195062153216_default_dev   app_d_1004304311066894336_dev   app_d_1010718326218174464_dev   app_d_1011464263104589824_dev   app_d_1016624577895215104_dev   app_d_1027753803562999808_dev   ca.pem               mysql-bin.000107   mysql-bin.000133   mysql-bin.000159app_0171769606782976_test_dev   app_587242401792_default_test   app_d_1004305219343425536_dev   app_d_1010790433914744832_dev   app_d_1011468334553231360_dev   app_d_1016630849134669824_dev   app_d_1027834867983568896_dev   client-cert.pem      mysql-bin.000108   mysql-bin.000134   mysql-bin.000160app_039531520_testappdemo_dev   app_618118144_zerocode_db_dev   app_d_1004305335215267840_dev   app_d_1010810408964583424_dev   app_d_1011475911693430784_dev   app_d_1016874600214048768_dev   app_d_1031846359492018176_dev   client-key.pem       mysql-bin.000109   mysql-bin.000135   mysql-bin.000161app_1154719241531392_utf8_dev   app_624027447296_default_test   app_d_1004305483873984512_dev   app_d_1010817221210533888_dev   app_d_1011512375550537728_dev   app_d_1017310111265206272_dev   app_d_1032242791453638656_dev   fastflow             mysql-bin.000110   mysql-bin.000136   mysql-bin.000162app_1189251336433664_jdxt_dev   app_6488617971712_shucun_test   app_d_1004326979078004736_dev   app_d_1010825204518744064_dev   app_d_1011538998677073920_dev   app_d_1018337333302321152_dev   app_d_1033622580274925568_dev  '#ib_16384_0.dblwr'   mysql-bin.000111   mysql-bin.000137   mysql.ibdapp_127492251648_default_test   app_659885162496_default_test   app_d_1005737741596581888_dev   app_d_1010832101951008768_dev   app_d_1011575392178925568_dev   app_d_1018338118778019840_dev   app_d_1033625372782837760_dev  '#ib_16384_1.dblwr'   mysql-bin.000112   mysql-bin.000138   mysql_upgrade_infoapp_138032611328_default_test   app_666318913536_default_test   app_d_1005738777421897728_dev   app_d_1010907921470058496_dev   app_d_1011575553651240960_dev   app_d_1018340928600989696_dev   app_d_1033983426992881664_dev   ib_buffer_pool       mysql-bin.000113   mysql-bin.000139   performance_schemaapp_174031101952_default_test   app_6845819412480_default_dev   app_d_1005739158608633856_dev   app_d_1010908254564904960_dev   app_d_1011827666318913536_dev   app_d_1018412911153754112_dev   app_d_1034827970723319808_dev   ibdata1              mysql-bin.000114   mysql-bin.000140   private_key.pemapp_1945102188544_default_dev   app_709634650112_default_test   app_d_1005739257745203200_dev   app_d_1010908346898313216_dev   app_d_1012278439994707968_dev   app_d_1018687570684260352_dev   app_d_1034828040540999680_dev   ibtmp1               mysql-bin.000115   mysql-bin.000141   public_key.pemapp_1945102188569_laodian_dev   app_725581508608_test_tt1_dev   app_d_1005739323465752576_dev   app_d_1010924624027447296_dev   app_d_1012278563198193664_dev   app_d_1019055184992382976_dev   app_d_1036464803857166336_dev  '#innodb_redo'        mysql-bin.000116   mysql-bin.000142   server-cert.pemapp_195062153216_default_test   app_791453638656_default_test   app_d_1006012396081340416_dev   app_d_1011079920993562624_dev   app_d_1013338123723853824_dev   app_d_1019873200897794048_dev   app_d_1036519544385974272_dev  '#innodb_temp'        mysql-bin.000117   mysql-bin.000143   server-key.pemapp_196571193344_default_test   app_816690434048_default_test   app_d_1006122233230352384_dev   app_d_1011104587242401792_dev   app_d_1013338201851154432_dev   app_d_1023842551653220352_dev   app_d_1036519946628116480_dev   lcdp                 mysql-bin.000118   mysql-bin.000144   sysapp_2138032611328_default_dev   app_8227738279612416_test_dev   app_d_1006132308405739520_dev   app_d_1011106345658216448_dev   app_d_1013338422039531520_dev   app_d_1024219997648994304_dev   app_d_1037656375662415872_dev   lcdp_erm             mysql-bin.000119   mysql-bin.000145   undo_001app_218821236633600_db_sq_dev   app_825204518744064_test1_dev   app_d_1006446232452059136_dev   app_d_1011149288259579904_dev   app_d_1013358153484193792_dev   app_d_1024231818401431552_dev   app_d_1040171117312413696_dev   lcdp_run             mysql-bin.000120   mysql-bin.000146   undo_002app_227738279612416_test_test   app_847371726848_default_test   app_d_1006448431248822272_dev   app_d_1011164197059948544_dev   app_d_1014489244858773504_dev   app_d_1024232357008785408_dev   app_od_974142457073610752_dev   master-bin.index     mysql-bin.000121   mysql-bin.000147   zzh_map_demoapp_233230352384_default_test   app_86488617971712_shucun_dev   app_d_1006457700924903424_dev   app_d_1011166023419293696_dev   app_d_1014528247108661248_dev   app_d_1024565990860390400_dev   app_od_975232884835905536_dev   mysql                mysql-bin.000122   mysql-bin.000148   zzh_map_demo2app_233238469062656_test_test   app_9233238469062656_test_dev   app_d_1006458292971884544_dev   app_d_1011167920884674560_dev   app_d_1014805207286214656_dev   app_d_1024566471858978816_dev   app_od_978592102489714688_dev   mysql-bin.000097     mysql-bin.000123   mysql-bin.000149app_308405739520_default_test   app_945102188544_default_test   app_d_1006459888120221696_dev   app_d_1011177196571193344_dev   app_d_1014827357393055744_dev   app_d_1024572042514006016_dev   app_od_978825127492251648_dev   mysql-bin.000098     mysql-bin.000124   mysql-bin.000150app_3238079225856_default_dev   app_979078004736_default_test   app_d_1006460401544974336_dev   app_d_1011189251336433664_dev   app_d_1014834673609797632_dev   app_d_1024572572867506176_dev   app_od_999137055907729408_dev   mysql-bin.000099     mysql-bin.000125   mysql-bin.000151app_335215267840_default_test   app_98871352568696832_ddd_dev   app_d_1006460812259610624_dev   app_d_1011191239532998656_dev   app_d_1014837617249759232_dev   app_d_1024573190734086144_dev   app_od_999244520643506176_dev   mysql-bin.000100     mysql-bin.000126   mysql-bin.000152app_396081340416_default_test   app_9962294104064_default_dev   app_d_1006751033644433408_dev   app_d_1011204323920179200_dev   app_d_1016166193735675904_dev   app_d_1024579370265620480_dev   app_od_999244709634650112_dev   mysql-bin.000101     mysql-bin.000127   mysql-bin.000153app_408964583424_default_test   app_d_1004300925747666944_dev   app_d_1006755011660759040_dev   app_d_1011205372915609600_dev   app_d_1016186488617971712_dev   app_d_1024579487827230720_dev   app_sc_prod                     mysql-bin.000102     mysql-bin.000128   mysql-bin.000154app_4623921942528_default_dev   app_d_1004301437498892288_dev   app_d_1007182270792089600_dev   app_d_1011220072671866880_dev   app_d_1016198049780740096_dev   app_d_1024580960116015104_dev   app_st_993335847371726848_dev   mysql-bin.000103     mysql-bin.000129   mysql-bin.000155app_4689726349312_default_dev   app_d_1004302221812768768_dev   app_d_1009283816690434048_dev   app_d_1011435482344189952_dev   app_d_1016218821236633600_dev   app_d_1024582367277088768_dev   app_szxc                        mysql-bin.000104     mysql-bin.000130   mysql-bin.000156app_488617971712_default_test   app_d_1004302307896664064_dev   app_d_1010401188190236672_dev   app_d_1011438016030961664_dev   app_d_1016591945580949504_dev   app_d_1024592473556127744_dev   auto.cnf                        mysql-bin.000105     mysql-bin.000131   mysql-bin.000157app_512927195136_default_test   app_d_1004304082401828864_dev   app_d_1010436753635815424_dev   app_d_1011459951754342400_dev   app_d_1016615229982781440_dev   app_d_1027746486259798016_dev   ca-key.pem                      mysql-bin.000106     mysql-bin.000132   mysql-bin.000158

binlog是二进制文件，直接用文本解析会存在一些乱码。使用mysqlbinlog工具处理后在可以正常读取。mysqlbinlog工具一般安装mysql的时候会默认安装。

mysqlbinlog --base64-output=DECODE-ROWS --start-datetime='2023-11-20 09:00:00' --stop-datetime='2023-11-20 11:00:00' /data/mysql/data/mysql-bin.000161 > mysqllog5.log

其中由于格式是ROW格式，如果直接导出，执行的sql语句会变成类似base64加密后格式样式。

所以追加了 --base64-output=DECODE-ROWS，就是为了转换成明文sql。

其他的start-datetime什么的都好理解，就不在说明了。

如果输出文件显示，应该是没有符合条件的数据。例如下面这个就是由于对应binlog的文件中不包含指定时间段的数据导致的。这时可以切换binlog目录测试。简单点看一下输出文件大小就可以知道是否包含有效数据了。

# 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 /*!*/;
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*/;

上图就是没有数据的文件大小，不足1k。

log4和log5条件一致，不过log5把ROW解码了，所以文件小了非常多。--base64-output=DECODE-ROWS就是这个的作用。