Downloads

Blog

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

January 15, 2020

Author

Share this Post:

vitess kubernetes mysql pt3 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<br>NAME                                                                READY     STATUS    RESTARTS   AGE<br>po/etcd-global-kbbcqlgvp9                                           1/1       Running   0          43m<br>po/etcd-zone1-lpc5zmdxxn                                            1/1       Running   0          43m<br>po/my-release-etcd-operator-etcd-backup-operator-6684dd6d8c-pr4n4   1/1       Running   0          1h<br>po/my-release-etcd-operator-etcd-operator-86d94989d6-w9lpx          1/1       Running   0          1h<br>po/my-release-etcd-operator-etcd-restore-operator-c655d757c-9nsnz   1/1       Running   0          1h<br>po/vtctld-757df48d4-c2gp9                                           1/1       Running   3          43m<br>po/vtgate-zone1-5cb4fcddcb-k2zsn                                    1/1       Running   3          43m<br>po/zone1-commerce-0-rdonly-0                                        6/6       Running   0          43m<br>po/zone1-commerce-0-replica-0                                       6/6       Running   0          43m<br>po/zone1-commerce-0-replica-1                                       6/6       Running   0          43m<br><br>NAME                                      DESIRED   SUCCESSFUL   AGE<br>jobs/commerce-apply-schema-initial        1         1            43m<br>jobs/commerce-apply-vschema-initial       1         1            43m<br>jobs/zone1-commerce-0-init-shard-master   1         1            43m<br>

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

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

Creating a Keyspace

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

mysql> show databases;<br>+-----------+<br>| Databases |<br>+-----------+<br>| commerce  |<br>+-----------+<br>1 row in set (0.00 sec)<br><br>mysql> show tables;<br>+--------------------+<br>| Tables_in_commerce |<br>+--------------------+<br>| corder             |<br>| customer           |<br>| product            |<br>+--------------------+<br>3 rows in set (0.01 sec)<br>

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

mysql> show create table corderG<br>*************************** 1. row ***************************<br>       Table: corder<br>Create Table: CREATE TABLE `corder` (<br>  `order_id` bigint(20) NOT NULL AUTO_INCREMENT,<br>  `customer_id` bigint(20) DEFAULT NULL,<br>  `sku` varbinary(128) DEFAULT NULL,<br>  `price` bigint(20) DEFAULT NULL,<br>  PRIMARY KEY (`order_id`)<br>) ENGINE=InnoDB DEFAULT CHARSET=utf8<br>1 row in set (0.00 sec)<br><br>mysql> show create table customerG<br>*************************** 1. row ***************************<br>       Table: customer<br>Create Table: CREATE TABLE `customer` (<br>  `customer_id` bigint(20) NOT NULL AUTO_INCREMENT,<br>  `email` varbinary(128) DEFAULT NULL,<br>  PRIMARY KEY (`customer_id`)<br>) ENGINE=InnoDB DEFAULT CHARSET=utf8<br>1 row in set (0.01 sec)<br><br>mysql> show create table productG<br>*************************** 1. row ***************************<br>       Table: product<br>Create Table: CREATE TABLE `product` (<br>  `sku` varbinary(128) NOT NULL,<br>  `description` varbinary(128) DEFAULT NULL,<br>  `price` bigint(20) DEFAULT NULL,<br>  PRIMARY KEY (`sku`)<br>) ENGINE=InnoDB DEFAULT CHARSET=utf8<br>1 row in set (0.00 sec)<br>

mysql> show create table corderG *************************** 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 customerG *************************** 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 productG *************************** 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<br>vschema:<br>            initial: |-<br>              {<br>                "tables": {<br>                  "product": {},<br>                  "customer": {},<br>                  "corder": {}<br>                }<br>              }<br>

