Creating a Vitess POC

I look into Vitess, talking about the different benefits of distributed database services, and create a quick Proof Of Concept to show how the system is put together.

Installing vitess

First of we need the registry and mysql server installed so we can keep track on which services are available, download new services and also run the actual database.

sudo apt install -y default-mysql-server default-mysql-client etcd curl

Before we start this POC we disable the default services as we will be setting up our own.

sudo service mysql stop
sudo service etcd stop
sudo systemctl disable mysql
sudo systemctl disable etcd

Downloading Vitess, copying into the /usr/local placement.

wget https://github.com/vitessio/vitess/releases/download/v13.0.0/vitess-13.0.0-bc4a960.tar.gz
tar -xzf vitess-13.0.0-bc4a960.tar.gz
cd vitess-13.0.0-bc4a960
sudo mkdir -p /usr/local/vitess
sudo cp -r * /usr/local/vitess/

Setup path to the Vitess executabels.

export PATH=/usr/local/vitess/bin:${PATH}

We need to setup the controller port, cell name and the current hostname used for the registry.

export VTCTLD_WEB_PORT=15000
export CELL=zone1
export ETCDCTL_API=2
export HOSTNAME=192.168.6.54
export VTDATAROOT="${PWD}/vtdataroot"
export ETCD_SERVER="$HOSTNAME:2379"

We set these topology flags that we will use for all services so they could find eachother. Connecting in this case to the global namespace but if we set this up in a production environment we would register services in the current cell.

export TOPOLOGY_FLAGS="--topo_implementation etcd2 --topo_global_server_address $ETCD_SERVER --topo_global_root /vitess/global"

We need to create directories for the registry and other tmp files.

mkdir -p "${VTDATAROOT}/etcd"
mkdir -p "${VTDATAROOT}/tmp"

Next up we create all the aliases needed for different tools. These could be copied to your .bashrc so you have them available next time you open a console.

alias mysql="command mysql -h 127.0.0.1 -P 15306"
alias vtctlclient="command vtctlclient --server localhost:15999 --log_dir ${VTDATAROOT}/tmp --alsologtostderr"
alias vtctldclient="command vtctldclient --server localhost:15999"

Make sure aliases are expanded in non-interactive shell

shopt -s expand_aliases

Starting up the registry service so we can keep track on all the services.

etcd --enable-v2=true --data-dir "${VTDATAROOT}/etcd/"  --listen-client-urls "http://${ETCD_SERVER}" --advertise-client-urls "http://${ETCD_SERVER}" > "${VTDATAROOT}"/tmp/etcd.out 2>&1 &

We will create two namespaces too keep the global information for all cells as well as the local information for each service.

etcdctl --endpoints "http://${ETCD_SERVER}" mkdir /vitess/global &
etcdctl --endpoints "http://${ETCD_SERVER}" mkdir /vitess/${CELL} &

We run the control skill to setup the cell information. This will give us an error but I've not figured out a better way to run this.

vtctl -topo_implementation etcd2 -topo_global_server_address $ETCD_SERVER -topo_global_root /vitess/global AddCellInfo \
  -root /vitess/${CELL} \
  -server_address "${ETCD_SERVER}" \
  ${CELL}

The control server is handling control functions like deciding how the shards should move between different tablet. It can also switch which one is primary and so on.

vtctld \
 $TOPOLOGY_FLAGS \
 --cell $CELL \
 --workflow_manager_init \
 --workflow_manager_use_election \
 --service_map 'grpc-vtctl,grpc-vtctld' \
 --backup_storage_implementation file \
 --file_backup_storage_root $VTDATAROOT/backups \
 --log_dir $VTDATAROOT/tmp \
 --port $VTCTLD_WEB_PORT \
 --grpc_port 15999 \
 --pid_file $VTDATAROOT/tmp/vtctld.pid \
  > $VTDATAROOT/tmp/vtctld.out 2>&1 &

The code below could be copied out to a script, give it a name like create_node.sh

#!/bin/bash

TABLET_PORT=$[15000 + $1]
GRPC_PORT=$[16000 + $1]
MYSQL_PORT=$[17000 + $1]

mysqlctl \
 --log_dir $VTDATAROOT/tmp \
 --tablet_uid $1 \
 --mysql_port $MYSQL_PORT \
 init

mkdir -p $VTDATAROOT/vt_$1

vttablet \
 $TOPOLOGY_FLAGS \
 --log_dir $VTDATAROOT/tmp \
 --log_queries_to_file $VTDATAROOT/tmp/vttablet_${MYSQL_PORT}_querylog.txt \
 --tablet-path "${CELL}-$1" \
 --tablet_hostname "" \
 --init_keyspace commerce \
 --init_shard 0 \
 --init_tablet_type $2 \
 --health_check_interval 5s \
 --enable_semi_sync \
 --enable_replication_reporter \
 --backup_storage_implementation file \
 --file_backup_storage_root $VTDATAROOT/backups \
 --restore_from_backup \
 --port ${TABLET_PORT} \
 --grpc_port ${GRPC_PORT} \
 --service_map 'grpc-queryservice,grpc-tabletmanager,grpc-updatestream' \
 --pid_file $VTDATAROOT/vt_$1/vttablet.pid \
 --vtctld_addr http://$HOSTNAME:$VTCTLD_WEB_PORT/ \
 > $VTDATAROOT/vt_$1/vttablet.out 2>&1 &

After we created the script we can run it multiple times like this.

chmod +x create_node.sh
./create_node 100 replica
./create_node 101 rdonly

Next up we create the default shard and set the primary tablet to handle that shard.

vtctldclient PlannedReparentShard commerce/0 --new-primary zone1-100

These are not required in a production enviroment but for this test you could import a simple schema into your database so you could play around with some data.

vtctlclient  ApplySchema --sql-file ./vitess-13.0.0-bc4a960/examples/local/create_commerce_schema.sql commerce
vtctlclient  ApplyVSchema --vschema-file ./vitess-13.0.0-bc4a960/examples/local/vschema_commerce_initial.json commerce

Last but not least we will create gateways that we will call from mysql from all of our services. You could have one gateway on each of your host if you like and use a load balancer to spread the calls between them.

vtgate \
  $TOPOLOGY_FLAGS \
  --log_dir $VTDATAROOT/tmp \
  --log_queries_to_file $VTDATAROOT/tmp/vtgate_querylog.txt \
  --port 15001 \
  --grpc_port 15991 \
  --mysql_server_port 15306 \
  --mysql_server_socket_path "/tmp/mysql.sock" \
  --cell $CELL \
  --cells_to_watch $CELL \
  --tablet_types_to_wait PRIMARY,REPLICA \
  --service_map 'grpc-vtgateservice' \
  --pid_file $VTDATAROOT/tmp/vtgate.pid \
  --mysql_auth_server_impl none \
  > $VTDATAROOT/tmp/vtgate.out 2>&1 &