Helm Chart for MySQL in GKE

Here is how to deploy and test connectivity of MySQL in Google Kubernetes Engine GKE

# connect to cluster in Google Kubernetes Engine
$ gcloud container clusters get-credentials <your-cluster> --zone <your-zone> --project <your-project>
$ git clone --depth=1 https://github.com/helm/charts.git
$ cd charts/stable/mysql
$ vim values.yaml
$ helm install --name dbserver -f values.yaml stable/mysql
$ kubectl get pods
$ MYSQL_ROOT_PASSWORD=$(kubectl get secret --namespace default dbserver-mysql -o jsonpath="{.data.mysql-root-password}" | base64 --decode; echo)
$ echo $MYSQL_ROOT_PASSWORD
$ kubectl run -i --tty ubuntu --image=ubuntu:16.04 --restart=Never -- bash -il
root@ubuntu:/# apt-get update && apt-get install mysql-client -y
root@ubuntu:/#  mysql -h dbserver-mysql -p
mysql> exit
root@ubuntu:/# exit
$ kubectl get pvc
$ kubectl get pv
 
# clean up
$ helm delete --purge dbserver
$ kubectl delete pod/ubuntu

MySQL and JSON column

Since version 5.7 MySQL has support for JSON. Here's create table script and simple CRUD SQL statements.

CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `jdoc` json DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `t1` (`id`, `jdoc`) VALUES
(1, '["officia", "et", "anim", "dolore", "ut", "duis", "quis"]'),
(2, '{"valid_until": "23.03.2016"}'),
(4, '{"valid_until": "24.04.2012"}'),
(6, '["lorem", "ipsum"]'),
(7, '{"valid_until": "24.04.2034"}');

ALTER TABLE `t1`  ADD PRIMARY KEY (`id`);

ALTER TABLE `t1`  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

-- SELECT id, json_extract(jdoc, "$.valid_until") as jd FROM t1 WHERE json_extract(jdoc, "$.valid_until") IS NOT NULL
-- INSERT INTO t1 VALUES (null, '{"valid_until":"24.04.2034"}' )
-- UPDATE t1 SET jdoc= JSON_SET(jdoc, "$.valid_until", "24.04.2016") WHERE id = 3;
-- DELETE FROM t1  WHERE json_extract(jdoc, "$.valid_until") = '24.04.2016';

Create New MySQL User and his Database

Create new MySQL user and database

create database eddy;
create user eddy@localhost identified by 'gt84weu$rA^o#HB';
grant all on eddy.* to eddy identified by 'gt84weu$rA^o#HB';

This snippet should be run from MySQL command line.

Two aliases for the same MySQL table

Here's how to establish database relationships between connected entities. In this example we'll use a highways map. The final query will show city names and highway that connects them in  result set rows.

Map of highways:

Connections between cities

Connecting Two Derived Table

You want to join results from two queries - the derived tables are solution for you.
Look at code below.Yellow highlighted lines are queries that you want to join and green highlighted

Complex SQL Queries with columns

Advanced MySQL queries with CASE, IF and FIND_IN_SET: