When access single storage from multiple pods of MySQL give error

[Sivakajan Sivaparan]

Actually what happen is, I need a MySQL deployment which accesses the same storage as a datastore. When creating a MySQL with more than one replica, only one replica becomes healthy. This is because, at a time only one pod can access the """ibdata1""" file. When check the logs of the pod it says, "Unable to lock ./ibdata1 error: 11" Give me a alternative way to access the same storage through two pods.

Recreate the issue: Create PV and PVC

apiVersion: v1
kind: PersistentVolume
metadata:
name: mysql-pv
labels:
type: local
spec:
persistentVolumeReclaimPolicy: Retain
capacity:
storage: 1Gi
accessModes:
- ReadWriteOnce
hostPath:
path: "/mnt/data"
---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
name: mysql-pvc
spec:
accessModes:
- ReadWriteOnce
resources:
requests:
storage: 1Gi
Create the mysql deployment

apiVersion: apps/v1
kind: Deployment
metadata:
name: mysql
spec:
replicas: 2
selector:
matchLabels:
app: mysql
template:
metadata:
labels:
app: mysql
spec:
containers:
- image: mysql:latest
name: mysql
env:
- name: MYSQL_ROOT_PASSWORD
value: pwd
ports:
- containerPort: 3306
volumeMounts:
- name: mysql-storage
mountPath: /var/lib/mysql
volumes:
- name: mysql-storage
persistentVolumeClaim:
claimName: mysql-pvc
After some seconds, you can see both pods running successfully.(k get pods)
Then open the bash of from the first pod. kubectl exec --stdin --tty mysql-pod-name-0 -- /bin/bash. Then open the mysql in that bash. mysql -u root -ppwd. Now you can access the MySQL. Play with it and create a db for reference.

Then exit from the pod-0 and open the bash of the second pod by kubectl exec --stdin --tty mysql-pod-name-1 -- /bin/bash. You can access the bash of pod-1. Now open the MySQL there. mysql -u root -ppwd. You will get an error like

bash-4.4# mysql -u root -ppwd
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2
In other way, try delete the pod-0(the workable pod).(kubectl delete pod pod_name_0). Then you can see another new pod(named pod-2) created. Now do the same procedure to access the mysql from both pods, you can observe that accessing the mysql from pod-1 will works with the already created db. and the pod-2 will expose the same error we observed in the pod-1 early.