OneLinersCommand workbench
Guides
Observability & Monitoring / Databases & Data

Enable and analyze the MySQL slow query log safely

Baseline workload, enable bounded slow logging, protect and rotate files, summarize candidates, use EXPLAIN ANALYZE carefully, and prove improvement.

75 min8 stepsChanges system stateRevision 2
Save or explore
#mysql#slow-query#performance#explain#index
Save to collectionCreate a collection in the sidebar first.
0 of 8 steps completed
Goal

Find expensive SQL using measured evidence without permanently logging every query or leaking sensitive statements.

Supported environments
  • MySQL 8.4 LTS
Prerequisites
  • Recovery access and maintenance window Keep console access and schedule write-affecting work so an interrupted package, grant, backup, or replication change can be recovered.
  • Verified backup Create a fresh backup and prove that its format, encryption key, and restore procedure are available before changing production data.
  • Capacity and compatibility Check database version, storage headroom, character sets, application driver compatibility, and expected downtime.df -h; df -i; free -m
Operating boundary

OneLiners never runs these steps or stores secrets. Review placeholders, versions, current state, and change-control requirements before using a command.

Full guide

What you will build

System
  • A production-ready MySQL implementation of “Enable and analyze the MySQL slow query log safely” with explicit identities, configuration ownership, observable checkpoints and a tested reversal path.
  • A before-state, native validation sequence and concrete evidence set covering the database service, client behavior, data or administrative surface affected by this procedure.
  • A least-privilege operational runbook that keeps secrets out of commands and verifies both the required success path and an expected denial or failure boundary.
Observable outcome
  • Find expensive SQL using measured evidence without permanently logging every query or leaking sensitive statements. The result is demonstrated through the guide's native verification commands and representative application or restore evidence.
  • Unrelated databases, accounts, listeners and services remain unchanged; operators can identify the exact revision, artifact and rollback action for this change.

Architecture

How the parts fit together

The server records statements exceeding a reviewed threshold without logging every request indefinitely. File ownership, rotation, disk budget and privacy controls protect the evidence. Native summary tools and performance_schema aggregate normalized patterns, while EXPLAIN on a safe representative SELECT shows access plans. Index or query changes are tested outside production and accepted only after latency and correctness verification.

Slow query logCaptures bounded evidence above a reviewed duration threshold.
Rotation and privacy controlsLimit disk consumption and protect statement data.
Digest aggregationRanks normalized query patterns by total operational cost.
Plan and workload testsValidate proposed indexes or query changes safely.
  1. Capture effective configuration, versions, listeners, identities, grants, workload health and a recoverable before-state.
  2. Prepare the slow log candidate using only official MySQL interfaces, protected secret delivery and the narrowest necessary scope.
  3. Apply one controlled layer at a time, run the immediate checkpoint and inspect native logs/status before proceeding.
  4. Perform end-to-end success and expected-failure tests; retain rollback evidence and schedule recurring verification.

Assumptions

  • The installed MySQL family and version are supported by the official sources linked below, and package/configuration provenance is known.
  • A maintenance or canary window, current recoverable backup and console-level recovery path exist before state-changing database work begins.
  • Operator, application, backup, replication and web-administration identities are separate; secrets are supplied by protected option files, environment injection with appropriate controls, or a secret manager.
  • Representative schema size, workload, storage capacity, network policy and recovery objectives are documented for the target environment.

Key concepts

user@host identity
MySQL-family authorization matches both an account name and its permitted client origin, not the name alone.
effective configuration
The final runtime values after packaged defaults and ordered configuration fragments have been combined.
consistent state
Data and metadata captured at one valid transactional or recovery point rather than a mixture of moments.
recovery objective
The acceptable data-loss window and restoration time that drive backup, replication and validation design.
positive and negative verification
Proving an intended operation succeeds and an operation outside the granted/security boundary fails.

Before you copy

Values used in this guide

{{database}}

Application database name; use an identifier approved by the database naming policy.

Example: billing
{{rows}}

Operator-reviewed value used by this tutorial for rows; derive it from the target environment and never from untrusted request data.

Example: example-rows
{{safeSelectStatement}}

Operator-reviewed value used by this tutorial for safeSelectStatement; derive it from the target environment and never from untrusted request data.

Example: SELECT id,status FROM invoices ORDER BY id DESC LIMIT 50
{{seconds}}

