Logoj0ke.net Open Build Service > Projects > internetx:mysql5 > proxysql > proxysql-admin
Sign Up | Log In

File proxysql-admin of Package proxysql

 
1
#!/bin/bash
2
# This script will assist with configuring ProxySQL (currently only Percona XtraDB cluster in combination with ProxySQL is supported)
3
# Version 1.0
4
###############################################################################################
5
6
# This program is copyright 2016-2017 Percona LLC and/or its affiliates.
7
#
8
# THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
9
# WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
10
# MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
11
#
12
# This program is free software; you can redistribute it and/or modify it under
13
# the terms of the GNU General Public License as published by the Free Software
14
# Foundation, version 2 or later
15
#
16
# You should have received a copy of the GNU General Public License version 2
17
# along with this program; if not, write to the Free Software Foundation, Inc.,
18
# 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA.
19
20
# Make sure only root can run this script
21
if [ "$(id -u)" -ne 0 ]; then
22
  echo "ERROR: This script must be run as root!" 1>&2
23
  exit 1
24
fi
25
26
#bash prompt internal configuration
27
BD=$(tput bold)
28
NBD=$(tput sgr0)
29
30
# Internal variables
31
if [ -e "/dummypathnonexisting/.mylogin.cnf" ]; then
32
  echo "ERROR! /dummypathnonexisting/.mylogin.cnf found. This should not happen. Terminating";
33
  exit 1
34
else
35
  export HOME="/dummypathnonexisting"
36
fi
37
38
# Dispay script usage details
39
usage () {
40
  echo "Usage: [ options ]"
41
  echo "Options:"
42
  echo "  --config-file                      Read login credentials from a configuration file (overrides any login credentials specified on the command line)"
43
  echo "  --quick-demo                       Setup a quick demo with no authentication"
44
  echo "  --proxysql-datadir=<datadir>       Specify proxysql data directory location"
45
  echo "  --proxysql-username=user_name      Username for connecting to the ProxySQL service"
46
  echo "  --proxysql-password[=password]     Password for connecting to the ProxySQL service"
47
  echo "  --proxysql-port=port_num           Port Nr. for connecting to the ProxySQL service"
48
  echo "  --proxysql-hostname=host_name      Hostname for connecting to the ProxySQL service"
49
  echo "  --cluster-username=user_name       Username for connecting to the Percona XtraDB Cluster node"
50
  echo "  --cluster-password[=password]      Password for connecting to the Percona XtraDB Cluster node"
51
  echo "  --cluster-port=port_num            Port Nr. for connecting to the Percona XtraDB Cluster node"
52
  echo "  --cluster-hostname=host_name       Hostname for connecting to the Percona XtraDB Cluster node"
53
  echo "  --cluster-app-username=user_name   Application username for connecting to the Percona XtraDB Cluster node"
54
  echo "  --cluster-app-password[=password]  Application password for connecting to the Percona XtraDB Cluster node"
55
  echo "  --without-cluster-app-user         Configure Percona XtraDB Cluster without application user"
56
  echo "  --monitor-username=user_name       Username for monitoring Percona XtraDB Cluster nodes through ProxySQL"
57
  echo "  --monitor-password[=password]      Password for monitoring Percona XtraDB Cluster nodes through ProxySQL"
58
  echo "  --enable, -e                       Auto-configure Percona XtraDB Cluster nodes into ProxySQL"
59
  echo "  --disable, -d                      Remove any Percona XtraDB Cluster configurations from ProxySQL"
60
  echo "  --node-check-interval=3000         Interval for monitoring node checker script (in milliseconds)"
61
  echo "  --mode=[loadbal|singlewrite]       ProxySQL read/write configuration mode, currently supporting: 'loadbal' and 'singlewrite' (the default) modes"
62
  echo "  --write-node=host_name:port        Writer node to accept write statments. This option is supported only when using --mode=singlewrite"
63
  echo "                                     Can accept comma delimited list with the first listed being the highest priority."
64
### Consider adding a select or prompt option for --write-node that will let you select from discovered nodes during setup ###
65
  echo "  --include-slaves=host_name:port    Add specified slave node(s) to ProxySQL, these nodes will go into the reader hostgroup and will only be put into"
66
  echo "                                     the writer hostgroup if all cluster nodes are down.  Slaves must be read only.  Can accept comma delimited list."
67
  echo "                                     If this is used make sure 'read_only=1' is in the slave's my.cnf"
68
  echo "  --adduser                          Adds the Percona XtraDB Cluster application user to the ProxySQL database"
69
  echo "  --syncusers                        Sync user accounts currently configured in MySQL to ProxySQL (deletes ProxySQL users not in MySQL)"
70
  echo "  --version, -v                      Print version info"
71
}
72
73
# Check if we have a functional getopt(1)
74
if ! getopt --test
75
  then
76
  go_out="$(getopt --options=edv --longoptions=config-file:,proxysql-datadir:,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:,include-slaves:,without-cluster-app-user,enable,disable,adduser,syncusers,version,help \
77
  --name="$(basename "$0")" -- "$@")"
78
  test $? -eq 0 || exit 1
79
  eval set -- "$go_out"
80
fi
81
82
if [[ $go_out == " --" ]];then
83
  usage
84
  exit 1
85
fi
86
87
if ! echo "$go_out" | grep -q 'config-file'; then
88
  if ! echo "$go_out" | grep -q 'quick-demo'; then
89
    # Reading default variables from default configuration file location.
90
    if [ -e "/etc/proxysql-admin.cnf" ]; then
91
      # Loading default configuration from /etc/proxysql-admin.cnf
92
      source /etc/proxysql-admin.cnf
93
      CONFIG_FILE="/etc/proxysql-admin.cnf"
94
    else
95
      echo "WARNING! Default configuration file (/etc/proxysql-admin.cnf) does not exist"
96
    fi
97
  fi
98
fi
99
100
for arg
101
do
102
  case "$arg" in
103
    -- ) shift; break;;
104
    --config-file )
105
      CONFIG_FILE="$2"
106
      shift 2
107
      if [ -z "${CONFIG_FILE}" ]; then
108
        echo "ERROR: The configuration file location (--config-file) was not provided. Terminating."
109
        exit 1
110
      fi
111
      if [ -e "${CONFIG_FILE}" ]; then
112
        # Loading configuration from ${CONFIG_FILE}
113
        source "${CONFIG_FILE}"
114
      else
115
        echo "ERROR: The configuration file ${CONFIG_FILE} specified by --config-file does not exist. Terminating."
116
        exit 1
117
      fi
118
    ;;
119
    --proxysql-datadir )
120
    PROXYSQL_DATADIR="$2"
121
    shift 2
122
    ;;
123
    --proxysql-username )
124
    PROXYSQL_USERNAME="$2"
125
    shift 2
126
    ;;
127
    --proxysql-password )
128
    case "$2" in
129
      "")
130
      read -r -s -p  "Enter ProxySQL password:" INPUT_PASS
131
      if [ -z "$INPUT_PASS" ]; then
132
        PROXYSQL_PASSWORD=""
133
    printf "\nContinuing without ProxySQL password...\n";
