Common MySQL binlog Commands and Usage Tips

Common Commands

1. Parse binlog for troubleshooting

If you only need to parse and view the binlog, add --base64-output=decode-rows to hide the row‑format content:

mysqlbinlog --no-defaults -vv --base64-output=decode-rows mysql-bin.000201

2. Parse transactions of a specific GTID

Use this to analyze what a particular transaction did:

mysqlbinlog --no-defaults -vv --base64-output=decode-rows --include-gtids='b0ca6715-7554-11ea-a684-02000aba3dad:614037' mysql-bin.000199

3. Parse binlog within a specified range

a. Time range

Use --start-datetime and --stop-datetime to parse binlogs within a rough time window (not precise enough for replay). If you only know an approximate interval, you can parse multiple binlog files at once:

mysqlbinlog --no-defaults -vv --base64-output=decode-rows --start-datetime='2020-08-18 11:20:00' --stop-datetime='2020-08-18 12:00:00' mysql-bin.000203 mysql-bin.000204 mysql-bin.000205

b. Position range

Use --start-position and --stop-position to parse a specific byte‑position range. When parsing multiple files, --start-position applies only to the first file and --stop-position to the last file.

mysqlbinlog --no-defaults -vv --base64-output=decode-rows  --start-position='537' --stop-position='945' mysql-bin.000204
# at 537           "starting position before the GTID event"
#200818 11:29:03 server id 3 end_log_pos 602 CRC32 0x7f07dd8c GTID last_committed=1 sequence_number=2 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'b0ca6715-7554-11ea-a684-02000aba3dad:614061'/*!*/;
...
#200818 11:29:03 server id 3 end_log_pos 945 CRC32 0xedf2b011 Query thread_id=3 exec_time=0 error_code=0 SET TIMESTAMP=1597721343/*!*/;
COMMIT /*!*/;
# at 945          "ending position after the COMMIT event"

c. GTID range

Use --include-gtids or --exclude-gtids to include or exclude specific GTID transactions. See the parameter explanations below for details.

4. Replay binlog

Do not add --base64-output=decode-rows when replaying, because the row format (the actual data) would be omitted.

You can also use the range parameters described above during replay.

Replaying to the current instance does not require changing the server‑id, but you must ensure the GTID does not already exist.

If the GTID already exists, replay will not error but will also not actually apply those transactions; you can bypass this restriction with --skip-gtids .

mysqlbinlog --no-defaults --skip-gtids mysql-bin.000203 | mysql -S /data/mysql/data/3306/mysqld.sock -proot

Parameter Explanations

1. --no-defaults – Prevents mysqlbinlog from reading client options from my.cnf that might cause failures.

2. -v – Shows only the row‑format strings; without it you cannot reconstruct pseudo‑SQL.

3. -vv – Reconstructs pseudo‑SQL from the row format and adds comments; it also shows the effect of binlog_rows_query_log_events .

4. --base64-output=decode-rows – Hides the raw row format; when combined with -v it decodes the rows into commented pseudo‑SQL.

5. --skip-gtids – Omits GTID event information, causing replayed transactions to generate new GTIDs as if they were fresh.

6. --include-gtids – Parses only the transactions that match the specified GTIDs.

7. --exclude-gtids – Skips parsing of the specified GTID transactions.