Operator-reviewed value used by this tutorial for seconds; derive it from the target environment and never from untrusted request data.

Example: 0.5
{{table}}

Operator-reviewed value used by this tutorial for table; derive it from the target environment and never from untrusted request data.

Example: invoices

Security and production boundaries

  • Never place database passwords, encryption keys, recovery codes or private certificates directly in reusable shell history or repository files.
  • Do not use `%`, global grants, disabled TLS verification, public administrative interfaces or world-readable dumps as troubleshooting shortcuts.
  • Treat backups, slow logs and phpMyAdmin sessions as sensitive data; encrypt, restrict, rotate and monitor them according to the same or stronger policy as production.

Stop before continuing if

  • Stop if the server version/config family is ambiguous, the backup cannot be restored, secrets would be exposed, or a proposed command affects databases/accounts beyond the declared scope.
  • Do not continue when native validation, representative workload, expected-denial test, TLS identity, replication continuity or storage capacity evidence is incomplete for this procedure.
  • Immediately isolate or revert a change that broadens a listener/grant, breaks application health, introduces replication error, loses recoverability or produces unbounded sensitive logs.
01

instruction

Capture workload and server baseline

read-only

Record uptime, throughput, connections, buffer pool, CPU, I/O, active statements, and current slow-log variables before changing observability.

Why this step matters

Capture workload and server baseline is a separate checkpoint because it establishes one auditable part of find expensive sql using measured evidence without permanently logging every query or leaking sensitive statements. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Record uptime, throughput, connections, buffer pool, CPU, I/O, active statements, and current slow-log variables before changing observability. Read the complete command or configuration before execution, replace the documented placeholders, and compare the target host with the supported environments listed at the beginning of this tutorial.

Capture the before-state and the exact output in the change record. If this step modifies a file or service, validate the candidate with its native checker before any reload; a successful process exit alone does not prove that the application uses the intended value.

Use a representative staging or canary host first. Repeat the stated checkpoint immediately, because proceeding with ambiguous evidence makes the later end-to-end verification and rollback materially harder.

System changes

  • This step may affect the files, packages, identities, services, or runtime policy named in “Capture workload and server baseline”. The scope must remain limited to the selected application and supported host.

Syntax explained

mysql
Invokes the principal tool or configuration operation for “Capture workload and server baseline”; confirm that it resolves to the expected packaged executable or file before using elevated privileges.
Command
mysql -e "SHOW GLOBAL STATUS WHERE Variable_name IN ('Uptime','Queries','Threads_connected','Threads_running','Slow_queries'); SHOW VARIABLES LIKE 'slow_query%'; SHOW VARIABLES LIKE 'long_query_time';"; iostat -xz 1 3
Example output / evidence
Database and host performance baseline are recorded.

Checkpoint: Checkpoint: Capture workload and server baseline

Continue whenDatabase and host performance baseline are recorded. Treat this concrete evidence as the minimum success signal and also confirm that unrelated services and the previous healthy path remain unchanged.

Stop whenStop before the next step when mysql reports an error, the output differs materially from the example, an unexpected package/service/path is affected, or the required before-state and rollback artifact are missing.

If this step fails

The command for “Capture workload and server baseline” fails, produces no useful evidence, or the expected service/configuration state is absent.

Likely causeA placeholder targets the wrong environment, the installed version uses another path or unit name, permissions are insufficient, or an earlier prerequisite was not satisfied.

Safe checks
  • command -v mysql 2>/dev/null || true
  • systemctl --failed --no-pager 2>/dev/null || true
  • journalctl -p warning -n 50 --no-pager 2>/dev/null || true

ResolutionDo not improvise a privileged workaround. Re-read the environment and variable table, inspect the official source for the installed version, restore the candidate file if necessary, and repeat only this checkpoint.

Security notes

  • Keep secrets out of shell history, process arguments, screenshots, logs, and tutorial placeholders. Elevate only the narrow command that requires it and preserve package signature and TLS verification.

Alternatives

  • When the host layout or software version differs, use the vendor-supported equivalent in a disposable staging environment and document the mapping before touching production.

Stop conditions

  • Stop if the action broadens network exposure, permissions, package sources, writable paths, or service privileges beyond the tutorial's declared architecture.
02

warning

Classify SQL logging sensitivity

caution

