Daily automatic database download

Community Support Database
5

Here’s a script I run (as root) on some of my servers once a day. It loops through the users and creates dumps of all their databases into /home/user/dbbackup. Your mileage may vary. Read and understand before using. I claim diplomatic immunity if it breaks your server (but it probably won’t)

#!/bin/bash
# script needs these to use Hestia Tools
export HESTIA=/usr/local/hestia
export PATH=$PATH:/usr/local/hestia/bin

# Loop through  Users
for USER in $( /usr/local/hestia/bin/v-list-users plain | awk '{print $1}' )
do
	DIR="/home/$USER/dbbackup"
	echo "= Processing $USER "
        # Make dir if this is the first time
	if [ ! -d "$DIR" ] ; then
		echo "Creating dir"
		mkdir -p "$DIR"
		chown -f "$USER":"$USER" "$DIR"
	fi

	# Loop through hestia DBs for that user
	for DB in  $( /usr/local/hestia/bin/v-list-databases "$USER" plain | awk '{print $1}' )
	do
		# Dump DB
		/usr/bin/mysqldump --defaults-extra-file=/root/.my.cnf --quick --single-transaction --skip-add-locks "$DB" | gzip > "$DIR"/"$(date +%F-%H-%M)"_"$DB".tar.gz
		# Pause to give the server a break
		sleep 10
	done
	# fix perms (silently)
	chown -f "$USER":"$USER" "$DIR"/*.tar.gz
	# Keep only last three days' files locally. Older can be recovered from offsite backup
	echo "Deleting old backups in $DIR"
	find "$DIR" -name '*tar.gz' -mtime +3 -delete
done
1 Like