# Created by: Jake Smith <jake@xz.cx>

# $FreeBSD$

PORTNAME=	proxysql-admin

PORTVERSION=	1.3.7

CATEGORIES=	databases

MAINTAINER=	jake@xz.cx

COMMENT=	Configures Percona XtraDB cluster nodes into ProxySQL

LICENSE=	GPLv2

RUN_DEPENDS=	bash:shells/bash \

		getopt:misc/getopt \

		pidof:sysutils/pidof

NO_ARCH=	yes

NO_BUILD=	yes

USES=		shebangfix

SHEBANG_FILES=	${PORTNAME} proxysql_*

USE_GITHUB=	yes

GH_ACCOUNT=	percona

GH_PROJECT=	proxysql-admin-tool

GH_TAGNAME=	${PORTVERSION}

do-install:

	${INSTALL_SCRIPT} ${WRKSRC}/proxysql-admin \

		${STAGEDIR}${PREFIX}/bin/proxysql-admin

	${MKDIR} ${STAGEDIR}${PREFIX}/libexec/${PORTNAME}

	${INSTALL_SCRIPT} ${WRKSRC}/proxysql_galera_checker \

		${STAGEDIR}${PREFIX}/libexec/${PORTNAME}/

	${INSTALL_SCRIPT} ${WRKSRC}/proxysql_node_monitor \

		${STAGEDIR}${PREFIX}/libexec/${PORTNAME}/

	${INSTALL_DATA} ${WRKSRC}/proxysql-admin.cnf \

		${STAGEDIR}${PREFIX}/etc/proxysql-admin.cnf.sample

post-install:

	${INSTALL_DATA} ${FILESDIR}/proxysql-admin.conf \

		${STAGEDIR}${PREFIX}/etc/newsyslog.conf.d/

.include <bsd.port.mk>

TIMESTAMP = 1502718138

SHA256 (percona-proxysql-admin-tool-1.3.7_GH0.tar.gz) = 8ab8d0b3ffb35c22956703fd98574e74594de85fe87e89747fe68220eee8f4cf

SIZE (percona-proxysql-admin-tool-1.3.7_GH0.tar.gz) = 23259

--- proxysql-admin.orig	2017-08-15 14:06:53 UTC

+++ proxysql-admin

@@ -64,9 +64,9 @@ usage () {

 }

 # Check if we have a functional getopt(1)

-if ! getopt --test

+if ! /usr/local/bin/getopt --test

   then

-  go_out="$(getopt --options=edv --longoptions=config-file:,proxysql-username:,proxysql-password::,proxysql-hostname:,proxysql-port:,cluster-username:,cluster-password::,cluster-hostname:,cluster-port:,monitor-username:,monitor-password:,cluster-app-username:,cluster-app-password:,node-check-interval:,quick-demo,mode:,write-node:,enable,disable,adduser,syncusers,version,help \

+  go_out="$(/usr/local/bin/getopt --options=edv --longoptions=config-file:,proxysql-username:,proxysql-password::,proxysql-hostname:,proxysql-port:,cluster-username:,cluster-password::,cluster-hostname:,cluster-port:,monitor-username:,monitor-password:,cluster-app-username:,cluster-app-password:,node-check-interval:,quick-demo,mode:,write-node:,enable,disable,adduser,syncusers,version,help \

   --name="$(basename "$0")" -- "$@")"

   test $? -eq 0 || exit 1

   eval set -- "$go_out"

@@ -80,12 +80,12 @@ fi

 if ! echo "$go_out" | grep -q 'config-file'; then

   if ! echo "$go_out" | grep -q 'quick-demo'; then

     # Reading default variables from default configuration file location.

-    if [ -e "/etc/proxysql-admin.cnf" ]; then

-      # Loading default configuration from /etc/proxysql-admin.cnf

-      source /etc/proxysql-admin.cnf

-      CONFIG_FILE="/etc/proxysql-admin.cnf"

+    if [ -e "/usr/local/etc/proxysql-admin.cnf" ]; then

+      # Loading default configuration from /usr/local/etc/proxysql-admin.cnf

+      source /usr/local/etc/proxysql-admin.cnf

+      CONFIG_FILE="/usr/local/etc/proxysql-admin.cnf"

     else

-      echo "WARNING! Default configuration file (/etc/proxysql-admin.cnf) does not exist"

+      echo "WARNING! Default configuration file (/usr/local/etc/proxysql-admin.cnf) does not exist"

     fi

   fi

 fi

