Today: July 13, 2020 11:06 am

Vitess and Workbench – Compatible pair ?

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 \
  -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/ \
  -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, 1000	77 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 ..

Share this Post :