In MySQL, the binary log format (binlog_format) can be set to STATEMENT, ROW, or MIXED. The ROW format records every row change individually, which porvides higheer consistency and reliability. However, when using the mysqlbinlog utility to inspect a binary log written in ROW format, the output may appear garbled because the tool does not automatically apply the correct character encoding.

Why Garbled Output Occurs

The ROW format stores data changes in a binary representation. If the character set used by the MySQL server differs from the one assumed by mysqlbinlog, the textual fields in the log cannot be decoded properly, resulting in unreadable characters.

Solutions

Two approaches can resolve the encoding mismatch:

• Specify the character set directly in the mysqlbinlog command.
• Configure the server’s default character set in the MySQL configuration file.

Option 1: Using the --character-set Flag

When invoking mysqlbinlog, pass the --character-set option with the desired encoding (e.g., utf8 or utf8mb4).

mysqlbinlog --character-set=utf8mb4 mysql-bin.000001

Option 2: Setting the Server Character Set Globally

Edit the MySQL configuration file (typically my.cnf or my.ini) and add the following under the [mysqld] section:

[mysqld]
character-set-server=utf8mb4

Restart the MySQL service after making the change.

Worked Example

The following walkthrough demonstrates creating a table, inserting data, and then using mysqlbinlog with the encoding flag to view the log output correctly.

-- Create a sample table
CREATE TABLE demo_users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(100)
) ENGINE=InnoDB;

-- Insert a row
INSERT INTO demo_users (full_name) VALUES ('Zoë Müller');

-- Rotate the binary log for clarity
FLUSH LOGS;

-- Use mysqlbinlog with the proper character set
mysqlbinlog --character-set=utf8mb4 mysql-bin.000002

Without the --character-set option, the name 'Zoë Müller' would likely appear as garbled text in the log output.