Skip to content

Instantly share code, notes, and snippets.

@MaaxGr
Created May 20, 2024 14:26
Show Gist options
  • Save MaaxGr/b0ed9675a9d581518543bd70a6b514ac to your computer and use it in GitHub Desktop.
Save MaaxGr/b0ed9675a9d581518543bd70a6b514ac to your computer and use it in GitHub Desktop.
#!/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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment