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 bare metal Docker image 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. MoveTables 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 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 {vitess_folder}/examples/local 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 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 



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; 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 Enable Binary Logging Enable Binary Logging Add the code to your MySQL my.cnf my.cnf ini server-id = 1
log_bin = /var/lib/mysql/mysql-bin
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON ini server-id = 1
log_bin = /var/lib/mysql/mysql-bin
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON 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; Create Users 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; 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 User

Purpose



vt_dba

Used by vttablet for administrative tasks



vt_repl

For Replication and Binary log streaming



vt_app

Standard user for all your application queries



vt_allprivs

Similar to vt_dba but used for background tasks or scripts that require high level access



vt_filtered

Used for filtered replication. Allows vitess pull specific rows or columns during replication User

Purpose



vt_dba

Used by vttablet for administrative tasks



vt_repl

For Replication and Binary log streaming



vt_app

Standard user for all your application queries



vt_allprivs

Similar to vt_dba but used for background tasks or scripts that require high level access



vt_filtered

Used for filtered replication. Allows vitess pull specific rows or columns during replication User

Purpose User User User Purpose Purpose vt_dba

Used by vttablet for administrative tasks vt_dba vt_dba Used by vttablet for administrative tasks Used by vttablet for administrative tasks vttablet vt_repl

For Replication and Binary log streaming vt_repl vt_repl For Replication and Binary log streaming For Replication and Binary log streaming vt_app

Standard user for all your application queries vt_app vt_app Standard user for all your application queries Standard user for all your application queries vt_allprivs

Similar to vt_dba but used for background tasks or scripts that require high level access vt_allprivs vt_allprivs Similar to vt_dba but used for background tasks or scripts that require high level access Similar to vt_dba but used for background tasks or scripts that require high level access vt_dba vt_filtered

Used for filtered replication. Allows vitess pull specific rows or columns during replication vt_filtered vt_filtered Used for filtered replication. Allows vitess pull specific rows or columns during replication Used for filtered replication. Allows vitess pull specific rows or columns during replication 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 A single keyspace - my_db my_db One shard 0 0 Three tablets, one Primary two Secondary, each having their own MySQL instance Primary Secondary, 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 #!/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 my_db #!/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' & #!/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" 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 vtctldclient MoveTables --workflow migration --target-keyspace my_db status Move to Step 5, when the state is Running 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 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 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 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 reversetraffic vtctldclient MoveTables --workflow migration --target-keyspace my_db 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 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. 14201 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

How to Migrate Your Existing MySQL Database to Vitess

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Stop Buffering: Stream JSON to S3 in CSV Format Using Node.js and Axios

2019 Database Trends – SQL vs. NoSQL, Top Databases, Single vs. Multiple Database Use

5 Best Courses to Learn MySQL Database for Beginners

58 Stories To Learn About Mysql

The Noonification: Using Git Hooks with MySQL (9/30/2023)

A Basic Guide to MySQL Tests Automation

Stop Buffering: Stream JSON to S3 in CSV Format Using Node.js and Axios

2019 Database Trends – SQL vs. NoSQL, Top Databases, Single vs. Multiple Database Use

5 Best Courses to Learn MySQL Database for Beginners

58 Stories To Learn About Mysql

The Noonification: Using Git Hooks with MySQL (9/30/2023)

A Basic Guide to MySQL Tests Automation

Light-Mode

Classic

Newspaper

Minty

Dark-Mode

Neon Noir

Minty

HN StartUps