Enable binary logging for a PolarDB cluster - PolarDB - Alibaba Cloud Documentation Center

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

Background

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, PolarDB allows you to enable the binary logging feature for better compatibility with the MySQL ecosystem. After you enable the binary logging feature, you can connect the database to data products such as Elasticsearch and AnalyticDB to facilitate data query and analytics. You can also synchronize data from a PolarDB for MySQL cluster to an ApsaraDB RDS for MySQL instance, from an ApsaraDB RDS for MySQL instance to a PolarDB for MySQL cluster, or between PolarDB for MySQL clusters.

Limits

If your PolarDB cluster was created on or after April 5, 2019, you can directly enable the binary logging feature. If your PolarDB cluster was created before April 5, 2019, update the minor version of your cluster to the latest version before you enable the binary logging feature. For information about how to update the minor version, see Minor version update.

Billing

Binary logs occupy cluster storage space. You are charged for the storage resources that you use. For more information, see Overview of storage pricing.

Usage notes

By default, the binary logging feature is disabled. After you enable the binary logging feature, the cluster automatically restarts. In most cases, a cluster restart task requires 5 minutes to complete. During the cluster restart, services may be interrupted for approximately 40 seconds. The length of recovery time varies based 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 can automatically reconnect to the database service.
Enabling the binary logging feature adversely affects the write performance of your cluster. However, the read performance is not affected. In most cases, the impact on performance is within 10%, but may increase to 40% under extreme workloads. For more information, see the FAQ section of this topic.
When you pull, subscribe to, or replicate binary logs by using services such as Data Transmission Service (DTS), we recommend that you use the Primary Endpoint of the PolarDB cluster. This ensures compatibility and stability because the primary endpoint points to the primary node that generates binary logs. For information about how to query the Primary Endpoint, see the "View the endpoint and port" section of the Manage the endpoints of a cluster topic.
After you enable the binary logging feature, committing a large transaction may block the commission 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, configure the sql_log_bin parameter.
Note
You can use the sql_log_bin parameter to enable or disable the session-level binary logging feature. By default, this feature is disabled. To enable the feature, go to Quota Center, find the polardb SQL _log_bin parameter permissions quota name, and then click Apply in the Actions column.
If you use DTS to migrate data from an ApsaraDB RDS instance to a PolarDB cluster, the binary logging feature is automatically enabled.

Enable binary logging

Enable the binary logging feature for existing clusters

Important

After you enable the binary logging feature for a cluster, the cluster automatically restarts. In most cases, a cluster restart task requires 5 minutes to complete. During the cluster restart, services may be interrupted for approximately 40 seconds. The length of recovery time varies based 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 can automatically reconnect to the cluster.

Log on to the PolarDB console.
In the upper-left corner of the console, select the region in which the cluster that you want to manage is deployed.
Find the cluster and click the cluster ID.
Enable the binary logging feature by using one of the following methods:
- Method 1:
  1. In the left-side navigation pane of the cluster, click Binary Logs.
  2. Click Enable Now.
  3. In the Enable Binary Logging dialog box, set the Effective Method parameter to Now or Scheduled.
    If you select Scheduled, specify the point in time at which you want to enable the binary logging feature.
  4. Click OK.
- Method 2: Configure the loose_polar_log_bin parameter to enable binary logging.
  1. In the left-side navigation pane of the cluster, choose Settings and Management > Parameters.
  2. Find the loose_polar_log_bin parameter and change the parameter value. For more information, see the "Modify parameters" section of the Configure cluster and node parameters topic.
    Note
    For a PolarDB for MySQL cluster that runs MySQL 5.6, change the parameter value to ON_WITH_GTID.
    For a PolarDB for MySQL cluster that runs MySQL 5.7 or MySQL 8.0, change the parameter value to ON.

Enable the binary logging feature when you create a cluster

To enable the binary logging feature when you create a cluster, select Enable in the Enable Binary Logging section. For more information, see Purchase an Enterprise Edition cluster and Purchase a subscription cluster.

Retain binary log files

Retention policies

The following retention policies are supported for binary log files:

If the binary logging feature is enabled, binary log files are retained for three days by default. The binary log files whose retention period exceeds three days 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 (seven days) by default.

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