@@ -362,13 +362,13 @@ if [ $MODE == "loadbal" ]; then

   WRITE_HOSTGROUP_ID=10

   READ_HOSTGROUP_ID=10

   if [ -e "${CONFIG_FILE}" ]; then

-    sudo sed -i "0,/^[ \t]*export MODE[ \t]*=.*$/s|^[ \t]*export MODE[ \t]*=.*$|export MODE=\"loadbal\"|" "${CONFIG_FILE}"

+    sed -i "" "0,/^[ 	]*export MODE[ 	]*=.*$/s|^[ 	]*export MODE[ 	]*=.*$|export MODE=\"loadbal\"|" "${CONFIG_FILE}"

   fi

 elif [ $MODE == "singlewrite" ]; then

   WRITE_HOSTGROUP_ID=10

   READ_HOSTGROUP_ID=11

   if [ -e "${CONFIG_FILE}" ]; then

-    sudo sed -i "0,/^[ \t]*export MODE[ \t]*=.*$/s|^[ \t]*export MODE[ \t]*=.*$|export MODE=\"singlewrite\"|" "${CONFIG_FILE}"

+    sed -i "" "0,/^[ 	]*export MODE[ 	]*=.*$/s|^[ 	]*export MODE[ 	]*=.*$|export MODE=\"singlewrite\"|" "${CONFIG_FILE}"

   fi

 fi

