Automate Daily MySQL Backups on Ubuntu Server

Learn how to automate daily MySQL database backups on an Ubuntu server using a Bash script.

Keeping regular backups of our MySQL databases is not just good practice—it’s critical. Whether we’re managing production applications, client websites, or internal tools, data loss can cost time, money, and trust. This guide walks through how we can automate daily MySQL backups on an Ubuntu server using a simple, reliable Bash script.

Prerequisites

Before we begin, let’s ensure we have the following in place:

• A Ubuntu 24.05 dedicated server or KVM VPS.
• A basic programming knowledge.

Automate Daily MySQL Backups on Ubuntu Server with Bash and Upload to Remote Server

Step 1: Update Our System

Let’s begin by ensuring our server is up-to-date. Run:

sudo apt update && sudo apt upgrade -y

This guarantees that we have the latest security patches and compatibility for backup tools.

Step 2: Install MySQL Client Tools

Most Ubuntu servers already have the necessary MySQL utilities installed. If not, we can install them with:

sudo apt install mysql-client -y

These tools include mysqldump, which we’ll use to export our databases.

Step 3: Create a Backup Directory

We need a secure location where our backup files will be stored. Let’s create a dedicated directory:

sudo mkdir -p /var/backups/mysql
sudo chown $USER:$USER /var/backups/mysql

We now have a folder with the correct permissions to store daily backups.

Step 4: Write the Bash Backup Script

Now let’s create the actual backup script. We’ll name it mysql_backup.sh and place it in a safe location:

nano ~/mysql_backup.sh

Paste the following code inside:

#!/bin/bash

# === Configuration ===
USER="root"
PASSWORD="your_mysql_password"
BACKUP_DIR="/var/backups/mysql"
DATE=$(date +"%Y-%m-%d_%H-%M")
MYSQL=/usr/bin/mysql
MYSQLDUMP=/usr/bin/mysqldump

# === Create backup directory if it doesn't exist ===
mkdir -p "$BACKUP_DIR"

# === Get list of databases ===
databases=`$MYSQL -u$USER -p$PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|mysql|sys)"`

# === Backup each database ===
for db in $databases; do
    echo "Backing up $db"
    $MYSQLDUMP -u$USER -p$PASSWORD --databases $db > "$BACKUP_DIR/$db-$DATE.sql"
done

# === Optional: Remove backups older than 7 days ===
find "$BACKUP_DIR" -type f -name "*.sql" -mtime +7 -exec rm {} \;

Save the file and exit (CTRL+X, then Y, then Enter).

Security Tip: Instead of writing the password directly in the script, consider using a .my.cnf file or exporting credentials from a .env file for better security.

Step 5: Make the Script Executable

Run this command to make our script runnable:

chmod +x ~/mysql_backup.sh

Now we can test it:

./mysql_backup.sh

We should see .sql files created in /var/backups/mysql.

Step 6: Schedule the Script with Cron

To automate our backups daily, we’ll set up a cron job. Open the crontab:

crontab -e

Add this line to run the script every day at 2 AM:

0 2 * * * /home/ubuntu/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1

• 0 2 * * * means daily at 2:00 AM.
• >> /var/log/mysql_backup.log logs output and errors.
• Make sure the script path matches where it’s saved, and that your user has permission to access MySQL.

Step 7: Test and Verify

We should always verify the cron job works correctly:

Check if the cron job runs the script by reviewing the log:

cat /var/log/mysql_backup.log

Confirm that backup files are created in /var/backups/mysql.

Optional: Compress and Upload to Remote Storage

To reduce space, we can compress backups:

gzip "$BACKUP_DIR/$db-$DATE.sql"

Updated mysql_backup.sh Script with Remote Upload Support

Let's extend the script to automatically upload backups to a remote server using either rsync or scp—two secure and reliable options. We’ll explain both and include sample code, so you can choose what fits your infrastructure best.

#!/bin/bash

# === Configuration ===
USER="root"
PASSWORD="your_mysql_password"
BACKUP_DIR="/var/backups/mysql"
DATE=$(date +"%Y-%m-%d_%H-%M")
MYSQL=/usr/bin/mysql
MYSQLDUMP=/usr/bin/mysqldump

# === Remote Server Config ===
REMOTE_USER="remoteuser"
REMOTE_HOST="192.168.1.100"
REMOTE_DIR="/home/remoteuser/mysql_backups"

# === Create backup directory if it doesn't exist ===
mkdir -p "$BACKUP_DIR"

# === Get list of databases ===
databases=`$MYSQL -u$USER -p$PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|mysql|sys)"`

# === Backup each database ===
for db in $databases; do
    echo "Backing up $db"
    DUMP_FILE="$BACKUP_DIR/$db-$DATE.sql"
    $MYSQLDUMP -u$USER -p$PASSWORD --databases $db > "$DUMP_FILE"

    # Compress the backup
    gzip "$DUMP_FILE"
done

# === Optional: Remove local backups older than 7 days ===
find "$BACKUP_DIR" -type f -name "*.sql.gz" -mtime +7 -exec rm {} \;

# === Upload backups to remote server using rsync or scp ===

# Using rsync (more efficient and can resume transfers)
rsync -avz --remove-source-files "$BACKUP_DIR/" "$REMOTE_USER@$REMOTE_HOST:$REMOTE_DIR/"

# OR uncomment below line to use scp instead
# scp "$BACKUP_DIR/"*.sql.gz "$REMOTE_USER@$REMOTE_HOST:$REMOTE_DIR/"

echo "Backup and remote upload completed at $(date)"

SSH Key Authentication (Recommended)

To automate secure uploads without entering passwords, we should set up SSH key-based authentication between the local server and the remote server:

ssh-keygen -t rsa -b 4096
ssh-copy-id remoteuser@192.168.1.100

This allows password-less rsync or scp, perfect for cron jobs.

How It Works

• gzip compresses the .sql backups to save bandwidth.
• rsync uploads and deletes the file from the local folder using --remove-source-files.
• Alternatively, scp simply transfers the compressed files (you can remove them manually if using scp).
• We can now run this script daily using a cron job, as explained earlier.

Final Cron Job Example

0 2 * * * /home/ubuntu/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1

Make sure:

• Remote SSH is configured.
• Remote server directory (/home/remoteuser/mysql_backups) exists and is writable.

Final Thoughts

By setting up this automated MySQL backup system on our Ubuntu server, we ensure our data is safe from unexpected failures. This approach is lightweight, effective, and easy to customize for different environments or schedules. As responsible developers and server admins, building in daily database backups is one of the smartest habits we can maintain.

Let’s protect our infrastructure—one script at a time.