Enable binary logging

Limits

• If your PolarDB cluster was created on or after April 5, 2019, you can enable binary logging directly. If your PolarDB cluster was created before April 5, 2019, you must upgrade the cluster's minor version to the latest version before you can enable binary logging. For more information about how to upgrade the minor version, see Version management.

Billing

Storage for binary logs is part of the cluster's storage space and is subject to storage fees. You can manage the storage space used by binary log files by setting the binary log retention period.

Billing examples

Usage notes

• Binary logging is disabled by default. After you enable binary logging, the cluster automatically restarts. The restart task usually completes within 5 minutes. During the restart, a transient disconnection of about 40 seconds occurs. The exact duration depends on the data volume and the number of tables. Perform this operation during off-peak hours and ensure your application has a reconnection mechanism.

• Enabling binary logging reduces write performance but does not affect read performance. Typically, the performance impact is less than 10%. Under extreme stress, the performance impact can reach 40%. For more information, see FAQ.

• When you pull, subscribe to, or synchronize binary logs, for example, using a tool such as DTS, use the Primary Endpoint of the PolarDB cluster. The primary endpoint points directly to the primary node that generates binary logs, which provides better compatibility and stability. For more information about how to view the Primary Endpoint, see View connection addresses and ports.

• After you enable binary logging, committing a large transaction can block other transactions from committing. This may also affect the duration of cluster restarts and specification changes.

• The loose_polar_log_bin parameter described in this topic is a global-level parameter. To use the session-level binary logging feature, you must enable the sql_log_bin parameter.

  Note

  The sql_log_bin parameter controls whether to enable session-level binary logging. By default, this parameter is disabled. To enable it, go to the Quota Center page. Find the polardb sql_log_bin parameter permissions quota and click Apply in the Actions column.

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

Enable binary logging

Disable binary logging

1. Log on to the PolarDB console, click Clusters in the navigation pane on the left, select the Region where the cluster is deployed, and then click the cluster ID to open the cluster details page.

2. You can disable binary logging in one of the following ways:

   • Method 1:

     1. Go to the Binlog Management page of the cluster.

        

     2. Click the Disable Binlog button.

     3. In the Disable Binlog dialog box, set the effective time to Effective Immediately or Scheduled Time.

        If you select Scheduled Time, specify the effective time.

        If needed, select the Immediately purge local binlogs check box.

        

     4. Click OK.

   • Method 2: Disable binary logging by setting the value of the loose_polar_log_bin parameter.

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

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

        Note

        • If your PolarDB for MySQL cluster runs MySQL 5.6, change the current value to OFF_WITH_GTID.

        • If your PolarDB for MySQL cluster runs MySQL 5.7 or MySQL 8.0, change the current value to OFF.

        • After you disable binary logging, existing binary log files are retained permanently. If you want to delete them, shorten the retention period and wait for the files to be automatically deleted before you disable binary logging.

Binary log file retention period

Obtain and view binary logs

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

FAQ

• Q: How long are binary log files retained?

  A: If binary logging is enabled for your cluster, binary log files are retained for 3 days by default. Binary log files older than 3 days are automatically deleted.

  Note

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

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

  If binary logging is disabled for your cluster, existing binary log files are retained permanently and are not automatically deleted.

  Note

  You can modify parameters to change the binary log file retention period for a cluster where binary logging is enabled. For more information, see Binary log file retention period.

• Q: What does the error message Could not find first log file name in binary log index file mean?

  A: The error message Could not find first log file name in binary log index file indicates that the binary log has been purged. Purged binary logs cannot be recovered.

• Q: Can I disable binary logging after it is enabled?

  A: Set the loose_polar_log_bin parameter to OFF and submit.

  Note

  After you disable binary logging, existing binary log files are retained permanently. If you want to delete them, shorten the retention period and wait for the files to be automatically deleted before you disable binary logging.

• Q: How do I reduce the storage space occupied by binary logs?

  A: You can set the binary log retention period parameter (loose_expire_logs_hours or binlog_expire_logs_seconds) to a smaller value to reduce the storage space occupied by binary log files.

• Q: How much does enabling binary logging affect performance?

  A: Enabling binary logging does not affect query (SELECT) performance. It only affects the performance of write and update operations, such as INSERT, UPDATE, and DELETE. Typically, the performance impact is less than 10%. Under extreme stress, the performance impact can reach 40%.

• Q: After binary logging is enabled, the cluster restarts automatically. How long does the restart task take to complete?

  A: Generally, the restart task completes within 5 minutes. During the restart, a transient disconnection of about 40 seconds occurs. The exact duration depends on the data volume and the number of tables. Perform this operation during off-peak hours and ensure your application has a reconnection mechanism.

• Q: How do I remotely obtain and view the content of binary logs?

  A: For more information, see Remotely obtain and parse the binary logs of a PolarDB for MySQL cluster.

• Q: Why can't I use the DMS feature Perform lock-free schema changes using lock-free change tickets to perform table schema changes, such as adding an index, on a PolarDB for MySQL cluster?

  A: Binary logging is disabled by default for PolarDB for MySQL clusters. Therefore, to use the lock-free schema change feature of DMS, you must first enable binary logging for the PolarDB for MySQL cluster. If you do not want to enable binary logging, you can also use online DDL commands to change the table schema.

• Q: After I enable binary logging, can I use Canal to collect changes made to MySQL?

  A: Yes. Canal can capture changes from MySQL.

• Q: Does executing the SHOW BINARY LOGS statement to query the size of binary log files affect cluster performance?

  A: No, it does not. The SHOW BINARY LOGS statement is a management operation and has a negligible impact on cluster performance. It does not directly affect the read and write performance of the database, unlike data write operations such as INSERT, UPDATE, and DELETE. Therefore, you can use this command for queries without worrying about performance degradation.

• Q: How do I query the last write time of a binary log in PolarDB for MySQL?

  A: Use the show full binary logs statement to view the name, size, and last write time of all binary log files.

  Note

  This feature is supported on PolarDB for MySQL clusters that meet the following requirements:

  • MySQL 8.0.2, and the minor version must be 8.0.2.2.0 or later.

  • MySQL 8.0.1, and the minor version must be 8.0.1.1.14 or later.

  • MySQL 5.7, and the minor version must be 5.7.1.0.27 or later.

  • MySQL 5.6, and the minor version must be 5.6.1.0.38 or later.

• Q: How do I view the content of a binary log at a specific time in PolarDB for MySQL?

  A: You cannot directly view the content of a binary log at a specific time. Instead, you can perform a point-in-time recovery and then parse the binary log to view its content.