-
Notifications
You must be signed in to change notification settings - Fork 20
/
proxysql_menu.sh
executable file
·218 lines (206 loc) · 9.11 KB
/
proxysql_menu.sh
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
#!/bin/bash
user=admin
passwd=admin
host=127.0.0.1
port=6032
app_port=6033
pcmd="mysql -h $host -u$user -p$passwd -P$port "
while true
do
echo "ProxySQL admin"
options=(
"ProxySQL Admin Shell"
"MySQL Connect to 'mukka' via ProxySQL"
"MySQL Connect to 'rol' via ProxySQL"
"[runtime] Show servers"
"[runtime] Show users"
"[runtime] Show repliation_hostgroups"
"[runtime] Show query_rules"
"[stats] Show connection_pool"
"[stats] Show command_counters"
"[stats] Show query digest"
"[stats] Show hostgroups"
"[log] Show connect"
"[log] Show ping"
"[log] Show read_only"
"[test][mukka] sysbench prepare"
"[test][mukka] sysbench run - 15 sec, ro"
"[test][mukka] sysbench run - 60 sec, ro"
"[test][mukka] Split R/W"
"[test][mukka] Create 'world' sample db"
"[HA][mukka] MHA online failover (interactive)"
"[HA][mukka] MHA online failover (noninteractive)"
"[test][rol] sysbench prepare"
"[test][rol] sysbench run - 15 sec, ro"
"[test][rol] sysbench run - 60 sec, ro"
"[test][rol] Split R/W"
"[test][rol] Create 'world' sample db"
"[HA][rol] MHA online failover (interactive)"
"[HA][rol] MHA online failover (noninteractive)"
"Quit")
PS3='Please enter your choice: '
exec_query () {
query=$1
echo "####"
echo "Command: $pcmd -e '$query' "
echo "####"
$pcmd "-e $query"
}
exec_cmd () {
cmd=$1
echo "####"
echo "Command: $cmd "
echo "####"
$cmd
}
select opt in "${options[@]}"
do
case $opt in
"ProxySQL Admin Shell")
$pcmd
break
;;
"MySQL Connect to 'mukka' via ProxySQL")
cmd="mysql -h $host --user=app1 --password=app1 --port $app_port"
exec_cmd "$cmd"
break
;;
"MySQL Connect to 'rol' via ProxySQL")
cmd="mysql -h $host --user=app3 --password=app3 --port $app_port"
exec_cmd "$cmd"
break
;;
"[runtime] Show servers")
query="SELECT hostgroup_id as hg, hostname,port,status,weight,max_connections, comment FROM runtime_mysql_servers ORDER BY hostgroup_id,hostname ASC;"
exec_query "$query"
break
;;
"[runtime] Show users")
query="SELECT username,password,default_hostgroup as hg, active,max_connections FROM runtime_mysql_users;"
exec_query "$query"
break
;;
"[runtime] Show repliation_hostgroups")
query="SELECT * FROM runtime_mysql_replication_hostgroups"
exec_query "$query"
break
;;
"[runtime] Show query_rules")
query="SELECT rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, destination_hostgroup hg,apply FROM mysql_query_rules ORDER BY rule_id;"
exec_query "$query"
break
;;
"[stats] Show connection_pool")
query="SELECT * FROM stats.stats_mysql_connection_pool;"
exec_query "$query"
break
;;
"[stats] Show command_counters")
query="SELECT Command,Total_Time_us, Total_cnt FROM stats_mysql_commands_counters WHERE Total_cnt;"
exec_query "$query"
break
;;
"[stats] Show query digest")
query="SELECT hostgroup hg, sum_time, count_star, substr(digest_text,1,80) FROM stats_mysql_query_digest ORDER BY sum_time DESC LIMIT 15;"
exec_query "$query"
break
;;
"[stats] Show hostgroups")
query="SELECT hostgroup hg, SUM(sum_time), SUM(count_star) FROM stats_mysql_query_digest GROUP BY hostgroup;"
exec_query "$query"
break
;;
"[log] Show connect")
query="SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;"
exec_query "$query"
break
;;
"[log] Show ping")
query="SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 10;"
exec_query "$query"
break
;;
"[log] Show read_only")
query="SELECT * FROM monitor.mysql_server_read_only_log ORDER BY time_start_us DESC LIMIT 10;"
exec_query "$query"
break
;;
"[test][mukka] sysbench prepare")
cmd="sysbench --report-interval=5 --num-threads=4 --num-requests=0 --max-time=20 --test=/usr/share/doc/sysbench/tests/db/oltp.lua --mysql-user=app1 --mysql-password=app1 --oltp-table-size=10000 --mysql-host=$host --mysql-port=$app_port prepare"
exec_cmd "$cmd"
break
;;
"[test][mukka] sysbench run - 15 sec, ro")
cmd="sysbench --report-interval=1 --num-threads=4 --num-requests=0 --test=/usr/share/doc/sysbench/tests/db/oltp.lua --mysql-user=app1 --mysql-password=app1 --oltp-table-size=10000 --mysql-host=$host --mysql-port=$app_port --oltp-read-only=on --mysql-ignore-errors=all --max-time=15 run"
exec_cmd "$cmd"
break
;;
"[test][mukka] sysbench run - 60 sec, ro")
cmd="sysbench --report-interval=1 --num-threads=4 --num-requests=0 --test=/usr/share/doc/sysbench/tests/db/oltp.lua --mysql-user=app1 --mysql-password=app1 --oltp-table-size=10000 --mysql-host=$host --mysql-port=$app_port --oltp-read-only=on --mysql-ignore-errors=all --max-time=60 run"
exec_cmd "$cmd"
break
;;
"[HA][mukka] MHA online failover (interactive)")
cmd="masterha_master_switch --conf=/etc/mha/mha_damp_server_mukka.cnf --master_state=alive --orig_master_is_new_slave --interactive=1"
exec_cmd "$cmd"
break
;;
"[HA][mukka] MHA online failover (noninteractive)")
cmd="masterha_master_switch --conf=/etc/mha/mha_damp_server_mukka.cnf --master_state=alive --orig_master_is_new_slave --interactive=0"
exec_cmd "$cmd"
break
;;
"[test][mukka] Split R/W")
query="REPLACE INTO mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) VALUES(1000,1,'^select',2,0);LOAD MYSQL QUERY RULES TO RUNTIME;SAVE MYSQL QUERY RULES TO DISK;\G"
exec_query "$query"
break
;;
"[test][mukka] Create 'world' sample db")
cmd="wget -O /tmp/world.sql.gz http://downloads.mysql.com/docs/world.sql.gz"
exec_cmd "$cmd"
zcat /tmp/world.sql.gz | mysql -h $host --user=app1 --password=app1 --port $app_port
break
;;
"[test][rol] sysbench prepare")
cmd="sysbench --report-interval=5 --num-threads=4 --num-requests=0 --max-time=20 --test=/usr/share/doc/sysbench/tests/db/oltp.lua --mysql-user=app3 --mysql-password=app3 --oltp-table-size=10000 --mysql-host=$host --mysql-port=$app_port prepare"
exec_cmd "$cmd"
break
;;
"[test][rol] sysbench run - 15 sec, ro")
cmd="sysbench --report-interval=1 --num-threads=4 --num-requests=0 --test=/usr/share/doc/sysbench/tests/db/oltp.lua --mysql-user=app3 --mysql-password=app3 --oltp-table-size=10000 --mysql-host=$host --mysql-port=$app_port --oltp-read-only=on --mysql-ignore-errors=all --max-time=15 run"
exec_cmd "$cmd"
break
;;
"[test][rol] sysbench run - 60 sec, ro")
cmd="sysbench --report-interval=1 --num-threads=4 --num-requests=0 --test=/usr/share/doc/sysbench/tests/db/oltp.lua --mysql-user=app3 --mysql-password=app3 --oltp-table-size=10000 --mysql-host=$host --mysql-port=$app_port --oltp-read-only=on --mysql-ignore-errors=all --max-time=60 run"
exec_cmd "$cmd"
break
;;
"[HA][rol] MHA online failover (interactive)")
cmd="masterha_master_switch --conf=/etc/mha/mha_damp_server_rol.cnf --master_state=alive --orig_master_is_new_slave --interactive=1"
exec_cmd "$cmd"
break
;;
"[HA][rol] MHA online failover (noninteractive)")
cmd="masterha_master_switch --conf=/etc/mha/mha_damp_server_rol.cnf --master_state=alive --orig_master_is_new_slave --interactive=0"
exec_cmd "$cmd"
break
;;
"[test][rol] Split R/W")
query="REPLACE INTO mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) VALUES(1000,1,'^select',4,0);LOAD MYSQL QUERY RULES TO RUNTIME;SAVE MYSQL QUERY RULES TO DISK;\G"
exec_query "$query"
break
;;
"[test][rol] Create 'world' sample db")
cmd="wget -O /tmp/world.sql.gz http://downloads.mysql.com/docs/world.sql.gz"
exec_cmd "$cmd"
zcat /tmp/world.sql.gz | mysql -h $host --user=app3 --password=app3 --port $app_port
break
;;
"Quit")
exit
;;
*) echo invalid option;;
esac
done
done