Automatisk backup database

Ved bruk av mariadb

..

Ved error 2002, reset tillatelser på lib folder.

chown -R mysql:mysql /var/lib/mysql
find /var/lib/mysql/* -type d -exec chmod 0700 {} \;
find /var/lib/mysql/* -type f -exec chmod 0660 {} \;
chmod 755 /var/lib/mysql

Ved bruk av mysqldump

For å kjøre backup hver mandag klokka 04:00, legg til følgende linje:

0 4 * * 0 bash ./backup_db.sh > /dev/null 2>&1

backup_db.sh:

#!/bin/sh

##### SYSTEM SETUP
BACKUPDIR=/var/www/database_backups

##### MYSQL SETUP
MYSQL_HOST="localhost"
MYSQL_USER="root"
MYSQL_PASS=""

##### DATABASER SOM SKAL TA BACKUP AV
DBARRAY=("db1" "db2" "db3")

##### BINARIES
TAR="$(which tar)"
GZIP="$(which gzip)"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"

##### COLORS
LF="\n"; CR="\r"
INVT="\033[7m"; NORM="\033[0m"; BOLD="\033[1m"; BLINK="\033[5m"
#UNDR="\033[2m\033[4m"; EOL="\033[0K"; EOD="\033[0J"
UNDR="\033[4m"; EOL="\033[0K"; EOD="\033[0J"
SOD="\033[1;1f"; CUR_UP="\033[1A"; CUR_DN="\033[1B"; CUR_LEFT="\033[1D"
CUR_RIGHT="\033[1C"
##--##
#-- ANSI code
SCR_HOME="\033[0;0H" #-- Home of the display
##--##
BLACK_F="\033[30m"; BLACK_B="\033[40m"
RED_F="\033[31m"; RED_B="\033[41m"
GREEN_F="\033[32m"; GREEN_B="\033[42m"
YELLOW_F="\033[33m"; YELLOW_B="\033[43m"
BLUE_F="\033[34m"; BLUE_B="\033[44m"
MAGENTA_F="\033[35m"; MAGENTA_B="\033[45m"
CYAN_F="\033[36m"; CYAN_B="\033[46m"
WHITE_F="\033[37m"; WHITE_B="\033[47m"

DAY=`/bin/date +%d.%b-%Y\|%H-%M-%S`

in_array() {
    local hay needle=$1
    shift
    for hay; do
        [[ $hay == $needle ]] && return 0
    done
    return 1
}

echo -e "${BOLD}Starter backup av databaser...${NORM}"

echo ""

DIR1=$BACKUPDIR/$DAY
mkdir $DIR1

DBS="$($MYSQL -u $MYSQL_USER -h $MYSQL_HOST -p$MYSQL_PASS -Bse 'show databases')"
for db in $DBS
do
    
    TEST=`in_array "$db" "${DBARRAY[@]}" && echo yes || echo no`

    if [ "$TEST" == "yes" ]; then

        DIRDB=$DIR1/$db
        if [ ! -d "$DIRDB" ]; then
            mkdir $DIRDB
        fi
        
        echo -e "${GREEN_F}Tar backup av:${NORM} ${BOLD}"$db"...${NORM}"
            FILE=$DIRDB/$db.sql.gz
            $MYSQLDUMP -u$MYSQL_USER -p$MYSQL_PASS $db --add-drop-database | $GZIP -9 > $FILE
            echo -e "[FERDIG] ${MAGNETA_F}Backup av ${NORM} ${BOLD}"$db"\n${NORM}"
    
    fi

done

cd $BACKUPDIR

CMPFILE=$DAY.tar.gz
$TAR -cvf $CMPFILE $DAY/ >/dev/null 2>&1

rm -rf $DIR1

DAY2=`/bin/date +%d.%b-%Y\|%H-%M-%S`
echo ""
echo -e "${WHITE_B}${BLACK_F}${BOLD}Backup startet: $DAY${NORM}"
echo -e "${WHITE_B}${BLACK_F}${BOLD}Backup ferdig: $DAY2${NORM}"