proxysql-status

#!/bin/bash -u
# This script will assist with configuring ProxySQL
# by querying the ProxySQL tables
# Version 2.0
###############################################################################################

# This program is copyright 2016-2020 Percona LLC and/or its affiliates.
#
# THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
# WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
# MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
#
# This program is free software; you can redistribute it and/or modify it under
# the terms of the GNU General Public License as published by the Free Software
# Foundation, version 2 or later
#
# You should have received a copy of the GNU General Public License version 2
# along with this program; if not, write to the Free Software Foundation, Inc.,
# 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA.

# shellcheck disable=SC2046,SC2181,SC1091

# Include the common functions
. $(dirname "${BASH_SOURCE[0]}")/proxysql-common

# Global variables
declare USER=""
declare PASSWORD=""
declare HOST=""
declare PORT=""
declare RUNTIME_OPTION=""
declare DUMP_ALL=1
declare DUMP_MAIN=0
declare DUMP_STATS=0
declare DUMP_MONITOR=0
declare DUMP_FILES=0
declare TABLE_FILTER=""
declare DUMP_STATS_RESET_TABLE=0

# The login-file is the path to an encrypted file that
# contains the credentials for proxysql, PXC cluster,
# monitor, and cluster-app logins.
declare    LOGIN_FILE=""
declare    LOGIN_PASSWORD=""
declare    LOGIN_PASSWORD_FILE=""

# Set this to 1 if the default user credentials from my.cnf
# are being used, set to 0 if the default my.cnf user credentials
# are not being used (default)
declare CREDENTIALS_FROM_CLIENT_CONFIG=0

#
# If set to 1, then calls to MySQL/ProxySQL via the MySQL client
# will have credentials passed via stdin rather than using bash
# process substitution.
#
declare   USE_STDIN_FOR_CREDENTIALS=0


function usage() {
    local path=$0
    cat << EOF
Usage example:
  $ ${path##*/} [options] [<USER> <PASSWORD> <HOST> <PORT>]

  Options:
    --files                 : display contents of proxysql-admin related files
    --main                  : display main tables (both on-disk and runtime)
    --monitor               : display monitor tables
    --runtime               : display runtime-related data
                              (implies --main)
    --stats                 : display stats tables
    --table=<table_name>    : display only tables that contain the table name
                              (note: this is a case-sensitive match)
    --with-stats-reset      : display _reset tables, by default _reset tables
                              will not be queried.

    --login-file=<login-file-path>
                            : Read login credentials from an encrypted file.
                              If the --login-password or --login-password-file
                              options are not specified, then the user
                              will be prompted for the password.
                              (command line options override any login file values)
    --login-password=<password>
                            : The key used to decrypt the encrypted login-file.
                              This cannot be used with --login-password-file.
    --login-password-file=<path>
                            : Read the key from a file using the <path>.
                              This cannot be used with --login-password
    --use-stdin-for-credentials
                            : If set, then the MySQL client will use stdin to send
                              credentials to the client (instead of process
                              substitution).
                              (default: process substitution is used)

  The default is to display all tables and files.

  If no credentials are specified (on the command line or via a login-file) then:
    1. The default MySQL client credentials are used (usually found
       in ~/.my.cnf), if they connect to a ProxySQL instance).
    2. If the default MySQL client credentials do not exist, or do not connect
       to a ProxySQL instance, then the credentials in /etc/proxysql-admin.cnf
       are used.

EOF
}


#
# Prints out the script version
#
# Globals:
#   PROXYSQL_ADMIN_VERSION
#
# Parameters:
#   None
#
function version()
{
    local path=$0
    printf "%s version %s\n" "${path##*/}" "${PROXYSQL_ADMIN_VERSION}"
}