134
      else
135
        PROXYSQL_PASSWORD="$INPUT_PASS"
136
      fi
137
      printf "\n"
138
      ;;
139
      *)
140
      PROXYSQL_PASSWORD="$2"
141
      ;;
142
    esac
143
    shift 2
144
    ;;
145
    --proxysql-hostname )
146
    PROXYSQL_HOSTNAME="$2"
147
    shift 2
148
    ;;
149
    --proxysql-port )
150
    PROXYSQL_PORT="$2"
151
    shift 2
152
    ;;
153
    --cluster-username )
154
    CLUSTER_USERNAME="$2"
155
    shift 2
156
    ;;
157
    --cluster-password )
158
    case "$2" in
159
      "")
160
      read -r -s -p  "Enter Percona XtraDB Cluster password:" INPUT_PASS
161
      if [ -z "$INPUT_PASS" ]; then
162
        CLUSTER_PASSWORD=""
163
    printf "\nContinuing without Percona XtraDB Cluster password...\n";
164
      else
165
        CLUSTER_PASSWORD="$INPUT_PASS"
166
      fi
167
      printf "\n"
168
      ;;
169
      *)
170
      CLUSTER_PASSWORD="$2"
171
      ;;
172
    esac
173
    shift 2
174
    ;;
175
    --cluster-hostname )
176
    CLUSTER_HOSTNAME="$2"
177
    shift 2
178
    ;;
179
    --cluster-port )
180
    CLUSTER_PORT="$2"
181
    shift 2
182
    ;;
183
    --monitor-username )
184
    MONITOR_USERNAME="$2"
185
    shift 2
186
    ;;
187
    --monitor-password )
188
    MONITOR_PASSWORD="$2"
189
    shift 2
190
    ;;
191
    --cluster-app-username )
192
    CLUSTER_APP_USERNAME="$2"
193
    shift 2
194
    ;;
195
    --cluster-app-password )
196
    CLUSTER_APP_PASSWORD="$2"
197
    shift 2
198
    ;;
199
    --without-cluster-app-user )
200
    shift
201
    WITHOUT_CLUSTER_APP_USER=1
202
    ;;
203
    -e | --enable )
204
    shift
205
    ENABLE=1
206
    ;;
207
    --adduser )
208
    shift
209
    ADDUSER=1
210
    ;;
211
    --syncusers )
212
    shift
213
    SYNCUSERS=1
214
    ;;
215
    -d | --disable )
216
    shift
217
    DISABLE=1
218
    ;;
219
    --node-check-interval )
220
    NODE_CHECK_INTERVAL="$2"
221
    shift 2
222
    ;;
223
    --mode )
224
    MODE="$2"
225
    shift 2
226
    if [ "$MODE" != "loadbal" ] && [ "$MODE" != "singlewrite" ]; then
227
      echo "ERROR: Invalid --mode passed:"
228
      echo "  Please choose any of these modes: loadbal, singlewrite"
229
      exit 1
230
    fi
231
    ;;
232
    --write-node )
233
#    if [ `grep -o ',' <<< $2 | wc -l` -gt 0 ];then
234
      WRITE_NODES=`echo "$2" | sed 's/,/ /g'`
235
      WRITE_NODE=`echo "$WRITE_NODES" | cut -d' ' -f1`
236
      enable_priority=1
237
#    else
238
#      WRITE_NODE="$2"
239
#    fi
240
    shift 2
241
    ;;
242
    --include-slaves )
243
      SLAVE_NODES=`echo "$2" | sed 's/,/ /g'`
244
    shift 2
245
    ;;
246
    --quick-demo )
247
    shift
248
    QUICK_DEMO="YES"
249
    ENABLE=1
250
    ;;
251
    -v | --version )
252
      echo "proxysql-admin version 1.4.6"
253
      exit 0
254
    ;;
255
    --help )
256
    usage
257
    exit 0
258
    ;;
259
  esac
260
done
261
262
if [ ! -z "$QUICK_DEMO" ]; then
263
  echo -e "\nThis script will assist with configuring ProxySQL (currently only Percona XtraDB cluster in combination with ProxySQL is supported)"
264
  echo -e "\nYou have selected the dry test run mode. WARNING: This will create a test user (with all privileges) in the Percona XtraDB Cluster & ProxySQL installations.\n"
265
  echo -e "You may want to delete this user after you complete your testing!\n"
266
  read -r -p "Would you like to proceed with '--quick-demo' [y/n] ? " check_param
267
  case $check_param in
268
    y|Y)
269
      echo -e "\nSetting up proxysql test configuration!\n"
270
    ;;
271
    n|N)
272
      echo -e "\nYou have selected No. Terminating.\n"
273
      exit 1
274
    ;;
275
    *)
276
      echo "Please type [y/n]! Terminating."
277
      exit 1
278
    ;;
279
  esac
280
fi
281
282
if [[ ! -e $(which mysql 2> /dev/null) ]] ;then
283
  echo "The mysql client was not found, please install the mysql client package using your OS packaging manager!"
284
  exit 1
285
fi
286
287
# Check the options gathered from the command line
288
if [ -z "$QUICK_DEMO" ]; then
289
  if [ -z "$PROXYSQL_USERNAME" ];then
290
    echo "The ProxySQL username is required!"
291
    usage
292
    exit 1
293
  fi
294
295
  if [[ -z "$PROXYSQL_HOSTNAME" ]]; then
296
    PROXYSQL_HOSTNAME="127.0.0.1"
297
  fi
298
299
  if [[ -z "$PROXYSQL_PORT" ]]; then
300
    PROXYSQL_PORT="6032"
301
  fi
302
303
  if [ -z "$CLUSTER_USERNAME" ];then
304
    echo "The Percona XtraDB Cluster username is required!"
305
    usage
306
    exit 1
307
  fi
308
309
  if [[ -z "$CLUSTER_HOSTNAME" ]]; then
310
    CLUSTER_HOSTNAME="localhost"
311
  fi
312
313
  if [[ -z "$CLUSTER_PORT" ]]; then
314
    CLUSTER_PORT="localhost"
315
  fi
316
else
317
  read -r -p "Do you want to use the default ProxySQL credentials (admin:admin:6032:127.0.0.1) [y/n] ? " check_param
318
  case $check_param in
319
    y|Y)
320
      PROXYSQL_USERNAME="admin"
321
      PROXYSQL_PASSWORD="admin"
322
      PROXYSQL_PORT="6032"
323
      PROXYSQL_HOSTNAME="127.0.0.1"
324
    ;;
325
    n|N)
326
      echo ""
327
      echo -n "Enter the ProxySQL user name: "
328
      read -r PROXYSQL_USERNAME
329
      read -r -s -p  "Enter the ProxySQL user password: " PROXYSQL_PASSWORD;echo ""
330
      echo -n "Enter the ProxySQL port: "
331
      read -r PROXYSQL_PORT
332
      echo -n "Enter the ProxySQL hostname: "
333
      read -r PROXYSQL_HOSTNAME
334
      echo ""
335
    ;;
336
    *)
337
      echo "Please type [y/n]! Terminating."
