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