Regular database backups are essential for disaster recovery. MySQL provides several tools — mysqldump for logical backups, mysqlpump for parallel dumps, and MySQL Shell’s dumpInstance for modern cloud-friendly backups. This guide covers backing up and restoring MySQL databases on Ubuntu 26.04 LTS.

Tested and valid on:

  • Ubuntu 26.04 LTS

Prerequisites

  • Ubuntu 26.04 LTS with MySQL 9 installed
  • A user with sudo or database privileges
  • Enough disk space for backup files

Step 1 – Single Database Backup with mysqldump

mysqldump -u root -p mydb > /backups/mydb_$(date +%Y%m%d).sql

The $(date +%Y%m%d) adds today’s date to the filename.

Step 2 – Backup All Databases

mysqldump -u root -p --all-databases > /backups/all_databases_$(date +%Y%m%d).sql

Step 3 – Compress the Backup

mysqldump -u root -p mydb | gzip > /backups/mydb_$(date +%Y%m%d).sql.gz

Step 4 – Restore a Single Database

First create the target database if it doesn’t exist:

sudo mysql
CREATE DATABASE mydb;
exit

Then restore:

mysql -u root -p mydb < /backups/mydb_20260516.sql

Step 5 – Restore from a Compressed Backup

gunzip < /backups/mydb_20260516.sql.gz | mysql -u root -p mydb

Step 6 – Automate with Cron

sudo mkdir -p /backups/mysql
crontab -e

Add this line to run nightly at 2 AM and keep 7 days of backups:

0 2 * * * mysqldump -u root -p'password' --all-databases | gzip > /backups/mysql/all_$(date +%Y%m%d).sql.gz && find /backups/mysql -mtime +7 -delete

Step 7 – Verify a Backup

Always test your backups by restoring to a test database:

mysql -u root -p testdb < /backups/mydb_20260516.sql
mysql -u root -p testdb -e 'SHOW TABLES;'

Conclusion

You now have a reliable backup and restore strategy for MySQL on Ubuntu 26.04 LTS. Automate backups with cron and store copies offsite (S3, SFTP, or external drive) to protect against server failure.