How to backup and restore MySQL databases

· Category: SQL & Databases

Short answer

Back up MySQL databases with mysqldump for logical backups or Percona XtraBackup for hot physical backups, and test restore procedures regularly.

Steps

  1. Logical backup: mysqldump -u root -p mydb > mydb.sql
  2. Restore: mysql -u root -p mydb < mydb.sql
  3. Single table backup: mysqldump mydb users > users.sql
  4. Consistent backup with replication: use --single-transaction for InnoDB.
  5. Automate backups with cron and verify checksums.

Tips

  • Store backups offsite or in object storage for disaster recovery.
  • Test restores on a staging environment to ensure backup integrity.

Common issues

  • Large databases take hours to dump and restore; consider incremental physical backups.
  • Restoring to an existing database with foreign keys may require SET FOREIGN_KEY_CHECKS=0.r