Maria/MySql DB Backup Scripts - Use at your will

Community Support Backups
1

Just wanted to share this with whomever could use it. I use backup solution that connects to my hestia server for file level backup. These scripts can be scheduled to dump the mysql databases to a folder “db-backup” within the user directory so that they can be backed up by a file only backup solution.

FYI there is no code to check if a user or database is not to be backed up - it will dump ALL databases. It keeps the last 7 days worth of sql dumps (which are really tiny).

Please feel free to adjust as you’d like!

These live in my /usr/local/hestia/bin/ folder. Sorry - no github location yet. Will get there. The m-backup-users-databases loops all users to call m-backup-user-databases which does the backup of a single user.

File: m-backup-user-databases

-------------------------------------------------------------------------------------------
#!/bin/bash
# info: backup system user with all its objects
# options: USER NOTIFY
#
# The call is used for backing up user with all its domains and databases.

#----------------------------------------------------------#
#                    Variable&Function                     #
#----------------------------------------------------------#

# Importing system variables
source /etc/profile

# Argument definition
user=$1

# Includes
source $HESTIA/func/main.sh
source $HESTIA/func/domain.sh
source $HESTIA/func/db.sh
source $HESTIA/conf/hestia.conf

#----------------------------------------------------------#
#                    Verifications                         #
#----------------------------------------------------------#

check_args '1' "$#" 'USER [NOTIFY]'
is_format_valid 'user'
is_system_enabled "$BACKUP_SYSTEM" 'BACKUP_SYSTEM'
is_object_valid 'user' 'USER' "$user"

#----------------------------------------------------------#
#                       Action                             #
#----------------------------------------------------------#

# Set backup directory if undefined
today=`date '+%Y%m%d_%H%M%S'`;
BACKUP=/$HOMEDIR/$user/db-backup
mkdir -p $BACKUP

# Get current time
start_time=$(date '+%s')

# Databases
echo -e "\n-- DB --" |tee -a $BACKUP/$user.log

# Parsing database exclusions
for database in $(search_objects 'db' 'SUSPENDED' "*" 'DB'); do
	exclusion=$(echo "$DB" |tr ',' '\n' |grep "^$database$")
	db_list="$db_list $database"
done

i=0
conf="$USER_DATA/db.conf"
db_list=$(echo "$db_list" |sed -e "s/  */\ /g" -e "s/^ //")
for database in $db_list; do
	check_backup_conditions
	((i ++))
	get_database_values

	echo -e "$(date "+%F %T") $database ($TYPE)" |tee -a $BACKUP/$user$today.log
	mkdir -p $BACKUP/$database/conf
	mkdir -p $BACKUP/$database/hestia

	cd $BACKUP/$database/
	grep "DB='$database'" $conf > hestia/db.conf

	dump="$BACKUP/$database/$database.$TYPE.$today.sql"
	dumpgz="$BACKUP/$database/$database.$TYPE.$today.sql.gz"
	grants="$$BACKUP/$database/conf/$database.$TYPE.$DBUSER"
	
	WAIT_LOOP_ENTERED=0
	while true
	do
		if pgrep -x "mysqldump" > /dev/null
		then
			WAIT_LOOP_ENTERED=1
			echo "Wait other mysqldump to finish"
			sleep 1
		else
			if [ "$WAIT_LOOP_ENTERED" -eq 1 ]; then
				echo "We can use mysqldump now"
			fi
			break
		fi
	done

	case $TYPE in
		mysql) dump_mysql_database ;;
		pgsql) dump_pgsql_database ;;
	esac

	# Compress dump
	gzip -$BACKUP_GZIP $dump

done

# Delete older than 7 days
find $BACKUP -name "*.gz" -type f -mtime +7 -exec rm -f {} \;

# Print total
if [ "$i" -eq 1 ]; then
	echo -e "$(date "+%F %T") *** $i database ***" |\
		tee -a $BACKUP/$user$today.log
else
	echo -e "$(date "+%F %T") *** $i databases ***"|\
		tee -a $BACKUP/$user$today.log
fi

# Calculation run time
end_time=$(date '+%s')
run_time=$((end_time - start_time))
run_time=$((run_time / 60))
current_time=$(date "+%T")
if [ "$run_time" -lt 1 ]; then
    run_time=1
fi
min=minutes
if [ "$run_time" -eq 1 ]; then
    min=minute
fi

echo "$(date "+%F %T") Size: $size MB" |tee -a $BACKUP/$user$today.log
echo "$(date "+%F %T") Runtime: $run_time $min" |tee -a $BACKUP/$user$today.log
-------------------------------------------------------------------------------------------

File: m-backup-users-databases

-------------------------------------------------------------------------------------------

#!/bin/bash
# info: backup all databases of all users
# options: NONE
#
# The function backups all system users.

#----------------------------------------------------------#
#                    Variable&Function                     #
#----------------------------------------------------------#

# Importing system environment  as we run this script
# mostly by cron which not read it by itself
source /etc/profile

# Includes
source $HESTIA/func/main.sh
source $HESTIA/conf/hestia.conf

#----------------------------------------------------------#
#                       Action                             #
#----------------------------------------------------------#

# Auto-repair all databases before backuping all accounts
mysqlrepair --all-databases --check --auto-repair > /dev/null 2>&1

for user in $($HESTIA/bin/v-list-sys-users plain); do
    log=$HESTIA/log/backup.log
    echo -e "================================" >> $log
    echo -e "$user" >> $log
    echo -e "--------------------------------\n" >> $log
    nice -n 19 ionice -c2 -n7 $BIN/m-backup-user-databases $user >> $log 2>&1
    echo -e "\n--------------------------------\n\n" >> $log
done

#----------------------------------------------------------#
#                       Hestia                             #
#----------------------------------------------------------#

# No Logging
#log_event "$OK" "$ARGUMENTS"

exit
2

please try to use the code function when posting something like that… I tried to correct it, but even that is difficult, esp. if it’s mutliple scripts :wink:

you could also just use pastebin or the like to put up your code. thanks for sharing anyway!
(I did not check what it’s doing, so everyone use at their own risk ^^)

if you are looking for readymade solution for mysql which even autorotates I suggest looking into AutoMySQLBackup :wink:

4 Likes