A simple bash script to automatically backup your MySql databases to git repositories.

mySqlAutoBackup.sh

#!/bin/bash

# © 2018 zekro Development
# contact[at]zekro.de | https://zekro.de

# MIT License

# Copyright (c) 2018 Ringo Hoffmann (zekro Development)

# Permission is hereby granted, free of charge, to any person obtaining a copy
# of this software and associated documentation files (the "Software"), to deal
# in the Software without restriction, including without limitation the rights
# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
# copies of the Software, and to permit persons to whom the Software is
# furnished to do so, subject to the following conditions:

# The above copyright notice and this permission notice shall be included in all
# copies or substantial portions of the Software.

# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
# SOFTWARE.

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

# COLOR CODES
_RED="\033[0;31m"
_CYAN="\033[0;36m"
_NC="\033[0m"

# STATICS
_CFG="config.cfg"


function logerr {
    printf "${_RED}ERROR${_NC} | $1\n"
}

function loginfo {
    printf "${_CYAN}INFO${_NC}  | $1\n"
}

function checkcmd {
    if [ "$(command -v $1)" == "" ]; then
        logerr "Command $1 does not exist or is not available for this user."
        exit 1
    fi
}

function checkconfig {
    if [ "$TIMER" == "" ]; then
        logerr "Config var TIMER is empty."
        exit 1
    fi

    if [ "$DB_ADDRESS" == "" ]; then
        logerr "Config var DB_ADDRESS is empty."
        exit 1
    fi

    if [ "$DB_USERNAME" == "" ]; then
        logerr "Config var DB_USERNAME is empty."
        exit 1
    fi

    if [ "$DB_PASSWORD" == "" ]; then
        logerr "Config var DB_PASSWORD is empty."
        exit 1
    fi

    if [ "$REPOS" == "" ]; then
        logerr "Config var REPOS is empty."
        exit 1
    fi
}

function makebackup {
    loginfo "Collecting MySql dump... This process can take up a while..."

    if ! [ -d ./.tmp ]; then
        mkdir ./.tmp
    fi

    databases=$(mysql -h $DB_ADDRESS -u $DB_USERNAME --password=$DB_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema)")

    for database in ${databases}; do
        loginfo "Dumping database ${database}..."
        mysqldump -h $DB_ADDRESS -u $DB_USERNAME \
            --password=$DB_PASSWORD $database > ./.tmp/$database.sql
    done

    for repo in ${REPOS[*]}; do
        IFS='/' read -ra reposplit <<< "$repo"
        lastindex=$(( ${#reposplit[*]} - 1 ))
        reponame=${reposplit[$lastindex]}
        if ! [ -d $reponame ]; then
            loginfo "${reponame} does not exists locally and will be cloned..."
            git clone $repo ./$reponame
        fi
        echo $PWD
        cp ./.tmp/. ./${reponame}/dumps -R
        cd ./${reponame}
        git add .
        git -c user.name="mySqlBackup" -c user.email="mySqlBackup" \
            commit -m "MySqlbackup: $(date +"%Y-%m-%d %H:%M:%S")"
        git push -u origin master
        cd ..
    done

    rm -f -r ./.tmp
}

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

# CHECK FOR MYSQLDUMP, GIT
checkcmd "mysqldump"
checkcmd "mysql"
checkcmd "git"

#--all-databases

# CHECK FOR CONFIG AND CREATE IF NOT EXISTENT
if ! [ -f $_CFG ]; then 
    echo "TIMER=86400  # Time period in seconds, defaulty one day (60s*60*24)" >> $_CFG
    echo "REPOS=()  # Enter the repositorys where the backups will go. This is an array seperated by spaces!" >> $_CFG
    echo "DB_ADDRESS="  >> $_CFG
    echo "DB_USERNAME=" >> $_CFG
    echo "DB_PASSWORD=" >> $_CFG
    logerr "${_CFG} missing and got created. Edit it and continue."
    exit 1
fi

# LOAD CONFIG
loginfo "Loading config..."
source $_CFG
checkconfig

mysql -h $DB_ADDRESS -u $DB_USERNAME \
    --password=$DB_PASSWORD -e "SHOW DATABASES;" \
    > /dev/null

loginfo "Successfully connected to database. Cedentials are correct."

if [ $TIMER == 0 ]; then
    loginfo "INITIATING BACKUP ONCE"
    loginfo "======================"
    makebackup
    exit 0
fi

while true; do
    loginfo "INITIATING BACKUP"
    loginfo "================="

    makebackup

    loginfo "==============="
    loginfo "BACKUP FINISHED"
    loginfo "==============="
    sleep $TIMER
done