Regular database backups are essential. This guide covers multiple methods for backing up and restoring MySQL 8.0 databases on Ubuntu 24.04 LTS — from simple mysqldump exports to automated cron jobs.

Tested and valid on:

  • Ubuntu 24.04 LTS

Prerequisites

  • Ubuntu 24.04 LTS server with MySQL 8.0 installed
  • A user with sudo privileges

Step 1 – Back Up a Single Database with mysqldump

Export a database to a SQL file:

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

Step 2 – Back Up All Databases

Export every database on the server:

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

Step 3 – Compress the Backup

Pipe through gzip to save space:

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

Step 4 – Restore a Single Database

Create the target database first, then import:

mysql -u root -p -e 'CREATE DATABASE mydb;'
mysql -u root -p mydb < /backup/mydb_20260516.sql

Step 5 – Restore from a Compressed Backup

Decompress and pipe directly into MySQL:

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

Step 6 – Create a Backup Directory

Set up a secure backup location:

sudo mkdir -p /backup/mysql
sudo chown mysql:mysql /backup/mysql
sudo chmod 750 /backup/mysql

Step 7 – Automate with a Cron Job

Create a backup script:

sudo nano /usr/local/bin/mysql-backup.sh

Add:

#!/bin/bash
DATE=$(date +%Y%m%d)
mysqldump -u backup_user -pBackupPass mydb | gzip > /backup/mysql/mydb_$DATE.sql.gz
# Keep only the last 30 days
find /backup/mysql/ -name '*.sql.gz' -mtime +30 -delete

Make executable and schedule daily at 2 AM:

sudo chmod +x /usr/local/bin/mysql-backup.sh
echo '0 2 * * * root /usr/local/bin/mysql-backup.sh' | sudo tee /etc/cron.d/mysql-backup

Conclusion

You now have a reliable backup and restore strategy for MySQL 8.0 on Ubuntu 24.04 LTS. Store backups offsite or in object storage (e.g., AWS S3, Backblaze B2) for disaster recovery.