MySQL Redo Logging Disabled Recovery Guard with Kestra

id: mysql-redo-logging-disabled-recovery-guard
namespace: company.team

inputs:
  - id: mysql_url
    type: STRING
    defaults: jdbc:mysql://localhost:3306/
    description: JDBC URL of the MySQL server to guard. The connecting user needs
      the INNODB_REDO_LOG_ENABLE privilege to turn redo logging back on.

  - id: use_secret_auth
    type: BOOL
    defaults: true
    description: Leave true in production so the connection reads MYSQL_USERNAME and
      MYSQL_PASSWORD from secrets. Set false only to pass credentials inline in
      a disposable test environment.

  - id: mysql_username
    type: STRING
    defaults: root
    description: Username used only when use_secret_auth is false. Ignored in production.

  - id: mysql_password
    type: STRING
    defaults: ""
    description: Password used only when use_secret_auth is false. Ignored in production.

  - id: dry_run
    type: BOOL
    defaults: true
    description: When true, report that redo logging is off without turning it back on.

  - id: auto_remediate
    type: BOOL
    defaults: false
    description: When true and dry_run is false, run ALTER INSTANCE ENABLE INNODB
      REDO_LOG behind an approval gate, then re-read the status variable.

triggers:
  - id: schedule
    type: io.kestra.plugin.core.trigger.Schedule
    disabled: true
    cron: "*/15 * * * *"
    description: Check every fifteen minutes that redo logging is on. Disabled by
      default and pinned to safe mode so it reports without changing anything
      until an operator enables it.
    inputs:
      dry_run: true
      auto_remediate: false

