sql-server - 带有 PersistentVolumeClaim 的 SQL Server pod

Question

这是场景:带有 minikube 的 SQL Server linux kubernetes 设置。

它在默认设置下运行良好，创建数据库/表没有问题。

但数据库文件不应存储在容器中，因此添加了 PersistentVolumeClaim，并且 pod 配置更改为使用声明并将/var/opt/mssql 挂载到 minikube VM 上的/sqldata。

apiVersion: v1
kind: PersistentVolume
metadata:
  name: sqldata
spec:
  capacity:
    storage: 1Gi
  storageClassName: sqlserver
  accessModes:
    - ReadWriteMany
  hostPath:
    path: "/sqldata"
---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: dbclaim
spec:
  accessModes:
    - ReadWriteMany
  storageClassName: sqlserver
  resources:
    requests:
      storage: 1Gi


apiVersion: v1
kind: Pod
spec:
  initContainers:
  - name: volume-permissions
    image: busybox
    command: ["sh", "-c", "chown -R 10001:0 /var/opt/mssql"]
    volumeMounts:
    - mountPath: "/var/opt/mssql"
      name: sqldata-storage
  volumes:
  - name: sqldata-storage
    persistentVolumeClaim:
      claimName: dbclaim

  containers:
  - image: mcr.microsoft.com/mssql/server
    name: foo
env:
- name: ACCEPT_EULA
  value: "Y"
- name: SA_PASSWORD
  valueFrom:
    secretKeyRef:
      name: sql-password
      key: sa_password
  - name: MSSQL_PID
    value: Developer
    volumeMounts:
    - mountPath: "/var/opt/mssql/data"
      name: sqldata-storage

也试过图片:microsoft/mssql-server-linux

chown -R 10001:0 /var/opt/mssql

在 initcontainer 中调用以授予 mssql 用户访问主机 VM 目录的权限。

但现在发生的情况是 sql server pod 启动并在一两分钟后停止并出现 CrashloopBackoff。

pod 的日志文件说:

2020-08-02 14:33:57.55 Server Registry startup parameters:-d /var/opt/mssql/data/master.mdf-l /var/opt/mssql/data/mastlog.ldf-e /var/opt/mssql/log/errorlog 2020-08-02 14:33:57.78 Server Error 87(The parameter is incorrect.) occurred while opening file'/var/opt/mssql/data/master.mdf' to obtain configuration informationat startup. An invalid startup option might have caused the error.Verify your startup options, and correct or remove them if necessary

登录到 minikube 虚拟机，看起来 sql server 确实具有访问权限，因为主表等是在实际安装的目录中创建的，尽管只设置了所有者权限 10001:

$ ls -l /sqldata
-rw-r----- 1 10001 root  4194304 Aug  9 06:51 master.mdf

要让它像这样运行需要检查什么？

Answer 1

我已经设法运行了它。我从您的规范中唯一更改的是从持久卷和 PersistentVolumeClaim 中删除 storageclassName。这是因为我没有创建存储类，所以不指定存储类将使用默认存储类。

这是我运行的 yaml。

#pv.yaml
apiVersion: v1
kind: PersistentVolume
metadata:
  name: sqldata
spec:
  capacity:
    storage: 1Gi
  accessModes:
    - ReadWriteMany
  hostPath:
    path: "/sqldata"

#pvc.yaml
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: dbclaim
spec:
  accessModes:
    - ReadWriteMany
  resources:
    requests:
      storage: 1Gi

#sqlserver.yaml
apiVersion: v1
kind: Pod
metadata: 
  name: sqlserver
spec:
  initContainers:
  - name: volume-permissions
    image: busybox
    command: ["sh", "-c", "chown -R 10001:0 /var/opt/mssql"]
    volumeMounts:
    - mountPath: "/var/opt/mssql"
      name: sqldata-storage
  volumes:
  - name: sqldata-storage
    persistentVolumeClaim:
      claimName: dbclaim

  containers:
  - image: mcr.microsoft.com/mssql/server
    name: foo
    volumeMounts:
    - mountPath: "/var/opt/mssql/data"
      name: sqldata-storage
    env:
    - name: ACCEPT_EULA
      value: "Y"
    - name: SA_PASSWORD
      valueFrom:
        secretKeyRef:
          name: sql-password
          key: sa_password
    - name: MSSQL_PID
      value: Developer

这就是我创建 secret 的方式

kubectl create secret generic sql-password --from-literal=sa_password=Passw0rd

这里是描述 pod 的输出。

vagrant@kubemaster:~$ kubectl describe pod sqlserver
Name:         sqlserver
Namespace:    default
Priority:     0
Node:         kubenode02/192.168.56.4
Start Time:   Thu, 13 Aug 2020 20:10:06 +0000
Labels:       <none>
Annotations:  <none>
Status:       Running
IP:           10.36.0.2
IPs:
  IP:  10.36.0.2
Init Containers:
  volume-permissions:
    Container ID:  docker://dbc81ddda15aa5af4b56085ee1923b530f1154ba147c589dcc76fb80121c2d0a
    Image:         busybox
    Image ID:      docker-pullable://busybox@sha256:4f47c01fa91355af2865ac10fef5bf6ec9c7f42ad2321377c21e844427972977
    Port:          <none>
    Host Port:     <none>
    Command:
      sh
      -c
      chown -R 10001:0 /var/opt/mssql
    State:          Terminated
      Reason:       Completed
      Exit Code:    0
      Started:      Thu, 13 Aug 2020 20:10:11 +0000
      Finished:     Thu, 13 Aug 2020 20:10:11 +0000
    Ready:          True
    Restart Count:  0
    Environment:    <none>
    Mounts:
      /var/opt/mssql from sqldata-storage (rw)
      /var/run/secrets/kubernetes.io/serviceaccount from default-token-w9t6t (ro)
Containers:
  foo:
    Container ID:   docker://f43e9321d85daa1b5695dc2944f42a4e12db34b97ba0f333d8a8b9afeace0f31
    Image:          mcr.microsoft.com/mssql/server
    Image ID:       docker-pullable://mcr.microsoft.com/mssql/server@sha256:1a69a5e5f7b00feae9edab6bd72e2f6fd5bbb4e74e4ca46e3cc46f1b911e1305
    Port:           <none>
    Host Port:      <none>
    State:          Running
      Started:      Thu, 13 Aug 2020 20:10:14 +0000
    Ready:          True
    Restart Count:  0
    Environment:
      ACCEPT_EULA:  Y
      SA_PASSWORD:  <set to the key 'sa_password' in secret 'sql-password'>  Optional: false
      MSSQL_PID:    Developer
    Mounts:
      /var/opt/mssql/data from sqldata-storage (rw)
      /var/run/secrets/kubernetes.io/serviceaccount from default-token-w9t6t (ro)
Conditions:
  Type              Status
  Initialized       True 
  Ready             True 
  ContainersReady   True 
  PodScheduled      True 
Volumes:
  sqldata-storage:
    Type:       PersistentVolumeClaim (a reference to a PersistentVolumeClaim in the same namespace)
    ClaimName:  dbclaim
    ReadOnly:   false
  default-token-w9t6t:
    Type:        Secret (a volume populated by a Secret)
    SecretName:  default-token-w9t6t
    Optional:    false
QoS Class:       BestEffort
Node-Selectors:  <none>
Tolerations:     node.kubernetes.io/not-ready:NoExecute for 300s
                 node.kubernetes.io/unreachable:NoExecute for 300s
Events:
  Type    Reason     Age        From                 Message
  ----    ------     ----       ----                 -------
  Normal  Scheduled  <unknown>  default-scheduler    Successfully assigned default/sqlserver to kubenode02
  Normal  Pulling    84s        kubelet, kubenode02  Pulling image "busybox"
  Normal  Pulled     80s        kubelet, kubenode02  Successfully pulled image "busybox"
  Normal  Created    80s        kubelet, kubenode02  Created container volume-permissions
  Normal  Started    80s        kubelet, kubenode02  Started container volume-permissions
  Normal  Pulling    79s        kubelet, kubenode02  Pulling image "mcr.microsoft.com/mssql/server"
  Normal  Pulled     78s        kubelet, kubenode02  Successfully pulled image "mcr.microsoft.com/mssql/server"
  Normal  Created    78s        kubelet, kubenode02  Created container foo
  Normal  Started    77s        kubelet, kubenode02  Started container foo
vagrant@kubemaster:~$ 

这是来自 pod 的日志。

