This topic describes how to remotely obtain binary logs from ApsaraDB PolarDB MySQL-compatible edition and parse binary logs by using mysqlbinlog.

Policies to obtain binary logs

Endpoint Policy
  • The primary endpoint
  • The default cluster endpoint
  • Custom cluster endpoints that contain the primary node.
Obtain binary logs from the primary node.
Note For more information about how to set cluster endpoints, see Manage a cluster endpoint.
Custom cluster endpoints that contain only read-only nodes. Obtain binary logs from any of the read-only nodes.
Note The version of the cluster must be ApsaraDB PolarDB MySQL-compatible edition 8.0 and the revision version of the cluster must be 8.0.1.1.12 or later. Otherwise, the error message Only allow to dump binary log on primary instance is returned.

For more information about how to view the version and upgrade a cluster, see Query the kernel version number and Upgrade versions.

Remotely obtain binary logs

  1. Enable binary logging for a ApsaraDB PolarDB MySQL-compatible edition cluster. For more information, see Enable binary logging.
    Note You must enable binary logging for the cluster before you view and obtain binary logs. Otherwise, the error message You are not using binary logging is returned.
  2. Install MySQL on the on-premises machine.
  3. Use the MySQL client to connect to the PolarDB cluster. For more information, see Use a client to connect to a PolarDB for MySQL cluster. In this topic, a Linux operating system is used. 1
  4. To view the binary logs of the cluster, run the following command on the client that is connected to the PolarDB cluster:
    show binary logs;
    The following result is returned:
    +------------------+-----------+
    | Log_name         | File_size |
    +------------------+-----------+
    | mysql-bin.000005 |      2639 |
    +------------------+-----------+
    1 row in set (0.00 sec)
  5. To obtain binary logs and save these logs to the on-premises machine, run the following command on the client.
    In this topic, a Linux operating system is used. To obtain binary logs and save these logs to the on-premises machine, you must run the EXIT command to exit MySQL before you run the following command. 2
    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
    -u The account name of the ApsaraDB PolarDB MySQL-compatible edition cluster. 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 a password, the password will be required after you run this command. TestPwd123
    -h The public endpoint of the ApsaraDB PolarDB MySQL-compatible edition cluster.
    Note
    • If the endpoint of the ApsaraDB PolarDB MySQL-compatible edition cluster uses the default port number 3306, you do not need to specify the port number. Otherwise, you must specify a port number after the endpoint.
    • You can connect to only the public-facing endpoints of the primary endpoint or cluster endpoints to remotely obtain binary logs. Cluster endpoints include the default cluster endpoint and custom cluster endpoints. For more information about how to apply for a public endpoint, see Apply for an endpoint.
    test-polardb.rwlb.rds.aliyuncs.com
    --raw You can specify this parameter to print binary logs in the original format. The parsed data is not displayed. --raw
    mysql-bin.****** The name of the binary log that you obtain by running the show binary logs; command. The name is displayed in the Log_name field. mysql-bin.000005

Use mysqlbinlog to view and parse binary logs

  • To view the details of binary logs by using mysqlbinlog, run the following command:
    mysqlbinlog -vv --base64-output=decode-rows mysql-bin.****** | 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 details of a binary log.

    3
  • Parse binary logs

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

FAQ

  • Why is the error message You are not using binary logging returned after I run the show binary logs; command?

    By default, the binary logging feature is disabled for the PolarDB cluster. Make sure that you have enabled binary logging. For more information, see Enable binary logging.

    Note After binary logging is enabled or disabled, the cluster automatically restarts for new settings 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 does the following error occur when I remotely obtain binary logs?
    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 outdated, one of the preceding error messages may be returned. Check the version of mysqlbinlog. Before you remotely obtain binary logs, we recommend that you upgrade the version of mysqlbinlog. For example, if the preceding error message is returned when you use version 3.3, you can upgrade mysqlbinlog to version 3.4.

  • Why are binary logs that I obtain not parsed?

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

    3
  • Why is the error message Only allow to dump binary log on primary instance returned when I query binary logs from read-only nodes?

    You can query binary logs from read-only nodes only when the version of the cluster is ApsaraDB PolarDB MySQL-compatible edition 8.0 and the revision version of the cluster is 8.0.1.1.12 or later. For more information about how to view the version and upgrade the cluster, see Query the kernel version number and Upgrade versions.

  • When I query binary logs from read-only nodes, why are the results different from those of the primary node?

    PolarDB allows the primary node and read-only nodes to share binary logs. After the binary logs on the primary node are synchronized to read-only nodes, you can obtain the same binary logs from read-only nodes. If a synchronization delay occurs, read-only nodes may be unable to read the binary logs that are newly written to the primary node. Typically, the latency is low when the size of source data is small.

  • Why are binary logs not deleted after the retention period specified by the binlog_expire_logs_seconds parameter expires?

    If a thread from read-only nodes is reading binary logs, the binary logs are immediately deleted. After new binary logs are written to the primary node, the system checks whether expired binary logs exist. The expired binary logs are deleted if they are not queried by threads of read-only nodes.