tasks:
  - id: detect
    type: io.kestra.plugin.jdbc.mysql.Query
    description: >-
      Read the Innodb_redo_log_enabled status variable. It is the only place the
      server reports whether redo logging is on, so this read is the detector.
    url: "{{ inputs.mysql_url }}"
    username: "{{ inputs.use_secret_auth ? secret('MYSQL_USERNAME') :
      inputs.mysql_username }}"
    password: "{{ inputs.use_secret_auth ? secret('MYSQL_PASSWORD') :
      inputs.mysql_password }}"
    fetchType: FETCH
    sql: SELECT VARIABLE_VALUE AS redo_enabled FROM performance_schema.global_status
      WHERE VARIABLE_NAME = 'Innodb_redo_log_enabled'

  - id: compute
    type: io.kestra.plugin.core.output.OutputValues
    description: >-
      Turn the status read into a finding count. One finding means redo logging
      is off. A server with no such status variable predates MySQL 8.0.21,
      cannot disable redo logging at all, so it reports zero findings and is
      named in the log.
    values:
      summary: >-
        {{ {'rows': (outputs.detect.rows ?? [])} | toJson | jq('(.rows // []) as
        $r | (if ($r|length) == 0 then "UNKNOWN" else ($r[0].redo_enabled |
        tostring) end) as $o | {observed: $o, supported: (($r|length) > 0),
        finding_count: (if $o == "OFF" then 1 else 0 end)}') | first | toJson }}

  - id: log_status
    type: io.kestra.plugin.core.log.Log
    message: |
      MySQL redo logging guard on {{ inputs.mysql_url }}: Innodb_redo_log_enabled is {{ fromJson(outputs.compute.values.summary).observed }}, {{ fromJson(outputs.compute.values.summary).finding_count }} finding(s).
      Status variable present: {{ fromJson(outputs.compute.values.summary).supported }}. Mode dry_run={{ inputs.dry_run }} auto_remediate={{ inputs.auto_remediate }}.

  - id: have_finding
    type: io.kestra.plugin.core.flow.If
    condition: "{{ fromJson(outputs.compute.values.summary).finding_count > 0 }}"
    then:
      - id: alert_redo_disabled
        type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
        description: Alert that the server is running with no crash recovery, in dry run
          and in remediate mode, so a human sees the unprotected window even
          when nothing is changed.
        url: "{{ secret('SLACK_WEBHOOK_URL') }}"
        payload: |
          {"text": "MySQL redo logging guard: Innodb_redo_log_enabled is OFF on {{ inputs.mysql_url }}. The server has no crash recovery, so an unclean shutdown loses committed transactions and it will not restart cleanly. dry_run={{ inputs.dry_run }} auto_remediate={{ inputs.auto_remediate }}. Execution {{ execution.id }}."}

      - id: mode
        type: io.kestra.plugin.core.flow.If
        condition: "{{ inputs.auto_remediate and not inputs.dry_run }}"
        then:
          - id: approval_gate
            type: io.kestra.plugin.core.flow.Pause
            description: Hold the run until a human approves. Re-enabling redo logging makes
              the next checkpoint write a full page flush, so an operator
              decides when that write load lands.
            pauseDuration: PT1H
            behavior: FAIL
            onResume:
              - id: approved
                type: BOOL
                required: false
                description: Set true to run ALTER INSTANCE ENABLE INNODB REDO_LOG. Anything
                  else leaves redo logging off.
          - id: apply
            type: io.kestra.plugin.core.flow.If
            condition: "{{ outputs.approval_gate.onResume.approved ?? false }}"
            then:
              - id: remediate_enable
                type: io.kestra.plugin.jdbc.mysql.Query
                description: >-
                  Turn redo logging back on. allowFailure so a refused statement
                  does not stop the pass and the re-read stays the real gate. A
                  connecting user without INNODB_REDO_LOG_ENABLE is refused
                  here, which is exactly the case the re-read catches.
                allowFailure: true
                url: "{{ inputs.mysql_url }}"
                username: "{{ inputs.use_secret_auth ? secret('MYSQL_USERNAME') :
                  inputs.mysql_username }}"
                password: "{{ inputs.use_secret_auth ? secret('MYSQL_PASSWORD') :
                  inputs.mysql_password }}"
                fetchType: NONE
                sql: ALTER INSTANCE ENABLE INNODB REDO_LOG
              - id: reverify
                type: io.kestra.plugin.jdbc.mysql.Query
                description: Re-read the same status variable. This read is the proof, not the
                  ALTER INSTANCE call.
                url: "{{ inputs.mysql_url }}"
                username: "{{ inputs.use_secret_auth ? secret('MYSQL_USERNAME') :
                  inputs.mysql_username }}"
                password: "{{ inputs.use_secret_auth ? secret('MYSQL_PASSWORD') :
                  inputs.mysql_password }}"
                fetchType: FETCH
                sql: SELECT VARIABLE_VALUE AS redo_enabled FROM performance_schema.global_status
                  WHERE VARIABLE_NAME = 'Innodb_redo_log_enabled'
              - id: verify_values
                type: io.kestra.plugin.core.output.OutputValues
                values:
                  observed_after: >-
                    {{ {'rows': (outputs.reverify.rows ?? [])} | toJson |
                    jq('(.rows // []) as $r | if ($r|length) == 0 then "UNKNOWN"
                    else ($r[0].redo_enabled | tostring) end') | first }}
              - id: verify_result
                type: io.kestra.plugin.core.flow.If
                condition: "{{ outputs.verify_values.values.observed_after == 'ON' }}"
                then:
                  - id: remediated
                    type: io.kestra.plugin.core.log.Log
                    message: "Redo logging is back on for {{ inputs.mysql_url }}. Before:
                      Innodb_redo_log_enabled was {{
                      fromJson(outputs.compute.values.summary).observed }}.
                      After: {{ outputs.verify_values.values.observed_after }}.
                      Crash recovery protects writes from here on. It does not
                      protect anything written while redo logging was off."
                  - id: report_unprotected_window
                    type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
                    description: Report the fix and the window that stays unprotected. Enabling redo
                      logging now does nothing for pages written while it was
                      off, so the finding is still worth a human reading it.
                    url: "{{ secret('SLACK_WEBHOOK_URL') }}"
                    payload: |
                      {"text": "MySQL redo logging guard: redo logging is back ON for {{ inputs.mysql_url }} (execution {{ execution.id }}). Anything written while it was OFF was never crash protected. If the server went down unclean during that window, check the data rather than assuming this fix covered it."}
                else:
                  - id: remediation_failed
                    type: io.kestra.plugin.core.execution.Fail
                    errorMessage: >-
                      Redo logging is still not on for {{ inputs.mysql_url }}.
                      Innodb_redo_log_enabled reads {{
                      outputs.verify_values.values.observed_after }} after the
                      ALTER INSTANCE call. The connecting user most likely lacks
                      the INNODB_REDO_LOG_ENABLE privilege, so the statement was
                      refused. The server is still running with no crash
                      recovery. Fix the grant then run again.
            else:
              - id: not_approved
                type: io.kestra.plugin.core.log.Log
                message: "Approval not granted, so ALTER INSTANCE ENABLE INNODB REDO_LOG did not
                  run. {{ inputs.mysql_url }} is still running with redo logging
                  off."
        else:
          - id: report_only
            type: io.kestra.plugin.core.log.Log
            message: "Report only (dry_run={{ inputs.dry_run }}, auto_remediate={{
              inputs.auto_remediate }}). Redo logging on {{ inputs.mysql_url }}
              would be turned back on with ALTER INSTANCE ENABLE INNODB
              REDO_LOG. No change made."
    else:
      - id: clean_log
        type: io.kestra.plugin.core.log.Log
        message: "No action. Innodb_redo_log_enabled reads {{
          fromJson(outputs.compute.values.summary).observed }} on {{
          inputs.mysql_url }}, so crash recovery is in place. Status variable
          present: {{ fromJson(outputs.compute.values.summary).supported }}."