338
      exit 1
339
    ;;
340
  esac
341
342
  read -r -p "Do you want to use the default Percona XtraDB Cluster credentials (root::3306:127.0.0.1) [y/n] ? " check_param
343
  case $check_param in
344
    y|Y)
345
      CLUSTER_USERNAME="root"
346
      CLUSTER_PASSWORD=""
347
      CLUSTER_PORT="3306"
348
      CLUSTER_HOSTNAME="127.0.0.1"
349
    ;;
350
    n|N)
351
      echo ""
352
      echo -n "Enter the Percona XtraDB Cluster username (super user): "
353
      read -r CLUSTER_USERNAME
354
      read -r -s -p  "Enter the Percona XtraDB Cluster user password: " CLUSTER_PASSWORD; echo ""
355
      echo -n "Enter the Percona XtraDB Cluster port: "
356
      read -r CLUSTER_PORT
357
      echo -n "Enter the Percona XtraDB Cluster hostname: "
358
      read -r CLUSTER_HOSTNAME
359
      echo ""
360
    ;;
361
    *)
362
      echo "Please type [y/n]! Terminating."
363
      exit 1
364
    ;;
365
  esac
366
  MONITOR_USERNAME='monitor'
367
  MONITOR_PASSWORD='monitor'
368
  CLUSTER_APP_USERNAME='pxc_test_user'
369
  CLUSTER_APP_PASSWORD=''
370
fi
371
372
if [[ -z "$PROXYSQL_DATADIR" ]]; then
373
  PROXYSQL_DATADIR='/var/lib/proxysql'
374
fi
375
376
if [[ -z "$NODE_CHECK_INTERVAL" ]]; then
377
  NODE_CHECK_INTERVAL=3000
378
fi
379
380
if [[ -z "$MODE" ]]; then
381
  MODE="singlewrite"
382
else
383
  if [ "$MODE" != "loadbal" ] && [ "$MODE" != "singlewrite" ]; then
384
    echo "ERROR: Invalid --mode passed:"
385
    echo "  Please choose any of these modes: loadbal, singlewrite"
386
    exit 1
387
  fi
388
fi
389
390
# Set default hostgroup values if not set in the config file
391
if [ -z $WRITE_HOSTGROUP_ID ];then WRITE_HOSTGROUP_ID=10;fi
392
if [ -z $READ_HOSTGROUP_ID ];then READ_HOSTGROUP_ID=11;fi
393
394
if [ $MODE == "loadbal" ]; then
395
  SLAVEREAD_HOSTGROUP_ID=$READ_HOSTGROUP_ID
396
  READ_HOSTGROUP_ID=$WRITE_HOSTGROUP_ID
397
  if [ -e "${CONFIG_FILE}" ]; then
398
    sed -i "0,/^[ \t]*export MODE[ \t]*=.*$/s|^[ \t]*export MODE[ \t]*=.*$|export MODE=\"loadbal\"|" "${CONFIG_FILE}"
399
  fi
400
elif [ $MODE == "singlewrite" ]; then
401
  SLAVEREAD_HOSTGROUP_ID=$READ_HOSTGROUP_ID
402
  if [ -e "${CONFIG_FILE}" ]; then
403
    sed -i "0,/^[ \t]*export MODE[ \t]*=.*$/s|^[ \t]*export MODE[ \t]*=.*$|export MODE=\"singlewrite\"|" "${CONFIG_FILE}"
404
  fi
