Setup and Deploy Vitess on Kubernetes (Minikube) for MySQL – Part III of III

Alkin Tezuysal · January 16, 2020 at 12:25 am

By Alkin Tezuysal Cloud, Insight for DBAs, MySQL cloud, DBA, MySQL 1 Comment

In this blog post, we will continue to explore Vitess and test an example database provided in its repository. This is Part III of the previously discussed installation of Vitess on minikube environment, so please make sure to follow those steps to bring the cluster up to the following level.

$ kubectl get pods,jobs
NAME                                                                READY     STATUS    RESTARTS   AGE
po/etcd-global-kbbcqlgvp9                                           1/1       Running   0          43m
po/etcd-zone1-lpc5zmdxxn                                            1/1       Running   0          43m
po/my-release-etcd-operator-etcd-backup-operator-6684dd6d8c-pr4n4   1/1       Running   0          1h
po/my-release-etcd-operator-etcd-operator-86d94989d6-w9lpx          1/1       Running   0          1h
po/my-release-etcd-operator-etcd-restore-operator-c655d757c-9nsnz   1/1       Running   0          1h
po/vtctld-757df48d4-c2gp9                                           1/1       Running   3          43m
po/vtgate-zone1-5cb4fcddcb-k2zsn                                    1/1       Running   3          43m
po/zone1-commerce-0-rdonly-0                                        6/6       Running   0          43m
po/zone1-commerce-0-replica-0                                       6/6       Running   0          43m
po/zone1-commerce-0-replica-1                                       6/6       Running   0          43m

NAME                                      DESIRED   SUCCESSFUL   AGE
jobs/commerce-apply-schema-initial        1         1            43m
jobs/commerce-apply-vschema-initial       1         1            43m
jobs/zone1-commerce-0-init-shard-master   1         1            43m

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

$ kubectl get pods,jobs

NAME READY STATUS RESTARTS AGE

po/etcd-global-kbbcqlgvp9 1/1 Running 0 43m

po/etcd-zone1-lpc5zmdxxn 1/1 Running 0 43m

po/my-release-etcd-operator-etcd-backup-operator-6684dd6d8c-pr4n4 1/1 Running 0 1h

po/my-release-etcd-operator-etcd-operator-86d94989d6-w9lpx 1/1 Running 0 1h

po/my-release-etcd-operator-etcd-restore-operator-c655d757c-9nsnz 1/1 Running 0 1h

po/vtctld-757df48d4-c2gp9 1/1 Running 3 43m

po/vtgate-zone1-5cb4fcddcb-k2zsn 1/1 Running 3 43m

po/zone1-commerce-0-rdonly-0 6/6 Running 0 43m

po/zone1-commerce-0-replica-0 6/6 Running 0 43m

po/zone1-commerce-0-replica-1 6/6 Running 0 43m

NAME DESIRED SUCCESSFUL AGE

jobs/commerce-apply-schema-initial 1 1 43m

jobs/commerce-apply-vschema-initial 1 1 43m

jobs/zone1-commerce-0-init-shard-master 1 1 43m

Copy local files to percona-client pod after running the following:

$ kubectl run -i --rm --tty percona-client --image=percona:5.7 --restart=Never -- bash -il
$ kubectl cp ~/go/src/vitess.io/vitess/examples/helm percona-client:/tmp/helm
$ kubectl cp ~/go/src/vitess.io/vitess/examples/common percona-client:/tmp/helm

bash-4.2$ mysql -h vtgate-zone1 -P 3306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.10-Vitess Percona Server (GPL), Release 23, Revision 500fcf5
Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.7.26-29, for Linux (x86_64) using  6.2
Connection id:		3
Current database:	commerce
Current user:		vt_app@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.5.10-Vitess Percona Server (GPL), Release 23, Revision 500fcf5
Protocol version:	10
Connection:		vtgate-zone1 via TCP/IP
Server characterset:	utf8
Db     characterset:	utf8
Client characterset:	utf8
Conn.  characterset:	utf8
TCP port:		3306
--------------