1	101_initial_cluster.yaml<br>vschema:<br> initial: \|-<br> {<br> "tables": {<br> "product": {},<br> "customer": {},<br> "corder": {}<br> }<br> }<br>

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<br>$ mysql -h vtgate-zone1 -P 3306 < insert_commerce_data.sql<br>mysql> select * from customer;<br>+-------------+--------------------+<br>| customer_id | email              |<br>+-------------+--------------------+<br>|           1 | alice@domain.com   |<br>|           2 | bob@domain.com     |<br>|           3 | charlie@domain.com |<br>|           4 | dan@domain.com     |<br>|           5 | eve@domain.com     |<br>+-------------+--------------------+<br>5 rows in set (0.01 sec)<br><br>mysql> select * from product;<br>+----------+-------------+-------+<br>| sku      | description | price |<br>+----------+-------------+-------+<br>| SKU-1001 | Monitor     |   100 |<br>| SKU-1002 | Keyboard    |    30 |<br>+----------+-------------+-------+<br>2 rows in set (0.02 sec)<br>mysql> select * from corder;<br>+----------+-------------+----------+-------+<br>| order_id | customer_id | sku      | price |<br>+----------+-------------+----------+-------+<br>|        1 |           1 | SKU-1001 |   100 |<br>|        2 |           2 | SKU-1002 |    30 |<br>|        3 |           3 | SKU-1002 |    30 |<br>|        4 |           4 | SKU-1002 |    30 |<br>|        5 |           5 | SKU-1002 |    30 |<br>+----------+-------------+----------+-------+<br>5 rows in set (0.01 sec)<br>

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

On another shell on a host machine (laptop);

$ helm list<br>NAME           	NAMESPACE	REVISION	UPDATED                             	STATUS  	CHART         	APP VERSION<br>trendy-abalone	default  	1       	2020-01-10 12:22:03.607056 +0000 UTC	deployed	vitess-1.0.7-5<br>$ export release=trendy-abalone<br>$ helm upgrade $release ../../helm/vitess -f 201_customer_keyspace.yaml<br>Release "trendy-abalone" has been upgraded.<br>LAST DEPLOYED: Tue Dec  3 18:22:05 2019<br>NAMESPACE: default<br>STATUS: DEPLOYED<br><br>RESOURCES:<br>==> v1/ConfigMap<br>NAME       AGE<br>vitess-cm  134m<br><br>==> v1/Job<br>NAME                                AGE<br>vtctlclient-create-customer-ks      2s<br>zone1-commerce-0-init-shard-master  134m<br><br>==> v1/Pod(related)<br>NAME                                      AGE<br>vtctlclient-create-customer-ks-mshjc      2s<br>vtctld-757df48d4-c2gp9                    134m<br>vtgate-zone1-5cb4fcddcb-k2zsn             134m<br>zone1-commerce-0-init-shard-master-p6mgv  134m<br>zone1-commerce-0-rdonly-0                 134m<br>zone1-commerce-0-replica-0                134m<br>zone1-commerce-0-replica-1                134m<br><br>==> v1/Service<br>NAME          AGE<br>vtctld        134m<br>vtgate-zone1  134m<br>vttablet      134m<br><br>==> v1beta1/Deployment<br>NAME          AGE<br>vtctld        134m<br>vtgate-zone1  134m<br><br>==> v1beta1/PodDisruptionBudget<br>NAME                      AGE<br>vtgate-zone1              134m<br>zone1-commerce-0-rdonly   134m<br>zone1-commerce-0-replica  134m<br><br>==> v1beta1/StatefulSet<br>NAME                      AGE<br>zone1-commerce-0-rdonly   134m<br>zone1-commerce-0-replica  134m<br><br>==> v1beta2/EtcdCluster<br>NAME         AGE<br>etcd-global  134m<br>etcd-zone1   134m<br><br><br>NOTES:<br>Release name: trendy-abalone<br><br>To access administrative web pages, start a proxy with:<br>  kubectl proxy --port=8001<br><br>Then use the following URLs:<br><br>      vtctld: http://localhost:8001/api/v1/namespaces/default/services/vtctld:web/proxy/app/<br>      vtgate: http://localhost:8001/api/v1/namespaces/default/services/vtgate-zone1:web/proxy/<br><br><br>This is the part that shows the completion of this job. <br>==> v1/Job<br>NAME                                AGE<br>vtctlclient-create-customer-ks      2s<br>

