Automate MySQL Database Backups on Linux: A Step-by-Step Guide

Ensuring the safety and security of your MySQL databases is crucial for any business. Regular MySQL database backups are a key component of a solid data protection strategy. In this blog, I’ll show you how to automate MySQL database backups on Linux using a simple shell script.

STEP 1: Create a Backup Shell Script

First, create a new shell script named mysql_backup_script.sh and add the following content:

#!/bin/bash

# MySQL database credentials
DB_USER="<DB_USER>"
DB_PASS="<DB_PASSWORD>"
DB_NAME="<DB_NAME>"

# Backup directory
BACKUP_DIR="<path/to/backup/directory>"

# Backup file name
BACKUP_FILE="$BACKUP_DIR/$DB_NAME-$(date +%Y%m%d%H%M%S).sql"

# Dump the MySQL database
mysqldump --user=$DB_USER --password=$DB_PASS $DB_NAME > $BACKUP_FILE

# Optional: Compress the backup file
# gzip $BACKUP_FILE

Replace <DB_USER>, <DB_PASSWORD>, <DB_NAME>, and <path/to/backup/directory> with your actual MySQL credentials, database name, and backup directory path.

Step 2: Configure executable permissions for the script

Add the executable permissions to the script file by running:

chmod +x mysql_backup.sh

Step 3: Schedule the MySQL database Backups

Schedule the backup script to run automatically on a specified time using cron. Open your crontab file by running:

crontab -e

Add the following line to schedule the backup to run daily at midnight:

0 0 * * * /path/to/mysql_backup_script.sh

Replace /path/to/mysql_backup_script.sh with the actual path to your backup script.

You can utilize the following reference, if you wish to configure different schedule for your backup.