Obtain and view RDS for MySQL binary logs - ApsaraDB RDS

This topic describes how to remotely obtain binary logs from an Alibaba Cloud RDS for MySQL instance and parse them by using the mysqlbinlog utility.

Important

Before you perform risky operations, such as modifying instance configurations or data, ensure that your instance is disaster-tolerant and fault-tolerant to protect your data.
If you modify the configurations or data of an instance, such as an ECS or RDS instance, create a snapshot or enable log backup for the RDS instance beforehand.
If you have granted permissions or submitted sensitive information (such as usernames and passwords) on the Alibaba Cloud platform, change the information promptly.

Obtain binary logs

Method 1: Download from the RDS console

Instances that use cloud disks: After you enable the log backup feature, which is enabled by default, local logs (binary logs) are uploaded or copied to a backup storage space in real time to form log backups. You can download the log backup for the desired point in time. For more information, see Download from cloud disk instances.
Instances that use a high-performance local disk: For more information, see Download from high-performance local disk instances.

Note

To learn how to enable log backup, see Back up an Alibaba Cloud RDS for MySQL instance.

Method 2: Remotely obtain binary logs

Connect to an Alibaba Cloud RDS for MySQL instance.
Important
The MySQL client version should match the RDS instance version.

Run the SHOW BINARY LOGS; statement to view the binary log file names. Record the name of the file you need, such as mysql-bin.xxx, from the Log_name column.

SHOW BINARY LOGS;

A similar output is returned:

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

Use the file name from the previous step to remotely obtain the binary log and save it locally.

Run the following command to exit the MySQL CLI and return to the operating system:
```
exit;
```

Run the following command to remotely obtain the binary log and save it to your local machine:

mysqlbinlog -u<$User> -p<$Password> -h<$Host> --read-from-remote-server --raw <mysql-bin.XXX>

The following table describes the parameters.

Parameter	Description
`-u<$User>`	The username used to connect to the MySQL server.
`-p<$Password>`	The password used to connect to the MySQL server.
`-h<$Host>`	The endpoint of the Alibaba Cloud RDS instance. To learn how to find the endpoint, see View the endpoints and port numbers of an Alibaba Cloud RDS for MySQL instance.
`--read-from-remote-server`	Connects to the specified remote MySQL server to obtain the binary log.
`--raw`	When used with `--read-from-remote-server`, this option causes `mysqlbinlog` to write the binary log to a local file in its original format.
`<mysql-bin.XXX>`	The name of the binary log file to obtain, as identified in step 2.

View binary log files by using mysqlbinlog

Run the following mysqlbinlog command to view the contents of a binary log file.

Important

The MySQL client version should match the RDS instance version.

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

To save the parsed content for review, write the output to a file.

mysqlbinlog -vv --base64-output=decode-rows <mysql-bin.XXX> > [$File_Name]

The following table describes the parameters.

Parameter	Description
`-vv`	Displays verbose output, including detailed content for each parsed event.
`--base64-output=decode-rows`	Prints binary log row events in a decoded form.
`<mysql-bin.XXX>`	The name of the binary log file to parse. Replace `XXX` with the actual file name.
`\| more`	Pipes the output to the `more` command for paginated display.
`> [$File_Name]`	The local file to which the parsed content is written.

Note

For more information about parsing binary logs, see the official MySQL documentation.

A similar output is returned:

[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

This section describes common issues when using the mysqlbinlog utility.

If you receive the following error, check the version of your mysqlbinlog utility. This error, which may occur in version 3.3, is fixed in version 3.4. To resolve this, use a later version of mysqlbinlog.
```
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.
```
If you do not specify the --base64-output=decode-rows parameter when running mysqlbinlog, the output remains encoded.
If you receive this error, check if your my.cnf configuration file contains the default-character-set=utf8mb4 field. If it does, add the --no-defaults parameter to your command to resolve the error. For example: mysqlbinlog --no-defaults -u[$User] -p[$Password] -h[$Host] --read-from-remote-server mysql-bin.XXX > [$File_Name].
```
mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8mb4'
```
If timestamps in the parsed log are incorrect, verify that the time zone of your local environment matches the database's time zone.
MySQL binary logs store timestamps in Unix time format without time zone data. The mysqlbinlog command converts these timestamps using the local time zone of the system where it is run. A time zone mismatch between the client environment and the database server will result in incorrect timestamps in the output.

Applies to

Alibaba Cloud RDS for MySQL