Estimating mysqlbinlog Parsing Progress by Monitoring File Handle Offsets

Problem : mysqlbinlog is an important tool that parses MySQL binary logs (binlog) into readable SQL, but it does not provide a progress indicator such as a --progress flag, making it difficult to monitor the parsing of large binlogs.

Experiment : Using the technique from Experiment 08 we generated a large binlog (approximately 1.1 GB, file bin.000002 ). Running mysqlbinlog on this file takes about 4.6 seconds. While it runs we inspect its open file handles and see that handle 3 is reading the binlog.

By checking the current offset of handle 3 we can estimate how much of the binlog has been processed; in the example the offset is about 600 MB, which corresponds to roughly 54 % of the total file.

Conclusion : Since mysqlbinlog does not output progress directly, we approximate its overall progress by observing the read position of the binlog file handle. The tool is not truly streaming; after the entire binlog is read it still spends some time finishing processing.

Future work will include additional experiments to investigate how mysqlbinlog internally processes binlog events.

Feel free to leave comments if you have more MySQL topics you’d like to explore.

Want to see more technical articles? Click “Reading”!