#!/bin/bash
#
# Script: mysql_backup
#
# Usage: mysql_backup
#
# Description: A tool for backing up MySQL/MariaDB databases
#
# Author: Nikita Shchetinin
#
# Changelog:
# * 06.03.2021: Nikita Shchetinin
# - Initial write
# * 28.10.2022: Nikita Shchetinin
# - Disk space check fix
# * 01.06.2023: Nikita Shchetinin
# - Moved to using pigz with a lower compression level
# * 07.28.2026: Jerimiah
# - Switched to per-table dumps instead of per-database dumps

if [ "$(whoami)" != 'root' ]
then
	echo "You must execute this script as the root user!"
	exit 1
fi

#==============================================================================
# SETTINGS
#==============================================================================

# Backup directory
directory=/backup/mysql

# Databases that should not be backed up (databases with names that start with "mysql" or end with "_schema" will not be backed up)
skip="(^mysql|_schema$)"

# The number of days to keep backups for
retain=7

# Timestamp format (YYYY-MM-DD_HH-MM-SS)
timestamp=$(date +%F_%H-%M-%S)

# Tables that failed to back up, collected during the run
failed_tables=""

#==============================================================================
# FUNCTIONS
#==============================================================================

DELETE() {
echo "Deleting backups that are older than $retain days from $directory..."
find $directory -type f -name "*.sql.gz" -mtime +$retain -delete
}

CHECK_SPACE() {
local available_size=$(df /backup/mysql | tail -1 | awk '{ print $4 "000"}')
local dump_size=$(mysqldump --all-databases | pigz -4 | wc -c)
if [ "$dump_size" -gt "$available_size" ]
then
	echo "The dumps would occupy all the space on the server, so they may not be created!"
	exit 1
fi
}

LIST_TABLES() {
local list_tables_sql="SELECT CONCAT(TABLE_SCHEMA,'.',TABLE_NAME) FROM information_schema.tables WHERE TABLE_SCHEMA NOT REGEXP '$skip' AND TABLE_TYPE IN ('BASE TABLE', 'VIEW')"
echo $(mysql -N -e "$list_tables_sql")
}

STATUS(){
printf '\r'
printf ' %0.s' {0..100}
printf '\r'
printf "$1"'\r'
}

BACKUP_TABLE(){
mkdir -p "$directory/$database"
backup_file="$directory/$database/$timestamp.$database.$table.sql.gz"
STATUS "...backing up $count of $total tables: $database.$table"
mysqldump $database $table | pigz -4 > "$backup_file"
local pipe_status=("${PIPESTATUS[@]}")
if [ "${pipe_status[0]}" -ne 0 ] || [ "${pipe_status[1]}" -ne 0 ]
then
	failed_tables+="$database.$table\n"
else
	output+="$database.$table => $backup_file\n"
fi
}

BACKUP_DBS(){
local items=$(LIST_TABLES)
local total=$(echo $items | wc -w | xargs)
local output=""
local count=1
for item in $items
do
	database="${item%%.*}"
	table="${item#*.}"
	BACKUP_TABLE
	local count=$((count+1))
done
echo -ne $output | column -t
}

HR(){
printf '=%.0s' {1..100}
printf "\n"
}

#==============================================================================
# RUN
#==============================================================================

DELETE
CHECK_SPACE
HR
BACKUP_DBS
HR
if [ -n "$failed_tables" ]
then
	echo -e "The following tables failed to back up:\n$failed_tables"
	exit 1
fi
echo "The backups are created"