Access a MySQL storage with two independent instances

Question

I tried to deploy MySQL deployment with Kubernetes, having three replicas which are accessing the same storage(PVC). Here is the configuration

apiVersion: v1
kind: PersistentVolume
metadata:
  name: mysql-pv
  labels:
    type: local
spec:
  persistentVolumeReclaimPolicy: Retain
  capacity:
    storage: 1Gi
  accessModes:
  - ReadWriteMany
  hostPath:
    path: "/mnt/data"
---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: mysql-pvc
spec:
  accessModes:
  - ReadWriteMany
  resources:
    requests:
      storage: 1Gi
---
apiVersion: v1
kind: Service
metadata:
  name: mysql-service
spec:
  type: NodePort
  ports:
  - protocol: TCP
    port: 3307
    targetPort: 3306
    nodePort: 30091
  selector:
    app: mysql
---
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

When you apply this configuration file kubectl apply -f file_name.yaml, you can create three pods, which are accessing the same storage for the databases. When you check the pods' status kubectl get pods, you can see only one pod becomes running and others be in a CrashLoop state. What is happening is, when creating more than one instance to use a common storage, only one instance can acquire the lock of the ibdata1 file. That's why only one pod becomes healthy and others in CrashLoop.( you can see this using kubectl logs pod-name). What I want is,

1. Can I release the lock of the ibdata file and use the storage for all the pods?(this mostly can not, because of consistency issues)
2. If not, how can I create the proposed idea?( accessing a single storage/volume using multiple pod instances)?
3. Would you suggest other ideas to achieve accessing a single storage using multiple pod instances?

Your answers and help are welcomed.

Answer 1

The only way to allow multiple MySQL instances to read the same data is to set all but one in read-only mode.

https://dev.mysql.com/doc/refman/8.0/en/innodb-read-only-instance.html

This mode of operation is appropriate in situations such as:

• Multiple MySQL instances querying the same data directory simultaneously, typically in a data warehousing configuration. You might use this technique to avoid bottlenecks that can occur with a heavily loaded MySQL instance, or you might use different configuration options for the various instances to tune each one for particular kinds of queries.

Read that manual page in full to understand how to configure it and how it works.

But it's not clear from your question what your goal is for doing this.

If you want to allow multiple MySQL instances to have read-write access to the data files, you can't do this. InnoDB manages data partially on storage and partially in buffers in RAM, within the mysqld process. For instance, if you had multiple engines, they would each have their own buffer pool, log buffer, and other memory buffers, and these would not be coordinated. So it's a virtual certainty that your data files would become corrupted very quickly.

If your goal is different, please describe it. Perhaps another solution is possible.

---

Re your comment:

You cannot have more than one MySQL instance running and using the same datadir. When a MySQL instance starts, it requires to lock the InnoDB tablespace file. So you could have one instance start after the other shuts down. But more than one running at the same time using the same datafiles is not going to work (in read-write mode).

It has long been a weakness of Kubernetes to run stateful services such as a database server. Most database administrators I know recommend that you should use Kubernetes only for stateless applications. If those applications need a database, the database should run persistently, not in a Kubernetes environment.

I know some vendors try to support MySQL Server running in Kubernetes, but it is always a difficult task. In my opinion, the difficulties outweigh the advantages.

Answer 2

Copy of my answer from stackoverflow:

You can, but totally shouldn't release the lock, as it will sacrifice the consistency and will most likely result in a broken database (at least at some point).

To implement something like this, you'd need a storage backend with some type of deduplication, which would find that the database files of all three copies are similar and would automatically deduplicate them. ZFS may be able to do this, and in the future you may want to look at simplyblock (disclaimer simplyblock employee), since we're working on this functionality, too.

ZFS may be your best bet right now, but be careful, ZFS has speed / latency implications. While it is the most reliable filesystem on the planet, it wasn't designed for highest throughput or lowest latency, but highest reliability.