$ 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<br>Release "trendy-abalone" has been upgraded.<br>LAST DEPLOYED: Tue Dec  3 18:28:13 2019<br>NAMESPACE: default<br>STATUS: DEPLOYED<br><br>RESOURCES:<br>==> v1/ConfigMap<br>NAME       AGE<br>vitess-cm  140m<br><br>==> v1/Job<br>NAME                                AGE<br>commerce-apply-vschema-vsplit       1s<br>customer-apply-vschema-vsplit       1s<br>customer-copy-schema-0              1s<br>zone1-commerce-0-init-shard-master  140m<br>zone1-customer-0-init-shard-master  1s<br><br>==> v1/Pod(related)<br>NAME                                      AGE<br>commerce-apply-vschema-vsplit-9gv88       1s<br>customer-apply-vschema-vsplit-zncbm       1s<br>customer-copy-schema-0-vbnqm              1s<br>vtctld-757df48d4-c2gp9                    140m<br>vtgate-zone1-5cb4fcddcb-k2zsn             140m<br>zone1-commerce-0-init-shard-master-p6mgv  140m<br>zone1-commerce-0-rdonly-0                 140m<br>zone1-commerce-0-replica-0                140m<br>zone1-commerce-0-replica-1                140m<br>zone1-customer-0-init-shard-master-fj75r  1s<br>zone1-customer-0-rdonly-0                 1s<br>zone1-customer-0-replica-0                1s<br>zone1-customer-0-replica-1                1s<br><br>==> v1/Service<br>NAME          AGE<br>vtctld        140m<br>vtgate-zone1  140m<br>vttablet      140m<br><br>==> v1beta1/Deployment<br>NAME          AGE<br>vtctld        140m<br>vtgate-zone1  140m<br><br>==> v1beta1/PodDisruptionBudget<br>NAME                      AGE<br>vtgate-zone1              140m<br>zone1-commerce-0-rdonly   140m<br>zone1-commerce-0-replica  140m<br>zone1-customer-0-rdonly   1s<br>zone1-customer-0-replica  1s<br><br>==> v1beta1/StatefulSet<br>NAME                      AGE<br>zone1-commerce-0-rdonly   140m<br>zone1-commerce-0-replica  140m<br>zone1-customer-0-rdonly   1s<br>zone1-customer-0-replica  1s<br><br>==> v1beta2/EtcdCluster<br>NAME         AGE<br>etcd-global  140m<br>etcd-zone1   140m<br><br><br>NOTES:<br>Release name: trendy-abalone<br><br>To access administrative web pages, start a proxy with:<br>  kubectl proxy --port=8001<br><br>Then use the following URLs:<br><br>      vtctld: http://localhost:8001/api/v1/namespaces/default/services/vtctld:web/proxy/app/<br>      vtgate: http://localhost:8001/api/v1/namespaces/default/services/vtgate-zone1:web/proxy/<br>