vagrant@kubemaster:~$ kubectl logs sqlserver
SQL Server 2019 will run as non-root by default.
This container is running as user mssql.
Your master database file is owned by mssql.
To learn more visit https://go.microsoft.com/fwlink/?linkid=2099216.
2020-08-13 20:10:17.89 Server      Setup step is FORCE copying system data file 'C:\templatedata\model_replicatedmaster.mdf' to '/var/opt/mssql/data/model_replicatedmaster.mdf'.
2020-08-13 20:10:17.96 Server      Setup step is FORCE copying system data file 'C:\templatedata\model_replicatedmaster.ldf' to '/var/opt/mssql/data/model_replicatedmaster.ldf'.
2020-08-13 20:10:17.96 Server      Setup step is FORCE copying system data file 'C:\templatedata\model_msdbdata.mdf' to '/var/opt/mssql/data/model_msdbdata.mdf'.
2020-08-13 20:10:17.97 Server      Setup step is FORCE copying system data file 'C:\templatedata\model_msdblog.ldf' to '/var/opt/mssql/data/model_msdblog.ldf'.
2020-08-13 20:10:18.06 Server      Microsoft SQL Server 2019 (RTM-CU6) (KB4563110) - 15.0.4053.23 (X64) 
    Jul 25 2020 11:26:55 
    Copyright (C) 2019 Microsoft Corporation
    Developer Edition (64-bit) on Linux (Ubuntu 18.04.4 LTS) <X64>
2020-08-13 20:10:18.07 Server      UTC adjustment: 0:00
2020-08-13 20:10:18.07 Server      (c) Microsoft Corporation.
2020-08-13 20:10:18.07 Server      All rights reserved.
2020-08-13 20:10:18.07 Server      Server process ID is 36.
2020-08-13 20:10:18.07 Server      Logging SQL Server messages in file '/var/opt/mssql/log/errorlog'.
2020-08-13 20:10:18.07 Server      Registry startup parameters: 
     -d /var/opt/mssql/data/master.mdf
     -l /var/opt/mssql/data/mastlog.ldf
     -e /var/opt/mssql/log/errorlog
