Back up, restore, and test a MySQL database
Create a consistent logical backup, restore it into isolation, verify representative data, and retain evidence.
Prove that a MySQL backup is recoverable instead of merely confirming a dump exists.
- MySQL Community Server 8.0, 8.4 LTS
- Backup identity Use least privilege for the selected objects.
- Credential storage Use mysql_config_editor or a protected option file, never a command-line password.
- Free space Reserve room for the archive and isolated restore.
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
- A compressed MySQL 8.4 logical backup that contains one selected database, its tables, views, triggers, routines, and scheduled events.
- A protected artifact with an independently recorded SHA-256 checksum and a copy in a separate failure domain.
- An isolated restore rehearsal that proves schema creation, representative row counts, stored objects, and an application-level read without touching production.
- mysqldump finishes without ignored diagnostics and gzip validates the complete stream.
- The checksum file matches the retained artifact and the archive can be read on the restore host.
- The disposable database contains the same expected table count and critical invariant recorded before the backup.
- The operator can state the backup scope, recovery point, restore duration, missing physical-recovery capabilities, and the exact evidence retained after the rehearsal.
Architecture
How the parts fit together
mysqldump is a client-side logical export. It reads metadata and rows through a normal authenticated MySQL connection, emits SQL, and streams that SQL through gzip into an ordinary file. A separate MySQL target executes the SQL into a deliberately named disposable database; comparison queries then test the restored state.
- The operator records source identity and invariants before taking the backup.
- mysqldump starts a consistent transaction for InnoDB, reads rows incrementally, and emits database objects plus data.
- gzip compresses the stream into a restricted artifact; sha256sum records its exact content.
- The artifact is copied to a separate failure domain and revalidated there.
- A clean disposable database is created, the SQL is loaded explicitly into it, and representative invariants are compared.
- Evidence is retained and only the disposable database is removed.
Assumptions
- The selected workload is predominantly InnoDB. --single-transaction does not make concurrent MyISAM or MEMORY changes consistent.
- No schema-changing statement such as ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, or TRUNCATE TABLE runs during the dump window.
- The backup identity can read every selected table, view, trigger, routine, and event required by the recovery scope.
- The restore server is compatible with the source SQL and has enough disk, temporary space, memory, and time for a full rehearsal.
- The procedure protects one application database. A complete server recovery design separately covers accounts, grants, configuration, encryption keys, binary logs, and point-in-time recovery.
- Examples use POSIX tools for the file pipeline. Windows operators can use equivalent gzip and checksum tooling while retaining the same database controls.
Key concepts
- Logical backup
- SQL definitions and row data that reconstruct database objects; it is portable but usually slower to restore than a physical backup.
- Consistent snapshot
- A transactionally coherent view of InnoDB data taken from one point in time while normal reads and writes continue.
- Recovery point objective
- The maximum acceptable amount of data loss, which determines whether periodic dumps alone are sufficient.
- Recovery time objective
- The maximum acceptable outage, measured here with an actual restore rather than an estimate based on file size.
- Invariant
- A stable, meaningful property such as an order count, latest timestamp, or financial total used to compare source and restored data.
- Failure domain
- Infrastructure that can fail independently; a second directory on the same disk is not an independent backup location.
Before you copy
Values used in this guide
{{database}}Exact source database name to export.
Example: appdb{{backup}}Absolute path of the compressed backup artifact.
Example: /srv/backups/mysql/appdb-2026-07-25.sql.gz{{restoreDatabase}}Unique disposable database name that cannot be confused with production.
Example: restore_test_20260725{{criticalTable}}Representative restored table used for a stable row-count or domain invariant.
Example: ordersSecurity and production boundaries
- A logical dump can contain credentials, personal data, API tokens, password hashes, and business secrets stored in tables. Treat it at least as sensitively as the live database.
- Use mysql_config_editor or a root-readable option file. Never pass --password=value because command arguments may be visible to other users and monitoring systems.
- Do not restore a dump from an untrusted source into a privileged server. SQL backups contain executable statements, routines, triggers, and definers.
- Encrypt backups at rest and in transit according to the data classification; file mode 0600 alone does not protect stolen disks or object-storage credentials.
- The disposable restore name is a safety boundary. Resolve and display it before CREATE or DROP, and refuse names that match any production database.
Stop before continuing if
- Stop if the selected database contains nontransactional tables whose write consistency is not controlled.
- Stop if schema migrations or bulk DDL cannot be paused for the dump window.
- Stop if mysqldump writes warnings or errors, any pipeline stage fails, or the produced artifact is unexpectedly small.
- Stop if the restore target name, host, or credentials could point to production.
- Stop if the restore reports an error, expected routines or events are absent, or application invariants differ.
- Do not delete the previous verified backup until the new artifact has passed an isolated restore and retention requirements.
command
Record source invariants
Capture server version, table count, and approximate database size.
Why this step matters
A restore comparison is meaningful only when source identity, version, scope, object counts, storage engines, and representative data are recorded before the backup begins.
What to understand
SELECT VERSION identifies compatibility requirements. information_schema supplies a reproducible table count without opening every object.
Extend the evidence with engine distribution, database size, routine and event counts, and one or more domain invariants whose expected values are understood by the application owner.
Use an exact database name in the query. A typo that returns zero is a failed preflight, not a valid empty baseline.
System changes
- No persistent change; reads server metadata and records source evidence outside MySQL.
Syntax explained
--login-path=backup- Loads the named encrypted client option group rather than exposing a password in argv.
-N- Suppresses column headings for stable evidence output.
-B- Uses batch output suitable for logs and automation.
-e- Executes the quoted SQL and exits.
Values stay on this page and are never sent or saved.
mysql --login-path=backup -NBe "SELECT VERSION(); SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='{{database}}';"8.4.2 47
Checkpoint: Freeze the evidence baseline
mysql --login-path=backup -NBe "SELECT VERSION(); SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='appdb';"Continue whenThe version and nonzero object count identify the intended source; domain invariants are saved with a timestamp.
Stop whenThe source identity is ambiguous, the scope is empty, nontransactional tables are unexplained, or credentials are broader than approved.
If this step fails
The table count is zero or much smaller than expected.
Likely causeThe database name is wrong or the backup user cannot see every selected object.
mysql --login-path=backup -NBe "SHOW DATABASES;"mysql --login-path=backup -e "SHOW GRANTS FOR CURRENT_USER();"
ResolutionCorrect the exact scope or least-privilege grants before creating an artifact.
Security notes
- Do not print credentials or sensitive sample rows into the evidence log.
Alternatives
- Record stronger application invariants such as totals grouped by immutable business date.
Stop conditions
- Do not continue without a reviewed source and restore scope.
command
Create a consistent logical dump
For InnoDB, use one transaction and include routines, events, and triggers.
Why this step matters
The export must represent one coherent InnoDB point, stream large tables without excessive memory, and explicitly include stored objects that MySQL 8.4 does not add automatically.
What to understand
--single-transaction starts a repeatable-read snapshot for transactional tables. It does not make MyISAM or MEMORY data consistent and it must not overlap schema-changing DDL.
--quick retrieves rows incrementally. --routines and --events include stored programs disabled by default; triggers are named explicitly to make scope reviewable.
--set-gtid-purged=OFF avoids injecting GTID state into this isolated database restore. Decide separately whether a replication or whole-instance recovery requires source coordinates and GTIDs.
The database is passed without --databases so the dump does not create or select the production database name during the test restore.
System changes
- Creates a new compressed SQL artifact; source rows and schema remain unchanged.
Syntax explained
--single-transaction- Exports a consistent InnoDB snapshot without locking application writes.
--quick- Streams table rows instead of buffering entire results in client memory.
--routines --events --triggers- Includes procedures, functions, scheduled events, and table triggers in the declared scope.
--set-gtid-purged=OFF- Leaves GTID_PURGED statements out of this application-database rehearsal.
gzip -9- Compresses the SQL stream with maximum gzip compression; CPU and duration should be measured.
Values stay on this page and are never sent or saved.
set -o pipefail; mysqldump --login-path=backup --single-transaction --quick --routines --events --triggers --hex-blob --set-gtid-purged=OFF {{database}} | gzip -9 > {{backup}}appdb-2026-07-24.sql.gz created
Checkpoint: Accept only a complete pipeline
test -s /srv/backups/mysql/appdb-2026-07-25.sql.gz && gzip --test /srv/backups/mysql/appdb-2026-07-25.sql.gzContinue whenThe pipeline exits zero and creates a non-empty gzip stream with no mysqldump diagnostic.
Stop whenAny pipeline command fails, output is empty, stderr contains a dump error, or DDL overlapped the snapshot.
If this step fails
mysqldump reports that a table definition changed during the dump.
Likely causeA migration or other DDL operation overlapped the consistent-read export.
mysql --login-path=backup -e "SHOW FULL PROCESSLIST;"mysql --login-path=backup -NBe "SELECT EVENT_NAME,SQL_TEXT FROM performance_schema.events_statements_history_long WHERE SQL_TEXT REGEXP '^(ALTER|CREATE|DROP|RENAME|TRUNCATE)' ORDER BY TIMER_START DESC LIMIT 10;"
ResolutionCoordinate a DDL-free window and repeat the entire dump into a new artifact.
Security notes
- Set a restrictive umask before creation so the file is never briefly world-readable.
Alternatives
- Use a supported physical or parallel backup method when logical export cannot meet the backup window.
Stop conditions
- Never retain a partial pipeline result as the latest good backup.
verification
Verify and checksum the artifact
Test gzip integrity, inspect the header, and record SHA-256.
Why this step matters
Compression validity, readable SQL metadata, and a cryptographic digest catch truncation or later alteration before an expensive restore window begins.
What to understand
gzip --test reads the full compressed stream and detects corruption. zcat plus head is only a format sanity check; it does not validate SQL semantics.
sha256sum writes an artifact identity that must travel with the file and be verified after every copy. The checksum is not a signature and does not prove who created the backup.
Inspect headers for source version, database name, and unexpected statements while keeping sensitive data out of shared logs.
System changes
- Creates or replaces the checksum sidecar for the exact backup path; does not change MySQL.
Syntax explained
gzip --test- Reads and validates the complete gzip member without extracting it.
zcat | head -n 20- Shows only initial SQL metadata for a quick scope review.
sha256sum | tee- Displays and records the digest in a sidecar file.
Values stay on this page and are never sent or saved.
gzip --test {{backup}} && zcat {{backup}} | head -n 20 && sha256sum {{backup}} | tee {{backup}}.sha256a64f... appdb-2026-07-24.sql.gz
Checkpoint: Bind checksum to artifact
cd /srv/backups/mysql && sha256sum --check appdb-2026-07-25.sql.gz.sha256Continue whenThe exact backup filename reports OK.
Stop whengzip validation fails, the header names the wrong source, or checksum verification is not OK.
If this step fails
sha256sum --check cannot find the file after transfer.
Likely causeThe artifact was renamed without updating the relative checksum entry or the two files were separated.
pwdls -l /srv/backups/mysqlcat /srv/backups/mysql/appdb-2026-07-25.sql.gz.sha256
ResolutionPreserve the original digest, place artifact and sidecar in a documented directory, and verify the explicit path.
Security notes
- Header inspection can reveal schema names; restrict operational logs appropriately.
Alternatives
- Use a signed manifest when provenance must be cryptographically authenticated.
Stop conditions
- Do not copy or restore an artifact whose integrity check fails.
instruction
Protect and copy the backup
Restrict permissions and copy artifact plus checksum to an independent failure domain.
Why this step matters
The backup is a concentrated copy of production data, so permissions, encryption, retention, and an independent storage location are part of correctness rather than optional housekeeping.
What to understand
Mode 0600 limits local reads and writes to the owner. Verify ownership as well; chmod does not fix a file owned by the wrong account.
Copy both artifact and checksum to storage that does not share the host, disk, credentials, and administrative failure domain of the source.
Record retention and immutability policy. A synchronized deletion or ransomware event can destroy an ordinary writable mirror.
System changes
- Restricts local artifact permissions and creates a protected remote or offline copy according to the chosen transport.
Syntax explained
chmod 600- Grants read/write only to the file owner and removes group/other access.
{{backup}}.sha256- Protects the checksum sidecar under the same ownership boundary.
Values stay on this page and are never sent or saved.
chmod 600 {{backup}} {{backup}}.sha256-rw------- ... appdb-2026-07-24.sql.gz
Checkpoint: Verify the independent copy
stat -c '%U:%G %a %n' /srv/backups/mysql/appdb-2026-07-25.sql.gz*Continue whenArtifact and checksum have the intended backup owner and mode 600; the remote copy independently passes SHA-256.
Stop whenThe destination shares the same failure domain, transport is unencrypted, ownership is wrong, or retention can delete every generation at once.
If this step fails
The backup copy exists remotely but the checksum differs.
Likely causeTransfer corruption, accidental replacement, or the wrong artifact and sidecar were paired.
sha256sum /srv/backups/mysql/appdb-2026-07-25.sql.gzcat /srv/backups/mysql/appdb-2026-07-25.sql.gz.sha256
ResolutionQuarantine the mismatched copy, identify the verified source, transfer again, and retain audit evidence.
Security notes
- Use encrypted transport and encryption at rest; avoid backup credentials with delete permission when append-only storage is available.
Alternatives
- Write to immutable object storage with versioning and retention lock.
Stop conditions
- Do not count a second path on the same host as an off-host backup.
command
Create an isolated restore database
Never test by overwriting the source database.
Why this step matters
A uniquely named clean database prevents the rehearsal from overwriting the source and makes every later verification and cleanup command auditable.
What to understand
Resolve the login path to the intended restore host before CREATE DATABASE. Display hostname, port, server UUID, and current user in the change record.
Use a name with an explicit restore_test prefix and date. Compare it against the source name in automation and fail closed on equality.
For exact charset and collation behavior, record the source defaults and create the test database with compatible values before loading.
System changes
- Creates one empty disposable database on the isolated restore server.
Syntax explained
--login-path=restore- Selects protected credentials and connection settings for the isolated target.
CREATE DATABASE- Creates the explicitly reviewed disposable schema container.
Values stay on this page and are never sent or saved.
mysql --login-path=restore -e "CREATE DATABASE {{restoreDatabase}};"Query OK, 1 row affected
Checkpoint: Prove target isolation
mysql --login-path=restore -NBe "SELECT @@hostname,@@port,@@server_uuid; SHOW DATABASES LIKE 'restore_test_20260725';"Continue whenThe server identity is the isolated restore host and exactly one empty disposable database exists.
Stop whenThe host, UUID, database name, or account could refer to production.
If this step fails
CREATE DATABASE reports that the target already exists.
Likely causeA prior rehearsal was not cleaned up or the chosen name is not unique.
mysql --login-path=restore -NBe "SELECT table_schema,COUNT(*) FROM information_schema.tables WHERE table_schema='restore_test_20260725' GROUP BY table_schema;"
ResolutionInspect and preserve prior evidence; choose a new unique target instead of dropping an unknown database.
Security notes
- Use a restore account constrained to the isolated environment.
Alternatives
- Provision a disposable MySQL container or ephemeral VM with the required server version.
Stop conditions
- Never create or drop the target until server identity and exact name are confirmed.
command
Restore the archive
Stream the decompressed dump through the MySQL client and capture errors.
Why this step matters
Only executing the complete SQL into a clean target proves that definitions, dependencies, data, routines, triggers, events, and client compatibility work together.
What to understand
The client selects restoreDatabase explicitly because the dump intentionally omits CREATE DATABASE and USE for the production name.
Capture stderr, wall-clock duration, client version, and exit status. A final exit code of zero must not hide earlier pipeline failures in automation.
Treat the SQL as executable code. Restore only artifacts produced by the trusted backup pipeline and inspect unexpected definers or privileged statements.
System changes
- Creates schema objects and loads data into the disposable restore database; the source remains read-only.
Syntax explained
zcat {{backup}}- Streams the verified SQL without creating an uncompressed disk copy.
--database={{restoreDatabase}}- Pins execution to the reviewed disposable database.
--login-path=restore- Uses protected isolated-target credentials.
Values stay on this page and are never sent or saved.
set -o pipefail; zcat {{backup}} | mysql --login-path=restore --database={{restoreDatabase}}Restore completed with exit code 0
Checkpoint: Accept a clean restore
mysql --login-path=restore -NBe "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='restore_test_20260725'; SHOW WARNINGS;"Continue whenThe restore exits zero, creates the expected nonzero object count, and leaves no unresolved error.
Stop whenAny statement fails, the target name differs, disk fills, or the process is interrupted.
If this step fails
The import stops on an object DEFINER that does not exist.
Likely causeA routine, view, event, or trigger references a source account absent from the isolated server.
zcat /srv/backups/mysql/appdb-2026-07-25.sql.gz | grep -n -m 20 'DEFINER'mysql --login-path=restore -NBe "SELECT user,host FROM mysql.user ORDER BY user,host;"
ResolutionReview the intended ownership model and provision a safe test identity or create a documented compatibility copy; keep the original artifact unchanged.
Security notes
- Do not restore an untrusted SQL dump with an administrative account.
Alternatives
- Restore into an ephemeral isolated server and destroy the entire instance after evidence capture.
Stop conditions
- Do not interpret a partially populated target as a successful restore.
verification
Compare schema and data invariants
Check object count, critical row counts, routines, and application-level invariants.
Why this step matters
An import can exit successfully while omitting required objects or restoring the wrong point, so structural and business-level comparisons decide whether the artifact meets recovery needs.
What to understand
Compare source-recorded table, routine, trigger, and event counts with the target. Account for intentionally excluded transient objects.
A critical row count is a starting invariant, not a complete application test. Add latest immutable timestamps, referential checks, totals, and read-only application smoke tests.
Record both expected and actual values with the artifact checksum and restore duration so future rehearsals can be trended.
System changes
- No persistent change; reads the restored database and writes a recovery evidence record.
Syntax explained
information_schema.tables- Counts restored table and view metadata for the disposable database.
{{restoreDatabase}}.{{criticalTable}}- Targets one explicitly chosen representative object.
-NBe- Produces compact deterministic comparison output.
Values stay on this page and are never sent or saved.
mysql --login-path=restore -NBe "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='{{restoreDatabase}}'; SELECT COUNT(*) FROM {{restoreDatabase}}.{{criticalTable}};"47 183204
Checkpoint: Sign off recovery invariants
mysql --login-path=restore -NBe "CHECK TABLE restore_test_20260725.orders; SELECT COUNT(*) FROM restore_test_20260725.orders;"Continue whenStructural counts and every approved domain invariant match the saved source evidence.
Stop whenAny expected object is missing, CHECK reports an error, or a domain value differs without an approved explanation.
If this step fails
Object counts match but the application read fails.
Likely causeRequired users, grants, configuration, external dependencies, or server SQL modes are outside the single-database dump.
mysql --login-path=restore -NBe "SELECT @@sql_mode,@@time_zone,@@character_set_server,@@collation_server;"mysql --login-path=restore -e "SHOW GRANTS FOR CURRENT_USER();"
ResolutionAdd the missing whole-service dependency to the recovery plan and repeat the isolated application verification.
Security notes
- Use read-only validation queries and redact sensitive values from evidence.
Alternatives
- Run an automated application acceptance suite against the isolated endpoint.
Stop conditions
- Do not certify a backup based only on table count or successful login.
warning
Require a production restore plan
Define write freeze, recovery point, current-state backup, rollback owner, and application verification.
Why this step matters
A production restore is a separate high-impact incident change that can overwrite newer data, invalidate binary-log recovery, and extend downtime if ownership and stop conditions are not explicit.
What to understand
Define the exact recovery point, write freeze, current-state preservation, responsible approver, restore owner, application verification, traffic plan, and rollback before touching production.
The tested disposable workflow supplies evidence but does not authorize production execution. Point-in-time requirements may need binary logs in addition to the logical dump.
Estimate capacity and duration from the rehearsal, then include DNS, queues, caches, scheduled jobs, replicas, and application credentials in the restoration sequence.
System changes
- Creates an approved production recovery plan; no production database command is executed by this step.
Syntax explained
Recovery point- The exact timestamp or transaction state that must be reconstructed.
Write freeze- The controlled point after which new production writes are blocked or redirected.
Current-state backup- Evidence preserving the pre-restore state for forensic analysis or rollback.
Production restore change plan approved
Checkpoint: Authorize a controlled recovery
Continue whenNamed owners approve the recovery point, artifact checksum, outage, verification, rollback, and communication plan.
Stop whenAny owner, artifact, dependency, current-state copy, or rollback decision is missing.
If this step fails
The requested recovery point is newer than the tested dump.
Likely causeThe incident needs point-in-time recovery but binary-log coverage or rehearsal is missing.
mysql --login-path=backup -NBe "SHOW BINARY LOG STATUS; SHOW BINARY LOGS;"
ResolutionPreserve logs and escalate to the documented point-in-time procedure; do not improvise on production.
Security notes
- Use two-person review for destructive target selection and keep an immutable incident log.
Alternatives
- Restore to a parallel environment and switch traffic after validation when architecture allows.
Stop conditions
- This warning never authorizes an in-place production restore by itself.
command
Remove only the test database
Drop the explicitly named disposable database after retaining evidence.
Why this step matters
Cleanup is intentionally destructive, so it must target only the proven disposable database after evidence, timing, logs, and checksum have been retained.
What to understand
Query server identity and database name immediately before DROP. Terminate or investigate unexpected sessions rather than using broad wildcard cleanup.
Retain the artifact, checksum, restore logs, measured duration, invariants, and issue notes according to policy.
Do not delete the most recent known-good generation merely because a newer backup file exists; promotion requires the completed rehearsal.
System changes
- Permanently drops the explicitly named disposable restore database and all objects inside it.
Syntax explained
DROP DATABASE {{restoreDatabase}}- Irreversibly removes only the named test database.
--login-path=restore- Keeps the destructive action on the isolated restore host.
Values stay on this page and are never sent or saved.
mysql --login-path=restore -e "DROP DATABASE {{restoreDatabase}};"Query OK, 0 rows affected
Checkpoint: Confirm cleanup boundary
mysql --login-path=restore -NBe "SELECT @@hostname,@@server_uuid; SHOW DATABASES LIKE 'restore_test_20260725';"Continue whenThe isolated server and exact disposable name are displayed; retained evidence is complete.
Stop whenThe name lacks the approved test prefix, server identity is unexpected, or evidence has not been retained.
If this step fails
DROP DATABASE reports active application use or the target has unexpected new writes.
Likely causeA test client still runs or the database name was reused for another purpose.
mysql --login-path=restore -e "SHOW FULL PROCESSLIST;"mysql --login-path=restore -NBe "SELECT table_name,update_time FROM information_schema.tables WHERE table_schema='restore_test_20260725';"
ResolutionStop and identify ownership; never force cleanup of a target whose purpose changed.
Security notes
- Require an explicit confirmation in automation and log the exact server UUID plus database name.
Alternatives
- Destroy the entire disposable VM or container after exporting evidence, if its identity is independently proven.
Stop conditions
- Never drop a database selected from an empty variable, wildcard, prefix alone, or unverified host.
Finish line
Verification checklist
gzip --test backup.sql.gzExit code 0.mysql -NBe "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='restore_test';"Matches the source count.mysql -NBe "SELECT COUNT(*) FROM restore_test.critical_table;"Matches the recorded invariant.Recovery guidance
Common problems and safe checks
mysqldump fails with access denied while reading a view, routine, event, or trigger.
Likely causeThe backup identity can read table rows but lacks privileges for every object included in the declared scope.
mysql --login-path=backup -e "SHOW GRANTS FOR CURRENT_USER();"mysql --login-path=backup -NBe "SELECT ROUTINE_SCHEMA,ROUTINE_NAME FROM information_schema.routines WHERE ROUTINE_SCHEMA='appdb';"
ResolutionGrant only the documented privileges required for the selected objects, repeat the complete dump, and do not accept a partial artifact.
The compressed file exists but gzip --test fails or the pipeline returned a nonzero status.
Likely causeDisk exhaustion, an interrupted session, a mysqldump error, or an unchecked pipeline stage produced a truncated stream.
df -h /srv/backups/mysqlgzip --test /srv/backups/mysql/appdb-2026-07-25.sql.gzjournalctl --since '1 hour ago' --no-pager | tail -n 100
ResolutionPreserve the failed artifact for diagnosis, correct capacity or access, enable pipefail in automation, and create a new timestamped backup.
The restore reports unknown collation, unsupported syntax, or a DEFINER error.
Likely causeThe target version or installed components differ, or object ownership embedded in the dump is unavailable.
mysql --login-path=restore -NBe "SELECT VERSION(); SHOW COLLATION;"zcat /srv/backups/mysql/appdb-2026-07-25.sql.gz | grep -n -m 10 -E 'DEFINER|COLLATE'
ResolutionRestore into a version-compatible isolated server, provision required identities safely, or generate a reviewed compatibility export; never edit the only retained backup.
The restore succeeds but a critical row count or business check differs.
Likely causeThe wrong source, artifact, target schema, or invariant was used, or nontransactional data changed during the dump.
sha256sum --check /srv/backups/mysql/appdb-2026-07-25.sql.gz.sha256mysql --login-path=restore -NBe "SELECT DATABASE(); SHOW TABLE STATUS FROM restore_test_20260725;"
ResolutionKeep the target for investigation, compare source evidence and backup scope, and do not promote the backup until the discrepancy is explained and a new rehearsal passes.
Reference
Frequently asked questions
Does a successful mysqldump exit mean the backup is good?
It proves only that the export process reported success. A usable backup also needs artifact integrity, independent retention, an isolated restore, object checks, and application invariants.
Why not use --all-databases?
This procedure intentionally proves one application database. Whole-instance recovery has different privilege, account, grant, GTID, configuration, and system-schema requirements and should be tested as its own plan.
Can writes continue during --single-transaction?
Normal InnoDB reads and writes can continue, but nontransactional tables are not protected and schema-changing statements can invalidate or fail the dump.
Recovery
Rollback
The dump is read-only; remove only isolated restore objects.
- Confirm the exact test database name.
- Drop the disposable restore database.
- Retain the verified backup and checksum according to policy.
Evidence