#! /bin/sh

if [[ ! -f "/usr/local/cpanel/cpanel" ]]; then
	echo -e "This is intended to run on cPanel servers"
	kill -9 $$
else
	:
fi

LOG_FILE="/root/dbms_update_log.log"
truncate -s 0 $LOG_FILE
echo -e "\n========================================================================\nStarting UpDATE process, logs will be saved at $LOG_FILE\n========================================================================\n" | tee -a "$LOG_FILE"
DATE=$(/usr/bin/date +%s)

#Functions to stop and restart the DBMS
restart_mysql() {
    (systemctl restart mysql.service && echo -e "DBMS Restarted") || (/scripts/restartsrv_mysql > /dev/null && echo -e "DBMS Restarted") || (service mysql restart > /dev/null && echo -e "DBMS Restarted") || (service mysqld restart > /dev/null && echo -e "DBMS Restarted") 
}

stop_mysql() {
    (systemctl stop mysql.service 2 &>/dev/null && echo -e "DBMS has been stopped") || (systemctl stop mysqld 2 &>/dev/null && echo -e "DBMS has been stopped") || (systemctl stop mariadb 2 &>/dev/null && echo -e "DBMS has been stopped")
}


#SQL mode
sqlm() {
    echo -e "\nBacking up the configuration file to /etc/my.cnf.$DATE:"
    cp -avr /etc/my.cnf /etc/my.cnf.$DATE
    echo -e "\n- SQL MODE:"
    (grep -Eq 'sql_mode|sql-mode' /etc/my.cnf &&
    echo -e "\e[1;92m[PASS] \e[0;32mSQL mode is set explicitly.\e(B\e[m\n" || 
    (echo -e "Current effective setting is: sql_mode=\"$(mysql -NBe 'select @@sql_mode;')\"\e(B\e[m"
    echo -e "Adding it to my.cnf..."
    sql2=$(mysql -NBe 'select @@sql_mode;')
    
    #Adding it under "[mysqld] block"
    line_num=$(awk '/^\[mysqld\]$/ {print NR; exit}' "/etc/my.cnf")
    if [[ -n "$line_num" ]]; then
        sed -i "${line_num}a\\sql_mode=$sql2" /etc/my.cnf
    elif [ ! -n "$line_num" ]; then
        echo "[mysqld]" >> /etc/my.cnf 
        echo "sql_mode=$sql2" >> /etc/my.cnf 
    else 
        :
    fi
    sed -i 's/::ffff:127.0.0.1/127.0.0.1/g' /etc/my.cnf
    sed -i "s/NO_AUTO_CREATE_USER,//g" /etc/my.cnf
    echo -e "Added, restarting MYSQL\n"
    restart_mysql  
    echo -e "Confirming: grep -E 'sql_mode|sql-mode' /etc/my.cnf"
    grep -E 'sql_mode|sql-mode' /etc/my.cnf && echo -e "\nGiving a few secs for MYSQL to start\n" && sleep 3))
}

exe() { echo "\$ $@" ; "$@" ; }

#Stop the script
stopp() {
	echo -e "\nErrors have been detected. The script is paused, use "Ctrl + z" to have access to the console. Run fg after you check to resume the script."
    pkill -STOP -P $$
}

#Exit status
exit_status() {
    e_status="$?"
    if [[ "$e_status" == "0" ]]; then
        : 
    else
        stopp
    fi
}

# Get DBMS version
sys_version() {
	echo "System version is $mysqlv"
}

