MySQL Database Backup

Here are is details on how to back up a MySQL database.

Needed:

Linux
MySQL client

Script:

CODE

#!/bin/bash

DUMP_ROOT_DIR="/DB_DUMPS/mysql/"
USERNAME=""
PASSWORD=""
SERVER=""
DATABASE=""
DATE=`/bin/date +%d-%m-%Y,%A`
WEEK_DAY=`/bin/date +%a`

DATE=`/bin/date +%Y-%m-%d`

# LOCAL BACKUP

export WEEK_DAY=`/bin/date +%u`
export DAY=`/bin/date +%d`
export MONTH_DAY=`/bin/date +%d-%m`


cd $DUMP_ROOT_DIR/daily/

# delete the old dump file
rm -f AG_dmp$WEEK_DAY.sql

# dump a new file
/usr/bin/mysqldump -u"$USERNAME" -p"$PASSWORD" -h "$SERVER" "$DATABASE" --no-tablespaces > "AG_dmp$WEEK_DAY.sql"

# delete the old tar file
rm -f AG_dmp$WEEK_DAY.sql.tar.gz

# create a new tar file based on the new dumo file
tar czf AG_dmp$WEEK_DAY.sql.tar.gz AG_dmp$WEEK_DAY.sql

# delete the new dump file
rm -f AG_dmp$WEEK_DAY.sql

#####################
## WEEK BACKUP  ##
#####################
if [ "$WEEK_DAY" == "0" ]; then # if today is sunday
echo 'DOING WEEK BACKUP'
cp $DUMP_ROOT_DIR/daily/database_dump_$WEEK_DAY.sql.tar.gz $DUMP_ROOT_DIR/weekly/database_dump_$DATE.sql.tar.gz
fi


#####################
##  MONTH BACKUP  ##
#####################
if [ "$DAY" == "01" ]; then # if today is the day 1 of any month
echo 'DOING MONTH BACKUP'
cp $DUMP_ROOT_DIR/daily/database_dump_$WEEK_DAY.sql.tar.gz $DUMP_ROOT_DIR/monthly/database_dump_$DATE.sql.tar.gz
fi


#####################
##  YEAR BACKUP   ##
#####################
if [ "$MONTH_DAY" == "01-01" ]; then # if today is january 1th
echo 'DOING YEAR BACKUP'
cp $DUMP_ROOT_DIR/daily/database_dump_$WEEK_DAY.sql.tar.gz $DUMP_ROOT_DIR/yearly/database_dump_$DATE.sql.tar.gz
fi

It’s needed to create the Linux FHS above:

CODE

mkdir -p $DUMP_ROOT_DIR/daily
mkdir -p $DUMP_ROOT_DIR/weekly
mkdir -p $DUMP_ROOT_DIR/monthly
mkdir -p $DUMP_ROOT_DIR/yearly

Configure it on the Automation > Local Program and create a local program that runs this script scheduled.