Backup MySQL using mysqldump script

It’s a simple script to backup MySQL db using mysqldump.

#!/bin/bash

if [ $# -lt 3 ]; then
cat <<EOF
  Backup MySQL DB using mysqldump

  usage:  backup_mysql_by_dump.sh <BACKUP_DB> <BACKUP_DIR> <BACKUP_KEEP_DAYS>
  examples:      
          backup_mysql_by_dump.sh cacti /data/cacti/mysql_backup/dump 7       --backup cacti db, and keep 7 days
          backup_mysql_by_dump.sh all /data/cacti/mysql_backup/dump 3         --backup all db, and keep 3 days

EOF
  exit 1
fi

# arguments
BACKUP_DB="$1"
BACKUP_DIR="$2"
BACKUP_KEEP_DAYS="$3"

# configuration
MYSQL_USER="cacti"
MYSQL_PASS="cacti"
MYSQL_HOST="localhost"
MYSQL_HOME="/usr"
DB_TYPE="InnoDB"
MAIL_TO="alexzeng@wordpress.com"

# prepare backup
DATE=`date +"%Y.%m.%d"`
BACKUP_FILE="$BACKUP_DIR/$BACKUP_DB.$DATE.sql.gz"
BACKUP_CMD="$MYSQL_HOME/bin/mysqldump"
BACKUP_CMD="$BACKUP_CMD -u $MYSQL_USER -p$MYSQL_PASS -h $MYSQL_HOST "
BACKUP_CMD="$BACKUP_CMD --routines --events "

if [ $DB_TYPE = "InnoDB" ]; then
  BACKUP_CMD="$BACKUP_CMD --single-transaction"
else
  BACKUP_CMD="$BACKUP_CMD --lock-tables FALSE"
fi

BACKUP_DB=`echo $BACKUP_DB | awk '{print tolower($0)}'`
if [ $BACKUP_DB = "all" ]; then
  BACKUP_CMD="$BACKUP_CMD --all-databases"
else
  BACKUP_CMD="$BACKUP_CMD $BACKUP_DB"
fi

if [ ! -d "$BACKUP_DIR" ]; then
    echo "Backup dir $BACKUP_DIR does not exist"
    exit 1
fi

# backup
$BACKUP_CMD | gzip > "$BACKUP_FILE" 
if [ $? -ne 0 ]; then
  mailx -s "Backup mysql cron job $0 failed" $MAIL_TO <<EOF
Backup files list :
`ls -lt $BACKUP_DIR/$BACKUP_DB.*.sql.gz`
EOF
fi

#delete old files
cd $BACKUP_DIR
for backup in `find . -ctime +$BACKUP_KEEP_DAYS -name "$BACKUP_DB.*.sql.gz"`; do rm -f $backup; done;
Advertisements

About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

One Response to Backup MySQL using mysqldump script

  1. Raist8 says:

    Nice script!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: