Database backup and restore-quick reference

Database backups protect you from data loss. MySQL has several backup methods - choose the easiest for your situation.

Backup with mysqldump

Command-line tool (VPS or Dedicated Server only—shared hosting users should use phpMyAdmin):

mysqldump -u username -p databasename > backup.sql
# Creates backup.sql file with all data

Backup all databases:

mysqldump -u root -p --all-databases > full-backup.sql

Backup via phpMyAdmin

1. Log in to phpMyAdmin (in cPanel)
2. Click your database
3. Click Export tab
4. Choose SQL format
5. Click Go - downloads backup file

Restore from backup

Via command line (VPS or Dedicated Server only):

mysql -u username -p databasename < backup.sql

Via phpMyAdmin (all hosting types):

1. Click database name
2. Click Import tab
3. Upload backup.sql file
4. Click Go

Schedule automatic backups

On VPS or Dedicated Server, use a cron job:

0 2 * * * mysqldump -u root -p'password' mydb > /home/backups/db_$(date +\%Y\%m\%d).sql

This backs up daily at 2 AM. On shared hosting, use the backup feature built into cPanel.

Related: Database administration | Backup strategies | WordPress backups

Related articles