Declaration of Variables

Sarouel · Nov 25, 2011

Hi All,

So, I chose my host (Pair Networks) because it runs FreeBSD. However, I am a total beginner, so you can throw stones at me, because my question is stupid, but this problem keeps me from moving forward.

In fact, my script works with a very convoluted code, but I want to simplify it.

Here is my problem: I need to backup, zip and encrypt my databases to finally send them to a mailbox.

Here is the bash script. In fact, the variable declaration does not work. Thank you for your help.

Code:

#!/bin/sh

# Pair Database Server URL:

SERVERDB='dbXYZ.pair.com'

# Database User Name:

USERDB='ABC_20'

# Database Password:

PASSDB='X1Y2Z3'

# Database Name:

DBNAME='POL_40'

#####

# Path to Backup directory:

PATH='/usr/home/USERNAME/public_html/Backup'

# Password to encrypt Database Before Sending it by email (Do NOT use semi-colon ";"):

OPENPASS='%4957I@6IcrV'

# Email address where send to the Backuped, Zipped and Encrypted Database:

EMAIL='me@mail.com'

# Email Subject (date and time is added at the end of this subject):

SUBJECT='POL_40 Backup'

# Delete the Zipped Backups Databases Older than n Day:

DAYS=1

##############################
#                            #
#  NO Change Under This Box  #
#                            #
##############################

DATE=`date +\%Y-\%m-\%d-at-\%H:\%M:\%S`

/usr/local/bin/mysqldump -h$SERVERDB -u$USERDB -p$PASSDB $DBNAME | gzip -9 > $PATH/$DBNAME-$DATE.sql.gz
 
openssl enc -aes-256-cbc -salt -a -in $PATH/$DBNAME-$DATE.sql.gz -out $PATH/$DBNAME-$DATE.sql.gz.ENC -pass pass:$PASS

/usr/local/bin/mutt -s "$SUBJECT $DATE" -a $PATH/$DBNAME-$DATE.sql.gz.ENC -- $EMAIL > /dev/null

rm $PATH/$DBNAME-$DATE.sql.gz.ENC

find $PATH/$DBNAME-*.sql.gz -mtime +$DAYS -exec rm {} \;

J65nko · Nov 26, 2011

From the command line/shell type:

Code:

$ echo $PATH

You then will understand why overwriting PATH in your script is not such a good idea

Sarouel · Dec 7, 2011

Hello J65nko,

Thank you, so I changed PATH with WAYTO.

However, it doesn't work. Here is the first alarm of email:

Code:

unknown option '+\%Y-\%m-\%d-at-\%H:\%M:\%S.sql.gz'

So, I changed those variables (Old > New):

DATE > CDATE
EMAIL > FEMAIL
DAYS > TDAYS

And I moved "CDATE" from line 47 to line 2, below "#!/bin/sh"

*****

Here is the new script:

Code:

#!/bin/sh

CDATE=`date +\%Y-\%m-\%d-at-\%H:\%M:\%S`

# Pair Database Server URL:

SERVERDB='dbXYZ.pair.com'

# Database User Name:

USERDB='ABC_20'

# Database Password:

PASSDB='X1Y2Z3'

# Database Name:

DBNAME='POL_40'

#####

# Path to Backup directory:

WAYTO='/usr/home/USERNAME/public_html/Backup'

# Password to encrypt Database Before Sending it by email (Do NOT use semi-colon ";"):

OPENPASS='%4957I@6IcrV'

# Email address where send to the Backuped, Zipped and Encrypted Database:

FEMAIL='me@mail.com'

# Email Subject (date and time is added at the end of this subject):

SUBJECT='POL_40 Backup'

# Delete the Zipped Backups Databases Older than n Day:

TDAYS=1

##############################
#                            #
#  NO Change Under This Box  #
#                            #
##############################

/usr/local/bin/mysqldump -h$SERVERDB -u$USERDB -p$PASSDB $DBNAME | gzip -9 > $WAYTO/$DBNAME-$CDATE.sql.gz
 
openssl enc -aes-256-cbc -salt -a -in $WAYTO/$DBNAME-$CDATE.sql.gz -out $WAYTO/$DBNAME-$CDATE.sql.gz.ENC -pass pass:$PASS

/usr/local/bin/mutt -s "$SUBJECT $CDATE" -a $WAYTO/$DBNAME-$CDATE.sql.gz.ENC -- $FEMAIL > /dev/null

rm $WAYTO/$DBNAME-$CDATE.sql.gz.ENC

find $WAYTO/$DBNAME-*.sql.gz -mtime +$DAYS -exec rm {} \;

Here are alarms:

Code:

dbXYZ.pair.com: not found
ABC_20: not found
X1Y2Z3: not found
POL_40: not found

%4957I@6IcrV: not found
me@mail.com not found
POL_40: not found
1: not found

So, script only find the first variable.

Thank you for your help!

SirDice · Dec 7, 2011

Use the correct quotes. I'm guessing you used backticks on all the other variables too.

There is a big difference between `, ' and "

Sarouel · Dec 7, 2011

SirDice said:
Use the correct quotes. I'm guessing you used backticks on all the other variables too.

There is a big difference between `, ' and "

Which one need I use?

SirDice · Dec 7, 2011

Give a man a fish and he'll have food for a day, teach a man to fish and he'll have food for the rest of his life.

The Bourne Shell.

Sarouel · Dec 7, 2011

SirDice said:
Give a man a fish and he'll have food for a day, teach a man to fish and he'll have food for the rest of his life.

The Bourne Shell.

Give a man a beer and he'll have drink for 5 minutes, give a man a brewery and he'll have drinks for the rest of his life.

Saint Gerard Adriaan Heineken, founder of Heineken.

*****

Thank you to everyone.

Here is the perfectly final working code:

Code:

#!/bin/sh

# Pair Database Server URL:

SERVERDB='dbXYZ.pair.com'

# Database User Name:

USERDB='ABC_20'

# Database Password:

PASSDB='X1Y2Z3'

# Database Name:

DBNAME='POL_40'

#####

# Path to Backup directory:

WAYTO='/usr/home/USERNAME/public_html/Backup'

# Password to encrypt Database Before Sending it by email (Do NOT use semi-colon ";"):

OPENPASS='%4957I@6IcrV'

# Email address where send to the Backuped, Zipped and Encrypted Database:

EMAIL='me@mail.com'

# Email Subject (date and time is added at the end of this subject):

SUBJECT='POL_40 Backup'

# Delete the Zipped Backups Databases Older than n Day:

DAYS=1

##############################
#                            #
#  NO Change Under This Box  #
#                            #
##############################

DATE=`date +\%Y-\%m-\%d-at-\%H:\%M:\%S`

/usr/local/bin/mysqldump -h$SERVERDB -u$USERDB -p$PASSDB $DBNAME | gzip -9 > $WAYTO/$DBNAME-$DATE.sql.gz
 
openssl enc -aes-256-cbc -salt -a -in $WAYTO/$DBNAME-$DATE.sql.gz -out $WAYTO/$DBNAME-$DATE.sql.gz.ENC -pass pass:$PASS

/usr/local/bin/mutt -s "$SUBJECT $DATE" -a $WAYTO/$DBNAME-$DATE.sql.gz.ENC -- $EMAIL > /dev/null

rm $WAYTO/$DBNAME-$DATE.sql.gz.ENC

find $WAYTO/$DBNAME-*.sql.gz -mtime +$DAYS -exec rm {} \;

SirDice · Dec 8, 2011

Sarouel said:
Give a man a beer and he'll have drink for 5 minutes, give a man a brewery and he'll have drinks for the rest of his life.

Saint Gerard Adriaan Heineken, founder of Heineken.

Haha.. Nice twist :beer

Sarouel · Oct 14, 2012

Hello,

Feed back after 10 months of using of this sh(1) script.

First of all, I want you to remember I am a total newby, so my new command lines added are there after many attempts.

Please, they are NOT perfect, so bring your comments to solve some errors.

*****

This command line:

Code:

[ -f $ATROOT/sent.lock ] && rm $ATROOT/sent.lock

Finds and delete the sent.lock file, if needed, AND fixes this error:

Code:

Couldn't lock /usr/home/lidynasty/sent

Nota Bene:

The current database will be saved on the server, BUT it will NOT be sent by email.

AND, no database will be sent by email as the file sent.lock will NOT be deleted firstly.

*****

This command line:

Code:

[ -f $ATROOT/sent ] && rm $ATROOT/sent

Finds and delete the sent file, if needed, AND solves the unstoppable growing size of the sent file.

Nota Bene:

If you DON'T delete the sent file, the size of this file will increase after each sending. The increasing is the exact size of the previously sent database.

So, if you have a lot of databases saved each hour, without deletion, the sent file could be as big as tens or thousands mega bits after one or two days only.

*****

This command line:

Code:

[ ! -f $ATROOT/sent ] && touch $ATROOT/sent

Finds and create the sent file, if needed, AND creates a new sent empty file with touch(1) command, AND fixes this error:

Code:

Couldn't find /usr/home/lidynasty/sent

*****

I included a useful reminder "step by step" on "how to decrypt and opening database on your Windows desktop computer" at the bottom of the sh script.

Code:

#!/bin/sh

CDATE=`date +\%Y-\%m-\%d-at-\%H:\%M:\%S`

# Pair Database Server URL:

SERVERDB='dbXYZ.pair.com'

# Database User Name:

USERDB='ABC_20'

# Database Password:

PASSDB='X1Y2Z3'

# Database Name:

DBNAME='POL_40'

#####

# Path to Root :

ATROOT='/usr/home/USERNAME'

# Path to Backup directory:

WAYTO='/usr/home/USERNAME/public_html/Backup'

# Password to encrypt Database Before Sending it by email (Do NOT use semi-colon ";"):

OPENPASS='%4957I@6IcrV'

# Email address where send to the Backuped, Zipped and Encrypted Database:

FEMAIL='me@mail.com'

# Email Subject (date and time is added at the end of this subject):

SUBJECT='POL_40 Backup'

# Delete the Zipped Backups Databases Older than n Day:

TDAYS=1

##############################
#                            #
#  NO Change Under This Box  #
#                            #
##############################

DATE=`date +\%Y-\%m-\%d-at-\%H:\%M:\%S`

/usr/local/bin/mysqldump -h$SERVERDB -u$USERDB -p$PASSDB $DBNAME | gzip -9 > $WAYTO/$DBNAME-$DATE.sql.gz
 
openssl enc -aes-256-cbc -salt -a -in $WAYTO/$DBNAME-$DATE.sql.gz -out $WAYTO/$DBNAME-$DATE.sql.gz.ENC -pass pass:$OPENPASS

[ -f $ATROOT/sent.lock ] && rm $ATROOT/sent.lock

[ -f $ATROOT/sent ] && rm $ATROOT/sent

[ ! -f $ATROOT/sent ] && touch $ATROOT/sent

/usr/local/bin/mutt -s "$SUBJECT $DATE" -a $WAYTO/$DBNAME-$DATE.sql.gz.ENC -- $EMAIL > /dev/null

