How to Back Up and Restore a MySQL Database on a Server with mysqldump

Losing a database can destroy an application. That's why regular backups are a must as soon as your application goes live. This article explains how to back up and restore a MySQL database using mysqldump, plus how to schedule it automatically.

Backing Up a Database with mysqldump

The basic command to create a backup of a single database:

mysqldump -u username -p database_name > backup.sql

You will be prompted to enter the password. The result is a backup.sql file containing the entire structure and data.

Backup with Compression (Save Space)

For a large database, compress it directly so the file is smaller:

mysqldump -u username -p database_name | gzip > backup.sql.gz

Restoring a Database

To restore a backup into a database (make sure the target database already exists):

mysql -u username -p database_name < backup.sql

If the file is compressed:

gunzip < backup.sql.gz | mysql -u username -p database_name

Backing Up All Databases at Once

mysqldump -u username -p --all-databases > full_backup.sql

Scheduling Automatic Backups with Cron

Create a simple backup script, for example /home/user/backup.sh:

#!/bin/bash
DATE=$(date +%F)
mysqldump -u username -p'password' database_name | gzip > /home/user/backups/db-$DATE.sql.gz

Make it executable, then register it in crontab so it runs every day at 2 AM:

chmod +x /home/user/backup.sh
crontab -e

# run the backup every day at 02:00
0 2 * * * /home/user/backup.sh

Important Tips

• Store backups in a different location (e.g. cloud storage), not just on the same server.
• Delete old backups periodically so they don't fill up the disk.
• Test the restore process occasionally — a backup that is never tested often turns out to be corrupted when you need it.

Backup in cPanel

If you use shared hosting, cPanel also provides a Backup menu and phpMyAdmin → Export for manual backups without the command line.

Conclusion

Backing up a MySQL database with mysqldump is simple but very crucial. Combine it with compression and a cron schedule so backups run automatically, and always keep a copy in a separate location. To understand cron scheduling further, see our guide on the Task Scheduler in Laravel.