405
fi
406
407
export PIDFILE="/tmp/cluster-proxysql-monitor.pid"
408
409
proxysql_exec() {
410
  query=$1
411
  args=$2
412
  printf "[client]\nuser=${PROXYSQL_USERNAME}\npassword=\"${PROXYSQL_PASSWORD}\"\nhost=${PROXYSQL_HOSTNAME}\nport=${PROXYSQL_PORT}\n" | \
413
      mysql --defaults-file=/dev/stdin --protocol=tcp -"${args}"e  "${query}" 2>/dev/null
414
}
415
416
mysql_exec() {
417
  query=$1
418
  printf "[client]\nuser=${CLUSTER_USERNAME}\npassword=\"${CLUSTER_PASSWORD}\"\nhost=${CLUSTER_HOSTNAME}\nport=${CLUSTER_PORT}\n" | \
419
      mysql --defaults-file=/dev/stdin --protocol=tcp -Bse "${query}" 2>/dev/null
420
}
421
422
slave_exec() {
423
  query=$1
424
  printf "[client]\nuser=${CLUSTER_USERNAME}\npassword=\"${CLUSTER_PASSWORD}\"\nhost=${SLAVE_HOSTNAME}\nport=${SLAVE_PORT}\n" | \
425
      mysql --defaults-file=/dev/stdin --protocol=tcp -Bse "${query}" 2>/dev/null
426
}
427
428
proxysql_connection_check(){
429
  CONNECTION_MSG=$( { printf "[client]\nuser=${PROXYSQL_USERNAME}\npassword=\"${PROXYSQL_PASSWORD}\"\nhost=${PROXYSQL_HOSTNAME}\nport=${PROXYSQL_PORT}\n" | mysql --defaults-file=/dev/stdin --protocol=tcp -e "show tables" >/dev/null; }  2>&1 )
430
  if [[ ! -z $CONNECTION_MSG ]]; then
431
    echo "$CONNECTION_MSG"
432
    echo "Please check the ProxySQL connection parameters! Terminating."
433
    exit 1
434
  fi
435
}
436
437
cluster_connection_check(){
438
  CONNECTION_MSG=$( { printf "[client]\nuser=${CLUSTER_USERNAME}\npassword=\"${CLUSTER_PASSWORD}\"\nhost=${CLUSTER_HOSTNAME}\nport=${CLUSTER_PORT}\n" | mysql --defaults-file=/dev/stdin --protocol=tcp -Bse "select @@port" >/dev/null; }  2>&1 )
439
  if [[ ! -z $CONNECTION_MSG ]]; then
440
    echo "$CONNECTION_MSG"
441
    echo "Please check the Percona XtraDB Cluster connection parameters! Terminating."
442
    exit 1
443
  fi
444
}
445
446
check_cmd(){
447
  MPID=$1
448
  ERROR_MSG=$2
449
  if [ "${MPID}" -ne 0 ]; then echo -e "\nERROR: $ERROR_MSG. Terminating!"; exit 1; fi
450
}
451
452
check_proxysql(){
453
  if ! pidof proxysql >/dev/null ; then
454
    echo "ProxySQL is not running, please check the error log at $PROXYSQL_DATADIR/proxysql.log"
455
    exit 1
456
  fi
457
}
458
459
CLUSTER_NAME=$(mysql_exec "select @@wsrep_cluster_name;")
460
rm -rf ${PROXYSQL_DATADIR}/${CLUSTER_NAME}_mode
461
echo "$MODE" > ${PROXYSQL_DATADIR}/${CLUSTER_NAME}_mode
462
463
user_input_check(){
464
  USER_CATEGORY=$1
465
  USER_DESCRIPTION=$2
466
  HOSTGROUP_ID=$3
467
  USERNAME=$(eval "echo \$${USER_CATEGORY}_USERNAME")
468
  PASSWORD=$(eval "echo \$${USER_CATEGORY}_PASSWORD")
469
  if [[ -z "$USERNAME" ]]; then
470
    read -r -p "Enter ${USER_DESCRIPTION}name : " ${USER_CATEGORY}_USERNAME
471
    while [[ -z "${USER_CATEGORY}_USERNAME" ]]
472
    do
473
      echo -n "No input entered, Enter ${USER_DESCRIPTION}name: "
474
      read -r ${USER_CATEGORY}_USERNAME
475
    done
476
  else
477
    if [ -z "$QUICK_DEMO" ]; then
478
      echo -e "${USER_DESCRIPTION}name as per command line/config-file is ${BD}$(eval "echo \$${USER_CATEGORY}_USERNAME")${NBD}"
479
    fi
480
  fi
481
  if [[ -z $PASSWORD ]]; then
482
    if [ -z "$QUICK_DEMO" ]; then
483
      read -r -s -p  "Enter ${USER_DESCRIPTION} password: " ${USER_CATEGORY}_PASSWORD
484
      while [[ -z "${USER_CATEGORY}_PASSWORD" ]]
485
      do
486
        read -r -s -p  "No input entered, Enter ${USER_DESCRIPTION} password: " ${USER_CATEGORY}_PASSWORD
487
      done
488
    fi
489
  fi
490
  USERNAME=$(eval "echo \$${USER_CATEGORY}_USERNAME")
491
  PASSWORD=$(eval "echo \$${USER_CATEGORY}_PASSWORD")
492
  if [ "$USER_CATEGORY" != "MONITOR" ]; then
493
    check_user=$(mysql_exec "SELECT user,host FROM mysql.user where user='$USERNAME' and host='$USER_HOST_RANGE';")
494
    if [[ -z "$check_user" ]]; then
495
      precheck_user=$(proxysql_exec "SELECT username FROM mysql_users where username='$USERNAME'")
496
      if [[ -z "$precheck_user" ]]; then  
497
        mysql_exec "CREATE USER $USERNAME@'$USER_HOST_RANGE' IDENTIFIED BY '$PASSWORD';"
498
        check_cmd $? "Failed to add Percona XtraDB Cluster application user: '$USERNAME'. Please check '$CLUSTER_USERNAME'@'$CLUSTER_HOSTNAME' has proper permission to create montioring user"
499
        if [ ! -z "$QUICK_DEMO" ]; then
500
          mysql_exec "GRANT ALL ON *.* to $USERNAME@'$USER_HOST_RANGE'"
501
          check_cmd $? "$CLUSTER_USERNAME@'$CLUSTER_HOSTNAME' does not have the GRANT privilege required to assign the requested permissions"
502
        fi
503
504
        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;"
505
        check_cmd $? "Failed to add Percona XtraDB Cluster application user: '$USERNAME' in ProxySQL database. Please check username, password and other options for connecting to ProxySQL database"
506
        if [ -z "$QUICK_DEMO" ]; then
507
          echo -e "\nPercona XtraDB Cluster application user '${BD}$USERNAME'@'$USER_HOST_RANGE${NBD}' has been added with the USAGE privilege, please make sure to the grant appropriate privileges"
508
        else
509
          echo -e "\nPercona XtraDB Cluster application user '${BD}$USERNAME'@'$USER_HOST_RANGE${NBD}' has been added with ${BD}ALL${NBD} privileges, ${BD}this user is created for testing purposes${NBD}"
510
        fi
511
      else
512
        echo -e "\nERROR: The application user ${BD}$USERNAME${NBD} is already present in ProxySQL database. Terminating!"
513
        echo -e " Note: ProxySQL do not allow duplicate username."
514
        exit 1
515
      fi
516
    else
517
      check_user=$(proxysql_exec "SELECT username FROM mysql_users where username='$USERNAME'")
518
      if [[ -z "$check_user" ]]; then
519
        echo -e "\nApplication user '${BD}${USERNAME}'@'$USER_HOST_RANGE${NBD}' already present in Percona XtraDB Cluster."
520
        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;"
521
        check_cmd $? "Failed to add Percona XtraDB Cluster application user: '$USERNAME' in ProxySQL database. Please check username, password and other options for connecting to ProxySQL database"
522
      else
523
        echo -e "\nERROR: The application user ${BD}$USERNAME${NBD} is already present in ProxySQL database. Terminating!"
524
        echo -e " Note: ProxySQL do not allow duplicate username."
525
        exit 1
526
      fi
527
    fi
528
  fi
529
}
530
531
# Auto configure Percona XtraDB Cluster nodes into ProxySQL
532
enable_proxysql(){
533
  # Checking proxysql binary location
534
  if [[ ! -e $(which proxysql 2> /dev/null) ]]; then
535
    echo "The proxysql binary was not found, please install the ProxySQL package"  
536
    exit 1
537
  elif [[ ! -e $(which proxysql_galera_checker 2> /dev/null) ]] ;then
538
    echo "The proxysql_galera_checker binary was not found, please check the ProxySQL package installation"  
539
    exit 1
540
  elif [[ ! -e $(which proxysql_node_monitor 2> /dev/null) ]]; then
541
    echo "The proxysql_node_monitor binary was not found, please check the ProxySQL package installation"  
542
    exit 1
543
  else
544
    PROXYSQL=$(which proxysql)
545
    PROXYSQL_GALERA_CHECK=$(which proxysql_galera_checker)
546
    PROXYSQL_NODE_MONITOR=$(which proxysql_node_monitor)
547
  fi
548
  # Check for existing proxysql process
549
  if ! pidof proxysql >/dev/null ; then
550
    echo "ProxySQL is not running; please start the proxysql service"
551
    exit 1
552
  fi
553
  proxysql_connection_check
554
555
  # Checking an existing host priority file if its there
556
  if [ -f $HOST_PRIORITY_FILE ]; then
557
    echo ""
558
    read -p "Host priority file ($HOST_PRIORITY_FILE) is already present. Would you like to replace with the new file [y/n] ? " check_param
559
    case $check_param in
560
      y|Y)
561
        rm -f $HOST_PRIORITY_FILE
562
      ;;
563
      n|N)
564
        echo -e "\nHost priority file is not deleted. Please make sure you have properly configured $HOST_PRIORITY_FILE"
565
      ;;
566
      *)
567
        echo "Please type [y/n]! Terminating."
568
        exit 1
569
      ;;
570
    esac
571
  fi
572
  #modifying proxysql-admin.cnf file with command line proxysql user credentials if you dont use --config-file option.
573
  #if [ -z "${CONFIG_FILE}" ]; then 
574
  #  sed -i "s|[ \t]*PROXYSQL_USERNAME[ \t]*=.*$| PROXYSQL_USERNAME=\"${PROXYSQL_USERNAME}\"|" /etc/proxysql-admin.cnf
575
  #  sed -i "s|[ \t]*PROXYSQL_PASSWORD[ \t]*=.*$| PROXYSQL_PASSWORD=\"${PROXYSQL_PASSWORD}\"|" /etc/proxysql-admin.cnf
576
  #  sed -i "s|[ \t]*PROXYSQL_HOSTNAME[ \t]*=.*$| PROXYSQL_HOSTNAME=\"${PROXYSQL_HOSTNAME}\"|" /etc/proxysql-admin.cnf
577
  #  sed -i "s|[ \t]*PROXYSQL_PORT[ \t]*=.*$| PROXYSQL_PORT=\"${PROXYSQL_PORT}\"|" /etc/proxysql-admin.cnf
578
  #fi
579
580
  cluster_connection_check
581
582
  check_hgs=$(proxysql_exec "SELECT hostgroup_id FROM mysql_servers where hostgroup_id in ($READ_HOSTGROUP_ID,$WRITE_HOSTGROUP_ID)")
583
  if [[ ! -z "$check_hgs" ]]; then
584
    echo -e "\nERROR: READ/WRITE hostgroups($READ_HOSTGROUP_ID,$WRITE_HOSTGROUP_ID) are already present in ProxySQL database. Terminating!"
585
    exit 1
586
  fi
587
  CLUSTER_NETWORK=$(mysql_exec "show status like 'wsrep_incoming_addresses'" | awk '{print $2}' | cut -d'.' -f1)
588
  if [[ "$CLUSTER_NETWORK" =~ ^[0-9]+$ ]]; then 
589
    USER_HOST_RANGE="$CLUSTER_NETWORK.%"
590
  else
591
    USER_HOST_RANGE="%"
592
  fi
593
  echo -e "\nConfiguring ProxySQL monitoring user.."
594
  user_input_check MONITOR "ProxySQL monitor user"
595
596
  check_user=$(mysql_exec "SELECT user,host FROM mysql.user where user='$MONITOR_USERNAME' and host='$USER_HOST_RANGE';")
597
  if [[ -z "$check_user" ]]; then
598
    mysql_exec "CREATE USER '$MONITOR_USERNAME'@'$USER_HOST_RANGE' IDENTIFIED BY '$MONITOR_PASSWORD';"
599
    check_cmd $?  "Failed to create the ProxySQL monitoring user. Please check '$CLUSTER_USERNAME'@'$CLUSTER_HOSTNAME' has proper permission to create montioring user"
600
    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'; "
601
    check_cmd $?  "Failed to set the mysql-monitor variables in ProxySQL. Please check username, password and other options for connecting to ProxySQL database"
602
    proxysql_exec "LOAD MYSQL VARIABLES TO RUNTIME;SAVE MYSQL VARIABLES TO DISK;"
603
    echo -e "\nUser '${BD}$MONITOR_USERNAME'@'$USER_HOST_RANGE${NBD}' has been added with USAGE privilege"
604
  else
605
    echo ""
606
    read -p "The monitoring user is already present in Percona XtraDB Cluster. Would you like to proceed with the existing username and password [y/n] ? " check_param
607
    case $check_param in
608
      y|Y)
609
        if [ -z "$QUICK_DEMO" ]; then
610
          read -r -s -p  "Please enter the password you have assigned to monitoring user '$MONITOR_USERNAME': " MONITOR_PASSWORD
611
          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'; "
612
          check_cmd $?  "Failed to set the mysql-monitor variables in ProxySQL. Please check username, password and other options for connecting to ProxySQL database"
613
          proxysql_exec "LOAD MYSQL VARIABLES TO RUNTIME;SAVE MYSQL VARIABLES TO DISK;"
614
        fi
615
      ;;
616
      n|N)
617
        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'; "
618
        check_cmd $?  "Failed to set the mysql-monitor variables in ProxySQL. Please check username, password and other options for connecting to ProxySQL database"
619
        proxysql_exec "LOAD MYSQL VARIABLES TO RUNTIME;SAVE MYSQL VARIABLES TO DISK;"
620
        echo -e "\nUser '${BD}$MONITOR_USERNAME'@'$USER_HOST_RANGE${NBD}' has been updated in ProxySQL database. Please make sure the credentials are the same in Percona XtraDB Cluster"
621
      ;;
622
      *)
623
        echo "Please type [y/n]! Terminating."
624
        exit 1
625
      ;;
626
    esac
627
  fi
628
629
  if [ "$WITHOUT_CLUSTER_APP_USER" != 1 ]; then
630
    echo -e "\nConfiguring the Percona XtraDB Cluster application user to connect through ProxySQL"
631
    user_input_check CLUSTER_APP "Percona XtraDB Cluster application user" $WRITE_HOSTGROUP_ID
632
  fi
633
634
  # Get the nodes in the cluster
635
  wsrep_address=($(mysql_exec "show status like 'wsrep_incoming_addresses'" | awk '{print $2}' | sed 's|,| |g'))
636
637
  # If any slave nodes were specified, verify they are replicating from a valid cluster node
638
  if [ -n "$SLAVE_NODES" ];then
639
    echo -e "\nVerifying specified slave nodes"
640
    for i in $SLAVE_NODES;do
641
      SLAVE_HOSTNAME=`echo $i | cut -d: -f1`
642
      SLAVE_PORT=`echo $i | cut -d: -f2`
643
      # Verify we can connect to the slave
644
      slave_exec 'show slave status\G' > /dev/null
645
      check_cmd $? "Failed to connect to $i, please check username, password and other options for connecting to the slave"
646
      # Check each of the specified slaves and verify they are a slave of one of the XtraDB cluster nodes
647
      slave_master=$(slave_exec 'show slave status\G'| egrep "Master_Host|Master_Port" | sed 's/ //g' | cut -d: -f2 | tr '\n' ':' | sed 's/:$//g')
648
      if [[ ${wsrep_address[*]} != *"$slave_master"* ]]; then
649
        echo -e "\nERROR: The slave node's master ($slave_master) does not exist in WSREP incoming address(${wsrep_address[*]})."
650
        echo -e "The specified slave is not replicating from any of the XDtraDB cluster nodes, this is not supported"
651
        echo -e "Please configure ProxySQL manually.. Terminating!"
652
        exit 1
653
      fi
654
      # Verify the slave read-only variable is set to '1'
655
      slave_ro=$(slave_exec 'SELECT @@read_only')
656
      if [ "$slave_ro" != "1" ];then
657
        echo -e "\nERROR: The slave $i is not set to read only.  Add 'read_only=1' to the my.cnf on the slave"
658
        echo -e "and restart MySQL.  Execute 'SET GLOBAL read_only = 1' on the slave to avoid the restart"
659
        echo -e "Correct this on slave nodes and run proxysql-admin again. Terminating!"
660
        exit 1
661
      fi
662
    done
663
    # GRANT REPLICATION CLIENT permissions to the monitoring user account
664
    echo -e "\nGranting 'REPLICATION CLIENT' privilege to $MONITOR_USERNAME@$USER_HOST_RANGE"
665
    mysql_exec "GRANT REPLICATION CLIENT ON *.* TO '$MONITOR_USERNAME'@'$USER_HOST_RANGE';"
666
    check_cmd $? "$CLUSTER_USERNAME@'$CLUSTER_HOSTNAME' does not have the GRANT privilege required to assign the requested permissions"