mysql>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

$ kubectl run -i --rm --tty percona-client --image=percona:5.7 --restart=Never -- bash -il

$ kubectl cp ~/go/src/vitess.io/vitess/examples/helm percona-client:/tmp/helm

$ kubectl cp ~/go/src/vitess.io/vitess/examples/common percona-client:/tmp/helm

bash-4.2$ mysql -h vtgate-zone1 -P 3306

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.5.10-Vitess Percona Server (GPL), Release 23, Revision 500fcf5

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> \s

--------------

mysql Ver 14.14 Distrib 5.7.26-29, for Linux (x86_64) using 6.2

Connection id: 3

Current database: commerce

Current user: vt_app@localhost

SSL: Not in use

Current pager: stdout

Using outfile: ''

Using delimiter: ;

Server version: 5.5.10-Vitess Percona Server (GPL), Release 23, Revision 500fcf5

Protocol version: 10

Connection: vtgate-zone1 via TCP/IP

Server characterset: utf8

Db characterset: utf8

Client characterset: utf8

Conn. characterset: utf8

TCP port: 3306

--------------

mysql>

Creating a Keyspace

The sample helm chart provides single unsharded keyspace with a single shard named 0:

mysql> show databases;
+-----------+
| Databases |
+-----------+
| commerce  |
+-----------+
1 row in set (0.00 sec)

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

mysql> show databases;

+-----------+

| Databases |

+-----------+

| commerce |

+-----------+

1 row in set (0.00 sec)

mysql> show tables;

+--------------------+

| Tables_in_commerce |

+--------------------+

| corder |

| customer |

| product |

+--------------------+

3 rows in set (0.01 sec)

The sample schema is simple enough to have only fields required to demonstrate sharding scheme.

mysql> show create table corder\G
*************************** 1. row ***************************
       Table: corder