Modify the retention period

Important

Changing the retention period of binary log files does not disrupt connections or require a cluster restart.

However, if a large volume of binary log files such as 10 TB needs to be purged due to a change in retention period, brief exceptions may occur during write operations. If a large volume of binary logs exists, we recommend that you change the retention period during off-peak hours. You can also progressively shorten the retention period. This way, only a part of binary logs is cleared each time. This mitigates the impact on your business operations.

If the binary logging feature is enabled for your cluster, perform the following operations to change the retention period of binary logs:
- PolarDB for MySQL V5.6 cluster: Change the value of the loose_expire_logs_hours parameter. Valid values of this parameter: 0 to 2376. Unit: hours. Default value: 72. A value of 0 specifies that binary log files are not automatically deleted.
- PolarDB for MySQL 5.7 or 8.0 cluster: Change the value of the binlog_expire_logs_seconds parameter. Valid values of this parameter: 0 to 4294967295. Unit: seconds. Default value: 259200. A value of 0 specifies that binary log files are not automatically deleted.
  Note
  After you change the values of these two parameters, historical binary logs in your cluster are not immediately cleared. You can use one of the following methods to trigger immediate purging:
  - Wait for the rotation of binary log files. When the active binary log file reaches the maximum size set by max_binlog_size, a new binary log file is created and all historical binary log files are automatically deleted.
  - Use the privileged account to run the flush binary logs command.
  - Restart the cluster. Historical binary log files are cleared after the cluster restarts.
If the binary logging feature is disabled for your cluster and you want to delete binary log files, perform the following steps: Enable the binary logging feature for your cluster. Set the loose_expire_logs_hours or binlog_expire_logs_seconds parameter to a smaller value, and wait for binary logs to expire and be automatically purged. Then, disable the binary logging feature.

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 are binary log files retained?
- If you enable the binary logging feature for your cluster, binary log files are retained for three days by default. Binary log files whose retention period exceeds three days 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 (seven days) by default.
- If you disable the binary logging feature for your cluster, existing binary log files are permanently retained.
Note
You can modify the parameters to change the retention period of binary log files for a cluster. For information about how to change the retention period of binary log files, see the Binary log retention section of this topic.
Can I disable the binary logging feature?
Yes, you can disable the binary logging feature. Set the loose_polar_log_bin parameter to OFF to disable the feature.
Note
After you disable the binary logging feature, existing binary log files are permanently retained. Before you disable this feature, we recommend that you shorten the retention period of binary logs. This way, binary logs that you no longer require are cleared.
How do I reduce the storage space occupied by binary logs?
You can set the loose_expire_logs_hours or binlog_expire_logs_seconds parameter to a smaller value to reduce the storage space occupied by binary logs.
How is the performance of a cluster affected after I enable the binary logging feature?
After you enable the binary logging feature, only the performance of write operations such as INSERT, UPDATE, and DELETE is affected. The performance of SELECT statements is unaffected. In most cases, the impact of the binary logging feature on performance is within 10%, but may increase to 40% under extreme workloads.
After the binary logging feature is enabled, the cluster automatically restarts. How long does it take for the restart process to complete?
In most cases, the restart task requires 5 minutes to complete. During the cluster restart, services may be interrupted for approximately 40 seconds. The length of recovery time varies based 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 can 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 perform lock-free DDL operations by using DMS to modify table schemas such as adding an index?
To change table schemas by using DMS without locking tables, you must first enable the binary logging feature. By default, the feature is disabled. If you do not want to enable the binary logging feature, you can change table schemas by using online DDL.
Can I use Canal to capture changes made to PolarDB for MySQL clusters after I enable the binary logging feature?
Yes, you can use Canal to capture changes made to PolarDB for MySQL clusters after you enable the binary logging feature.
If I execute the SHOW BINARY LOGS statement to query binary log file size, will the cluster performance be affected?
No. The SHOW BINARY LOGS statement is a database management operation that has minimal impact on the cluster performance. No data is written to the database after you execute the statement. Therefore, the read and write performance of the database is not affected like data write operations, such as the INSERT, UPDATE, and DELETE statements.