How to get or set the clustered database username and password in Jelastic JPS

Question

I am trying to set up a Jelastic clustered database as described in Setting Up Auto-Clusterization with Cloud Scripting but I don't see documentation there that describes how to either set or retrieve the cluster username and password.

I did try passing db_user and db_pass to the cluster, names I found in some of the sample JPS files, as well as having those as settings but the credentials were still just the Jelastic generated ones.

Here is the JPS I am trying to use; it includes a simple Debian container that requires the database credentials as environment variables. In this case the Docker container includes just the MariaDB client for testing purpose, the real environment is bit more complex than that, running scripts in the startup that need the database connection.

{
    "version": "1.5",
    "type": "install",
    "name": "Database test",
    "skipNodeEmails": true,
    "globals":
    {
        "MYSQL_ROOT_USERNAME": "root",
        "MYSQL_ROOT_PASSWORD": "${fn.password(20)}",
        "MYSQL_USERNAME": "username",
        "MYSQL_PASSWORD": "${fn.password(20)}",
        "MYSQL_DATABASE": "database",
        "MYSQL_HOSTNAME": "ProxySQL"
    },
    "nodes":
    [
        {
            "image": "mireiawen/debian-sql",
            "count": 1,
            "cloudlets": 8,
            "nodeGroup": "vds",
            "displayName": "SQL worker",
            "env":
            {
                "MYSQL_ROOT_USERNAME": "${globals.MYSQL_ROOT_USERNAME}",
                "MYSQL_ROOT_PASSWORD": "${globals.MYSQL_ROOT_PASSWORD}",
                "MYSQL_USERNAME": "${globals.MYSQL_USERNAME}",
                "MYSQL_PASSWORD": "${globals.MYSQL_PASSWORD}",
                "MYSQL_DATABASE": "${globals.MYSQL_DATABASE}",
                "MYSQL_HOSTNAME": "${globals.MYSQL_HOSTNAME}"
            }
        },
        {
            "nodeType": "mariadb-dockerized",
            "nodeGroup": "sqldb",
            "count": "2",
            "cloudlets": 16,
            "cluster":
            {
                "scheme": "master"
            }
        }
    ]
}

This JPS seems to launch the MariaDB master-master cluster correctly with the ProxySQL included, I am just lacking on the documentation about how to either provide the database credentials to the database cluster, or a way to retrieve the generated ones to be used as variables in the JPS to send those to the containers.

score 4 · Accepted Answer · answered Jan 04 '19 at 09:59

The mechanism has been improved so now you can pass custom credentials to cluster using either environment variables or cluster settings:

type: install
name: env. variables

nodes: 
  nodeType: mariadb-dockerized
  nodeGroup: sqldb
  count: 2
  cloudlets: 8
  env:
    DB_USER: customuser
    DB_PASS: custompass
  cluster:
    scheme: master

or

type: install
name: cluster settings

nodes: 
  nodeType: mariadb-dockerized
  nodeGroup: sqldb
  count: 2
  cloudlets: 8
  cluster:
    scheme: master
    db_user: customuser
    db_pass: custompass

score 2 · Answer 2 · answered Dec 30 '18 at 11:05

Thank you for the good question. The mechanism of passing custom credentials should be and will be improved soon. At the moment you can use the example below. In short, we disable automated clustering and enable it again with custom username and password.

---
version: 1.5
type: install
name: Database test
skipNodeEmails: true
baseUrl: https://raw.githubusercontent.com/jelastic-jps/mysql-cluster/master
globals:
  logic_jps: ${baseUrl}/addons/auto-clustering/scripts/auto-cluster-logic.jps
  MYSQL_USERNAME: username
  MYSQL_PASSWORD: ${fn.password(20)}

nodes:
- image: mireiawen/debian-sql
  count: 1
  cloudlets: 8
  nodeGroup: extra
  displayName: SQL worker
  env:
    MYSQL_USERNAME: ${globals.MYSQL_USERNAME}
    MYSQL_PASSWORD: ${globals.MYSQL_PASSWORD}

- nodeType: mariadb-dockerized
  nodeGroup: sqldb
  count: 2
  cloudlets: 16
  cluster: false

onInstall: 
  install:
    jps: ${globals.logic_jps}
    envName: ${env.envName}
    nodeGroup: sqldb
    settings:
      path: ${baseUrl}
      scheme: master
      logic_jps: ${globals.logic_jps}
      db_user: ${globals.MYSQL_USERNAME}
      db_pass: ${globals.MYSQL_PASSWORD}
      repl_user: repl-${fn.random}
      repl_pass: ${fn.password(20)}

After environment is ready, you can test the connection by executing the following command in your docker image:

 mysql -h proxy -u $MYSQL_USERNAME -p$MYSQL_PASSWORD

How to get or set the clustered database username and password in Jelastic JPS

2 Answers2