This topic describes how to enable the binary logging feature for a PolarDB cluster.

Background information

PolarDB is a cloud-native database that is compatible with MySQL. By default, PolarDB uses redo logs, which are more advanced than binary logs. However, to better integrate with the MySQL ecosystem, PolarDB allows you to enable the binary logging feature. After you enable the binary logging feature, you can access data services, such as Elasticsearch and AnalyticDB. You can also replicate data from a PolarDB for MySQL cluster to an ApsaraDB for RDS instance, from an ApsaraDB for RDS instance to a PolarDB for MySQL cluster, or between PolarDB for MySQL clusters.

Limits

  • If your PolarDB cluster was created after April 5, 2019, you can directly enable the binary logging feature. If your PolarDB cluster was created before April 5, 2019, upgrade the minor version of your cluster to the latest version before you enable the binary logging feature. For more information about how to upgrade the minor version, see Version Management.
  • The binary logging feature cannot be enabled for secondary clusters in a global database network (GDN).

Billing

The storage that is used to store binary logs is a part of the cluster storage. You are charged for the actual storage resources that you use. For more information, see Storage pricing.

Precautions

  • By default, the binary logging feature is disabled. After you enable the binary logging feature, the cluster automatically restarts. A cluster restart task is typically completed in five minutes. During the restart, services may be interrupted for up to 40 seconds. The length of recovery time depends on the data volume and the number of tables. We recommend that you perform this operation during off-peak hours and make sure that your application is configured to automatically reconnect to the database service.
  • By default, binary logs are retained for two weeks and then automatically deleted. For more information, see FAQ.
  • When the binary logging feature is enabled, the write performance of your cluster is deteriorated while the read performance is not affected. Typically, your cluster may lose less than 10% of the performance after you enable the binary logging feature. For more information, see FAQ.
  • We recommend that you use the Primary Endpoint of PolarDB when you pull, subscribe to, or replicate binary logs by using services such as Data Transmission Service (DTS). This ensures compatibility and stability because the primary endpoint points to the primary node that generates binary logs. For more information about how to query the Primary Endpoint, see View an endpoint.
  • After you enable the binary logging feature, committing large transactions may block the submission of other transactions and affect the restart and configuration change duration of the cluster.
  • The loose_polar_log_bin parameter described in this topic is a global parameter. To enable the session-level binary logging feature, you can specify the sql_log_bin parameter.
    Note The sql_log_bin parameter is used to enable or disable the session-level binary logging feature. By default, the session-level binary logging feature is disabled. To enable this feature, submit a ticket to contact technical support.

Procedure

  1. Log on to the PolarDB console.
  2. In the upper-left corner of the console, select the region where the cluster that you want to manage is deployed.
  3. Find the cluster you want to manage and click the cluster ID.
  4. In the left-side navigation pane, choose Settings and Management > Parameters.
  5. Find the loose_polar_log_bin parameter and click the No. icon in the Current Value column. In the dialog box that appears, enter a new value and click OK.
    1
    Note
    • For a PolarDB for MySQL cluster that runs MySQL 5.6, enter ON_WITH_GTID.
    • For a PolarDB for MySQL cluster that runs MySQL 5.7 or MySQL 8.0, enter ON.
  6. In the upper-left corner of the page, click Apply Changes. In the Save Changes dialog box, click OK.
    2
    Note
    • After you enable the binary logging feature, the cluster automatically restarts. The binary logging feature takes effect after the restart is complete. A restart task is typically completed in five minutes. During the restart, services may be interrupted for up to 40 seconds. The length of recovery time depends on the data volume and the number of tables. We recommend that you perform this operation during off-peak hours and make sure that your application is configured to automatically reconnect to the database service.
    • For PolarDB for MySQL 8.0 and 5.6 clusters, you can set the innodb_fast_startup parameter to ON to accelerate the restart process.
    • If the "Custins minor version does not support current action" message is returned, submit a ticket to enable the binary logging feature.

FAQ

  • How long can binary logs be stored?
    Binary logs are stored based on the following policies:
    • By default, binary logs are retained for two weeks and then automatically deleted.
      • For PolarDB for MySQL 5.6 clusters, you can change the value of the loose_expire_logs_hours parameter to specify the retention period of binary logs. The valid values of this parameter range from 0 to 2376. Unit of valid values: hours. The value 0 indicates that binary logs are not automatically deleted.
      • For PolarDB for MySQL 5.7 or 8.0 clusters, you can change the value of the binlog_expire_logs_seconds parameter to specify the retention period of binary logs. The valid values of this parameter range from 0 to 4294967295. Unit of valid values: seconds. The value 0 indicates that binary logs are not automatically deleted.
      • After you set the retention period of binary logs by modifying the loose_expire_logs_hours or binlog_expire_logs_seconds parameter, historical binary log files in the cluster are not automatically deleted. Historical binary log files in the cluster will be cleared after you restart the cluster or after a new binary log file is used when the last binary log file in the cluster reaches the max_binlog_size value.
      • You can also execute the flush binary logs statement by using the privileged account to immediately use a new binary log file and clear expired binary logs.
    • After the binary logging feature is disabled, existing binary logs are permanently retained.
      Note To delete the binary logs, you can enable the feature and set loose_expire_logs_hours or binlog_expire_logs_seconds to a smaller value. After the retention period is reached, binary logs are automatically deleted. Then, you can disable the binary logging feature.
  • Can I disable the binary logging feature?

    Yes, the binary logging feature is disabled after you change the value of the loose_polar_log_bin parameter to OFF and submit the modification.

    Note After the binary logging feature is disabled, existing binary logs are permanently retained. To delete the binary logs, you can enable the feature and specify a short retention period. After the retention period is reached, binary logs are automatically deleted. Then, you can disable the binary logging feature.
  • How can I reduce the storage space occupied by binary logs?

    You can set loose_expire_logs_hours or binlog_expire_logs_seconds to a smaller value to reduce the storage space occupied by binary logs.

  • How is the performance of a cluster affected after the binary logging feature is enabled?

    After you enable the binary logging feature, only the write performance is affected, such as the performance of INSERT, UPDATE, and DELETE statements. However, the performance of SELECT statements is unaffected. Typically, your cluster may lose less than 10% of the performance after you enable the binary logging feature.

  • After binary logging is enabled, the cluster automatically restarts. How long does it take to restart the cluster?

    In most cases, it takes five minutes to restart the cluster. During the restart, services may be interrupted for up to 40 seconds. The length of recovery time depends on the data volume and the number of tables. We recommend that you perform this operation during off-peak hours and make sure that your application is configured to automatically reconnect to the database service.

  • How do I remotely retrieve and view binary logs?

    For more information, see Remotely obtain and parse binary log records from a cluster of the PolarDB for MySQL.

  • Why am I unable to use the Perform lock-free DDL operations feature of Data Management Service (DMS) to manage tables of a PolarDB for MySQL cluster, for example, to add an index to a table?

    By default, the binary logging feature of the PolarDB for MySQL cluster is disabled. To use DMS to change schemas without locking tables, you must first enable the binary logging feature for the PolarDB for MySQL cluster. If you do not want to enable the binary logging feature, you can execute data definition language (DDL) statements to change table schemas.