CODEWITHSUNDEEP
mysqldockerkubernetes
Steffen Schmitz
Steffen Schmitz

Reputation: 890

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

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

Upvotes: 5

Views: 17351

Answers (5)

Jared Laethem
Jared Laethem

Reputation: 21

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

Upvotes: 2

Gajen Sunthara
Gajen Sunthara

Reputation: 4818

Following worked me by having the db password as stringData.

enter image description here

Secret:

apiVersion: v1
kind: Secret
metadata:
  name: db-secret
type: Opaque
data:
  db: bG8ryXYx1cw==
  db_username: cm9vdA==

stringData:
  app_port: '3000'
  db_host: 'db-sql.default.svc.cluster.local'
  db_port: '3306'
  db_password: ‘<redacted>!'

In your Deployment yaml

 - name: DB_PASSWORD
            valueFrom:
              secretKeyRef:
                name: lokalus-server-secret
                key: db_password

Upvotes: -1

ivan.sim
ivan.sim

Reputation: 9268

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

Upvotes: 3

sfgroups
sfgroups

Reputation: 19099

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==

Upvotes: 1

sfgroups
sfgroups

Reputation: 19099

You need to give the access to client machine to connect mysql database.

replace the <ip> address with your desktop ip and run this command on mysql database. then test the connection.

GRANT ALL PRIVILEGES ON *.* TO 'root'@'<ip>'   WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'   WITH GRANT OPTION;
FLUSH PRIVILEGES;

The way you created secret is not correct. remove and create it like this. I tested in my cluster it worked.

kubectl create secret generic db-credentials --from-literal=mysql-root-password=Test1234 --from-literal=mysql-user=testadm --from-literal=mysql-password=Test1234

Upvotes: 1

Related Questions