PolarDB:Remotely obtain and parse binary log records from a cluster of the PolarDB for MySQL

Policies to obtain binary log records

Remotely obtain binary log records

1. Enable binary logging for a cluster of PolarDB for MySQL. For more information, see Enable binary logging.
   Note You must enable binary logging for the cluster before you view and obtain binary log records. Otherwise, the You are not using binary logging error message is returned.
2. Install MySQL on the local server.
3. Use the MySQL client to connect to your PolarDB cluster. For more information, see Use a client to connect to a cluster. In the example provided in this topic, a Linux OS is used.
4. To view the binary log records of the cluster, execute the following statement on the client that is connected to the PolarDB cluster:

   show binary logs;

   The following query result is returned:

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

5. Run the following command on the client to obtain binary log records and save the log records to your local server:
   The OS used in the following example is a Linux OS. Before you run the following command to obtain binary log records and save the log records 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******

   Example:

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

   Parameter	Description	Example value
   -u	The name of the account used to connect to the cluster of PolarDB for MySQL. For more information about how to create an account, see Create a database account.	test_api
   -p	The password of the account. If you do not specify the password, you are required to enter the password after you run the preceding command.	TestPwd123
   -h	The public endpoint of the cluster of PolarDB for MySQL. Note If the endpoint of the PolarDB for MySQL cluster uses the default port 3306, you do not need to specify the port number. Otherwise, you must specify a port number at the end of the specified value. You can connect only to the public primary endpoint or a public cluster endpoint to remotely obtain binary log records. The cluster endpoint can be the default cluster endpoint or a custom cluster endpoint. For more information about how to apply for a public endpoint, see Apply for a cluster endpoint or a primary endpoint.	test-polardb.rwlb.rds.aliyuncs.com
   --raw	Specifies that the binary log records are printed in the original format. The parsed data is not displayed.	--raw
   mysql-bin.******	The name of the binary log file that you obtain by executing the show binary log records; statement. The name is displayed in the Log_name field.	mysql-bin.000005

Use mysqlbinlog to view and parse binary log records

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

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

  Note

  • -vv: queries SQL statements and remarks.
  • --base64-output=decode-rows: parses the log records in the binary log files.

  The example provided in the following figure shows the details of binary log records.

  
• Parse binary log records

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

FAQ

• Why is the error message You are not using binary logging returned after I execute the show binary log records; statement?

  The error message is returned because the binary logging feature is not enabled. By default, the binary logging feature is disabled for your PolarDB cluster. To solve this issue, make sure that you enable binary logging. For more information, see Enable binary logging.

  Note After you enable or disable binary logging, the cluster automatically restarts. Then, the new parameter configuration takes 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 is one of the following error messages returned when I remotely obtain binary log records?

  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: Got error reading packet from server: '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 returned. Check the version of mysqlbinlog. Before you remotely obtain binary log records, we recommend that you update mysqlbinlog to a later version. For example, if the preceding error message is returned when you use V3.3, you can upgrade mysqlbinlog to V3.4.

• Why are binary log records that I obtain not parsed?

  If you do not specify the --base64-output=decode-rows parameter when you query binary log records, the returned binary log records are not parsed. Make sure that the --base64-output=decode-rows parameter is specified when you use mysqlbinlog to query binary log records.

  
• Why is the error message Only allow to dump binary log on primary instance returned when I query binary log records from read-only nodes?
  Make sure that your cluster meets one of the following requirements:

  • Your cluster of PolarDB for MySQL runs MySQL 8.0 and the revision version of your cluster is V8.0.1.1.12 or later.
  • Your cluster of PolarDB for MySQL runs MySQL 5.7 and the revision version of your cluster is V5.7.1.0.12 or later.

  Otherwise, you cannot obtain binary log records from read-only nodes. For more information about how to view the version and upgrade a cluster, see Version management.
• When I query binary log records from read-only nodes, why are the results different from those obtained from the primary node?

  PolarDB allows the primary node and read-only nodes to share binary log records. After the binary log records on the primary node are synchronized to read-only nodes, you can obtain the same binary log records from read-only nodes. If a sync latency occurs, read-only nodes may be unable to read the binary log records 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 are binary log records not deleted after the retention period specified by the binlog_expire_logs_seconds parameter expires?

  If a binary log record expires when the binary log record is being read by a thread from a read-only node, the binary log record is not immediately deleted. After new binary log records are written to the primary node, the system checks whether expired binary log records exist. The expired binary log records are deleted if they are not being accessed by threads of read-only nodes.