#Pre-work for 5.6-5.7
prepre() {
    echo "Deprecated MySQL Variables 5.6 ---> 5.7: ";egrep -wi 'innodb\_additional\_mem\_pool\_size|safe\_show\_database|skip\_locking|skip\_symlink|master-*|log\-slow\-queries|innodb\_additional\_mem\_pool\_size|innodb\_use\_sys\_malloc|storage\_engine|create\_old\_temporals|default-authentication-plugin|thread_concurrency|timed\_mutexes|log-slow-admin-statements|log-slow-slave-statements' /etc/my.cnf;echo;echo "MySQL Datadir: ";mysql -e "show variables;" |grep datadir;echo;echo "Users with Old Style Passwords: "; if [[ $(mysql -e "SELECT Host, User, Password AS Hash FROM mysql.user WHERE Password REGEXP '^[0-9a-fA-F]{16}' ORDER BY User, Host;"|wc -l) > 0 ]];then echo "Yes";else echo "No"; echo; echo -e "\e[1;31mWARNING: MySQL 5.7's sys schema can break databases when upgrading to MariaDB\e[0m";fi; echo

    echo -e "\n- Important variables:"
    (set -x; mysql -e "show variables like '%innodb_additional_mem_pool_size'"; mysql -e "show variables like '%innodb_use_sys_malloc'"; mysql -e "show variables like '%storage_engine'"; mysql -e "show variables like '%create_old_temporals'"; mysql -e "show variables like '%default-authentication-plugin'"; mysql -e "show variables like '%thread_concurrency'"; mysql -e "show variables like '%timed_mutexes'"; mysql -e "show variables like '%log-slow-admin-statements'"; mysql -e "show variables like '%log-slow-slave-statements'";) 
}

#Pre-checks and backups
pre_checks() {
    
    echo -e "\n- Checking for corruption:"
    mychecktemp=$(mysqlcheck -Asc)
    echo -e "\nmysqlcheck -Asc"
    if [[ -z "$mychecktemp" ]]; then
        echo -e "\nNo output. All good.\n"
    else
        echo $mychecktemp
        mychecktemp2=$(echo $mychecktemp | grep -iE "corrupt|crashe" )
        if [[ ! -z "$mychecktemp2" ]]; then 
            stopp
	    else 
		    echo -e "\nMinor errors/warnings\n" 
	    fi
    fi
 
    echo -e "- Backups:\n"
    mkdir -p /root/dbms_back
    if [[ ! -d "/root/dbms_back/mysqldumps" ]]; then 
	    mkdir /root/dbms_back/mysqldumps
    fi
    cd /root/dbms_back/mysqldumps
    (set -x; pwd)
    echo
    exe eval '(echo "SHOW DATABASES;" | mysql -Bs | grep -v '^information_schema$' | while read i ; do echo Dumping $i ; mysqldump --single-transaction $i | gzip -c > $i.sql.gz ; done)'
    echo
    error='0';count='';for f in $(/bin/ls *.sql.gz); do if [[ ! $(zegrep 'Dump completed on [0-9]{4}-([0-9]{2}-?){2}' ${f}) ]]; then echo "Possible error: ${f}"; error=$((error+1)); fi ; count=$((count+1)); done; (echo "Error count: ${error}"; echo "Total DB_dumps: ${count}"; echo "Total DBs: $(mysql -NBe 'SELECT COUNT(*) FROM information_schema.SCHEMATA WHERE schema_name NOT IN ("information_schema");')";)|column -t
    if [[ "$error" != 0 ]]; then
        stopp
    fi     
 
    echo -e "\nRsync data dir:"
    ddir=$(mysql -e "show variables;" |grep datadir| awk {'print $2'})
    bakdir=("/root/dbms_back/mysql.backup.$(date +%s)/")
    (systemctl stop mysql.service 2&> /dev/null && echo -e "MYSQL has been stopped") || (service mysql stop 2&> /dev/null && echo -e "MYSQL has been stopped*") || (service mysqld stop 2&> /dev/null && echo -e "MYSQL has been stopped**")
    sleep 1 && echo
    echo "Path to data dir: $ddir"
    echo "rsync -aHl $ddir $bakdir"
    rsync -aHl $ddir $bakdir
    exit_status
    echo -e "Synced\n" 
    echo "Restarting MYSQL..."
    (systemctl restart mysql.service 2&> /dev/null && echo -e "Restarted") || (/scripts/restartsrv_mysql 2&> /dev/null && echo -e "Restarted*") || (service mysql restart 2&> /dev/null && echo -e "Restarted**") || (service mysqld restart 2&> /dev/null && echo -e "Restarted***")
    sleep 3

    echo -e "\n\n-Checking HTTP status of all domains prior the upgrade:\n"
    sort /etc/userdomains | cut -f1 -d: | grep -v '*' | while read i; do curl -sILo /dev/null -w "%{http_code} " -m 5 http://$i; echo $i; done > /root/dbms_back/mysql_pre_upgrade_http_check
    (set -x; egrep -v '^(0|2)00 ' /root/dbms_back/mysql_pre_upgrade_http_check)
    echo -e "\n- WHM Upgrade: MySQL/MariaDB Upgrade"
}

