Backup mysql databases and compress with LZMA, bash script

Tuesday, 27 July 2010 15:39

Bash Script to backup mysql and compress with LZMA
Compress with LZMA mode 2, fastest mode, low cpu, low ram.

Final backup will look like:
backup/mysql/YEAR/MONTH/{DB_NAME}_YYYMMMDDD_HHMMSS.sql.lzma
Example:
/backup/mysql/2010/07/mail_20100727_163027.sql.lzma





#!/bin/bash

# Script to backup mysql

# Compress with LZMA mode 2, fastest mode, low cpu, low ram. 

# writen by Frederico Araujo 

# http://frederico-araujo.com

# 

# Final backup will look like:

# /backup/mysql/YEAR/MONTH/{DB_NAME}_YYYMMMDDD_HHMMSS.sql.lzma

# Example:

# /backup/mysql/2010/07/mail_20100727_163027.sql.lzma



# Mysql Settings

DB_USER="root"

DB_PASSWD="password"



# List of Databases to backup:

DATABASES="mydb mydb1 mydb2"



# Nice Level. Try to use less resources

NICE=17



# Backup Folder

BACKUP_DIR="/backup/mysql"



NOW="$(date +"%Y%m%d_%H%M%S")"

DATE_FOLDER="$(date +"%Y/%m")"

FINAL_FOLDER="${BACKUP_DIR}/${DATE_FOLDER}"

mkdir -p $FINAL_FOLDER





for DB in $DATABASES

do

    SQL="${FINAL_FOLDER}/${DB}_${NOW}.sql"

    nice -n $NICE /usr/bin/mysqldump -u$DB_USER -p$DB_PASSWD $DB > "${SQL}"

    nice -n $NICE /usr/bin/lzma -2 -z "${SQL}"

    chmod 600 "${SQL}.lzma"

    echo "Creating backup of: ${DB}"

done



echo "Done"

exit 0