Enable binary logging - PolarDB - Alibaba Cloud Documentation Center

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

Background information

PolarDB for MySQL 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 for MySQL 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 for MySQL cluster was created after April 5, 2019, you can directly enable the binary logging feature. If your PolarDB for MySQL 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.
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 for MySQL 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 for MySQL, the binary logging feature is automatically enabled.

Enable the binary logging feature for existing clusters

Log on to the PolarDB console.
In the upper-left corner of the console, select the region where the cluster is deployed.
Find the cluster and click the cluster ID.
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.
  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 PolarDB for MySQL 8.0 and 5.6 clusters, 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.

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. The default value is 336. Unit: 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. The default value is 1209600. Unit: 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 are 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 value of max_binlog_size.
  - 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.
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.