#
# Executes an SQL query
#
# Globals:
#   USER
#   PASSWORD
#   HOST
#   PORT
#   LOGIN_PATH
#
# Arguments:
#   1: arguments to be passed to mysql
#   2: the query
#
function mysql_exec() {
  local args=$1
  local query=$2
  local retvalue
  local retoutput
  local defaults=""
  local default_auth=""

  # shellcheck disable=SC2086
  if [[ $CREDENTIALS_FROM_CLIENT_CONFIG -eq 0 ]]; then
    defaults=$(printf '[client]\nuser=%s\npassword="%s"\nhost=%s\nport=%s\n%s' \
      "${USER}" \
      "${PASSWORD}" \
      "${HOST}" \
      "${PORT}" \
      "${default_auth}"
    )

    if [[ $USE_STDIN_FOR_CREDENTIALS -eq 1 ]]; then
      retoutput=$(printf "%s" "${defaults}" | mysql --defaults-file=/dev/stdin --protocol=tcp --unbuffered --batch --silent ${args} -e "$query")
      retvalue=$?
    else
      retoutput=$(mysql --defaults-file=<(echo "${defaults}") --protocol=tcp --unbuffered --batch --silent ${args} -e "$query")
      retvalue=$?
    fi
  else
    retoutput=$(mysql ${args} -e "${query}")
    retvalue=$?
  fi

  if [[ -n $retoutput ]]; then
    printf "%s\n" "${retoutput}"
  fi
  return $retvalue
}


