BackupMySQL.sh

Bash script that dumps all MySQL/MariaDB databases individually, compresses them with gzip, and stores them on an NFS mount.

Addresses below are RFC 5737 documentation ranges or placeholders - swap in your own.

Table of Contents#

Overview
Prerequisites
Parameters
Credentials File
The Script
Usage
Filename Collision Risk
Restore Procedure
Troubleshooting
See Also
Sources

1. Overview#

This script performs automated MySQL/MariaDB backups by:

Verifying (and mounting if needed) the NFS backup share
Querying the database server for a list of all user databases
Dumping each database individually with mysqldump
Compressing each dump with gzip
Logging all operations to a log file

It is designed to run as a scheduled cron job (e.g., hourly or daily) and stores dumps with time-based filenames for rotation.

2. Prerequisites#

mysql client and mysqldump utility (from mariadb-client or mysql-client package)
gzip for compression
NFS mount configured in /etc/fstab for the backup destination
A MySQL/MariaDB credentials file (see Credentials File)
Network access to the database host
The backup user must have SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER, and RELOAD privileges on all databases

3. Parameters#

Edit these variables at the top of the script:

Variable	Description	Example
`NFS`	NFS mount point	`/mnt/Backup/Database`
`BACKUP_DIR`	Directory for dump files	`${NFS}/MySQL`
`BACKUP_LOG`	Log file path	`${BACKUP_DIR}/backup-sql`
`BACKUP_CREDENTIALS`	Path to MySQL credentials file	`/home/backup/.sqlpwd`
`DB_HOST`	Database server hostname or IP	`192.0.2.14`

4. Credentials File#

The script uses --defaults-extra-file to read credentials from a file instead of passing them on the command line. Create the file at the path specified in BACKUP_CREDENTIALS:

[client]
user=backup
password=<your-password>

Secure the file:

chmod 600 /home/backup/.sqlpwd
chown backup:backup /home/backup/.sqlpwd

This approach avoids exposing the password in process listings or shell history.

5. The Script#

#!/bin/bash

NFS="/mnt/Backup/Database"
BACKUP_DIR="${NFS}/MySQL"
BACKUP_LOG="${BACKUP_DIR}/backup-sql"
BACKUP_CREDENTIALS=/home/backup/.sqlpwd
DB_HOST=192.0.2.14

# Ensure NFS share is mounted
if test -d "$NFS"; then
    echo "$(date "+%F %H:%M:%S") - Share is already mounted" > $BACKUP_LOG
else
    sudo /bin/mount /mnt/Backup/Database
    echo "$(date "+%F %H:%M:%S") - Share had to be mounted" > $BACKUP_LOG
fi

# Get list of user databases (exclude system databases)
DATABASES=$(mysql --defaults-extra-file=$BACKUP_CREDENTIALS -h $DB_HOST -Bse "show databases" 2> $BACKUP_LOG | grep -v -E "schema|mysql|sys")
DB_COUNT=$(echo $DATABASES | wc -w)

raus(){
 echo $*
 exit 1
}

test $DB_COUNT -ge 1 || raus "$(date "+%F %H:%M:%S") - Backup failed: List is empty  $(tail -1 $BACKUP_LOG)"

# Dump each database
echo "$(date "+%F %H:%M:%S") - Backup started" >> $BACKUP_LOG
echo "$(date "+%F %H:%M:%S") - Backing up MySQL:" >> $BACKUP_LOG
for i in $DATABASES
do
   BACKUP_FILE=$BACKUP_DIR/${DB_HOST}_$(date +%u-%H)_${i}.dump
   echo "$(date "+%F %H:%M:%S") - Backing up database $i..." >> $BACKUP_LOG
   mysqldump --defaults-extra-file=$BACKUP_CREDENTIALS \
         --host $DB_HOST \
         --single-transaction \
         --default-character-set=utf8mb4 \
         --quick \
         --extended-insert \
         --routines \
         --triggers \
         --quote-names \
         --opt \
         --compress $i > $BACKUP_FILE 2>> $BACKUP_LOG || raus $(date "+%F %H:%M:%S") - Backup failed: $(tail -1 $BACKUP_LOG)
   echo "$(date "+%F %H:%M:%S") - Compressing $BACKUP_FILE" >> $BACKUP_LOG
   gzip -f $BACKUP_FILE
