Enable binary logging

Pricing

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

Notes

• By default, binary logs are retained for two weeks and then automatically deleted. For more information, see FAQ.
• By default, the binary logging feature is disabled. To enable the binary logging feature, you must restart all the nodes in the cluster. This operation may interrupt your services. Before you enable the feature, make sure that your services are unaffected by the interruption.
• After the binary logging feature is enabled, the write performance is compromised. However, the read performance is unaffected. For more information, see FAQ.
• We recommend that you use the Primary Endpoint of PolarDB when you pull, subscribe to, or synchronize binary logs by using a tool 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 information about how to query the Primary Endpoint, see View an endpoint and port number.
• The loose_polar_log_bin parameter described in this topic is a global parameter. To use the session-level binary logging feature, you can specify the sql_log_bin parameter to enable the feature. For more information, see sql_log_bin.
  Note In most cases, the sql_log_bin parameter is used to temporarily disable the session-level binary logging feature. The default value is ON.

Procedure

1. Log on to the PolarDB console.
2. In the upper-left corner of the console, select the region where the cluster is deployed.
3. Find the cluster 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 icon in the Current Value column. In the dialog box that appears, enter a new value and click OK.
   

   Note

   • For a PolarDB cluster that uses the database engine of MySQL 5.6, enter ON_WITH_GTID.
   • For a PolarDB cluster that uses the database engine of 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 message, click OK.
   

   Note

   • After you perform the preceding operations, you must restart the cluster for the binary logging feature to take effect. We recommend that you restart the nodes during off-peak hours and make sure that your application can automatically reconnect to the cluster.
   • If the version of your ApsaraDB PolarDB MySQL-compatible edition cluster is 8.0, you can set the innodb_fast_startup parameter to ON to accelerate the restart.
   • If the error message Custins minor version does not support current action 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.
    • If the version of your ApsaraDB PolarDB MySQL-compatible edition cluster is 5.6, 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 2,376 hours. A value of 0 indicates that binary logs are not automatically deleted.
    • If the version of your ApsaraDB PolarDB MySQL-compatible edition cluster is 5.7 or 8.0, 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 4,294,967,295 seconds. A value of 0 indicates that binary logs are not automatically deleted.
  • 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 the loose_expire_logs_hours or binlog_expire_logs_seconds parameter to a small 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 The existing binary logs are not deleted after the binary logging feature is disabled. To save storage resources, we recommend that you set a shorter retention period for binary logs. After the binary logs that you no longer need are automatically deleted, you can disable the binary logging feature.
• How can I reduce the size of the binary logs?

  To reduce the size of the binlog file, you can set the loose_expire_logs_hours or binlog_expire_logs_seconds parameter to a smaller value.

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

  After you enable the binary logging feature, only the write performance is affected. For example, the performance of INSERT, UPDATE, and DELETE statements is affected after binary logging is enabled. However, The performance of SELECT statements is unaffected. Typically, the 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 require to restart the cluster?

  In most cases, it requires 5 minutes to restart the cluster. During the restart, the service may be disconnected for about 40 seconds. The required time depends on data volume and the number of tables. We recommend that you enable the feature during off-peak hours and make sure that your application can automatically reconnect to the cluster.

• How do I remotely retrieve and view the binary logs?

  For more information, see Remotely obtain and parse binary logs from a PolarDB for MySQL cluster.

• Why am I unable to use the Change schemas without locking tables feature of DMS to manage tables of a ApsaraDB PolarDB MySQL-compatible edition cluster, for example, to add an index to a table?

  By default, the binary logging feature of the ApsaraDB PolarDB MySQL-compatible edition cluster is disabled. To use DMS to change schemas without locking tables, you must first enable the binary logging feature for the ApsaraDB PolarDB MySQL-compatible edition cluster. If you do not want to enable the binary logging feature, you can execute DDL statements to manage tables.