#!/bin/bash
# This script will assist with configuring ProxySQL in combination with Percona XtraDB cluster.
# Version 1.0
###############################################################################################


# Make sure only root can run this script
if [ $(id -u) -ne 0 ]; then
  echo "ERROR: This script must be run as root!" 1>&2
  exit 1
fi

# Dispay script usage details
usage () {
  echo "Usage: [ options ]"
  echo "Options:"
    echo " --config-file                   	Override login credentials from command line and read login credentials from config file."
    echo " --proxysql-user=user_name       	User to use when connecting to the ProxySQL service"
    echo " --proxysql-password[=password]  	Password to use when connecting to the ProxySQL service"
    echo " --proxysql-port=port_num        	Port to use when connecting to the ProxySQL service"
    echo " --proxysql-host=host_name       	Hostname to use when connecting to the ProxySQL service"
    echo " --cluster-user=user_name        	User to use when connecting to the Percona XtraDB Cluster node"
    echo " --cluster-password[=password]   	Password to use when connecting to the Percona XtraDB Cluster node"
    echo " --cluster-port=port_num         	Port to use when connecting to the Percona XtraDB Cluster node"
    echo " --cluster-host=host_name        	Hostname to use when connecting to the Percona XtraDB Cluster node"
    echo " --monitor-user=user_name        	User to use for monitoring Percona XtraDB Cluster nodes through ProxySQL"
    echo " --monitor-password[=password]   	Password to for monitoring Percona XtraDB Cluster nodes through ProxySQL"
    echo " --pxc-app-write-user=user_name  	Application write user to use when connecting to the Percona XtraDB Cluster node"
    echo " --pxc-app-write-password[=password]	Application write password to use when connecting to the Percona XtraDB Cluster node"
    echo " --pxc-app-read-user=user_name  	Application read user to use when connecting to the Percona XtraDB Cluster node"
    echo " --pxc-app-read-password[=password]	Application read password to use when connecting to the Percona XtraDB Cluster node"
    echo " --enable, -e                        	Auto-configure Percona XtraDB Cluster nodes into ProxySQL"
    echo " --disable, -d                       	Remove Percona XtraDB Cluster configurations from ProxySQL"
    echo " --galera-check-interval         	Interval for monitoring proxysql_galera_checker script(in milliseconds)"
    echo " --mode                          	ProxySQL read/write configuration mode, currently it supports 'loadbal and 'singlewrite' modes"
    echo " --write-node                   	Writer node to accept write statments. This option only support with --mode=singlewrite"  
    echo " --adduser                       	Add Percona XtraDB Cluster application user to ProxySQL database"
    echo " --version, -v                       	Print version info"
}

# Check if we have a functional getopt(1)
if ! getopt --test
  then
  go_out="$(getopt --options=edv --longoptions=proxysql-user:,proxysql-password::,proxysql-host:,proxysql-port:,cluster-user:,cluster-password::,cluster-host:,cluster-port:,monitor-user:,monitor-password:,pxc-app-user:,pxc-app-password:,galera-check-interval:,mode:,write-node:,config-file:,enable,disable,adduser,version,help \
  --name="$(basename "$0")" -- "$@")"
  test $? -eq 0 || exit 1
  eval set -- $go_out
fi

if [[ $go_out == " --" ]];then
  usage
  exit 1
fi