2020-08-13 20:10:18.08 Server      SQL Server detected 1 sockets with 2 cores per socket and 2 logical processors per socket, 2 total logical processors; using 2 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
2020-08-13 20:10:18.09 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2020-08-13 20:10:18.09 Server      Detected 1594 MB of RAM. This is an informational message; no user action is required.
2020-08-13 20:10:18.09 Server      Using conventional memory in the memory manager.
2020-08-13 20:10:18.09 Server      Page exclusion bitmap is enabled.
2020-08-13 20:10:18.12 Server      Buffer pool extension is not supported on Linux platform.
2020-08-13 20:10:18.12 Server      Buffer Pool: Allocating 262144 bytes for 180348 hashPages.
2020-08-13 20:10:18.34 Server      Buffer pool extension is already disabled. No action is necessary.
2020-08-13 20:10:18.90 Server      Successfully initialized the TLS configuration. Allowed TLS protocol versions are ['1.0 1.1 1.2']. Allowed TLS ciphers are ['ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES128-SHA256:ECDHE-ECDSA-AES256-SHA384:ECDHE-ECDSA-AES256-SHA:ECDHE-ECDSA-AES128-SHA:AES256-GCM-SHA384:AES128-GCM-SHA256:AES256-SHA256:AES128-SHA256:AES256-SHA:AES128-SHA:!DHE-RSA-AES256-GCM-SHA384:!DHE-RSA-AES128-GCM-SHA256:!DHE-RSA-AES256-SHA:!DHE-RSA-AES128-SHA'].
2020-08-13 20:10:18.94 Server      Query Store settings initialized with enabled = 1, 
2020-08-13 20:10:18.96 Server      The maximum number of dedicated administrator connections for this instance is '1'
2020-08-13 20:10:18.97 Server      Node configuration: node 0: CPU mask: 0x0000000000000003:0 Active CPU mask: 0x0000000000000003:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2020-08-13 20:10:18.98 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
2020-08-13 20:10:19.01 Server      In-Memory OLTP initialized on lowend machine.
2020-08-13 20:10:19.05 Server      [INFO] Created Extended Events session 'hkenginexesession'
2020-08-13 20:10:19.06 Server      Database Instant File Initialization: enabled. For security and performance considerations see the topic 'Database Instant File Initialization' in SQL Server Books Online. This is an informational message only. No user action is required.
ForceFlush is enabled for this instance. 
2020-08-13 20:10:19.09 Server      Total Log Writer threads: 1. This is an informational message; no user action is required.
2020-08-13 20:10:19.12 Server      clflushopt is selected for pmem flush operation.
2020-08-13 20:10:19.14 Server      Software Usage Metrics is disabled.
2020-08-13 20:10:19.16 Server      CLR version v4.0.30319 loaded.
2020-08-13 20:10:19.18 spid8s      [1]. Feature Status: PVS: 0. CTR: 0. ConcurrentPFSUpdate: 1.
2020-08-13 20:10:19.18 spid8s      Starting up database 'master'.
ForceFlush feature is enabled for log durability.
2020-08-13 20:10:19.61 Server      Common language runtime (CLR) functionality initialized.
2020-08-13 20:10:19.76 spid8s      Service Master Key could not be decrypted using one of its encryptions. See sys.key_encryptions for details.
2020-08-13 20:10:19.77 spid8s      An error occurred during Service Master Key initialization. SQLErrorCode=33095, State=8, LastOsError=0.
2020-08-13 20:10:19.91 spid8s      Resource governor reconfiguration succeeded.
2020-08-13 20:10:19.91 spid8s      SQL Server Audit is starting the audits. This is an informational message. No user action is required.
2020-08-13 20:10:19.92 spid8s      SQL Server Audit has started the audits. This is an informational message. No user action is required.
2020-08-13 20:10:20.00 spid8s      SQL Trace ID 1 was started by login "sa".
2020-08-13 20:10:20.03 spid8s      Server name is 'sqlserver'. This is an informational message only. No user action is required.
2020-08-13 20:10:20.07 spid23s     Always On: The availability replica manager is starting. This is an informational message only. No user action is required.
2020-08-13 20:10:20.08 spid23s     Always On: The availability replica manager is waiting for the instance of SQL Server to allow client connections. This is an informational message only. No user action is required.
2020-08-13 20:10:20.08 spid8s      [4]. Feature Status: PVS: 0. CTR: 0. ConcurrentPFSUpdate: 1.
2020-08-13 20:10:20.11 spid10s     [32767]. Feature Status: PVS: 0. CTR: 0. ConcurrentPFSUpdate: 1.
2020-08-13 20:10:20.12 spid8s      Starting up database 'msdb'.
2020-08-13 20:10:20.13 spid10s     Starting up database 'mssqlsystemresource'.
2020-08-13 20:10:20.14 spid10s     The resource database build version is 15.00.4053. This is an informational message only. No user action is required.
2020-08-13 20:10:20.19 spid22s     A self-generated certificate was successfully loaded for encryption.
2020-08-13 20:10:20.21 spid22s     Server is listening on [ 0.0.0.0 <ipv4> 1433].
2020-08-13 20:10:20.22 Server      Server is listening on [ ::1 <ipv6> 1434].
2020-08-13 20:10:20.22 Server      Server is listening on [ 127.0.0.1 <ipv4> 1434].
2020-08-13 20:10:20.23 Server      Dedicated admin connection support was established for listening locally on port 1434.
2020-08-13 20:10:20.25 spid22s     Server is listening on [ ::1 <ipv6> 1431].
2020-08-13 20:10:20.25 spid10s     [3]. Feature Status: PVS: 0. CTR: 0. ConcurrentPFSUpdate: 1.
2020-08-13 20:10:20.26 spid22s     Server is listening on [ 127.0.0.1 <ipv4> 1431].
2020-08-13 20:10:20.26 spid10s     Starting up database 'model'.
2020-08-13 20:10:20.28 spid22s     SQL Server is now ready for client connections. This is an informational message; no user action is required.
2020-08-13 20:10:20.57 spid10s     Clearing tempdb database.
2020-08-13 20:10:20.94 spid10s     [2]. Feature Status: PVS: 0. CTR: 0. ConcurrentPFSUpdate: 1.
2020-08-13 20:10:20.95 spid10s     Starting up database 'tempdb'.
2020-08-13 20:10:21.21 spid10s     The tempdb database has 1 data file(s).
2020-08-13 20:10:21.22 spid23s     The Service Broker endpoint is in disabled or stopped state.
2020-08-13 20:10:21.23 spid23s     The Database Mirroring endpoint is in disabled or stopped state.
2020-08-13 20:10:21.24 spid8s      Recovery is complete. This is an informational message only. No user action is required.
2020-08-13 20:10:21.26 spid23s     Service Broker manager has started.
vagrant@kubemaster:~$ 

