Workbench is the most popular mysql client GUI tool . Does it work well with Vitess ? We are using sample cluster given in the docs. Ensure that you have 3 mysql instances, 3 vttablets , vtgate ,etcd and vtctld are running by executing “pgrep -fl vtdata”
-bash-4.2$ pgrep -fl vtdata 19684 etcd 19723 vtctld 19775 mysqld 19812 vttablet 19878 mysqld 19916 vttablet 19988 mysqld 20024 vttablet 20119 vtgate
You can also validate Vitess cluster health by visiting below URL (IP has to be replaced by yours)
HealthCheck Tablet Cache
| Cell | Keyspace | Shard | TabletType | TabletStats | ||
|---|---|---|---|---|---|---|
| Zone 1 | commerce | 0 | MASTER | zone-1-0000000100(MasterTS: 1593275068) | ||
| Zone 1 | commerce | 0 | REPLICA | zone-1-0000000101(RepLag: 0) | ||
| Zone 1 | commerce | 0 | RDONLY | zone-1-0000000102(RepLag: 0) | ||
Validate vtgate ports :
We are going to talk to cluster via vtgate. You can validate vtgate ports by using below procedure :
netstat -nltp | grep -i vtgate
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp6 0 0 :::15991 :::* LISTEN 20119/vtgate
tcp6 0 0 :::15001 :::* LISTEN 20119/vtgate
tcp6 0 0 :::15306 :::* LISTEN 20119/vtgate
15991 → GRPC_PORT
15306 → MySQL_PORT
15001 → VTGATE_PORT(You can view the UI here)
Validate from command line if you can access vtgate via 15306 port :
If you are not able to connect , please check if appropriate firewalls rule exist . Below screenshot shows that we are able to connect using command line via 15306
Connecting via workbench :
Once we validated access via commandline , we can proceed to test workbench connection as illustrated below :
Surprise #1 : Can connect with any user/password
First thing that may surprise you is that you can connect to mysql without password. Why is that ? As we dig into the code we notice that authentication flag is missing –mysql_auth_server_static_file “./mysql_auth_server_static_creds.json”. This was done intentionally as this is a test environment for learning and exploring vitess .
How to modify this behaviour ?
You can add the -mysql_auth_server_static_file to point to credentials file and restart vtgate(NOTE: There is another method to do this which we are not detailing here)
# Start vtgate. # shellcheck disable=SC2086 vtgate \ $TOPOLOGY_FLAGS \ -log_dir $VTDATAROOT/tmp \ -log_queries_to_file $VTDATAROOT/tmp/vtgate_querylog.txt \ -port $web_port \ -grpc_port $grpc_port \ -mysql_server_port $mysql_server_port \ -mysql_server_socket_path $mysql_server_socket_path \ -cell $cell \ -cells_to_watch $cell \ -tablet_types_to_wait MASTER,REPLICA \ -gateway_implementation discoverygateway \ -service_map 'grpc-vtgateservice' \ -pid_file $VTDATAROOT/tmp/vtgate.pid \ -mysql_auth_server_impl none \ > $VTDATAROOT/tmp/vtgate.out 2>&1 &
Surprise #2 : Cant see information_schema,mysql,performance_schema
Also you can notice that performance_schema,information_schema etc are not visible under schema list(Only commerce schema is present as seen in screenshot above) . One other noteworthy point is command like “use information_schema ; show tables” does not work
How can I query information_schema,performance_schema from workbench editor then ?Although they are not listed under schemas you can still run queries on information_schema,performance_schema like below without any issue
select * from information_schema.key_column_usage LIMIT 0, 100077 row(s) returned
0.172 sec / 0.000 sec
Conclusion : Workbench works partially for some use cases . You can query objects , run sql queries etc ..