for arg
do
  case "$arg" in
    -- ) shift; break;;
    --proxysql-user )
    PROXYSQL_USERNAME="$2"
    shift 2
    ;;
    --proxysql-password )
    case "$2" in
      "")
      read -r -s -p  "Enter ProxySQL password:" INPUT_PASS
      if [ -z "$INPUT_PASS" ]; then
        PROXYSQL_PASSWORD=""
	printf "\nContinuing without ProxySQL password...\n";
      else
        PROXYSQL_PASSWORD="$INPUT_PASS"
      fi
      printf "\n"
      ;;
      *)
      PROXYSQL_PASSWORD="$2"
      ;;
    esac
    shift 2
    ;;
    --proxysql-host )
    PROXYSQL_HOSTNAME="$2"
    shift 2
    ;;
    --proxysql-port )
    PROXYSQL_PORT="$2"
    shift 2
    ;;
    --cluster-user )
    CLUSTER_USERNAME="$2"
    shift 2
    ;;
    --cluster-password )
    case "$2" in
      "")
      read -r -s -p  "Enter Cluster password:" INPUT_PASS
      if [ -z "$INPUT_PASS" ]; then
        CLUSTER_PASSWORD=""
	printf "\nContinuing without Cluster password...\n";
      else
        CLUSTER_PASSWORD="$INPUT_PASS"
      fi
      printf "\n"
      ;;
      *)
      CLUSTER_PASSWORD="$2"
      ;;
    esac
    shift 2
    ;;
    --cluster-host )
    CLUSTER_HOSTNAME="$2"
    shift 2
    ;;
    --cluster-port )
    CLUSTER_PORT="$2"
    shift 2
    ;;
    --monitor-user )
    MONITOR_USERNAME="$2"
    shift 2
    ;;
    --monitor-password )
    MONITOR_PASSWORD="$2"
    shift 2
    ;;
    --pxc-app-write-user )
    PXC_APP_WRITE_USERNAME="$2"
    shift 2
    ;;
    --pxc-app-write-password )
    PXC_APP_WRITE_PASSWORD="$2"
    shift 2
    ;;
    --pxc-app-read-user )
    PXC_APP_READ_USERNAME="$2"
    shift 2
    ;;
    --pxc-app-read-password )
    PXC_APP_READ_PASSWORD="$2"
    shift 2
    ;;
    --config-file )
      config_file="$2"
      shift 2
      if [ -z "${config_file}" ]; then
        echo "ERROR: You have not given config file location. Terminating."
        exit 1
      fi
      if [ -e "${config_file}" ]; then
        source "${config_file}"
      else
        echo "ERROR: ${config_file} does not exist. Terminating."
        exit 1
      fi
      ;;
    -e | --enable )
    shift
    enable=1
    ;;
    --adduser )
    shift
    adduser=1
    ;;
    -d | --disable )
    shift
    disable=1
    ;;
    --galera-check-interval )
    GALERA_CHECK_INTERVAL="$2"
    shift 2
    ;;
    --mode )
    RW_MODE="$2"
    shift 2
    if [ $RW_MODE != "loadbal" -a $RW_MODE != "singlewrite" ]; then
      echo "ERROR: Invalid --mode passed:"
      echo "  Please choose any of these modes loadbal|singlewrite"
      exit 1
    fi
    ;;
    --write-node )
    WRITE_NODE="$2"
    shift 2
    ;;
    -v | --version )
      echo "proxysql-admin version v1.3.1"
      exit 0
    ;;
    --help )
    usage
    exit 0
    ;;
  esac
done

if [[ ! -e `which mysql 2> /dev/null` ]] ;then
  echo "mysql client is not found, please install the mysql client package" 
  exit 1
fi

# Check the options gathered from the command line
if [ -z "$PROXYSQL_USERNAME" ];then
  echo "The ProxySQL username is required!"
  usage
  exit 1
fi

if [[ -z "$PROXYSQL_HOSTNAME" ]]; then
  PROXYSQL_HOSTNAME="-h127.0.0.1"
fi

if [[ -z "$PROXYSQL_PORT" ]]; then
  PROXYSQL_PORT="-P6032"
fi

if [ -z "$CLUSTER_USERNAME" ];then
  echo "The Percona XtraDB Cluster username is required!"
  usage
  exit 1
fi

if [[ -z "$CLUSTER_HOSTNAME" ]]; then
  CLUSTER_HOSTNAME="localhost"
fi

if [[ -z "$CLUSTER_PORT" ]]; then
  CLUSTER_PORT="3306"
fi

if [[ -z "$GALERA_CHECK_INTERVAL" ]]; then
  GALERA_CHECK_INTERVAL=3000
fi

if [[ -z "$RW_MODE" ]]; then
  RW_MODE="loadbal"
else
  if [ $RW_MODE != "loadbal" -a $RW_MODE != "singlewrite" ]; then
    echo "ERROR: Invalid --mode passed:"
    echo "  Please choose any of these modes loadbal|singlewrite"
    exit 1
  fi
fi

if [ $RW_MODE == "loadbal" ]; then
  WRITE_HOSTGROUP_ID=10
  READ_HOSTGROUP_ID=10
