How to manage users and databases

This guide explains how to create and manage users, databases, and access roles for your MySQL instance on Hikube. You will also learn how to switch the primary node in a replicated cluster.

Prerequisites

• kubectl configured with your Hikube kubeconfig
• A MySQL instance deployed on your tenant
• A mysql client to test connections

Steps

1. Add a user

Users are defined in the users section of the manifest. Each user is identified by a name and can have a password and a connection limit:

mysql-users.yaml

apiVersion: apps.cozystack.io/v1alpha1
kind: MariaDB
metadata:
  name: example
spec:
  replicas: 2
  size: 10Gi
  resourcesPreset: small

  users:
    appuser:
      password: SecureAppPassword
      maxUserConnections: 100
    analytics:
      password: SecureAnalyticsPassword
      maxUserConnections: 20
    readonly:
      password: SecureReadOnlyPassword
      maxUserConnections: 10

Parameter	Description	Default
users[name].password	User password	""
users[name].maxUserConnections	Maximum number of simultaneous connections for this user	0 (unlimited)

tip

Limit maxUserConnections per user to prevent a single application from consuming all available server connections.

2. Create a database with roles

Databases are defined in the databases section. Each database can assign admin (read/write) or readonly (read-only) roles to users:

mysql-databases.yaml

apiVersion: apps.cozystack.io/v1alpha1
kind: MariaDB
metadata:
  name: example
spec:
  replicas: 2
  size: 10Gi
  resourcesPreset: small

  users:
    appuser:
      password: SecureAppPassword
      maxUserConnections: 100
    analytics:
      password: SecureAnalyticsPassword
      maxUserConnections: 20
    readonly:
      password: SecureReadOnlyPassword
      maxUserConnections: 10

  databases:
    production:
      roles:
        admin:
          - appuser          # appuser a les droits complets sur "production"
        readonly:
          - readonly         # readonly peut uniquement lire "production"
          - analytics        # analytics peut aussi lire "production"
    analytics_db:
      roles:
        admin:
          - analytics        # analytics a les droits complets sur "analytics_db"
        readonly:
          - readonly         # readonly peut lire "analytics_db"

note

A single user can have different roles on different databases. For example, analytics is admin on analytics_db but readonly on production.

3. Apply the changes

Apply the manifest to create or update users and databases:

kubectl apply -f mysql-databases.yaml

4. Retrieve credentials

Passwords are stored in a Kubernetes Secret named <instance>-credentials:

kubectl get secret example-credentials -o json | jq -r '.data | to_entries[] | "\(.key): \(.value|@base64d)"'

Expected output:

root: cr42msoxKhnEajfo
appuser: SecureAppPassword
analytics: SecureAnalyticsPassword
readonly: SecureReadOnlyPassword

tip

The root password is automatically generated by the operator. Use it only for cluster administration, never in your applications.

5. Test the connection

Via port-forward (internal access)

kubectl port-forward svc/mysql-example 3306:3306

mysql -h 127.0.0.1 -P 3306 -u appuser -p production

Via LoadBalancer (if external: true)

# Récupérer l'IP externe
kubectl get svc mysql-example-primary -o jsonpath='{.status.loadBalancer.ingress[0].ip}'

mysql -h <IP_EXTERNE> -P 3306 -u appuser -p production

Verify the user's permissions:

-- En tant que appuser (admin sur production)
SHOW DATABASES;
CREATE TABLE test (id INT PRIMARY KEY);
INSERT INTO test VALUES (1);

-- En tant que readonly (lecture seule sur production)
SELECT * FROM test;       -- OK
INSERT INTO test VALUES (2);  -- ERREUR : accès refusé

6. Switch the primary node (optional)

In a replicated MySQL cluster, one node is designated as primary (writes) and the others as replicas (reads). You can switch the primary role to another node, for example during maintenance.

Edit the MariaDB resource

kubectl edit mariadb mysql-example

Modify the replication section to designate the new primary:

switchover.yaml

spec:
  replication:
    primary:
      podIndex: 1   # Promouvoir mysql-example-1 en primary

Verify the switchover

kubectl get mariadb

Expected output:

NAME            READY   STATUS    PRIMARY           UPDATES                    AGE
mysql-example   True    Running   mysql-example-1   ReplicasFirstPrimaryLast   84m

warning

Switching the primary may cause a brief write interruption during the promotion of the new node. Reads remain available via the replicas.

Verification

Verify the complete configuration of your instance:

kubectl get mariadb example -o yaml

Make sure that:

• Users are present in the users section
• Databases are listed in the databases section
• Roles are correctly assigned

Going further

• API Reference: complete list of user and database parameters
• How to vertically scale: adjust CPU and memory resources