How to Check and Configure binlog Retention in Aurora MySQL

Applicability of This Document

Ready-to-Run Commands

-- 対象: Aurora MySQL 2.x / 3.x
-- 権限: mysql スキーマのストアドプロシージャ実行権限（通常はマスターユーザー）
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
CALL mysql.rds_show_configuration;

-- 対象: Aurora MySQL 2.x / 3.x
-- 権限: 接続権限（グローバル変数の参照のみ）
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
SELECT
    @@global.log_bin           AS log_bin_enabled,
    @@global.binlog_format     AS binlog_format,
    @@global.binlog_row_image  AS binlog_row_image,
    @@global.binlog_checksum   AS binlog_checksum;

-- 対象: Aurora MySQL 2.x / 3.x（ライターインスタンス）
-- 権限: REPLICATION CLIENT 権限
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能

-- 1) 現存するbinlogファイルとサイズ
SHOW BINARY LOGS;

-- 2) 現在の書き込み位置（Aurora MySQL 3.x の一部バージョンでは
--    SHOW BINARY LOG STATUS に置き換えられているため、エラーになる場合はそちらを使う）
SHOW MASTER STATUS;

-- 対象: Aurora MySQL 2.x / 3.x
-- 権限: RDSのマスターユーザー相当（一般ユーザーではアクセス拒否になる）
-- 変更作業: あり（binlog の保持時間ポリシーを変更する）
-- Production 実行: 可能。ただしストレージ消費が増えるため事前に見積もること

-- 保持時間は「時間」単位で指定する（例: 24時間）
CALL mysql.rds_set_configuration('binlog retention hours', 24);

-- 設定後の値を確認する
CALL mysql.rds_show_configuration;

-- 明示設定を解除して既定の挙動（早期削除）に戻す場合
-- CALL mysql.rds_set_configuration('binlog retention hours', NULL);

# 対象: Aurora MySQL（DBクラスターパラメータグループ）
# 権限: IAM rds:ModifyDBClusterParameterGroup, rds:RebootDBInstance
# 変更作業: あり（クラスターパラメータ変更 + ライターインスタンス再起動）
# Production 実行: 不可（再起動を伴うためメンテナンス時間帯に計画実行）

# 1) 現在値と反映方法を確認する
aws rds describe-db-cluster-parameters \
  --db-cluster-parameter-group-name example-aurora-mysql-cluster-params \
  --query "Parameters[?ParameterName=='binlog_format' || ParameterName=='binlog_row_image'].{Name:ParameterName,Value:ParameterValue,Apply:ApplyType}" \
  --output table

# 2) binlog_format を ROW に変更する（クラスター単位のパラメータ）
aws rds modify-db-cluster-parameter-group \
  --db-cluster-parameter-group-name example-aurora-mysql-cluster-params \
  --parameters '[
    {"ParameterName":"binlog_format","ParameterValue":"ROW","ApplyMethod":"pending-reboot"}
  ]'

# 3) ライターインスタンスを再起動して反映する（接続断が発生する）
aws rds reboot-db-instance --db-instance-identifier example-aurora-instance

-- 対象: Aurora MySQL 2.x / 3.x（ライターインスタンス）
-- 権限: MySQL 8.0互換は BINLOG_ADMIN、5.7互換は SUPER 相当（RDSではマスターユーザー）
-- 変更作業: あり（binlog ファイルを削除する。削除したログは復元できない）
-- Production 実行: 原則不可。全ての消費側の読み取り位置を確認してからのみ実施する

-- 1) 先に消費側（レプリカ・DMS・CDC）がどこまで読んでいるかを確認する
SHOW BINARY LOGS;

-- 2) 指定ファイルより前のbinlogを削除する
PURGE BINARY LOGS TO 'mysql-bin-changelog.000123';

-- 3) 日時指定で削除する場合
-- PURGE BINARY LOGS BEFORE '2026-08-01 00:00:00';

How to Read the Results

When to Use This

• A DMS CDC task stopped with an error along the lines of "binlog not found"
• An external replica was paused and then resumed, but it could not catch up and errored out
• After enabling the binlog, the cluster's storage usage keeps increasing
• Running `CALL mysql.rds_set_configuration(...)` resulted in access denied
• A DMS endpoint test shows a warning related to binlog settings

