KubeCon NA 2023 Vitess Data Migration Demo

demo.sh

#!/bin/bash

set -o pipefail

export SKIP_VTADMIN=1

function display () {
  echo -e "\033[33m${1}\033[0m"
}

function fail () {
  echo -e "\033[31m${1}\033[0m"
  exit 1
}

function wait_for_input() {
  echo
  read -rp "${1}"
  echo
}

function wait_for_keyspace_serving_in_vtgate() {
  if [[ -z ${1} ]]; then
    fail "A keyspace must be specified when waiting for a serving primary tablet in vtgate."
  fi
  local keyspace=${1}

  echo -n "Waiting for ${keyspace} keyspace to be serving in vtgate..."
  while true; do
    if mysql "${keyspace}" -e "show tables" &> /dev/null; then
      break
    fi
    echo -n "."
    sleep 1
  done
  echo
}


if [[ -z "${VTROOT}" ]]; then
  fail "\$VTROOT must be set to the location of the Vitess source."
fi

if [[ -z "${VTDATAROOT}" ]] || [[ ! -d "${VTDATAROOT}" || ! -w "${VTDATAROOT}" ]]; then
  fail "\$VTDATAROOT must be set to a directory which exists and we can write to."
fi

source "${VTROOT}/examples/common/env.sh" || fail "Failed to source env.sh. Is your \$VTROOT set correctly?"

display "Welcome to the KubeCon NA 2023 Vitess Migration Demo!"
wait_for_input "Press any key to start"

pushd "${VTROOT}/examples/local" > /dev/null || fail "Failed to change to the Vitess examples/local directory."

display "Setting up initial Vitess cluster and commerce keyspace...\n"
./101_initial_cluster.sh
wait_for_healthy_shard commerce 0
wait_for_keyspace_serving_in_vtgate commerce
mysql < ../common/insert_commerce_data.sql > /dev/null

display "\n\nNow we have a single unsharded Vitess keyspace called commerce with the following schema:"
mysql commerce -e "show tables"
mysql commerce -e "show create table corder\G show create table customer\G show create table product\G"
wait_for_input "Press any key to continue"

display "\nNow let's add a foreign key constraint so that when a customer is deleted so are their orders:"
mysql commerce -v -e "ALTER TABLE corder ADD FOREIGN KEY (customer_id) REFERENCES customer(customer_id) ON DELETE CASCADE"
display "\nAnd confirm that it's working as expected by trying to insert an orphan record in the corder table:\n"
mysql commerce -v -e "insert into corder values (100, 100, 'SKU-1001', 94.99)"
wait_for_input "Press any key to continue"

display "\nLet's see what current data we have:"
mysql --binary-as-hex=false commerce -v -e "select * from corder; select * from customer; select * from product"
wait_for_input "Press any key to continue"

display "\nNow let's add a second keyspace called customer. This will be sharded and this is where we'll migrate our customer related data to...\n"
vtctldclient CreateKeyspace --durability-policy=semi_sync customer || fail "Failed to create and configure the customer keyspace"
./302_new_shards.sh
wait_for_healthy_shard customer -80
wait_for_healthy_shard customer 80-
wait_for_keyspace_serving_in_vtgate customer
./301_customer_sharded.sh &> /dev/null

display "\nNow we have two keyspaces or databases:"
mysql commerce -v -e "show databases"
echo
display "\$ vtctldclient GetTablets" && vtctldclient GetTablets
wait_for_input "Press any key to continue"

display "\nNow let's start the MoveTables operation to migrate our customer and corder tables from the commerce database to the customer one:"
display '$ vtctldclient MoveTables --workflow commerce2customer --target-keyspace customer create --source-keyspace commerce --tables "customer,corder"' && vtctldclient MoveTables --workflow commerce2customer --target-keyspace customer create --source-keyspace commerce --tables "customer,corder"
echo
wait_for_workflow_running customer commerce2customer
wait_for_input "Press any key to continue"