[ -f $ATROOT/sent.lock ] && rm $ATROOT/sent.lock

[ -f $ATROOT/sent ] && rm $ATROOT/sent

[ ! -f $ATROOT/sent ] && touch $ATROO/sent

rm $WAYTO/$DBNAME-$DATE.sql.gz.ENC

find $WAYTO/$DBNAME-*.sql.gz -mtime +$DAYS -exec rm {} \;

#####################################################################################################################
#                                             																									#
#  Decrypt from Pair:																										#
#                                             																									#
# ??? (Sorry, I don't know)	 																									#
#                                             																									#
# ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------	#
#                                             																									#
# Decrypt from Windows: 																										#
#                                             																									#
# 1 - Download "OpenSSL for Windows 64bit version" ref:																					# 
#      http://code.google.com/p/openssl-for-windows/																						#
# 2 - Install "OpenSSL for Windows 64bit version" in this directory: "C:\Program Files\OpenSSL"																#
# 3 - Run a command line from Windows: "cmd.exe" ref: 																						#
#      http://www.howtogeek.com/howto/windows-vista/run-a-command-as-administrator-from-the-windows-vista-run-box/													#
# 4 - From Windows command line, go to this directory: "OpenSSL" of your computer: "cd C:\Program Files\OpenSSL\bin"													#
# 5 - Run this command line: "openssl"																								#
# 6 - Run this command line:																										#
#"enc -d -aes-256-cbc -salt -a -in C:\Users\YourUserName\Downloads\$DBNAME-$DATE.sql.gz.ENC -out C:\Users\YourUserName\Downloads\$DBNAME-$DATE.sql.gz -pass pass:$OPENPASS"	#
# 7 - Enter your password: $OPENPASS																								#
#                                             																									#
#  *********************************************************************************************************************************************************************************************	#
#                                             																									#
#  Unzip from Pair:																											#
#                                             																									#
# gunzip $DBNAME-$DATE.sql.gz 																									#
#                                             																									#
# ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------	#
#                                             																									#
# Unzip from Windows : 																										#
#                                             																									#
# 1 - Install and run 7Zip ref:																									#
#       http://www.7-zip.org/																									#
# 2 - From 7Zip...																											#
# 3 - Open the archive on your computer $DBNAME-$DATE.sql.gz 																				#
# 4 - If needed, open SQL file with  Notepad++ ou SciTE ref:																					#
#      http://notepad-plus-plus.org/																									#
#      http://www.scintilla.org/SciTE.html																								#
# 5 - Or reinstall your database																									#
#                                             																									#
#####################################################################################################################

Sarouel · Sep 3, 2018

2018 update:

Warning with MySQL 5.6

"--password=$dbpwd" gets an "insecure" warning with MySQL 5.6:

"Warning: Using a password on the command line interface can be insecure."

https://dev.mysql.com/doc/refman/5.6/en/mysql-config-editor.html

You can fix this issue this way:

1 - Create a ".mylogin.cnf" file with this command line:

mysql_config_editor set --login-path=DBNAME --host=DBSERVER --user=USERNAME --password

DBNAME - The name of the database for which you wish to set the login

DBSERVER - The name of the server holding the DB, such as localhost

USERNAME - The MySQL login username for the DBNAME database

You will then be prompted for your database password

Please note: This ".mylogin.cnf" file will be saved here: /usr/home/YourUsername/.mylogin.cnf

Your database password will be encrypted there.

2 - If you have more than only one database in your database server, please add your additional databases credentials into your just created ".mylogin.cnf" file your second, third, fourth... databases with the same command line:

mysql_config_editor set --login-path=DBNAME2 --host=DBSERVER --user=USERNAME2 --password
mysql_config_editor set --login-path=DBNAME3 --host=DBSERVER --user=USERNAME3 --password
Etc.

All your additional database passwords will be encrypted in the same ".mylogin.cnf" file.

*****

Please note, I leaved Pair networks in 2017 for a "MX90-SSD" Hetzner managed server the same year.

IMPORTANT

At Hetzner you will need to create a text file "mailmessage.txt" on your "$ATROOT" directory:

$ATROOT/mailmessage.txt

From this command line:

/usr/bin/mutt -s "$SUBJECT $DATE" -a $WAYTO/$DBNAME-$DATE.sql.gz.ENC -- $EMAIL < $ATROOT/mailmessage.txt

Message's body could be 2 words long like "Backup succeed.".

Bash:

 #!/bin/sh

# Database Server URL:

SERVERDB='my-database-server.com'

# Database User Name:

USERDB='Database-User-Name'

# Database Password:

# Do No more use Database Password from MySQL 5.6 because it is insecure!

# If you are using MySQL 5.6 and up, please read the above notice and use "mysql_config_editor" instead.

# PASSDB='Do-Not-Note-Database-Password-Here-Because-This-Is-Insecure'

# Database Name:

DBNAME='My-Database-Name'

#####

# Path to Root :

ATROOT='/usr/home/My-Account'

# Path to Backup directory:

WAYTO='/usr/home/My-Account/My-Backup-Directory'

# Password to encrypt Database Before Sending it by email (Do NOT use semi-colon ";"):

OPENPASS='2$qr€ab2xnUB64BxVn'

# Email address where send to the Backuped, Zipped and Encrypted Database:

EMAIL='MyEmailAddress@gmx.com'

# Email Subject (date and time is added at the end of this subject):

SUBJECT='Account Backup Date:'

# Delete the Zipped Backups Databases Older than n Day:

DAYS=1

#########################
#                                              #
#  Do NOT Change Under This Box   #
#                                              #
#########################

DATE=`date +\%Y-\%m-\%d-at-\%H:\%M:\%S`

/usr/bin/mysqldump --login-path=$DBNAME $DBNAME --single-transaction --routines --triggers --default-character-set=latin1 | gzip -9 > $WAYTO/$DBNAME-$DATE.sql.gz

openssl enc -aes-256-cbc -salt -a -in $WAYTO/$DBNAME-$DATE.sql.gz -out $WAYTO/$DBNAME-$DATE.sql.gz.ENC -pass pass:$OPENPASS

[ -f $ATROOT/sent.lock ] && rm $ATROOT/sent.lock

[ -f $ATROOT/sent ] && rm $ATROOT/sent

[ ! -f $ATROOT/sent ] && touch $ATROOT/sent

# At Pair.com :

# /usr/bin/mutt -s "$SUBJECT $DATE" -a $WAYTO/$DBNAME-$DATE.sql.gz.ENC -- $EMAIL > /dev/null

# At Hetzner.com :

/usr/bin/mutt -s "$SUBJECT $DATE" -a $WAYTO/$DBNAME-$DATE.sql.gz.ENC -- $EMAIL < $ATROOT/mailmessage.txt

[ -f $ATROOT/sent.lock ] && rm $ATROOT/sent.lock

[ -f $ATROOT/sent ] && rm $ATROOT/sent

[ ! -f $ATROOT/sent ] && touch $ATROOT/sent

rm $WAYTO/$DBNAME-$DATE.sql.gz.ENC

find $WAYTO/$DBNAME-*.sql.gz -mtime +$DAYS -exec rm {} \;

#############################################################################################################
#                                                                                                                                         #
#  To Decrypt From UNIX Terminal :                                                                                            #
#                                                                                                                                         #
# 1 - Open OpenSSL: openssl                                                                                            #
# 2 - enc -d -aes-256-cbc -salt -a -in $WAYTO/$DBNAME-$DATE.sql.gz.ENC -out $WAYTO/$DBNAME-$DATE.sql.gz -pass pass:$OPENPASS                            #
#                                                                                                                                         #
# --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------    #
#                                                                                                                                         #
# To Decrypt From Windows :                                                                                         #
#                                                                                                                                         #
# 1 - Download "OpenSSL for Windows 64bit version"                                                                                #
# 2 - Install "OpenSSL for Windows 64bit version" on this directory : "C:\Program Files\OpenSSL"                                                #
# 3 - Open the Command Prompt Windows console : "cmd.exe"                                                                        #
# 4 - Go from the Windows console to the directory "OpenSSL" of your desktop : "cd C:\Program Files\OpenSSL\bin"                                        #
# 5 - Open "openssl" from Windows console : "openssl"                                                                                #
# 6 - Run this command line :                                                                                                 #
#    enc -d -aes-256-cbc -salt -a -in C:\Users\My-Username\Downloads\$DBNAME-$DATE.sql.gz.ENC -out C:\Users\My-Username\Downloads\$DBNAME-$DATE.sql.gz -pass pass:$OPENPASS"        #
# 7 - Enter your password at the prompt: $OPENPASS                                                                                    #
#                                                                                                                                         #
#  ********************************************************************************************************************************************************************************    #
#                                                                                                                                         #
#  To Unzip from Hetzner :                                                                                            #
#                                                                                                                                         #
# gunzip $DBNAME-$DATE.sql.gz                                                                                             #
#                                                                                                                                         #
# --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------    #
#                                                                                                                                         #
# To Unzip from  Windows :                                                                                             #
#                                                                                                                                         #
# 1 - Open 7Zip                                                                                                     #
# 2 - From 7Zip...                                                                                                    #
# 3 - Open the archive on your Windows computer $DBNAME-$DATE.sql.gz                                                                     #
# 4 - If needed, open the SQL file with Notepad++ or SciTE, or...                                                                    #
# 5 - Reinstall your database on your Web server                                                                                    #
#                                                                                                                                         #
#############################################################################################################

Sarouel · Nov 3, 2026

2026 update: the script, rewritten for safety

Hello everyone,

Some of you may remember this thread: I started it in 2011 as a total beginner, and posted an update in 2018.

First, the good news: the 2018 version still works perfectly. It has been running from cron every night for years, and it saved me two or three times when a database went wrong. But I have always worried about security, and that worry has not gone away.

My programming level has not moved since 2011: still zero. In 2011, SirDice taught me to fish

. So in 2024 I asked Claude.ai (an AI assistant) to help me modernise the script, and in 2026 with Claude Opus 5.5 High, before finally posting it, I asked it to review everything again. The 2024 draft had real problems (wrong bash path for FreeBSD, the passphrase visible in ps, an unencrypted copy left on the server). This is the corrected and tested version.

What it does
Every night: a FULL dump of one database, compressed, encrypted, checked, then e-mailed to you. One file = one complete database, so restoring needs a single file (no incremental backups).

What is new

Plain /bin/sh (POSIX): no bash needed on FreeBSD.
No password anywhere in the script: database credentials and the encryption passphrase are in two separate files readable only by you.
The passphrase is never on the command line, so other users cannot see it with ps.
The unencrypted dump never touches the disk (dump, gzip and openssl in one stream).
After encryption, the script decrypts the file again and checks that the dump is complete ("-- Dump completed") before sending it.
On any failure you get an alert e-mail, plus a log file.
Works with MySQL and MariaDB (mariadb-dump is detected automatically).
PBKDF2 with 600000 iterations (current OWASP figure), file names Windows accepts, a lock against double runs, and no more sent / sent.lock hack (mutt is told not to keep a copy).

Files

db-backup.sh: the script (below)
backup.conf: the settings, one file per database (below)
A step-by-step setup guide for beginners, including how to store the database password with an option file or with mysql_config_editor, cron, and how to decrypt and restore on FreeBSD or Windows (next post)

Comments and corrections from the experts are very welcome. This time I promise to read the manual you link to. :beer

Thanks to everyone who helped in this thread over the years, and to Claude.ai for this version.

db-backup.sh

sh:

#!/bin/sh
#
# db-backup.sh - 2026 edition
#
# Dumps ONE MySQL/MariaDB database, compresses it, encrypts it,
# checks that the encrypted file can really be decrypted and is complete,
# then e-mails it to you. Made to run unattended from cron.
#
# Each backup is a FULL backup: one single file is enough to restore.
#
# Usage:
#   db-backup.sh                      (uses backup.conf next to this script)
#   db-backup.sh /path/to/other.conf  (one config file per database)
#
# Plain POSIX sh: works with FreeBSD /bin/sh, Linux dash or bash.
# No bash needed.
#
# History: 2011 original, 2018 update, 2026 rewrite with help from Claude.ai

set -eu
umask 077      # every file this script creates is readable by you only

# ---------------------------------------------------------------------------
# 1. Load the configuration
# ---------------------------------------------------------------------------
SCRIPT_DIR=$(cd "$(dirname "$0")" && pwd)
CONFIG_FILE=${1:-"$SCRIPT_DIR/backup.conf"}

if [ ! -r "$CONFIG_FILE" ]; then
    echo "ERROR: cannot read config file: $CONFIG_FILE" >&2
    exit 1