elif [ $RW_MODE == "singlewrite" ]; then
  WRITE_HOSTGROUP_ID=10
  READ_HOSTGROUP_ID=11
fi

PIDFILE=/tmp/pxc-proxysql-monitor.pid

proxysql_exec() {
  query=$1
  printf "[client]\nuser=${PROXYSQL_USERNAME}\npassword=${PROXYSQL_PASSWORD}\nhost=${PROXYSQL_HOSTNAME}\nport=${PROXYSQL_PORT}\n" | \
      mysql --defaults-file=/dev/stdin --protocol=tcp -e "${query}" 2>/dev/null
}

mysql_exec() {
  query=$1
  printf "[client]\nuser=${CLUSTER_USERNAME}\npassword=${CLUSTER_PASSWORD}\nhost=${CLUSTER_HOSTNAME}\nport=${CLUSTER_PORT}\n" | \
      mysql --defaults-file=/dev/stdin --protocol=tcp -Bse "${query}" 2>/dev/null
}

proxysql_connection_check(){
  if ! proxysql_exec "show tables" >/dev/null; then 
    echo "Please enter valid ProxySQL connection parameters! Terminating.."
    exit 1
  fi
}

pxc_connection_check(){
  if ! mysql_exec "select @@port" >/dev/null; then 
    echo "Please enter valid PXC connection parameters! Terminating.."
    exit 1
  fi
}
check_cmd(){
  MPID=$1
  ERROR_MSG=$2
  if [ ${MPID} -ne 0 ]; then echo -e "\nERROR: $ERROR_MSG. Terminating!"; exit 1; fi
}

check_proxysql(){
  if ! pidof proxysql >/dev/null ; then
    echo "ProxySQL is not running, please check the error log at /var/lib/proxysql/proxysql.log"
    exit 1
  fi
}

user_input_check(){
  USER_CATEGORY=$1
  USER_DESCRIPTION=$2
  HOSTGROUP_ID=$3
  USERNAME=`eval "echo \\$${USER_CATEGORY}_USERNAME"`
  PASSWORD=`eval "echo \\$${USER_CATEGORY}_PASSWORD"`
  if [[ -z $USERNAME ]]; then
    read -r -p "Enter ${USER_DESCRIPTION}name : " ${USER_CATEGORY}_USERNAME
      while [[ -z "${USER_CATEGORY}_USERNAME" ]]
      do
        echo -n "No input entered, Enter ${USER_DESCRIPTION}name: "
      read -r ${USER_CATEGORY}_USERNAME
    done
  else
    echo -e "${USER_DESCRIPTION}name as per command line is `eval "echo \\$${USER_CATEGORY}_USERNAME"`"
  fi
  if [[ -z $PASSWORD ]]; then
    read -r -s -p  "Enter ${USER_DESCRIPTION} password: " ${USER_CATEGORY}_PASSWORD
    while [[ -z "${USER_CATEGORY}_PASSWORD" ]]
    do
      read -r -s -p  "No input entered, Enter ${USER_DESCRIPTION} password: " ${USER_CATEGORY}_PASSWORD
    done
  fi
  USERNAME=`eval "echo \\$${USER_CATEGORY}_USERNAME"`
  PASSWORD=`eval "echo \\$${USER_CATEGORY}_PASSWORD"`
  if [ "$USER_CATEGORY" != "MONITOR" ]; then
    check_user=`mysql_exec "SELECT user,host FROM mysql.user where user='$USERNAME' and host='%';"`
    if [[ -z "$check_user" ]]; then
      mysql_exec "CREATE USER $USERNAME@'%' IDENTIFIED BY '$PASSWORD';"
      check_cmd $? "Cannot add Percona XtraDB Cluster application user : '$USERNAME' (GRANT)"
      proxysql_exec "INSERT INTO mysql_users (username,password,active,default_hostgroup) values ('$USERNAME','$PASSWORD',1,$HOSTGROUP_ID);LOAD MYSQL USERS TO RUNTIME;SAVE MYSQL USERS FROM RUNTIME;SAVE MYSQL USERS TO DISK;"
      check_cmd $? "Cannot add Percona XtraDB Cluster application user : '$USERNAME' (mysql_users update)"
      echo -e "\n\nPercona XtraDB Cluster application user '$USERNAME'@'%' has been added with USAGE privilege, please make sure to grant appropriate privileges\n"
    else
      check_user=`proxysql_exec "SELECT username FROM mysql_users where username='$USERNAME'"`
      if [[ -z "$check_user" ]]; then
        echo -e "\nApplication user '${USERNAME}'@'%' already present in Percona XtraDB Cluster.\n"
        proxysql_exec "INSERT INTO mysql_users (username,password,active,default_hostgroup) values ('$USERNAME','$PASSWORD',1,$HOSTGROUP_ID);LOAD MYSQL USERS TO RUNTIME;SAVE MYSQL USERS FROM RUNTIME;SAVE MYSQL USERS TO DISK;"
        check_cmd $? "Cannot add Percona XtraDB Cluster application user : '$USERNAME' (mysql_users update)"
      else
	echo -e "\nApplication user '${USERNAME}'@'%' already present in Percona XtraDB Cluster and ProxySQL database.\n"
      fi
    fi
  fi
}

