Can't connect to mysql pod in Kubernetes when using Secrets for password (Access denied)

Question

I try to setup a mysql database in Kubernetes. I configured a ConfigMap to store the Database name and a Secret that contains the root password, the user and the password for the user.

When I try to connect to the DB afterwards (Inside the container with mysql cli and from outside with IntelliJ Database tool) I get an "ERROR 1045 (28000): Access denied for user 'testadm'@'localhost' (using password: YES)" error.

My kubernetes.yaml file:

apiVersion: v1
kind: ConfigMap
metadata:
  name: db
data:
  mysql-database: database

---
apiVersion: v1
kind: Secret
metadata:
   name: db-credentials
type: Opaque
data:
  mysql-root-password: VGVzdDEyMzQK # Test1234
  mysql-user: dGVzdGFkbQo= # testadm
  mysql-password: VGVzdDEyMzQK # Test1234

---
apiVersion: apps/v1beta1
kind: Deployment
metadata:
  name: mysql
spec:
  replicas: 1
  strategy:
    type: Recreate
  template:
    metadata:
      labels:
        app: mysql
    spec:
      containers:
        - name: mysql
          image: mysql:5.7
          ports:
            - containerPort: 3306
          env:
            - name: MYSQL_DATABASE
              valueFrom:
                configMapKeyRef:
                  name: db
                  key: mysql-database
            - name: MYSQL_ROOT_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: db-credentials
                  key: mysql-root-password
            - name: MYSQL_USER
              valueFrom:
                secretKeyRef:
                  name: db-credentials
                  key: mysql-user
            - name: MYSQL_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: db-credentials
                  key: mysql-password

If I set the passwords directly like below the connection succeeds inside of the container and from the outside!

env:
  - name: MYSQL_ROOT_PASSWORD
    value: Test1234

If I inspect the env variables inside the container I can't spot a difference between the two approaches.

Is there any additional formatting required to use the passwords stored in the secret? I also tried to place the values in the data-dictionary in quotes like this:

data:
  mysql-root-password: "VGVzdDEyMzQK"

Version information

Docker 17.06.0-ce
Minikube 0.21.0
Kubectl Server 1.7.0
Kubectl Client 1.7.3

Solution 1

you can use this yaml file.

apiVersion: v1
kind: Secret
metadata:
   name: db-credentials
type: Opaque
data:
  mysql-password: VGVzdDEyMzQ=
  mysql-root-password: VGVzdDEyMzQ=
  mysql-user: dGVzdGFkbQ==

Solution 2

Are you sure the data in your secret yaml is base64-encoded correctly? Using https://www.base64encode.org/, your data block is supposed to look like:

data:
  mysql-root-password: VGVzdDEyMzQ= # Test1234
  mysql-user: dGVzdGFkbQ== # testadm
  mysql-password: VGVzdDEyMzQ= # Test1234

Solution 3

For anyone having an issue not resolved by line breaks issue as was case with OP here, note that you can't change the mysql password once the database is created. The environment variable is only read when the db is created so if you are using a persistent volume claim you need log in with the old password and change it "manually": https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html