All Products
Search
Document Center

PolarDB:Enable binary logging

Last Updated:Feb 21, 2024

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 cluster to an ApsaraDB for RDS instance, from an ApsaraDB for RDS instance to a PolarDB cluster, or between PolarDB 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 Minor version update.

  • 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 storage resources that you use. For more information, see Overview.

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.

  • 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. In extreme cases, performance degradation may be up to 40%. For more information, see FAQ.

  • We recommend that you use the Primary Endpoint of the PolarDB cluster 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 the endpoint and port number.

  • 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 the feature, go to the Quota Center page. In the Actions column of polardb SQL_log_bin parameter permissions, click Apply.

  • If you use DTS to migrate data from ApsaraDB RDS to PolarDB, the binary logging feature is automatically enabled.

Retention periods for binary log files

Binary log files are retained based on the following policies:

  • By default, binary logs are retained for three days, after which they are automatically deleted.

    Note
    • For a PolarDB for MySQL cluster that was purchased before November 23, 2023, binary log files are retained for two weeks (14 days) by default.

    • For a PolarDB for MySQL cluster that was purchased before January 17, 2024, binary log files are retained for one week (7 days) by default.

    You can use one of the following methods to modify the retention period of binary log files:

    • If your PolarDB cluster runs MySQL 5.6, you can adjust the loose_expire_logs_hours parameter to configure a proper retention period for your binary logs. The value range of this parameter is [0, 2376], measured in hours. Its default value is 72. The value 0 indicates that binary log files are not automatically deleted.

    • If your PolarDB cluster runs MySQL 5.7 or 8.0, you can adjust the binlog_expire_logs_seconds parameter to configure a proper retention period for your binary logs. The value range of this parameter is [0, 4294967295], measured in seconds. Its default value is 259200. The value 0 indicates that binary log files are not automatically deleted.

    Note

    After you set the retention period of binary log files by modifying the loose_expire_logs_hours or binlog_expire_logs_seconds parameter, historical binary log files in the cluster are not automatically deleted. To clear historical binary log files, you can use one of the following methods:

    • Historical binary log files in the cluster are cleared 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 log files.

    • You can restart the cluster. Historical binary log files are cleared after the cluster is restarted.

  • After the binary logging feature is disabled, existing binary log files are permanently retained.

  • Note

    To delete the binary log files, you can enable the feature and set the loose_expire_logs_hours or binlog_expire_logs_seconds parameter to a smaller value. After the retention period is reached, binary log files are automatically deleted. Then, you can disable the binary logging feature.

Enable binary logging

Enable the binary logging feature for existing clusters

  1. Log on to the PolarDB console.

  2. In the upper-left corner of the console, select the region in which the cluster that you want to manage is deployed.

  3. Find the cluster and click the cluster ID.

  4. You can use one of the following methods to enable the binary logging feature:

    • Method 1: Click Enable binary logging to enable binary logging.

      1. Click Enable binary logging in the upper-right corner.

        image.png

      2. In the Enable binary logging dialog box, click Enable.

      3. In the Notice message, click OK.

    • Method 2: Configure the loose_polar_log_bin parameter to enable binary logging.

      1. In the left-side navigation pane, choose Settings and Management > Parameters.

      2. Find the loose_polar_log_bin parameter and modify the parameter value. For more information, see Modify parameters.

        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.

After you enable the binary logging feature, the cluster automatically restarts. The binary logging feature takes effect after the restart is complete. A cluster restart task is typically completed within 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.

Note

For a PolarDB for MySQL cluster that runs MySQL 8.0 or MySQL 5.6, you can set the innodb_fast_startup parameter to ON to accelerate the restart process.

Enable the binary logging feature when you create a new cluster

When you create a cluster, select Enable in Enable Binary Logging section on the Cluster Configurations page. For more information, see Purchase a pay-as-you-go cluster and Purchase a subscription cluster.

Obtain and view binary logs

You can use the mysqlbinlog tool to view and parse binary logs. For more information, see Remotely obtain and parse binary logs of a PolarDB for MySQL cluster.

FAQ

  • How long can binary log files be retained?

    • By default, binary logs are retained for three days, after which they are automatically deleted.

      Note
      • For a PolarDB for MySQL cluster that was purchased before November 23, 2023, binary log files are retained for two weeks (14 days) by default.

      • For a PolarDB for MySQL cluster that was purchased before January 17, 2024, binary log files are retained for one week (7 days) by default.

    • After the binary logging feature is disabled, existing binary log files are permanently retained.

    Note

    You can modify the parameters to modify the retention period of binary log files for a cluster. For more information, see Retention periods for binary log files.

  • 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 log files are permanently retained. To delete the binary log files, you can enable the feature and specify a short retention period. After the retention period is reached, binary log files 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. In extreme cases, performance degradation may be up to 40%.

  • 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 logs of a PolarDB for MySQL cluster.

  • Why am I unable to use the Perform lock-free DDL operations feature of Data Management (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.

  • Can I use Canal to collect changes from PolarDB for MySQL clusters after binary logging is enabled?

    Yes, you can use Canal to collect changes from PolarDB for MySQL clusters.