Maintenance

Back Up and Restore MySQL Databases for PHP Applications

Create consistent database backups, protect them and prove that recovery works before an incident occurs.

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.

References

MariaDB Backup and Restore · MySQL Backup and Recovery

Share this page Help other developers discover this resource.