Skip to content

Instantly share code, notes, and snippets.

@jawabuu

jawabuu/vitess-automigrate.sh

Last active April 3, 2023 10:24
Show Gist options
  • Save jawabuu/da27bb6bbb562d9caa5d1692955c892a to your computer and use it in GitHub Desktop.
Save jawabuu/da27bb6bbb562d9caa5d1692955c892a to your computer and use it in GitHub Desktop.
Download ZIP
Migrate your existing mysql database into Vitess automatically
Raw
vitess-automigrate.sh
#!/usr/bin/env bash
## This installs the Vitess Operator
kubectl kustomize github.com/planetscale/vitess-operator.git/deploy?ref=v2.8.2 | kubectl apply -f -
## This creates your Vitess Cluster
kubectl apply -f vitess-cluster.yml
## Edit vitess-cluster.yml to match your existing db variables and your desired vitess cluster variables.
## In VitessCluster update;
## externalDatastore.user: externalDatastore.host externalDatastore.port & externalDatastore.database
## In the example-cluster-config Secret update;
## external_users.json with your existing mysql user & password
## users.json with your desired vitess mysql user & password
## orc_config.json with your desired MySQLTopologyUser & MySQLTopologyPassword
## init_db.sql with your desired MySQLTopologyUser & MySQLTopologyPassword
Raw
vitess-cluster.yml
apiVersion: planetscale.com/v2
kind: VitessCluster
metadata:
name: example
namespace: default
spec:
images:
vtorc: vitess/lite:v15.0.2
vtadmin: vitess/lite:v15.0.2
vtctld: vitess/lite:v15.0.2
vtgate: vitess/lite:v15.0.2
vttablet: vitess/lite:v15.0.2
vtbackup: vitess/lite:v15.0.2
mysqld:
mysql56Compatible: vitess/lite:v15.0.2
mysqldExporter: prom/mysqld-exporter:v0.11.0
globalLockserver:
etcd:
resources:
requests:
cpu: 75m
memory: 75Mi
limits:
memory: 75Mi
cells:
- name: zone1
gateway:
extraFlags:
enable_system_settings: "true"
authentication:
static:
secret:
name: example-cluster-config
key: users.json
secureTransport:
required: false
replicas: 1
resources:
requests:
cpu: 75m
memory: 100Mi
limits:
memory: 100Mi
vitessDashboard:
cells:
- zone1
extraEnv:
- name: MY_NODE_NAME
valueFrom:
fieldRef:
fieldPath: spec.nodeName
extraFlags:
security_policy: read-only
remote_operation_timeout: 600s
action_timeout: 600s
replicas: 1
resources:
limits:
memory: 75Mi
requests:
cpu: 75m
memory: 75Mi
vtadmin:
rbac:
name: example-cluster-config
key: rbac-admin.yaml
cells:
- zone1
apiAddresses:
- http://localhost:8081
replicas: 1
readOnly: true
apiResources:
limits:
memory: 128Mi
requests:
cpu: 100m
memory: 128Mi
webResources:
limits:
memory: 128Mi
requests:
cpu: 100m
memory: 128Mi
keyspaces:
- name: ext_db
turndownPolicy: Immediate
databaseName: ext_db
partitionings:
- equal:
parts: 1
shardTemplate:
databaseInitScriptSecret:
name: example-cluster-config
key: init_db.sql
replication:
enforceSemiSync: false
initializeBackup: false
tabletPools:
- cell: zone1
type: externalmaster
replicas: 1
vttablet:
extraFlags:
db_charset: utf8mb4
mysql_auth_static_reload_interval: 30s
watch_replication_stream: "true"
track_schema_versions: "true"
enable_semi_sync: "false"
disable_active_reparents: "true"
resources:
requests:
cpu: 50m
memory: 64Mi
externalDatastore:
user: external-db-user
host: external-db-host
port: 3306
database: external-db
credentialsSecret:
name: example-cluster-config
key: external_users.json
- name: exampledb
turndownPolicy: Immediate
vitessOrchestrator:
configSecret:
name: example-cluster-config
key: orc_config.json
resources:
requests:
cpu: 25m
memory: 64Mi
partitionings:
- equal:
parts: 1
shardTemplate:
databaseInitScriptSecret:
name: example-cluster-config
key: init_db.sql
replication:
enforceSemiSync: false
tabletPools:
- cell: zone1
type: replica
replicas: 2
vttablet:
extraFlags:
db_charset: utf8mb4
init_db_name_override: exampledb
mysql_auth_static_reload_interval: 30s
disable_active_reparents: "true"
enable_semi_sync: "false"
resources:
requests:
cpu: 50m
memory: 100Mi
mysqld:
resources:
requests:
cpu: 50m
memory: 100Mi
dataVolumeClaimTemplate:
accessModes: ["ReadWriteOnce"]
resources:
requests:
storage: 2Gi
- cell: zone1
type: rdonly
replicas: 1
vttablet:
extraFlags:
db_charset: utf8mb4
init_db_name_override: exampledb
mysql_auth_static_reload_interval: 30s
disable_active_reparents: "true"
enable_semi_sync: "false"
resources:
requests:
cpu: 50m
memory: 100Mi
mysqld:
resources:
requests:
cpu: 50m
memory: 100Mi
dataVolumeClaimTemplate:
accessModes: ["ReadWriteOnce"]
resources:
requests:
storage: 2Gi
updateStrategy:
type: Immediate
---
apiVersion: v1
kind: Secret
metadata:
name: example-cluster-config
namespace: default
type: Opaque
stringData:
external_users.json: |
{
"external-db-user": ["external-db-password"],
}
users.json: |
{
"vtgate": [{
"UserData": "changethisuser",
"Password": "changethispassword"
}]
}
orc_config.json: |
{
"Debug": true,
"Durability": "none",
"MySQLTopologyUser": "change-this-orc_client_user",
"MySQLTopologyPassword": "change-this-orc_client_user_password",
"MySQLReplicaUser": "vt_repl",
"MySQLReplicaPassword": "",
"RecoveryPeriodBlockSeconds": 5
}
rbac.yaml: |
rules:
- resource: "*"
actions:
- "get"
- "create"
- "put"
- "ping"
subjects: ["*"]
clusters: ["*"]
- resource: "Shard"
actions:
- "emergency_failover_shard"
- "planned_failover_shard"
subjects: ["*"]
clusters: ["*"]
rbac-admin.yaml: |
rules:
- resource: "*"
actions: ["*"]
subjects: ["*"]
clusters: ["*"]
init_db.sql: |
# This file is executed immediately after mysql_install_db,
# to initialize a fresh data directory.
###############################################################################
# Equivalent of mysql_secure_installation
###############################################################################
# Changes during the init db should not make it to the binlog.
# They could potentially create errant transactions on replicas.
SET sql_log_bin = 0;
# Remove anonymous users.
DELETE FROM mysql.user WHERE User = '';
# Disable remote root access (only allow UNIX socket).
DELETE FROM mysql.user WHERE User = 'root' AND Host != 'localhost';
# Remove test database.
DROP DATABASE IF EXISTS test;
###############################################################################
# Vitess defaults
###############################################################################
# Vitess-internal database.
CREATE DATABASE IF NOT EXISTS _vt;
# Note that definitions of local_metadata and shard_metadata should be the same
# as in production which is defined in go/vt/mysqlctl/metadata_tables.go.
CREATE TABLE IF NOT EXISTS _vt.local_metadata (
name VARCHAR(255) NOT NULL,
value VARCHAR(255) NOT NULL,
db_name VARBINARY(255) NOT NULL,
PRIMARY KEY (db_name, name)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS _vt.shard_metadata (
name VARCHAR(255) NOT NULL,
value MEDIUMBLOB NOT NULL,
db_name VARBINARY(255) NOT NULL,
PRIMARY KEY (db_name, name)
) ENGINE=InnoDB;
# Admin user with all privileges.
CREATE USER 'vt_dba'@'localhost';
GRANT ALL ON *.* TO 'vt_dba'@'localhost';
GRANT GRANT OPTION ON *.* TO 'vt_dba'@'localhost';
# User for app traffic, with global read-write access.
CREATE USER 'vt_app'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, FILE,
REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES,
LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW,
SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER
ON *.* TO 'vt_app'@'localhost';
# User for app debug traffic, with global read access.
CREATE USER 'vt_appdebug'@'localhost';
GRANT SELECT, SHOW DATABASES, PROCESS ON *.* TO 'vt_appdebug'@'localhost';
# User for administrative operations that need to be executed as non-SUPER.
# Same permissions as vt_app here.
CREATE USER 'vt_allprivs'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, FILE,
REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES,
LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW,
SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER
ON *.* TO 'vt_allprivs'@'localhost';
# User for slave replication connections.
# TODO: Should we set a password on this since it allows remote connections?
CREATE USER 'vt_repl'@'%';
GRANT REPLICATION SLAVE ON *.* TO 'vt_repl'@'%';
# User for Vitess filtered replication (binlog player).
# Same permissions as vt_app.
CREATE USER 'vt_filtered'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, FILE,
REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES,
LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW,
SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER
ON *.* TO 'vt_filtered'@'localhost';
# User for Orchestrator (https://github.com/openark/orchestrator).
# TODO: Reenable when the password is randomly generated.
CREATE USER 'change-this-orc_client_user'@'%' IDENTIFIED BY 'change-this-orc_client_user_password';
GRANT SUPER, PROCESS, REPLICATION SLAVE, RELOAD
ON *.* TO 'orc_client_user'@'%';
GRANT SELECT
ON _vt.* TO 'orc_client_user'@'%';
#SET sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION;
#SET sql_mode=(SELECT REPLACE(@@sql_mode,'NO_ZERO_DATE',''));
FLUSH PRIVILEGES;
RESET SLAVE ALL;
RESET MASTER;
---
apiVersion: v1
kind: Service
metadata:
name: vtgate
namespace: default
spec:
type: NodePort
ports:
- name: mysql
port: 3306
protocol: TCP
targetPort: mysql
nodePort: 30036
selector:
planetscale.com/component: vtgate
---
apiVersion: v1
kind: ConfigMap
metadata:
name: vitess-scripts
namespace: default
data:
externaldb_vreplication.sh: |
#!/bin/bash
set -e
VTCTLD_SERVER=${VTCTLD_SERVER:-'vtctld:15999'}
EXTERNAL_DB_PREFIX=${EXTERNAL_DB_PREFIX:-'ext_'}
CREATE_STREAM=${CREATE_STREAM:-'yes'}
# Wait until source and destination masters are available
until (/vt/bin/vtctlclient --server $VTCTLD_SERVER ListAllTablets | grep $EXTERNAL_DB_PREFIX | grep "master\|primary" ); do
echo 'waiting for external master..';
sleep 1;
done
until (/vt/bin/vtctlclient --server $VTCTLD_SERVER ListAllTablets | grep -v $EXTERNAL_DB_PREFIX | grep "master\|primary" ); do
echo 'waiting for managed master..';
sleep 1;
done
# Get source and destination tablet and shard information
TABLET_INFO=$(/vt/bin/vtctlclient --server $VTCTLD_SERVER ListAllTablets)
source_alias=$(echo "$TABLET_INFO "| grep $EXTERNAL_DB_PREFIX | grep "master\|primary" | awk '{ print $1 }')
dest_alias=$(echo "$TABLET_INFO "| grep -v $EXTERNAL_DB_PREFIX | grep "master\|primary" | awk '{ print $1 }')
source_keyspace=$(echo "$TABLET_INFO "| grep $EXTERNAL_DB_PREFIX | grep "master\|primary" | awk '{ print $2 }')
dest_keyspace=$(echo "$TABLET_INFO "| grep -v $EXTERNAL_DB_PREFIX | grep "master\|primary" | awk '{ print $2 }')
source_shard=$(echo "$TABLET_INFO "| grep $EXTERNAL_DB_PREFIX | grep "master\|primary" | awk '{ print $3 }')
dest_shard=$(echo "$TABLET_INFO "| grep -v $EXTERNAL_DB_PREFIX | grep "master\|primary" | awk '{ print $3 }')
source_tablet=$(echo "$TABLET_INFO "| grep $EXTERNAL_DB_PREFIX | grep "master\|primary" | awk '{ print $2 "/" $3}')
dest_tablet=$(echo "$TABLET_INFO "| grep -v $EXTERNAL_DB_PREFIX | grep "master\|primary" | awk '{ print $2 "/" $3}')
# Disable foreign_key checks on destination
/vt/bin/vtctlclient --server $VTCTLD_SERVER ExecuteFetchAsDba $dest_alias 'SET GLOBAL FOREIGN_KEY_CHECKS=0;'
# Get source_sql mode
source_sql_mode=$(/vt/bin/vtctlclient --server $VTCTLD_SERVER ExecuteFetchAsDba $source_alias 'SELECT @@GLOBAL.sql_mode' | awk 'NR==4 {print $2}')
# Apply source sql_mode to destination
# The intention is to avoid replication errors
/vt/bin/vtctlclient --server $VTCTLD_SERVER ExecuteFetchAsDba $dest_alias "SET GLOBAL sql_mode='$source_sql_mode';"
# Verify sql_mode matches
[ $source_sql_mode == $(/vt/bin/vtctlclient --server $VTCTLD_SERVER ExecuteFetchAsDba $dest_alias 'SELECT @@GLOBAL.sql_mode' | awk 'NR==4 {print $2}') ] && \
echo "Source and Destination sql_mode Match." || echo "sql_mode MisMatch"
until /vt/bin/vtctlclient --server $VTCTLD_SERVER GetSchema $dest_alias; do
echo "Waiting for destination schema to be ready..";
sleep 3;
done
if [ "$REPLICATION_TYPE" == "debug" ]; then
# Sleep to allow exec into pod
sleep 900
exit 0
fi
if [ "$REPLICATION_TYPE" == "workflow" ]; then
# Create Workflow
/vt/bin/vtctlclient --server $VTCTLD_SERVER MoveTables -- --keep_data Complete $dest_keyspace.ext_vrepl || \
/vt/bin/vtctlclient --server $VTCTLD_SERVER MoveTables -- Cancel $dest_keyspace.ext_vrepl || true
/vt/bin/vtctlclient --server $VTCTLD_SERVER MoveTables -- --source=$source_keyspace --all Create $dest_keyspace.ext_vrepl
/vt/bin/vtctlclient --server $VTCTLD_SERVER MoveTables Show $dest_keyspace.ext_vrepl
/vt/bin/vtctlclient --server $VTCTLD_SERVER MoveTables Progress $dest_keyspace.ext_vrepl
until /vt/bin/vtctlclient --server $VTCTLD_SERVER MoveTables -- --reverse_replication=false --tablet_types=rdonly,replica SwitchTraffic $dest_keyspace.ext_vrepl; do
echo "Waiting for acceptable replication lag..";
sleep 3;
done
/vt/bin/vtctlclient --server $VTCTLD_SERVER GetRoutingRules $dest_keyspace
fi
if [ "$REPLICATION_TYPE" == "vreplication" ]; then
# Copy schema from source to destination shard
/vt/bin/vtctlclient --server $VTCTLD_SERVER CopySchemaShard $source_tablet $dest_tablet || true
# Verify schema
/vt/bin/vtctlclient --server $VTCTLD_SERVER GetSchema $dest_alias
# Start vreplication
if [ "$CREATE_STREAM" == "yes" ]; then
/vt/bin/vtctlclient --server $VTCTLD_SERVER VReplicationExec $dest_alias 'insert into _vt.vreplication (db_name, source, pos, max_tps, max_replication_lag, tablet_types, time_updated, transaction_timestamp, state) values('"'"''"$dest_keyspace"''"'"', '"'"'keyspace:\"'"$source_keyspace"'\" shard:\"'"$source_shard"'\" filter:<rules:<match:\"/.*\" > > on_ddl:EXEC_IGNORE '"'"', '"'"''"'"', 9999, 9999, '"'"'master'"'"', 0, 0, '"'"'Running'"'"')'
else
echo "Skip Creating VReplication Stream"
fi
fi
# Check vreplication status
/vt/bin/vtctlclient --server $VTCTLD_SERVER VReplicationExec $dest_alias 'select * from _vt.vreplication'
echo "############"
echo "Add this in your yaml under mysqld spec to persist sql_mode after restarts"
echo "configOverrides: \"sql_mode = $source_sql_mode\""
---
apiVersion: batch/v1
kind: Job
metadata:
name: externaldb-vreplication
spec:
backoffLimit: 5
activeDeadlineSeconds: 1200
template:
spec:
containers:
- name: replication
image: vitess/lite:v15.0.2
command: ['bash', '-c', '/scripts/externaldb_vreplication.sh']
env:
- name: CREATE_STREAM
value: "yes"
- name: REPLICATION_TYPE
value: "workflow"
volumeMounts:
- mountPath: /scripts/externaldb_vreplication.sh
name: vitess-scripts
subPath: externaldb_vreplication.sh
restartPolicy: OnFailure
volumes:
- configMap:
defaultMode: 0755
name: vitess-scripts
name: vitess-scripts
---
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment