Connecting to MySQL 5.6 inside Docker For Desktop/Kubernetes: ERROR 1130 (HY000): Host 'xx.xx.xx.xx' is not allowed to connect to this MySQL server

I'm following theses instructions (page 181) to create a persistent volume & claim, a mysql replica set & service. I specify mysql v5.6 in the yaml file for the replica set.

After viewing the log for the pod, it looks like it is successful. So then I

kubectl run -it --rm --image=mysql --restart=Never mysql-client -- bash
mysql -h mysql -p 3306 -u root

It prompts me for the password and then I get this error:

ERROR 1130 (HY000): Host '10.1.0.17' is not allowed to connect to this MySQL server

Apparently MySQL has a feature where it does not allow remote connections by default and I have to change the configuration files and I don't know how to do that inside a yaml file. Below is my YAML. How do I change it to allow remote connections?

Thanks

Siegfried

cat <<END-OF-FILE | kubectl apply -f -
apiVersion: apps/v1
kind: ReplicaSet
metadata:
  name: mysql
  # labels so that we can bind a Service to this Pod
  labels:
    app: mysql
spec:
  replicas: 1
  selector:
    matchLabels:
      app: mysql
  template:
    metadata:
      labels:
        app: mysql
    spec:
      containers:
      - name: tododata
        image: mysql:5.6
        resources:
          requests:
            cpu: 1
            memory: 2Gi
        env:
        # Environment variables are not a best practice for security,
        # but we're using them here for brevity in the example.
        # See Chapter 11 for better options.
        - name: MYSQL_ROOT_PASSWORD
          value: some-password-here
        livenessProbe:
          tcpSocket:
            port: 3306
        ports:
        - containerPort: 3306
        volumeMounts:
          - name: tododata
            # /var/lib/mysql is where MySQL stores its databases
            mountPath: "/var/lib/mysql"
      volumes:
      - name: tododata
        persistentVolumeClaim:
          claimName: tododata
END-OF-FILE

Sat Oct 24 2020 3PM EDT Update: Try Bitnami MySQL

I like Ben's idea of using bitnami mysql because then I don't have to create my own custom docker image. However, when using bitnami and trying to connect to they mysql server I get

ERROR 2003 (HY000): Can't connect to MySQL server on 'my-release-mysql.default.svc.cluster.local' (111)

This happens after I successfully get a bash shell with this command:

kubectl run my-release-mysql-client --rm --tty -i --restart='Never' --image  docker.io/bitnami/mysql:8.0.22-debian-10-r0 --namespace default --command -- bash

Then, as per the instructions, I do this and get the HY000 error above.

mysql -h my-release-mysql.default.svc.cluster.local -uroot -p

Wed Nov 04 2020 Update:

Thanks Ben.. Yes -- I had already tried that on Oct 24 (approx) and when I do a k describe pod I get mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/opt/bitnami/mysql/tmp/mysql.sock' (2)' Check that mysqld is running and that the socket: '/opt/bitnami/mysql/tmp/mysql.sock' exists!.

Of course, when I run the mysql client as described in the nicely generated instructions, the client cannot connect because mysqld has died.

This is after having deleted the pvcs and stss and doing helm delete my-release prior to reinstalling via helm.

Unfortunately, when I tried this the first time (a couple of weeks ago) I did not set the root password and used the default generated password and I think it is still trying to use that.

This did work on azure kubernetes after having created a fresh azure kubernetes cluster. How can I reset my kubernetes cluster in my docker for desktop windows? I tried google searching and no luck so far.

Thanks Siegfried

Solution

After a lot of help from the bitnami folks, I learned that my spinning disks on my 4 year old notebook computer are kinda slow (now why this is a problem with Bitnami MySQL and not Bitnami PostreSQL is a mystery).

This works for me:

helm install my-mysql bitnami/mysql \
  --set image.debug=true \
  --set primary.persistence.enabled=false,secondary.persistence.enabled=false \
  --set primary.readinessProbe.enabled=false,primary.livenessProbe.enabled=false \
  --set secondary.readinessProbe.enabled=false,secondary.livenessProbe.enabled=false

This turns off the peristent volumes so the data is lost when the pod dies.

Yes this is useful for me for development purposes and no one should be using Docker For Desktop/Kubernetes for production anyway... I just need to populate a tiny database and test my queries and if I need to repopulate database every time I reboot, well, that is not a big problem.

So maybe I need to get a new notebook computer? The price of notebook computers with 4TB of spinning disk space has gone up in the last couple of years.... And I cannot find any SSD drives of that size so even if I purchased a new replacement with spinning disks I might have the same problem? Hmm....

Thanks everyone for your help!

Siegfried