Query icon
Log icon
If icon
Request icon
Pause icon
Loop icon
Fail icon
Trigger icon

MySQL General Query Log Left On Revert Guard

Detect a forgotten MySQL general query log, approve runtime OFF and prove instrumented file writes stop while slow-log settings stay intact.

Categories
DataInfrastructure

Safety

SET GLOBAL general_log = ON is the quickest way to see every statement a MySQL server receives. It is usually turned on for a debugging session and then forgotten. From then on every query, including the text of statements with personal data, is written to disk on every request. The log grows without bound and costs I/O. Turning it off is a one-line change, but the wrong server, an intentional audit log or a change made by another administrator a second ago must be left alone. So the flow refuses by default and every guard below is load bearing. Each one was proven by its own run.

Guard What it stops
expected_general_log: OFF policy, INTENTIONAL_ON to opt out turning off a log someone keeps on purpose
protected_setters undoing a change made by a protected account such as observability
Grace period on variables_info.SET_TIME ending a debugging session that is still going on
Persisted ON, startup ON, TABLE or FILE,TABLE output spared fighting configuration that was set deliberately
Missing file instrumentation makes the server ineligible a change the flow could not verify afterwards
auto_remediate false, dry_run true, Pause three separate gates before one write
Recheck of server UUID, change time, setter and slow-log settings after approval acting on a plan that went stale while the approval waited
Conditional write that sends an invalid value when the precondition fails rewriting the setter and time metadata when nothing should change
Read-back plus a controlled query burst against the file counter a SET that returned success being reported as a fix

How it works

  1. detect (io.kestra.plugin.jdbc.mysql.Query) reads general_log, log_output, the persisted and startup values, the setter and SET_TIME from performance_schema.variables_info, the slow-log settings and the byte counter of the wait/io/file/sql/query_log instrument. It classifies the server as eligible or names why not.
  2. report logs the setter, age, output destination and persisted state. It never reads statement contents. incident (If) continues only when the server is eligible and posts the finding to Slack (finding).
  3. change_enabled (If) requires auto_remediate: true and dry_run: false. Otherwise report_only logs and the run ends.
  4. approval (Pause) waits for a person. approved_change reads onResume.approved ?? false, so a resume with no answer is a refusal.
  5. recheck reads the server again. unchanged_plan (If) continues only if the server UUID, change time, setter, output destination and slow-log settings all match the plan. Otherwise stale_approval fails the run before any write.
  6. restore_runtime_off sets general_log with one conditional statement: OFF if the same change is still in place, otherwise the invalid value -1. MySQL rejects -1 with error 1231 and leaves the variable, its setter and its time unchanged.
  7. after_write reads the value back. controlled_query_burst (Loop) runs five harmless queries. verify reads the file counter again. outcome fails the run (unresolved) unless logging is OFF and the counter did not move. alert_failure posts failures to Slack.

The event trigger forgotten_log (io.kestra.plugin.jdbc.mysql.Trigger) ships disabled: true, with dry_run: true and auto_remediate: false.

What you get

  • An alert when a general query log has been left on past the grace period, naming who turned it on and when.
  • A return to the expected OFF state that only happens on the server and change that were approved.
  • Proof that statements stopped reaching the log file. When they did not, the run fails and says so.
  • Existing log contents, slow-log settings and intentional audit logging left as they were.

Who it's for

Database operators and SREs who turn on MySQL's general query log for debugging and want it reliably turned off afterwards.

Why orchestrate this with Kestra

A forgotten log has no alert of its own: the server keeps working while the file grows. Kestra makes it an event-driven check, holds the change at a Pause for a person, rechecks the server after the approval and proves the effect with a controlled workload, all in one execution. MySQL's own variable metadata and Performance Schema instrument provide the evidence.

Tested end to end

Kestra 2.0.5, JDBC MySQL plugin 2.1.0, MySQL 8.0.46. Validate was clean: no constraints, warnings or deprecations. Every state was read with an independent mysql client, not from the flow's output. All seven required cases ran on the final revision 3:

Case Execution Result
Healthy 7gxsy51Fl9wSzIqGqAbVln SUCCESS. general_log OFF, nothing written
Dry run 1iGhiT2g7fP8yVppD3aDxm SUCCESS. Eligible, still ON, file kept growing from 277,992 to 285,124 bytes, no write
Declined approval hApUtoVkQwzACJPUBqgzs SUCCESS. Still ON, setter and set time unchanged
Remediation 2R6ypFIzyx7Mqmj4Nj5YkE SUCCESS. ON to OFF. The file stayed at 308,814 bytes with the same SHA-256 through five queries and a separate client burst. Slow logging stayed ON and its file kept growing
Repeat QPpLY4R1PqSLcsTMfTdx4 SUCCESS. Already OFF, nothing written
Refused write 2oFKIHLWHrM83IlbXqKBWd FAILED as intended. A readonly user without SYSTEM_VARIABLES_ADMIN reached the write, MySQL refused it, logging stayed ON and the file grew from 310,976 to 339,133 bytes. Verification failed the run
Change during the Pause 7mABDq1Mdyg8z03hHk3lxz FAILED as intended. The protected observability account changed the setting while the approval waited. recheck refused before any write and the new setter was kept

Earlier revisions also proved the protected cases: intentional ON, protected setter, persisted ON, TABLE and FILE,TABLE output, disabled instrumentation, startup ON, below-grace changes and a two-second grace boundary. Five changes made during the Pause failed before any write. A fresh digest-pinned fixture repeated the seven required cases. An enabled copy of the event trigger produced no execution while healthy and one execution once logging was turned on. Slack was a local HTTP sink.

Revision 3 exists because a review found that the earlier conditional write, when its precondition failed, rewrote general_log to its current value and so changed SET_TIME and SET_USER. The invalid-value form leaves all three untouched, measured on the same server.

Prerequisites

  • MySQL 8.0 with Performance Schema and the wait/io/file/sql/query_log instrument enabled. Tested on 8.0.46 only.
  • Kestra 2.0.5 with the JDBC MySQL and Slack plugins.
  • Minimum permissions: SELECT on performance_schema, plus SYSTEM_VARIABLES_ADMIN to turn the log off. No SUPER.

Secrets

  • MYSQL_JDBC_URL, MYSQL_USERNAME, MYSQL_PASSWORD: the server and account.
  • SLACK_WEBHOOK_URL: Slack incoming webhook for findings and failures.

Inputs

Input Default Purpose
expected_general_log OFF Policy. INTENTIONAL_ON spares a server that keeps the log on purpose
protected_setters [observability] Accounts whose changes are never undone
grace_seconds 1800 Time the log may stay on before it counts as forgotten
dry_run true Report only
auto_remediate false Ask for approval to turn the log off

Quick start

  1. Add the four secrets. Grant the account SELECT on performance_schema and SYSTEM_VARIABLES_ADMIN.
  2. Execute with the defaults. Read the report: setter, age and output destination.
  3. List debugging or audit accounts you never want undone in protected_setters.
  4. Execute with dry_run: false and auto_remediate: true, then approve the Pause.
  5. Read the verification lines: the variable is OFF and the file counter did not move.
  6. Enable the forgotten_log trigger to check continuously.

How to extend

  • Rotate or archive the existing general log file after the change, with its own approval.
  • Run the same flow per server from a list, so each keeps its own approval and recheck.
  • Feed the finding into a ticket so the person who turned the log on is told.

Limits: the change is runtime only. It does not edit my.cnf or a persisted value. It does not shrink the existing file either. The conditional SET is not a lock, so another administrator can still change the variable afterwards. MySQL 8.4 was not tested.

Links

This blueprint was created by zkasuran.

See How

New to Kestra?

Use blueprints to kickstart your first workflows.