Back Up and Restore an Ubuntu MySQL App

This walkthrough covers Back Up and Restore an Ubuntu MySQL App. A disposable MySQL database for practice, a restricted backup directory and a separate restore target. Use your own database names and authenticated administrative connection settings.

The video is an illustrated code walkthrough with AI narration. Code blocks are integration excerpts, not a complete tested application. Helper names such as stores, middleware and clients describe responsibilities you must implement in your existing project. No live server, payment provider or device execution is claimed.

Before you begin

Use a separate development environment, fictional data and test credentials. Record dependency versions and follow the official references below for your installed versions. Keep a working baseline and add one workflow at a time.

1. Define what recovery requires

A database dump is only part of application recovery. Identify uploaded files, the application version, configuration, and any keys required to decrypt data. Decide how much recent data loss and downtime are acceptable. Those objectives determine frequency and retention. Keep a written restore procedure that someone other than the original developer can follow, and practise it before an emergency.

Database records
Uploaded files
Application release and dependency lockfiles
Configuration recovery procedure
Encryption keys stored separately

2. Create a protected dump

Create backups with restrictive permissions and make pipeline failures visible. The single-transaction option is useful for transactional tables, but it isn't a universal consistency guarantee for every engine or concurrent schema change. Avoid running migrations during the dump. Use a protected credentials mechanism rather than placing a password in command history. Record the database version and the exact options needed for your schema.

umask 077
set -o pipefail
mysqldump --single-transaction --no-tablespaces appdb |
 gzip > appdb-backup.sql.gz

3. Verify before marking success

Check the compressed file and record a checksum after the dump completes successfully. These checks detect some corruption and transfer problems; they don't prove that the database can be restored. Write to a temporary filename first and rename only after success so incomplete files aren't mistaken for finished backups. Keep logs useful but free of passwords and sensitive record contents.

gzip -t appdb-backup.sql.gz
sha256sum appdb-backup.sql.gz > appdb-backup.sql.gz.sha256
# Write completion metadata only after successful exit codes.

4. Copy backups off the VPS

Copy the verified backup to a separate failure domain. A second directory on the same VPS won't protect against losing the server. Restrict the transfer identity and storage permissions, and decide who can delete historical backups. Consider encryption and immutable retention according to the data's sensitivity. Verify the destination copy and monitor transfer failure instead of assuming that creation on the source is enough.

rsync -av --partial appdb-backup.sql.gz backup-host:/protected/path/
# Use a dedicated restricted identity and verify destination.
# Encrypt before transfer when the storage trust model requires it.

5. Restore into an isolated database

Practise restoration into an isolated target, never over the live database as a routine test. Ensure the dump doesn't contain database-switching statements that would redirect the import unexpectedly. Match required character sets, routines, and compatibility settings. Then verify representative relationships and application queries, not just the command's exit status. A successful decompression check alone can't establish a working recovery path.

mysql -e "CREATE DATABASE restore_check;"
gzip -dc appdb-backup.sql.gz | mysql restore_check
# Verify schema, row counts and representative queries.

6. Schedule with observable failures

A system timer can schedule a reviewed backup service. The timer is only the schedule; the service still needs a restricted execution context, reliable error propagation, and appropriate credentials. Persistent scheduling can catch a missed run after downtime. Add monitoring for stale backups and failed restores, not just process failure. A daily job that quietly produces unusable files isn't a backup strategy.

[Timer]
OnCalendar=*-*-* 02:00:00
Persistent=true
 
[Install]
WantedBy=timers.target

7. Test the application after restore

Finish the exercise by running the application against the restored data in an isolated environment. Confirm the workflows that matter, including files linked from database records. Measure recovery time and record any missing dependencies or credentials. Retention cleanup should run only after new backups are verified, and should never remove the last known-good recovery point merely because a calendar date changed.

Restore database -> restore files -> configure app
Run migrations appropriate to the recovered release
Check login, record reads, writes and file access
Measure recovery duration and document issues

Integration checks

Test database command failure, full disk, interrupted transfer, invalid archive, missing credentials, empty restore target and representative application behaviour after recovery.

These are verification instructions, not reported test results. The excerpts have not been executed as a complete integrated application. Review authentication, database schema, dependency versions and operational settings before deployment.

Official references

• Reference 1
• Reference 2

Explore CodeKits development services.