Disaster Recovery 101: Automate MySQL Backups on Ubuntu 24.04

AI development environment on Ubuntu

There are two types of server admins: those who back up, and those who haven't lost data yet.

If your VPS crashes or you accidentally delete a table, a snapshot from last week isn't good enough. You need daily, automated database backups.

In this guide, we will write a simple script that automatically backs up your MySQL database every night and deletes old backups to save space.

Prerequisites: A Joy Services VPS with MySQL or MariaDB installed.

Step 1: Create the Backup Directory

We need a safe place to store our files. We will create a hidden folder in your home directory.

Bash
root@joy:~# mkdir -p /home/backups/mysql

Step 2: Create the Backup Script

We will write a small script that:

  1. Dumps your database to a file.
  2. Compresses it (zipping it) to save space.
  3. Deletes any backup older than 7 days.

1. Open a new file:

Bash
root@joy:~# nano /root/db-backup.sh

2. Paste this code:
(Replace YOUR_DB_USER, YOUR_DB_PASSWORD, and YOUR_DB_NAME with your real details).

Bash Script
#!/bin/bash # Configuration USER="root" PASSWORD="your_real_password_here" DATABASE="your_database_name" BACKUP_DIR="/home/backups/mysql" DATE=$(date +"%Y-%m-%d_%H-%M") # 1. Create Backup mysqldump -u$USER -p$PASSWORD $DATABASE | gzip > $BACKUP_DIR/db_$DATE.sql.gz # 2. Check if successful if [ $? -eq 0 ]; then echo "Backup Successful: db_$DATE.sql.gz" else echo "Backup Failed!" exit 1 fi # 3. Delete backups older than 7 days find $BACKUP_DIR -type f -name "*.sql.gz" -mtime +7 -delete

3. Make it executable:

Bash
root@joy:~# chmod +x /root/db-backup.sh

Step 3: Test the Script

Never trust a script until you see it run.

Bash
root@joy:~# ./db-backup.sh
Expected Output
Backup Successful: db_2025-02-14_10-30.sql.gz
Backup Failed? If you see mysqldump: Got error: 1045: Access denied, check your PASSWORD in the script. It must be exact.

Step 4: Automate with Cron

We don't want to run this manually. Let's schedule it to run every day at 3:00 AM.

1. Open the Cron Editor:

Bash
root@joy:~# crontab -e

2. Add this line at the bottom:

Cron Config
0 3 * * * /root/db-backup.sh >> /var/log/db-backup.log 2>&1

3. Verify the Cron Job:

Bash
root@joy:~# crontab -l
Expected Output
0 3 * * * /root/db-backup.sh >> /var/log/db-backup.log 2>&1

You now have a fully automated disaster recovery plan. Every morning at 3 AM, your data is saved, compressed, and rotated.