How to Migrate Your Existing MySQL Database to Vitess

Vitess is a database solution that helps you deploy, scale, and manage large database clusters. It enables horizontal scaling by allowing you to shard your DB while keeping application changes to a minimum.

If you are looking to set up Vitess, you can set it up on bare metal, Docker image, or k8s

Moving a production database can come with its own challenges. In this article, I’d be guiding you through a Live Migration using the MoveTables workflow of how I and how you can migrate a production database to Vitess with very minimal downtime.

In this guide, I’m assuming you know what Vitess is and have it set up.

A brief recap about the Vitess Cluster

A Vitess cluster is a collection of components that work together to present a fault-tolerant, horizontally scalable MySQL, key components are:

• Topology Service (etcd, consul) - shares metadata about the clusters
• vtctld - admin or control plane service, exposes an API, a web UI to manage clusters
• vttablet - each tablet wraps a MySQL instance, manages replication, backup, and query handling. Can be either primary or replica
• vttate - entry point for applications, your external applications connect to the vtgate, not to MySQL; It translates your SQL into queries against the correct shard and also handles connection pooling, shard routing.
• keyspace - is similar to your MySQL DB
• shard - horizontal partition of a keyspace

P.S - while setting up Vitess, you’d have a local copy of Vitess, the scripts run in this tutorial would be run in the {vitess_folder}/examples/local folder

Steps to Migrate Existing MySQL

Step 1: Preparing your MySQL source

Before you can attach Vitess to your existing DB, you need to do the following

1. Enable Binary Logging

   Add the code to your MySQL my.cnf

   ini server-id = 1
   log_bin = /var/lib/mysql/mysql-bin
   binlog_format = ROW
   gtid_mode = ON
   enforce_gtid_consistency = ON 
   

2. Create Users

   CREATE USER 'vt_repl'@'%' IDENTIFIED BY 'REPL-PASS';
   GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'vt_repl'@'%';
   
   CREATE USER 'vt_dba'@'%' IDENTIFIED BY 'VTDBA-PASS';
   GRANT ALL PRIVILEGES ON *.* TO 'vt_dba'@'%';
   
   CREATE USER 'vt_app'@'%' IDENTIFIED BY 'VTAPP-PASS';
   GRANT ALL PRIVILEGES ON *.* TO 'vt_app'@'%';
   
   CREATE USER 'vt_allprivs'@'%' IDENTIFIED BY 'VTALLPRIVS-PASS';
   GRANT ALL PRIVILEGES ON *.* TO 'vt_allprivs'@'%';
   
   CREATE USER 'vt_filtered'@'%' IDENTIFIED BY 'VTFILTERED-PASS';
   GRANT ALL PRIVILEGES ON *.* TO 'vt_filtered'@'%';
   
   FLUSH PRIVILEGES;
   

The table gives a little breakdown of the various users

Restart your MySQL after making these changes

Step 2: Initiate a Vitess Cluster

This step initiates your Vitess cluster; this is the cluster you’d attach to your existing MySQL DB.

This script creates a single local Vitess cluster with:

• A single keyspace - my_db
• One shard 0
• Three tablets, one Primary two Secondary, each having their own MySQL instance
• vtgate for external applications to connect

#!/bin/bash
SIDECAR_DB_NAME=${SIDECAR_DB_NAME:-"_vt"}

# start topo server
if [ "${TOPO}" = "zk2" ]; then
 CELL=zone1 ../common/scripts/zk-up.sh
elif [ "${TOPO}" = "consul" ]; then
 CELL=zone1 ../common/scripts/consul-up.sh
else CELL=zone1 ../common/scripts/etcd-up.sh
fi
# start vtctld
CELL=zone1 ../common/scripts/vtctld-up.sh

# create keyspace
if vtctldclient GetKeyspace my_db > /dev/null 2>&1 ; then
 # Keyspace already exists: we could be running this 101 example on an non-empty VTDATAROOT
 vtctldclient SetKeyspaceDurabilityPolicy --durability-policy=semi_sync my_db || fail "Failed to set keyspace durability policy on the my_db keyspace"
else
 # Create the keyspace with the sidecar database name and set the
 # correct durability policy. Please see the comment above for
 # more context on using a custom sidecar database name in your
 # Vitess clusters.
 vtctldclient CreateKeyspace --sidecar-db-name="${SIDECAR_DB_NAME}" --durability-policy=semi_sync my_db || fail "Failed to create and configure the my_db keyspace"
fi