Slow logs may contain credentials, tokens, personal data, and literals; set ownership, retention, access, and redaction rules before enabling them.

Why this step matters

Classify SQL logging sensitivity is a separate checkpoint because it establishes one auditable part of find expensive sql using measured evidence without permanently logging every query or leaking sensitive statements. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Slow logs may contain credentials, tokens, personal data, and literals; set ownership, retention, access, and redaction rules before enabling them. Read the complete command or configuration before execution, replace the documented placeholders, and compare the target host with the supported environments listed at the beginning of this tutorial.

Capture the before-state and the exact output in the change record. If this step modifies a file or service, validate the candidate with its native checker before any reload; a successful process exit alone does not prove that the application uses the intended value.

Use a representative staging or canary host first. Repeat the stated checkpoint immediately, because proceeding with ambiguous evidence makes the later end-to-end verification and rollback materially harder.

System changes

  • This step may affect the files, packages, identities, services, or runtime policy named in “Classify SQL logging sensitivity”. The scope must remain limited to the selected application and supported host.

Syntax explained

install
Invokes the principal tool or configuration operation for “Classify SQL logging sensitivity”; confirm that it resolves to the expected packaged executable or file before using elevated privileges.
Command
sudo install -d -o mysql -g adm -m 0750 /var/log/mysql; sudo touch /var/log/mysql/mysql-slow.log; sudo chown mysql:adm /var/log/mysql/mysql-slow.log; sudo chmod 0640 /var/log/mysql/mysql-slow.log
Example output / evidence
The slow log has restricted ownership and a documented retention owner.

Checkpoint: Checkpoint: Classify SQL logging sensitivity

Continue whenThe slow log has restricted ownership and a documented retention owner. Treat this concrete evidence as the minimum success signal and also confirm that unrelated services and the previous healthy path remain unchanged.

Stop whenStop before the next step when install reports an error, the output differs materially from the example, an unexpected package/service/path is affected, or the required before-state and rollback artifact are missing.

If this step fails

The command for “Classify SQL logging sensitivity” fails, produces no useful evidence, or the expected service/configuration state is absent.

Likely causeA placeholder targets the wrong environment, the installed version uses another path or unit name, permissions are insufficient, or an earlier prerequisite was not satisfied.

Safe checks
  • command -v install 2>/dev/null || true
  • systemctl --failed --no-pager 2>/dev/null || true
  • journalctl -p warning -n 50 --no-pager 2>/dev/null || true

ResolutionDo not improvise a privileged workaround. Re-read the environment and variable table, inspect the official source for the installed version, restore the candidate file if necessary, and repeat only this checkpoint.

Security notes

  • Keep secrets out of shell history, process arguments, screenshots, logs, and tutorial placeholders. Elevate only the narrow command that requires it and preserve package signature and TLS verification.

Alternatives

  • When the host layout or software version differs, use the vendor-supported equivalent in a disposable staging environment and document the mapping before touching production.

Stop conditions

  • Stop if the action broadens network exposure, permissions, package sources, writable paths, or service privileges beyond the tutorial's declared architecture.
03

config

Choose a focused threshold and scope

caution

Start with a threshold informed by latency objectives, leave log_queries_not_using_indexes off unless sampling deliberately, and avoid long_query_time=0 on production.

Why this step matters

Choose a focused threshold and scope is a separate checkpoint because it establishes one auditable part of find expensive sql using measured evidence without permanently logging every query or leaking sensitive statements. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Start with a threshold informed by latency objectives, leave log_queries_not_using_indexes off unless sampling deliberately, and avoid long_query_time=0 on production. Read the complete command or configuration before execution, replace the documented placeholders, and compare the target host with the supported environments listed at the beginning of this tutorial.

Capture the before-state and the exact output in the change record. If this step modifies a file or service, validate the candidate with its native checker before any reload; a successful process exit alone does not prove that the application uses the intended value.

Use a representative staging or canary host first. Repeat the stated checkpoint immediately, because proceeding with ambiguous evidence makes the later end-to-end verification and rollback materially harder.

System changes

  • This step may affect the files, packages, identities, services, or runtime policy named in “Choose a focused threshold and scope”. The scope must remain limited to the selected application and supported host.

Syntax explained

