Getting Started with Vitess by Kubernetes Operator

There are fewer lock contentions to worry about, replication is a lot happier, production impact of outages become smaller, backups and restores run faster, and a lot more secondary advantages can be realized

Getting Started

Docker

Follow this tutorial

git clone git@github.com:vitessio/vitess.git
cd vitess
make docker_local
./docker/local/run.sh

Kubernetes Operator

Prerequisite

Follow this tutorial.

minikube start --kubernetes-version=v1.19.16 --cpus=4 --memory=4000 --disk-size=32g

Install vitess client.

version=6.0.20-20200818
file=vitess-${version}-90741b8.tar.gz
wget https://github.com/vitessio/vitess/releases/download/v${version}/${file}
tar -xzf ${file}
cd ${file/.tar.gz/}
sudo mkdir -p /usr/local/vitess
sudo cp -r * /usr/local/vitess/
export PATH=$PATH:/usr/local/vitess/bin

Install Operator

git clone git@github.com:vitessio/vitess.git
cd vitess/examples/operator
kubectl apply -f operator.yaml -n vitess

Start the first cluster

kubectl apply -f 101_initial_cluster.yaml -n vitess

Connect to vitess by mysql client

> minikube kubectl -- port-forward svc/example-vtgate-ae7df4b6 15306:3306
Forwarding from 127.0.0.1:15306 -> 3306
Forwarding from [::1]:15306 -> 3306
Handling connection for 15306

Open a different session

> mysql -h 127.0.0.1 -P 15306 -u user
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.7.9-vitess-14.0.0-SNAPSHOT Version: 14.0.0-SNAPSHOT (Git revision 270cf96cd2 branch 'main') built on Sat Mar 26 10:24:01 UTC 2022 by vitess@buildkitsandbox using go1.18 linux/amd64

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| commerce           |
| information_schema |
| mysql              |
| sys                |
| performance_schema |
+--------------------+
5 rows in set (0.00 sec)

Create a schema

minikube kubectl -- port-forward svc/example-vtctld-625ee430 15999:15999

set sql (cat create_commerce_schema.sql)
> vtctlclient -server=localhost:15999 ApplySchema -sql="$sql" commerce
E0326 19:20:53.480875  165620 main.go:64] E0327 02:20:53.480177 vtctl.go:3352] schema change failed, ExecuteResult: {
  "FailedShards": null,
  "SuccessShards": null,
  "CurSQLIndex": 0,
  "Sqls": [
    "create table product(   sku varbinary(128),   description varbinary(128),   price bigint,   primary key(sku) ) ENGINE=InnoDB",
    "create table customer(   customer_id bigint not null auto_increment,   email varbinary(128),   primary key(customer_id) ) ENGINE=InnoDB",
    "create table corder(   order_id bigint not null auto_increment,   customer_id bigint,   sku varbinary(128),   price bigint,   primary key(order_id) ) ENGINE=InnoDB"
  ],
  "UUIDs": null,
  "ExecutorErr": "rpc error: code = Unknown desc = TabletManager.PreflightSchema on zone1-2469782763 error: /usr/bin/mysql: exit status 1, output: ERROR 2013 (HY000) at line 3: Lost connection to MySQL server during query\n: /usr/bin/mysql: exit status 1, output: ERROR 2013 (HY000) at line 3: Lost connection to MySQL server during query\n",
  "TotalTimeSpent": 232941953
}

For some reasons, after I ran vschema at first, and then after waiting for a few minutes, then I was able to run the query.

set vschema (cat vschema_commerce_initial.json)
vtctlclient -server=localhost:15999 ApplyVSchema -vschema="$vschema" commerce
vtctlclient -server=localhost:15999 ApplySchema -sql="$sql" commerce

> mysql -h 127.0.0.1 -P 15306 -u user
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.7.9-vitess-14.0.0-SNAPSHOT Version: 14.0.0-SNAPSHOT (Git revision 270cf96cd2 branch 'main') built on Sat Mar 26 10:24:01 UTC 2022 by vitess@buildkitsandbox using go1.18 linux/amd64

mysql> show tables;
+--------------------+
| Tables_in_commerce |
+--------------------+
| corder             |
| customer           |
| product            |
+--------------------+
3 rows in set (0.00 sec)

Move a Table

Follow this table.

Insert a table

> mysql -h 127.0.0.1 -P 15306 -u user < ../common/insert_commerce_data.sql
> mysql -h 127.0.0.1 -P 15306 -u user < ../common/select_commerce_data.sql
Using commerce
Customer
customer_id     email
1       alice@domain.com
2       bob@domain.com
3       charlie@domain.com
4       dan@domain.com
5       eve@domain.com
Product
sku     description     price
SKU-1001        Monitor 100
SKU-1002        Keyboard        30
COrder
order_id        customer_id     sku     price
1       1       SKU-1001        100
2       2       SKU-1002        30
3       3       SKU-1002        30
4       4       SKU-1002        30
5       5       SKU-1002        30

> mysql -h 127.0.0.1 -P 15306 -u user --table --execute="show vitess_tablets"
+-------+----------+-------+------------+---------+------------------+-------------+----------------------+
| Cell  | Keyspace | Shard | TabletType | State   | Alias            | Hostname    | PrimaryTermStartTime |
+-------+----------+-------+------------+---------+------------------+-------------+----------------------+
| zone1 | commerce | -     | PRIMARY    | SERVING | zone1-2469782763 | 172.18.0.9  | 2022-03-27T01:45:19Z |
| zone1 | commerce | -     | REPLICA    | SERVING | zone1-2548885007 | 172.18.0.10 |                      |
+-------+----------+-------+------------+---------+------------------+-------------+----------------------+

> minikube kubectl -- apply -f 201_customer_tablets.yaml
vitesscluster.planetscale.com/example configured

Portforward

> vim pf.sh
# Edit alias kubectl="minikube kubectl --"

> ./pf.sh

> mysql -h 127.0.0.1 -P 15306 -u user --table --execute="show vitess_tablets"
+-------+----------+-------+------------+---------+------------------+-------------+----------------------+
| Cell  | Keyspace | Shard | TabletType | State   | Alias            | Hostname    | PrimaryTermStartTime |
+-------+----------+-------+------------+---------+------------------+-------------+----------------------+
| zone1 | commerce | -     | PRIMARY    | SERVING | zone1-2469782763 | 172.18.0.9  | 2022-03-27T01:45:19Z |
| zone1 | commerce | -     | REPLICA    | SERVING | zone1-2548885007 | 172.18.0.10 |                      |
| zone1 | customer | -     | PRIMARY    | SERVING | zone1-1250593518 | 172.18.0.11 | 2022-03-27T02:44:43Z |
| zone1 | customer | -     | REPLICA    | SERVING | zone1-3778123133 | 172.18.0.12 |                      |
+-------+----------+-------+------------+---------+------------------+-------------+----------------------+

Move a table into different keyspace

> vtctlclient -server=localhost:15999 MoveTables -source commerce -tables 'customer,corder' Create customer.commerce2customer
E0326 19:58:50.201219  262097 main.go:67] remote error: rpc error: code = Unknown desc = TabletManager.GetSchema on zone1-2469782763 error: EOF (errno 2013) (sqlstate HY000) during query: SELECT COLUMN_NAME as column_name
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_SCHEMA = 'vt_commerce' AND TABLE_NAME = 'corder'
                ORDER BY ORDINAL_POSITION
EOF (errno 2013) (sqlstate HY000) during query: SELECT COLUMN_NAME as column_name
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_SCHEMA = 'vt_commerce' AND TABLE_NAME = 'customer'
                ORDER BY ORDINAL_POSITION
EOF (errno 2013) (sqlstate HY000) during query: SELECT COLUMN_NAME as column_name
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_SCHEMA = 'vt_commerce' AND TABLE_NAME = 'product'
                ORDER BY ORDINAL_POSITION
EOF (errno 2013) (sqlstate HY000) during query: SELECT table_name as table_name, ordinal_position as ordinal_position, COLUMN_NAME as column_name
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_SCHEMA = 'vt_commerce'
                AND TABLE_NAME IN ('corder', 'customer', 'product')
                AND COLUMN_KEY = 'PRI'
                ORDER BY table_name, ordinal_position: EOF (errno 2013) (sqlstate HY000) during query: SELECT COLUMN_NAME as column_name
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_SCHEMA = 'vt_commerce' AND TABLE_NAME = 'corder'
                ORDER BY ORDINAL_POSITION
EOF (errno 2013) (sqlstate HY000) during query: SELECT COLUMN_NAME as column_name
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_SCHEMA = 'vt_commerce' AND TABLE_NAME = 'customer'
                ORDER BY ORDINAL_POSITION
EOF (errno 2013) (sqlstate HY000) during query: SELECT COLUMN_NAME as column_name
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_SCHEMA = 'vt_commerce' AND TABLE_NAME = 'product'
                ORDER BY ORDINAL_POSITION
EOF (errno 2013) (sqlstate HY000) during query: SELECT table_name as table_name, ordinal_position as ordinal_position, COLUMN_NAME as column_name
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_SCHEMA = 'vt_commerce'
                AND TABLE_NAME IN ('corder', 'customer', 'product')
                AND COLUMN_KEY = 'PRI'
                ORDER BY table_name, ordinal_position

Try to monitor the progress

> vtctlclient -server=localhost:15999 VReplicationExec zone1-1250593518 "select * from _vt.copy_state"
> vtctlclient -server=localhost:15999 VReplicationExec zone1-2469782763 "select * from _vt.copy_state"
> vtctlclient -server=localhost:15999 VReplicationExec zone1-2548885007 "select * from _vt.copy_state"
E0326 20:00:50.714505  268220 main.go:67] remote error: rpc error: code = Unknown desc = TabletManager.VReplicationExec on zone1-2548885007 error: vreplication engine is closed: vreplication engine is closed

Retry to run move table operations

> vtctlclient -server=localhost:15999 MoveTables -source commerce -tables 'customer,corder' Create customer.commerce2customer
E0326 20:02:11.167288  271560 main.go:67] remote error: rpc error: code = Unknown desc = TabletManager.VReplicationExec on zone1-1250593518 error: error in connecting to mysql db with connection <nil>, err net.Dial(/vt/socket/mysql.sock) to local server failed: dial unix /vt/socket/mysql.sock: connect: connection refused (errno 2002) (sqlstate HY000): error in connecting to mysql db with connection <nil>, err net.Dial(/vt/socket/mysql.sock) to local server failed: dial unix /vt/socket/mysql.sock: connect: connection refused (errno 2002) (sqlstate HY000)
> vtctlclient -server=localhost:15999 MoveTables -source commerce -tables 'customer,corder' Create customer.commerce2customer
Waiting for workflow to start:
0% ... 0% ... 0% ... 0% ... 0% ... 0% ... 0% ... 0% ... 0% ... 0% ... 0% ... 0% ... 0% ... 0% ... 0% ...
Workflow started successfully with 1 stream(s)

Following vreplication streams are running for workflow customer.commerce2customer:

id=1 on -/zone1-1250593518: Status: Copying. VStream Lag: 0s.

Validate the result

> vtctlclient -server=localhost:15999 VDiff customer.commerce2customer
Summary for table corder:
        ProcessedRows: 5
        MatchingRows: 5
        MismatchedRows: 0
        ExtraRowsSource: 0
        ExtraRowsTarget: 0
Summary for table customer:
        ProcessedRows: 5
        MatchingRows: 5
        MismatchedRows: 0
        ExtraRowsSource: 0
        ExtraRowsTarget: 0

Check routing routes

> vtctlclient -server=localhost:15999 GetRoutingRules commerce

{
  "rules": [
    {
      "fromTable": "customer.customer",
      "toTables": [
        "commerce.customer"
      ]
    },
    {
      "fromTable": "customer@replica",
      "toTables": [
        "commerce.customer"
      ]
    },
    {
      "fromTable": "commerce.customer@replica",
      "toTables": [
        "commerce.customer"
      ]
    },
    {
      "fromTable": "commerce.customer@rdonly",
      "toTables": [
        "commerce.customer"
      ]
    },
    {
      "fromTable": "corder@replica",
      "toTables": [
        "commerce.corder"
      ]
    },
    {
      "fromTable": "customer.customer@rdonly",
      "toTables": [
        "commerce.customer"
      ]
    },
    {
      "fromTable": "corder@rdonly",
      "toTables": [
        "commerce.corder"
      ]
    },
    {
      "fromTable": "customer",
      "toTables": [
        "commerce.customer"
      ]
    },
    {
      "fromTable": "customer.customer@replica",
      "toTables": [
        "commerce.customer"
      ]
    },
    {
      "fromTable": "commerce.corder@replica",
      "toTables": [
        "commerce.corder"
      ]
    },
    {
      "fromTable": "customer.corder@replica",
      "toTables": [
        "commerce.corder"
      ]
    },
    {
      "fromTable": "customer@rdonly",
      "toTables": [
        "commerce.customer"
      ]
    },
    {
      "fromTable": "commerce.corder@rdonly",
      "toTables": [
        "commerce.corder"
      ]
    },
    {
      "fromTable": "customer.corder",
      "toTables": [
        "commerce.corder"
      ]
    },
    {
      "fromTable": "customer.corder@rdonly",
      "toTables": [
        "commerce.corder"
      ]
    },
    {
      "fromTable": "corder",
      "toTables": [
        "commerce.corder"
      ]
    }
  ]
}

Switch non-primary traffic

> vtctlclient -server=localhost:15999 MoveTables -tablet_types=rdonly,replica SwitchTraffic customer.commerce2customer
SwitchTraffic was successful for workflow customer.commerce2customer
Start State: Reads Not Switched. Writes Not Switched
Current State: All Reads Switched. Writes Not Switched

Confirm the new routes

> vtctlclient -server=localhost:15999 GetRoutingRules commerce
{
  "rules": [
    {
      "fromTable": "customer@replica",
      "toTables": [
        "customer.customer"
      ]
    },
    {
      "fromTable": "customer",
      "toTables": [
        "commerce.customer"
      ]
    },
    {
      "fromTable": "customer.corder",
      "toTables": [
        "commerce.corder"
      ]
    },
    {
      "fromTable": "commerce.customer@replica",
      "toTables": [
        "customer.customer"
      ]
    },
    {
      "fromTable": "commerce.corder@replica",
      "toTables": [
        "customer.corder"
      ]
    },
    {
      "fromTable": "customer.corder@replica",
      "toTables": [
        "customer.corder"
      ]
    },
    {
      "fromTable": "customer@rdonly",
      "toTables": [
        "customer.customer"
      ]
    },
    {
      "fromTable": "commerce.corder@rdonly",
      "toTables": [
        "customer.corder"
      ]
    },
    {
      "fromTable": "corder",
      "toTables": [
        "commerce.corder"
      ]
    },
    {
      "fromTable": "commerce.customer@rdonly",
      "toTables": [
        "customer.customer"
      ]
    },
    {
      "fromTable": "corder@replica",
      "toTables": [
        "customer.corder"
      ]
    },
    {
      "fromTable": "customer.customer@rdonly",
      "toTables": [
        "customer.customer"
      ]
    },
    {
      "fromTable": "corder@rdonly",
      "toTables": [
        "customer.corder"
      ]
    },
    {
      "fromTable": "customer.customer",
      "toTables": [
        "commerce.customer"
      ]
    },
    {
      "fromTable": "customer.customer@replica",
      "toTables": [
        "customer.customer"
      ]
    },
    {
      "fromTable": "customer.corder@rdonly",
      "toTables": [
        "customer.corder"
      ]
    }
  ]
}

This moves commerce of fromTable to customer of toTables.

Switch traffic to primary

> vtctlclient -server=localhost:15999 MoveTables -tablet_types=primary SwitchTraffic customer.commerce2customer

SwitchTraffic was successful for workflow customer.commerce2customer
Start State: All Reads Switched. Writes Not Switched
Current State: All Reads Switched. Writes Switched

vtctlclient -server=localhost:15999 GetRoutingRules commerce { “rules”: [ { “fromTable”: “customer@replica”, “toTables”: [ “customer.customer” ] }, { “fromTable”: “customer.corder@replica”, “toTables”: [ “customer.corder” ] }, { “fromTable”: “commerce.corder@rdonly”, “toTables”: [ “customer.corder” ] }, { “fromTable”: “commerce.corder”, “toTables”: [ “customer.corder” ] }, { “fromTable”: “commerce.customer@replica”, “toTables”: [ “customer.customer” ] }, { “fromTable”: “customer@rdonly”, “toTables”: [ “customer.customer” ] }, { “fromTable”: “corder”, “toTables”: [ “customer.corder” ] }, { “fromTable”: “customer.customer@replica”, “toTables”: [ “customer.customer” ] }, { “fromTable”: “customer.corder@rdonly”, “toTables”: [ “customer.corder” ] }, { “fromTable”: “commerce.customer”, “toTables”: [ “customer.customer” ] }, { “fromTable”: “corder@replica”, “toTables”: [ “customer.corder” ] }, { “fromTable”: “customer.customer@rdonly”, “toTables”: [ “customer.customer” ] }, { “fromTable”: “customer”, “toTables”: [ “customer.customer” ] }, { “fromTable”: “commerce.corder@replica”, “toTables”: [ “customer.corder” ] }, { “fromTable”: “commerce.customer@rdonly”, “toTables”: [ “customer.customer” ] }, { “fromTable”: “corder@rdonly”, “toTables”: [ “customer.corder” ] } ] } ```

Remove the data from the old keyspace.

> vtctlclient -server=localhost:15999 MoveTables Complete customer.commerce2customer

Complete was successful for workflow customer.commerce2customer
Start State: All Reads Switched. Writes Switched
Current State: Workflow Not Found

> mysql -h 127.0.0.1 -P 15306 -u user < ../common/select_commerce_data.sql
Using commerce
Customer
ERROR 1146 (42S02) at line 4: target: commerce.-.primary: vttablet: rpc error: code = NotFound desc = Table 'vt_commerce.customer' doesn't exist (errno 1146) (sqlstate 42S02) (CallerID: user): Sql: "select * from customer", BindVars: {}

2024

Back to Top ↑

2023

Follow Kubernetes the Hard way

4 minute read

This article was written by just following Kelsey Hightower’s Kubernetes Hardway document to understand Kubernetes internal architecture.

Gcp Billing Analyze

less than 1 minute read

There are a few documents to manage billing data in BigQuery Attribution of committed use discount fees and credits How to export to BigQuery Structur...

Prometheus Metrics Overview on Grafana

1 minute read

In this post, some variables defined in Grafana are used for Prometheus metrics, including $__rate_interval: This article describes the benefit of this va...

Use Google Secret Manager in a GKE cluster

3 minute read

There are an awesome article about the options to use the Google Secret Manager and their pros and cons. In this article, use Secrets Store CSI Driver by fol...

Working around MySQL lock metadata

2 minute read

There are multiple documents about innodb locks on MySQL 5.7: InnoDB locking Locks Set by Different SQL Statements in InnoDB Using InnoDB Transaction ...

Upgrade Windows 10 to Windows 11

3 minute read

I used to use Windows 11, but for some reasons, the OS stopped working and I needed to clean-install it from Windows 10 from windows recovery environment.

Back to Top ↑

2022

MySQL backup and restore

1 minute read

In this article, explain how to backup MySQL database using Percona Xtrabackup. There are two binaries, innobackupex and xtrabackup. innobackupex is a wrappe...

tmux

1 minute read

Basic configuration

Back to Top ↑

2021

MySQL Replication

1 minute read

This configuration is for the version 5.7 and it’s minimum configuration in the official document.

jq cheetsheet

less than 1 minute read

jq is used to parse JSON result, format and output on the cli.

Compare static site generator

less than 1 minute read

There are many web sites to compare static site generator, but they miss some explanations that require to me. For some people, these features are important ...

Back to Top ↑

2020

Getting Started with Kubernetes Deployment

less than 1 minute read

The deployment is many use cases and in this page, they’re not described. For the details for those use cases or the concept of deployment, see official page.

Overview about MySQL Lock

2 minute read

This document is written for MySQL 5.7, so these contents may be not correct for other versions.

MySQL Performance

2 minute read

This document is written for MySQL 5.7, so these contents may be not correct for other versions. In this page, performance_schema is mainly discussed.

Git hooks

less than 1 minute read

Configurations

gitHub pages

3 minute read

Getting Started See Official tutorial for detail steps.

Gitconfig

1 minute read

Configuration The detail for gitconfig is written in official page.

git cli

less than 1 minute read

Written in March 2020.

MySQL Tuner

less than 1 minute read

MySQL Tuner tool This is a tool to review a configuration for MySQL server.

kubectl cheetsheet

less than 1 minute read

Collect recent error logs If the logs are outputted by zap, error messages are aggregated by checking level = error. This log does not work very well if the ...

Introduction to GCP Cloud endpoints

less than 1 minute read

The Cloud endpoint is actually the NGINX proxy which offers the following features on GCP. Authentication and validation Logging and monitoring in GCP

HTTP/2 for Go

1 minute read

http package in golang supports HTTP/2 protocols. It’s automatically configured.

Back to Top ↑

2019

Terraform overview

1 minute read

Basic concepts There are some basic components for terraform.

Protocol Buffers for Go with Gadgets

less than 1 minute read

gogo/protobuf is the library to store some extensions from golang/protobuf in this repository. There are some useful packages that golang/protobuf does not p...

Introduction to GCP Cloud CDN

less than 1 minute read

Target upstream services Cloud CDN can have only GCP load balancer as the upstream services. And GCP load balancer can configure one of followings for backen...

Getting Started with Google closure library

less than 1 minute read

Some JavaScript library depends on Google Closure. If you need to understand the behavior of such a library, you have to know closure. The official document ...

Back to Top ↑