Deploying MySQL High Available solution (InnoDB Cluster + Group Replication) inside Kubernetes using the Oracle MySQL Operator.
- Introducing the Oracle MySQL Operator for Kubernetes
- Oracle MySQL Operator GitGub Tutorial
- Setting up MySQL InnoDB Cluster using Docker containers
- Manually install Kubernetes cluster on your laptop computer in minutes⭐
- A Kubernetes v1.8.0+ cluster
- P.S this project uses the above ↑ mentioned Kubernetes cluster solution running locally in the host computer
- Helm installed and configured in your cluster.
Clone the project and cd into the folder:
$ git clone https://github.com/oracle/mysql-operator $ cd mysql-operator First create a namespace for the mysql-operator:
$ kubectl create ns mysql-operator The below command deploys the MySQL Operator on the Kubernetes cluster in the default configuration.
$ helm install mysql-operator mysql-operator Output:
NAME: mysql-operator LAST DEPLOYED: Sat Dec 14 14:01:37 2019 NAMESPACE: default STATUS: deployed REVISION: 1 TEST SUITE: None NOTES: Thanks for installing the MySQL Operator. Check if the operator is running with kubectl -n mysql-operator get poCheck the install:
$ kubectl -n mysql-operator get po NAME READY STATUS RESTARTS AGE mysql-operator-65475db5-c95f2 1/1 Running 0 88s[Optional] To list all of the releases, run:
$ helm list NAME NAMESPACE REVISION UPDATED STATUS CHART APP VERSION mysql-operator default 1 2019-12-14 14:03:58.40078221 +0000 UTC deployed mysql-operator-0.2.1The first time you create a MySQL Cluster in a namespace (other than in the namespace into which you installed the mysql-operator) you need to create the mysql-agent ServiceAccount and RoleBinding in that namespace, in our case will be innodb-cluster, run:
$ kubectl create namespace innodb-cluster Then run:
$ cat <<EOF | kubectl create -f - apiVersion: v1 kind: ServiceAccount metadata: name: mysql-agent namespace: innodb-cluster --- kind: RoleBinding apiVersion: rbac.authorization.k8s.io/v1beta1 metadata: name: mysql-agent namespace: innodb-cluster roleRef: apiGroup: rbac.authorization.k8s.io kind: ClusterRole name: mysql-agent subjects: - kind: ServiceAccount name: mysql-agent namespace: innodb-cluster EOF Similar output must be displayed:
serviceaccount/mysql-agent created rolebinding.rbac.authorization.k8s.io/mysql-agent createdThe next command will deploy a MySQL InnoDB Cluster with 4 members and single primary mode (multiMaster: false).
Single Primary: Just one group member (primary) is set to read-write mode. All the others (secondaries) are set to read-only mode.Multi-Primary: All group members are primaries and set to read-write mode and there is no need to engage an election.
To find out how to create larger clusters and configure storage see clusters.
Create your MySQL password with a password field. Mine is secret. (P.S. The rest of the tutorial will use secret as password)
$ kubectl create secret generic -n innodb-cluster mysql-root-user-secret --from-literal=password=secret Create a new cluster:
$ cat <<EOF | kubectl create -f - apiVersion: mysql.oracle.com/v1alpha1 kind: Cluster metadata: name: my-app-db namespace: innodb-cluster spec: multiMaster: false members: 4 rootPasswordSecret: name: mysql-root-user-secret EOF Output:
cluster.mysql.oracle.com/my-app-db createdTo check whether it's deployed:
$ kubectl -n innodb-cluster get mysqlclusters NAME AGE my-app-db 1mIt will take some minutes till you get all pods running:
$ kubectl -n innodb-cluster get pods NAME READY STATUS RESTARTS AGE my-app-db-0 2/2 Running 0 8m56s my-app-db-1 2/2 Running 0 7m12s my-app-db-2 2/2 Running 0 3m14s my-app-db-3 2/2 Running 0 2m13s[Optional] List all pods in the namespace, with more details
$ kubectl get pods -o wide -n innodb-cluster NAME READY STATUS RESTARTS AGE IP NODE NOMINATED NODE my-app-db-0 2/2 Running 0 8m58s 10.244.1.3 worker1.vagrant.vm <none> my-app-db-1 2/2 Running 0 7m14s 10.244.2.3 worker2.vagrant.vm <none> my-app-db-2 2/2 Running 0 3m16s 10.244.1.4 worker1.vagrant.vm <none> my-app-db-3 2/2 Running 0 2m15s 10.244.2.5 worker2.vagrant.vm <none> Streaming data from a pod:
$ kubectl logs -f my-app-db-0 -n innodb-cluster mysql + base=1000 ++ grep -o '[^-]*$' ++ cat /etc/hostname + index=0 ++ expr 1000 + 0 + /entrypoint.sh --server_id=1000 --datadir=/var/lib/mysql --user=mysql --gtid_mode=ON --log-bin --binlog_checksum=NONE --enforce_gtid_consistency=ON --log-slave-updates=ON --binlog-format=ROW --master-info-repository=TABLE --relay-log-info-repository=TABLE --transaction-write-set-extraction=XXHASH64 --relay-log=my-app-db-0-relay-bin --report-host=my-app-db-0.my-app-db --log-error-verbosity=3 [Entrypoint] MySQL Docker Image 8.0.12-1.1.7 [Entrypoint] Initializing database 2019-12-14T14:38:17.515523Z 0 [Note] [MY-010096] [Server] Ignoring --secure-file-priv value as server is running with --initialize(-insecure). 2019-12-14T14:38:17.515597Z 0 [Note] [MY-010949] [Server] Basedir set to /usr/. 2019-12-14T14:38:17.515611Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.12) initializing of server in progress as process 27 2019-12-14T14:38:17.517890Z 0 [Note] [MY-010458] [Server] --initialize specified on an existing data directory. 2019-12-14T14:38:17.519036Z 0 [Note] [MY-010163] [Server] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use '--log-bin=my-app-db-0-bin' to avoid this problem. 2019-12-14T14:38:17.523244Z 0 [Note] [MY-012366] [InnoDB] InnoDB: Using Linux native AIO 2019-12-14T14:38:17.523555Z 0 [Note] [MY-010747] [Server] Plugin 'FEDERATED' is disabled. 2019-12-14T14:38:17.525070Z 1 [Note] [MY-012274] [InnoDB] InnoDB: The first innodb_system data file 'ibdata1' did not exist. A new tablespace will be created! ...[Optional] You can also check the logs from my-app-db-1, my-app-db-2 or my-app-db-3.
Let's use a MySQL client container to verify that you can connect to MySQL from within the Kubernetes cluster.
$ kubectl run mysql-client --image=mysql:8.0 -n innodb-cluster -it --rm --restart=Never \ -- mysql -h my-app-db -uroot -psecret -e 'SHOW DATABASES' Output:
mysql: [Warning] Using a password on the command line interface can be insecure. +-------------------------------+ | Database | +-------------------------------+ | information_schema | | mysql | | mysql_innodb_cluster_metadata | | performance_schema | | sys | +-------------------------------+ pod "mysql-client" deletedLet's use the Performance Schema tables to monitor Group Replication:
$ kubectl run mysql-client --image=mysql:8.0 -n innodb-cluster -it --rm --restart=Never \ -- mysql -h my-app-db -uroot -psecret -e 'SELECT * FROM performance_schema.replication_group_members' Output:
mysql: [Warning] Using a password on the command line interface can be insecure. +---------------------------+--------------------------------------+-----------------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-----------------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 55a19bcf-1eb8-11ea-9ec1-eee593fa0e7e | my-app-db-0.my-app-db | 3306 | ONLINE | PRIMARY | 8.0.12 | | group_replication_applier | 6a92e636-1eb8-11ea-a52b-f6c9be9815ee | my-app-db-1.my-app-db | 3306 | ONLINE | SECONDARY | 8.0.12 | | group_replication_applier | 8c560b8f-1eb8-11ea-a031-a6d528525d75 | my-app-db-2.my-app-db | 3306 | ONLINE | SECONDARY | 8.0.12 | | group_replication_applier | cccc24a7-1eb8-11ea-97f0-862bcaacbbd8 | my-app-db-3.my-app-db | 3306 | ONLINE | SECONDARY | 8.0.12 | +---------------------------+--------------------------------------+-----------------------+-------------+--------------+-------------+----------------+ pod "mysql-client" deletedTo uninstall/delete the mysql-operator deployment, run:
$ helm delete mysql-operator Checking the pods:
$ kubectl -n innodb-cluster get pods NAME READY STATUS RESTARTS AGE my-app-db-0 2/2 Terminating 0 21m my-app-db-1 2/2 Terminating 0 19m my-app-db-2 2/2 Terminating 0 15m my-app-db-3 2/2 Terminating 0 14mTo delete the created namespace, run:
$ kubectl delete ns innodb-cluster