667
  fi
668
669
  # Adding Percona XtraDB Cluster nodes to ProxySQL
670
  echo -e "\nAdding the Percona XtraDB Cluster server nodes to ProxySQL"
671
  if [ $MODE == "loadbal" ]; then
672
    proxysql_exec "DELETE FROM mysql_servers WHERE hostgroup_id=$WRITE_HOSTGROUP_ID"
673
    for i in "${wsrep_address[@]}"; do  
674
      ws_ip=$(echo "$i" | cut -d':' -f1)
675
      ws_port=$(echo "$i" | cut -d':' -f2)
676
      proxysql_exec "INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,comment) VALUES ('$ws_ip',$WRITE_HOSTGROUP_ID,$ws_port,1000,'READWRITE');"
677
      check_cmd $? "Failed to add the Percona XtraDB Cluster server node $ws_ip:$ws_port. Please check username, password and other options for connecting to ProxySQL database"
678
    done
679
    proxysql_exec "LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;"
680
681
  elif [ $MODE == "singlewrite" ]; then
682
    proxysql_exec "DELETE FROM mysql_servers WHERE hostgroup_id in ($WRITE_HOSTGROUP_ID,$READ_HOSTGROUP_ID)"
683
    proxysql_exec "DELETE FROM mysql_query_rules WHERE destination_hostgroup in ($WRITE_HOSTGROUP_ID,$READ_HOSTGROUP_ID)"
684
    if [ -z "$QUICK_DEMO" ]; then
685
      if [ -z "$WRITE_NODE" ]; then
686
        writer_ws_ip=$(mysql_exec "show variables like 'wsrep_provider_options'" | grep -o -P '(?<=base_host =).*(?=; base_port)' | xargs)
687
        writer_ws_port=$CLUSTER_PORT
688
      else
689
        writer_ws_ip=$(echo "$WRITE_NODE" | awk -F':' '{print $1}')
690
        writer_ws_port=$(echo "$WRITE_NODE" | awk -F':' '{print $2}')
691
        if [ -z "$writer_ws_port" ];then
692
          writer_ws_port=3306
693
        fi
694
        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
695
        if [ $? -ne 0 ]; then 
696
          echo -e "\nERROR: Failed to establish connection to write node $writer_ws_ip:$writer_ws_port. Please check write node is alive and username, password and other options for connecting to server.... Terminating!\n"; 
697
          proxysql_exec "DELETE FROM mysql_users WHERE default_hostgroup in ($WRITE_HOSTGROUP_ID,$READ_HOSTGROUP_ID);"
698
          check_cmd $? "Failed to delete Percona XtraDB Cluster user from ProxySQL. Please check username, password and other options for connecting to ProxySQL database"
699
          exit 1
700
        fi
701
      fi
702
    else
703
      writer_ws_ip=$(mysql_exec "show variables like 'wsrep_provider_options'" | grep -o -P '(?<=base_host =).*(?=; base_port)' | xargs)
704
      writer_ws_port=$CLUSTER_PORT
705
    fi
706
    proxysql_exec "DELETE FROM mysql_servers WHERE hostgroup_id=$WRITE_HOSTGROUP_ID"
707
    if [[ ${wsrep_address[*]} != *"$writer_ws_ip:$writer_ws_port"* ]]; then
708
      echo -e "\nERROR: Writer node cluster address($writer_ws_ip:$writer_ws_port) does not exist in WSREP incoming address(${wsrep_address[*]})."
709
      echo -e "Different wsrep incoming and cluster IP addresses are not supported by proxysql-admin at this time"
710
      echo -e "Please configure ProxySQL manually.. Terminating!"
711
      exit 1
712
    fi
713
714
    # Create the host priority file if multiple write nodes were specified
715
    if [ -n $enable_priority ];then
716
      # Create empty priority config file
717
      echo '# ProxySQL Host Priority File' > $HOST_PRIORITY_FILE
718
      echo '# Specify nodes in order from highest priority to lowest' >> $HOST_PRIORITY_FILE
719
      check_cmd $? "Failed to create the host priority file, verify the directory permissions: $HOST_PRIORITY_FILE"
720
      # Add the specified hosts to the file
721
      echo -e "\nConfiguring $MODE mode with the following nodes designated as priority order:"
722
      for i in $WRITE_NODES;do
723
        echo $i >> $HOST_PRIORITY_FILE
724
        echo " $i"
725
      done
726
    fi
727
728
    for i in "${wsrep_address[@]}"; do  
729
      ws_ip=$(echo "$i" | cut -d':' -f1)
730
      ws_port=$(echo "$i" | cut -d':' -f2)
731
      if [ "$ws_ip" == "$writer_ws_ip" ] && [ "$ws_port" == "$writer_ws_port" ]; then
732
        proxysql_exec "INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,comment) VALUES ('$writer_ws_ip',$WRITE_HOSTGROUP_ID,$writer_ws_port,1000000,'WRITE');"
733
        check_cmd $? "Failed to add the Percona XtraDB Cluster server node $writer_ws_ip:$writer_ws_port. Please check username, password and other options for connecting to ProxySQL database"
734
        echo -e "\nWrite node info"
735
        proxysql_exec "SELECT hostname,hostgroup_id,port,weight,comment FROM mysql_servers WHERE hostgroup_id=$WRITE_HOSTGROUP_ID" "t"
736
      else
737
        proxysql_exec "INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,comment) VALUES ('$ws_ip',$READ_HOSTGROUP_ID,$ws_port,1000,'READ');"
738
        check_cmd $? "Failed to add the Percona XtraDB Cluster server node $ws_ip:$ws_port. Please check username, password and other options for connecting to ProxySQL database"
739
      fi
740
    done
741
    if [ "$WITHOUT_CLUSTER_APP_USER" != 1 ]; then
742
      proxysql_exec "INSERT INTO mysql_query_rules (username,destination_hostgroup,active,match_digest,apply) values('$CLUSTER_APP_USERNAME',$WRITE_HOSTGROUP_ID,1,'^SELECT.*FOR UPDATE',1),('$CLUSTER_APP_USERNAME',$READ_HOSTGROUP_ID,1,'^SELECT ',1);"
743
      check_cmd $? "Failed to add the read query rule. Please check username, password and other options for connecting to ProxySQL database"
744
    fi
745
    proxysql_exec "LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;LOAD MYSQL QUERY RULES TO RUNTIME;SAVE MYSQL QUERY RULES TO DISK;"
746
  fi
747
748
  # Adding slave nodes here if specified
749
  if [ -n "$SLAVE_NODES" ];then
750
    echo -e "\nAdding the following slave server nodes to ProxySQL:"
751
    for i in $SLAVE_NODES;do
752
      ws_ip=$(echo "$i" | cut -d':' -f1)
753
      ws_port=$(echo "$i" | cut -d':' -f2)
754
      proxysql_exec "INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,comment) VALUES ('$ws_ip',$SLAVEREAD_HOSTGROUP_ID,$ws_port,1000,'SLAVEREAD');"
755
      check_cmd $? "Failed to add the slave node $ws_ip:$ws_port. Please check username, password and other options for connecting to ProxySQL database"
