k8s deployment - access denied for user 'root'

[jackchuong]

Hi all,
I have an internal k8s cluster , metalLB , ingress nginx , everything working fine, here my yaml

---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: thankyou-mysql-pvc
  namespace: thankyou
spec:
  storageClassName: nfs-idcnas002
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 5Gi
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: utilities
  namespace: thankyou
spec:
  selector:
    matchLabels:
      app: utilities
  template:
    metadata:
      labels:
        app: utilities
    spec:
      containers:
      - image: mysql:8.0-debian
        name: thankyoumysql
        env:
        - name: TZ
          value: "Asia/Ho_Chi_Minh"
        - name: MYSQL_ROOT_PASSWORD
          value: "******"
        volumeMounts:
        - name: thankyou-mysql-pvc
          mountPath: /var/lib/mysql
      volumes:
      - name: thankyou-mysql-pvc
        persistentVolumeClaim:
          claimName: thankyou-mysql-pvc
---
apiVersion: v1
kind: Service
metadata:
  name: thankyou-mysql-svc
  namespace: thankyou
spec:
  selector:
    app: utilities
  ports:
  - name: postgres
    port: 3306
    protocol: TCP
    targetPort: 3306
---

kubectl -n thankyou get pod,svc -o wide
NAME                                  READY   STATUS    RESTARTS   AGE     IP            NODE             NOMINATED NODE   READINESS GATES
pod/utilities-958454b5b-dwcs9         1/1     Running   0          74m     10.42.1.156   k3s-dc-worker1   <none>           <none>

NAME                          TYPE        CLUSTER-IP     EXTERNAL-IP   PORT(S)    AGE     SELECTOR
service/thankyou-mysql-svc    ClusterIP   10.43.37.158   <none>        3306/TCP   74m     app=utilities

kubectl -n thankyou exec -it pod/utilities-958454b5b-dwcs9 -- bash
root@utilities-958454b5b-dwcs9:/# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.36 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

kubectl -n thankyou logs -f pod/utilities-958454b5b-dwcs9
2024-03-01 19:04:07+07:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.36-1debian12 started.
2024-03-01 19:04:07+07:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2024-03-01 19:04:07+07:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.36-1debian12 started.
2024-03-01 19:04:08+07:00 [Note] [Entrypoint]: Initializing database files
2024-03-01T12:04:08.348107Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.36) initializing of server in progress as process 81
2024-03-01T12:04:08.359785Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-03-01T12:04:10.049926Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-03-01T12:04:13.722052Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2024-03-01 19:04:17+07:00 [Note] [Entrypoint]: Database files initialized
2024-03-01 19:04:17+07:00 [Note] [Entrypoint]: Starting temporary server
2024-03-01T12:04:18.487793Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.36) starting as process 125
2024-03-01T12:04:18.540505Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-03-01T12:04:19.459316Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-03-01T12:04:20.681618Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-03-01T12:04:20.681674Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-03-01T12:04:20.721543Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
2024-03-01T12:04:20.783147Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: /var/run/mysqld/mysqlx.sock
2024-03-01T12:04:20.783280Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.36'  socket: '/var/run/mysqld/mysqld.sock'  port: 0  MySQL Community Server - GPL.
2024-03-01 19:04:20+07:00 [Note] [Entrypoint]: Temporary server started.
Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/leapseconds' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/tzdata.zi' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it.

2024-03-01 19:04:24+07:00 [Note] [Entrypoint]: Stopping temporary server
2024-03-01T12:04:24.270689Z 10 [System] [MY-013172] [Server] Received SHUTDOWN from user root. Shutting down mysqld (Version: 8.0.36).
2024-03-01T12:04:25.713693Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.36)  MySQL Community Server - GPL.
2024-03-01 19:04:26+07:00 [Note] [Entrypoint]: Temporary server stopped

2024-03-01 19:04:26+07:00 [Note] [Entrypoint]: MySQL init process done. Ready for start up.

2024-03-01T12:04:26.742641Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.36) starting as process 1
2024-03-01T12:04:26.761106Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-03-01T12:04:27.761636Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-03-01T12:04:28.795771Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-03-01T12:04:28.795824Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-03-01T12:04:28.814090Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
2024-03-01T12:04:28.858969Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2024-03-01T12:04:28.859125Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.36'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server - GPL.

I NAT service/thankyou-mysql-svc out to k8s cluster using ingress nginx , from a computer outside k8s cluster I can telnet ingress nginx IPaddress port 3306 successfully
When I use mysql client like sqlyog community 64 (on computer outside k8s cluster) connect to ingress nginx IPaddress port 3306 with username root and password I get error

error no 1045
access denied for user 'root'@'10.42.2.229' (using password: YES)

Below docker-compose.yml works just fine.

version: '3.1'

services:
  thankyou-mysql-svc:
    image: mysql:8.0-debian
    container_name: mysqlthankyou
    environment:
    - TZ=Asia/Ho_Chi_Minh
    - MYSQL_ROOT_PASSWORD=******
    ports:
      - 3306:3306
    security_opt:
    - seccomp:unconfined
    volumes:
    - ./mysqldata:/var/lib/mysql
    restart: always
    networks:
      - thankyounet

networks:
  thankyounet:

Please give me some advice, thank you very much.

[jackchuong]

My bad, I NAT service/thankyou-mysql-svc to k8s cluster using ingress nginx wrongly , I fixed issue. But I found some useful tips to enable mysql_native_password plugin , hope it helps

---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: thankyou-mysql-pvc
  namespace: thankyou
spec:
  storageClassName: nfs-idcnas002
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 5Gi
---
# config-map.yaml
apiVersion: v1
kind: ConfigMap
metadata:
  ## name of ConfigMap. This will be referred from volume definition
  name: mysql-config
  namespace: thankyou
  labels:
    app: mysql
data:
  ## default_auth is the name of config. This will be referred from volume mount definition
  default_auth: |
    [mysqld]
    default_authentication_plugin=mysql_native_password
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: utilities
  namespace: thankyou
spec:
  selector:
    matchLabels:
      app: utilities
  template:
    metadata:
      labels:
        app: utilities
    spec:
      containers:
      - image: mysql:8.0-debian
        name: thankyoumysql
        env:
        - name: TZ
          value: "Asia/Ho_Chi_Minh"
        - name: MYSQL_ROOT_PASSWORD
          value: "******"
        volumeMounts:
        - name: thankyou-mysql-pvc
          mountPath: /var/lib/mysql
        - name: mysql-config-volume ## specify volume name
          mountPath: /etc/mysql/conf.d/default_auth.cnf ## path to mount file
          subPath: default_auth ## name of config
      volumes:
      - name: thankyou-mysql-pvc
        persistentVolumeClaim:
          claimName: thankyou-mysql-pvc
      - name: mysql-config-volume ## volume name
        configMap:
          name: mysql-config ## name of ConfigMap
---
apiVersion: v1
kind: Service
metadata:
  name: thankyou-mysql-svc
  namespace: thankyou
spec:
  selector:
    app: utilities
  ports:
  - name: postgres
    port: 3306
    protocol: TCP
    targetPort: 3306
---