Regularly backup mysql

backup.sh

#!/bin/bash

# Configs

MY_HOST='localhost'  # the host of this script

BACKUP_MATRIX=(
    "localhost;userName;password;dbName"
)

#############################################################################################

SCRIPT_ROOT="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"
 
dateSuffix=`date +%Y-%m-%d`
year=`date +%Y`
month=`date +%m`
day=`date +%d`
timeSuffix=`date +%H-%M-%S`

BACKUP_CONTAINER=$SCRIPT_ROOT/${year}_${month}/Dump_${dateSuffix}_${timeSuffix}


log() {
    echo "[`date`] $1"
}

backup() {
    HOST=$1; USER=$2; PASS=$3; DB_TABLE=$4;
    
    log "Dumping data in ${USER}@${HOST} with database ${DB_TABLE}"

    # Sometimes it may fail to lock tables, add '--skip-lock-tables' to fix it
    MYSQL_PWD=${PASS} mysqldump -h "${HOST}" -u "${USER}" --routines "${DB_TABLE}" > "${BACKUP_CONTAINER}/${DB_TABLE} (${USER}@${HOST}).sql"
}

if [ "$1" == "backup" ]; then
    log "Start"

    # create folder if not exists
    log "Creating folder at $BACKUP_CONTAINER ..."
    [ -d "$BACKUP_CONTAINER" ] || mkdir -p "$BACKUP_CONTAINER"

    # loop
    for var in "${BACKUP_MATRIX[@]}"
    do
        old_ifs=$IFS
        IFS=";"
        set $var
        backup "$1" "$2" "$3" "$4"
        IFS=$old_ifs
    done

    log "Finish"
elif [ "$1" == "show" ]; then
    echo "Going to show *** passwords ***"
    echo -n "(Ctrl-C to exit)"
    read $1

    for var in "${BACKUP_MATRIX[@]}"
    do
        old_ifs=$IFS
        IFS=";"
        set $var
        echo "Run on $1 ..."
        echo "  CREATE USER '$2'@'$MY_HOST' IDENTIFIED BY '$3';"
        echo "  GRANT select, lock tables ON \`$4\`.* TO '$2'@'$MY_HOST';"
        echo "  FLUSH PRIVILEGES;"
        echo ""
        IFS=$old_ifs
    done
else
    echo "Usage: $0 [backup|show]"
fi

crontab

0 23 * * * /home/user/Backup/backup.sh backup >> /home/user/Backup/run.log 2>&1