MySQL backup script with email support and lzma compression (for cron)

Let me share a shell script to perform a backup of a MySQL database. It is intended to run regularly (e.g. as cronjob). I created it carefully. Finally, it may distinguish itself from similar scripts by the following points:

It stores the backup file locally. Additionally, it sends an email with the backup file via any SMTP server you like (localhost:25 by default). STARTTLS/SSL is supported. Therefore, I use mailsend.py (an SMTP-based email sender I’ve written in Python). If mysqldump does not exit with return code 0, a special email is sent. mysqldump‘s stderr output is always fetched and sent via mail.

It does not use your database password directly in the mysqldump command (in which case other users would be able to see the your password in the process list while the command is running). This mistake seems to be done often.

It uses lzma compression, which produces higher compression ratios than gzip/bz2 (at the cost of computing power).

It automatically deletes local backup files older than a user-given number of days.

The script:

```
#!/bin/sh
```
```
 
```

# database backup settings. edit these accordingly.

```
EMAIL="user@host.com"
```
```
BACKUP_NAME="FORUM_DB_BACKUP"
```
```
BACKUP_DIR="/path/to/backup/dir"
```
```
DB_NAME="dbname"
```
```
DB_HOST="127.0.0.1"
```
```
DB_USER="username"
```

DB_PASSWORDFILE="/path_to/.db_password_file"

```
DEL_OLDER_THAN_X_DAYS="30"
```
```
 
```
```
if [[ ! -d ${BACKUP_DIR} ]]; then
```
```
    mkdir ${BACKUP_DIR}
```
```
fi
```

BACKUP_PATH=${BACKUP_DIR}/$(date +%Y%m%d-%H%M%S)_${BACKUP_NAME}.sql.lzma

```
 
```

# dump database content to stdout. read db password from file (never put it

# on the commandline directly, as other users may read it in the process

# list). forward both stdout and stderr to lzma, which is more efficient

# than gzip/bz2. store the data in a file with a meaningful name.

```
mysqldump \
```
```
    --user=${DB_USER} \
```

    --password=$(cat ${DB_PASSWORDFILE}) \

```
    --host=${DB_HOST} \
```
```
    --opt --quote-names \
```
```
    ${DB_NAME} \
```
```
    |& lzma -c > \
```
```
    ${BACKUP_PATH}
```
```
 
```

# send the backup file to your email address using the SMTP server listening

# on localhost:25. if you wish to use another SMTP server (localhost:465 or

# smtp.gmail.com:587 or whatever), see mailsend.py --help.

# note: PIPESTATUS may not work within other shells than bash.

```
if [[ ${PIPESTATUS[0]} -eq 0 ]]; then
```

    python mailsend.py -s ${BACKUP_NAME} -f ${BACKUP_PATH} ${EMAIL}

```
else
```

    python mailsend.py -s ${BACKUP_NAME}_ERROR -f ${BACKUP_PATH} \

    -m "mysqldump threw an error. It's stderr is within the attached archive." \

```
    ${EMAIL}
```
```
fi
```
```
 
```

# delete backup files older than X days (as set above)

```
find \
```
```
    ${BACKUP_DIR} \
```
```
    -name \*.sql.lzma \
```
```
    -mtime +${DEL_OLDER_THAN_X_DAYS} \
```
```
    -exec rm -f '{}' \;
```

How to use it:

Put the lines above in a file on your server and make it executable.
Create the password file containing your database password in clear text (apply chmod 600 to the file!).
Adjust the settings within the header of the script.
If you like the email feature, store mailsend.py (download link below) in the same directory as the backup script (or adjust the paths). If you don’t like the email feature, outcomment the corresponding lines 35 to 41.
If your server does not know the lzma command, replace it with gzip (line 28).
Configure your new backup script as cronjob.

Mailsend.py:

I will publish a blog post introducing mailsend.py soon. This is what I can provide until then:
View the help: here
View the code: here

Download it:
mailsend_2010_11_16.zip
mailsend_2010_11_16.tar.gz

Jan-Philip Gehrcke, PhD

MySQL backup script with email support and lzma compression (for cron)

The script:

How to use it:

Mailsend.py:

Leave a Reply Cancel reply