How do I remotely obtain and parse the binary log file of an ApsaraDB RDS for MySQL instance? - ApsaraDB RDS

Prerequisites

Before you begin, ensure that you have:

An ApsaraDB RDS for MySQL instance with the log backup feature enabled. To enable it, see Procedure
A MySQL client installed locally, running the same MySQL version as the RDS instance
The instance endpoint. To find it, see View and manage instance endpoints and ports
A database account with sufficient permissions to connect to the instance

Download binary log files

Two methods are available. Use the console method unless you need programmatic or real-time access.

Method 1 (recommended): Download from the console

Cloud disk instances: Log backup is enabled by default. Binary logs are uploaded to backup storage in real time. Download logs at any point in time from the console. See the cloud disk section in Download methods.
Local disk instances: See the local disk section in Download methods.

Method 2: Download using mysqlbinlog

Use this method to download binary log files directly to your machine using mysqlbinlog.

Connect to the RDS instance:
```
mysql -u<username> -p -h<endpoint>
```

List available binary log files and note the Log_name values:

SHOW BINARY LOGS;

The output lists available binary log files:

mysql> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000022 |    406039 |
| mysql-bin.000023 |     71497 |
+------------------+-----------+
2 rows in set (0.01 sec)

Exit the MySQL CLI:
```
exit;
```
Download the binary log file to your local machine. Replace <mysql-bin.XXX> with the file name from Step 2 and <output-file> with the local file name to save to:
- --read-from-remote-server — connects to the remote MySQL server to stream binary log content.
- --raw — saves the file in raw binary format instead of text.
- -u<username> — the database account username.
- -p<password> — the database account password.
- -h<endpoint> — the RDS instance endpoint.
- <mysql-bin.XXX> — the binary log file name from Step 2.
- > <output-file> — the local file to save the binary log to.
```
mysqlbinlog \
  -u<username> \
  -p<password> \
  -h<endpoint> \
  --read-from-remote-server \
  --raw \
  <mysql-bin.XXX> > <output-file>
```
Parameters:
Verify the file was downloaded successfully:
```
more <output-file>
```

Parse binary log files

After downloading the binary log file, use mysqlbinlog to parse and view its contents.

To view the parsed output page by page:

mysqlbinlog \
  -vv \
  --base64-output=decode-rows \
  <mysql-bin.XXX> | more

To write the parsed output to a file:

mysqlbinlog \
  -vv \
  --base64-output=decode-rows \
  <mysql-bin.XXX> > <output-file>

Parameters:

-vv — shows detailed event information, including row-level changes in human-readable form.
--base64-output=decode-rows — decodes Base64-encoded row events so you can read the SQL statements.
<mysql-bin.XXX> — the binary log file to parse.
| more — pages through the output one screen at a time.
> <output-file> — writes the output to a local file.

The parsed output looks similar to:

[root@iZbp****** ~]# mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000022 | more
# 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 /*!*/;
# at 4
#230911  9:27:28 server id 26718053  end_log_pos 123 CRC32 0xa231cb44   Start: binlog v 4, server v 5.7.42-log created 230911  9:27:28
# at 123
#230911  9:27:28 server id 26718053  end_log_pos 194 CRC32 0x078b6dc1   Previous-GTIDs
# a63b4ed1-4c86-11ee-9029-00163e157053:1-27339
# at 194
#230911  9:27:32 server id 26718053  end_log_pos 259 CRC32 0x59b848c3   GTID    last_committed=0        sequence_number=1       rbr_only=yes    original_committed_ti
mestamp=0       immediate_commit_timestamp=0    transaction_length=0
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=0 (1970-01-01 08:00:00.000000 CST)
# immediate_commit_timestamp=0 (1970-01-01 08:00:00.000000 CST)
/*!80001 SET @@session.original_commit_timestamp=0*//*!*/;
/*!80014 SET @@session.original_server_version=0*//*!*/;
/*!80014 SET @@session.immediate_server_version=0*//*!*/;
SET @@SESSION.GTID_NEXT= 'a63b4ed1-4c86-11ee-9029-00163e157053:27340'/*!*/;
# at 259
#230911  9:27:32 server id 26718053  end_log_pos 327 CRC32 0xc0dddaec   Query   thread_id=16849 exec_time=0     error_code=0
SET TIMESTAMP=1694395652/*!*/;
SET @@session.pseudo_thread_id=16849/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=2097152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb3 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN

FAQ

The output shows unreadable binary content instead of SQL statements.

Add --base64-output=decode-rows to the mysqlbinlog command. Without it, row-based binary log events are displayed in raw Base64-encoded format.

I see this error: `ERROR: Error in Log_event::read_log_event(): 'Sanity check failed'`

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.

This is a known issue in mysqlbinlog 3.3. Upgrade to mysqlbinlog 3.4 or later to resolve it.

I see this error: `mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8mb4'`

Your my.cnf configuration file contains default-character-set=utf8mb4, which mysqlbinlog does not support. Add --no-defaults to skip the configuration file:

mysqlbinlog \
  --no-defaults \
  -u<username> \
  -p<password> \
  -h<endpoint> \
  --read-from-remote-server \
  <mysql-bin.XXX> > <output-file>

The timestamps in the parsed output don't match the actual event times.

Binary log timestamps are stored as UNIX timestamps with no time zone information. mysqlbinlog converts them using the local time zone of the machine where you run the command. If that time zone differs from the RDS instance's time zone, the displayed times will be off. Set your local time zone to match the RDS instance before running mysqlbinlog.

Applicable scope

ApsaraDB RDS for MySQL