Disclaimer: This article may contain information about third-party products. Such information is for reference only. Alibaba Cloud does not make any guarantee, express or implied, with respect to the performance and reliability of third-party products, as well as potential impacts of operations on the products.

Overview

This topic describes how to remotely obtain binlogs from ApsaraDB RDS for MySQL and use the mysqlbinlog tool to parse binlogs.

Background information

Alibaba Cloud reminds you that:

  • Before you perform high-risk operations such as modifying instance configurations or data, we recommend that you check the disaster recovery and fault tolerance capabilities of the instances to ensure data security.
  • You can modify the configurations and data of instances including ECS and ApsaraDB RDS instances. We recommend that you create snapshots or enable RDS log backup before you modify instance configurations or data.
  • If you have authorized or submitted sensitive information such as the logon account and password in Alibaba Cloud, we recommend that you modify the information in a timely manner.

You can perform the following steps to remotely obtain binlogs and parse binlogs:

Remotely obtain binary logs

Select an appropriate binary log acquisition method based on the actual situation:

Remotely obtain binary logs

  1. Use a client to connect to an instance. For more information, see Connect to an instance.
  2. Execute the following SQL statement to view and record the Log_name value in the logs table. This value is the binary log file name, for example, mysql-bin.xxx.
    show binary logs;
    The following figure shows the sample command output.
    View the current binlog file
  3. According to the binary log file name obtained in the previous step, run the following command on the client to remotely obtain the binary log and save it to the local computer:
    mysqlbinlog -u[$User] -p[$Password] -h[$Host] --read-from-remote-server --raw mysql-bin.XXX > [$File_Name]
    Note:
    • [$Host] refers to the remote connection address of the ApsaraDB for RDS instance.
    • [$File_Name] Remotely obtains the file name of the Binlog file stored locally.
    • [$User] refers to the user used by the remote connection.
    • [$Password] The password of the user used for the remote connection.
  4. Run the following command to check whether the binary log is remotely obtained:
    more [$File_Name]
    The following figure shows the sample command output.
    Save View

Download directly from the console

Log files in the ApsaraDB for RDS console. For more information, see Download binary log files.

Introduction to the use of the mysqlbinlog tool

  1. Run the following command to view the details of binary logs by using mysqlbinlog:
    mysqlbinlog -vv --base64-output=decode-rows mysql-bin.XXX | more
    Note:
    • -vv: allows you to view SQL statements and remarks.
    • --base64-output=decode-rows: allows you to parse binary logs.
    The following figure shows the sample command output.
    155503646337626_zh-CN.png
  2. For more information about how to parse binary logs, see the MySQL official website.

References

The following describes two common problems encountered when using the mysqlbinlog tool:

  • If the following error content appears, check the version of the mysqlbinlog tool used. For example, if you encounter the following error with the 3.3 version, but this issue has been fixed in the 3.4 version, the 3.4 version can be viewed normally. Therefore, if this issue occurs, you can view it using the mysqlbinlog tool of the higher version.
    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.
  • --base64-output=decode-rows parameters are not used, resulting in the output being unresolved content.
    155503646337627_zh-CN.png

  • If the following error occurs, check whether the default-character-set=utf8mb4 field exists in the my.cnf configuration file. If so, you can avoid this problem by adding --no-defaults parameters to the command. 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'

Application scope

  • ApsaraDB RDS for MySQL