errors:
  - id: alert_failure
    type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
    description: Post to Slack on any failure, including a connection error or a
      re-read that still shows redo logging off.
    url: "{{ secret('SLACK_WEBHOOK_URL') }}"
    payload: |
      {"text": "MySQL redo logging guard FAILED for {{ inputs.mysql_url }} (execution {{ execution.id }}). Either the server could not be read or redo logging was still off after the fix. Check the run."}

ALTER INSTANCE DISABLE INNODB REDO_LOG exists to make a bulk load faster. It is meant to be switched back on the moment the load finishes. Left off, the server runs with no crash recovery at all. An unclean shutdown loses committed transactions. The server will not even restart cleanly in that state. Nothing warns you, because every query and every commit behaves normally right up until the power cuts. This guard reads the Innodb_redo_log_enabled status variable, turns redo logging back on behind a gate, then proves crash recovery is really back.

This blueprint was created by zkasuran.

Safety

This flow only ever turns redo logging ON. ALTER INSTANCE ENABLE INNODB REDO_LOG is the only write it can issue, so it has no path to disabling redo logging on a healthy server. That is why it is safe to leave on a schedule.
Read-only by default. dry_run is true and auto_remediate is false, so a plain run reports what it found.
The write happens only when auto_remediate is true and dry_run is false. Even then it waits for a human to resume the Pause with approved set true. An unanswered gate reads as not approved.
The run passes only when a second read of the status variable returns ON.
The honest caveat, stated in the alert as well as here: enabling redo logging now does not retroactively protect anything written while it was off. If the server crashed during that window the damage already exists. The finding is worth reporting even after the fix, because it tells an operator there was an unprotected window.

How it works

detect (io.kestra.plugin.jdbc.mysql.Query) reads Innodb_redo_log_enabled from performance_schema.global_status. OFF means redo logging is disabled.
compute (io.kestra.plugin.core.output.OutputValues) turns that into a finding count. A server with no such status variable predates MySQL 8.0.21, cannot disable redo logging at all, so it reports zero findings rather than a false positive.
log_status (io.kestra.plugin.core.log.Log) records the observed value, the finding count and the mode.
have_finding (io.kestra.plugin.core.flow.If) ends with a clean log when redo logging is on. Otherwise alert_redo_disabled posts to Slack in every mode, including dry run.
mode (io.kestra.plugin.core.flow.If) enters remediation only when auto_remediate is true and dry_run is false. Otherwise report_only logs what would run.
approval_gate (io.kestra.plugin.core.flow.Pause) holds the run for a human. Re-enabling redo logging makes the next checkpoint flush pages that were never logged, so an operator picks the moment.
remediate_enable runs ALTER INSTANCE ENABLE INNODB REDO_LOG with allowFailure: true, so a refused statement does not short circuit the gate.
reverify re-reads the same status variable. verify_result fails the run through io.kestra.plugin.core.execution.Fail when it is not ON, then report_unprotected_window posts the fix plus the window that stays unprotected.

What you get

A detector for a server with no crash recovery, a state that produces no error, no warning, no slow query. Every commit looks normal until an unclean shutdown.
A fix that can only move the server in the safe direction. There is no input, branch or mode that disables redo logging.
A real verification step. The run passes only when the status variable reads ON after the change.
A failure that names the usual cause. A connecting user without INNODB_REDO_LOG_ENABLE has its ALTER INSTANCE refused, which the re-read catches instead of the run reporting success.
Slack alerts when redo logging is off, when the fix lands (with the unprotected window spelled out) and on any failure.

Who it's for

Platform engineers who load MySQL in bulk and disable redo logging to speed it up, where one failed or interrupted load leaves the switch off.
DBAs who inherit a server and want proof that crash recovery is actually on rather than assumed.
Teams running MySQL on a host where power loss or an OOM kill is a real event, who cannot afford to find out about this after the restart fails.

Why orchestrate this with Kestra

MySQL will not tell you that redo logging is off. There is no warning in the error log after the initial statement, no alert, nothing in a normal health check. Kestra adds what is missing. It reads the status variable on a schedule, gates the fix behind dry run and human approval, re-reads the variable so a hopeful fix becomes a verified one, raises the finding in Slack even after the fix because the unprotected window is itself the news.