756
      proxysql_exec "LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;"
757
      echo " $i"
758
    done
759
  fi
760
761
  # Adding Percona XtraDB Cluster monitoring scripts
762
  # Adding proxysql galera check scheduler
763
  proxysql_exec "DELETE FROM SCHEDULER WHERE COMMENT='$CLUSTER_NAME';"
764
  check_cmd $? "Failed to delete the Percona XtraDB Cluster nodes from ProxySQL. Please check username, password and other options for connecting to ProxySQL database"
765
  if [ $MODE == "singlewrite" ]; then
766
    NUMBER_WRITERS=1
767
  else
768
    NUMBER_WRITERS=0
769
  fi
770
  proxysql_exec "INSERT  INTO SCHEDULER (active,interval_ms,filename,arg1,arg2,arg3,arg4,arg5,comment) VALUES (1,$NODE_CHECK_INTERVAL,'$PROXYSQL_GALERA_CHECK',$WRITE_HOSTGROUP_ID,$READ_HOSTGROUP_ID,$NUMBER_WRITERS,1,'$PROXYSQL_DATADIR/${CLUSTER_NAME}_proxysql_galera_check.log','$CLUSTER_NAME');"
771
  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"
772
  
773
  proxysql_exec "LOAD SCHEDULER TO RUNTIME;SAVE SCHEDULER TO DISK;"
774
}
775
776
# Removing Percona XtraDB Cluster configuration from proxysql
777
disable_proxysql(){
778
  proxysql_connection_check
779
  echo "Removing default cluster application user from ProxySQL database."
780
  proxysql_exec "DELETE FROM mysql_users WHERE username='$CLUSTER_APP_USERNAME';"
781
  check_cmd $? "Failed to delete the Percona XtraDB Cluster user ($CLUSTER_APP_USERNAME) from ProxySQL. Please check username, password and other options for connecting to ProxySQL database"
782
  echo "Removing cluster nodes from ProxySQL database."
783
  proxysql_exec "DELETE FROM mysql_servers WHERE hostgroup_id in ($WRITE_HOSTGROUP_ID,$READ_HOSTGROUP_ID);"
784
  check_cmd $? "Failed to delete the Percona XtraDB Cluster nodes from ProxySQL. Please check username, password and other options for connecting to ProxySQL database"
785
  echo "Removing scheduler script from ProxySQL database."
786
  proxysql_exec "DELETE FROM SCHEDULER WHERE COMMENT='$CLUSTER_NAME';"
787
  check_cmd $? "Failed to delete the Galera checker from ProxySQL. Please check username, password and other options for connecting to ProxySQL database"
788
  echo "Removing query rules from ProxySQL database if any."
789
  proxysql_exec "DELETE FROM mysql_query_rules WHERE destination_hostgroup in ($WRITE_HOSTGROUP_ID,$READ_HOSTGROUP_ID)"
790
  check_cmd $? "Failed to delete the query rules from ProxySQL. Please check username, password and other options for connecting to ProxySQL database"
791
  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;LOAD MYSQL QUERY RULES TO RUNTIME;SAVE MYSQL QUERY RULES TO DISK;"
792
  # Delete the host priority file
793
  rm -f $HOST_PRIORITY_FILE
794
}
795
796
adduser(){
797
  proxysql_connection_check
798
  cluster_connection_check
799
  echo -e "\nAdding Percona XtraDB Cluster application user to the ProxySQL database"
800
  echo -n "Enter the Percona XtraDB Cluster application user name: "
801
  read -r CLUSTER_APP_WRITE_USERNAME
802
  while [[ -z "$CLUSTER_APP_WRITE_USERNAME" ]]
803
  do
804
    echo -n "No input entered. Enter the Percona XtraDB Cluster application user name: "
805
    read -r CLUSTER_APP_WRITE_USERNAME
806
  done
807
  read -r -s -p  "Enter the Percona XtraDB Cluster application user password: " CLUSTER_APP_WRITE_PASSWORD
808
  while [[ -z "$CLUSTER_APP_WRITE_PASSWORD" ]]
809
  do
810
    read -r -s -p  "No input entered. Enter the Percona XtraDB Cluster application user password: " CLUSTER_APP_WRITE_PASSWORD
811
  done
812
813
  check_user=$(proxysql_exec "SELECT username FROM mysql_users where username='$CLUSTER_APP_WRITE_USERNAME'")
814
  if [[ -z "$check_user" ]]; then
815
    check_cluster_user=$(mysql_exec "SELECT user,host FROM mysql.user where user='$CLUSTER_APP_WRITE_USERNAME'")
816
    if [[ -z "$check_cluster_user" ]]; then
817
      echo -e "\n\n"
818
      read -r -p "The application user '$CLUSTER_APP_WRITE_USERNAME' does not exist in Percona XtraDB Cluster. Would you like to proceed [y/n] ? " check_param
819
      case $check_param in
820
        y|Y)
821
          proxysql_exec "INSERT INTO mysql_users (username,password,active,default_hostgroup) values ('$CLUSTER_APP_WRITE_USERNAME','$CLUSTER_APP_WRITE_PASSWORD',1,$WRITE_HOSTGROUP_ID);LOAD MYSQL USERS TO RUNTIME;SAVE MYSQL USERS FROM RUNTIME;SAVE MYSQL USERS TO DISK;"
822
          check_cmd $? "Failed to add the Percona XtraDB Cluster application user: '$CLUSTER_APP_WRITE_USERNAME' to ProxySQL database. Please check username, password and other options for connecting to ProxySQL database"
823
          echo -e "\nPlease create the user ${BD}$CLUSTER_APP_WRITE_USERNAME${NBD} in Percona XtraDB Cluster to access the application through ProxySQL"
824
        ;;
825
        n|N)
826
          exit 1
827
        ;;
828
        *)
829
          echo "Please type [y/n]! Terminating."
830
          exit 1
831
        ;;
832
      esac
833
    else
834
      proxysql_exec "INSERT INTO mysql_users (username,password,active,default_hostgroup) values ('$CLUSTER_APP_WRITE_USERNAME','$CLUSTER_APP_WRITE_PASSWORD',1,$WRITE_HOSTGROUP_ID);LOAD MYSQL USERS TO RUNTIME;SAVE MYSQL USERS FROM RUNTIME;SAVE MYSQL USERS TO DISK;"
835
      check_cmd $? "Failed to add the Percona XtraDB Cluster application user: '$CLUSTER_APP_WRITE_USERNAME' to ProxySQL database. Please check username, password and other options for connecting to ProxySQL database"
836
    fi
837
  else
838
    echo -e "\nERROR: The application user '$CLUSTER_APP_WRITE_USERNAME' already exist in ProxySQL database. Terminating."
839
    exit 1
840
  fi
