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.