# Auto configure Percona XtraDB Cluster nodes into ProxySQL
enable_proxysql(){
  # Checking proxysql binary location
  if [[ ! -e $(which proxysql 2> /dev/null) ]]; then
    echo "The proxysql binary was not found, please install the ProxySQL package"  
    exit 1
  elif [[ ! -e $(which proxysql_galera_checker 2> /dev/null) ]] ;then
    echo "The proxysql_galera_checker binary was not found, please check ProxySQL package installation"  
    exit 1
  elif [[ ! -e $(which proxysql_node_monitor 2> /dev/null) ]]; then
    echo "The proxysql_node_monitor binary was not found, please check ProxySQL package installation"  
    exit 1
  else
    PROXYSQL=$(which proxysql)
    PROXYSQL_GALERA_CHECK=$(which proxysql_galera_checker)
    PROXYSQL_NODE_MONITOR=$(which proxysql_node_monitor)
  fi
  # Check for existing proxysql process
  if ! pidof proxysql >/dev/null ; then
    echo "ProxySQL is not running; please start proxysql service"
    exit 1
  fi
  proxysql_connection_check

  #modifying proxysql-admin.cnf file with command line proxysql user credentials if you dont use --config-file option.
  if [ -z "${config_file}" ]; then 
    sed -i "s|[ \t]*PROXYSQL_USERNAME[ \t]*=.*$| PROXYSQL_USERNAME=\"${PROXYSQL_USERNAME}\"|" /etc/proxysql-admin.cnf
    sed -i "s|[ \t]*PROXYSQL_PASSWORD[ \t]*=.*$| PROXYSQL_PASSWORD=\"${PROXYSQL_PASSWORD}\"|" /etc/proxysql-admin.cnf
    sed -i "s|[ \t]*PROXYSQL_HOSTNAME[ \t]*=.*$| PROXYSQL_HOSTNAME=\"${PROXYSQL_HOSTNAME}\"|" /etc/proxysql-admin.cnf
    sed -i "s|[ \t]*PROXYSQL_PORT[ \t]*=.*$| PROXYSQL_PORT=\"${PROXYSQL_PORT}\"|" /etc/proxysql-admin.cnf
  fi

  pxc_connection_check
  echo -e "\nConfiguring ProxySQL monitoring user.."
  user_input_check MONITOR "ProxySQL monitor user"

  check_user=`mysql_exec "SELECT user,host FROM mysql.user where user='$MONITOR_USERNAME' and host='%';"`
  if [[ -z "$check_user" ]]; then
    mysql_exec "CREATE USER $MONITOR_USERNAME@'%' IDENTIFIED BY '$MONITOR_PASSWORD';"
    check_cmd $?  "Cannot create the ProxySQL monitoring user"
    proxysql_exec "update global_variables set variable_value='$MONITOR_USERNAME' where variable_name='mysql-monitor_username'; update global_variables set variable_value='$MONITOR_PASSWORD' where variable_name='mysql-monitor_password'; "
    check_cmd $?  "Cannot set the mysql-monitor variables in ProxySQL"
    proxysql_exec "LOAD MYSQL VARIABLES TO RUNTIME;SAVE MYSQL VARIABLES TO DISK;"
    echo -e "\n\nUser '$MONITOR_USERNAME'@'%' has been added with USAGE privilege\n"
  else
    echo -e "\n"
    read -p "Monitoring user is already present in Percona XtraDB Cluster. Would you like to proceed with existing username and password [y/n] ? " check_param
    case $check_param in
      y|Y)
        read -r -s -p  "Please enter the password you have assigned to monitoring user '$MONITOR_USERNAME': " MONITOR_PASSWORD
        proxysql_exec "update global_variables set variable_value='$MONITOR_USERNAME' where variable_name='mysql-monitor_username'; update global_variables set variable_value='$MONITOR_PASSWORD' where variable_name='mysql-monitor_password'; "
        check_cmd $?  "Cannot set the mysql-monitor variables in ProxySQL"
        proxysql_exec "LOAD MYSQL VARIABLES TO RUNTIME;SAVE MYSQL VARIABLES TO DISK;"
      ;;
      n|N)
        proxysql_exec "update global_variables set variable_value='$MONITOR_USERNAME' where variable_name='mysql-monitor_username'; update global_variables set variable_value='$MONITOR_PASSWORD' where variable_name='mysql-monitor_password'; "
        check_cmd $?  "Cannot set the mysql-monitor variables in ProxySQL"
        proxysql_exec "LOAD MYSQL VARIABLES TO RUNTIME;SAVE MYSQL VARIABLES TO DISK;"
        echo -e "\n\nUser '$MONITOR_USERNAME'@'%' has been updated in ProxySQL database. Please make sure the credentials are same in Percona XtraDB Cluster\n"
      ;;
      *)
        echo "Please type [y/n]! Terminating.."
        exit 1
      ;;
    esac
  fi

  echo -e "\nConfiguring Percona XtraDB Cluster application users (READ and WRITE) to connect through ProxySQL"
  user_input_check PXC_APP_WRITE "Percona XtraDB Cluster application write user" $WRITE_HOSTGROUP_ID
  user_input_check PXC_APP_READ "Percona XtraDB Cluster application read user" $READ_HOSTGROUP_ID

  # Adding Percona XtraDB Cluster nodes to ProxySQL
  echo -e "\nAdding the Percona XtraDB Cluster server nodes to ProxySQL"
  if [ $RW_MODE == "loadbal" ]; then
    proxysql_exec "DELETE FROM mysql_servers WHERE hostgroup_id=$WRITE_HOSTGROUP_ID"
    wsrep_address=(`mysql_exec "show status like 'wsrep_incoming_addresses'" | awk '{print $2}' | sed 's|,| |g'`)
    for i in "${wsrep_address[@]}"; do	
      ws_ip=$(echo $i | cut -d':' -f1)
      ws_port=$(echo $i | cut -d':' -f2)
      proxysql_exec "INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,comment) VALUES ('$ws_ip',$WRITE_HOSTGROUP_ID,$ws_port,1000,'READWRITE');"
      check_cmd $? "Failed to add the Percona XtraDB Cluster server node $ws_ip:$ws_port"
    done
    proxysql_exec "LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;"

  elif [ $RW_MODE == "singlewrite" ]; then
    proxysql_exec "DELETE FROM mysql_servers WHERE hostgroup_id in ($WRITE_HOSTGROUP_ID,$READ_HOSTGROUP_ID)"
    if [ -z $WRITE_NODE ]; then
      read -r -p  "You have not given writer node info through command line/config-file. Please enter writer-node info (eg : 127.0.0.1:3306): " WRITE_NODE
      writer_ws_ip=$(echo $WRITE_NODE | cut -d':' -f1)
      writer_ws_port=$(echo $WRITE_NODE | cut -d':' -f2)
      printf "[client]\nuser=${CLUSTER_USERNAME}\npassword=${CLUSTER_PASSWORD}\nhost=${writer_ws_ip}\nport=${writer_ws_port}\n" | mysql --defaults-file=/dev/stdin --protocol=tcp -Bse "select @@port" >/dev/null 2>/dev/null
      if [ $? -ne 0 ]; then 
        echo -e "\nERROR: Could not establish connection to write node $writer_ws_ip:$writer_ws_port. Terminating!\n"; 
        proxysql_exec "DELETE FROM mysql_users WHERE default_hostgroup in ($WRITE_HOSTGROUP_ID,$READ_HOSTGROUP_ID);"
        check_cmd $? "Cannot delete Percona XtraDB Cluster users from ProxySQL"
        exit 1
      fi
    else
      writer_ws_ip=$(echo $WRITE_NODE | cut -d':' -f1)
      writer_ws_port=$(echo $WRITE_NODE | cut -d':' -f2)
      printf "[client]\nuser=${CLUSTER_USERNAME}\npassword=${CLUSTER_PASSWORD}\nhost=${writer_ws_ip}\nport=${writer_ws_port}\n" | mysql --defaults-file=/dev/stdin --protocol=tcp -Bse "select @@port" >/dev/null 2>/dev/null
      if [ $? -ne 0 ]; then 
        echo -e "\nERROR: Could not establish connection to write node $writer_ws_ip:$writer_ws_port. Terminating!\n"; 
        proxysql_exec "DELETE FROM mysql_users WHERE default_hostgroup in ($WRITE_HOSTGROUP_ID,$READ_HOSTGROUP_ID);"
        check_cmd $? "Cannot delete Percona XtraDB Cluster users from ProxySQL"
        exit 1
      fi
    fi

    proxysql_exec "DELETE FROM mysql_servers WHERE hostgroup_id=$WRITE_HOSTGROUP_ID"
    wsrep_address=(`mysql_exec "show status like 'wsrep_incoming_addresses'" | awk '{print $2}' | sed 's|,| |g'`)
    for i in "${wsrep_address[@]}"; do	
      ws_ip=$(echo $i | cut -d':' -f1)
      ws_port=$(echo $i | cut -d':' -f2)
      if [ "$ws_ip" == "$writer_ws_ip" -a "$ws_port" == "$writer_ws_port" ]; then
        proxysql_exec "INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,comment) VALUES ('$writer_ws_ip',$WRITE_HOSTGROUP_ID,$writer_ws_port,1000,'WRITE');"
        check_cmd $? "Failed to add the Percona XtraDB Cluster server node $writer_ws_ip:$writer_ws_port"
      else
        proxysql_exec "INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,comment) VALUES ('$ws_ip',$READ_HOSTGROUP_ID,$ws_port,1000,'READ');"
        check_cmd $? "Failed to add the Percona XtraDB Cluster server node $ws_ip:$ws_port"
      fi
    done
    proxysql_exec "LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;"
  fi

  # Adding Percona XtraDB Cluster monitoring scripts
  # Adding proxysql galera check scheduler
  proxysql_exec "DELETE FROM SCHEDULER WHERE ID=10;"
  check_cmd $?
  proxysql_exec "INSERT  INTO SCHEDULER (id,active,interval_ms,filename,arg1,arg2,arg3,arg4,arg5) VALUES (10,1,$GALERA_CHECK_INTERVAL,'$PROXYSQL_GALERA_CHECK',$WRITE_HOSTGROUP_ID,$READ_HOSTGROUP_ID,${#wsrep_address[@]},1,'/var/lib/proxysql/proxysql_galera_check.log');"
  check_cmd $? "Failed to add the Percona XtraDB Cluster monitoring scheduler in ProxySQL"

  # Adding Percona XtraDB Cluster membership checking scheduler
  proxysql_exec "DELETE FROM SCHEDULER WHERE ID=11;"
  check_cmd $?
  proxysql_exec "INSERT  INTO SCHEDULER (id,active,interval_ms,filename,arg1,arg2,arg3) VALUES (11,1,5000,'$PROXYSQL_NODE_MONITOR',$WRITE_HOSTGROUP_ID,$READ_HOSTGROUP_ID,'/var/lib/proxysql/proxysql_node_monitor.log');"
  check_cmd $? "Failed to add the Percona XtraDB Cluster membership checking scheduler in ProxySQL"

  proxysql_exec "LOAD SCHEDULER TO RUNTIME;SAVE SCHEDULER TO DISK;"
}

# Removing PXC configuration from proxysql
disable_proxysql(){
  proxysql_connection_check
  proxysql_exec "DELETE FROM mysql_users WHERE default_hostgroup in ($WRITE_HOSTGROUP_ID,$READ_HOSTGROUP_ID);"
  check_cmd $? "Cannot delete Percona XtraDB Cluster users from ProxySQL"
  proxysql_exec "DELETE FROM mysql_servers WHERE hostgroup_id in ($WRITE_HOSTGROUP_ID,$READ_HOSTGROUP_ID);"
  check_cmd $? "Cannot delete Percona XtraDB Cluster nodes from ProxySQL"
  proxysql_exec "DELETE FROM SCHEDULER WHERE ID IN (10,11);"
  check_cmd $? "Cannot delete Galera checker and node monitoring scheduler from ProxySQL"
  proxysql_exec "LOAD MYSQL USERS TO RUNTIME;SAVE MYSQL USERS TO DISK;LOAD SCHEDULER TO RUNTIME;SAVE SCHEDULER TO DISK;LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;"
}

adduser(){
  proxysql_connection_check
  pxc_connection_check
  echo -e "\nAdding Percona XtraDB Cluster application user to ProxySQL database"
  echo -n "Enter Percona XtraDB Cluster application user name: "
  read -r PXC_APP_WRITE_USERNAME
  while [[ -z "$PXC_APP_WRITE_USERNAME" ]]
  do
    echo -n "No input entered, Enter Percona XtraDB Cluster application user name: "
    read -r PXC_APP_WRITE_USERNAME
  done
  read -r -s -p  "Enter Percona XtraDB Cluster application user password: " PXC_APP_WRITE_PASSWORD
  while [[ -z "$PXC_APP_WRITE_PASSWORD" ]]
  do
    read -r -s -p  "No input entered, Enter Percona XtraDB Cluster application user password: " PXC_APP_WRITE_PASSWORD
  done

  check_user=`proxysql_exec "SELECT username FROM mysql_users where username='$PXC_APP_WRITE_USERNAME'"`
  if [[ -z "$check_user" ]]; then
    check_pxc_user=`mysql_exec "SELECT user,host FROM mysql.user where user='$PXC_APP_WRITE_USERNAME'"`
    if [[ -z "$check_pxc_user" ]]; then
      echo -e "\n\n"
      read -p "Application user '$PXC_APP_WRITE_USERNAME' does not exist in Percona XtraDB Cluster. Would you like to proceed [y/n] ? " check_param
      case $check_param in
        y|Y)
          proxysql_exec "INSERT INTO mysql_users (username,password,active,default_hostgroup) values ('$PXC_APP_WRITE_USERNAME','$PXC_APP_WRITE_PASSWORD',1,$WRITE_HOSTGROUP_ID);LOAD MYSQL USERS TO RUNTIME;SAVE MYSQL USERS FROM RUNTIME;SAVE MYSQL USERS TO DISK;"
          check_cmd $? "Cannot add Percona XtraDB Cluster application user : '$PXC_APP_WRITE_USERNAME' to ProxySQL database"
        ;;
        n|N)
          exit 1
        ;;
        *)
          echo "Please type [y/n]! Terminating.."
          exit 1
        ;;
      esac
    else
      proxysql_exec "INSERT INTO mysql_users (username,password,active,default_hostgroup) values ('$PXC_APP_WRITE_USERNAME','$PXC_APP_WRITE_PASSWORD',1,$WRITE_HOSTGROUP_ID);LOAD MYSQL USERS TO RUNTIME;SAVE MYSQL USERS FROM RUNTIME;SAVE MYSQL USERS TO DISK;"
      check_cmd $? "Cannot add Percona XtraDB Cluster application user : '$PXC_APP_WRITE_USERNAME' to ProxySQL database"
    fi
  else
    echo -e "\nERROR: Application user '$PXC_APP_WRITE_USERNAME' already exist in ProxySQL database. Terminating.."
    exit 1
  fi
}

if [ "$enable" == 1 -o "$disable" == 1 -o "$adduser" == 1 ]; then
  if [ "$enable" == 1 ];then
    enable_proxysql
    echo "ProxySQL configuration completed!"
  fi
  if [ "$disable" == 1 ];then  
    disable_proxysql
    echo "ProxySQL configuration removed!"
  fi
  if [ "$adduser" == 1 ];then  
    adduser
    echo -e "\nAdded Percona XtraDB Cluster application user to ProxySQL database!"
  fi
else
  echo "Usage: proxysql-admin <user credentials> {enable|disable}"
  usage
fi

