In a production environment, your data is your most valuable asset. Whether you are running a logistics system, an accounting app, or a personal blog, a reliable backup strategy is non-negotiable.

Following up on our previous PostgreSQL automation guide, this article will show you how to automate MySQL backups on Ubuntu 24.04 using a Shell script and Cron jobs, including timezone optimization for WIB (UTC+7).

Prerequisites

• Ubuntu 24.04 Server installed.
• MySQL Server (version 8.0 or newer) installed and running.
• Sudo privileges.

Step 1: Configure Passwordless Authentication

To allow the script to run automatically without manual intervention, it must run seamlessly without pausing to prompt for a password. In MySQL, the most secure way to store credentials is by utilizing the .my.cnf configuration file in the user's home directory.

1. Create the file in your home directory:

nano ~/.my.cnf

2. Add your credentials using the format below. Replace it with your actual MySQL password (using the root user or a dedicated backup user is highly recommended):

[mysqldump]
user=root
password=your_secure_password

[mysqladmin]
user=root
password=your_secure_password

3. Set strict permissions (MySQL strictly requires this file to be readable only by its owner; otherwise, the configuration will be ignored for security reasons):

chmod 0600 ~/.my.cnf

Step 2: Set the System Timezone to UTC+7

To ensure your backups run during off-peak hours (e.g., early morning Indonesia Western Time), sync your server's timezone to WIB:

sudo timedatectl set-timezone Asia/Jakarta

Step 3: Create the Backup Script

Now, we will create a script that handles the database dump, compresses it into .gz format to save disk space, and purges old, expired backup files.

1. Create the script file:

nano ~/backup_mysql.sh

2. Paste the following code:

#!/bin/bash

# Configuration
DB_NAME="your_database_name"
BACKUP_DIR="/home/$(whoami)/backups"
# Format: YYYY-MM-DD_HHMMSS (using local time)
DATE=$(date +"%Y-%m-%d_%H%M%S")

# Ensure backup directory exists
mkdir -p $BACKUP_DIR

# Execute mysqldump
# --single-transaction & --quick minimize table locking impact on production DBs
mysqldump --single-transaction --quick --lock-tables=false $DB_NAME | gzip > $BACKUP_DIR/backup_${DB_NAME}_${DATE}.sql.gz

# Housekeeping: Delete backups older than 7 days
find $BACKUP_DIR -type f -mtime +7 -name "*.sql.gz" -delete

3. Make the script executable:

chmod +x ~/backup_mysql.sh

Step 4: Schedule with Cron

We will use the system's Crontab to trigger this script automatically every day at 02:00 AM WIB.

1. Open the crontab editor:

crontab -e

2. Add this line at the very bottom of the file:

0 2 * * * /home/your_username/backup_mysql.sh

⚠️ Note: Make sure to replace your_username with your actual Ubuntu username.

Step 5: Verify the Setup

You can manually test the script to ensure the backup file is properly generated and compression works as expected:

./backup_mysql.sh
ls -lh ~/backups

Summary of Benefits

• Automation: Requires absolutely zero manual intervention.
• Timezone Aware: Backups are executed during the lowest traffic hours in your local time (WIB).
• Disk Management: Old backups are automatically deleted after 7 days, and files are compressed using gzip.
• Security: Password credentials are safely isolated inside the .my.cnf file with restricted access permissions.

Pro Tip: For critical applications, always consider syncing these local backup files to a remote cloud storage (such as Google Drive or AWS S3) using tools like rclone as an extra layer of disaster recovery.