backupMariaDb.sh

#!/bin/bash

# Constants
MYSQLDUMP="mysqldump --defaults-extra-file='/etc/mysql/root.cnf'"
MYSQL="mysql --defaults-extra-file='/etc/mysql/root.cnf'"

# Define color codes
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
NC='\033[0m' # No Color

fetch_databases() {
  eval "$MYSQL -e \"SHOW DATABASES;\"" | tail -n +2
}

# Check if the database name is provided
if [ -z "$1" ]; then
  echo -e "${YELLOW}Usage: $0 <database-name>${NC}"
  echo -e "${YELLOW}Available DBs:${NC}"
  fetch_databases
  exit 1
fi

DATABASE_NAME=$1
CURRENT_DATETIME=$(date +%Y%m%d_%H%M%S)

# Define the backup directory
BACKUP_DIR="/root/backups/mariadb"

# Define the filename for the SQL dump
FILENAME="${DATABASE_NAME}_backup_${CURRENT_DATETIME}.sql"

# Full path to the SQL dump file
BACKUP_PATH="${BACKUP_DIR}/${FILENAME}"

# Estimate the size of the database for the progress bar
DB_SIZE=$(eval "$MYSQL -e \"SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024, 0) 'DB Size in MB' FROM information_schema.tables WHERE table_schema='$DATABASE_NAME';\"" | tail -n 1)


# Check if the DB_SIZE is a number
if ! [[ $DB_SIZE =~ ^[0-9]+(\.[0-9]+)?$ ]]; then
  echo -e "${RED}Failed to retrieve database size.${NC}"
  exit 1
fi
# Run the mysqldump command to create the SQL dump
eval "$MYSQLDUMP $DATABASE_NAME" | pv -s ${DB_SIZE}M > $BACKUP_PATH

# Check if the mysqldump command was successful
if [ $? -eq 0 ]; then
  BACKUP_SIZE=$(du -sh "$BACKUP_PATH" | cut -f1)
  echo -e "${GREEN}Database backup successful: ${BACKUP_PATH}${NC}"
  echo -e "${GREEN}Backup size: ${BACKUP_SIZE}${NC}"
  echo -e "${GREEN}First 10 rows of the backup file:${NC}"
  head -n 10 "$BACKUP_PATH"
else
  echo -e "${RED}Database backup failed${NC}"
  exit 1
fi