mysql
Invokes the principal tool or configuration operation for “Choose a focused threshold and scope”; confirm that it resolves to the expected packaged executable or file before using elevated privileges.
{{…}}
Marks an operator-supplied value. Replace every placeholder deliberately; do not paste untrusted text or leave braces in the production command.
configuration file
Is the persistent configuration boundary changed by this step. Keep an attributable backup and validate the consumer before reload.
Configuration
Fill variables0/2 ready

Values stay on this page and are never sent or saved.

mysql -e "SET PERSIST long_query_time={{seconds}}; SET PERSIST min_examined_row_limit={{rows}};"
Example output / evidence
Finite long_query_time and examined-row thresholds are persisted.

Checkpoint: Checkpoint: Choose a focused threshold and scope

Continue whenFinite long_query_time and examined-row thresholds are persisted. Treat this concrete evidence as the minimum success signal and also confirm that unrelated services and the previous healthy path remain unchanged.

Stop whenStop before the next step when mysql reports an error, the output differs materially from the example, an unexpected package/service/path is affected, or the required before-state and rollback artifact are missing.

If this step fails

The command for “Choose a focused threshold and scope” fails, produces no useful evidence, or the expected service/configuration state is absent.

Likely causeA placeholder targets the wrong environment, the installed version uses another path or unit name, permissions are insufficient, or an earlier prerequisite was not satisfied.

Safe checks
  • command -v mysql 2>/dev/null || true
  • systemctl --failed --no-pager 2>/dev/null || true
  • journalctl -p warning -n 50 --no-pager 2>/dev/null || true

ResolutionDo not improvise a privileged workaround. Re-read the environment and variable table, inspect the official source for the installed version, restore the candidate file if necessary, and repeat only this checkpoint.

Security notes

  • Keep secrets out of shell history, process arguments, screenshots, logs, and tutorial placeholders. Elevate only the narrow command that requires it and preserve package signature and TLS verification.

Alternatives

  • When the host layout or software version differs, use the vendor-supported equivalent in a disposable staging environment and document the mapping before touching production.

Stop conditions

  • Stop if the action broadens network exposure, permissions, package sources, writable paths, or service privileges beyond the tutorial's declared architecture.
04

command

Enable FILE slow logging

caution

Set the explicit log file and enable slow_query_log for a bounded observation window.

Why this step matters

Enable FILE slow logging is a separate checkpoint because it establishes one auditable part of find expensive sql using measured evidence without permanently logging every query or leaking sensitive statements. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Set the explicit log file and enable slow_query_log for a bounded observation window. Read the complete command or configuration before execution, replace the documented placeholders, and compare the target host with the supported environments listed at the beginning of this tutorial.

Capture the before-state and the exact output in the change record. If this step modifies a file or service, validate the candidate with its native checker before any reload; a successful process exit alone does not prove that the application uses the intended value.

Use a representative staging or canary host first. Repeat the stated checkpoint immediately, because proceeding with ambiguous evidence makes the later end-to-end verification and rollback materially harder.

System changes

  • This step may affect the files, packages, identities, services, or runtime policy named in “Enable FILE slow logging”. The scope must remain limited to the selected application and supported host.

Syntax explained

mysql
Invokes the principal tool or configuration operation for “Enable FILE slow logging”; confirm that it resolves to the expected packaged executable or file before using elevated privileges.
Command
mysql -e "SET PERSIST slow_query_log_file='/var/log/mysql/mysql-slow.log'; SET PERSIST slow_query_log=ON;"; mysql -e "SHOW VARIABLES WHERE Variable_name IN ('slow_query_log','slow_query_log_file','long_query_time','min_examined_row_limit');"
Example output / evidence
Slow logging is ON with the intended file and thresholds.

Checkpoint: Checkpoint: Enable FILE slow logging

Continue whenSlow logging is ON with the intended file and thresholds. Treat this concrete evidence as the minimum success signal and also confirm that unrelated services and the previous healthy path remain unchanged.

Stop whenStop before the next step when mysql reports an error, the output differs materially from the example, an unexpected package/service/path is affected, or the required before-state and rollback artifact are missing.

If this step fails

The command for “Enable FILE slow logging” fails, produces no useful evidence, or the expected service/configuration state is absent.

Likely causeA placeholder targets the wrong environment, the installed version uses another path or unit name, permissions are insufficient, or an earlier prerequisite was not satisfied.

