SQL Backups0rz

backup.sh

#!/bin/bash

mysql_user="root"
mysql_password="ROOT_PASSWORD"

backup_dir=/home/you/backups

mysqldump_options="--skip-opt --add-drop-database --add-drop-table --add-locks --compact --complete-insert --create-options --disable-keys --events --extended-insert --flush-logs --flush-privileges --lock-tables --quick --quote-names --routines --set-charset --single-transaction --triggers"

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

stamp=$(date +%s)
temp="/tmp/backup_temp"
touch "$temp"

mkdir $backup_dir/$stamp

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

echo -ne "Enumerating databases ... "
databases=( $(mysql --user=$mysql_user --password=$mysql_password --skip-column-names --batch -e "show databases;" 2>"$temp") );
echo "found ${#databases[@]}.";

tables=0

echo -ne "Backing up databases ... "
for i in ${databases[@]}; do
        if [ $i != "performance_schema" ] && [ $i != "information_schema" ] && [ $i != "phpmyadmin" ] && [ $i != "test" ]; then
                echo -ne "$i "
                mysqldump --user=$mysql_user --password=$mysql_password $mysqldump_options --databases $i | pigz > "$backup_dir/$stamp/$i.sql.gz"
        fi
done
echo "Done"

crontab

0 */4 * * * sudo /your/path/backup.sh > /dev/null

Prerequisites

apt-get install pigz