fi
case $CONFIG_FILE in
    */*) . "$CONFIG_FILE" ;;
    *)   . "./$CONFIG_FILE" ;;
esac

# These settings are required
for var in DB_NAME BACKUP_DIR KEY_FILE EMAIL_TO SUBJECT; do
    eval "val=\${$var:-}"
    if [ -z "$val" ]; then
        echo "ERROR: $var is not set in $CONFIG_FILE" >&2
        exit 1
    fi
done

# Default values for anything the config file does not set
: "${DB_CNF_FILE:=}"
: "${DB_LOGIN_PATH:=}"
: "${DB_CHARSET:=utf8mb4}"
: "${DUMP_CMD:=}"
: "${PBKDF2_ITER:=600000}"
: "${KEEP_DAYS:=1}"
: "${MAX_ATTACH_MB:=20}"
: "${ALERT_EMAIL:=$EMAIL_TO}"
: "${MUTT:=}"
: "${LOG_FILE:=$BACKUP_DIR/backup.log}"

STAMP=$(date +%Y-%m-%d_%Hh%M)          # no ":" so Windows accepts the name
FINAL="$BACKUP_DIR/${DB_NAME}_${STAMP}.sql.gz.enc"
PART="$FINAL.part"
LOCK_DIR="$BACKUP_DIR/.lock_${DB_NAME}"
RC_FILE="$BACKUP_DIR/.rc_${DB_NAME}.$$"
ERR_FILE="$BACKUP_DIR/.err_${DB_NAME}.$$"
BODY_FILE="$BACKUP_DIR/.mail_${DB_NAME}.$$"

# ---------------------------------------------------------------------------
# 2. Small helper functions
# ---------------------------------------------------------------------------
log() {
    printf '[%s] %s: %s\n' "$(date '+%Y-%m-%d %H:%M:%S')" "$DB_NAME" "$1" >> "$LOG_FILE"
}

# Called on any problem: writes the log, prints to stderr (cron mails stderr
# to you if MAILTO is set) and sends a short alert e-mail with no attachment.
fail() {
    log "FAILED: $1"
    echo "BACKUP FAILED for $DB_NAME: $1" >&2
    if [ -n "$MUTT" ] && [ -x "$MUTT" ]; then
        printf 'Backup of database %s FAILED on %s at %s.\n\nReason: %s\n\nSee the log: %s\n' \
            "$DB_NAME" "$(hostname)" "$STAMP" "$1" "$LOG_FILE" \
            | "$MUTT" -e 'set copy=no' -s "FAILED: $SUBJECT $STAMP" -- "$ALERT_EMAIL" || true
    fi
    exit 1
}

cleanup() {
    rm -f "$PART" "$RC_FILE" "$ERR_FILE" "$BODY_FILE"
    rmdir "$LOCK_DIR" 2>/dev/null || true
}

# ---------------------------------------------------------------------------
# 3. Checks before starting
# ---------------------------------------------------------------------------
mkdir -p "$BACKUP_DIR"

# Find mutt if no path was given
if [ -z "$MUTT" ]; then
    MUTT=$(command -v mutt || true)
fi

# Lock: never run two backups of the same database at the same time
if ! mkdir "$LOCK_DIR" 2>/dev/null; then
    fail "another backup is still running (remove $LOCK_DIR if it is stale)"
fi
trap cleanup EXIT
trap 'exit 1' INT TERM HUP

[ -n "$MUTT" ] && [ -x "$MUTT" ] || fail "mutt not found (set MUTT in the config)"
command -v openssl >/dev/null 2>&1 || fail "openssl not found"
command -v gzip    >/dev/null 2>&1 || fail "gzip not found"

# Dump program: MariaDB 11+ prefers mariadb-dump, MySQL uses mysqldump
if [ -z "$DUMP_CMD" ]; then
    DUMP_CMD=$(command -v mariadb-dump || command -v mysqldump || true)
fi
[ -n "$DUMP_CMD" ] || fail "neither mariadb-dump nor mysqldump found (set DUMP_CMD)"

# Encryption passphrase: read from a file, never written on the command line
[ -r "$KEY_FILE" ] || fail "cannot read encryption key file $KEY_FILE"
[ -s "$KEY_FILE" ] || fail "encryption key file $KEY_FILE is empty"

# Database credentials: option file (MySQL + MariaDB) or login path (MySQL)
if [ -n "$DB_CNF_FILE" ]; then
    [ -r "$DB_CNF_FILE" ] || fail "cannot read credentials file $DB_CNF_FILE"
elif [ -z "$DB_LOGIN_PATH" ]; then
    fail "set DB_CNF_FILE or DB_LOGIN_PATH in the config"
fi

# ---------------------------------------------------------------------------
# 4. Dump -> compress -> encrypt, in one stream.
#    The unencrypted dump is never written to disk.
# ---------------------------------------------------------------------------
run_dump() {
    # --defaults-extra-file MUST be the very first option
    if [ -n "$DB_CNF_FILE" ]; then
        set -- "--defaults-extra-file=$DB_CNF_FILE"
    else
        set -- "--login-path=$DB_LOGIN_PATH"
    fi
    "$DUMP_CMD" "$@" \
        --single-transaction \
        --routines \
        --triggers \
        --hex-blob \
        --no-tablespaces \
        --default-character-set="$DB_CHARSET" \
        "$DB_NAME"
}

log "starting backup with $(basename "$DUMP_CMD")"

# FreeBSD sh has no "pipefail" before 12.x and dash has none at all, so the
# exit code of the dump is saved in a small file and checked afterwards.
if ! { run_dump 2>"$ERR_FILE"; echo $? > "$RC_FILE"; } \
        | gzip -9 \
        | openssl enc -aes-256-cbc -pbkdf2 -iter "$PBKDF2_ITER" -md sha256 -salt \
                  -pass "file:$KEY_FILE" -out "$PART"
then
    fail "compression or encryption failed"
fi

DUMP_RC=$(cat "$RC_FILE" 2>/dev/null || echo 99)
if [ "$DUMP_RC" != "0" ]; then
    fail "dump exited with code $DUMP_RC: $(head -c 500 "$ERR_FILE" 2>/dev/null | tr '\n' ' ')"
fi

# ---------------------------------------------------------------------------
# 5. Verify: decrypt the file we just made and check the dump is complete.
#    mysqldump and mariadb-dump both end with "-- Dump completed on ...".
# ---------------------------------------------------------------------------
if ! openssl enc -d -aes-256-cbc -pbkdf2 -iter "$PBKDF2_ITER" -md sha256 \
             -pass "file:$KEY_FILE" -in "$PART" \
        | gzip -dc \
        | tail -n 1 \
        | grep -q '^-- Dump completed'
then
    fail "verification failed: the encrypted backup could not be decrypted or is incomplete"
fi

mv "$PART" "$FINAL"
SIZE_BYTES=$(wc -c < "$FINAL" | tr -d ' ')
SIZE_MB=$(( (SIZE_BYTES + 1048575) / 1048576 ))
log "backup verified: $(basename "$FINAL") ($SIZE_MB MB)"

# ---------------------------------------------------------------------------
# 6. Send it by e-mail (body is generated, with the exact decrypt command)
# ---------------------------------------------------------------------------
NAME=$(basename "$FINAL")
OUTNAME=${NAME%.enc}

if [ "$SIZE_MB" -gt "$MAX_ATTACH_MB" ]; then
    fail "backup is $SIZE_MB MB, over the $MAX_ATTACH_MB MB e-mail limit. It is kept on the server: $FINAL"
fi

cat > "$BODY_FILE" <<EOF
Backup of database $DB_NAME, made on $(hostname) at $STAMP.
Size: $SIZE_MB MB. Verified: the file decrypts and the dump is complete.

TO DECRYPT (Unix, macOS or Windows, OpenSSL 1.1.1 or newer):

  openssl enc -d -aes-256-cbc -pbkdf2 -iter $PBKDF2_ITER -md sha256 -in $NAME -out $OUTNAME

OpenSSL asks for your encryption passphrase.
Then open $OUTNAME with 7-Zip (Windows) or run: gunzip $OUTNAME
EOF

if ! "$MUTT" -e 'set copy=no' -s "$SUBJECT $STAMP" -a "$FINAL" -- "$EMAIL_TO" < "$BODY_FILE"; then
    fail "mutt could not send the e-mail (the backup is kept on the server: $FINAL)"
fi
log "e-mail sent to $EMAIL_TO"

# ---------------------------------------------------------------------------
# 7. Delete local encrypted copies older than KEEP_DAYS days
# ---------------------------------------------------------------------------
find "$BACKUP_DIR" -maxdepth 1 -type f -name "${DB_NAME}_*.sql.gz.enc" \
     -mtime +"$KEEP_DAYS" -exec rm -f {} +

log "done"
exit 0

backup.conf
Save it as backup.conf next to the script.

sh:

# backup.conf - settings for db-backup.sh
#
# Copy this file to "backup.conf" (same folder as db-backup.sh) and edit it.
# One config file per database: for a second database, make a copy such as
# "shop.conf" and run:  db-backup.sh /path/to/shop.conf
#
# This file contains NO password. Secrets live in two separate files that
# only you can read (see SETUP_GUIDE):
#   - the database credentials file (DB_CNF_FILE)
#   - the encryption passphrase file (KEY_FILE)

# --- Database ---------------------------------------------------------------

# Name of the database to back up
DB_NAME='your_database_name'

# Credentials, choose ONE way:
#  A) An option file, works with MySQL AND MariaDB (recommended)
DB_CNF_FILE="$HOME/.my-backup-your_database_name.cnf"
#  B) A mysql_config_editor login path (Oracle MySQL only, not MariaDB).
#     To use it, empty DB_CNF_FILE above:  DB_CNF_FILE=''
DB_LOGIN_PATH=''

# Character set used to read the data.
# utf8mb4 is right for almost every modern database.
# If your old backups used another value (for example latin1), keep that value:
# changing it on an old application can corrupt accented characters.
DB_CHARSET='utf8mb4'

# Dump program. Leave empty to auto-detect (mariadb-dump, then mysqldump).
DUMP_CMD=''

# --- Files and folders ------------------------------------------------------

# Folder where the encrypted backups are written (outside your web folder!)
BACKUP_DIR="$HOME/backups/db"

# File holding your encryption passphrase (first line only, chmod 600)
KEY_FILE="$HOME/.backup_key"

# How many days to keep the encrypted copies on the server
KEEP_DAYS=1

# Log file
LOG_FILE="$BACKUP_DIR/backup.log"

# --- Encryption -------------------------------------------------------------

# PBKDF2 iterations (key stretching). 600000 is the current OWASP figure.
# The SAME number is needed to decrypt, and it is printed in every e-mail.
PBKDF2_ITER=600000

# --- E-mail -----------------------------------------------------------------

# Where the encrypted backup is sent
EMAIL_TO='your_email@example.com'

# Where failure alerts go (default: same address). Use an address whose
# filters will NOT hide the alert in a spam folder.
ALERT_EMAIL=''

# Subject; date and time are added at the end
SUBJECT='Database backup'

# Most mail providers refuse attachments over 20-25 MB. Over this size the
# backup is kept on the server and you get an alert instead.
MAX_ATTACH_MB=20

# Path to mutt. Leave empty to auto-detect.
MUTT=''

db-backup.sh - Setup guide (2026 edition)

This guide is written for beginners. Follow the steps in order. Everything is typed in a terminal (SSH) on your server, as your normal user. You do NOT need root.

What you get at the end: every night, cron makes a full, compressed and encrypted backup of your database, checks that it can really be decrypted, and e-mails it to you. If anything goes wrong, you get an alert e-mail.

One backup file = one complete database. No incremental backups, nothing to chain together: to restore, you only need the most recent good file.

Step 1 - Put the files in place

Create a folder OUTSIDE your web folder (never inside public_html or www), and put db-backup.sh and backup.conf.example in it:

Code:

mkdir -p ~/db-backup
cd ~/db-backup
cp backup.conf.example backup.conf
chmod 700 ~/db-backup
chmod 700 db-backup.sh
chmod 600 backup.conf

Step 2 - Store your encryption passphrase in a file

The backup is encrypted with a passphrase. The script reads it from a file, so it never appears in the script, on the command line or in ps.

Type these two lines, then type your passphrase, press Enter, then press Ctrl+D:

Code:

umask 077
cat > ~/.backup_key

Then check that only you can read it:

Code:

chmod 600 ~/.backup_key
ls -l ~/.backup_key

The line must start with -rw-------. (Using cat like this keeps the passphrase out of your shell history; umask 077 makes the file private from the very first second.)

Rules for the passphrase:

Make it long: 20 characters or more, or 5-6 random words.
Only the FIRST line of the file is used.
Keep a copy somewhere else (password manager, paper in a drawer). If your server dies, this file dies with it, and without the passphrase your e-mailed backups are useless. This is the most important rule of this whole guide.

Step 3 - Store your database credentials

The script never contains your database password. Pick ONE of the two methods below.

Method A (recommended): an option file - works with MySQL AND MariaDB

Create one small file per database. Replace the three values:

Code:

cat > ~/.my-backup-your_database_name.cnf <<'EOF'
[client]
host="your_database_server.example.com"
user="your_database_user"
password="your_database_password"
EOF
chmod 600 ~/.my-backup-your_database_name.cnf

Keep the double quotes: they protect special characters such as #.

Then, in backup.conf, check that DB_CNF_FILE points to this file.

Test it (prints the first lines of a dump of the table structures):

Code:

mysqldump --defaults-extra-file=$HOME/.my-backup-your_database_name.cnf \
          --no-data --no-tablespaces your_database_name | head -n 20

On MariaDB 11 and newer, replace mysqldump with mariadb-dump.

Several databases? Create one file per database: .my-backup-shop.cnf, .my-backup-blog.cnf, and so on.

Method B: mysql_config_editor - Oracle MySQL only

mysql_config_editor stores all your logins in ONE file, ~/.mylogin.cnf. Two things to know before choosing it:

It exists only with Oracle MySQL. MariaDB does not have it.
The file is obfuscated, not truly encrypted. The MySQL manual itself says a determined attacker with access to your account can read it. Its real benefit is the same as Method A: the password is not in your scripts. The file must be readable by you only, or MySQL ignores it.

Create one "login path" per database (you are asked for the password):

Code:

mysql_config_editor set --login-path=shop --host=your_database_server.example.com --user=shop_user --password
mysql_config_editor set --login-path=blog --host=your_database_server.example.com --user=blog_user --password

List them (passwords are hidden):

Code:

mysql_config_editor print --all

Change one (run set again with the same name) or delete one:

Code:

mysql_config_editor remove --login-path=blog

Then, in each config file, set DB_CNF_FILE='' and DB_LOGIN_PATH='shop'.

Step 4 - Edit backup.conf

Open backup.conf with your editor (ee backup.conf on FreeBSD, or nano backup.conf) and fill in at least:

DB_NAME - the database name
DB_CNF_FILE - the file from Step 3
BACKUP_DIR - where the encrypted backups are kept on the server
EMAIL_TO - where to send the backups
ALERT_EMAIL - where to send failure alerts. If your backups go to a folder you never read (spam filter, archive), put an address you DO read.

About DB_CHARSET: keep utf8mb4 for a modern database. If you used another value for years (latin1 was common with old PHP sites), keep your old value. Changing it can break accented characters.

Step 5 - First run, by hand

Code:

~/db-backup/db-backup.sh ~/db-backup/backup.conf
echo $?

0 means success. Then check:

Code:

cat ~/backups/db/backup.log

You should see backup verified, e-mail sent and done, and receive an e-mail with the encrypted file attached.

Then do Step 8 once with that e-mail, to be sure you can restore.

Step 6 - Run it every night with cron

Code:

crontab -e

Add these lines (example: every night at 3:17):

Code:

MAILTO=your_email@example.com
17 3 * * * /bin/sh $HOME/db-backup/db-backup.sh $HOME/db-backup/backup.conf

MAILTO makes cron e-mail you anything the script prints on errors, as a second safety net.

Several databases? One config file and one cron line per database:

Code:

17 3 * * * /bin/sh $HOME/db-backup/db-backup.sh $HOME/db-backup/shop.conf
27 3 * * * /bin/sh $HOME/db-backup/db-backup.sh $HOME/db-backup/blog.conf

Step 7 - Keeping a month of backups

The server only keeps KEEP_DAYS days of copies. Your mailbox is the real archive: for example, a mail filter that moves these e-mails into a folder and deletes them after 30 days gives you 30 days of history, without filling the server.

Most mail providers refuse attachments over 20-25 MB. If a backup gets bigger than MAX_ATTACH_MB, it is kept on the server and you get an alert.

Step 8 - Restore a backup

8.1 Decrypt

The exact command is printed in every backup e-mail. It looks like this:

Code:

openssl enc -d -aes-256-cbc -pbkdf2 -iter 600000 -md sha256 -in mydb_2026-11-03_03h17.sql.gz.enc -out mydb_2026-11-03_03h17.sql.gz

OpenSSL asks for your passphrase.

On Windows:

Install OpenSSL. Two easy choices: "Win64 OpenSSL Light" from slproweb.com, or Git for Windows (it includes openssl.exe).
Save the attachment, for example in your Downloads folder.
Open a terminal (Start menu, type cmd) and go to that folder: cd %USERPROFILE%\Downloads
Paste the command from the e-mail. If Windows says openssl is not found, type its full path instead, for example: "C:\Program Files\OpenSSL-Win64\bin\openssl.exe" enc -d ...
Type your passphrase.

8.2 Decompress

Windows: open the .sql.gz file with 7-Zip and extract the .sql file.
Unix: gunzip mydb_2026-11-03_03h17.sql.gz

8.3 Put the data back

Warning: this replaces the current content of the database.

Code:

mysql --defaults-extra-file=$HOME/.my-backup-your_database_name.cnf your_database_name < mydb_2026-11-03_03h17.sql

(mariadb instead of mysql on MariaDB 11+.) You can also import the .sql file with phpMyAdmin or Adminer.

Note: dumps made by recent mariadb-dump start with a special "sandbox" line. It imports fine into MariaDB, but an Oracle MySQL client refuses it. To move data from MariaDB to MySQL, delete that first line.

Coming from the 2011 or 2018 version of this script?

Backups made by the old script (still in your mailbox) decrypt with: openssl enc -d -aes-256-cbc -pbkdf2 -in OLD_FILE.sql.gz.ENC -out OLD_FILE.sql.gz (no -iter: the old script used OpenSSL's default of 10000).
Remove the database password and the encryption passphrase from the old script once the new one works. They should exist only in the files of Steps 2 and 3.
The sent / sent.lock lines are gone: the new script tells mutt not to keep a copy of sent mail (set copy=no), which was the cause of that growing file.
mailmessage.txt is no longer needed: the e-mail text is generated, with the exact decrypt command and the real file name.

What changed in 2026, and why

Problem in older versions	2026 fix
Passphrase on the command line (`-k` / `-pass pass:`), visible to other users with `ps`	Read from a file only you can read (`-pass file:`)
Unencrypted `.sql.gz` left on the server	Dump, compression and encryption in one stream; only the encrypted file touches the disk
A failed or half-finished dump was still e-mailed as if all was fine	Exit code checked, and the encrypted file is decrypted again to confirm the dump ends with `-- Dump completed`
Silent failures	Alert e-mail plus a log line, and cron's `MAILTO` as a backup
10000 PBKDF2 iterations (OpenSSL default)	600000 iterations, the current OWASP figure for PBKDF2-HMAC-SHA256
`:` in file names, which Windows refuses	`2026-11-03_03h17`
`#!/bin/bash` (the 2024 draft): bash is not at that path on FreeBSD	Plain `/bin/sh`, tested with dash and bash
`mysqldump` only, and `mysql_config_editor` only	Works with MySQL and MariaDB (`mariadb-dump` auto-detected); credentials in an option file that both understand
`PROCESS privilege` error with MySQL 8.0.21+ on shared hosting	`--no-tablespaces`
Two runs at the same time could mix files	Lock folder per database

Good habits

Once every few months, restore one backup into a test database. A backup you have never restored is a hope, not a backup.
Keep your database server up to date. Oracle MySQL 8.0 stopped receiving security fixes at the end of April 2026; the supported long-term version is 8.4. If you are on a managed or shared server, ask your host which version you run.
If you change the database password, update the file from Step 3.

cracauer@ · Nov 3, 2026

Sarouel said:
/usr/bin/mysqldump --login-path=$DBNAME $DBNAME --single-transaction --routines --triggers --default-character-set=latin1 | gzip -9 > $WAYTO/$DBNAME-$DATE.sql.gz

openssl enc -aes-256-cbc -salt -a -in $WAYTO/$DBNAME-$DATE.sql.gz -out $WAYTO/$DBNAME-$DATE.sql.gz.ENC -pass pass:$OPENPASS

You should put all these variable references into double quotes (unless you deliberately want them to expand to multiple tokens).

ralphbsz · Nov 5, 2026

SirDice said:
Use the correct quotes. I'm guessing you used backticks on all the other variables too.

There is a big difference between `, ' and "

Is there a good textbook (could be a web site) that teaches shell programming for beginners? I don't know one, only know reference manuals; O'Reilly used to have quite a few of those. But for a beginner, the basic concepts (in particular quoting, variable substitution, and the process model) is vitally important, and that is hard to lear from trial and error, or from staring at details.

Just as an example of not-so-subtle problems that have hit me recently when shell programming: If a while loop creates a sub shell (sometimes it does), then changing variable in the sub shell won't have an effect after the sub shell ends. And within the sub shell, stdin/stdout may be redirected, so commands like "rm -i" (ask the user for confirmation) break in bizarre fashions.

Also: Give a man a fish, and you feed him for a day. Teach him to fish, and he'll spend every Saturday on the lake drinking beer in his new boat, and spend all his money on fishing gear.

bakul · Nov 5, 2026

What I did originally was to read the /bin/sh manpage line by line and experimented and experimented -- but I was much younger then and had lots of patience!

There is Arnold Robbins' Classic Shell Scripting book (O'Reilly). Unix Power Tools is supposed to be quite good.

But the only way to really grok variable substitution (& shell specific variants) is to experiment!

SirDice · Nov 5, 2026

ralphbsz said:
Is there a good textbook (could be a web site) that teaches shell programming for beginners?

The Grymoire website is pretty good. But it may not be for absolute beginners with zero prior programming experiences.

cracauer@ · Nov 5, 2026

Let's face is: correct quoting can be hell in bourne shell. Maybe there is a specific tutorial out there? I don't think glossing over the manpage gets the job done here.

sic_101 · Nov 5, 2026

Unix / Linux - Shell Quoting Mechanisms

In this chapter, we will discuss in detail about the Shell quoting mechanisms. We will start by discussing the metacharacters. Unix Shell provides various metacharacters which have special meaning while using them in any Shell Script and causes

www.tutorialspoint.com

bakul · Nov 5, 2026

Quoting is pretty straight-forward. Use single quotes if you want no substitutions. Within single quotes (in /bin/sh) only two characters need to be escaped with a backslash (\) before them if you want them in the string: a backslash and a single quote. Use double quotes if you want variable substitutions or want to insert the output of commands. Avoid using backquotes for the latter. Use $(<command> ...) instead as this can nest. Since more special chars are interpreted within double quotes, if you want those characters in the string, they must be escaped. When experimenting, it is best to write a simple program and examine its output. For example: main(int c, char**v){for (int i=1; i<c; i++)printf("%d: <%s>\n", i, v[i]);} - put this in a file using an editor (or use /bin/echo). Compile this (ignore warnings but not errors) and feed it your quote experiments.

/bin/sh may seem tricky if you are used to zsh, the cadillac of shells, as it does a lot of extra things in the name of convenience. For example, echo 'x\ny' will behave differently from /bin/echo 'x\ny'. Other shells have their own peculiarities.

skhal · Nov 5, 2026

ralphbsz said:
Is there a good textbook (could be a web site) that teaches shell programming for beginners? I don't know one, only know reference manuals; O'Reilly used to have quite a few of those. But for a beginner, the basic concepts (in particular quoting, variable substitution, and the process model) is vitally important, and that is hard to lear from trial and error, or from staring at details.

Just as an example of not-so-subtle problems that have hit me recently when shell programming: If a while loop creates a sub shell (sometimes it does), then changing variable in the sub shell won't have an effect after the sub shell ends. And within the sub shell, stdin/stdout may be redirected, so commands like "rm -i" (ask the user for confirmation) break in bizarre fashions.

Also: Give a man a fish, and you feed him for a day. Teach him to fish, and he'll spend every Saturday on the lake drinking beer in his new boat, and spend all his money on fishing gear.

man sh is the best source.

People use bash for advanced concepts like arrays but I would not do that. IMHO if you need to write anything more complicated than O(10) lines of code, choose another language: Python, Go, etc.

mer · Nov 5, 2026

I think bakul in #18, last paragraph has good information/perspective.
bash and zsh recognize/accept sh syntax, other distributions implicitly equate "sh == bash" so one can start to expect that. Going to a pure sh, trying to do "bash-isms" (simple bashisms) may appear to work but get wrong results.

Sometimes it's worth it to just muck around: man sh in one window, editor in another with something starting with #! /bin/sh and play with all the quotes.

Espionage724 · Nov 5, 2026

Sarouel said:
Every night: a FULL dump of one database, compressed, encrypted, checked

I've done something like this a few years with 5 websites Windows, Linux, and FreeBSD; scripts, cron/systemd timers, Task Scheduler, sh, bash, BAT, full dump/restores various MariaDB versions; it's fun

I keep things simple and consistent; like WordPress I back-up files and database same time in a script, and cron it once a month:

Code:

'/usr/bin/tar' -czf '/mnt/NAS1/Servers/Scheduled Backups/wordpress-files-auto-'$(date +%Y-%m-%d)'.tar.gz' -C '/usr/local/www' 'blog'
'/usr/local/bin/mariadb-dump' --single-transaction --quick 'wordpress' -r '/mnt/NAS1/Servers/Scheduled Backups/wordpress-database-auto-'$(date +%Y-%m-%d)'.sql'

Those two lines can have one word changed for the other 4 sites, maybe a tar/mariadb-dump path change cross FreeBSD or Linux, and same syntax Windows

Sarouel · 2026-11-12T04:53:09+0000

Thank you all! I did not expect such a turnout, 15 years after my first post in this thread.

Quoting (#12, #13, #18)
You are right, and my 2018 script was a fine example of what not to do: unquoted variables, backticks, and the passphrase on the command line (-pass pass:$OPENPASS). In the new version, every variable passed to a command is in double quotes, $(...) replaces the backticks, and the passphrase never appears on the command line: it is read from a file with -pass file:.

And to prove cracauer@ right that quoting can be hell: the first time I filled in my own config file, I forgot ONE closing quote. The shell answered Backup: not found. It had glued two lines together and tried to run the word "Backup" from my e-mail subject as a command. So the script now checks the config file line by line before loading it, and says something a beginner can understand:

Code:

ERROR in backup.conf, line 18: ALERT_EMAIL='me@example.com    # alerts
- a quote (') at BOTH ends of the value
- no space before or after the = sign

Bashisms (#20)
The script is plain /bin/sh, no bash. It was tested with dash, which refuses bashisms, and it now runs every hour for 5 of my e-commerce sites on FreeBSD.

skhal (#19)
Fair point about the length. Here it stays in sh because it only needs what a managed host already has: sh, mysqldump or mariadb-dump, gzip, openssl, mutt and cron. Nothing to install.

Espionage724 (#21)
Simple and consistent is how I like it too. The extra lines in mine exist because my backups leave the server: they travel by e-mail, so they must be encrypted. And since nobody looks at them, the script has to shout when something goes wrong (failed dump, truncated dump, mail not sent). Good point about the site files: mine only covers the databases, on purpose. My sites are online shops: the files barely change, while the database changes with every order. So each database is backed up every hour, the sites ten minutes apart, and losing one hour of orders is the worst case.

ralphbsz (#13)
Give a man a fish and he eats for a day. Teach him to fish and he spends all his money on fishing gear. Ask an AI to fish: it brings back a full net, but you'd better check every fish before serving it to the forum.

Thanks bakul, SirDice, sic_101 and skhal for the reading list (Classic Shell Scripting, Unix Power Tools, the Grymoire, man sh). Fifteen years late, I might finally open one.

Unless I just let Claude take the wheel and I’m content to read him the road book, given that I still don’t have a driving licence… He’ll drive me home safely, especially if I say a prayer (or several) to Saint Gerard Adriaan Heineken, founder of Heineken.

Changes since my last post

Credentials are read with --defaults-file: only the file you name is read, so a ~/.my.cnf made for another site can no longer replace the right credentials.
A clear message if that file has database= in its [client] section (mysqldump on MySQL 8+ refuses it; put it under [mysql]).
All settings are grouped at the top of the two files: defaults for all databases at the top of the script, one small config file per database.
The config check described above.
Big backups are cut into pieces, one e-mail per piece, like big archives on floppy disks in the old days. Each e-mail explains how to glue them back together (cat on Unix, copy /b on Windows).

The updated script and config are below; the updated setup guide is in the next post.

db-backup.sh

sh:

#!/bin/sh
# db-backup.sh - full, encrypted and verified MySQL/MariaDB backup, by e-mail.
# Usage:  db-backup.sh /path/to/backup.conf        Help: SETUP_GUIDE.md

###############################################################################
#                                                                             #
#   DEFAULT SETTINGS, shared by ALL your databases                            #
#                                                                             #
#   Each database has its own small config file (backup.conf). Any setting    #
#   that file leaves empty ('') takes the value below. Change a value here    #
#   to change it for all your databases at once.                              #
#                                                                             #
###############################################################################

DEFAULT_BACKUP_DIR="$HOME/backups/db"  # encrypted copies kept on the server
DEFAULT_KEY_FILE="$HOME/.backup_key"   # file holding the encryption passphrase
DEFAULT_KEEP_DAYS=1                    # days the copies stay on the server
DEFAULT_SUBJECT='Database backup'      # e-mail subject (date and time added)
DEFAULT_ALERT_EMAIL=''                 # failure alerts; '' = same as EMAIL_TO
DEFAULT_MAX_ATTACH_MB=15               # size of each e-mail attachment, MB
DEFAULT_MAX_PARTS=10                   # bigger backups are cut in up to N parts
DEFAULT_DB_CHARSET='utf8mb4'           # old sites often need 'latin1'
DEFAULT_LOG_FILE=''                    # '' or a name alone = in BACKUP_DIR
DEFAULT_PBKDF2_ITER=600000             # encryption strength (shown in e-mails)
DEFAULT_MUTT=''                        # path to mutt; '' = found automatically
DEFAULT_DUMP_CMD=''                    # dump program; '' = found automatically

###############################################################################
#                                                                             #
#                  Do NOT change anything under this box                      #
#                                                                             #
###############################################################################

# Dumps ONE MySQL/MariaDB database, compresses it, encrypts it, checks that
# the encrypted file really decrypts and is complete, then e-mails it.
# Each backup is a FULL backup: one single file is enough to restore.
# Plain POSIX sh: FreeBSD /bin/sh, Linux dash or bash. No bash needed.
# History: 2011 original, 2018 update, 2026 rewrite with help from Claude.ai

set -eu
umask 077      # every file this script creates is readable by you only

# ---------------------------------------------------------------------------
# 1. Load the configuration
# ---------------------------------------------------------------------------
SCRIPT_DIR=$(cd "$(dirname "$0")" && pwd)
CONFIG_FILE=${1:-"$SCRIPT_DIR/backup.conf"}

if [ ! -r "$CONFIG_FILE" ]; then
    echo "ERROR: cannot read config file: $CONFIG_FILE" >&2
    exit 1
fi

# Check every setting line before loading the file, so that a missing quote
# gives a clear message instead of a strange shell error.
BAD_LINE=$(awk -v q="'" '
    BEGIN {
        ok = "^[ \t]*[A-Za-z_][A-Za-z0-9_]*=(" q "[^" q "]*" q "|\"[^\"]*\"|[^ \t\"#" q "]*)[ \t]*(#.*)?$"
    }
    /\r/              { print NR ": Windows line endings (save the file in Unix format)"; exit }
    /^[ \t]*(#|$)/    { next }
    $0 ~ ok           { next }
                      { print NR ": " $0; exit }' "$CONFIG_FILE")
if [ -n "$BAD_LINE" ]; then
    echo "ERROR in $CONFIG_FILE, line $BAD_LINE" >&2
    echo "Each setting must look like:  NAME='value'   # optional comment" >&2
    echo "- a quote (') at BOTH ends of the value" >&2
    echo "- no space before or after the = sign" >&2
    exit 1
fi

case $CONFIG_FILE in
    */*) . "$CONFIG_FILE" ;;
    *)   . "./$CONFIG_FILE" ;;
esac

# Required in every config file
for var in DB_NAME EMAIL_TO; do
    eval "val=\${$var:-}"
    if [ -z "$val" ]; then
        echo "ERROR: $var is empty in $CONFIG_FILE" >&2
        exit 1
    fi
done

# Empty settings take the default value from the top of this file
: "${DB_CNF_FILE:=}"
: "${DB_LOGIN_PATH:=}"
: "${BACKUP_DIR:=$DEFAULT_BACKUP_DIR}"
: "${KEY_FILE:=$DEFAULT_KEY_FILE}"
: "${KEEP_DAYS:=$DEFAULT_KEEP_DAYS}"
: "${SUBJECT:=$DEFAULT_SUBJECT}"
: "${ALERT_EMAIL:=$DEFAULT_ALERT_EMAIL}"
: "${ALERT_EMAIL:=$EMAIL_TO}"
: "${MAX_ATTACH_MB:=$DEFAULT_MAX_ATTACH_MB}"
: "${MAX_PARTS:=$DEFAULT_MAX_PARTS}"
: "${DB_CHARSET:=$DEFAULT_DB_CHARSET}"
: "${LOG_FILE:=$DEFAULT_LOG_FILE}"
: "${LOG_FILE:=$BACKUP_DIR/backup.log}"
: "${PBKDF2_ITER:=$DEFAULT_PBKDF2_ITER}"
: "${MUTT:=$DEFAULT_MUTT}"
: "${DUMP_CMD:=$DEFAULT_DUMP_CMD}"