display "\nNow if we look at the status we can see that the initial data has been copied and the workflow is in the running or replicating phase:"
display "\$ vtctldclient MoveTables --workflow commerce2customer --target-keyspace customer status" && vtctldclient MoveTables --workflow commerce2customer --target-keyspace customer status
wait_for_input "Press any key to continue"

display "\nNow we can use the VDiff command to ensure that our data was copied correctly:"
display "\$ vtctldclient vdiff --workflow=commerce2customer --target-keyspace=customer --format=json create" && vtctldclient vdiff --workflow=commerce2customer --target-keyspace=customer --format=json create
display "Waiting for vdiff to complete...\n"
while [[ $(vtctldclient vdiff --workflow=commerce2customer --target-keyspace=customer --format=json show last | jq -r '.State') != "completed" ]]; do
  sleep 1
done
display "VDiff results:"
display "\$ vtctldclient vdiff --workflow=commerce2customer --target-keyspace=customer --format=json show --verbose last" && vtctldclient vdiff --workflow=commerce2customer --target-keyspace=customer --format=json show --verbose last
wait_for_input "Press any key to continue"

commerce_primary_uid=$(vtctldclient GetTablets --keyspace=commerce --tablet-type=primary | awk '{print $1}' | cut -d '-' -f2)
customer_dash80_primary_uid=$(vtctldclient GetTablets --keyspace=customer --tablet-type=primary --shard=-80 | grep -i primary | awk '{print $1}' | cut -d '-' -f2)
customer_80dash_primary_uid=$(vtctldclient GetTablets --keyspace=customer --tablet-type=primary --shard=80- | grep -i primary | awk '{print $1}' | cut -d '-' -f2)

display "\nNow let's play with the data, first looking more closely at how our data is being housed and served...\n"
display "\$ vtctldclient GetRoutingRules" && vtctldclient GetRoutingRules
wait_for_input "Press any key to continue"
display "\$ mysql -e 'show vitess_tablets'" && mysql --binary-as-hex=false -e "show vitess_tablets"
display "\$ mysql commerce:0 -e 'select @@global.log_error'" && mysql --binary-as-hex=false commerce:0 -e "select @@global.log_error"
display "\$ mysql customer:-80 -e 'select @@global.log_error'" && mysql --binary-as-hex=false customer:-80 -e "select @@global.log_error"
display "\$ mysql customer:80- -e 'select @@global.log_error'" && mysql --binary-as-hex=false customer:80- -e "select @@global.log_error"
wait_for_input "Press any key to continue"

display "\nNow let's add a new customer and order in the commerce database and confirm that those changes are getting replicated...\n"
mysql --binary-as-hex=false -vv -e "insert into customer values (10, 'mlord@planetscale.com'); insert into corder values (100, 10, 'SKU-1001', 94.99)"
wait_for_input "Press any key to continue"

display "\nNow let's confirm that this new data was replicated to the customer database...\n"
while [[ $(command mysql -u root --socket="${VTDATAROOT}/vt_${customer_dash80_primary_uid}/mysql.sock" vt_customer --skip-column-names -s -e 'select * from corder' | wc -l) -lt 5 ]]; do
  sleep 1
done
display "\$ mysql -u root --socket=${VTDATAROOT}/vt_${customer_dash80_primary_uid}/mysql.sock vt_customer -e 'select * from customer; select * from corder'" && command mysql -v -u root --socket="${VTDATAROOT}/vt_${customer_dash80_primary_uid}/mysql.sock" --binary-as-hex=false vt_customer -e 'select * from customer; select * from corder'
echo
display "\$ mysql -u root --socket=${VTDATAROOT}/vt_${customer_80dash_primary_uid}/mysql.sock vt_customer -e 'select * from customer; select * from corder'" && command mysql -v -u root --socket="${VTDATAROOT}/vt_${customer_80dash_primary_uid}/mysql.sock" --binary-as-hex=false vt_customer -e 'select * from customer; select * from corder'

display "\nNow that we've confirmed that everything is working, we're ready to switch our application traffic to the customer database...\n"
wait_for_input "Press any key to continue"

