#!/bin/bash
#: Title       : cpanel-db-mapping.sh
#: Date Created: Wed Fev 28 15:16:41 CDT 2018
#: Last Edit   : Mon Fev 28 16:31:43 CDT 2018
#: Author      : PlanetHoster (Quentin C.)
#: Version     : 1.00
#: Description : Find unlinked databases in cPanel (PS-574)
#: Disclamer   : Please note that we cannot guarantee the relevance, timeliness, or accuracy of this script.
#: How use ? curl -s https://deploy.planethoster.net/fix/cpanel-db-mapping.sh | sh

if [ -f /usr/local/cpanel/version ]
then

    #: Make a list(e.g. database_list.txt) of cPanel databases on the server:
    whmapi1 list_databases|grep -oP '(?<=name: ).+' > /root/database_list.txt

    #: Read all databases in MySQL
    mysql --defaults-file=/root/.my.cnf -Ns -e "show databases"| while read database
    do
        #: Exclude system database
        if [[ ! "$database" =~ ^(information_schema|mysql|performance_schema|cphulkd|horde|modsec|roundcube|leechprotect)$ ]];
        then

          #: Is not present in the file ?
          if [ "$(grep -c ^$database$ /root/database_list.txt)" -eq 0 ]
          then

              #: Take the cPanel username linked
              cpaneluser=$(echo $database | cut -d '_' -f 1)

              #: Already exist on the server ?
              if [ ! -f /var/cpanel/users/$cpaneluser ];
              then
                    echo "cPanel $cpaneluser Not exist / Need manual DROP $database"
                    #echo "DROP DATABASE IF EXISTS $database;" >> /root/manual_db_drop.sql
              else
                    echo "cPanel $cpaneluser Exist / Need Link $database"
                    #/usr/local/cpanel/bin/dbmaptool $cpaneluser --type mysql --dbs $database
              fi
          fi
        fi
    done

else
    echo "cPanel is not installed"
fi