Create Table: CREATE TABLE `corder` (
  `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `customer_id` bigint(20) DEFAULT NULL,
  `sku` varbinary(128) DEFAULT NULL,
  `price` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show create table customer\G
*************************** 1. row ***************************
       Table: customer
Create Table: CREATE TABLE `customer` (
  `customer_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `email` varbinary(128) DEFAULT NULL,
  PRIMARY KEY (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

mysql> show create table product\G
*************************** 1. row ***************************
       Table: product
Create Table: CREATE TABLE `product` (
  `sku` varbinary(128) NOT NULL,
  `description` varbinary(128) DEFAULT NULL,
  `price` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`sku`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

mysql> show create table corder\G

*************************** 1. row ***************************

Table: corder

Create Table: CREATE TABLE `corder` (

`order_id` bigint(20) NOT NULL AUTO_INCREMENT,

`customer_id` bigint(20) DEFAULT NULL,

`sku` varbinary(128) DEFAULT NULL,

`price` bigint(20) DEFAULT NULL,

PRIMARY KEY (`order_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

mysql> show create table customer\G

*************************** 1. row ***************************

Table: customer

Create Table: CREATE TABLE `customer` (

`customer_id` bigint(20) NOT NULL AUTO_INCREMENT,

`email` varbinary(128) DEFAULT NULL,

PRIMARY KEY (`customer_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.01 sec)

mysql> show create table product\G

*************************** 1. row ***************************

Table: product

Create Table: CREATE TABLE `product` (

`sku` varbinary(128) NOT NULL,

`description` varbinary(128) DEFAULT NULL,

`price` bigint(20) DEFAULT NULL,

PRIMARY KEY (`sku`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

At this point, Vitess is a single keyspace and VSchema is required to describe keyspaces.

 101_initial_cluster.yaml
vschema:
            initial: |-
              {
                "tables": {
                  "product": {},
                  "customer": {},
                  "corder": {}
                }
              }

1

2

3

4

5

6

7

8

9

10

101_initial_cluster.yaml

vschema:

initial: |-

{

"tables": {

"product": {},

"customer": {},

"corder": {}

}

Vertical Split

We will start loading some sample data to our keyspace. But before that, we need the sample SQL script.

$ cd /tmp/helm/common
$ mysql -h vtgate-zone1 -P 3306 < insert_commerce_data.sql
mysql> select * from customer;
+-------------+--------------------+
| customer_id | email              |
+-------------+--------------------+
|           1 | alice@domain.com   |
|           2 | bob@domain.com     |
|           3 | charlie@domain.com |
|           4 | dan@domain.com     |
|           5 | eve@domain.com     |
+-------------+--------------------+
5 rows in set (0.01 sec)

mysql> select * from product;
+----------+-------------+-------+
| sku      | description | price |
+----------+-------------+-------+
| SKU-1001 | Monitor     |   100 |
| SKU-1002 | Keyboard    |    30 |
+----------+-------------+-------+
2 rows in set (0.02 sec)
mysql> select * from 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 |
+----------+-------------+----------+-------+
5 rows in set (0.01 sec)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

$ cd /tmp/helm/common

$ mysql -h vtgate-zone1 -P 3306 < insert_commerce_data.sql

mysql> select * from customer;

+-------------+--------------------+

| customer_id | email |

+-------------+--------------------+

| 1 | alice@domain.com |

| 2 | bob@domain.com |

| 3 | charlie@domain.com |

| 4 | dan@domain.com |

| 5 | eve@domain.com |

+-------------+--------------------+

5 rows in set (0.01 sec)

mysql> select * from product;

+----------+-------------+-------+

| sku | description | price |

+----------+-------------+-------+

| SKU-1001 | Monitor | 100 |

| SKU-1002 | Keyboard | 30 |

+----------+-------------+-------+

2 rows in set (0.02 sec)

mysql> select * from corder;

+----------+-------------+----------+-------+

+----------+-------------+----------+-------+

| 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 |

+----------+-------------+----------+-------+

5 rows in set (0.01 sec)

On another shell on a host machine (laptop);

$ helm list
NAME           	NAMESPACE	REVISION	UPDATED                             	STATUS  	CHART         	APP VERSION
trendy-abalone	default  	1       	2020-01-10 12:22:03.607056 +0000 UTC	deployed	vitess-1.0.7-5
$ export release=trendy-abalone
$ helm upgrade $release ../../helm/vitess -f 201_customer_keyspace.yaml
Release "trendy-abalone" has been upgraded.
LAST DEPLOYED: Tue Dec  3 18:22:05 2019
NAMESPACE: default
STATUS: DEPLOYED

RESOURCES:
==> v1/ConfigMap
NAME       AGE
vitess-cm  134m

==> v1/Job
NAME                                AGE
vtctlclient-create-customer-ks      2s
zone1-commerce-0-init-shard-master  134m

==> v1/Pod(related)
NAME                                      AGE
vtctlclient-create-customer-ks-mshjc      2s
vtctld-757df48d4-c2gp9                    134m
vtgate-zone1-5cb4fcddcb-k2zsn             134m
zone1-commerce-0-init-shard-master-p6mgv  134m
zone1-commerce-0-rdonly-0                 134m
zone1-commerce-0-replica-0                134m
zone1-commerce-0-replica-1                134m

==> v1/Service
NAME          AGE
vtctld        134m
vtgate-zone1  134m
vttablet      134m

==> v1beta1/Deployment
NAME          AGE
vtctld        134m
vtgate-zone1  134m

==> v1beta1/PodDisruptionBudget
NAME                      AGE
vtgate-zone1              134m
zone1-commerce-0-rdonly   134m
zone1-commerce-0-replica  134m

==> v1beta1/StatefulSet
NAME                      AGE
zone1-commerce-0-rdonly   134m
zone1-commerce-0-replica  134m

==> v1beta2/EtcdCluster
NAME         AGE
etcd-global  134m
etcd-zone1   134m


NOTES:
Release name: trendy-abalone

To access administrative web pages, start a proxy with:
  kubectl proxy --port=8001

Then use the following URLs:

      vtctld: http://localhost:8001/api/v1/namespaces/default/services/vtctld:web/proxy/app/
      vtgate: http://localhost:8001/api/v1/namespaces/default/services/vtgate-zone1:web/proxy/


This is the part that shows the completion of this job. 
==> v1/Job
NAME                                AGE
vtctlclient-create-customer-ks      2s

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

$ helm list

NAME NAMESPACE REVISION UPDATED STATUS CHART APP VERSION

trendy-abalone default 1 2020-01-10 12:22:03.607056 +0000 UTC deployed vitess-1.0.7-5

$ export release=trendy-abalone

$ helm upgrade $release ../../helm/vitess -f 201_customer_keyspace.yaml

Release "trendy-abalone" has been upgraded.

LAST DEPLOYED: Tue Dec 3 18:22:05 2019

NAMESPACE: default

STATUS: DEPLOYED

RESOURCES:

==> v1/ConfigMap

NAME AGE

vitess-cm 134m

==> v1/Job

NAME AGE

vtctlclient-create-customer-ks 2s

zone1-commerce-0-init-shard-master 134m

==> v1/Pod(related)

NAME AGE

vtctlclient-create-customer-ks-mshjc 2s

vtctld-757df48d4-c2gp9 134m

vtgate-zone1-5cb4fcddcb-k2zsn 134m

zone1-commerce-0-init-shard-master-p6mgv 134m

zone1-commerce-0-rdonly-0 134m

zone1-commerce-0-replica-0 134m

zone1-commerce-0-replica-1 134m

==> v1/Service

NAME AGE

vtctld 134m

vtgate-zone1 134m

vttablet 134m

==> v1beta1/Deployment

NAME AGE

vtctld 134m

vtgate-zone1 134m

==> v1beta1/PodDisruptionBudget

NAME AGE

vtgate-zone1 134m

zone1-commerce-0-rdonly 134m

zone1-commerce-0-replica 134m

==> v1beta1/StatefulSet

NAME AGE

zone1-commerce-0-rdonly 134m

zone1-commerce-0-replica 134m

==> v1beta2/EtcdCluster

NAME AGE

etcd-global 134m

etcd-zone1 134m

NOTES:

Release name: trendy-abalone

To access administrative web pages, start a proxy with:

kubectl proxy --port=8001

Then use the following URLs:

vtctld: http://localhost:8001/api/v1/namespaces/default/services/vtctld:web/proxy/app/

vtgate: http://localhost:8001/api/v1/namespaces/default/services/vtgate-zone1:web/proxy/

This is the part that shows the completion of this job.

==> v1/Job

NAME AGE

vtctlclient-create-customer-ks 2s

Creating Customer Tablets

This part is to create vttablet instances (as seen above diagram) back to the new keyspace. Later, we’ll move the necessary tables.

$ helm upgrade $release ../../helm/vitess/ -f 202_customer_tablets.yaml
Release "trendy-abalone" has been upgraded.
LAST DEPLOYED: Tue Dec  3 18:28:13 2019
NAMESPACE: default
STATUS: DEPLOYED

RESOURCES:
==> v1/ConfigMap
NAME       AGE
vitess-cm  140m

==> v1/Job
NAME                                AGE
commerce-apply-vschema-vsplit       1s
customer-apply-vschema-vsplit       1s
customer-copy-schema-0              1s
zone1-commerce-0-init-shard-master  140m
zone1-customer-0-init-shard-master  1s

==> v1/Pod(related)
NAME                                      AGE
commerce-apply-vschema-vsplit-9gv88       1s
customer-apply-vschema-vsplit-zncbm       1s
customer-copy-schema-0-vbnqm              1s
vtctld-757df48d4-c2gp9                    140m
vtgate-zone1-5cb4fcddcb-k2zsn             140m
zone1-commerce-0-init-shard-master-p6mgv  140m
zone1-commerce-0-rdonly-0                 140m
zone1-commerce-0-replica-0                140m
zone1-commerce-0-replica-1                140m
zone1-customer-0-init-shard-master-fj75r  1s
zone1-customer-0-rdonly-0                 1s
zone1-customer-0-replica-0                1s
zone1-customer-0-replica-1                1s

==> v1/Service
NAME          AGE
vtctld        140m
vtgate-zone1  140m
vttablet      140m

==> v1beta1/Deployment
NAME          AGE
vtctld        140m
vtgate-zone1  140m

==> v1beta1/PodDisruptionBudget
NAME                      AGE
vtgate-zone1              140m
zone1-commerce-0-rdonly   140m
zone1-commerce-0-replica  140m
zone1-customer-0-rdonly   1s
zone1-customer-0-replica  1s

==> v1beta1/StatefulSet
NAME                      AGE
zone1-commerce-0-rdonly   140m
zone1-commerce-0-replica  140m
zone1-customer-0-rdonly   1s
zone1-customer-0-replica  1s

==> v1beta2/EtcdCluster
NAME         AGE
etcd-global  140m
etcd-zone1   140m


NOTES:
Release name: trendy-abalone

To access administrative web pages, start a proxy with:
  kubectl proxy --port=8001

Then use the following URLs:

      vtctld: http://localhost:8001/api/v1/namespaces/default/services/vtctld:web/proxy/app/
      vtgate: http://localhost:8001/api/v1/namespaces/default/services/vtgate-zone1:web/proxy/

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

$ helm upgrade $release ../../helm/vitess/ -f 202_customer_tablets.yaml

Release "trendy-abalone" has been upgraded.

LAST DEPLOYED: Tue Dec 3 18:28:13 2019

NAMESPACE: default

STATUS: DEPLOYED

RESOURCES:

==> v1/ConfigMap

NAME AGE

vitess-cm 140m

==> v1/Job

NAME AGE

commerce-apply-vschema-vsplit 1s

customer-apply-vschema-vsplit 1s

customer-copy-schema-0 1s

zone1-commerce-0-init-shard-master 140m

zone1-customer-0-init-shard-master 1s

==> v1/Pod(related)

NAME AGE

commerce-apply-vschema-vsplit-9gv88 1s

customer-apply-vschema-vsplit-zncbm 1s

customer-copy-schema-0-vbnqm 1s

vtctld-757df48d4-c2gp9 140m

vtgate-zone1-5cb4fcddcb-k2zsn 140m

zone1-commerce-0-init-shard-master-p6mgv 140m

zone1-commerce-0-rdonly-0 140m

zone1-commerce-0-replica-0 140m

zone1-commerce-0-replica-1 140m

zone1-customer-0-init-shard-master-fj75r 1s

zone1-customer-0-rdonly-0 1s

zone1-customer-0-replica-0 1s

zone1-customer-0-replica-1 1s

==> v1/Service

NAME AGE

vtctld 140m

vtgate-zone1 140m

vttablet 140m

==> v1beta1/Deployment

NAME AGE

vtctld 140m

vtgate-zone1 140m

==> v1beta1/PodDisruptionBudget

NAME AGE

vtgate-zone1 140m

zone1-commerce-0-rdonly 140m

zone1-commerce-0-replica 140m

zone1-customer-0-rdonly 1s

zone1-customer-0-replica 1s

==> v1beta1/StatefulSet

NAME AGE

zone1-commerce-0-rdonly 140m

zone1-commerce-0-replica 140m

zone1-customer-0-rdonly 1s

zone1-customer-0-replica 1s

==> v1beta2/EtcdCluster

NAME AGE

etcd-global 140m

etcd-zone1 140m

NOTES:

Release name: trendy-abalone

To access administrative web pages, start a proxy with:

kubectl proxy --port=8001

Then use the following URLs:

vtctld: http://localhost:8001/api/v1/namespaces/default/services/vtctld:web/proxy/app/

vtgate: http://localhost:8001/api/v1/namespaces/default/services/vtgate-zone1:web/proxy/

We have now two schemas even though customer vschema has tables, as the physical tables are still in commerce.

mysql> show databases;
+-----------+
| Databases |
+-----------+
| commerce  |
| customer  |
+-----------+
2 rows in set (0.01 sec)
mysql> use commerce
Database changed
mysql> show tables;
+--------------------+
| Tables_in_commerce |
+--------------------+
| corder             |
| customer           |
| product            |
+--------------------+
3 rows in set (0.00 sec)

mysql> use customer; show tables;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
+--------------------+
| Tables_in_commerce |
+--------------------+
| corder             |
| customer           |
| product            |
+--------------------+
3 rows in set (0.01 sec)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

mysql> show databases;

+-----------+

| Databases |

+-----------+

| commerce |

| customer |

+-----------+

2 rows in set (0.01 sec)

mysql> use commerce

Database changed

mysql> show tables;

+--------------------+

| Tables_in_commerce |

+--------------------+

| corder |

| customer |

| product |

+--------------------+

3 rows in set (0.00 sec)

mysql> use customer; show tables;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

+--------------------+

| Tables_in_commerce |

+--------------------+

| corder |

| customer |

| product |

+--------------------+

3 rows in set (0.01 sec)

We have six running vttablet pods ($kubectl get pods,jobs).

NAME                                      DESIRED   SUCCESSFUL   AGE
jobs/commerce-apply-vschema-vsplit        1         1            6m
jobs/customer-apply-vschema-vsplit        1         1            6m
jobs/customer-copy-schema-0               1         1            6m
jobs/zone1-commerce-0-init-shard-master   1         1            2h
jobs/zone1-customer-0-init-shard-master   1         1            6m

1

2

3

4

5

6

NAME DESIRED SUCCESSFUL AGE

jobs/commerce-apply-vschema-vsplit 1 1 6m

jobs/customer-apply-vschema-vsplit 1 1 6m

jobs/customer-copy-schema-0 1 1 6m

jobs/zone1-commerce-0-init-shard-master 1 1 2h

jobs/zone1-customer-0-init-shard-master 1 1 6m

Creating VerticalSplitClone

The following step is to run a vertical split which will start the migration of commerce data to customer.

$ helm upgrade $release ../../helm/vitess/ -f 203_vertical_split.yaml
Release "trendy-abalone" has been upgraded.
LAST DEPLOYED: Tue Dec  3 18:42:12 2019
NAMESPACE: default
STATUS: DEPLOYED

RESOURCES:
==> v1/ConfigMap
NAME       AGE
vitess-cm  154m

==> v1/Job
NAME                                AGE
vtworker-vertical-split             2s
zone1-commerce-0-init-shard-master  154m
zone1-customer-0-init-shard-master  14m

==> v1/Pod(related)
NAME                                      AGE
vtctld-757df48d4-c2gp9                    154m
vtgate-zone1-5cb4fcddcb-k2zsn             154m
vtworker-vertical-split-mhs95             2s
zone1-commerce-0-init-shard-master-p6mgv  154m
zone1-commerce-0-rdonly-0                 154m
zone1-commerce-0-replica-0                154m
zone1-commerce-0-replica-1                154m
zone1-customer-0-init-shard-master-fj75r  14m
zone1-customer-0-rdonly-0                 14m
zone1-customer-0-replica-0                14m
zone1-customer-0-replica-1                14m

==> v1/Service
NAME          AGE
vtctld        154m
vtgate-zone1  154m
vttablet      154m

==> v1beta1/Deployment
NAME          AGE
vtctld        154m
vtgate-zone1  154m

==> v1beta1/PodDisruptionBudget
NAME                      AGE
vtgate-zone1              154m
zone1-commerce-0-rdonly   154m
zone1-commerce-0-replica  154m
zone1-customer-0-rdonly   14m
zone1-customer-0-replica  14m

==> v1beta1/StatefulSet
NAME                      AGE
zone1-commerce-0-rdonly   154m
zone1-commerce-0-replica  154m
zone1-customer-0-rdonly   14m
zone1-customer-0-replica  14m

==> v1beta2/EtcdCluster
NAME         AGE
etcd-global  154m
etcd-zone1   154m


NOTES:
Release name: trendy-abalone

To access administrative web pages, start a proxy with:
  kubectl proxy --port=8001

Then use the following URLs:

      vtctld: http://localhost:8001/api/v1/namespaces/default/services/vtctld:web/proxy/app/
      vtgate: http://localhost:8001/api/v1/namespaces/default/services/vtgate-zone1:web/proxy/

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

$ helm upgrade $release ../../helm/vitess/ -f 203_vertical_split.yaml

Release "trendy-abalone" has been upgraded.

LAST DEPLOYED: Tue Dec 3 18:42:12 2019

NAMESPACE: default

STATUS: DEPLOYED

RESOURCES:

==> v1/ConfigMap

NAME AGE

vitess-cm 154m

==> v1/Job

NAME AGE

vtworker-vertical-split 2s

zone1-commerce-0-init-shard-master 154m

zone1-customer-0-init-shard-master 14m

==> v1/Pod(related)

NAME AGE

vtctld-757df48d4-c2gp9 154m

vtgate-zone1-5cb4fcddcb-k2zsn 154m

vtworker-vertical-split-mhs95 2s

zone1-commerce-0-init-shard-master-p6mgv 154m

zone1-commerce-0-rdonly-0 154m

zone1-commerce-0-replica-0 154m

zone1-commerce-0-replica-1 154m

zone1-customer-0-init-shard-master-fj75r 14m

zone1-customer-0-rdonly-0 14m

zone1-customer-0-replica-0 14m

zone1-customer-0-replica-1 14m

==> v1/Service

NAME AGE

vtctld 154m

vtgate-zone1 154m

vttablet 154m

==> v1beta1/Deployment

NAME AGE

vtctld 154m

vtgate-zone1 154m

==> v1beta1/PodDisruptionBudget

NAME AGE

vtgate-zone1 154m

zone1-commerce-0-rdonly 154m

zone1-commerce-0-replica 154m

zone1-customer-0-rdonly 14m

zone1-customer-0-replica 14m

==> v1beta1/StatefulSet

NAME AGE

zone1-commerce-0-rdonly 154m

zone1-commerce-0-replica 154m

zone1-customer-0-rdonly 14m

zone1-customer-0-replica 14m

==> v1beta2/EtcdCluster

NAME AGE

etcd-global 154m

etcd-zone1 154m

NOTES:

Release name: trendy-abalone

To access administrative web pages, start a proxy with:

kubectl proxy --port=8001

Then use the following URLs:

vtctld: http://localhost:8001/api/v1/namespaces/default/services/vtctld:web/proxy/app/

vtgate: http://localhost:8001/api/v1/namespaces/default/services/vtgate-zone1:web/proxy/

The following tasks have been performed.

Dirty copy data from commerce’s customer and corder tables to customer’s tables.
Stop replication on commerce’s rdonly tablet and perform final sync.
Start a filtered replication process from commerce->customer that keeps the customer’s tables in sync with those in commerce

mysql> \. select_customer0_data.sql
Using customer/0
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
Customer
+-------------+--------------------+
| customer_id | email              |
+-------------+--------------------+
|           1 | alice@domain.com   |
|           2 | bob@domain.com     |
|           3 | charlie@domain.com |
|           4 | dan@domain.com     |
|           5 | eve@domain.com     |
+-------------+--------------------+
5 rows in set (0.00 sec)
 
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 |
+----------+-------------+----------+-------+
5 rows in set (0.00 sec)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

mysql> \. select_customer0_data.sql

Using customer/0

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

Customer

+-------------+--------------------+

| customer_id | email |

+-------------+--------------------+

| 1 | alice@domain.com |

| 2 | bob@domain.com |

| 3 | charlie@domain.com |

| 4 | dan@domain.com |

| 5 | eve@domain.com |

+-------------+--------------------+

5 rows in set (0.00 sec)

COrder

+----------+-------------+----------+-------+

+----------+-------------+----------+-------+

| 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 |

+----------+-------------+----------+-------+

5 rows in set (0.00 sec)

Final Cut Over

Once we’ve verified the customer and corder tables are being updated continuously from commerce we can cut over by the order of rdonly, replica and master.

$ helm upgrade $release ../../helm/vitess/ -f 204_vertical_migrate_replicas.yaml
$ helm upgrade $release ../../helm/vitess/ -f 205_vertical_migrate_master.yaml

1 2	$ helm upgrade $release ../../helm/vitess/ -f 204_vertical_migrate_replicas.yaml $ helm upgrade $release ../../helm/vitess/ -f 205_vertical_migrate_master.yaml

After running the above commands, commerce data will not be available.

mysql> \. select_commerce_data.sql
Using commerce/0
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
Customer
ERROR 1105 (HY000): vtgate: http://vtgate-zone1-5cb4fcddcb-k2zsn:15001/: target: commerce.0.master, used tablet: zone1-1564760600 (zone1-commerce-0-replica-0.vttablet), vttablet: rpc error: code = FailedPrecondition desc = disallowed due to rule: enforce blacklisted tables (CallerID: userData1)
Product
+----------+-------------+-------+
| sku      | description | price |
+----------+-------------+-------+
| SKU-1001 | Monitor     |   100 |
| SKU-1002 | Keyboard    |    30 |
+----------+-------------+-------+
2 rows in set (0.01 sec)

COrder
ERROR 1105 (HY000): vtgate: http://vtgate-zone1-5cb4fcddcb-k2zsn:15001/: target: commerce.0.master, used tablet: zone1-1564760600 (zone1-commerce-0-replica-0.vttablet), vttablet: rpc error: code = FailedPrecondition desc = disallowed due to rule: enforce blacklisted tables (CallerID: userData1)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

mysql> \. select_commerce_data.sql

Using commerce/0

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

Customer

ERROR 1105 (HY000): vtgate: http://vtgate-zone1-5cb4fcddcb-k2zsn:15001/: target: commerce.0.master, used tablet: zone1-1564760600 (zone1-commerce-0-replica-0.vttablet), vttablet: rpc error: code = FailedPrecondition desc = disallowed due to rule: enforce blacklisted tables (CallerID: userData1)

Product

+----------+-------------+-------+

| sku | description | price |

+----------+-------------+-------+

| SKU-1001 | Monitor | 100 |

| SKU-1002 | Keyboard | 30 |

+----------+-------------+-------+

2 rows in set (0.01 sec)

COrder

ERROR 1105 (HY000): vtgate: http://vtgate-zone1-5cb4fcddcb-k2zsn:15001/: target: commerce.0.master, used tablet: zone1-1564760600 (zone1-commerce-0-replica-0.vttablet), vttablet: rpc error: code = FailedPrecondition desc = disallowed due to rule: enforce blacklisted tables (CallerID: userData1)

Conclusion

In this blog, we’ve gone over preplanned examples by Vitess as you should be able to mimic your use case using a similar methodology. We had to go outside of the conventional Kubernetes setup using Minikube. While this has caused us to apply some workarounds, it’s clear to see how to scale with Vitess and its possibilities.

Links:

Please read part I of this series: Introduction to Vitess on Kubernetes for MySQL – Part I

Please read part II of this series: Setup and Deploy Vitess on Kubernetes (Minikube) for MySQL – Part II

References

Credits

Daniel Guzman Burgos – Technical Lead (MySQL)
Mykola Marzhan – Director of Server Engineering
Sergey Kuzmichev – Support Engineer

Setup and Deploy Vitess on Kubernetes (Minikube) for MySQL – Part III of III

Upcoming Webinars

Services

Products

Solutions

Resources

More

Contact Us

Setup and Deploy Vitess on Kubernetes (Minikube) for MySQL – Part III of III

Creating a Keyspace

Vertical Split

Creating Customer Tablets

Creating VerticalSplitClone

Final Cut Over

Conclusion

References

Credits

Related

Author

Share this post

Comment (1)

Leave a Reply Cancel reply

Upcoming Webinars

Services

Products

Solutions

Resources

More

Contact Us