sanfx · February 19, 2022 15:20 · Feb 19, 2022
diff --git a/dbbackup.sh b/dbbackup.sh
@@ -0,0 +1,41 @@
+#!/bin/bash
+
+# in case the mysql db is located in docker container.
+append_cli=$4
+# For taking backup
+backup_dir=/media/storage/backup/db_backup/
+datestamp=$(date +%d-%m-%y-%H-%M)
+db_user=backup
+db_port=$2
+db_pass=$3
+host=$1
+
+die() {
+    echo >&2 "$@"
+    exit 1
+}
+
+[ "$(id -u)" = 0 ] || die "Must be root to run script"
+[ -n "$host" ] || die "host not passed."
+[ -n "$db_port" ] || die "PORT not passed."
+
+# remove backups older than $days_keep
+days_keep=7
+find ${backup_dir}* -mtime +$days_keep -exec rm -f {} \; 2> /dev/null
+# create backups securely
+umask 006
+# list MySQL databases and dump each
+IFS=$'\n' read -d '' -r -a db_list \
+ < <($append_cli mysql -h $host -u $db_user -p"$db_pass" --batch --skip-column-names -e'show databases where `Database` not REGEXP "^mysql|^sys" AND `Database` not like "%_schema";')
+
+echo "Listing databases"
+echo ${db_list##Database}
+
+for db in ${db_list[@]};
+
+do
+ FILENAME=${backup_dir}${db}-${datestamp}.${db_port}.gz
+ echo "Initiating backup of $DB for ${host} on port ${db_port}"
+ $append_cli mysqldump -h $host -P $db_port -u $db_user -p"$db_pass" $db --single-transaction | gzip > $FILENAME
+ echo "Done backing up ${FILENAME}"
+done