Overview
A backup is useful only if it can be restored within the time the business can tolerate. Define recovery point and recovery time objectives before choosing a schedule. A daily dump may be insufficient for a database that changes continuously.
Choose a backup method
Logical backups such as mysqldump or mariadb-dump are portable and useful for migrations and smaller databases. Physical backups can be faster for large datasets but are more dependent on server version and storage layout. Use the tool appropriate to the database engine and version.
Create protected backups
Store backups outside the public document root. Restrict filesystem permissions and encrypt copies that leave the server. Do not put database passwords directly in shell history or public scripts. Keep at least one copy isolated from the production server so a compromised host cannot destroy every backup.
Example logical dump
A typical command-line pattern is:
mariadb-dump --single-transaction --routines --triggers --user=backup_user --password app_database > app_database.sql
Options and consistency guarantees depend on the storage engines and server version. Review the installed tool's help and documentation. --single-transaction is designed for transactional tables such as InnoDB; it does not make non-transactional tables consistent.
Test restoration
Restore into a separate database or isolated environment. Check that tables, indexes, character sets, routines and expected row counts are present. Run application smoke tests against the restored copy. Record the time required and any manual steps.
Retention and monitoring
Keep daily, weekly and monthly restore points according to business and legal requirements. Alert on failed jobs, unexpectedly small files and missed schedules. Periodically test a complete recovery, not just whether a file exists.
Checklist
- Define RPO and RTO.
- Store backups outside the web root.
- Restrict access and encrypt off-host copies.
- Monitor backup completion and size.
- Test restoration on a separate target.
- Document credentials, steps and recovery ownership.