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.
|
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:
|
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 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:
|
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.
|
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.
|
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.
|
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; +----------+-------------+----------+-------+ | 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);
|
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.
|
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.
|
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).
|
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.
|
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
|
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 | +-------------+--------------------+ +-------------+--------------------+ 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.
|
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.
|
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
About the Author
Alkin TezuysalAlkin has extensive experience in enterprise relational databases working in various sectors for large corporations. With more than 20 years of industry experience, he has acquired skills for managing large projects from the ground up to production. For the past 10 years, he's been focusing on e-commerce, SaaS and MySQL technologies. He managed and architected database topologies for high volume sites at eBay Intl. He has several years of experience in 24X7 support and operational tasks as well as improving database systems for major companies. He has led MySQL global operations team on Tier 1/2/3 support for MySQL customers. In 2016 he has joined Percona's expert technical management team.
Great blog!