Safe checks
  • command -v mysql 2>/dev/null || true
  • systemctl --failed --no-pager 2>/dev/null || true
  • journalctl -p warning -n 50 --no-pager 2>/dev/null || true

ResolutionDo not improvise a privileged workaround. Re-read the environment and variable table, inspect the official source for the installed version, restore the candidate file if necessary, and repeat only this checkpoint.

Security notes

  • Keep secrets out of shell history, process arguments, screenshots, logs, and tutorial placeholders. Elevate only the narrow command that requires it and preserve package signature and TLS verification.

Alternatives

  • When the host layout or software version differs, use the vendor-supported equivalent in a disposable staging environment and document the mapping before touching production.

Stop conditions

  • Stop if the action broadens network exposure, permissions, package sources, writable paths, or service privileges beyond the tutorial's declared architecture.
05

config

Configure safe rotation

caution

Use logrotate with mysql/admin ownership, copytruncate only if required by platform policy, or flush logs after rotation; test in debug mode.

Why this step matters

Configure safe rotation is a separate checkpoint because it establishes one auditable part of find expensive sql using measured evidence without permanently logging every query or leaking sensitive statements. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Use logrotate with mysql/admin ownership, copytruncate only if required by platform policy, or flush logs after rotation; test in debug mode. Read the complete command or configuration before execution, replace the documented placeholders, and compare the target host with the supported environments listed at the beginning of this tutorial.

Capture the before-state and the exact output in the change record. If this step modifies a file or service, validate the candidate with its native checker before any reload; a successful process exit alone does not prove that the application uses the intended value.

Use a representative staging or canary host first. Repeat the stated checkpoint immediately, because proceeding with ambiguous evidence makes the later end-to-end verification and rollback materially harder.

System changes

  • This step may affect the files, packages, identities, services, or runtime policy named in “Configure safe rotation”. The scope must remain limited to the selected application and supported host.

Syntax explained

sudoedit
Invokes the principal tool or configuration operation for “Configure safe rotation”; confirm that it resolves to the expected packaged executable or file before using elevated privileges.
/etc/logrotate.d/mysql-slow
Is the persistent configuration boundary changed by this step. Keep an attributable backup and validate the consumer before reload.
File /etc/logrotate.d/mysql-slow
Configuration
sudoedit /etc/logrotate.d/mysql-slow; sudo logrotate --debug /etc/logrotate.d/mysql-slow
Example output / evidence
The rotation policy parses and retains restricted permissions.

Checkpoint: Checkpoint: Configure safe rotation

Continue whenThe rotation policy parses and retains restricted permissions. Treat this concrete evidence as the minimum success signal and also confirm that unrelated services and the previous healthy path remain unchanged.

Stop whenStop before the next step when sudoedit reports an error, the output differs materially from the example, an unexpected package/service/path is affected, or the required before-state and rollback artifact are missing.

If this step fails

The command for “Configure safe rotation” fails, produces no useful evidence, or the expected service/configuration state is absent.

Likely causeA placeholder targets the wrong environment, the installed version uses another path or unit name, permissions are insufficient, or an earlier prerequisite was not satisfied.

Safe checks
  • command -v sudoedit 2>/dev/null || true
  • systemctl --failed --no-pager 2>/dev/null || true
  • journalctl -p warning -n 50 --no-pager 2>/dev/null || true

ResolutionDo not improvise a privileged workaround. Re-read the environment and variable table, inspect the official source for the installed version, restore the candidate file if necessary, and repeat only this checkpoint.

Security notes

  • Keep secrets out of shell history, process arguments, screenshots, logs, and tutorial placeholders. Elevate only the narrow command that requires it and preserve package signature and TLS verification.

Alternatives

  • When the host layout or software version differs, use the vendor-supported equivalent in a disposable staging environment and document the mapping before touching production.

Stop conditions

  • Stop if the action broadens network exposure, permissions, package sources, writable paths, or service privileges beyond the tutorial's declared architecture.
06

instruction

Aggregate slow statements

read-only

Use mysqldumpslow or an approved digest tool on a copied, access-controlled log; rank by total time, mean time, rows examined, and frequency.

Why this step matters

Aggregate slow statements is a separate checkpoint because it establishes one auditable part of find expensive sql using measured evidence without permanently logging every query or leaking sensitive statements. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Use mysqldumpslow or an approved digest tool on a copied, access-controlled log; rank by total time, mean time, rows examined, and frequency. Read the complete command or configuration before execution, replace the documented placeholders, and compare the target host with the supported environments listed at the beginning of this tutorial.

Capture the before-state and the exact output in the change record. If this step modifies a file or service, validate the candidate with its native checker before any reload; a successful process exit alone does not prove that the application uses the intended value.

Use a representative staging or canary host first. Repeat the stated checkpoint immediately, because proceeding with ambiguous evidence makes the later end-to-end verification and rollback materially harder.

System changes

  • This step may affect the files, packages, identities, services, or runtime policy named in “Aggregate slow statements”. The scope must remain limited to the selected application and supported host.

Syntax explained

mysqldumpslow
Invokes the principal tool or configuration operation for “Aggregate slow statements”; confirm that it resolves to the expected packaged executable or file before using elevated privileges.
Command
sudo mysqldumpslow -s at -t 20 /var/log/mysql/mysql-slow.log; sudo mysqldumpslow -s c -t 20 /var/log/mysql/mysql-slow.log
Example output / evidence
Top time-consuming and most frequent normalized statements are listed.

Checkpoint: Checkpoint: Aggregate slow statements

Continue whenTop time-consuming and most frequent normalized statements are listed. Treat this concrete evidence as the minimum success signal and also confirm that unrelated services and the previous healthy path remain unchanged.

Stop whenStop before the next step when mysqldumpslow reports an error, the output differs materially from the example, an unexpected package/service/path is affected, or the required before-state and rollback artifact are missing.

If this step fails

The command for “Aggregate slow statements” fails, produces no useful evidence, or the expected service/configuration state is absent.

Likely causeA placeholder targets the wrong environment, the installed version uses another path or unit name, permissions are insufficient, or an earlier prerequisite was not satisfied.

Safe checks
  • command -v mysqldumpslow 2>/dev/null || true
  • systemctl --failed --no-pager 2>/dev/null || true
  • journalctl -p warning -n 50 --no-pager 2>/dev/null || true

ResolutionDo not improvise a privileged workaround. Re-read the environment and variable table, inspect the official source for the installed version, restore the candidate file if necessary, and repeat only this checkpoint.

Security notes

  • Keep secrets out of shell history, process arguments, screenshots, logs, and tutorial placeholders. Elevate only the narrow command that requires it and preserve package signature and TLS verification.

Alternatives

  • When the host layout or software version differs, use the vendor-supported equivalent in a disposable staging environment and document the mapping before touching production.

Stop conditions

  • Stop if the action broadens network exposure, permissions, package sources, writable paths, or service privileges beyond the tutorial's declared architecture.
07

instruction

Analyze a candidate in a safe environment

read-only

Run EXPLAIN first; use EXPLAIN ANALYZE only on a replica or controlled transaction because it executes the statement.

Why this step matters

Analyze a candidate in a safe environment is a separate checkpoint because it establishes one auditable part of find expensive sql using measured evidence without permanently logging every query or leaking sensitive statements. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Run EXPLAIN first; use EXPLAIN ANALYZE only on a replica or controlled transaction because it executes the statement. Read the complete command or configuration before execution, replace the documented placeholders, and compare the target host with the supported environments listed at the beginning of this tutorial.

Capture the before-state and the exact output in the change record. If this step modifies a file or service, validate the candidate with its native checker before any reload; a successful process exit alone does not prove that the application uses the intended value.

Use a representative staging or canary host first. Repeat the stated checkpoint immediately, because proceeding with ambiguous evidence makes the later end-to-end verification and rollback materially harder.

System changes

  • This step may affect the files, packages, identities, services, or runtime policy named in “Analyze a candidate in a safe environment”. The scope must remain limited to the selected application and supported host.

Syntax explained

mysql
Invokes the principal tool or configuration operation for “Analyze a candidate in a safe environment”; confirm that it resolves to the expected packaged executable or file before using elevated privileges.
{{…}}
Marks an operator-supplied value. Replace every placeholder deliberately; do not paste untrusted text or leave braces in the production command.
Command
Fill variables0/2 ready

Values stay on this page and are never sent or saved.

mysql {{database}} -e "EXPLAIN FORMAT=TREE {{safeSelectStatement}};"
Example output / evidence
Plan, access type, row estimates, and filters are recorded without modifying data.