$ 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;<br>+-----------+<br>| Databases |<br>+-----------+<br>| commerce  |<br>| customer  |<br>+-----------+<br>2 rows in set (0.01 sec)<br>mysql> use commerce<br>Database changed<br>mysql> show tables;<br>+--------------------+<br>| Tables_in_commerce |<br>+--------------------+<br>| corder             |<br>| customer           |<br>| product            |<br>+--------------------+<br>3 rows in set (0.00 sec)<br><br>mysql> use customer; show tables;<br>Reading table information for completion of table and column names<br>You can turn off this feature to get a quicker startup with -A<br><br>Database changed<br>+--------------------+<br>| Tables_in_commerce |<br>+--------------------+<br>| corder             |<br>| customer           |<br>| product            |<br>+--------------------+<br>3 rows in set (0.01 sec)<br>

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

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

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

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<br>Release "trendy-abalone" has been upgraded.<br>LAST DEPLOYED: Tue Dec  3 18:42:12 2019<br>NAMESPACE: default<br>STATUS: DEPLOYED<br><br>RESOURCES:<br>==> v1/ConfigMap<br>NAME       AGE<br>vitess-cm  154m<br><br>==> v1/Job<br>NAME                                AGE<br>vtworker-vertical-split             2s<br>zone1-commerce-0-init-shard-master  154m<br>zone1-customer-0-init-shard-master  14m<br><br>==> v1/Pod(related)<br>NAME                                      AGE<br>vtctld-757df48d4-c2gp9                    154m<br>vtgate-zone1-5cb4fcddcb-k2zsn             154m<br>vtworker-vertical-split-mhs95             2s<br>zone1-commerce-0-init-shard-master-p6mgv  154m<br>zone1-commerce-0-rdonly-0                 154m<br>zone1-commerce-0-replica-0                154m<br>zone1-commerce-0-replica-1                154m<br>zone1-customer-0-init-shard-master-fj75r  14m<br>zone1-customer-0-rdonly-0                 14m<br>zone1-customer-0-replica-0                14m<br>zone1-customer-0-replica-1                14m<br><br>==> v1/Service<br>NAME          AGE<br>vtctld        154m<br>vtgate-zone1  154m<br>vttablet      154m<br><br>==> v1beta1/Deployment<br>NAME          AGE<br>vtctld        154m<br>vtgate-zone1  154m<br><br>==> v1beta1/PodDisruptionBudget<br>NAME                      AGE<br>vtgate-zone1              154m<br>zone1-commerce-0-rdonly   154m<br>zone1-commerce-0-replica  154m<br>zone1-customer-0-rdonly   14m<br>zone1-customer-0-replica  14m<br><br>==> v1beta1/StatefulSet<br>NAME                      AGE<br>zone1-commerce-0-rdonly   154m<br>zone1-commerce-0-replica  154m<br>zone1-customer-0-rdonly   14m<br>zone1-customer-0-replica  14m<br><br>==> v1beta2/EtcdCluster<br>NAME         AGE<br>etcd-global  154m<br>etcd-zone1   154m<br><br><br>NOTES:<br>Release name: trendy-abalone<br><br>To access administrative web pages, start a proxy with:<br>  kubectl proxy --port=8001<br><br>Then use the following URLs:<br><br>      vtctld: http://localhost:8001/api/v1/namespaces/default/services/vtctld:web/proxy/app/<br>      vtgate: http://localhost:8001/api/v1/namespaces/default/services/vtgate-zone1:web/proxy/<br>

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

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 | [email protected] | | 2 | [email protected] | | 3 | [email protected] | | 4 | [email protected] | | 5 | [email protected] | +-------------+--------------------+ 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)

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<br>$ helm upgrade $release ../../helm/vitess/ -f 205_vertical_migrate_master.yaml<br>

1	$ helm upgrade $release ../../helm/vitess/ -f 204_vertical_migrate_replicas.yaml<br>$ helm upgrade $release ../../helm/vitess/ -f 205_vertical_migrate_master.yaml<br>

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

mysql> . select_commerce_data.sql<br>Using commerce/0<br>Reading table information for completion of table and column names<br>You can turn off this feature to get a quicker startup with -A<br><br>Database changed<br>Customer<br>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)<br>Product<br>+----------+-------------+-------+<br>| sku      | description | price |<br>+----------+-------------+-------+<br>| SKU-1001 | Monitor     |   100 |<br>| SKU-1002 | Keyboard    |    30 |<br>+----------+-------------+-------+<br>2 rows in set (0.01 sec)<br><br>COrder<br>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)<br>

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