@@ -413,7 +413,7 @@ check_cmd(){

 check_proxysql(){

   if ! pidof proxysql >/dev/null ; then

-    echo "ProxySQL is not running, please check the error log at /var/lib/proxysql/proxysql.log"

+    echo "ProxySQL is not running, please check the error log at /var/log/proxysql.log"

     exit 1

   fi

 }

@@ -574,7 +574,7 @@ enable_proxysql(){

     proxysql_exec "DELETE FROM mysql_query_rules WHERE destination_hostgroup in ($WRITE_HOSTGROUP_ID,$READ_HOSTGROUP_ID)"

     if [ -z "$QUICK_DEMO" ]; then

       if [ -z "$WRITE_NODE" ]; then

-        writer_ws_ip=$(mysql_exec "show variables like 'wsrep_provider_options'" | grep -o -P '(?<=base_host =).*(?=; base_port)' | xargs)

+        writer_ws_ip=$(mysql_exec "show variables like 'wsrep_provider_options'" | sed -r 's/.*base_host =( [^ ]+);.*/\1/' | xargs)

         writer_ws_port=$CLUSTER_PORT

       else

         writer_ws_ip=$(echo "$WRITE_NODE" | awk -F':' '{print $1}')

@@ -591,7 +591,7 @@ enable_proxysql(){

         fi

       fi

     else

-      writer_ws_ip=$(mysql_exec "show variables like 'wsrep_provider_options'" | grep -o -P '(?<=base_host =).*(?=; base_port)' | xargs)

+      writer_ws_ip=$(mysql_exec "show variables like 'wsrep_provider_options'" | sed -r 's/.*base_host =( [^ ]+);.*/\1/' | xargs)

       writer_ws_port=$CLUSTER_PORT

     fi

     proxysql_exec "DELETE FROM mysql_servers WHERE hostgroup_id=$WRITE_HOSTGROUP_ID"

@@ -629,7 +629,7 @@ enable_proxysql(){

   else

     NUMBER_WRITERS=0

   fi

-  proxysql_exec "INSERT  INTO SCHEDULER (id,active,interval_ms,filename,arg1,arg2,arg3,arg4,arg5) VALUES (10,1,$NODE_CHECK_INTERVAL,'$PROXYSQL_GALERA_CHECK',$WRITE_HOSTGROUP_ID,$READ_HOSTGROUP_ID,$NUMBER_WRITERS,1,'/var/lib/proxysql/proxysql_galera_check.log');"

+  proxysql_exec "INSERT  INTO SCHEDULER (id,active,interval_ms,filename,arg1,arg2,arg3,arg4,arg5) VALUES (10,1,$NODE_CHECK_INTERVAL,'$PROXYSQL_GALERA_CHECK',$WRITE_HOSTGROUP_ID,$READ_HOSTGROUP_ID,$NUMBER_WRITERS,1,'/var/log/proxysql_galera_check.log');"

   check_cmd $? "Failed to add the Percona XtraDB Cluster monitoring scheduler in ProxySQL. Please check username, password and other options for connecting to ProxySQL database"

   # Adding Percona XtraDB Cluster membership checking scheduler

@@ -722,13 +722,13 @@ syncusers() {

   MYSQLVER=$(mysql_exec "SELECT VERSION();" | tail -1 | cut -d'.' -f1,2 )

   case $MYSQLVER in

-    5.6)

-      password_field="Password";;

     5.7)

       password_field="authentication_string";;

+    *)

+      password_field="Password";;

   esac

-  

-  mysql_users=$(mysql_exec "SELECT User,${password_field} FROM mysql.user where ${password_field}!=''" | sed 's/\t/,/g' | egrep -v "User,${password_field}|mysql.sys" | sort | uniq )

+

+  mysql_users=$(mysql_exec "SELECT User,${password_field} FROM mysql.user where ${password_field}!=''" | sed 's/	/,/g' | egrep -v "User,${password_field}|mysql.sys" | sort | uniq )

   check_cmd $? "Failed to load user list from Percona XtraDB Cluster. Please check username, password and other options for connecting to Percona XtraDB Cluster"

   #Checking whether user is part of proxysql admin user list

   proxysql_admin_user_check(){

@@ -741,7 +741,7 @@ syncusers() {

     fi

   }

   # Get current ProxySQL users and filter out header row

-  proxysql_users=$(proxysql_exec "SELECT username,password FROM mysql_users where password!=''" | sed 's/\t/,/g' | egrep -v "username,password" | sort | uniq )

+  proxysql_users=$(proxysql_exec "SELECT username,password FROM mysql_users where password!=''" | sed 's/	/,/g' | egrep -v "username,password" | sort | uniq )

   check_cmd $? "Failed to load user list from ProxySQL database. Please check username, password and other options for connecting to ProxySQL database"

   echo -e "\nSyncing user accounts from Percona XtraDB Cluster to ProxySQL"

@@ -805,7 +805,7 @@ if [ "$ENABLE" == 1 ] || [ "$DISABLE" ==

     echo -e "\nProxySQL read/write configuration mode is ${BD}$MODE${NBD}"

     enable_proxysql

     echo -e "\nProxySQL configuration completed!\n"

-    PROXYSQL_CLIENT_PORT=$(proxysql_exec "SELECT * FROM runtime_global_variables WHERE variable_name='mysql-interfaces'" | awk '{print $2}' | grep -o -P '(?<=:).*(?=;)')

+    PROXYSQL_CLIENT_PORT=$(proxysql_exec "SELECT * FROM runtime_global_variables WHERE variable_name='mysql-interfaces'" | awk -F ':' '/:/ {print $NF}')

     echo -e "ProxySQL has been successfully configured to use with Percona XtraDB Cluster\n"

     echo -e "You can use the following login credentials to connect your application through ProxySQL\n"

     if [ -z "$QUICK_DEMO" ]; then

--- proxysql-admin.cnf.orig	2017-06-27 09:48:53 UTC

+++ proxysql-admin.cnf

@@ -1,3 +1,6 @@

+# add scheduler scripts to path

+export PATH=$PATH:/usr/local/libexec/proxysql-admin

+

 # proxysql admin interface credentials.

 export PROXYSQL_USERNAME="admin"

 export PROXYSQL_PASSWORD="admin"

--- proxysql_galera_checker.orig	2017-08-15 14:06:53 UTC

+++ proxysql_galera_checker

@@ -1,10 +1,10 @@

 #!/usr/local/bin/bash

 ## inspired by Percona clustercheck.sh

-if [ -f /etc/proxysql-admin.cnf ]; then

-  source /etc/proxysql-admin.cnf

+if [ -f /usr/local/etc/proxysql-admin.cnf ]; then

+  source /usr/local/etc/proxysql-admin.cnf

 else

-  echo "Assert! proxysql-admin configuration file :/etc/proxysql-admin.cnf does not exists, Terminating!" >> $ERR_FILE

+  echo "Assert! proxysql-admin configuration file :/usr/local/etc/proxysql-admin.cnf does not exists, Terminating!" >> $ERR_FILE

   exit 1

 fi

 #

@@ -50,11 +50,11 @@ WRITER_IS_READER="${4:-1}"

 ERR_FILE="${5:-/dev/null}"

 CHECK_STATUS=0

 #Running proxysql_node_monitor script.

-if [ ! -f /usr/bin/proxysql_node_monitor ] ;then

-  echo "`date` ERROR! Could not run /usr/bin/proxysql_node_monitor. Monitoring script does not exists in default location. Terminating" >> ${ERR_FILE}

+if [ ! -f /usr/local/libexec/proxysql-admin/proxysql_node_monitor ] ;then

+  echo "`date` ERROR! Could not run /usr/local/libexec/proxysql-admin/proxysql_node_monitor. Monitoring script does not exists in default location. Terminating" >> ${ERR_FILE}

   exit 1  

 else

-  /usr/bin/proxysql_node_monitor $HOSTGROUP_WRITER_ID $HOSTGROUP_READER_ID /var/lib/proxysql/proxysql_node_monitor.log

+  /usr/local/libexec/proxysql-admin/proxysql_node_monitor $HOSTGROUP_WRITER_ID $HOSTGROUP_READER_ID /var/log/proxysql_node_monitor.log

 fi

--- proxysql_node_monitor.orig	2017-08-15 14:06:53 UTC

+++ proxysql_node_monitor

@@ -2,10 +2,10 @@

 # This script will assist to setup Percona XtraDB cluster ProxySQL monitoring script.

 #####################################################################################

-if [ -f /etc/proxysql-admin.cnf ]; then

-  source /etc/proxysql-admin.cnf

+if [ -f /usr/local/etc/proxysql-admin.cnf ]; then

+  source /usr/local/etc/proxysql-admin.cnf

 else

-  echo "`date` Assert! proxysql-admin configuration file :/etc/proxysql-admin.cnf does not exists, Terminating!" >> $ERR_FILE

+  echo "`date` Assert! proxysql-admin configuration file :/usr/local/etc/proxysql-admin.cnf does not exists, Terminating!" >> $ERR_FILE

   exit 1

 fi

@@ -41,7 +41,7 @@ mysql_exec() {

 # Update Percona XtraDB Cluster nodes in ProxySQL database

 update_cluster(){

-  current_hosts=(`proxysql_exec "SELECT hostname,port FROM mysql_servers where hostgroup_id in ( $WRITE_HOSTGROUP_ID, $READ_HOSTGROUP_ID )" | sed 's|\t|:|g' | tr '\n' ' '`)

+  current_hosts=(`proxysql_exec "SELECT hostname,port FROM mysql_servers where hostgroup_id in ( $WRITE_HOSTGROUP_ID, $READ_HOSTGROUP_ID )" | sed 's|	|:|g' | tr '\n' ' '`)

   wsrep_address=(`mysql_exec "SHOW STATUS LIKE 'wsrep_incoming_addresses'" | awk '{print $2}' | sed 's|,| |g'`)

   if [ ${#wsrep_address[@]} -eq 0 ]; then

     echo "`date` Alert! wsrep_incoming_addresses is empty. Terminating!" >> $ERR_FILE

@@ -104,7 +104,7 @@ mode_change_check(){

   if [[ -n "$checkwriter_hid" ]]; then

     proxysql_exec "UPDATE mysql_servers set hostgroup_id = $READ_HOSTGROUP_ID, comment='READ', weight=1000 WHERE comment='WRITE' and status='OFFLINE_SOFT'"

     check_cmd $? "Cannot update Percona XtraDB Cluster writer node in ProxySQL database, Please check proxysql credentials"

-    current_hosts=(`proxysql_exec "SELECT hostname,port FROM mysql_servers WHERE  status='ONLINE' and comment='READ'  ORDER BY random() LIMIT 1" | sed 's|\t|:|g' | tr '\n' ' '`)

+    current_hosts=(`proxysql_exec "SELECT hostname,port FROM mysql_servers WHERE  status='ONLINE' and comment='READ'  ORDER BY random() LIMIT 1" | sed 's|	|:|g' | tr '\n' ' '`)

     ws_ip=$(echo $current_hosts | cut -d':' -f1)

     ws_port=$(echo $current_hosts | cut -d':' -f2)

     proxysql_exec "UPDATE mysql_servers set hostgroup_id = $WRITE_HOSTGROUP_ID, comment='WRITE', weight=1000000 WHERE hostname='$ws_ip' and port=$ws_port"

# configuration file for newsyslog for net-snmp

#

# see newsyslog.conf(5) for details

#

# logfilename          [owner:group]    mode count size when  flags [/pid_file] [sig_num]

/var/log/proxysql_node_monitor.log  			644  7	   100	*     J

/var/log/proxysql_galera_check.log			644  7	   100	*     J

The ProxySQL Admin (proxysql-admin) solution configures

Percona XtraDB cluster nodes into ProxySQL.

WWW: https://github.com/percona/proxysql-admin-tool

Pre-requisites before use

- ProxySQL and Cluster should be up and running

- MySQL client (or fork) should be installed locally

bin/proxysql-admin

libexec/proxysql-admin/proxysql_galera_checker

libexec/proxysql-admin/proxysql_node_monitor

etc/newsyslog.conf.d/proxysql-admin.conf

@sample etc/proxysql-admin.cnf.sample