Checkpoint: Checkpoint: Analyze a candidate in a safe environment

Continue whenPlan, access type, row estimates, and filters are recorded without modifying data. Treat this concrete evidence as the minimum success signal and also confirm that unrelated services and the previous healthy path remain unchanged.

Stop whenStop before the next step when mysql reports an error, the output differs materially from the example, an unexpected package/service/path is affected, or the required before-state and rollback artifact are missing.

If this step fails

The command for “Analyze a candidate in a safe environment” fails, produces no useful evidence, or the expected service/configuration state is absent.

Likely causeA placeholder targets the wrong environment, the installed version uses another path or unit name, permissions are insufficient, or an earlier prerequisite was not satisfied.

Safe checks
  • command -v mysql 2>/dev/null || true
  • systemctl --failed --no-pager 2>/dev/null || true
  • journalctl -p warning -n 50 --no-pager 2>/dev/null || true

ResolutionDo not improvise a privileged workaround. Re-read the environment and variable table, inspect the official source for the installed version, restore the candidate file if necessary, and repeat only this checkpoint.

Security notes

  • Keep secrets out of shell history, process arguments, screenshots, logs, and tutorial placeholders. Elevate only the narrow command that requires it and preserve package signature and TLS verification.

Alternatives

  • When the host layout or software version differs, use the vendor-supported equivalent in a disposable staging environment and document the mapping before touching production.

Stop conditions

  • Stop if the action broadens network exposure, permissions, package sources, writable paths, or service privileges beyond the tutorial's declared architecture.
08

verification

Test one reversible optimization and compare

caution

Add or alter an index first on staging or a controlled replica, refresh statistics, replay the query, compare plan and latency, and watch write cost.

Why this step matters

Test one reversible optimization and compare is a separate checkpoint because it establishes one auditable part of find expensive sql using measured evidence without permanently logging every query or leaking sensitive statements. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Add or alter an index first on staging or a controlled replica, refresh statistics, replay the query, compare plan and latency, and watch write cost. Read the complete command or configuration before execution, replace the documented placeholders, and compare the target host with the supported environments listed at the beginning of this tutorial.

Capture the before-state and the exact output in the change record. If this step modifies a file or service, validate the candidate with its native checker before any reload; a successful process exit alone does not prove that the application uses the intended value.

Use a representative staging or canary host first. Repeat the stated checkpoint immediately, because proceeding with ambiguous evidence makes the later end-to-end verification and rollback materially harder.

System changes

  • This step may affect the files, packages, identities, services, or runtime policy named in “Test one reversible optimization and compare”. The scope must remain limited to the selected application and supported host.

Syntax explained

mysql
Invokes the principal tool or configuration operation for “Test one reversible optimization and compare”; confirm that it resolves to the expected packaged executable or file before using elevated privileges.
{{…}}
Marks an operator-supplied value. Replace every placeholder deliberately; do not paste untrusted text or leave braces in the production command.
Command
Fill variables0/3 ready

Values stay on this page and are never sent or saved.

mysql {{database}} -e "ANALYZE TABLE {{table}}; EXPLAIN ANALYZE {{safeSelectStatement}};"
Example output / evidence
Measured latency or rows examined improve without unacceptable write or storage cost.

Checkpoint: Checkpoint: Test one reversible optimization and compare

Continue whenMeasured latency or rows examined improve without unacceptable write or storage cost. Treat this concrete evidence as the minimum success signal and also confirm that unrelated services and the previous healthy path remain unchanged.

Stop whenStop before the next step when mysql reports an error, the output differs materially from the example, an unexpected package/service/path is affected, or the required before-state and rollback artifact are missing.

If this step fails

The command for “Test one reversible optimization and compare” fails, produces no useful evidence, or the expected service/configuration state is absent.

Likely causeA placeholder targets the wrong environment, the installed version uses another path or unit name, permissions are insufficient, or an earlier prerequisite was not satisfied.

Safe checks
  • command -v mysql 2>/dev/null || true
  • systemctl --failed --no-pager 2>/dev/null || true
  • journalctl -p warning -n 50 --no-pager 2>/dev/null || true

ResolutionDo not improvise a privileged workaround. Re-read the environment and variable table, inspect the official source for the installed version, restore the candidate file if necessary, and repeat only this checkpoint.

