Remotely obtain and parse the binary log files of a PolarDB for MySQL cluster

Policies to obtain binary log files

Remotely obtain binary log files

1. Enable binary logging for the PolarDB for MySQL cluster. For more information, see Enable binary logging.

   Note

   To access and obtain the binary log files of a PolarDB for MySQL cluster, you must first enable binary logging for the cluster. If binary logging is disabled, the You are not using binary logging error message is displayed when you attempt to perform binary log-related operations.

2. Install a MySQL client on your local server.

3. Connect to the PolarDB cluster from the MySQL client. For more information, see Database connection. In the following example, a Linux operating system is used.

4. Execute the following statement on the MySQL client to view the binary log files of the PolarDB cluster:

   show binary logs;

   Sample result:

   +------------------+-----------+
   | Log_name         | File_size |
   +------------------+-----------+
   | mysql-bin.000005 |      2639 |
   +------------------+-----------+
   1 row in set (0.00 sec)

5. Run the following command on the MySQL client to obtain binary log files and save the files to your server:

   In this example, the MySQL client runs on a Linux operating system. Before you run the following command to obtain binary log files and save the files to the local server, you must run the exit command to exit MySQL.

   mysqlbinlog -u<Username> -p<Password> -h<Endpoint> --read-from-remote-server --raw mysql-bin.******

   Sample command:

   mysqlbinlog -utest_api -p -htest-polardb.rwlb.rds.aliyuncs.com --read-from-remote-server --raw mysql-bin.000005

   Parameter	Description	Example

   Parameter	Description	Example
   -u	The database account name of the PolarDB for MySQL cluster. For more information about how to create a database account, see Create and manage a database account.	test_api
   -p	The password of the database account. If you do not specify the password in the command, you are required to enter the password after you run the command.	TestPwd123
   -h	The private or public endpoint of the PolarDB for MySQL cluster. Note If the endpoint of your cluster uses the default port number, you do not need to specify the port number. Otherwise, you must specify a port number after the endpoint. If the PolarDB for MySQL cluster runs in the same region and virtual private cloud (VPC) as your server, select a private endpoint. For example, if your server and the cluster that you want to access reside in the China (Hangzhou) region and run in the same VPC, we recommend that you use a private endpoint for connection. This ensures a secure and efficient access experience. In other scenarios, use a public endpoint. For more information about how to apply for a public endpoint, see Manage the endpoints of a cluster.	test-polardb.rwlb.rds.aliyuncs.com
   --raw	Specifies that the data in the binary log files are displayed in their original, unprocessed format.	--raw
   mysql-bin.******	The name of the binary log file that is obtained when you execute the SHOW BINARY LOGS; statement. The name is displayed in the Log_name field.	mysql-bin.000005

Use mysqlbinlog to view and parse binary log files

• Run the following command to view the binary log files by using mysqlbinlog:

  mysqlbinlog -vv --base64-output=decode-rows mysql-bin.****** | more

  Note

  • -vv: displays detailed information about the executed SQL statements and other changes.

  • --base64-output=decode-rows: parses the binary log files.

  • We recommend that you use the same MySQL version as that of the database engine of the PolarDB for MySQL cluster to avoid issues such as failures in parsing binary log files.

  The example provided in the following figure shows the detailed information of binary log files.

  

• Parse binary log files

  For information about how to parse binary log files, see Parse binary log files.

FAQ

• Why is the You are not using binary logging error message displayed after I execute the SHOW BINARY LOGS; statement?

  By default, binary logging is disabled for PolarDB clusters. Make sure that you enable binary logging for your cluster.

  Note

  After you enable or disable binary logging, the cluster automatically restarts for the configuration to take effect. We recommend that you enable or disable binary logging during off-peak hours and make sure that your applications can automatically reconnect to the cluster.

• Why do I fail to remotely obtain binary log files? Why is one of the following error messages displayed?

  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.
  ERROR: Goterrorreadingpacketfromserver: 'Slave can not handle replication events with the checksum that master is configured to log; 

  If the version of mysqlbinlog is earlier than the required version, one of the preceding error messages may be displayed. Check the version of mysqlbinlog. Before you attempt to remotely obtain binary log files, we recommend that you update mysqlbinlog to a later version. For example, if one of the preceding error messages is returned when you use V3.3, you can upgrade mysqlbinlog to V3.4.

• Why do I fail to remotely obtain binary log files? Why is the following error message displayed?

  ERROR: Failed on connect: Lost connection to MySQL server at 'reading initial communication packet', system error: 104

  This error message usually indicates that a connection to the MySQL server cannot be established. You can troubleshoot the issue from the following aspects:

  • Network connectivity. We recommend that you check whether the network connection is normal and ensure that you can access your PolarDB cluster.

  • Cluster whitelist settings. We recommend that you check whether the local IP address is added to the whitelists of your cluster. For more information about how to check whether the local IP address is added to the whitelists of a cluster, see Configure an IP whitelist.

  • Database settings. We recommend that you check whether your local MySQL server can be connected to databases and ensure you can successfully access the databases.

• Why are the binary log files that I obtain not parsed?

  If you do not configure the --base64-output=decode-rows parameter when you use mysqlbinlog to view binary log files, the returned binary log files are not parsed. Make sure that you configure the --base64-output=decode-rows parameter when you use mysqlbinlog to view binary log files.

  

• Why is the Only allow to dump binary log file on primary instance error message displayed when I attempt to obtain binary log files from a read-only node?

  You can obtain binary log files from a read-only node only when your cluster runs one of the following database engine versions:

  • PolarDB for MySQL 8.0 whose revision version is 8.0.1.1.12 or later.

  • PolarDB for MySQL 5.7 whose revision version is 5.7.1.0.12 or later.

  Otherwise, you cannot obtain binary log files from read-only nodes. For more information about how to view and update the database engine version of a cluster, see Minor version update.

• Why are the binary logs obtained from a read-only node sometimes inconsistent with the binary logs retrieved from the primary node?

  In a PolarDB cluster that consists of a primary node and read-only nodes, the primary node synchronizes binary log files to the read-only nodes to ensure data consistency. If a sync latency occurs, read-only nodes may be unable to read the binary log files that are newly written to the primary node. In most cases, the sync latency is low when the size of source data is small.

• Why is a binary log file not deleted after the retention period of the file specified by the binlog_expire_logs_seconds parameter elapses?

  If a binary log file expires while being read by a thread from a read-only node, the binary log file is not immediately deleted. After a new binary log file is written to the primary node, the system checks whether expired binary log files exist. If the expired binary log files are not being accessed by threads of read-only nodes, the expired binary log files are deleted.