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

This topic describes how to remotely obtain the binary log file of an ApsaraDB RDS for MySQL instance and use mysqlbinlog to parse the file.

Important

Take note of the following items:

Before you perform high-risk operations such as modifying the configurations or data of your instances, we recommend that you check the disaster recovery and fault tolerance capabilities of the instances to ensure data security.
Before you modify the configurations or data of an instance, such as an Elastic Compute Service (ECS) instance or an ApsaraDB RDS instance, we recommend that you create snapshots or enable backup for the instance. For example, you can enable log backup for an ApsaraDB RDS instance.
If you have granted permissions on sensitive information or submitted sensitive information in the Alibaba Cloud Management Console, we recommend that you modify the sensitive information at the earliest opportunity. Sensitive information includes usernames and passwords.

Obtain the binary log file

You need to select a method to obtain the binary log file based on your business requirements.

Method 1: (Recommended) Download log files from the ApsaraDB RDS console

Download log files from the ApsaraDB RDS console. For more information, see Download the backup files of an ApsaraDB RDS for MySQL instance.

Method 2: Remotely obtain the binary log file

Connect a client to the RDS instance. For more information, see Use a database client or the CLI to connect to an ApsaraDB RDS for MySQL instance.

Execute the following SQL statement to view and record the value of Log_name in the logs table. The value is the name of the binary log file, such as mysql-bin.xxx.

show binary logs;

The following shows a sample output.

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000022 |    406039 |
| mysql-bin.000023 |     71497 |
+------------------+-----------+
2 rows in set (0.01 sec)

Run the following command in the CLI based on the name of the binary log file that is obtained in the preceding step to remotely obtain the binary log file and save the file to your on-premises device:

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

The following table describes the parameters.

Parameter	Description
`-u<$User>`	The username that is used to connect to the MySQL server.
`-p<$Password>`	The password that is used to connect to the MySQL server.
`-h<$Host>`	The endpoint that is used to remotely connect to the RDS instance. For more information, see View and manage instance endpoints and ports.
`--read-from-remote-server`	The remote MySQL server to which `mysqlbinlog` is connected to obtain the content of the binary log file.
`--raw`	The raw format that is used to display the content of the binary log file in the output of the `mysqlbinlog` command after the parsing.
`<mysql-bin.XXX>`	The name of the binary log file that needs to be parsed. You need to configure this parameter based on the result from Step 2.
`> [$File_Name]`	The name of the binary log file that is remotely obtained and saved to your on-premises device.

Run the following command to check whether the binary log file is obtained:
```
more [$File_Name]
```

Use mysqlbinlog to view the binary log file

Run the following command in the CLI to view the content of the binary log file by using mysqlbinlog:

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

The following table describes the parameters.

Parameter	Description
`-vv`	The output details, including details about the events obtained from the parsing.
`--base64-output=decode-rows`	Specifies that Base64 encoding is used to record the binary log entries.
`<mysql-bin.XXX>`	The name of the binary log file that needs to be parsed. Replace `XXX` with the actual file name.
`\| more`	Specifies that a pipeline is used to send the output to the `more` command to display the output by page.

Note

For more information about binary log parsing, see MySQL documentation.

The 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 the common errors that may occur when you use mysqlbinlog.

If the following error occurs, check the version of mysqlbinlog. For example, if you use mysqlbinlog 3.3 and encounter the following error, you can use mysqlbinlog 3.4 to view the content of the binary log file because this error is resolved in mysqlbinlog 3.4. If this error occurs, you can use a later mysqlbinlog version to view the content of the binary log file.
```
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 use mysqlbinlog to view the content of the binary log file but the --base64-output=decode-rows parameter is not used, the output is the unparsed content.
If the following error occurs, check whether the default-character-set=utf8mb4 parameter exists in the my.cnf configuration file. If the parameter exists, add the --no-defaults parameter to prevent this error. 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 the time of binary logs resolved by the mysqlbinlog command is different from the actual time, check whether the time zone of the environment in which the mysqlbinlog command is run is the same as the time zone of your ApsaraDB RDS for MySQL database.
In MySQL, the time information of binary logs is stored as UNIX timestamps that do not include time zone information. Therefore, the time resolved by the mysqlbinlog command is converted based on the local time zone of the environment in which the command is run. If the time zone of the environment is different from that of the RDS for MySQL instance, the resolved time is inconsistent with the actual time.

Applicable scope

ApsaraDB RDS for MySQL