Issue
When installing the DB Governor, it's highly recommended to back up all the MySQL data.
Environment
- MySQL Governor
- MySQL
Solution
Could be used before installing MySQL-governor and MySQL/MariaDB packages.
#!/usr/bin/env bash
RED='\033[0;31m' # Red Color
GR='\033[0;32m' # Green Color
YW='\033[0;33m' # Yellow Color
PR='\033[0;35m' # Purpule Color
CY='\033[0;36m' # Cyan Color
NC='\033[0m' # No Color
set -e
echo -e $PR" !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!"$NC
echo -e $CY" This script will perform the backup process of the MySQL databases!"$NC
echo -e $CY" and install the \"pv\" package to show the progress bar during backup process"$NC
echo -e $CY" Do you want to proceed?"$NC
echo -e $PR" !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!"$NC
echo -n -e $YW" Please choose"$NC $PR"[Y/y"$NC $RED"| N/n]:"$NC
read -r input
if [[ $input != y ]] && [[ $input != Y ]] && [[ $input != yes ]] && [[ $input != Yes ]]; then
echo -e ""
echo -e $CY" Thanks for using the script!"$NC
echo -e $GR" Bye!Bye!"$NC
exit
fi
if [ -e /etc/redhat-release ]; then
#Check Operating system
OS_VERSION=$(cat /etc/redhat-release | awk {'print $3'} | sed 's/\..*//')
else
echo -e $RED" !!!!!!!!/etc/redhat-release file is absent!!!!!!!!"$NC
echo -e $YW" Please make sure that the \"cloudlinux-release\" package is installed"$NC
exit
fi
package_name="pv"
if ! rpm -q "$package_name" &> /dev/null; then
echo ""
echo -e $YW" Checking if the \"pv\" package is installed in the system..."$NC
echo ""
sleep 2
if [ "$OS_VERSION" == 7 ]; then
repo_url="https://dl.fedoraproject.org/pub/epel/7/x86_64/Packages/p/"
elif [ "$OS_VERSION" == 8 ]; then
repo_url="https://dl.fedoraproject.org/pub/epel/8/Everything/x86_64/Packages/p/"
fi
list=$(curl -s $repo_url | grep 'href="pv-' | sort -V | tail -n 1)
filename=$(echo $list | sed 's/.*href="//;s/".*//')
full_url=$repo_url$filename
echo -e $CY" Downloading and installing $filename"$NC
rpm -ivh $full_url &> /dev/null
if rpm -q "$package_name" &> /dev/null; then
echo -e ""
echo -e $GR" Package \"pv\" has been installed successfully!"$NC
else
echo -e ""
echo -e $RED" !!!The package \"pv\" wasn't installed! Please contact support!!!"$NC
fi
else
echo -e ""
echo -e $GR" The $package_name package has already installed!"$NC
fi
# Create a variable with the default path value
default_backup_dir="/backup/mysqlbkp_$(date +%Y%m%d%H%M)"
first_prompt=true
while true; do
if [[ $first_prompt == true ]]; then
echo -e ""
echo -e " Would you like to use the following path $GR($default_backup_dir)$NC?$PR[yes/y]$NC for confirmation, $CY[n/no]$NC to set own path or $RED'exit'$NC to interrupt the script: \c"
read use_default
first_prompt=false
else
echo -e ""
if [[ -n $use_default && $use_default != "exit" ]]; then
if [[ $use_default =~ ^/ ]]; then
if [ -d "$use_default" ]; then
echo -e ""
echo -e $YW" The directory $use_default already exists!"$NC
echo -e $CY" Do you want to use this directory? [$GR(yes/y)$CY] for confirmation, [$RED'n/no'$CY] to set a new path or '$RED'exit'$CY' to interrupt the script: \c"$NC
read use_existing
if [[ $use_existing =~ ^[Yy](es)?$ ]]; then
backup_dir=$use_default
break
elif [[ $use_existing =~ ^[Nn](o)?$ ]]; then
use_default="exit"
elif [[ $use_existing == "exit" ]]; then
echo -e ""
echo -e ""
echo -e $CY" Thank you for using the script!"$NC
echo -e $GR" Bye!Bye!"$NC
exit 0
else
echo -e ""
echo -e $RED" !!!Input not recognized. Please enter 'exit' to exit, 'yes'/'y' to use the default path, 'no'/'n' to set a new path!!!"$NC
fi
else
backup_dir=$use_default
break
fi
fi
fi
echo -e "$CY Please enter the absolute path to creating the new backup directory example:$NC $GR/backup/testbackupdir$NC"
echo -e " or enter $RED'exit'$NC to close the script or you can enter $PR'yes'/'y'$NC to use the default path: \c"
read use_default
fi
if [[ $use_default =~ ^[Yy](es)?$ ]]; then
backup_dir=$default_backup_dir
break
elif [[ $use_default == "exit" ]]; then
echo -e ""
echo -e ""
echo -e $CY" Thank you for using the script!"$NC
echo -e $GR" Bye!Bye!"$NC
exit 0
elif [[ -n $use_default && $use_default != "exit" ]]; then
if [[ $use_default =~ ^/ ]]; then
if [ -d "$use_default" ]; then
echo -e ""
echo -e $YW" The directory $use_default already exists!"$NC
echo -e $CY" Do you want to use this directory? [$GR(yes/y)$CY] for confirmation, [$RED'n/no'$CY] to set a new path or '$RED'exit'$CY' to interrupt the script: \c"$NC
read use_existing
if [[ $use_existing =~ ^[Yy](es)?$ ]]; then
backup_dir=$use_default
break
elif [[ $use_existing =~ ^[Nn](o)?$ ]]; then
use_default="exit"
elif [[ $use_existing == "exit" ]]; then
echo -e ""
echo -e ""
echo -e $CY" Thank you for using the script!"$NC
echo -e $GR" Bye!Bye!"$NC
exit 0
else
echo -e ""
echo -e $RED" !!!Input not recognized. Please enter 'exit' to exit, 'yes'/'y' to use the default path, 'no'/'n' to set a new path!!!"$NC
fi
else
backup_dir=$use_default
break
fi
fi
else
echo -e ""
echo -e $RED" !!!Input not recognized. Please enter 'exit' to exit or 'yes'/'y' to use the default path!!!"$NC
fi
done
# Checking if the directory exists
if [ ! -d "$backup_dir" ]; then
echo -e ""
echo -e $YW" !The directory is absent!"$NC
echo -e $GR" Creating the new directory!"$NC
sleep 1
mkdir -p "$backup_dir"
fi
# Checking if the new directory was created successfully
if [ -d "$backup_dir" ]; then
echo -e ""
echo -e $GR" The directory to store backup files has been created successfully: $backup_dir"$NC
else
echo -e ""
echo -e $RED"!Oh no! Something went wrong. The directory wasn't created! Please try to create it manually to see the result!"$NG
exit 1
fi
check_mysql_cnf_file() {
if [ -f "/etc/.my.cnf" ]; then
echo -e $GR" Found /etc/.my.cnf file. Using credentials from it."$NC
return 0
else
echo -e $YW" None control panel has been detected and the /etc/.my.cnf file not found. Please enter MySQL credentials manually."$NC
return 1
fi
}
get_user_credentials() {
if ! check_mysql_cnf_file; then
echo -n -e $YW" Enter MySQL username (or type 'exit' to cancel): "$NC
read userdb
if [[ $userdb == "exit" ]]; then
echo -e ""
echo -e $CY" Thank you for using the script!"$NC
echo -e $GR" Bye!Bye!"$NC
exit 0
fi
echo -n -e $YW" Enter MySQL password: "$NC
read -s userpw
echo -e ""
fi
}
check_panel () {
ROOT_PLESK_DIR="/usr/local/psa/admin/"
ROOT_CPANEL_DIR="/usr/local/cpanel/whostmgr/docroot/"
ROOT_DA_DIR="/usr/local/directadmin/"
if [ -d $ROOT_PLESK_DIR ]; then
PANEL="plesk"
elif [ -d $ROOT_CPANEL_DIR ]; then
PANEL="cpanel"
elif [ -d $ROOT_DA_DIR ]; then
PANEL="directadmin"
else
PANEL="unknown"
fi
}
do_backup () {
local MYSQL_PWD=""
local userdb=""
local userpw=""
local total_databases=0
local backup_count=0
check_panel
if [[ $PANEL == "plesk" ]]; then
MYSQL_PWD=$(cat /etc/psa/.psa.shadow)
userdb="admin"
total_databases=$(mysql -p$MYSQL_PWD -u admin -e "show databases;" | awk '{print $1}' | egrep -v "Database|information_schema|performance_schema|sys" | wc -l)
for db in $(mysql -p"$MYSQL_PWD" -u "$userdb" -e "show databases;" | awk '{print $1}' | egrep -v "Database|information_schema|performance_schema|sys"); do
echo -e ""
echo -e $GR" Dumping database: $db"$NC
mysqldump -p"$MYSQL_PWD" -u "$userdb" --routines --events --triggers $db | pv --progress --size "$(mysqldump -p"$MYSQL_PWD" -u "$userdb" $db | wc -c)" > "$backup_dir/$db.sql"
backup_count=$((backup_count + 1))
echo -e " Backup progress: $backup_count out of $total_databases databases backed up."
done
echo -e ""
echo -e $GR" Congratilations! The backup process has been completed! You may find your backup files here: $backup_dir"$NC
echo -e $YW" !!!Please check the output and make sure that all databases are backed up successfully!!!!"$NC
elif [[ $PANEL == "cpanel" || $PANEL == "directadmin" ]]; then
total_databases=$(mysql -e "show databases;" | awk '{print $1}' | egrep -v "Database|information_schema|performance_schema|sys" | wc -l)
for db in $(mysql -e "show databases;" | awk '{print $1}' | egrep -v "Database|information_schema|performance_schema|sys"); do
echo -e ""
echo -e $GR" Dumping database: $db"$NC
mysqldump --routines --events --triggers $db | pv --progress --size "$(mysqldump $db | wc -c)" > "$backup_dir/$db.sql"
backup_count=$((backup_count + 1))
echo -e " Backup progress: $backup_count out of $total_databases databases backed up."
done
echo -e ""
echo -e $GR" Congratilations! The backup process has been completed! You may find your backup files here: $backup_dir"$NC
echo -e $YW" !!!Please check the output and make sure that all databases are backed up successfully!!!!"$NC
else
get_user_credentials
total_databases=$(mysql -p"$userpw" -u "$userdb" -e "show databases;" | awk '{print $1}' | egrep -v "Database|information_schema|performance_schema|sys" | wc -l)
for db in $(mysql -p"$userpw" -u "$userdb" -e "show databases;" | awk '{print $1}' | egrep -v "Database|information_schema|performance_schema|sys"); do
echo -e ""
echo -e $GR" Dumping database: $db"$NC
mysqldump -p"$userpw" -u "$userdb" --routines --events --triggers $db | pv --progress --size "$(mysqldump $db | wc -c)" > "$backup_dir/$db.sql"
backup_count=$((backup_count + 1))
echo -e " Backup progress: $backup_count out of $total_databases databases backed up."
done
echo -e ""
echo -e $GR" Congratilations! The backup process has been completed! You may find your backup files here: $backup_dir"$NC
echo -e $YW" !!!Please check the output and make sure that all databases are backed up successfully!!!!"$NC
fi
}
do_backup
Comments
0 comments
Please sign in to leave a comment.