done

echo "$(date "+%F %H:%M:%S") - MySQL dump completed at $(date)" >> $BACKUP_LOG
echo "$(date "+%F %H:%M:%S") - Done" >> $BACKUP_LOG

exit 0

6. Usage#

Manual Execution#

chmod +x /opt/scripts/BackupMySQL.sh
/opt/scripts/BackupMySQL.sh

Cron Schedule#

Run every 6 hours:

0 */6 * * * /opt/scripts/BackupMySQL.sh >> /var/log/backup-mysql.log 2>&1

Run daily at 02:00:

0 2 * * * /opt/scripts/BackupMySQL.sh >> /var/log/backup-mysql.log 2>&1

7. Filename Collision Risk#

The current script uses date +%u-%H for filenames, which produces the day-of-week number (1-7) and hour (00-23). For example: 192.0.2.14_3-02_mydb.dump.

Problem: This creates only 168 unique filename slots (7 days x 24 hours). When run on the same day-of-week at the same hour across different weeks, the new dump silently overwrites the previous one. This acts as a 7-day rotation by design, but if you need longer retention, this format is insufficient.

Alternative for unique filenames: Replace %u-%H with %F_%H%M (full date plus time) to produce globally unique names:

# Unique filename: 192.0.2.14_2026-03-22_0200_mydb.dump
BACKUP_FILE=$BACKUP_DIR/${DB_HOST}_$(date +%F_%H%M)_${i}.dump

With unique filenames, add a separate cleanup step to manage retention:

# Delete dumps older than 14 days
find "$BACKUP_DIR" -name "*.dump.gz" -mtime +14 -delete

8. Restore Procedure#

Restore a Single Database#

# Decompress the dump
gunzip 192.0.2.14_3-02_mydb.dump.gz

# Restore to the same or a different database
mysql --defaults-extra-file=/home/backup/.sqlpwd -h 192.0.2.14 mydb < 192.0.2.14_3-02_mydb.dump

# Or restore to a new database name
mysql --defaults-extra-file=/home/backup/.sqlpwd -h 192.0.2.14 -e "CREATE DATABASE mydb_restored;"
mysql --defaults-extra-file=/home/backup/.sqlpwd -h 192.0.2.14 mydb_restored < 192.0.2.14_3-02_mydb.dump

Restore Without Decompressing#

zcat 192.0.2.14_3-02_mydb.dump.gz | mysql --defaults-extra-file=/home/backup/.sqlpwd -h 192.0.2.14 mydb

Verify a Dump Before Restore#

# Check if the dump is valid gzip
gunzip -t 192.0.2.14_3-02_mydb.dump.gz

# Preview the first few lines
zcat 192.0.2.14_3-02_mydb.dump.gz | head -50

Troubleshooting#

Issue	Cause	Solution
`ERROR 1045 Access denied`	Wrong credentials or insufficient privileges	Verify credentials file; grant required privileges
`NFS share not mounted`	Mount point missing or network issue	Check `/etc/fstab`; verify network; run `mount` manually
`Backup failed: List is empty`	Cannot connect to database or no user databases	Test `mysql -h <host>` manually; check network/firewall
Dump file is 0 bytes	Database is empty or mysqldump error	Check `BACKUP_LOG` for errors; verify database exists
Disk full on NFS	Too many backups or large databases	Add cleanup via `find -mtime -delete`; check NFS quota
`--compress` warning	Deprecated in newer MySQL/MariaDB	Remove `--compress` flag or replace with `--compress=zstd` for MySQL 8.0.18+
Old backups overwritten	`%u-%H` filename collision across weeks	Switch to `%F_%H%M` format (see Filename Collision Risk)