这是我通过在挂载路径/var/opt/mysql/data 中创建测试文件“test file”并删除 pod 并重新创建它来检查持久卷是否有效的方法。您仍然可以在同一路径中找到我创建的测试文件。

vagrant@kubemaster:~$ kubectl exec -ti sqlserver -- /bin/bash
mssql@sqlserver:/$ 
mssql@sqlserver:/$ cd /var/opt/mssql/data/
mssql@sqlserver:/var/opt/mssql/data$ ls -lrt
total 72068
-rw-r----- 1 mssql root      256 Aug 13 19:28 Entropy.bin
-rw-r----- 1 mssql root 14090240 Aug 13 20:06 msdbdata.mdf
-rw-r----- 1 mssql root  4194304 Aug 13 20:10 model_replicatedmaster.mdf
-rw-r----- 1 mssql root   524288 Aug 13 20:10 model_replicatedmaster.ldf
-rw-r----- 1 mssql root 14090240 Aug 13 20:10 model_msdbdata.mdf
-rw-r----- 1 mssql root   524288 Aug 13 20:10 model_msdblog.ldf
-rw-r----- 1 mssql root  4194304 Aug 13 20:10 master.mdf
-rw-r----- 1 mssql root   524288 Aug 13 20:10 msdblog.ldf
-rw-r----- 1 mssql root  8388608 Aug 13 20:10 modellog.ldf
-rw-r----- 1 mssql root  8388608 Aug 13 20:10 model.mdf
-rw-r----- 1 mssql root  8388608 Aug 13 20:10 templog.ldf
-rw-r----- 1 mssql root  8388608 Aug 13 20:10 tempdb.mdf
-rw-r----- 1 mssql root  2097152 Aug 13 20:10 mastlog.ldf
mssql@sqlserver:/var/opt/mssql/data$ 
mssql@sqlserver:/var/opt/mssql/data$ touch testfile
mssql@sqlserver:/var/opt/mssql/data$ exit
exit
vagrant@kubemaster:~$ kubectl delete pod sqlserver
pod "sqlserver" deleted
vagrant@kubemaster:~$ kubectl create -f sqlserver.yaml 
pod/sqlserver created
vagrant@kubemaster:~$ 
vagrant@kubemaster:~$ kubectl exec -ti sqlserver -- /bin/bash
mssql@sqlserver:/$ 
mssql@sqlserver:/$ ls -lrt /var/opt/mssql/data/
total 72068
-rw-r----- 1 mssql root      256 Aug 13 19:28 Entropy.bin
-rw-r--r-- 1 mssql root        0 Aug 13 20:17 testfile
-rw-r----- 1 mssql root 14090240 Aug 13 20:17 msdbdata.mdf
-rw-r----- 1 mssql root  4194304 Aug 13 20:18 model_replicatedmaster.mdf
-rw-r----- 1 mssql root   524288 Aug 13 20:18 model_replicatedmaster.ldf
-rw-r----- 1 mssql root 14090240 Aug 13 20:18 model_msdbdata.mdf
-rw-r----- 1 mssql root   524288 Aug 13 20:18 model_msdblog.ldf
-rw-r----- 1 mssql root  4194304 Aug 13 20:18 master.mdf
-rw-r----- 1 mssql root   524288 Aug 13 20:18 msdblog.ldf
-rw-r----- 1 mssql root  8388608 Aug 13 20:18 modellog.ldf
-rw-r----- 1 mssql root  8388608 Aug 13 20:18 model.mdf
-rw-r----- 1 mssql root  8388608 Aug 13 20:18 templog.ldf
-rw-r----- 1 mssql root  8388608 Aug 13 20:18 tempdb.mdf
-rw-r----- 1 mssql root  2097152 Aug 13 20:18 mastlog.ldf
mssql@sqlserver:/$ 
mssql@sqlserver:/$ exit
exit
vagrant@kubemaster:~$