ApsaraDB RDS for MySQL remotely obtains and parses Binlog logs -

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

Note

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.

Procedure

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

Use a client to connect to an instance. For more information, see Connect to an instance.
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.
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.
Run the following command to check whether the binary log is remotely obtained:
```
more [$File_Name]
```
The following figure shows the sample command output.

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

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

ApsaraDB RDS for MySQL