841
}
842
843
syncusers() {
844
  # This function was created to auto sync all the existing users already in MySQL to proxySQL's
845
  # mysql_users table.  As there is not much point in having users in ProxySQL that don't exist in
846
  # MySQL this function with delete any users from ProxySQL that were not found in MySQL.  Going
847
  # forward you can add/remove application users in MySQL then rerun proxysql-admin with the
848
  # --syncusers switch to replicate the changes to ProxySQL.
849
  # LIMITATIONS: Will not work properly in cases where the same user name exists in MySQL with
850
  #              several hosts and different passwords.  This will cause ProxySQL to throw a
851
  #              "UNIQUE constraint failed" error message.
852
853
  proxysql_connection_check
854
  cluster_connection_check
855
856
  # Get current MySQL users, filter out header row and mysql.sys user
857
  MYSQLVER=$(mysql_exec "SELECT VERSION();" | tail -1 | cut -d'.' -f1,2 )
858
859
  case $MYSQLVER in
860
    5.6)
861
      password_field="Password";;
862
    5.7)
863
      password_field="authentication_string";;
864
    10.2)
865
      password_field="Password";;
866
  esac
867
  
868
  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 )
869
  check_cmd $? "Failed to load user list from Percona XtraDB Cluster. Please check username, password and other options for connecting to Percona XtraDB Cluster"
870
  #Checking whether user is part of proxysql admin user list
871
  proxysql_admin_user_check(){
872
    userchk=$1
873
    proxysql_admin_users=($(proxysql_exec "select variable_value  from global_variables where variable_name like 'admin-%_credentials'" | cut -d':' -f1 | grep -v variable_value))
874
    if [[ " ${proxysql_admin_users[@]} " =~ " $userchk " ]];then
875
      is_proxysql_admin_user=1
876
    else
877
      is_proxysql_admin_user=0
878
    fi
879
  }
880
  # Get current ProxySQL users and filter out header row
881
  proxysql_users=$(proxysql_exec "SELECT username,password FROM mysql_users where password!=''" | sed 's/\t/,/g' | egrep -v "username,password" | sort | uniq )
882
  check_cmd $? "Failed to load user list from ProxySQL database. Please check username, password and other options for connecting to ProxySQL database"
883
884
  echo -e "\nSyncing user accounts from Percona XtraDB Cluster to ProxySQL"
885
  # TEST FOR USERS THAT EXIST IN MYSQL BUT NOT IN PROXYSQL HERE AND ADD
886
  for mysql_user in $mysql_users;do
887
    match=0
888
    for proxysql_user in $proxysql_users;do
889
      if [ "$proxysql_user" == "$mysql_user" ];then
890
        match=1
891
      fi
892
    done
893
    if [ "$match" == 0 ];then
894
      user=`echo $mysql_user | cut -d, -f1`
895
      password=`echo $mysql_user | cut -d, -f2`
896
897
      # Check if same username exists with a different password, delete the user to recreate.
898
      for proxysql_user in $proxysql_users;do
899
        echo $proxysql_user | grep "^${user}," > /dev/null
900
        if [ "$?" == 0 ];then
901
          # Remove the user
902
          echo "Deleting existing user: $user"
903
          proxysql_exec "DELETE FROM mysql_users WHERE username='${user}'"
904
          check_cmd $? "Failed to delete the user ($user) from ProxySQL database. Please check username, password and other options for connecting to ProxySQL database"
905
        fi
906
      done
907
      proxysql_admin_user_check $user
908
      if [[ "$is_proxysql_admin_user" == "1" ]];then
909
        echo -e "\nNote : '$user' is in proxysql admin user list, cannot not add this user to proxysql database( For more info https://github.com/sysown/proxysql/issues/709)"
910
      else
911
        proxysql_exec "INSERT INTO mysql_users (username, password, active, default_hostgroup) VALUES ('${user}', '${password}', 1, 10)"
912
        check_cmd $? "Failed to add the user ($user) from Percona XtraDB Cluster to ProxySQL database. Please check username, password and other options for connecting to ProxySQL database"
913
      fi
914
    fi
915
  done
916
917
  # TEST FOR USERS THAT EXIST IN PROXYSQL BUT NOT IN MYSQL HERE AND REMOVE
918
  for proxysql_user in $proxysql_users;do
919
    match=0
920
    for mysql_user in $mysql_users;do
921
      if [ "$proxysql_user" == "$mysql_user" ];then
922
        match=1
923
      fi
924
    done
925
    if [ "$match" == 0 ];then
926
      # Delete the ProxySQL user
927
      user=`echo $proxysql_user | cut -d, -f1`
928
      echo -e "\nRemoving $proxysql_user from ProxySQL"
929
      proxysql_exec "DELETE FROM mysql_users WHERE username='${user}'"
930
      check_cmd $? "Failed to delete the user ($user) from ProxySQL database. Please check username, password and other options for connecting to ProxySQL database"
931
    fi
932
  done
933
934
  proxysql_exec "SAVE MYSQL USERS TO DISK;LOAD MYSQL USERS TO RUNTIME;" 
935
}
936
937
if [ "$ENABLE" == 1 ] || [ "$DISABLE" == 1 ] || [ "$ADDUSER" == 1 ] || [ "$SYNCUSERS" == 1 ]; then
938
  if [ "$ENABLE" == 1 ];then
939
    if [ -z "$QUICK_DEMO" ]; then
940
      echo -e "\nThis script will assist with configuring ProxySQL (currently only Percona XtraDB cluster in combination with ProxySQL is supported)"
941
    fi
942
    echo -e "\nProxySQL read/write configuration mode is ${BD}$MODE${NBD}"
943
    enable_proxysql
944
    echo -e "\nProxySQL configuration completed!\n"
945
    PROXYSQL_CLIENT_PORT=$(proxysql_exec "SELECT * FROM runtime_global_variables WHERE variable_name='mysql-interfaces'" | awk '{print $2}' | grep -o -P '(?<=:).*' | cut -d';' -f1 )
946
    echo -e "ProxySQL has been successfully configured to use with Percona XtraDB Cluster\n"
947
    echo -e "You can use the following login credentials to connect your application through ProxySQL\n"
948
    if [ -z "$QUICK_DEMO" ]; then
949
      echo -e "${BD}mysql --user=$CLUSTER_APP_USERNAME -p  --host=$PROXYSQL_HOSTNAME --port=$PROXYSQL_CLIENT_PORT --protocol=tcp ${NBD}\n"
950
    else
951
      echo -e "${BD}mysql --user=$CLUSTER_APP_USERNAME  --host=$PROXYSQL_HOSTNAME --port=$PROXYSQL_CLIENT_PORT --protocol=tcp ${NBD}\n"
952
    fi 
953
  fi
954
  if [ "$DISABLE" == 1 ];then  
955
    disable_proxysql
956
    echo "ProxySQL configuration removed!"
957
  fi
958
  if [ "$ADDUSER" == 1 ];then  
959
    adduser
960
    echo -e "\nAdded Percona XtraDB Cluster application user to the ProxySQL database!"
961
  fi
962
  if [ "$SYNCUSERS" == 1 ];then
963
    # Check for existing proxysql process
964
    syncusers
965
    echo -e "\nSynced Percona XtraDB Cluster users to the ProxySQL database!"
966
  fi
967
else
968
  echo "Usage: proxysql-admin <user credentials> {enable|disable}"
969
  usage
970
fi
971