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
RDS instances that use cloud disks: After you enable the log backup feature for an RDS instance that uses cloud disks, binary logs are uploaded or replicated to the backup storage in real time and stored as log backup files. The log backup feature is enabled by default. You can download binary logs at the specific point in time. For more information, see the RDS instances that use cloud disks table in Download methods.
RDS instances that use local disks: For more information about how to download the binary logs of the instances, see the RDS instances that uses local disks table in Download methods.
Note
For more information about how to enable the log backup feature, see Procedure.
Method 2: Remotely obtain the binary log file
Connect a client to the RDS instance.
Important
We recommend that you use a client that runs the same MySQL version as that of the RDS instance from which you want to obtain binary log files.
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.
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)
Use the name of the binary log file that is obtained in the previous step to remotely obtain the binary log file and save the file to your on-premises device.
Run the following command to exit the MySQL CLI and go to the operating system:
Run the following command 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>
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:
Use mysqlbinlog to view the binary log file
Log on to the operating system in which the MySQL client is installed and run the following command to use mysqlbinlog to view the content of the binary log file:
Important
We recommend that you use a client that runs the same MySQL version as that of the RDS instance from which you want to obtain binary log files.
mysqlbinlog -vv --base64-output=decode-rows <mysql-bin.XXX> | more
You can write the content of the parsed binary log file to a file for viewing.
mysqlbinlog -vv --base64-output=decode-rows <mysql-bin.XXX> > [$File_Name]
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. |
> [$File_Name]
| The name of the local file to which you want to write the content of the parsed binary log file. |
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
Elastic Compute Service (ECS)
Lingma