Security notes

  • Keep secrets out of shell history, process arguments, screenshots, logs, and tutorial placeholders. Elevate only the narrow command that requires it and preserve package signature and TLS verification.

Alternatives

  • When the host layout or software version differs, use the vendor-supported equivalent in a disposable staging environment and document the mapping before touching production.

Stop conditions

  • Stop if the action broadens network exposure, permissions, package sources, writable paths, or service privileges beyond the tutorial's declared architecture.

Finish line

Verification checklist

Bounded loggingmysql -NBe "SELECT @@slow_query_log,@@long_query_time,@@min_examined_row_limit,@@slow_query_log_file"Logging state and thresholds match the observation plan.
Post-change workloadmysql -e "SHOW GLOBAL STATUS WHERE Variable_name IN ('Slow_queries','Threads_running');"; tail -n 20 /var/log/mysql/mysql-slow.logRepresentative workload meets the objective without new slow-query regressions.

Recovery guidance

Common problems and safe checks

The native client reports access denied even though a similarly named account has grants.

Likely causeDatabase identities include a host component and authentication plugin; the connection matched another user@host row or transport.

Safe checks
  • mysql -e 'SELECT USER(), CURRENT_USER();'
  • mysql -e "SELECT User,Host,plugin FROM mysql.user ORDER BY User,Host;"
  • mysql -e 'SHOW GRANTS FOR CURRENT_USER;'

ResolutionIdentify the exact matched account and create or modify only the intended user@host identity; do not broaden it to '%' as a shortcut.

TLS is enabled but the client still connects with an unexpected identity or without hostname verification.

Likely causeThe client uses encryption-only mode, connects by an address absent from the SAN, trusts a broad system CA set, or another option file overrides policy.

Safe checks
  • mysql --print-defaults
  • openssl s_client -starttls mysql -connect db.example.com:3306 -servername db.example.com </dev/null
  • mysql -e "SHOW STATUS LIKE 'Ssl_%';"

ResolutionUse the documented hostname, intended CA and VERIFY_IDENTITY-equivalent mode; remove conflicting client options and retest expected failure with a wrong hostname.

A change passes syntax checks but application errors, lag, latency or disk use increases.

Likely causeThe candidate is syntactically valid but workload, lock, cache, rotation, privilege or version assumptions are wrong.

Safe checks
  • mysqladmin ping
  • mysql -e 'SHOW PROCESSLIST;'
  • journalctl -u mariadb -u mysql -p warning --since '-10 min' --no-pager

ResolutionStop promotion, preserve evidence, revert the smallest changed configuration/account/routing layer and reproduce with a safe representative workload.

After the procedure

Alternatives and next steps

Consider these alternatives

  • Use a managed database only after private connectivity, identity, encryption, monitoring, backup restoration and responsibility boundaries are proven.
  • Use immutable configuration automation and a disposable recovery environment when it produces stronger review, repeatability and rollback than manual host edits.

Operate it safely

  • Automate health, capacity, certificate, replication, backup and restore-test alerts with an owner and response runbook.
  • Review accounts, grants, package support, encryption material and recovery evidence after every major release and at least every 90–180 days.
  • Practice the rollback or restore from recorded artifacts on an isolated host so the documented time and dependencies remain credible.

Reference

Frequently asked questions

Does a successful command mean the procedure is complete?

No. Database tools may return success before application compatibility, replica replay, restore integrity, TLS identity or expected denials are proven. Use every checkpoint and final verification.

Can an administrator use the application account for convenience?

No. Separate identities make least privilege, rotation, audit and incident containment possible; the application should not hold administrative capabilities.

Why test restoration or negative access separately?

A backup that cannot restore and a grant that was never tested for denial are assumptions, not controls. Isolation makes those tests safe and repeatable.

Recovery

Rollback

Revert the tested schema change and disable or restore prior slow-log settings after the observation window.

  1. Restore prior SET PERSIST values or RESET PERSIST the added variables, then verify effective settings.
  2. Drop only the newly tested index after confirming no other workload depends on it.
  3. Retain protected evidence for the agreed period, then delete it through the sensitive-log process.

Evidence

Sources and review

Verified 2026-07-25Review due 2026-10-23
MySQL 8.4 slow query logofficialMySQL 8.4 EXPLAIN statementofficialMySQL 8.4 account management statementsofficial