Check all Databases on a Server Account

ByAdmin

Check all Databases on a Server Account

Script to audit all WordPress installs under [Account] i.e. /home/Account/

Assumes you have root access
This will show every wp-config.php, the DB name, DB user, whether the DB exists, and whether the DB user exists.
Change [accountname] to the account you wish to audit e.g. /home/accountname/
The script will check these paths:

/home/accountname/domain.com/wp-config.php
/home/accountname/public_html/wp-config.php
/home/accountname/public_html/domain.com/wp-config.php
/home/accountname/public_html/domain.com/blog/wp-config.php
bash
cat >/root/check-account-wpdbs.sh <<'EOF'
#!/bin/bash

CPUSER="$1"

if [ -z "$CPUSER" ]; then
  echo "Usage: $0 cpanel_account"
  echo "Example: $0 [accountname]"
  exit 1
fi

HOME_DIR="/home/$CPUSER"

if [ ! -d "$HOME_DIR" ]; then
  echo "ERROR: account home folder not found: $HOME_DIR"
  exit 1
fi

extract_define() {
  local key="$1"
  local file="$2"

  KEY="$key" perl -ne '
    if (/define\s*\(\s*["\x27]\Q$ENV{KEY}\E["\x27]\s*,\s*["\x27]([^"\x27]+)["\x27]/) {
      print "$1\n";
      exit;
    }
  ' "$file"
}

sql_escape() {
  printf "%s" "$1" | sed "s/\\\\/\\\\\\\\/g; s/'/''/g"
}

find_wp_configs() {
  {
    # Normal installs:
    # /home/account/domain.com/wp-config.php
    # /home/account/public_html/wp-config.php
    find "$HOME_DIR" -maxdepth 3 -type f -name wp-config.php 2>/dev/null

    # Explicit deeper public_html installs:
    # /home/account/public_html/domain.com/wp-config.php
    # /home/account/public_html/domain.com/blog/wp-config.php
    if [ -d "$HOME_DIR/public_html" ]; then
      find "$HOME_DIR/public_html" -maxdepth 5 -type f -name wp-config.php 2>/dev/null
    fi
  } | sort -u
}

printf "WP_CONFIG\tDB_NAME\tDB_USER\tDB_EXISTS\tDB_USER_EXISTS\tDB_USER_MATCHES\n"

while IFS= read -r WP; do
  DB="$(extract_define DB_NAME "$WP")"
  DU="$(extract_define DB_USER "$WP")"

  if [ -z "$DB" ] || [ -z "$DU" ]; then
    printf "%s\t%s\t%s\t%s\t%s\t%s\n" \
      "$WP" \
      "${DB:-NOT_FOUND}" \
      "${DU:-NOT_FOUND}" \
      "SKIP" \
      "SKIP" \
      "Could not read DB_NAME or DB_USER from wp-config.php"
    continue
  fi

  DBSQL="$(sql_escape "$DB")"
  DUSQL="$(sql_escape "$DU")"

  DBEX="$(mariadb -NBe "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME='$DBSQL' LIMIT 1;" 2>/dev/null | head -1)"

  USER_MATCHES="$(mariadb -NBe "SELECT CONCAT(User,'@',Host) FROM mysql.user WHERE User='$DUSQL' ORDER BY Host;" 2>/dev/null | tr '\n' ',' | sed 's/,$//')"

  if [ -n "$DBEX" ]; then
    DB_EXISTS="YES"
  else
    DB_EXISTS="NO"
  fi

  if [ -n "$USER_MATCHES" ]; then
    DB_USER_EXISTS="YES"
  else
    DB_USER_EXISTS="NO"
    USER_MATCHES="NO_MATCHING_DB_USER_FOUND"
  fi

  printf "%s\t%s\t%s\t%s\t%s\t%s\n" \
    "$WP" \
    "$DB" \
    "$DU" \
    "$DB_EXISTS" \
    "$DB_USER_EXISTS" \
    "$USER_MATCHES"

done < <(find_wp_configs)
EOF

chmod +x /root/check-account-wpdbs.sh

bash -n /root/check-account-wpdbs.sh
Run this cmd at terminal prompt<br>/root/check-account-wpdbs.sh [accountname] | column -t -s $'\t'<br><br>To save ouput to a file, run<br><code>/root/check-account-wpdbs.sh [accountname] > /root/[accountname]-wpdb-check.tsv</code>

The output columns will be:
WP_CONFIG
DB_NAME
DB_USER
DB_EXISTS
DB_USER_EXISTS
DB_USER_MATCHES

Example result:

WP_CONFIG                                    DB_NAME   DB_USER  DB_EXISTS  DB_USER_EXISTS         DB_USER_MATCHES
/home/accountname/domain1.com/wp-config.php  domain1   domain1   YES         YES                   domain1@192.0.2.1,domain1@localhost,domain1@server.test-domain.com
/home/accountname/domain2.com/wp-config.php  domain2   domain2   NO          YES                   domain2@192.0.2.1,domain2@localhost,domain2@server.test-domain.com
/home/accountname/domain3.com/wp-config.php  domain3   domain3   NO          YES                   domain3@192.0.2.1,domain3@localhost,domain3@server.test-domain.com
/home/accountname/domain4.com/wp-config.php  domain4   domain4   YES         YES                   domain4@192.0.2.1,domain4@localhost,domain4@server.test-domain.com
/home/accountname/domain5.com/wp-config.php  domain5   domain5   YES         YES                   domain5@192.0.2.1,domain5@localhost,domain5@server.test-domain.com
/home/accountname/domain6.com/wp-config.php  domain6   domain6   YES         YES                   domain6@192.0.2.1,domain6@localhost,domain6@server.test-domain.com
/home/accountname/domain7.com/wp-config.php  domain7   domain7   YES         YES                   domain7@192.0.2.1,domain7@localhost,domain7@server.test-domain.com
/home/accountname/domain8.com/wp-config.php  domain8   domain8   YES         YES                   domain8@192.0.2.1,domain8@localhost,domain8@server.test-domain.com

Similar Posts