#Prep work for 5 to 8 upgrades (MySQL)
five_to_eight_prep() {
    echo -e "\nUpgrade checker:"
    echo -e "\nInstalling mysql-shell\n"
    _centos_version=$(rpm -q kernel | head -1 | grep -Po '(?<=el)[0-9]')
    if [[ "$_centos_version" == 8 ]]; then
	_repo_rpm="https://dev.mysql.com/get/mysql80-community-release-el8-8.noarch.rpm"
    elif [[ "$_centos_version" == 7 ]]; then
	_repo_rpm="https://dev.mysql.com/get/mysql80-community-release-el7-9.noarch.rpm"
    elif [[ "$_centos_version" == 6 ]]; then
	_repo_rpm="https://dev.mysql.com/get/mysql80-community-release-el6-10.noarch.rpm"
    fi
    (yum -y install $_repo_rpm && yum-config-manager --disable mysql80-community mysql-connectors-community mysql-tools-community
    yum -y --enablerepo=mysql-tools-community install mysql-shell) 2>&1 > /dev/null
    mysql_pass=$(sed -nre '/password/s/^ *password *= *"?([^"]+)"? *$/\1/gp' /root/.my.cnf)
    if [[ -z "$mysql_pass" ]]; then
	echo "The password could not be retrieved, try to find it to resume the script"
	stopp 
    fi
    echo
    if [[ -z "$mysql_pass" ]]; then
	echo "Enter the password"
	read mysql_pass
    fi
}

#Upgrade using cPanel's API
main_cpanel_proc() {
    echo -e "List of available versions:\n"
    /usr/local/cpanel/bin/whmapi1 installable_mysql_versions| grep "version: '"
    echo -e "\nWhich one are you installing? Only numbers (5.7, 10.5, etc.)"
    read vers
    while true; do 
        if  [[ "$vers" == '10.5' ]] || [[ "$vers" == '10.6' ]] || [[ "$vers" == '5.7' ]] || [[ "$vers" == '8.0' ]]; then
        #check here add vers
            break
        else
            echo "Invalid option, choose again."
            read vers
        fi
    done
    
    id=$(/usr/local/cpanel/bin/whmapi1 start_background_mysql_upgrade version=$vers|grep "upgrade_id" | cut -d ":" -f2)
    id="${id:1}"
    echo -e "\nWHM Upgrade ID: $id"
    sleep 1
    aver=$(/usr/local/cpanel/bin/whmapi1 background_mysql_upgrade_status upgrade_id=$id| tail -n6|grep "state:"| cut -d ":" -f2| awk '{print $1;}')
    echo -e "\nUpgrading..."
    BAR='#'
    while [[ "$aver" == "inprogress" ]]; do
        echo -ne "${BAR}"
        sleep 5
        aver=$(/usr/local/cpanel/bin/whmapi1 background_mysql_upgrade_status upgrade_id=$id| tail -n6|grep "state:"| cut -d ":" -f2| awk '{print $1;}')
    done
    sleep 5
    if [[ "$aver" == "failed" || "$aver" == "failure" ]]; then
        echo -e "\nCheck the log at /var/cpanel/logs/$id/unattended_background_upgrade.log"
        stopp
    else
        echo -e "\n"
        /usr/local/cpanel/bin/whmapi1 background_mysql_upgrade_status upgrade_id=$id| tail -n6
    fi
}