# A log file name without a folder (e.g. 'backup.log') goes in BACKUP_DIR
case $LOG_FILE in
    */*) ;;
    *)   LOG_FILE="$BACKUP_DIR/$LOG_FILE" ;;
esac

# Numbers must be numbers
for var in KEEP_DAYS MAX_ATTACH_MB MAX_PARTS PBKDF2_ITER; do
    eval "val=\$$var"
    case $val in
        ''|*[!0-9]*)
            echo "ERROR: $var must be a whole number (now: '$val') in $CONFIG_FILE" >&2
            exit 1 ;;
    esac
done
if [ "$MAX_ATTACH_MB" -lt 1 ] || [ "$MAX_PARTS" -lt 1 ]; then
    echo "ERROR: MAX_ATTACH_MB and MAX_PARTS must be at least 1 in $CONFIG_FILE" >&2
    exit 1
fi

STAMP=$(date +%Y-%m-%d_%Hh%M)          # no ":" so Windows accepts the name
FINAL="$BACKUP_DIR/${DB_NAME}_${STAMP}.sql.gz.enc"
PART="$FINAL.part"
LOCK_DIR="$BACKUP_DIR/.lock_${DB_NAME}"
RC_FILE="$BACKUP_DIR/.rc_${DB_NAME}.$$"
ERR_FILE="$BACKUP_DIR/.err_${DB_NAME}.$$"
BODY_FILE="$BACKUP_DIR/.mail_${DB_NAME}.$$"
PIECE_DIR="$BACKUP_DIR/.pieces_${DB_NAME}.$$"

# ---------------------------------------------------------------------------
# 2. Small helper functions
# ---------------------------------------------------------------------------
log() {
    printf '[%s] %s: %s\n' "$(date '+%Y-%m-%d %H:%M:%S')" "$DB_NAME" "$1" >> "$LOG_FILE"
}

# Called on any problem: writes the log, prints to stderr (cron mails stderr
# to you if MAILTO is set) and sends a short alert e-mail with no attachment.
fail() {
    log "FAILED: $1"
    echo "BACKUP FAILED for $DB_NAME: $1" >&2
    if [ -n "$MUTT" ] && [ -x "$MUTT" ]; then
        printf 'Backup of database %s FAILED on %s at %s.\n\nReason: %s\n\nSee the log: %s\n' \
            "$DB_NAME" "$(hostname)" "$STAMP" "$1" "$LOG_FILE" \
            | "$MUTT" -e 'set copy=no' -s "FAILED: $SUBJECT $STAMP" -- "$ALERT_EMAIL" || true
    fi
    exit 1
}

cleanup() {
    rm -f "$PART" "$RC_FILE" "$ERR_FILE" "$BODY_FILE"
    rm -rf "$PIECE_DIR"
    rmdir "$LOCK_DIR" 2>/dev/null || true
}

# ---------------------------------------------------------------------------
# 3. Checks before starting
# ---------------------------------------------------------------------------
mkdir -p "$BACKUP_DIR"

# Find mutt if no path was given
if [ -z "$MUTT" ]; then
    MUTT=$(command -v mutt || true)
fi

# Lock: never run two backups of the same database at the same time
if ! mkdir "$LOCK_DIR" 2>/dev/null; then
    fail "another backup is still running (remove $LOCK_DIR if it is stale)"
fi
trap cleanup EXIT
trap 'exit 1' INT TERM HUP

[ -n "$MUTT" ] && [ -x "$MUTT" ] || fail "mutt not found (set MUTT in the config)"
command -v openssl >/dev/null 2>&1 || fail "openssl not found"
command -v gzip    >/dev/null 2>&1 || fail "gzip not found"

# Dump program: MariaDB 11+ prefers mariadb-dump, MySQL uses mysqldump
if [ -z "$DUMP_CMD" ]; then
    DUMP_CMD=$(command -v mariadb-dump || command -v mysqldump || true)
fi
[ -n "$DUMP_CMD" ] || fail "neither mariadb-dump nor mysqldump found (set DUMP_CMD)"

# Encryption passphrase: read from a file, never written on the command line
[ -r "$KEY_FILE" ] || fail "cannot read encryption key file $KEY_FILE"
[ -s "$KEY_FILE" ] || fail "encryption key file $KEY_FILE is empty"

# Database credentials: option file (MySQL + MariaDB) or login path (MySQL)
if [ -n "$DB_CNF_FILE" ]; then
    [ -r "$DB_CNF_FILE" ] || fail "cannot read credentials file $DB_CNF_FILE"
    CNF_TO_CHECK=$DB_CNF_FILE
elif [ -n "$DB_LOGIN_PATH" ]; then
    CNF_TO_CHECK="$HOME/.my.cnf"     # still read when a login path is used
else
    fail "set DB_CNF_FILE or DB_LOGIN_PATH in the config"
fi

# A "database=" line in the [client] section is fine for the mysql client,
# but mysqldump has no such option: MySQL 8 and newer stop with
# "unknown variable 'database=...'". Catch it here with a clear message.
if [ -r "$CNF_TO_CHECK" ] && awk '
    /^[ \t]*\[/ {
        s = tolower($0); sub(/^[ \t]*\[[ \t]*/, "", s); sub(/[ \t]*\].*$/, "", s); next
    }
    (s == "client" || s == "mysqldump" || s == "mariadb-dump" ||
     s == "client-server" || s == "client-mariadb") &&
    tolower($0) ~ /^[ \t]*database[ \t]*(=|$)/ { found = 1 }
    END { exit !found }' "$CNF_TO_CHECK"
then
    fail "$CNF_TO_CHECK has a 'database=' line in its [client] section, which mysqldump refuses. Move that line under a [mysql] section, or delete it: the database name comes from DB_NAME in the config."
fi

# ---------------------------------------------------------------------------
# 4. Dump -> compress -> encrypt, in one stream.
#    The unencrypted dump is never written to disk.
# ---------------------------------------------------------------------------
run_dump() {
    # --defaults-file MUST be the very first option. It reads ONLY this file,
    # so a ~/.my.cnf made for another site cannot override these credentials.
    if [ -n "$DB_CNF_FILE" ]; then
        set -- "--defaults-file=$DB_CNF_FILE"
    else
        set -- "--login-path=$DB_LOGIN_PATH"
    fi
    "$DUMP_CMD" "$@" \
        --single-transaction \
        --routines \
        --triggers \
        --hex-blob \
        --no-tablespaces \
        --default-character-set="$DB_CHARSET" \
        "$DB_NAME"
}

log "starting backup with $(basename "$DUMP_CMD")"

# FreeBSD sh has no "pipefail" before 12.x and dash has none at all, so the
# exit code of the dump is saved in a small file and checked afterwards.
if ! { run_dump 2>"$ERR_FILE"; echo $? > "$RC_FILE"; } \
        | gzip -9 \
        | openssl enc -aes-256-cbc -pbkdf2 -iter "$PBKDF2_ITER" -md sha256 -salt \
                  -pass "file:$KEY_FILE" -out "$PART"
then
    fail "compression or encryption failed"
fi

DUMP_RC=$(cat "$RC_FILE" 2>/dev/null || echo 99)
if [ "$DUMP_RC" != "0" ]; then
    fail "dump exited with code $DUMP_RC: $(head -c 500 "$ERR_FILE" 2>/dev/null | tr '\n' ' ')"
fi

# ---------------------------------------------------------------------------
# 5. Verify: decrypt the file we just made and check the dump is complete.
#    mysqldump and mariadb-dump both end with "-- Dump completed on ...".
# ---------------------------------------------------------------------------
if ! openssl enc -d -aes-256-cbc -pbkdf2 -iter "$PBKDF2_ITER" -md sha256 \
             -pass "file:$KEY_FILE" -in "$PART" \
        | gzip -dc \
        | tail -n 1 \
        | grep -q '^-- Dump completed'