Tested end to end

Run on Kestra 2.0.5 against MySQL 8.0.46, with ALTER INSTANCE DISABLE INNODB REDO_LOG used to create the degraded state and the mysql client used to confirm the status variable before and after every run.

Detector can fail: the same server reported zero findings with redo logging on and exactly one finding with it off. Both reads matched SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_enabled' taken from the mysql client at the same moment.
Dry run on the degraded server detected the finding, alerted, changed nothing. The status variable read OFF before and after.
Remediate with approval turned redo logging back on. The re-read returned ON and the mysql client agreed.
Declining the gate left redo logging off, confirmed from the client.
Re-verify can fail: with a user that holds SELECT on performance_schema but not INNODB_REDO_LOG_ENABLE, the ALTER INSTANCE was refused, allowFailure let the run continue, the re-read still said OFF and the run failed rather than reporting success.
An unreachable host failed the detect step and fired the Slack alert through the errors block.

Prerequisites

MySQL 8.0.21 or newer reachable over JDBC. The connecting user needs SELECT on performance_schema to read the status variable, plus the INNODB_REDO_LOG_ENABLE privilege to turn redo logging back on. A read-only user still runs the detector.

Secrets

MYSQL_USERNAME: MySQL user, read when use_secret_auth is true.
MYSQL_PASSWORD: password for that user.
SLACK_WEBHOOK_URL: used by the alerts and the errors block.
In Kestra OSS, provide each secret as a base64-encoded environment variable prefixed SECRET_.

Inputs

Name	Type	Default	Description
`mysql_url`	STRING	`jdbc:mysql://localhost:3306/`	JDBC URL of the MySQL server to guard.
`use_secret_auth`	BOOL	`true`	Read credentials from secrets. Set false only for inline test credentials.
`mysql_username`	STRING	`root`	Username used only when use_secret_auth is false.
`mysql_password`	STRING	``	Password used only when use_secret_auth is false.
`dry_run`	BOOL	`true`	Report without turning redo logging back on.
`auto_remediate`	BOOL	`false`	Run the ALTER INSTANCE behind the approval gate when dry_run is false.

Quick start

Set MYSQL_USERNAME, MYSQL_PASSWORD and SLACK_WEBHOOK_URL as namespace secrets.
Import this flow.
Run it with mysql_url pointed at the server. The default dry run tells you whether crash recovery is on.
To fix a server that reports OFF, run again with dry_run false and auto_remediate true, then resume the paused execution with approved true.
Enable the schedule once you trust it. Safe mode is pinned in the trigger inputs, so the scheduled run reports and never writes until you change that.

How to extend

Drop the Pause for a staging server where the checkpoint flush cost does not matter, keeping the dry-run gate.
Add a second detector read of Innodb_redo_log_read_only to catch a server that came up in a read-only redo state.
Open a ticket from the alert branch for an environment where a change record is required before any ALTER INSTANCE.
Point one copy of the flow at each MySQL instance and record which host each scheduled copy guards in the flow labels.

MySQL Redo Logging Disabled Recovery Guard

Safety

How it works

What you get

Who it's for

Why orchestrate this with Kestra

Tested end to end

Prerequisites

Secrets

Inputs

Quick start

How to extend

Links

Tasks

Orchestrate with Kestra

Share this Blueprint

Create a Blueprint

New to Kestra?

MySQL Redo Logging Disabled Recovery Guard

Safety

How it works

What you get

Who it's for

Why orchestrate this with Kestra

Tested end to end

Prerequisites

Secrets

Inputs

Quick start

How to extend

Links

Tasks

Orchestrate with Kestra

Share this Blueprint

Create a Blueprint

More Data Blueprints

Hold Every Release to a Lighthouse Score Budget

KeyDB Active-Replica Multi-Master Peering Sentinel

Kinesis Stream Throughput Quota Gate

Changelog CI Validation with Liquibase Update-Testing-Rollback

Alert When Streaming Windows Ship With Late or Missing Rows

Close IFRS 16 leases each month with remeasurements, terminations and the maturity analysis

More Infrastructure Blueprints

Retire Unused Kestra Flows with Quarantine, Approval and Restore

KMS Key Rotation Compliance Gate

Backport Kestra UI hotfixes to a Git review branch

Gate a Kestra upgrade against every live flow

Kong Ingress Upstream Target Unhealthy and Circuit Breaker Sentinel

KeyDB Active-Replica Multi-Master Peering Sentinel

New to Kestra?