# start mysqlctls for keyspace my_db
# because MySQL takes time to start, we do this in parallel
for i in 300 301 302; do
 CELL=zone1 TABLET_UID=$i ../common/scripts/mysqlctl-up.sh &
done

sleep 2
echo "Waiting for mysqlctls to start..."
wait
echo "mysqlctls are running!"

# start vttablets for keyspace my_db
for i in 300 301 302; do
 CELL=zone1 KEYSPACE=my_db TABLET_UID=$i ../common/scripts/vttablet-up.sh
done

# start vtorc
../common/scripts/vtorc-up.sh

wait_for_healthy_shard my_db 0 || exit 1

echo "my_db keyspace is healthy!"

echo "Starting vtgate..."
# start vtgate
CELL=zone1 ../common/scripts/vtgate-up.sh

# start vtadmin

if [[ -n ${SKIP_VTADMIN} ]]; then
 echo -e "\nSkipping VTAdmin! If this is not what you want then please unset the SKIP_VTADMIN env variable in your shell."
else ../common/scripts/vtadmin-up.sh
fi

Step 3: Attach an existing vttablet to your existing MySQL instance

This Script attaches an unmanaged vttablet to your existing MySQL instance, this would be the gateway through which Vitess would migrate data from your existing MySQL DB to your target my_db keyspace

#!/bin/bash

source ../common/env.sh

vttablet \
 $TOPOLOGY_FLAGS \
 --log_dir $VTDATAROOT/tmp \
 --topo_implementation etcd2 \
 --alsologtostderr \
 --tablet-path zone1-0000000301 \
 --init_keyspace my_db \
 --init_db_name_override my_db \
 --init_shard 0 \
 --init_tablet_type replica \
 --unmanaged \
 --service_map 'grpc-queryservice,grpc-tabletmanager,grpc-updatestream' \
 --enable_replication_reporter \
 --port 15004 \
 --grpc_port 16003 \
 --db_host 127.0.0.1 \
 --db_port 3306 \
 --db_dba_user vt_dba \
 --db_dba_password 'VTDBA-PASS' \
 --db_repl_user vt_repl \
 --db_repl_password 'REPL-PASS' \
 --db_app_user vt_app \
 --db_app_password 'VTAPP-PASS' \
 --db_allprivs_user vt_allprivs \
 --db_allprivs_password 'VTALLPRIVS-PASS' \
 --db_filtered_user vt_filtered \
 --db_filtered_password 'VTFILTERED-PASS' &

Step 4: Start a MoveTables Workflow

This starts VReplication streams; copies existing data from your MySQL DB into the target keyspace, and kept in sync via replication

source ../common/env.sh

vtctldclient TabletExternallyReparented zone1-301 || fail "Failed to reparent my_db/0 after moving primary"
echo "my_db/0 reparented after moving primary"

vtctldclient MoveTables \
 --workflow migration \
 --target-keyspace my_db \
 create \
 --source-keyspace source_database \
 --all-tables \
 || fail "Failed to create MoveTables workflow"

Check the status of your migration by running the following script

vtctldclient MoveTables --workflow migration --target-keyspace my_db status

Move to Step 5, when the state is Running

Step 5: Switch Traffic and Clean up

Before switching traffic, you want to check that the source and target keyspaces match

vtctldclient VDiff --target-keyspace my_db --workflow migration show last

The response should look similar to this

VDiff Summary for my_db.migration (4c664dc2-eba9-11ec-9ef7-920702940ee0)
State:        completed
RowsCompared: 196
HasMismatch:  false
StartedAt:    2022-06-26 22:44:29
CompletedAt:  2022-06-26 22:44:31

After confirming there’s no mismatch, run the script to switch traffic to your target keyspace

vtctldclient MoveTables --workflow migration --target-keyspace my_db switchtraffic

If an error occurs at this point, you can easily switch traffic in the reverse direction using reversetraffic

vtctldclient MoveTables --workflow migration --target-keyspace my_db reversetraffic

Run the complete script to complete the workflow, cleanup, and detach the source tablet

vtctldclient MoveTables --workflow migration --target-keyspace my_db complete

Your data should have been migrated completely now. You can visit your Vitess admin dashboard at port 14201 to view your keyspace, which should contain all of your migrated tables and data across 3 tablets.

Conclusion

We’ve successfully migrated our MySQL data to Vitess using the MoveTables Workflow with minimal downtime.

The next step would be to shard the data to start enjoying the benefits of Vitess horizontal scalability.

Please let me know if you have any questions or want a follow-up on sharding and resharding workflows