display "\nWe can run the VDiff again to confirm that the data is in sync before we switch traffic:\n"
display "\$ vtctldclient vdiff --workflow=commerce2customer --target-keyspace=customer --format=json create" && vtctldclient vdiff --workflow=commerce2customer --target-keyspace=customer --format=json create
display "Waiting for vdiff to complete...\n"
while [[ $(vtctldclient vdiff --workflow=commerce2customer --target-keyspace=customer --format=json show last | jq -r '.State') != "completed" ]]; do
  sleep 1
done
display "VDiff results:"
display "\$ vtctldclient vdiff --workflow=commerce2customer --target-keyspace=customer --format=json show --verbose last" && vtctldclient vdiff --workflow=commerce2customer --target-keyspace=customer --format=json show --verbose last
wait_for_input "Press any key to continue"

display "\nNow let's switch all of our traffic for the customer tables to the customer database...\n"
display "\$ vtctldclient MoveTables --workflow commerce2customer --target-keyspace customer switchtraffic" && vtctldclient MoveTables --workflow commerce2customer --target-keyspace customer switchtraffic
wait_for_input "Press any key to continue"

display "\nNow we can see that reflected in the routing rules:"
display "\$ vtctldclient GetRoutingRules" && vtctldclient GetRoutingRules
wait_for_input "Press any key to continue"

display "\nNow let's delete our new customer and confirm that those changes are getting replicated, including the cascaded deletion of their orders...\n"
mysql --binary-as-hex=false -vv -e "delete from customer where email = 'mlord@planetscale.com'"
wait_for_input "Press any key to continue"

display "\nNow let's confirm that these changes were replicated BACK to our original commerce database...\n"
while [[ $(command mysql -u root --socket="${VTDATAROOT}/vt_${commerce_primary_uid}/mysql.sock" vt_commerce --skip-column-names -s -e 'select * from corder' | wc -l) -gt 5 ]]; do
  sleep 1
done
display "\$ mysql -u root --socket=${VTDATAROOT}/vt_${commerce_primary_uid}/mysql.sock vt_commerce -e 'select * from customer; select * from corder'" && command mysql -v -u root --socket="${VTDATAROOT}/vt_${commerce_primary_uid}/mysql.sock" --binary-as-hex=false vt_commerce -e 'select * from customer; select * from corder'
wait_for_input "Press any key to continue"

display "\nIf for some reason we need to revert the traffic back to the commerce database, we can do so using ReverseTraffic:\n"
display "\$ vtctldclient MoveTables --workflow commerce2customer --target-keyspace customer reversetraffic" && vtctldclient MoveTables --workflow commerce2customer --target-keyspace customer reversetraffic
wait_for_input "Press any key to continue"

display "\nAnd finally we can correct any issue and then try again...\n"
display "\$ vtctldclient MoveTables --workflow commerce2customer --target-keyspace customer switchtraffic" && vtctldclient MoveTables --workflow commerce2customer --target-keyspace customer switchtraffic
wait_for_input "Press any key to continue"

display "\nOnce we've performed any final validation we can complete the migration...\n"
display "\$ vtctldclient MoveTables --workflow commerce2customer --target-keyspace customer complete" && vtctldclient MoveTables --workflow commerce2customer --target-keyspace customer complete
wait_for_input "Press any key to continue"

display "\nAnd now we can see that the customer tables only exist in the customer database...\n"
mysql --binary-as-hex=false -v -e "show tables from commerce; show tables from customer"
wait_for_input "Press any key to continue"

display "\nAnd lastly we can see that our workflow and the routing rules are now cleaned up as well...\n"
display "\$ vtctldclient workflow --keyspace customer list" && vtctldclient workflow --keyspace customer list
echo
display "\$ vtctldclient GetRoutingRules" && vtctldclient GetRoutingRules
wait_for_input "Press any key to finish"

popd > /dev/null || fail "Failed to go back to original directory."

display "\nThanks for joining! Please visit https://vitess.io/docs/ to learn more about Vitess."

exit 0