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.
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
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 thelogs
table. The value is the name of the binary log file, such asmysql-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 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.
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 |
| The output details, including details about the events obtained from the parsing. |
| Specifies that Base64 encoding is used to record the binary log entries. |
| The name of the binary log file that needs to be parsed. Replace |
| Specifies that a pipeline is used to send the output to the |
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.If the following error occurs, check whether the
default-character-set=utf8mb4
parameter exists in themy.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