function parse_args() {
    local go_out=""

   # TODO: kennt, what happens if we don't have a functional getopt()?
    # Check if we have a functional getopt(1)
    if ! getopt --test; then
        go_out="$(getopt --options=hv --longoptions=runtime,main,stats,monitor,files,table:,with-stats-reset,login-file:,login-password:,login-password-file:,use-stdin-for-credentials,version,help \
        --name="$(basename "$0")" -- "$@")"
        check_cmd $? "$LINENO" "Script error: getopt() failed with arguments: $*"
        eval set -- "$go_out"
    fi

    while [[ $# -gt 0 ]];
    do
        arg="$1"
        case "$arg" in
            -- )
                shift
                break;;
            --runtime )
                shift
                RUNTIME_OPTION=" LIKE 'runtime_%'"
                DUMP_ALL=0
                DUMP_MAIN=1
                ;;
            --main )
                shift
                DUMP_ALL=0
                DUMP_MAIN=1
                ;;
            --stats )
                shift
                DUMP_ALL=0
                DUMP_STATS=1
                ;;
            --monitor )
                shift
                DUMP_ALL=0
                DUMP_MONITOR=1
                ;;
            --files )
                shift
                DUMP_ALL=0
                DUMP_FILES=1
                ;;
            --table )
                TABLE_FILTER=$2
                shift 2
                ;;
            --with-stats-reset )
                shift
                DUMP_STATS_RESET_TABLE=1
                ;;
            --login-file)
                LOGIN_FILE="$2"
                check_permission -e "$LINENO" "$LOGIN_FILE" "login-file"
                check_permission -r "$LINENO" "$LOGIN_FILE" "login-file"
                debug "$LINENO"  "--login-file specified, using : $LOGIN_FILE"
                shift 2
                ;;
            --login-password)
                if [[ -n $LOGIN_PASSWORD_FILE ]]; then
                    error "$LINENO" "--login-password cannot be used with --login-password-file"
                    exit 1
                fi
                LOGIN_PASSWORD="$2"
                shift 2
                ;;
            --login-password-file)
                if [[ -n $LOGIN_PASSWORD ]]; then
                    error "$LINENO" "--login-password-file cannot be used with --login-password"
                    exit 1
                fi
                LOGIN_PASSWORD_FILE="$2"
                shift 2
                ;;
            --use-stdin-for-credentials )
                USE_STDIN_FOR_CREDENTIALS=1
                shift
                ;;
            -v | --version )
                version
                exit 0
                ;;
            -h | --help )
                usage
                exit 0
                ;;
        esac
    done

    if [[ $# -eq 0 && -z $LOGIN_FILE ]]; then
        # If no credentials have been provided, try the default
        # mysql client credentials

        mysql -e "SHOW tables" 2>/dev/null | grep -q "runtime_proxysql_servers"
        if [[ $? -eq 0 ]]; then
            echo -e "Connecting to ProxySQL with the default MySQL client credentials"
            echo -e "Usually found in ~/.my.cnf"
            CREDENTIALS_FROM_CLIENT_CONFIG=1
        fi
    fi

    if [[ $CREDENTIALS_FROM_CLIENT_CONFIG -eq 0 ]]; then

        # Load the data if the login-file has been set
        # Run this before the command-line parsing, so that the command-line
        # options can override the login path settings
        if [[ -n $LOGIN_FILE ]]; then

            # Check for key
            if [[ -n $LOGIN_PASSWORD_FILE ]]; then
                #if [[ ! -e $LOGIN_PASSWORD_FILE ]]; then
                #    error "$LINENO" "Cannot read from the login-password file: $LOGIN_PASSWORD_FILE"
                #    exit 1
                #fi
                LOGIN_PASSWORD=$(cat "$LOGIN_PASSWORD_FILE")
                if [[ -z $LOGIN_PASSWORD ]]; then
                    error "$LINENO" "Did not find any data in the login-password file: $LOGIN_PASSWORD_FILE"
                    exit 1
                fi
            fi
            if [[ -z $LOGIN_PASSWORD ]]; then
                read -r -s -p  "Enter the login-file password:" LOGIN_PASSWORD
                echo
            fi

            # Extract the information
            load_login_file "$LINENO" "$LOGIN_FILE" "$LOGIN_PASSWORD"
            if [[ $? -ne 0 ]]; then
                error "$LINENO" "Cannot read the credentials from the login-file"
                exit 1
            fi

            [[ -n $PROXYSQL_USERNAME ]] && USER=$PROXYSQL_USERNAME;
            [[ -n $PROXYSQL_PASSWORD ]] && PASSWORD=$PROXYSQL_PASSWORD;
            [[ -n $PROXYSQL_HOSTNAME ]] && HOST=$PROXYSQL_HOSTNAME;
            [[ -n $PROXYSQL_PORT ]] && PORT=$PROXYSQL_PORT;

        elif [[ $# -eq 0 ]]; then
            # When no arguments are passed try to read from /etc/proxysql-admin.cnf
            if [[ ! -r /etc/proxysql-admin.cnf ]]; then
                error $LINENO "Cannot find /etc/proxysql-admin.cnf to read the credentials." \
                    "\nYou can either consider creating the cnf file or pass the credentials through command-line in the format <USER> <PASSWORD> <HOST> <PORT>"
                exit 1
            else
                source /etc/proxysql-admin.cnf
                USER=$PROXYSQL_USERNAME
                PASSWORD=$PROXYSQL_PASSWORD
                HOST=$PROXYSQL_HOSTNAME
                PORT=$PROXYSQL_PORT
            fi
            
        elif [[ $# -ne 4 ]]; then
            error "$LINENO" "Incorrect usage: Please use the format <USER> <PASSWORD> <HOST> <PORT>"
            usage
            exit 1
        else
            [[ -n ${1+} ]] && USER=$1
            [[ -n ${2+} ]] && PASSWORD=$2
            [[ -n ${3+} ]] && HOST=$3
            [[ -n ${4+} ]] && PORT=$4
        fi


        if [[ -z $USER || -z $PASSWORD || -z $HOST || -z $PORT ]]; then

            error "$LINENO" "One of the user, password, host, or port parameterd is missing."
            exit 1
        fi
    fi

}


parse_args "$@"

# Run a test to see if we can connect
TABLES=$(mysql_exec -BN "SELECT 1" >/dev/null)
if [[ $? -ne 0 ]]; then
    error "$LINENO" "Cannot connect to the server at $HOST:$PORT"
    echo -e "Please check that the address is correct and the server is online"
    exit 1
fi

if [[ $DUMP_ALL -eq 1 || $DUMP_MAIN -eq 1 ]]; then
    echo "............ DUMPING MAIN DATABASE ............"
    TABLES=$(mysql_exec -BN "SHOW TABLES $RUNTIME_OPTION" 2>/dev/null)
    for table in $TABLES
    do
        if [[ -n $TABLE_FILTER && $table != *${TABLE_FILTER}* ]]; then
            continue
        fi
        echo "***** DUMPING $table *****"
        mysql_exec -t "SELECT * FROM $table"
        echo "***** END OF DUMPING $table *****"
        echo ""
    done
    echo "............ END OF DUMPING MAIN DATABASE ............"
    echo ""
fi

if [[ $DUMP_ALL -eq 1 || $DUMP_STATS -eq 1 ]]; then
    echo "............ DUMPING STATS DATABASE ............"
    TABLES=$(mysql_exec -BN "SHOW TABLES FROM stats" 2> /dev/null)
    for table in $TABLES
    do
        if [[ -n $TABLE_FILTER && $table != *${TABLE_FILTER}* ]]; then
            continue
        fi
        # Dump _reset tables only if we specify option --with-stats-reset
        if [[ $DUMP_STATS_RESET_TABLE -eq 0 ]]; then
            if echo "$table" | grep -q "_reset$"; then
                continue
			      fi
        fi
        echo "***** DUMPING stats.$table *****"
        mysql_exec "-t --database=stats" "SELECT * FROM $table" 2> /dev/null
        echo "***** END OF DUMPING stats.$table *****"
        echo ""
    done
    echo "............ END OF DUMPING STATS DATABASE ............"
    echo ""
fi

if [[ $DUMP_ALL -eq 1 || $DUMP_MONITOR -eq 1 ]]; then
    echo "............ DUMPING MONITOR DATABASE ............"
    TABLES=$(mysql_exec -BN "SHOW TABLES FROM monitor" 2> /dev/null)
    for table in $TABLES
    do
        if [[ -n $TABLE_FILTER && $table != *${TABLE_FILTER}* ]]; then
            continue
        fi
        echo "***** DUMPING monitor.$table *****"
        mysql_exec "-t --database=monitor" "SELECT * FROM $table" 2> /dev/null
        echo "***** END OF DUMPING monitor.$table *****"
        echo ""
    done
    echo "............ END OF DUMPING MONITOR DATABASE ............"
    echo ""
fi

if [[ $DUMP_ALL -eq 1 || $DUMP_FILES -eq 1 ]]; then
    if [[ -z $TABLE_FILTER ]]; then
      if [[ -r "/var/lib/proxysql/host_priority.conf" ]]; then
        echo "............ DUMPING HOST PRIORITY FILE ............"
        cat /var/lib/proxysql/host_priority.conf 2>&1
        echo "............ END OF DUMPING HOST PRIORITY FILE ............"
      else
        echo "/var/lib/proxysql/host_priority.conf not found or not readble by you!"
      fi
      echo ""
      if [[ -r "/etc/proxysql-admin.cnf" ]]; then
        echo "............ DUMPING PROXYSQL ADMIN CNF FILE ............"
        cat /etc/proxysql-admin.cnf 2>&1
        echo "............ END OF DUMPING PROXYSQL ADMIN CNF FILE ............"
      else
        echo "/etc/proxysql-admin.cnf not found or not readble by you!" 
      fi
      echo ""
      if [[ -r "/etc/config.toml" ]]; then
        echo "............ DUMPING PERCONA SCHEDULER ADMIN CNF FILE ............"
        cat /etc/config.toml 2>&1
        echo "............ END OF DUMPING PERCONA SCHEDULER ADMIN CNF FILE ............"
      else
        echo "/etc/config.toml not found or not readble by you!"
      fi
      echo ""
    fi
fi