All Products
Search
Document Center

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

Last Updated:Oct 09, 2023

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.

Procedure

You can perform the following steps to remotely obtain and parse the binary log file.

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

  1. 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.

  2. 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)

  3. 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 change the internal and public endpoints and port numbers of an ApsaraDB RDS for MySQL instance.

    --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.

  4. 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 following shows a sample output.

[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.

    image.png
  • 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'

Applicable scope

ApsaraDB RDS for MySQL