#Upgrade using the CloudLinux's governor
upgrade_do_governor() {
    sqlm 
    sys_version 
    pre_checks
    if [[ "$gov_package" == 0 ]]; then
        operation=$(echo "update")
    else
        operation=$(echo "install")
    fi
    echo -e "\nThis installation will use the Governor script."
    echo -e "\nTo which version would you like to upgrade?\nOptions:\n\nMYSQL:\nmysql56, mysql57, mysql80\n\nMariaDB:\nmariadb103, mariadb104, mariadb105, mariadb106, mariadb1011\n"
    read answ2; echo -e "\nSelected: $answ2"
    supported_versions=(mysql56 mysql57 mysql80 mariadb103 mariadb104 mariadb105 mariadb106 mariadb1011)
    while true; do
        if [[ "${supported_versions[@]}" =~ "$answ2" ]]; then
            echo -e "\nUpgrading to $answ2 using the MySQL Governor script:"
            exe eval 'yum -y $operation governor-mysql'
            exe eval '/usr/share/lve/dbgovernor/mysqlgovernor.py --mysql-version=$answ2'
            exe eval '/usr/share/lve/dbgovernor/mysqlgovernor.py --install --yes'
            exit_code_gov="$?"
            if [[ "$exit_code_gov" == 1 ]]; then
                echo -e "\nUpgrade failed.\n"
            else 
                finish
                post_check 
            fi 
            break
        else
            echo "Invalid option, choose again."
            read answ2
        fi
    done
}

#cPanel option selection
cpanel_options() {

    if [[ "$mysqlv" == "5.7."* || "$mysqlv" == "5.6."* ]]; then
        sqlm  
        sys_version 
        prepre 
        five_to_eight_prep 
        pre_checks 
        echo
    (set -x; mysqlsh -hlocalhost -uroot --password=$mysql_pass -e 'util.checkForServerUpgrade()')
        echo -e "\nIf you see any errors (warnings are usually safe to ignore), pause the script with Ctrl+z."
        main_cpanel_proc 
        post_check 

    elif [[ "$mysqlv" == "10.3."* || "$mysqlv" == "10.5."* || "$mysqlv" == "10.6."* ]]; then
        sqlm 
        sys_version 
        pre_checks 
        main_cpanel_proc 
        post_check 
        
    elif [[ "$mysqlv" == "10.11."* || "$mysqlv" == "8.0."* ]]; then
        echo -e "You are already on the latest version.\n" 

    else 
        echo -e "This is a not supported upgrade.\n" 
    fi
}

#Postcheck
post_check() {
    sleep 1
    echo -e "\n\nPost-check:\n"
    sort /etc/userdomains | cut -f1 -d: | grep -v '*' | while read i; do curl -sILo /dev/null -w "%{http_code} " -m 5 http://$i; echo $i; done > /root/dbms_back/mysql_post_upgrade_http_check
    exe eval 'diff /root/dbms_back/mysql_pre_upgrade_http_check /root/dbms_back/mysql_post_upgrade_http_check'
    echo -e "\nAll set.\n"
}


#Main Procedure:
upgrade_do_cpanel() {
    mysqlv=$(mysql -V | grep -Eo "[0-9]+\.[0-9]+\.[0-9]+";)
    if [[ "$(cat /etc/redhat-release)" == *"CloudLinux"* ]]; then
        echo "CloudLinux server detected..." 
        gov_package=$(rpm -q governor-mysql &>/dev/null; echo $?)
        dbms_packages=$(rpm -qa| grep -i "cl-mysql\|cl-mariadb" &>/dev/null; echo $?)

        if [[ "$gov_package" == 0 ]] && [[ "$dbms_packages" == 0 ]]; then
            upgrade_do_governor
        else
            echo -e "\nWill you be using the MySQL Governor's script? y/n" 
            read answ ; echo $answ
            if [[ $answ == "yes" || $answ == "Yes" || $answ == "YES" || $answ == "y" ]]; then
                upgrade_do_governor 
            else
                echo "Ok, cPanel upgrade then." 
                cpanel_options 
            fi
        fi
    fi 
}

upgrade_do_cpanel | tee -a "$LOG_FILE"