Possible Causes (Most Likely First)

1. 01

   The retention time has no explicit setting

   If `binlog retention hours` is `NULL`, RDS purges binlogs early once it judges them unnecessary. If a CDC consumer pauses, the binlog it needs upon resuming may already be gone.

2. 02

   The retention time is shorter than the consumer's downtime

   If the retention time is shorter than the time a consumer (a DMS task, an external replica) is down for maintenance or due to a failure, it cannot catch up on resume. The setting needs to be derived by working backward from the acceptable downtime.

3. 03

   `binlog_format` is not set to `ROW`

   With `STATEMENT` or `MIXED`, the row-level change details that CDC needs cannot be obtained. On Aurora, this is configured in the cluster parameter group.

4. 04

   The executing user lacks RDS master-user-equivalent privileges

   `mysql.rds_set_configuration` is a stored procedure provided by RDS, and running it requires master-user-equivalent privileges. A regular application user will get access denied.

5. 05

   A manual `PURGE BINARY LOGS` deleted logs that were still needed

   Deleting a file that a consumer hasn't read yet makes recovery impossible. This is sometimes run as a stopgap when storage is tight and the problem only surfaces later.

6. 06

   A failover switched the write target

   When the writer switches, how the consumer handles continuity of binlog file names and positions becomes an issue. Check the CDC side's resume method.

Diagnostic Steps

1. 1

   Check the current retention time

   Read-only

   Run `CALL mysql.rds_show_configuration;` and check the value of `binlog retention hours`. This is read-only and safe.

2. 2

   Check whether the binlog is enabled and in ROW format

   Read-only

   Check `@@global.log_bin` and `@@global.binlog_format`. Also check `binlog_row_image`.

3. 3

   Check the range and total size of existing binlogs

   Read-only

   Use `SHOW BINARY LOGS` to see the oldest file and the total size. This can also be used to estimate before extending the retention time.

4. 4

   Check the consumer's read position and lag

   Read-only

   For DMS, check the task's CDC lag metric; for an external replica, check the replica's status to see how far it has read.

5. 5

   Check the storage usage trend in CloudWatch

   Read-only

   Estimate the increase from extending the retention time based on the actual trend.

6. 6

   Check the executing user's privileges

   Read-only

   Use `SHOW GRANTS;` to check whether the user can run `mysql.rds_set_configuration`.

Solutions

Differences by Version and Environment

If This Doesn't Resolve It

• Check how the CDC consumer persists its resume position

  Check where DMS or a replica stores its read position and how it resumes after a failover.

• Check the cluster's storage usage trend

  Isolate the growth factor including not just the binlog but also temporary space and snapshots.

• Sort out the relationship with the backup retention period

  Binlog retention and backup retention are separate settings. Clarify which one covers which recovery requirement.

• Check how the master user is managed

  Without a defined procedure for storing and using the master user credentials, you may not be able to change settings when needed.

• Build a list of consumers that read the binlog

  Identifying what depends on the binlog — DMS, external replicas, change-notification mechanisms, etc. — speeds up decisions about the retention time.

Basis and Limitations of This Document

FAQ

Questions This Document Covers

• Getting a permission error on rds_set_configuration
• DMS CDC says it cannot find the binlog
• Want to enable the binlog in Aurora MySQL
• The binlog is putting pressure on storage

What the Risk Labels Mean

• Read-onlyDoes not change data or settings.
• LowImpact is limited, but permissions and load should be checked.
• MediumMay affect performance, locking, or cost.
• HighMay cause an outage, data loss, or require recovery work.
• Expert Review RequiredRequires a separate review before applying to production.

GIIP's Scope of Support

binlog retention tends to become a "set it once and forget it" setting, but in practice its appropriateness changes every time the CDC consumer configuration changes. GIIP tracks the retention setting, the total size of existing binlogs, and the CDC-side lag on the same screen, and notifies as soon as the lag approaches the retention time. Irreversible operations such as deleting binlogs or changing `binlog_format` are excluded from automation and are designed to always require human approval.

Related Services

• AWSマネージド運用の範囲を確認する
• 24時間監視の対象を確認する