then
    fail "verification failed: the encrypted backup could not be decrypted or is incomplete"
fi

mv "$PART" "$FINAL"
SIZE_BYTES=$(wc -c < "$FINAL" | tr -d ' ')
SIZE_MB=$(( (SIZE_BYTES + 1048575) / 1048576 ))
log "backup verified: $(basename "$FINAL") ($SIZE_MB MB)"

# ---------------------------------------------------------------------------
# 6. Send it by e-mail. A backup bigger than MAX_ATTACH_MB is cut into
#    pieces (like big archives on floppy disks), one e-mail per piece.
#    The e-mail text is generated, with the exact commands to restore.
# ---------------------------------------------------------------------------
NAME=$(basename "$FINAL")
OUTNAME=${NAME%.enc}
PIECE_BYTES=$((MAX_ATTACH_MB * 1048576))
PARTS=$(( (SIZE_BYTES + PIECE_BYTES - 1) / PIECE_BYTES ))
DECRYPT_CMD="openssl enc -d -aes-256-cbc -pbkdf2 -iter $PBKDF2_ITER -md sha256 -in $NAME -out $OUTNAME"

if [ "$PARTS" -gt "$MAX_PARTS" ]; then
    fail "backup is $SIZE_MB MB: it needs $PARTS e-mails of $MAX_ATTACH_MB MB, more than MAX_PARTS=$MAX_PARTS. It is kept on the server: $FINAL"
fi

send_mail() {   # $1 = subject, $2 = file to attach (body is in BODY_FILE)
    "$MUTT" -e 'set copy=no' -s "$1" -a "$2" -- "$EMAIL_TO" < "$BODY_FILE"
}

if [ "$PARTS" -le 1 ]; then
    cat > "$BODY_FILE" <<EOF
Backup of database $DB_NAME, made on $(hostname) at $STAMP.
Size: $SIZE_MB MB. Verified: the file decrypts and the dump is complete.

TO RESTORE:

1. Decrypt (Linux, FreeBSD, macOS or Windows, OpenSSL 1.1.1 or newer).
   OpenSSL asks for your encryption passphrase:

     $DECRYPT_CMD

2. Decompress:

   - On Linux, FreeBSD or macOS:   gunzip $OUTNAME
   - On Windows: open $OUTNAME with 7-Zip.
EOF
    send_mail "$SUBJECT $STAMP" "$FINAL" \
        || fail "mutt could not send the e-mail (the backup is kept on the server: $FINAL)"
    log "e-mail sent to $EMAIL_TO"
else
    mkdir "$PIECE_DIR"
    split -b "$PIECE_BYTES" "$FINAL" "$PIECE_DIR/$NAME.part-" \
        || fail "could not cut the backup into pieces"
    SHA=$(openssl dgst -sha256 "$FINAL" | sed 's/^.*= *//')

    WIN_LIST=''
    for P in "$PIECE_DIR/$NAME".part-*; do
        WIN_LIST="${WIN_LIST:+$WIN_LIST + }$(basename "$P")"
    done

    I=0
    for P in "$PIECE_DIR/$NAME".part-*; do
        I=$((I + 1))
        cat > "$BODY_FILE" <<EOF
Backup of database $DB_NAME, made on $(hostname) at $STAMP.
Size: $SIZE_MB MB. Verified: the file decrypts and the dump is complete.

This backup was too big for one e-mail.
THIS IS PART $I OF $PARTS. You need ALL $PARTS e-mails with the same date and time.

TO RESTORE:

1. Save the $PARTS attachments in the same folder.

2. Glue them back together, in that folder. Copy ONLY the line for your
   system (one line, nothing else):

   - On Linux, FreeBSD or macOS (terminal):

     cat $NAME.part-* > $NAME

   - On Windows (cmd.exe, not PowerShell):

     copy /b $WIN_LIST $NAME

3. Optional check. This command:

     openssl dgst -sha256 $NAME

   must end with this code:

     $SHA

4. Decrypt (OpenSSL asks for your encryption passphrase):

     $DECRYPT_CMD

5. Decompress:

   - On Linux, FreeBSD or macOS:   gunzip $OUTNAME
   - On Windows: open $OUTNAME with 7-Zip.
EOF
        send_mail "$SUBJECT $STAMP (part $I of $PARTS)" "$P" \
            || fail "mutt could not send part $I of $PARTS (the backup is kept on the server: $FINAL)"
    done
    rm -rf "$PIECE_DIR"
    log "e-mailed to $EMAIL_TO in $PARTS parts of up to $MAX_ATTACH_MB MB"
fi

# ---------------------------------------------------------------------------
# 7. Delete local encrypted copies older than KEEP_DAYS days
# ---------------------------------------------------------------------------
find "$BACKUP_DIR" -maxdepth 1 -type f -name "${DB_NAME}_*.sql.gz.enc" \
     -mtime +"$KEEP_DAYS" -exec rm -f {} +

log "done"
exit 0

backup.conf
Save it as backup.conf next to the script.

sh:

# backup.conf - settings for ONE database, used by db-backup.sh
# Explanations: see the HELP section at the bottom of this file.

###############################################################################
#                                                                             #
#   YOUR SETTINGS                                                             #
#                                                                             #
#   The first 3 lines are required. Leave the others empty ('') to use the    #
#   default shown on the right (defaults are at the top of db-backup.sh).     #
#                                                                             #
###############################################################################

DB_NAME='your_database_name'                           # database to back up
DB_CNF_FILE="$HOME/.my-backup-your_database_name.cnf"  # its credentials file
EMAIL_TO='your_email@example.com'                      # backups are sent here

ALERT_EMAIL=''     # failure alerts           default: same as EMAIL_TO
SUBJECT=''         # e-mail subject           default: Database backup
DB_CHARSET=''      # character set            default: utf8mb4
BACKUP_DIR=''      # copies on the server     default: $HOME/backups/db
KEY_FILE=''        # passphrase file          default: $HOME/.backup_key
KEEP_DAYS=''       # days kept on the server  default: 1
MAX_ATTACH_MB=''   # attachment size, MB      default: 15
MAX_PARTS=''       # max e-mails per backup   default: 10
LOG_FILE=''        # log file                 default: BACKUP_DIR/backup.log
DB_LOGIN_PATH=''   # mysql_config_editor      (only if DB_CNF_FILE is '')
PBKDF2_ITER=''     # encryption strength      default: 600000
MUTT=''            # path to mutt             default: found automatically
DUMP_CMD=''        # dump program             default: found automatically

###############################################################################
#                                                                             #
#   HELP - nothing to change below this line                                  #
#                                                                             #
###############################################################################
#
# Each setting: NAME='value'  - a quote at both ends, no space around =
#
# This file contains NO password. Secrets live in two separate files that
# only you can read (see SETUP_GUIDE.md, steps 2 and 3).
#
# SEVERAL DATABASES: one config file per database. Copy this file (for
#   example to shop.conf), change it, and add one cron line per file:
#   /bin/sh $HOME/db-backup/db-backup.sh $HOME/db-backup/shop.conf
#
# DB_NAME        Name of the database to back up.
#
# DB_CNF_FILE    Credentials file (works with MySQL AND MariaDB), chmod 600:
#                  [client]
#                  host="your_database_server.example.com"
#                  user="your_database_user"
#                  password="your_database_password"
#                No "database=" line in [client]: mysqldump refuses it. If
#                your other scripts need it, put it under a [mysql] section.
#                Several databases with the same user can share one file.
#
# EMAIL_TO       Where the encrypted backups are sent.
#
# ALERT_EMAIL    Where failure alerts are sent. If your backups go to a
#                folder you never read (spam filter, archive), put here an
#                address you DO read.
#
# SUBJECT        E-mail subject. Date and time are added at the end.
#
# DB_CHARSET     utf8mb4 is right for almost every modern database. If your
#                old backups used another value (often latin1), keep it:
#                changing it can break accented characters.
#
# BACKUP_DIR     Folder for the encrypted copies on the server. Keep it
#                OUTSIDE your web folder.
#
# KEY_FILE       File holding your encryption passphrase (first line only,
#                chmod 600). KEEP A COPY ELSEWHERE: without it, your e-mailed
#                backups cannot be opened.
#
# KEEP_DAYS      Days the encrypted copies stay on the server. Your mailbox
#                is the long-term archive.
#
# MAX_ATTACH_MB  Size of each attachment. A bigger backup is cut into pieces
#                of this size, one e-mail per piece (like big archives on
#                floppy disks). Each e-mail explains how to glue the pieces
#                back together. An attachment makes the e-mail about 37%
#                bigger, so stay well under your provider's limit:
#                Outlook/Hotmail 25 MB per e-mail -> 15;  GMX 50 MB -> 35.
#
# MAX_PARTS      Most e-mails for one backup. If more are needed, the backup
#                is kept on the server and you get an alert instead.
#
# LOG_FILE       Log of every run. A name alone (backup.log) goes in BACKUP_DIR.
#
# DB_LOGIN_PATH  Oracle MySQL only: a login path made with mysql_config_editor
#                (see SETUP_GUIDE.md, method B). Used only if DB_CNF_FILE=''.
#
# PBKDF2_ITER    Key stretching for the encryption. The SAME number is needed
#                to decrypt; it is printed in every backup e-mail.
#
# MUTT, DUMP_CMD Full paths, only if they are not found automatically.
#                DUMP_CMD is mariadb-dump (MariaDB 11+) or mysqldump.

Sarouel · 2026-11-12T04:55:00+0000

db-backup.sh - Setup guide (2026 edition)

This guide is written for beginners. Follow the steps in order. Everything is typed in a terminal (SSH) on your server, as your normal user. You do NOT need root.

What you get at the end: every night, cron makes a full, compressed and encrypted backup of your database, checks that it can really be decrypted, and e-mails it to you. If anything goes wrong, you get an alert e-mail.

One backup file = one complete database. No incremental backups, nothing to chain together: to restore, you only need the most recent good file.

Step 1 - Put the files in place

Create a folder OUTSIDE your web folder (never inside public_html or www), and put db-backup.sh and backup.conf.example in it:

Code:

mkdir -p ~/db-backup
cd ~/db-backup
cp backup.conf.example backup.conf
chmod 700 ~/db-backup
chmod 700 db-backup.sh
chmod 600 backup.conf

Step 2 - Store your encryption passphrase in a file

The backup is encrypted with a passphrase. The script reads it from a file, so it never appears in the script, on the command line or in ps.

Type these two lines, then type your passphrase, press Enter, then press Ctrl+D:

Code:

umask 077
cat > ~/.backup_key

Then check that only you can read it:

Code:

chmod 600 ~/.backup_key
ls -l ~/.backup_key

The line must start with -rw-------. (Using cat like this keeps the passphrase out of your shell history; umask 077 makes the file private from the very first second.)

Rules for the passphrase:

Make it long: 20 characters or more, or 5-6 random words.
Only the FIRST line of the file is used.
Keep a copy somewhere else (password manager, paper in a drawer). If your server dies, this file dies with it, and without the passphrase your e-mailed backups are useless. This is the most important rule of this whole guide.

Step 3 - Store your database credentials

The script never contains your database password. Pick ONE of the two methods below.

Method A (recommended): an option file - works with MySQL AND MariaDB

Create one small file per database. Replace the three values:

Code:

cat > ~/.my-backup-your_database_name.cnf <<'EOF'
[client]
host="your_database_server.example.com"
user="your_database_user"
password="your_database_password"
EOF
chmod 600 ~/.my-backup-your_database_name.cnf

Keep the double quotes: they protect special characters such as #.

Do NOT put a database= line in the [client] section. The database name comes from DB_NAME in backup.conf, and mysqldump has no database option: MySQL 8 and newer stop with unknown variable 'database=...'. The script checks for this and tells you.

You already have .my.cnf files with database= for other scripts? You can reuse them. Move the database= line under a [mysql] section, which only the mysql client reads, so your other scripts keep working:

Code:

[client]
host="your_database_server.example.com"
user="your_database_user"
password="your_database_password"

[mysql]
database="your_database_name"

Then, in backup.conf, check that DB_CNF_FILE points to this file. The script reads ONLY this file (--defaults-file), so a ~/.my.cnf made for another site can never replace these credentials.

Test it (prints the first lines of a dump of the table structures):

Code:

mysqldump --defaults-file=$HOME/.my-backup-your_database_name.cnf \
          --no-data --no-tablespaces your_database_name | head -n 20

On MariaDB 11 and newer, replace mysqldump with mariadb-dump.

Several databases? One credentials file per database user: .my-backup-shop.cnf, .my-backup-blog.cnf, and so on. If one user can read several databases (common: one user per site, several databases per site), one file is enough: point several config files to it, each with its own DB_NAME.

Method B: mysql_config_editor - Oracle MySQL only

mysql_config_editor stores all your logins in ONE file, ~/.mylogin.cnf. Two things to know before choosing it:

It exists only with Oracle MySQL. MariaDB does not have it.
The file is obfuscated, not truly encrypted. The MySQL manual itself says a determined attacker with access to your account can read it. Its real benefit is the same as Method A: the password is not in your scripts. The file must be readable by you only, or MySQL ignores it.

Create one "login path" per database (you are asked for the password):

Code:

mysql_config_editor set --login-path=shop --host=your_database_server.example.com --user=shop_user --password
mysql_config_editor set --login-path=blog --host=your_database_server.example.com --user=blog_user --password

List them (passwords are hidden):

Code:

mysql_config_editor print --all

Change one (run set again with the same name) or delete one:

Code:

mysql_config_editor remove --login-path=blog

Then, in each config file, set DB_CNF_FILE='' and DB_LOGIN_PATH='shop'.

Step 4 - Edit backup.conf

All settings are grouped at the top of two files. Nothing to hunt for in the code.

backup.conf (one per database). Open it with your editor (ee backup.conf on FreeBSD, or nano backup.conf). The settings are in the box at the top; explanations are in the HELP section at the bottom.

Required: DB_NAME, DB_CNF_FILE (the file from Step 3) and EMAIL_TO.
Everything else can stay empty (''): the default value is used.
Recommended: ALERT_EMAIL. If your backups go to a folder you never read (spam filter, archive), put an address you DO read.
DB_CHARSET: keep utf8mb4 for a modern database. If you used another value for years (latin1 was common with old PHP sites), keep your old value. Changing it can break accented characters.

db-backup.sh (shared by all your databases). The first lines hold a "DEFAULT SETTINGS" box: the values used whenever a backup.conf leaves a setting empty. Change a value there to change it for all your databases at once (for example the alert address, or where the passphrase file is). Do not change anything under that box.

Step 5 - First run, by hand

Code:

~/db-backup/db-backup.sh ~/db-backup/backup.conf
echo $?

0 means success. Then check:

Code:

cat ~/backups/db/backup.log

You should see backup verified, e-mail sent and done, and receive an e-mail with the encrypted file attached.

Then do Step 8 once with that e-mail, to be sure you can restore.

Step 6 - Run it every night with cron

Code:

crontab -e

Add these lines (example: every night at 3:17):

Code:

MAILTO=your_email@example.com
17 3 * * * /bin/sh $HOME/db-backup/db-backup.sh $HOME/db-backup/backup.conf

MAILTO makes cron e-mail you anything the script prints on errors, as a second safety net.

Online shop? The database changes with every order, so back it up more often. Every hour, at minute 10:

Code:

10 * * * * /bin/sh $HOME/db-backup/db-backup.sh $HOME/db-backup/backup.conf

Each backup e-mail is a full copy, so count: one backup per hour kept 30 days in your mailbox is 720 e-mails per database. Check your mailbox quota.

Several databases? One config file and one cron line per database:

Code:

17 3 * * * /bin/sh $HOME/db-backup/db-backup.sh $HOME/db-backup/shop.conf
27 3 * * * /bin/sh $HOME/db-backup/db-backup.sh $HOME/db-backup/blog.conf

Step 7 - Keeping a month of backups

The server only keeps KEEP_DAYS days of copies. Your mailbox is the real archive: for example, a mail filter that moves these e-mails into a folder and deletes them after 30 days gives you 30 days of history, without filling the server.

Big backups are cut into pieces, like big archives on floppy disks. A backup bigger than MAX_ATTACH_MB is sent as several e-mails, one piece each: "part 1 of 3", "part 2 of 3"... Each e-mail explains how to glue the pieces back together (Step 8.0).

An attachment makes the e-mail about 37% bigger than the file, so keep MAX_ATTACH_MB well under your provider's limit: 15 for Outlook/Hotmail (25 MB per e-mail), 35 for GMX (50 MB attachments).

MAX_PARTS (default 10) limits the number of e-mails per backup. Beyond that, the backup is kept on the server and you get an alert.

Remember: a backup in pieces needs ALL its pieces. If one e-mail is lost, that backup cannot be restored, so use the one before it.

Step 8 - Restore a backup

8.0 Only if the backup came in several e-mails

Save all the attachments with the same date and time (...part-aa, ...part-ab, ...) in the same folder.
Glue them back together. The exact command is in each e-mail; copy only the ONE line for your system: - Linux, FreeBSD or macOS: cat mydb_2026-11-03_03h17.sql.gz.enc.part-* > mydb_2026-11-03_03h17.sql.gz.enc - Windows (cmd.exe, not PowerShell): copy /b mydb_...part-aa + mydb_...part-ab + mydb_...part-ac mydb_2026-11-03_03h17.sql.gz.enc
Optional check: openssl dgst -sha256 mydb_2026-11-03_03h17.sql.gz.enc must print the code written in the e-mails.

Then continue with 8.1 as for a normal backup.

8.1 Decrypt

The exact command is printed in every backup e-mail. It looks like this:

Code:

openssl enc -d -aes-256-cbc -pbkdf2 -iter 600000 -md sha256 -in mydb_2026-11-03_03h17.sql.gz.enc -out mydb_2026-11-03_03h17.sql.gz

OpenSSL asks for your passphrase.

On Windows:

Install OpenSSL. Two easy choices: "Win64 OpenSSL Light" from slproweb.com, or Git for Windows (it includes openssl.exe).
Save the attachment, for example in your Downloads folder.
Open a terminal (Start menu, type cmd) and go to that folder: cd %USERPROFILE%\Downloads
Paste the command from the e-mail. If Windows says openssl is not found, type its full path instead, for example: "C:\Program Files\OpenSSL-Win64\bin\openssl.exe" enc -d ...
Type your passphrase.

8.2 Decompress

Windows: open the .sql.gz file with 7-Zip and extract the .sql file.
Unix: gunzip mydb_2026-11-03_03h17.sql.gz

8.3 Put the data back

Warning: this replaces the current content of the database.

Code:

mysql --defaults-file=$HOME/.my-backup-your_database_name.cnf your_database_name < mydb_2026-11-03_03h17.sql

(mariadb instead of mysql on MariaDB 11+.) You can also import the .sql file with phpMyAdmin or Adminer.

Note: dumps made by recent mariadb-dump start with a special "sandbox" line. It imports fine into MariaDB, but an Oracle MySQL client refuses it. To move data from MariaDB to MySQL, delete that first line.

Coming from the 2011 or 2018 version of this script?

Backups made by the old script (still in your mailbox) decrypt with: openssl enc -d -aes-256-cbc -pbkdf2 -in OLD_FILE.sql.gz.ENC -out OLD_FILE.sql.gz (no -iter: the old script used OpenSSL's default of 10000).
Remove the database password and the encryption passphrase from the old script once the new one works. They should exist only in the files of Steps 2 and 3.
The sent / sent.lock lines are gone: the new script tells mutt not to keep a copy of sent mail (set copy=no), which was the cause of that growing file.
mailmessage.txt is no longer needed: the e-mail text is generated, with the exact decrypt command and the real file name.

What changed in 2026, and why

Problem in older versions	2026 fix
Passphrase on the command line (`-k` / `-pass pass:`), visible to other users with `ps`	Read from a file only you can read (`-pass file:`)
Unencrypted `.sql.gz` left on the server	Dump, compression and encryption in one stream; only the encrypted file touches the disk
A failed or half-finished dump was still e-mailed as if all was fine	Exit code checked, and the encrypted file is decrypted again to confirm the dump ends with `-- Dump completed`
Silent failures	Alert e-mail plus a log line, and cron's `MAILTO` as a backup
10000 PBKDF2 iterations (OpenSSL default)	600000 iterations, the current OWASP figure for PBKDF2-HMAC-SHA256
`:` in file names, which Windows refuses	`2026-11-03_03h17`
`#!/bin/bash` (the 2024 draft): bash is not at that path on FreeBSD	Plain `/bin/sh`, tested with dash and bash
`mysqldump` only, and `mysql_config_editor` only	Works with MySQL and MariaDB (`mariadb-dump` auto-detected); credentials in an option file that both understand
`PROCESS privilege` error with MySQL 8.0.21+ on shared hosting	`--no-tablespaces`
Two runs at the same time could mix files	Lock folder per database

Good habits

Once every few months, restore one backup into a test database. A backup you have never restored is a hope, not a backup.
Keep your database server up to date. Oracle MySQL 8.0 stopped receiving security fixes at the end of April 2026; the supported long-term version is 8.4. If you are on a managed or shared server, ask your host which version you run.
If you change the database password, update the file from Step 3.

Declaration of Variables

Sarouel

J65nko

Sarouel

SirDice

Administrator

Sarouel

SirDice

Administrator

Sarouel

SirDice

Administrator

Sarouel

Sarouel

Sarouel

cracauer@

ralphbsz

bakul

SirDice

Administrator

cracauer@

sic_101

Unix / Linux - Shell Quoting Mechanisms

bakul

skhal

mer

Espionage724

Sarouel

Sarouel