MySQL Operator

This is a go-based Kubernetes operator built with operator-sdk, which manages MySQL databases, schema, users, permissions in existing MySQL servers. This operator DOES NOT manage MySQL cluster like other MySQL operators such as vitess, mysql/mysql-operator.

Motivation

Reduce human operations:

1. User management: When creating a MySQL user for an application running on Kubernetes, it's necessary to create a MySQL user and create a Secret manually or with a script, which can be replaced with a Kubernetes operator. The initial idea is from KafkaUser and KafkaTopic in Strimzi Kafka Operator. With a custom resource for MySQL user, we can manage MySQL users with Kubernetes manifest files as a part of dependent application. Benefits from such a custom resource and operator:
   1. Kubernetes manifest files for an application and its dependent resources (including MySQL user) can be managed together with Kustomize or Helm chart, with which we can easily duplicate whole environment.
   2. There's no chance to require someone to check the raw password as it's stored directly to Secret by the operator, and read by the dependent application from the Secret.
2. Database migration: Reduce manual operations but keep changelog. When any schema migration or database operation is required, we needed a human operation, which has potential risk of human errors that should be avoided. With a Kubernetes operator, we can execute each database operation in the standard way with traceable changlog.

Versions

• Go: 1.21

Components

1. Custom Resource
   1. MySQL: MySQL cluster (host, port, adminUser, adminPassword hoding the credentials to connect to MySQL)
   2. MySQLUser: MySQL user (mysqlName and host)
   3. MySQLDB: MySQL database (mysqlName, dbName, schemaMigrationFromGitHub)
2. Reconciler
   1. MySQLReconciler is responsible for managing MySQLClients based on MySQL and MySQLDB resources
   2. MySQLUserReconciler is responsible for creating/deleting MySQL users defined in MySQLUser using MySQLClients, and creating Secret to store MySQL user's password
   3. MySQLDBReconciler is responsible for creating/deleting database and schema migration defined in MySQLDB using MySQLClients

Getting Started

1. Install (Create CRD and operator objects) With kustomize:

   kubectl apply -k https://github.com/nakamasato/mysql-operator/config/install
   

   With Helm:

   helm repo add nakamasato https://nakamasato.github.io/helm-charts
   helm repo update
   helm install mysql-operator nakamasato/mysql-operator
   

2. (Optional) prepare MySQL.

   kubectl apply -k https://github.com/nakamasato/mysql-operator/config/mysql
   

3. Apply custom resources (MySQL, MySQLUser, MySQLDB).

   mysql.yaml credentials to connect to the MySQL:

   apiVersion: mysql.nakamasato.com/v1alpha1
   kind: MySQL
   metadata:
     name: mysql-sample
   spec:
     host: mysql.default # need to include namespace if you use Kubernetes Service as an endpoint.
     adminUser:
       name: root
       type: raw
     adminPassword:
       name: password
       type: raw

   mysqluser.yaml: MySQL user

   apiVersion: mysql.nakamasato.com/v1alpha1
   kind: MySQLUser
   metadata:
     name: sample-user
   spec:
     mysqlName: mysql-sample
     host: '%'

   mysqldb.yaml: MySQL database

   apiVersion: mysql.nakamasato.com/v1alpha1
   kind: MySQLDB
   metadata:
     name: sample-db # this is not a name for MySQL database but just a Kubernetes object name
   spec:
     dbName: sample_db # this is MySQL database name
     mysqlName: mysql-sample

   kubectl apply -k https://github.com/nakamasato/mysql-operator/config/samples-on-k8s
   

4. Check MySQLUser and Secret for the MySQL user

   kubectl get mysqluser
   NAME         PHASE   REASON
   sample-user   Ready   Both secret and mysql user are successfully created.
   

   kubectl get secret
   NAME                            TYPE     DATA   AGE
   mysql-mysql-sample-sample-user   Opaque   1      10s
   

