A backup script for humans

mysql_db_backup.sh

#!/bin/bash

DB_NAME=$1
DB_HOST=$2
DB_USER="root"
DB_PASS="root"

BK_FOLDER="/tmp/backup"

BK_FILE_NAME="Dump$(date +%Y%m%d)"
BK_FILE_EXT="tar.gz"

MYSQL=/usr/lib/mysql-workbench/mysql
MYSQLDUMP=/usr/lib/mysql-workbench/mysqldump
COMPRESS="tar --create --gzip --file"

idx=0
while [ -d "${BK_FOLDER}/${BK_FILE_NAME}${suffix}.${BK_FILE_EXT}" ]; do
	suffix="_$IDX"
	idx=$(( $IDX+1 ))
done;

BK_DEST="${BK_FOLDER}/${BK_FILE_NAME}${suffix}.${BK_FILE_EXT}"
TMP_DIR=$(mktemp --directory)

cd $TMP_DIR

TABLES="$($MYSQL \
	--host=$DB_HOST \
	--database=$DB_NAME \
	--user=$DB_USER \
	--password=$DB_PASS \
	--batch \
	--silent \
	--execute 'SHOW TABLES')"

for table in $TABLES; do
	$MYSQLDUMP > "${DB_NAME}_${table}.sql" \
		--host=$DB_HOST \
		--user=$DB_USER \
		--password=$DB_PASS \
		--create-options \
		--quote-names \
		--lock-tables \
		--dump-date \
		--disable-keys \
		--tz-utc \
		--add-locks \
		--extended-insert \
		--add-drop-table \
		--comments \
		--force \
		"$DB_NAME" \
		"$table"
done

$COMPRESS $BK_DEST ./ && rm --recursive --force $TMP_DIR