-
Notifications
You must be signed in to change notification settings - Fork 76
/
proxysql-admin
executable file
·3008 lines (2675 loc) · 107 KB
/
proxysql-admin
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
#!/bin/bash
# This script will assist with configuring ProxySQL
# (currently only Percona XtraDB cluster in combination with ProxySQL is supported)
# Version 2.0
###############################################################################################
# This program is copyright 2016-2020 Percona LLC and/or its affiliates.
#
# THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
# WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
# MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
#
# This program is free software; you can redistribute it and/or modify it under
# the terms of the GNU General Public License as published by the Free Software
# Foundation, version 2 or later
#
# You should have received a copy of the GNU General Public License version 2
# along with this program; if not, write to the Free Software Foundation, Inc.,
# 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.
#-------------------------------------------------------------------------------
# shellcheck disable=SC2046,SC2181,SC1091
#
# Step 1 : Bash internal configuration
#
set -o nounset # no undefined variables
set -o pipefail # internal pipe failures cause an exit
# Include the common functions
. $(dirname "${BASH_SOURCE[0]}")/proxysql-common
. $(dirname "${BASH_SOURCE[0]}")/proxysql-admin-common
#-------------------------------------------------------------------------------
#
# Step 2 : Global variables
#
#
# Script parameters/constants
#
# default timeout is 10 seconds
# shellcheck disable=SC2034
declare -i TIMEOUT=10
#
# Global variables used by the script
#
declare CONFIG_FILE="/etc/proxysql-admin.cnf"
declare PROXYSQL_DATADIR=""
# The login-file is the path to an encrypted file that
# contains the credentials for proxysql, PXC cluster,
# monitor, and cluster-app logins.
declare LOGIN_FILE=""
declare LOGIN_PASSWORD=""
declare LOGIN_PASSWORD_FILE=""
# Variables declared in proxysql-admin-common
# ----------------------------------------
# readonly PROXYSQL_ADMIN_VERSION="2.0.12"
# readonly REQUIRED_OPENSSL_VERSION="1.1.1"
# readonly PROXYSQL_ADMIN_OPENSSL_NAME="proxysql-admin-openssl"
# declare -i DEBUG=0
# declare PROXYSQL_USERNAME=""
# declare PROXYSQL_PASSWORD=""
# declare PROXYSQL_PORT=""
# declare PROXYSQL_HOSTNAME=""
# declare CLUSTER_USERNAME=""
# declare CLUSTER_PASSWORD=""
# declare CLUSTER_HOSTNAME=""
# declare CLUSTER_PORT=""
# declare CLUSTER_APP_USERNAME=""
# declare CLUSTER_APP_PASSWORD=""
# declare MONITOR_USERNAME=""
# declare MONITOR_PASSWORD=""
# The SAFE_XXX versions are the version that have
# been escaped. The single quotes will have been
# SQL-escaped, which means ' -> ''
# So these are the versions that should be used in any SQL query.
declare SAFE_MONITOR_USERNAME=""
declare SAFE_MONITOR_PASSWORD=""
declare WRITE_NODE=""
declare MODE="singlewrite"
declare -i NODE_CHECK_INTERVAL=-1
declare -i WRITER_HOSTGROUP_ID=-1
declare -i WRITER_HOSTGROUP_COMMAND_LINE=0
declare -i READER_HOSTGROUP_ID=-1
declare -i BACKUP_WRITER_HOSTGROUP_ID=-1
declare -i OFFLINE_HOSTGROUP_ID=-1
declare -i QUICK_DEMO=0
declare -i ENABLE=0
declare -i DISABLE=0
declare -i ADDUSER=0
declare -i SYNCUSERS=0
declare -i SYNCMULTICLUSTERUSERS=0
declare -i ADD_QUERY_RULE=0
declare -i UPDATE_CLUSTER=0
declare -i UPDATE_MYSQL_VERSION=0
declare -i CHECK_IF_ENABLED=0
declare -i FORCE=0
declare -i REPORT_STATUS=0
# This can be specifed by the "--server=IP:PORT" option for
# a single address. This is used by --syncusers to sync a
# single node (that does not need to be part of a cluster)
declare SINGLE_SERVER=""
declare -i USE_EXISTING_MONITOR_PASSWORD=0
declare -i WITH_CLUSTER_APP_USER=1
declare -i DISABLE_UPDATES=0
# If we have to disable updates, store the original values
# so that we can restore them back to these values.
# These variables have the value of 'true' | 'false'
declare ADMIN_CHECKSUM_MYSQL_QUERY_RULES_ORIGINAL_VALUE=""
declare ADMIN_CHECKSUM_MYSQL_SERVERS_ORIGINAL_VALUE=""
declare ADMIN_CHECKSUM_MYSQL_USERS_ORIGINAL_VALUE=""
declare ADMIN_CLUSTER_USERNAME_ORIGINAL_VALUE=""
declare ADMIN_CLUSTER_CHECK_INTERVAL_ORIGINAL_VALUE=""
declare MYSQL_CLIENT_VERSION=""
declare USE_SSL="no"
declare -i USE_SSL_OPTION=0
#
# Default value for max_connections in mysql_servers
#
declare MAX_CONNECTIONS="1000"
#
# Default value for max-transactions-behind
#
declare -i MAX_TRANSACTIONS_BEHIND=100
declare WRITERS_ARE_READERS="backup"
declare -i WRITERS_ARE_READERS_OPTION=-1
declare -i REMOVE_ALL_SERVERS=0
#
# Set these values if a parameter is needed by the operation
# (For example, --disable only needs --writer-hg)
#
declare NEEDS_WRITER_HOSTGROUP=0
declare NEEDS_READER_HOSTGROUP=0
declare NEEDS_BACKUP_WRITER_HOSTGROUP=0
declare NEEDS_OFFLINE_HOSTGROUP=0
#
# If set to 1, then calls to MySQL/ProxySQL via the MySQL client
# will have credentials passed via stdin rather than using bash
# process substitution.
#
declare USE_STDIN_FOR_CREDENTIALS=0
#-------------------------------------------------------------------------------
#
# Step 3 : Helper functions
#
#
# Dispay script usage details
#
function usage() {
local path=$0
cat << EOF
Usage: ${path##*/} [ options ]
Options:
--config-file=<config-file> Read login credentials from a configuration file
(command line options override any configuration file values)
--login-file=<login-file-path> Read login credentials from an encrypted file.
If the --login-password or --login-password-file options
are not specified, then the user will be prompted
for the password.
(command line options override any login file values)
--login-password=<password> The key used to decrypt the encrypted login-file.
This cannot be used with --login-password-file.
--login-password-file=<path> Read the key from a file using the <path>.
This cannot be used with --login-password
--writer-hg=<number> The hostgroup that all traffic will be sent to
by default. Nodes that have 'read-only=0' in MySQL
will be assigned to this hostgroup.
--backup-writer-hg=<number> If the cluster has multiple nodes with 'read-only=0'
and max_writers set, then additional nodes (in excess
of max_writers), will be assigned to this hostgroup.
--reader-hg=<number> The hostgroup that read traffic should be sent to.
Nodes with 'read-only=0' in MySQL will be assigned
to this hostgroup.
--offline-hg=<number> Nodes that are determined to be OFFLINE will
be assigned to this hostgroup.
--proxysql-datadir=<datadir> Specify the proxysql data directory location
--proxysql-username=<user_name> ProxySQL service username
--proxysql-password[=<password>] ProxySQL service password
--proxysql-port=<port_num> ProxySQL service port number
--proxysql-hostname=<host_name> ProxySQL service hostname
--cluster-username=<user_name> Percona XtraDB Cluster node username
--cluster-password[=<password>] Percona XtraDB Cluster node password
--cluster-port=<port_num> Percona XtraDB Cluster node port number
--cluster-hostname=<host_name> Percona XtraDB Cluster node hostname
--cluster-app-username=<user_name> Percona XtraDB Cluster node application username
--cluster-app-password[=<password>] Percona XtraDB Cluster node application password
--without-cluster-app-user Configure Percona XtraDB Cluster without an application user
--monitor-username=<user_name> Username for monitoring Percona XtraDB Cluster nodes through ProxySQL
--monitor-password[=<password>] Password for monitoring Percona XtraDB Cluster nodes through ProxySQL
--use-existing-monitor-password Do not prompt for a new monitor password if one is provided.
--node-check-interval=<NUMBER> The interval at which the proxy should connect
to the backend servers in order to monitor the
Galera staus of a node (in milliseconds).
(default: 5000)
--mode=[loadbal|singlewrite] ProxySQL read/write configuration mode
currently supporting: 'loadbal' and 'singlewrite'
(default: 'singlewrite')
--write-node=<IPADDRESS>:<PORT> Specifies the node that is to be used for
writes for singlewrite mode. If left unspecified,
the cluster node is then used as the write node.
This only applies when 'mode=singlewrite' is used.
--max-connections=<NUMBER> Value for max_connections in the mysql_servers table.
This is the maximum number of connections that
ProxySQL will open to the backend servers.
(default: 1000)
--max-transactions-behind=<NUMBER> Determines the maximum number of writesets a node
can have queued before the node is SHUNNED to avoid
stale reads.
(default: 100)
--use-ssl=[yes|no] If set to 'yes', then connections between ProxySQL
and the backend servers will use SSL.
(default: no)
--writers-are-readers=[yes|no|backup]
If set to 'yes', then all writers (backup-writers also)
are added to the reader hostgroup.
If set to 'no', then none of the writers (backup-writers also)
will be added to the reader hostgroup.
If set to 'backup', then only the backup-writers
will be added to the reader hostgroup.
(default: backup)
--remove-all-servers When used with --update-cluster, this will remove all
servers belonging to the current cluster before
updating the list.
--server=<IPADDRESS>:<PORT> Specifies the IP address and port for a single server. This can
be used with --syncusers or --sync-multi-cluster-users
to sync a single non-cluster server node.
--add-query-rule Create query rules for synced mysql user. This is applicable only
for singlewrite mode and works only with --syncusers
and --sync-multi-cluster-users options.
--force This option will skip existing configuration checks in mysql_servers,
mysql_users and mysql_galera_hostgroups tables. This option will
work with '--enable' and '--update-cluster'.
This will also cause certain checks to issue warnings instead
of an error.
--disable-updates Disable admin updates for ProxySQL cluster for the
current operation. The default is to not change the
admin variable settings. If this option is specifed,
these options will be set to false.
(default: updates are not disabled)
--use-stdin-for-credentials If set, then the MySQL client will use stdin to send credentials
to the client (instead of process substition).
(default: process subsitution is used)
--debug Enables additional debug logging.
--help Displays this help text.
These options are the possible operations for proxysql-admin.
One of the options below must be provided.
--adduser Adds the Percona XtraDB Cluster application user to the ProxySQL database
--disable, -d Remove any Percona XtraDB Cluster configurations from ProxySQL
--enable, -e Auto-configure Percona XtraDB Cluster nodes into ProxySQL
--update-cluster Updates the cluster membership, adds new cluster nodes
to the configuration.
--update-mysql-version Updates the mysql-server_version variable in ProxySQL with the version
from a node in the cluster.
--quick-demo Setup a quick demo with no authentication
--syncusers Sync user accounts currently configured in MySQL to ProxySQL
May be used with --enable. --server may be used with this
to specify a single server to sync.
(deletes ProxySQL users not in MySQL)
--sync-multi-cluster-users Sync user accounts currently configured in MySQL to ProxySQL
May be used with --enable. --server may be used with this
to specify a single server to sync.
(doesn't delete ProxySQL users not in MySQL)
--is-enabled Checks if the current configuration is enabled in ProxySQL.
--status Returns a status report on the current configuration.
If "--writer-hg=<NUM>" is specified, then the
data corresponding to the galera cluster with that
writer hostgroup is displayed. Otherwise, information
for all clusters will be displayed.
--version, -v Prints the version info
EOF
}
#
# Prints out the script version
#
# Globals:
# PROXYSQL_ADMIN_VERSION
#
# Parameters:
# None
#
function version()
{
local path=$0
printf "%s version %s\n" "${path##*/}" "${PROXYSQL_ADMIN_VERSION}"
}
# Check proxysql running status
#
# Globals:
# None
#
# Arguments:
# None
#
# Exits if we could not connect to the proxysql instance
#
function proxysql_connection_check() {
proxysql_exec "$LINENO" "SELECT 1" >/dev/null
check_cmd $? "$LINENO" "ProxySQL connection check failed."\
"\n-- Could not connect to ProxySQL at $PROXYSQL_HOSTNAME:$PROXYSQL_PORT"\
"\n-- Please check the ProxySQL connection parameters and status."
debug "$LINENO" "ProxySQL connection check succeeded"
}
# Check the PXC cluster running status
# (well one node in the cluster anyway)
#
# Globals:
# None
#
# Arguments:
# None
#
# Exits if we could not connect to a cluster node
#
function cluster_connection_check() {
mysql_exec "$LINENO" "SELECT @@PORT" >/dev/null
check_cmd $? "$LINENO" "Server connection check failed."\
"\n-- Could not connect to the server at $CLUSTER_HOSTNAME:$CLUSTER_PORT"\
"\n-- Please check the connection parameters and status."
debug "$LINENO" "server connection check succeeded"
}
# Queries the user for the proxysql connection parameters
#
# Globals:
# PROYXSQL_HOST
# PROXYSQL_PORT
# PROXYSQL_USERNAME
# PROXYSQL_PASSWORD
#
# Arguments:
# None
#
function quickdemo_get_proxysql_params() {
debug "$LINENO" "quickdemo_get_proxysql_params ()"
read -r -p "Do you want to use the default ProxySQL credentials (admin:admin:6032:127.0.0.1) [y/n] ? " check_param
case $check_param in
y|Y)
PROXYSQL_USERNAME="admin"
PROXYSQL_PASSWORD="admin"
PROXYSQL_PORT="6032"
PROXYSQL_HOSTNAME="127.0.0.1"
;;
n|N)
echo ""
echo -n "Enter the ProxySQL user name: "
read -r PROXYSQL_USERNAME
read -r -s -p "Enter the ProxySQL user password: " PROXYSQL_PASSWORD;echo ""
echo -n "Enter the ProxySQL port: "
read -r PROXYSQL_PORT
echo -n "Enter the ProxySQL hostname: "
read -r PROXYSQL_HOSTNAME
echo ""
;;
*)
error "" "Please type [y/n]!"
exit 1
;;
esac
}
# Queries the user for the PXC cluster connection parameters
#
# Globals:
# CLUSTER_HOSTNAME
# CLUSTER_PORT
# CLUSTER_USERNAME
# CLUSTER_PASSWORD
#
# Arguments:
# None
#
function quickdemo_get_cluster_params() {
debug "$LINENO" "quickdemo_get_cluster_params ()"
read -r -p "Do you want to use the default Percona XtraDB Cluster credentials (root::3306:127.0.0.1) [y/n] ? " check_param
case $check_param in
y|Y)
CLUSTER_USERNAME="root"
CLUSTER_PASSWORD=""
CLUSTER_PORT="3306"
CLUSTER_HOSTNAME="127.0.0.1"
;;
n|N)
echo ""
echo -n "Enter the Percona XtraDB Cluster username (super user): "
read -r CLUSTER_USERNAME
read -r -s -p "Enter the Percona XtraDB Cluster user password: " CLUSTER_PASSWORD; echo ""
echo -n "Enter the Percona XtraDB Cluster port: "
read -r CLUSTER_PORT
echo -n "Enter the Percona XtraDB Cluster hostname: "
read -r CLUSTER_HOSTNAME
echo ""
;;
*)
error "" "Please type [y/n]."
exit 1
;;
esac
}
# Checks for certain variables and prompts the user for
# the values if necessary.
#
# Globals:
# QUICK_DEMO
# MONITOR_USERNAME, MONITOR_PASSWORD
# CLUSTER_APP_USERNAME, CLUSTER_APP_PASSWORD
# CLUSTER_HOSTNAME, CLUSTER_USERNAME
# USER_HOST_RANGE
#
# Arguments:
# 1: the category for the variable, this may either be MONITOR or CLUSTER_APP
# 2: a description of the user
# 3: the hostgroup to associate the user with
# (only needed if user_category='CLISTER APP')
#
function user_input_check() {
debug "$LINENO" "user_input_check ( $(dump_arguments "$@") )"
local user_category=$1
local user_description=$2
local hostgroup_id
local username
local password
local safe_username
local safe_password
if [[ $user_category == "CLUSTER_APP" ]]; then
hostgroup_id=$3
fi
username=$(eval "echo \$${user_category}_USERNAME")
password=$(eval "echo \$${user_category}_PASSWORD")
if [[ -z $username ]]; then
read -r -p "Enter ${user_description}name : " "${user_category}"_USERNAME
username=$(eval "echo \$${user_category}_USERNAME")
while [[ -z ${username} ]]
do
echo -n "No input entered, Enter ${user_description} name: "
read -r "${user_category}"_USERNAME
username=$(eval "echo \$${user_category}_USERNAME")
done
else
if [[ $QUICK_DEMO -eq 0 ]]; then
echo -e "${user_description} name as per command line/config-file is ${BD}$(eval "echo \$${user_category}_USERNAME")${NBD}"
fi
fi
if [[ -z $password ]]; then
if [[ $QUICK_DEMO -eq 0 ]]; then
read -r -s -p "Enter ${user_description} password: " "${user_category}"_PASSWORD
password=$(eval "echo \$${user_category}_PASSWORD")
while [[ -z $password ]]
do
read -r -s -p "No input entered, Enter ${user_description} password: " "${user_category}"_PASSWORD
password=$(eval "echo \$${user_category}_PASSWORD")
done
fi
fi
username=$(eval "echo \$${user_category}_USERNAME")
password=$(eval "echo \$${user_category}_PASSWORD")
safe_username=${username//\'/\'\'}
safe_password=${password//\'/\'\'}
if [[ $user_category == "CLUSTER_APP" ]]; then
local check_user
check_user=$(mysql_exec "$LINENO" "SELECT user,host FROM mysql.user where user='$safe_username' and host='$USER_HOST_RANGE';")
check_cmd $? "$LINENO" "Failed to retrieve the user information from PXC."\
"\n-- Please check the PXC connection parameters and status."
if [[ -z "$check_user" ]]; then
if [[ $FORCE -eq 1 ]]; then
proxysql_exec "$LINENO" "DELETE FROM mysql_users where username='$safe_username';"
check_cmd $? "$LINENO" "Failed to delete the PXC application user: '$username' from the ProxySQL database."\
"\n-- Please check the ProxySQL connection parameters and status."
fi
local precheck_user
precheck_user=$(proxysql_exec "$LINENO" "SELECT username FROM mysql_users where username='$safe_username'")
check_cmd $? "$LINENO" "Failed to query ProxySQL for the user."\
"\n-- Please check the ProxySQL connection parameters and status."
if [[ -z "$precheck_user" ]]; then
mysql_exec "$LINENO" "CREATE USER '$safe_username'@'$USER_HOST_RANGE' IDENTIFIED WITH mysql_native_password BY '$safe_password';"
check_cmd $? "$LINENO" "Failed to add the PXC application user to PXC: $username" \
"\n-- Please check if '$CLUSTER_USERNAME'@'$CLUSTER_HOSTNAME' has the proper permissions to create the montioring user"
if [[ $QUICK_DEMO -eq 1 ]]; then
mysql_exec "$LINENO" "GRANT ALL ON *.* to '$safe_username'@'$USER_HOST_RANGE'"
check_cmd $? "$LINENO" "Failed to grant permissions to '$username'@'$USER_HOST_RANGE'"\
"\n-- Please check if $CLUSTER_USERNAME@'$CLUSTER_HOSTNAME' has the GRANT privilege"\
"\n-- required to assign the requested permissions"
fi
proxysql_exec "$LINENO" \
"INSERT INTO mysql_users
(username,password,active,default_hostgroup)
VALUES
('$safe_username','$safe_password',1,$hostgroup_id);"
check_cmd $? "$LINENO" "Failed to add the PXC application user: '$username' to the ProxySQL database."\
"\n-- Please check the ProxySQL connection parameters and status."
proxysql_load_to_runtime_save_to_disk "MYSQL USERS" "$LINENO" 1
if [[ $QUICK_DEMO -eq 0 ]]; then
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}"
else
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"
fi
else
error "$LINENO" "The application user ${BD}$username${NBD} is already present in the ProxySQL database."
echo -e "-- Note: ProxySQL does not allow duplicate usernames."
exit 1
fi
else
if [[ $FORCE -eq 1 ]]; then
proxysql_exec "$LINENO" "DELETE FROM mysql_users where username='$safe_username';"
check_cmd $? "$LINENO" "Failed to delete the PXC application user: '$safe_username' from the ProxySQL database."\
"\n-- Please check the ProxySQL connection parameters and status."
fi
local check_user
check_user=$(proxysql_exec "$LINENO" "SELECT username FROM mysql_users where username='$safe_username'")
check_cmd $? "$LINENO" "Could not retrieve the users from the ProxySQL database."\
"\n-- Please check the ProxySQL connection parameters and status."
if [[ -z "$check_user" ]]; then
echo -e "\nApplication user '${BD}${username}'@'$USER_HOST_RANGE${NBD}' already present in PXC.\n"
proxysql_exec "$LINENO" "INSERT INTO mysql_users (username,password,active,default_hostgroup) values ('$safe_username','$safe_password',1,$hostgroup_id);"
check_cmd $? "$LINENO" "Failed to add the PXC application user: '$username' to the ProxySQL database."\
"\n-- Please check the ProxySQL connection parameters and status."
proxysql_load_to_runtime_save_to_disk "MYSQL USERS" "$LINENO" 1
else
error "$LINENO" "The application user ${BD}$username${NBD} is already present in the ProxySQL database."
echo -e "-- Note: ProxySQL does not allow duplicate usernames."
exit 1
fi
fi
fi
}
# Gets the list of hostgroups given only the writer hostgroup
#
# Globals:
# None
#
# Arguments:
# Parameter 1: the writer hostgroup
#
# Outputs:
# A list of hostgroups, separated by commas (',')
# The order of the hostgroups is:
# 1. writer hostgroup
# 2. reader hostgroup
# 3. backup writer hostgroup
# 4. offline hostgroup
#
function get_all_hostgroups_from_writer_hostgroup()
{
local hg_list
hg_list=$(proxysql_exec "$LINENO" \
"SELECT writer_hostgroup || ',' || reader_hostgroup || ',' ||
backup_writer_hostgroup || ',' || offline_hostgroup
FROM mysql_galera_hostgroups
WHERE writer_hostgroup = $WRITER_HOSTGROUP_ID")
check_cmd "$?" "$LINENO" "Cannot retrieve hostgroup information from ProxySQL" \
"\n-- Please check the ProxySQL configuration and status."
echo "$hg_list"
}
# Adds the list of servers to ProxySQL
# This will add all of the servers in the list to the WRITER hostgroup
# ProxySQL is responsible for moving them to the correct hostgroup
#
# Globals:
# WRITER_HOSTGROUP_ID
# MAX_CONNECTIONS
# USE_SSL_OPTION
#
# Arguments:
# Parameter 1 : A list of servers (space-separated in a string)
#
function add_servers_to_proxysql()
{
local server_list=$1
for i in ${server_list}; do
local ws_address ws_ip ws_port
ws_address=$(separate_ip_port_from_address "$i")
ws_ip=$(echo "$ws_address" | cut -d' ' -f1)
ws_port=$(echo "$ws_address" | cut -d' ' -f2)
proxysql_exec "$LINENO" \
"INSERT INTO mysql_servers
(hostname,hostgroup_id,port,weight,max_connections,use_ssl)
VALUES
('$ws_ip',$WRITER_HOSTGROUP_ID,$ws_port,1000,$MAX_CONNECTIONS,$USE_SSL_OPTION);"
check_cmd $? "$LINENO" "Failed to add the PXC server node with address: $i."\
"\n-- Please check the ProxySQL connection parameters and status."
done
}
# Auto configure Percona XtraDB Cluster nodes into ProxySQL
#
# Globals:
# READER_HOSTGROUP_ID, WRITER_HOSTGROUP_ID, BACKUP_WRITER_HOSTGROUP_ID, OFFLINE_HOSTGROUP_ID
# CLUSTER_PORT
# USER_HOST_RANGE
# MONITOR_USERNAME, MONITOR_PASSWORD
# CLUSTER_APP_USERNAME, CLUSTER_APP_PASSWORD
# CLUSTER_USERNAME, CLUSTER_HOSTNAME
# USE_EXISTING_MONITOR_PASSWORD
# WITH_CLUSTER_APP_USER
# QUICK_DEMO
# WRITE_NODE
# MODE
#
# Arguments:
# None
#
function enable_proxysql() {
debug "$LINENO" "enable_proxysql ()"
# Checking proxysql binary location
if [[ ! -e $(which proxysql 2> /dev/null) ]]; then
error "$LINENO" "The proxysql binary was not found."\
"\n-- Please install the ProxySQL package."
exit 1
fi
proxysql_connection_check
! native_scheduler_capability_check "$LINENO" && exit 1
cluster_connection_check
local cluster_name
cluster_name=$(mysql_exec "$LINENO" "select @@wsrep_cluster_name;")
check_cmd $? "$LINENO" "Could not retrieve the cluster name from the PXC cluster at $CLUSTER_HOSTNAME:$CLUSTER_PORT"\
"\n-- Please check the PXC connection parameters and status."
readonly cluster_name
debug "$LINENO" "PXC cluster name is : $cluster_name"
local all_hostgroups="$WRITER_HOSTGROUP_ID,$READER_HOSTGROUP_ID,$BACKUP_WRITER_HOSTGROUP_ID,$OFFLINE_HOSTGROUP_ID"
local check_hgs
# First, check to see if mysql_galera_hostgroups is using any of these
# hostgroups. This check will be ignored if we use --force option.
if [[ $FORCE -ne 1 ]]; then
check_hgs=$(proxysql_exec "$LINENO" \
"SELECT COUNT(*)
FROM mysql_galera_hostgroups
WHERE
writer_hostgroup IN ($all_hostgroups) OR
reader_hostgroup IN ($all_hostgroups) OR
backup_writer_hostgroup IN ($all_hostgroups) OR
offline_hostgroup IN ($all_hostgroups)")
check_cmd $? "$LINENO" "Could not retrieve hostgroup information from ProxySQL"\
"\n-- Please check the ProxySQL connection parameters and status."
if [[ -n $check_hgs && $check_hgs -ne 0 ]]; then
error "$LINENO" "One or more of the hostgroups($all_hostgroups) is already being used" \
"\n-- by ProxySQL in mysql_galera_hostgroups." \
"\n-- To avoid conflicts, please use different values for the hostgroups."
proxysql_exec "$LINENO" \
"SELECT
writer_hostgroup AS writer,
reader_hostgroup AS reader,
backup_writer_hostgroup AS backup_writer,
offline_hostgroup AS offline,
comment
FROM mysql_galera_hostgroups
WHERE
writer_hostgroup IN ($all_hostgroups) OR
reader_hostgroup IN ($all_hostgroups) OR
backup_writer_hostgroup IN ($all_hostgroups) OR
offline_hostgroup IN ($all_hostgroups)" "-t"
echo ""
exit 1
fi
fi
# Now, check to see if there are any entries in mysql_servers
# using these hostgroups. This check will be ignored if we use --force option.
if [[ $FORCE -ne 1 ]]; then
check_hgs=$(proxysql_exec "$LINENO" \
"SELECT hostgroup_id
FROM mysql_servers
WHERE
hostgroup_id IN ($all_hostgroups) AND
status <> 'OFFLINE_HARD'")
check_cmd $? "$LINENO" "Could not retrieve hostgroup and server information from ProxySQL"\
"\n-- Please check the ProxySQL connection parameters and status."
if [[ -n "$check_hgs" ]]; then
error "$LINENO" "One or more of the hostgroups($all_hostgroups) has a server assigned to that hostgroup."\
"\n-- To avoid conflicts, please use different values for the hostgroups."
proxysql_exec "$LINENO" \
"SELECT
hostgroup_id,
hostname,
port,
status,
weight
FROM mysql_servers
WHERE
hostgroup_id IN ($all_hostgroups)" "-t"
echo ""
exit 1
fi
fi
# Clear out any servers that are OFFLINE_HARD
# If --disable is called, then --enable is called quickly afterwards,
# ProxySQL may still be updating the previous server list. So this
# will remove any leftovers in the runtime_mysql_servers table
proxysql_exec "$LINENO" \
"DELETE
FROM mysql_servers
WHERE
hostgroup_id in ($all_hostgroups) AND
status = 'OFFLINE_HARD'"
check_cmd $? "$LINENO" "Could not delete offline servers from ProxySQL"\
"\n-- Please check the ProxySQL connection parameters and status."
local cluster_network
local wsrep_addresses
wsrep_addresses=$(mysql_exec "$LINENO" "show status like 'wsrep_incoming_addresses'")
check_cmd $? "$LINENO" "Could not retrieve the cluster addresses from PXC."\
"\n-- Please check the PXC connection parameters and status."
wsrep_addresses=$(echo "$wsrep_addresses" | awk '{print $2}' | sed 's|,| |g')
cluster_network=$(echo "$wsrep_addresses" | cut -d' ' -f1 | cut -d'.' -f1)
# If we have only digits in $cluster_network, then we can assume
# that we have an IPv4 address
if [[ "$cluster_network" =~ ^[0-9]+$ ]]; then
USER_HOST_RANGE="$cluster_network.%"
else
USER_HOST_RANGE="%"
fi
# More sanity checks
local writer_ws_ip
local writer_ws_port
local writer_ws_address=""
# If we have specified a write-node, check that the node is NOT read-only
if [[ $MODE == 'singlewrite' ]]; then
# Ensure that we have a writer node at this point
if [[ -n "$WRITE_NODE" ]]; then
writer_ws_address=$(separate_ip_port_from_address "$WRITE_NODE")
writer_ws_ip=$(echo "$writer_ws_address" | cut -d' ' -f1)
writer_ws_port=$(echo "$writer_ws_address" | cut -d' ' -f2)
if [ -z "$writer_ws_port" ];then
writer_ws_port=3306
fi
writer_ws_address=$(combine_ip_port_into_address "$writer_ws_ip" "$writer_ws_port")
local is_read_only
is_read_only=$(exec_sql "$LINENO" "$CLUSTER_USERNAME" "$CLUSTER_PASSWORD" "$writer_ws_ip" "$writer_ws_port" \
"select @@global.read_only")
if [ $? -ne 0 ]; then
error "$LINENO" "Failed to establish a connection to the write node $writer_ws_address."\
"\n-- Please check that the write node is alive and the connection parameters are correct";
proxysql_exec "$LINENO" "DELETE FROM mysql_users WHERE default_hostgroup in ($WRITER_HOSTGROUP_ID,$READER_HOSTGROUP_ID,$BACKUP_WRITER_HOSTGROUP_ID,$OFFLINE_HOSTGROUP_ID);"
check_cmd $? "$LINENO" "Failed to delete PXC user from ProxySQL."\
"\n-- Please check the ProxySQL connection parameters and status."
exit 1
fi
if [[ $is_read_only -eq 1 ]]; then
if [[ $FORCE -eq 1 ]]; then
warning "$LINENO" "The specified write node ($WRITE_NODE) is read-only"
else
error "$LINENO" "The specified write node ($WRITE_NODE) is read-only" \
"\n-- and cannot be used as a writer node."
exit 1
fi
fi
else
# If there is no WRITE_NODE specified, check that we have at least
# one write node available
for i in $wsrep_addresses; do
[[ -z $i ]] && continue;
local ws_address ws_ip ws_port
ws_address=$(separate_ip_port_from_address "$i")
ws_ip=$(echo "$ws_address" | cut -d' ' -f1)
ws_port=$(echo "$ws_address" | cut -d' ' -f2)
local is_read_only
is_read_only=$(exec_sql "$LINENO" "$CLUSTER_USERNAME" "$CLUSTER_PASSWORD" "$ws_ip" "$ws_port" \
"select @@global.read_only")
if [[ $? -eq 0 && $is_read_only -eq 0 ]]; then
writer_ws_ip=$ws_ip
writer_ws_port=$ws_port
writer_ws_address=$(combine_ip_port_into_address "$writer_ws_ip" "$writer_ws_port")
break
fi
done
fi
if [[ -z $writer_ws_address ]]; then
error "$LINENO" "Could not find a writer node." \
"\n-- Cluster nodes checked: $wsrep_addresses"
exit 1
fi
if [[ " ${wsrep_addresses} " != *"$writer_ws_address"* ]]; then
error "$LINENO" "Writer node cluster address($writer_ws_address) does not exist"\
"\n-- in WSREP incoming address(${wsrep_addresses})."
echo -e "-- Different wsrep incoming and cluster IP addresses are not supported"
echo -e "-- by proxysql-admin at this time. Please configure ProxySQL manually."
exit 1
fi
# Check that we do not have ANY read-only nodes since they will
# be ignored. We'll have to do this until this bug is fixed.
# https://github.com/sysown/proxysql/issues/2014
if [[ $WRITERS_ARE_READERS == "backup" ]]; then
for i in $wsrep_addresses; do
[[ -z $i ]] && continue;
local ws_address ws_ip ws_port
ws_address=$(separate_ip_port_from_address "$i")
ws_ip=$(echo "$ws_address" | cut -d' ' -f1)
ws_port=$(echo "$ws_address" | cut -d' ' -f2)
local is_read_only
is_read_only=$(exec_sql "$LINENO" "$CLUSTER_USERNAME" "$CLUSTER_PASSWORD" "$ws_ip" "$ws_port" \
"select @@global.read_only")
if [[ $? -eq 0 && $is_read_only -eq 1 ]]; then
writer_ws_ip=$ws_ip
writer_ws_port=$ws_port
writer_ws_address=$(combine_ip_port_into_address "$writer_ws_ip" "$writer_ws_port")
error "$LINENO" "Found a read-only node($writer_ws_address) when using" \
"\n-- --writers-are-readers=backup. This is not allowed since read-only nodes" \
"\n-- are ignored when using --writers-are-readers=backup (which is the default)"
exit 1
fi
done
fi
fi
echo -e "Configuring the ProxySQL monitoring user."
user_input_check MONITOR "ProxySQL monitor user"
SAFE_MONITOR_USERNAME=${MONITOR_USERNAME//\'/\'\'}
SAFE_MONITOR_PASSWORD=${MONITOR_PASSWORD//\'/\'\'}
readonly MONITOR_USERNAME
local check_user
check_user=$(mysql_exec "$LINENO" \
"SELECT user,host
FROM mysql.user
WHERE user='${SAFE_MONITOR_USERNAME}' AND host='$USER_HOST_RANGE';")
check_cmd $? "$LINENO" "Could not retrieve the monitor user information from PXC."\
"\n-- Please check the PXC cluster connection parameters and status."
if [[ -z "$check_user" ]]; then
# No monitor user found in MySQL, create the monitor user
mysql_exec "$LINENO" "CREATE USER '$SAFE_MONITOR_USERNAME'@'$USER_HOST_RANGE' IDENTIFIED WITH mysql_native_password BY '$SAFE_MONITOR_PASSWORD';"
check_cmd $? "$LINENO" "Failed to create the ProxySQL monitoring user."\
"\n-- Please check that '$CLUSTER_USERNAME'@'$CLUSTER_HOSTNAME' has the proper permissions to create the montioring user"
proxysql_exec "$LINENO" \
"UPDATE global_variables
SET variable_value='$SAFE_MONITOR_USERNAME'
WHERE variable_name='mysql-monitor_username';
UPDATE global_variables
SET variable_value='$SAFE_MONITOR_PASSWORD'
WHERE variable_name='mysql-monitor_password'; "
check_cmd $? "$LINENO" "Failed to set the mysql-monitor variables in ProxySQL."\
"\n-- Please check the ProxySQL connection parameters and status."
proxysql_load_to_runtime_save_to_disk "MYSQL VARIABLES" $LINENO
echo -e "\nUser '${BD}$MONITOR_USERNAME'@'$USER_HOST_RANGE${NBD}' has been added with USAGE privileges"
else
# Monitor user found in MySQL, do we want to use this existing user?
local check_param
if [[ $USE_EXISTING_MONITOR_PASSWORD -eq 1 ]]; then
# We have a password, so no need to ask for a new password
check_param="n"
else
echo ""
echo -e "The monitoring user is already present in Percona XtraDB Cluster.\n"
read -r -p "Would you like to enter a new password [y/n] ? " check_param
fi
case $check_param in
y|Y)
read -r -s -p "Please enter the password you have assigned to the monitoring user '$MONITOR_USERNAME': " MONITOR_PASSWORD
echo ""
SAFE_MONITOR_PASSWORD=${MONITOR_PASSWORD//\'/\'\'}
monitor_exec "$LINENO" "$MONITOR_USERNAME" "$MONITOR_PASSWORD" "-Bs" "SELECT 1" >/dev/null
check_cmd $? "$LINENO" "Failed to connect to Percona XtraDB Cluster using monitor credentials."\
"\n-- Please check MONITOR_USERNAME and MONITOR_PASSWORD"
proxysql_exec "$LINENO" \
"UPDATE global_variables
SET variable_value='$SAFE_MONITOR_USERNAME'
WHERE variable_name='mysql-monitor_username';
UPDATE global_variables
SET variable_value='$SAFE_MONITOR_PASSWORD'
WHERE variable_name='mysql-monitor_password'; "
check_cmd $? "$LINENO" "Failed to set the mysql-monitor variables in ProxySQL."\
"\n-- Please check the ProxySQL connection parameters"
proxysql_load_to_runtime_save_to_disk "MYSQL VARIABLES" $LINENO
echo -e "\nMonitoring user '${BD}$MONITOR_USERNAME'@'$USER_HOST_RANGE${NBD}' has been setup in the ProxySQL database."
;;
n|N)
monitor_exec "$LINENO" "$MONITOR_USERNAME" "$MONITOR_PASSWORD" "-Bs" "SELECT 1" >/dev/null
check_cmd $? "$LINENO" "Failed to connect to Percona XtraDB Cluster using monitor credentials."\
"\n-- Please check MONITOR_USERNAME and MONITOR_PASSWORD"
proxysql_exec "$LINENO" \
"UPDATE global_variables
SET variable_value='$SAFE_MONITOR_USERNAME'
WHERE variable_name='mysql-monitor_username';
UPDATE global_variables
SET variable_value='$SAFE_MONITOR_PASSWORD'
WHERE variable_name='mysql-monitor_password';"
check_cmd $? "$LINENO" "Failed to set the mysql-monitor variables in ProxySQL."\
"\n-- Please check the ProxySQL connection parameters"
proxysql_load_to_runtime_save_to_disk "MYSQL VARIABLES" $LINENO
echo -e "\nMonitoring user '${BD}$MONITOR_USERNAME'@'$USER_HOST_RANGE${NBD}' has been setup in the ProxySQL database."
;;
*)
error "" "Please type [y/n]!"
exit 1
;;
esac
fi
debug "$LINENO" "monitor username is '$MONITOR_USERNAME'"
if [[ $WITH_CLUSTER_APP_USER -eq 1 ]]; then
echo -e "\nConfiguring the Percona XtraDB Cluster application user to connect through ProxySQL"
user_input_check CLUSTER_APP "Percona XtraDB Cluster application user" $WRITER_HOSTGROUP_ID
SAFE_CLUSTER_APP_USERNAME=${CLUSTER_APP_USERNAME//\'/\'\'}
readonly CLUSTER_APP_USERNAME
readonly CLUSTER_APP_PASSWORD
fi
# Adding Percona XtraDB Cluster nodes to ProxySQL
# -----------------------------------------------
if [[ $MODE == 'singlewrite' ]]; then
if [[ $QUICK_DEMO -ne 0 ]]; then
writer_ws_ip=$(mysql_exec "$LINENO" "show variables like 'wsrep_provider_options'" | grep -o -P '(?<=base_host =).*(?=; base_port)' | xargs)
check_cmd $? "$LINENO" "Could not get the wsrep_provider_options from the PXC cluster"\
"\n-- Please check the PXC connection parameters and status."
writer_ws_port=$CLUSTER_PORT
writer_ws_address=$(combine_ip_port_into_address "$writer_ws_ip" "$writer_ws_port")