5. Connect to MySQL with the secret

   kubectl exec -it $(kubectl get po | grep mysql | head -1 | awk '{print $1}') -- mysql -usample-user -p$(kubectl get secret mysql-mysql-sample-nakamasato -o jsonpath='{.data.password}' | base64 --decode)
   

6. Delete custom resources (MySQL, MySQLUser, MySQLDB). Example:

   kubectl delete -k https://github.com/nakamasato/mysql-operator/config/samples-on-k8s
   

   NOTICE

   custom resources might get stuck if MySQL is deleted before (to be improved). → Remove finalizers to forcifully delete the stuck objects:

   kubectl patch mysqluser <resource_name> -p '{"metadata":{"finalizers": []}}' --type=merge
   

   kubectl patch mysql <resource_name> -p '{"metadata":{"finalizers": []}}' --type=merge
   

   kubectl patch mysqldb <resource_name> -p '{"metadata":{"finalizers": []}}' --type=merge
   

7. (Optional) Delete MySQL

   kubectl delete -k https://github.com/nakamasato/mysql-operator/config/mysql
   

8. Uninstall mysql-operator

   kubectl delete -k https://github.com/nakamasato/mysql-operator/config/install
   

With GCP Secret Manager

Instead of writing raw password in MySQL.Spec.AdminPassword, you can get the password for root user from an external secret manager (e.g. GCP) (ref: Authenticate to Google Cloud using a service account)

1. Create SecretManager

   echo -n "password" | gcloud secrets create mysql-password --data-file=-
   echo -n "root" | gcloud secrets create mysql-user --data-file=-
   

2. Create a Secret for credentials json for service account with roles/secretm anager.secretAccessor permission

   kubectl create secret generic gcp-sa-private-key --from-file=sa-private-key.json
   

3. Install mysql-operator with --set adminUserSecretType=gcp --set gcpProjectId=$PROJECT_ID

   helm repo add nakamasato https://nakamasato.github.io/helm-charts
   helm repo update
   helm install mysql-operator nakamasato/mysql-operator --set adminUserSecretType=gcp --set gcpProjectId=$PROJECT_ID
   

4. You can specify type: gcp for adminUser and adminPassword.

   apiVersion: mysql.nakamasato.com/v1alpha1
   kind: MySQL
   metadata:
     name: mysql-sample
   spec:
     host: mysql.default # need to include namespace if you use Kubernetes Service as an endpoint.
     adminUser:
       name: mysql-user # secret name in SecretManager
       type: gcp
     adminPassword:
       name: mysql-password # secret name in SecretManager
       type: gcp

   Example: (you need to run kubectl apply -k config/mysql)

   kubectl apply -k config/samples-on-k8s-with-gcp-secretmanager
   

Read credentials from GCP SecretManager

With k8s Secret Manager

Instead of writing raw password in MySQL.Spec.AdminPassword, you can get the password for root user from an external secret manager (e.g. K8s)

1. Create Kubernetes Secret.

   kubectl create secret generic mysql-user --from-literal=key=root
   kubectl create secret generic mysql-password --from-literal=key=password
   

2. Install mysql-operator with --set adminUserSecretType=k8s --set adminUserSecretNamespace=default

   helm repo add nakamasato https://nakamasato.github.io/helm-charts
   helm repo update
   helm install mysql-operator nakamasato/mysql-operator --set adminUserSecretType=k8s --set adminUserSecretNamespace=default
   

3. You can specify type: k8s for adminUser and adminPassword.

   apiVersion: mysql.nakamasato.com/v1alpha1
   kind: MySQL
   metadata:
     name: mysql-sample
   spec:
     host: mysql.default # need to include namespace if you use Kubernetes Service as an endpoint.
     adminUser:
       name: mysql-user # secret name in SecretManager
       type: k8s
     adminPassword:
       name: mysql-password # secret name in SecretManager
       type: k8s

   Example: (you need to run kubectl apply -k config/mysql)

   kubectl apply -k config/samples-on-k8s-with-k8s-secret
   

Exposed Metrics

• mysql_user_created_total
• mysql_user_deleted_total

Contributing

CONTRIBUTING