MySQL Backup Script

Introduction

🌩️ Picture this: it’s a dark and stormy night. Thunder is clashing, and you—yes, you, the brave tech warrior—are pacing up and down in your fortress (also known as your home office). 😰 You’re drenched in a cold sweat, haunted by the Database Demons that lurk in the depths of corrupt backups. They’re menacing, they’re terrifying, and they’re out to ruin your day! 🧙‍♂️👹

Just when you think all hope is lost, in comes your friendly neighborhood superhero—Backup Man, also known as yours truly, PiraffeBoy! 🦸‍♂️💫 With a cape made of code and a shield of server scripts, I swoop in to banish those nasty Database Demons once and for all.

With a flash and a bang, I unleash my secret weapon—a MySQL Backup Script so powerful, it makes those Database Demons run for the hills. 🌄 It performs a complete mysqldump of your precious databases, locks them in a vault of security, and throws away the key! 🗝️💼

Your databases are now safe, your integrity is intact, and peace returns to the kingdom of serverland. 🏰🌈 You can finally sleep at night, dreaming of optimized queries and normalized tables. 😴💭

Haha, alright, that was a bit dramatic! 😂 But hey, when it comes to backups, the stakes are real—even if there aren’t any capes involved. A successful, working backup is no laughing matter. 🤷‍♂️👍

What Does the Script Do?

At its core, the script performs automated backups of all MySQL databases on a server. It offers options to exclude specific databases and stores all backups in a designated directory. Additionally, it automatically deletes backups older than a certain number of days. Here’s the script you’ll find running on my machines:

#!/bin/bash
# ------------------------------------------------------------------------
# piraffe’s MySQL Dump Script
# Created by piraffe.com / Daniel Bahl 2023
# 
# Automatically backs up all MySQL databases on the server, with options to exclude specific ones.
# All backups will be stored in the /backup/mysql (change $DEST and $MBD below) directory.
# ------------------------------------------------------------------------

# Configuration
MyUSER="root"      # MySQL Username
MyHOST="localhost" # MySQL Server Hostname
IGNOREDB="test"    # Databases to ignore
DEST="/backup"     # Backup Destination
MBD="$DEST/mysql"  # Main Backup Directory
KEEPXDAYS=4        # Number of days to keep backups

# MySQL Password, read from a secure file
# REMEMBER TO CHMOD 400 mysqlpass for security
MyPASS="$(cat /root/etc/mysqlpass)" 

# Auto-detect paths for required binaries
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
CHOWN="$(which chown)"
CHMOD="$(which chmod)"
GZIP="$(which gzip)"

# Get system information
HOST="$(hostname)"
NOW="$(date +"%d-%m-%Y")" # Current date in dd-mm-yyyy format

# Ensure backup directory exists
[ ! -d $MBD ] && mkdir -p $MBD

# Lock down directory permissions
$CHOWN 0.0 -R $DEST
$CHMOD 0600 $DEST

# Fetch database list
DBS="$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse 'SHOW DATABASES')"

# Perform backup
for db in $DBS; do
    if [[ "$IGNOREDB" != *"$db"* ]]; then # Skip databases in $IGNOREDB
        FILE="$MBD/$db.$HOST.$NOW.gz"
        $MYSQLDUMP -u $MyUSER -h $MyHOST -p$MyPASS $db | $GZIP -9 > $FILE
    fi
done

# Delete old backups older than KEEPXDAYS days
find $MBD -name "*.gz" -mtime +$KEEPXDAYS -exec rm {} \;

Decoding The Script

Configuration Section: Here, variables like MySQL username (MyUSER), MySQL password (MyPASS), server hostname (MyHOST), and databases to ignore (IGNOREDB) are defined.
Auto-detect Paths: This part detects system paths for essential binaries like mysql, mysqldump, chown, chmod, and gzip.
System Information and Date: HOST captures the hostname, and NOW stores the current date.
Directory Checks and Permissions: The script ensures the backup directory exists and sets directory permissions to 0600.
Database List and Backup: This is the heart of the script, where each database is backed up individually.
Old Backup Deletion: Deletes backups older than KEEPXDAYS days.

How to Use and Customize

Permissions and Security: `chmod` and `MyPASS`

First, set the script to be executable with chmod +x /path/to/script.sh.

For security, the script reads the MySQL password from a file (/root/etc/mysqlpass). Create this file and place your MySQL password inside it. Use chmod 400 /root/etc/mysqlpass to ensure only the root user can read this file.

Cronjobs: Automated Backups

To run the script automatically every night, open the crontab for editing:

crontab -e

Add this line to run the script at 2:00 AM daily:

0 2 * * * /path/to/script.sh

Conclusion

A MySQL backup script might not be the hero in a blockbuster movie, but it’s the unsung hero in my servers, saving the day before any potential disaster strikes. If you’re losing sleep over potential database corruption during backups, feel free to use and customize this script. Trust me, your future self will thank you.

My MySQL Backup Script