LEARN. PRACTICE. GROW.Define a verifiable outcome
The lab starts with accounts A and B, balances 40 and 60 and two audit rows created by a trigger. It adds a totals view, a reading procedure and a deliberately disabled event. This inventory supplies verifiable restore outcomes: two rows, total 100 and objects needed by the workflow. A zero dump exit code only confirms the command within requested scope; it does not establish that scope covers the whole service. Record tables, data, executable objects, accounts and configuration needing separate treatment. Examples are synthetic and represent neither real banking data nor internal banking procedures.
Snapshot and concurrency boundaries
While dumps were produced, another connection retained an update of A to 999 without commit. The single-transaction dump later recovered committed value 40, and the pending write was rolled back. This demonstrates exclusion of that uncommitted change on InnoDB tables; it does not test every concurrency combination. No DDL ran during copying. Documentation limits consistency guarantees to appropriate engines and operations, so this result should not be extended to MyISAM or concurrent schema changes. Define coordination between backup and changes before the window, including what to do when an incompatible operation is already running.
Scope includes more than tables
The first restore used a dump without the routines and events options. It recovered two tables, one view and one trigger, but no procedure or event. The next dump explicitly included those objects, and target inventory confirmed both. Do not assess completeness only through table count or file size. An application can start and fail later when it calls an absent routine or expects scheduled work. The experiment’s event remained disabled and the scheduler stayed OFF to prevent scheduled execution. Event presence does not establish that it should be enabled automatically during recovery; activation needs its own service decision.
Structure without data and data without structure
The no-data option produced structure and objects but left accounts and audit empty on the target. A file containing only accounts data failed with 1146 when the table did not yet exist. After loading matching structure, the same file inserted both accounts. The existing trigger then created two audit rows. This effect helps explain why recovery order and scope matter: loading data into already active objects can execute additional logic. The test should observe business outcome and associated effects rather than only confirm INSERT completion. Matching structure is necessary, and its executable behavior also belongs in acceptance.
The source can advance after backup
After dumps finished, the source committed A=999 and added LATE=5, reaching three accounts and total 1064. The complete restore still showed A=40, B=60 and total 100. The file reproduces captured state; it does not automatically incorporate later changes. The difference does not prove corruption, but the recovery point must be explained to the business. The lab neither replayed binary logs nor recovered to a selected instant. If the requirement is newer than the backup, an appropriate incremental recovery plan and execution evidence are needed. Log existence alone does not establish that the chain was applied correctly.
Identify artifact and configuration
The script recorded the dump’s SHA256 and confirmed its bytes stayed unchanged through restores. This identifies the artifact used and helps detect changes, but proves neither origin authenticity nor functional completeness. Both servers ran MySQL 9.7.2 and reported GTID mode ON. The dump used set-gtid-purged=OFF in this isolated logical recovery exercise; it did not establish preservation of GTID history for replica setup. Configuration and login options were explicitly bounded to private lab sockets. In a real service, credentials, artifact protection, transport and the GTID policy choice need validation appropriate to the environment. A matching hash cannot replace those decisions.
mysqldump --no-defaults --no-login-paths --protocol=SOCKET \
--socket=/owned/source/mysql.sock --user=root \
--single-transaction --quick --no-tablespaces --set-gtid-purged=OFF \
--routines --events --databases lab > full.sql
# Lab-only empty-password account on a private socket. Review options for a real service.
mysql --no-defaults --no-login-paths --protocol=SOCKET \
--socket=/owned/target/mysql.sock --user=root --batch < full.sqlThe ordinary dump recovered tables, a view and a trigger; only the version with explicit options included the procedure and disabled event.
Common pitfalls
An existing file as recovery; one database dump as account backup; single-transaction as protection for every engine or DDL operation.
Related topics: Logical backup and object inventory · Permissions and recovery acceptance
Evidence starts with chosen scope and ends with observed target state, including objects the application depends on.
Reference: Logical backup scope and options · MySQL 9.7 LTS with InnoDB reference semantics