PolarDB:Remotely obtain and parse the binary log files of a PolarDB for MySQL cluster

Prerequisites

Before you begin, ensure that you have:

• Enabled binary logging for the PolarDB for MySQL cluster. Binary logging is disabled by default. Without it, SHOW BINARY LOGS; returns You are not using binary logging. Enabling or disabling binary logging triggers an automatic cluster restart — perform this change during off-peak hours and make sure your applications can reconnect automatically.

• A MySQL client installed on your local server.

• The cluster endpoint (private or public). See Manage the endpoints of a cluster.

• A database account for the PolarDB for MySQL cluster. See Create and manage a database account.

How it works

The node that serves binary logs depends on which endpoint you connect to:

Downloading binary logs from a read-only node requires:

• PolarDB for MySQL 8.0, revision version 8.0.1.1.12 or later

• PolarDB for MySQL 5.7, revision version 5.7.1.0.12 or later

If the version requirement is not met, mysqlbinlog returns Only allow to dump binary log file on primary instance. See Minor version update to upgrade.

Download binary log files

1. Connect to the PolarDB cluster from the MySQL client. See Database connection.

   

2. List the available binary log files:

   SHOW BINARY LOGS;

   The output lists each log file name and size:

   +------------------+-----------+
   | Log_name         | File_size |
   +------------------+-----------+
   | mysql-bin.000005 |      2639 |
   +------------------+-----------+
   1 row in set (0.00 sec)

   Note the Log_name value — use it in the next step.

3. Exit the MySQL client:

   exit

4. Run mysqlbinlog to download the binary log file to your local server: Command format:

   Parameters:

   Parameter	Description
   --read-from-remote-server	Required. Connects to the cluster as a replication client to stream binary logs remotely.
   --host / -h	The private or public endpoint of the cluster. Use a private endpoint if the cluster and your server are in the same region and virtual private cloud (VPC); otherwise, use a public endpoint. If the endpoint uses a non-default port, append the port number after the endpoint.
   --user / -u	The database account name of the PolarDB for MySQL cluster.
   --password / -p	The account password. Omit the value to enter it interactively after running the command.
   --raw	Saves the binary log in its original binary format instead of converting it to text.
   mysql-bin.<log-number>	The name of the binary log file from the Log_name field in step 2.

   mysqlbinlog -u<Username> -p<Password> -h<Endpoint> --read-from-remote-server --raw mysql-bin.******

   Example:

   mysqlbinlog -utest_api -p -htest-polardb.rwlb.rds.aliyuncs.com --read-from-remote-server --raw mysql-bin.000005

   

View and parse binary log files

Run mysqlbinlog on the downloaded file to decode and view its contents:

mysqlbinlog -vv --base64-output=decode-rows mysql-bin.<log-number> | more

• -vv: shows detailed SQL statements and row-level changes

• --base64-output=decode-rows: decodes base64-encoded row events into readable SQL

The output shows the decoded contents of each event in the binary log file:

For full mysqlbinlog syntax and options, see the MySQL documentation.

FAQ

ERROR: Error in Log_event::read_log_event(): 'Sanity check failed', data_len: 151, event_type: 35
ERROR: Could not read entry at offset 120: Error in log format or read error.
ERROR: Got error reading packet from server: 'Slave can not handle replication events with the checksum that master is configured to log;

ERROR: Failed on connect: Lost connection to MySQL server at 'reading initial communication packet', system error: 104

Why are the binary log file contents not decoded?

If you run mysqlbinlog without --base64-output=decode-rows, row events are displayed as raw base64 strings:

Always include --base64-output=decode-rows when viewing binary log files.