企业运维 | MySQL关系型数据库在Docker与Kubernetes容器环境中快速搭建部署主从实践

# 准备数据持久化目录
mkdir -vp /app/data

# 准备mysql8.x仓库镜像
docker pull mysql:8.0.30

# 准备root密码不采用环境变量直接显示密码
echo "weiyigeek.top" > /app/my-secret-pw

# 一条命令创建运行mysql数据库容器
docker run -d --name mysql8.0 --restart=always \\
-v "/app/data":/var/lib/mysql \\
-v "/app/my-secret-pw":/app/my-secret-pw \\
-e MYSQL_ROOT_PASSWORD_FILE=/app/my-secret-pw \\
-e MYSQL_DATABASE=app \\
-e MYSQL_USER=weiyigeek \\
-e MYSQL_PASSWORD=password \\
-p 3306:3306 \\
mysql:8.0.30 \\
--default-authentication-plugin=mysql_native_password
# 144e883af1a99901913a986d540382c8aefe3e5bd96730ad76a019b2567159bb

# 可以为 mysqld 使用特定的 UID/GID , 例如此处的 1000 用户。
--user 1000:1000 

# 可以为 mysqld 指定命令行参数。
--character-set-server=utf8mb4 
--collation-server=utf8mb4_unicode_ci

# 容器
$ docker ps
CONTAINER ID   IMAGE          COMMAND                  CREATED              STATUS              PORTS                               NAMES
05c5a0e23e39   mysql:8.0.30   "docker-entrypoint.s…"   About a minute ago   Up About a minute   0.0.0.0:3306->3306/tcp, 33060/tcp   mysql8.0

# 日志
$ docker logs mysql8.0
2022-09-27 14:19:03+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.30-1.el8 started.
2022-09-27 14:19:03+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2022-09-27 14:19:03+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.30-1.el8 started.
2022-09-27 14:19:03+00:00 [Note] [Entrypoint]: Initializing database files

# 连接测试
$ docker exec -it mysql8.0 sh -c 'mysql -u root -p"weiyigeek.top"'
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.30    |
+-----------+
1 row in set (0.00 sec)

docker pull adminer:latest

# Standalone
docker run -d --restart unless-stopped --name db_adminer -p 8080:8080 adminer:latest

# FastCGI
docker run -d --name db_admine_fastcgi --link some_database:db -p 9000:9000 adminer:fastcgi

tee my.cnf <<'EOF'
[mysqld]
# 执行用户
user=mysql

# 开放监听服务端口
port=3306
bind-address=*
socket=/var/run/mysqld/mysqld.sock

# 数据目录
datadir=/var/lib/mysql

# 进程 pid 文件
pid-file=/var/run/mysqld/mysqld.pid

# 插件默认路径
plugin-dir=/usr/lib64/mysql/plugin/

# 安全文件路径
secure-file-priv=/var/lib/mysql-files

# 启用日志与路径设置
general-log=on
general-log-file=/var/lib/mysql/mysql8x.log

# 服务器字符集设置
character-set-server=utf8mb4 
collation-server=utf8mb4_unicode_ci

# 数据库容灾binlog启用配置
log-bin=binlog
log-bin-index=binlog.index

# 认证密码策略, 默认 aching_sha2_password , 针对于old链接认证方式为 mysql_native_password
default-authentication-plugin=mysql_native_password

# 跳过某些操作
skip-host-cache
skip-name-resolve

[client]
socket=/var/run/mysqld/mysqld.sock
EOF

# my.cnf 可用配置
Variables (--variable-name=value)
and boolean options {FALSE|TRUE}                             Value (after reading options)
------------------------------------------------------------ -------------
abort-slave-event-count                                      0
activate-all-roles-on-login                                  FALSE
...................................
wait-timeout                                                 28800
windowing-use-high-precision                                 TRUE
xa-detach-on-prepare                                         TRUE

MYSQL_DATABASE=数据库名称
MYSQL_USER=应用用户
MYSQL_PASSWORD=应用账号密码
MYSQL_ROOT_PASSWORD=ROOT账户密码
MYSQL_RANDOM_ROOT_PASSWORD=yes # 允许为为 root 用户生成一个随机初始密码并将其打印到stdout
MYSQL_ALLOW_EMPTY_PASSWORD=yes # 以允许使用根用户的空白密码启动容器,非常不建议在实践环境中使用该变量
# MYSQL_ONETIME_PASSWORD   # 通常不适用,此功能仅在 MySQL 5.6+ 上受支持。 在 MySQL 5.5 上使用此选项将在初始化期间引发适当的错误。 
# MYSQL_INITDB_SKIP_TZINFO # 默认情况下,入口点脚本会自动加载所需的时区数据 CONVERT_TZ()功能。 如果不需要，任何非空值都会禁用时区加载。 

# 备份
$ docker exec mysql8.0 sh -c 'exec mysqldump --all-databases -uroot -p"$MYSQL_ROOT_PASSWORD"' > /some/path/on/your/host/all-databases.sql

# 恢复
$ docker exec -i mysql8.0 sh -c 'exec mysql -uroot -p"$MYSQL_ROOT_PASSWORD"' < /some/path/on/your/host/all-databases.sql

version: '3.1'
services:
  db:
    image: mysql:8.0.30
    container_name: mysql8.x
    # NOTE: use of "mysql_native_password" is not recommended: https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password
    # (this is just an example, not intended to be a production configuration)
    command: --default-authentication-plugin=mysql_native_password
    restart: always
    environment:
      # MYSQL_ROOT_PASSWORD: example
      MYSQL_ROOT_PASSWORD_FILE: /app/my-secret-pw
      MYSQL_DATABASE: app
      MYSQL_USER: weiyigeek
      MYSQL_PASSWORD: password
    volumes:
      - "/app/data:/var/lib/mysql"
      - "/app/my-secret-pw:/app/my-secret-pw"
    ports:
      - 3306:3306
  # 部署adminer 进行图形化管理 mysql 数据库
  adminer:
    image: adminer
    restart: always
    ports:
      - 8080:8080

mkdir -vp /app/data
echo "weiyigeek.top" > /app/my-secret-pw

docker stack deploy -c mysql.yml mysql 
docker-compose -f mysql.yml up

tee K8s-Standalone-MySQL.yaml <<'EOF'
kind: Service
apiVersion: v1
metadata:
  name: {APP_NAME}
  namespace: {NAMESPACE}
  labels:
    app: {APP_NAME}
    type: standalone
spec:
  type: NodePort
  ports:
  - name: server
    port: 3306
    protocol: TCP
    targetPort: 3306
    nodePort: {NODEPORT}
  selector:
    app: {APP_NAME}
    type: standalone
---
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: {APP_NAME}
  namespace: {NAMESPACE}
  labels:
    app: {APP_NAME}
    type: standalone
  annotations:
    version: {APP_VERSION}
spec:
  replicas: 1
  selector:
    matchLabels:
      app: {APP_NAME}
      type: standalone
  serviceName: {APP_NAME}
  template:
    metadata:
      labels:
        app: {APP_NAME}
        type: standalone
    spec:
      # 运行节点标签选择
      # nodeSelector:
      #   app: database
      containers:
      - name: {APP_NAME}
        image: mysql:{APP_VERSION}
        imagePullPolicy: IfNotPresent
        ports:
        - name: server
          containerPort: 3306
        env:
        - name: MYSQL_ROOT_PASSWORD
          value: "{MYSQL_ROOT_PASSWORD}"
        - name: MYSQL_DATABASE
          value: "{MYSQL_DATABASE}"
        - name: MYSQL_USER
          value: "{MYSQL_USER}"
        - name: MYSQL_PASSWORD
          value: "{MYSQL_PASSWORD}"
        volumeMounts:
        - name: data
          mountPath: /var/lib/mysql
      #  - name: mysql-conf
      #    mountPath: /etc/mysql/my.cnf
      #    subPath: my.cnf
      #  - name: log
      #    mountPath: /var/log/mysqld.log
        resources:
          limits:
            memory: "4Gi"
            cpu: "2"
          requests:
            memory: "512Mi"
            cpu: "1"
      volumes:
      # 方式1,持久化 hostPath
      - name: mysql-persistent-storage
        hostPath:
          path: {HOSTPATH}
          type: DirectoryOrCreate
      # - name: mysql-conf
      #   configMap:
      #     name: mysql-conf
      #     items:
      #     - key: my.cnf
      #       path: my.cnf
   # 方式2,持久化nfs存储卷
  volumeClaimTemplates:
  - metadata:
      name: data
      labels:
        app: {APP_NAME}
        type: standalone
    spec:
      accessModes:
      - ReadWriteOnce
      storageClassName: {storageClassName}
      resources:
        requests:
          storage: 5Gi
EOF

# 注意,通常此目录为挂到各k8s节点上的nfs服务存储
mkdir -vp /app/data

# 替换关键配置
sed -i -e "s#{APP_NAME}#mysql-weiyigeek#g" -e "s#{NAMESPACE}#database#g" -e "s#{NODEPORT}#31001#g"  -e "s#{APP_VERSION}#8.0.30#g" \\
-e "s#{MYSQL_ROOT_PASSWORD}#weiyigeek.top#g"  -e "s#{MYSQL_DATABASE}#app#g" -e "s#{MYSQL_USER}#weiyigeek#g"  -e "s#{MYSQL_PASSWORD}#password#g" \\
-e "s#{HOSTPATH}#/app/data#g" -e "s#{storageClassName}#nfs-dev#g" \\
K8s-Standalone-MySQL.yaml

# 例如，可以将 my.cnf 使用 configmap 控制器进行存储，此外我采用镜像缺省的没有使用如下方式。
kubectl create configmap mysql-conf --from-file=my.cnf --namespace database

# 名词空间
kubectl create namespace database

# 部署mysql资源清单
kubectl apply -f K8s-Standalone-MySQL.yaml
  # service/mysql-weiyigeek created
  # statefulset.apps/mysql-weiyigeek created
  
# 查看部署情况
kubectl get sts,svc,pod -n database
  # NAME                         READY   AGE
  # statefulset.apps/mysql-weiyigeek   1/1     77s
  
  # NAME                TYPE       CLUSTER-IP       EXTERNAL-IP   PORT(S)          AGE
  # service/mysql-weiyigeek   NodePort   10.108.74.113    <none>        3306:31001/TCP   77s
  
  # NAME              READY   STATUS    RESTARTS   AGE
  # pod/mysql-weiyigeek-0   1/1     Running   0          77s

# 日志查看
kubectl logs -f --tail 50 -n database pod/mysql-weiyigeek-0

# 持久化数据查看
kubectl get pvc -n database
  # NAME               STATUS   VOLUME                                     CAPACITY   ACCESS MODES   STORAGECLASS   AGE
  # data-mysql-weiyigeek-0   Bound    pvc-37390e64-9401-4b66-8b4f-216d91e2a7fd   5Gi        RWO            nfs-dev        4m55s

cd /storage/dev/pvc/local/database-data-mysql-weiyigeek-0-pvc-37390e64-9401-4b66-8b4f-216d91e2a7fd
ls 
  # app             binlog.000002   ca.pem           '#ib_16384_0.dblwr'   ibdata1        '#innodb_temp'   mysql.sock           public_key.pem    sys
  # auto.cnf        binlog.index    client-cert.pem  '#ib_16384_1.dblwr'   ibtmp1          mysql           performance_schema   server-cert.pem   undo_001
  # binlog.000001   ca-key.pem      client-key.pem    ib_buffer_pool      '#innodb_redo'   mysql.ibd       private_key.pem      server-key.pem    undo_002

# 温馨提示：master节点上需要安装 helm 然后进行拉取部署的相关资源部署清单图表
helm3 repo add bitnami https://charts.bitnami.com/bitnami
helm3 search repo bitnami/mysql -l
  # NAME            CHART VERSION   APP VERSION     DESCRIPTION
  # bitnami/mysql   9.3.4           8.0.30          MySQL is a fast, reliable, scalable, and easy t....

# 拉取到本地以及其部署清单图表
$ helm3 pull bitnami/mysql  --version 9.3.4 --untar
$ ls mysql/
Chart.lock  charts  Chart.yaml  README.md  templates  values.schema.json  values.yaml

vim mysql/values.yaml
....
# 修改1.使用内部仓库镜像地址（后续会将其同步到内部harbor中此处先更改）
image:
  registry: harbor.weiyigeek.top
  repository: library/mysql
  tag: 8.0.30-debian-11-r15
...
# 修改2.MySQL部署模式 (`standalone` or `replication`)此处为主从复制。
architecture: replication

# 修改3.数据库认证账号（root、普通用户、replication用户）相关密码以及创建的数据库设置，密码留空则会自动生成
auth:
  rootPassword: ""
  createDatabase: true
  database: "app"
  username: "app"
  password: ""
  replicationUser: replicator
  replicationPassword: ""

# 修改4.MySQL Primary 服务相关参数配置
primary:
  name: primary
  # 资源限制 : 此处 1000m 表示使用1个CPU的资源，内存最大4G。
  resources:
    limits:
      cpu: 1000m   
      memory: 4Gi
  # 修改5.主资源持久化配置，此处我已经搭建了动态逻辑卷。
  persistence:
    enabled: true
    storageClass: "nfs-local"
    accessModes:
      - ReadWriteOnce
    size: 10Gi
  # 修改6.主服务持久化配置,注意此处与secondary服务节点配置不同
  service:
    type: NodePort
    ports:
      mysql: 3306
    nodePorts:
      mysql: "31006"

# 修改7.MySQL Secondary 服务相关参数配置
secondary:
  name: secondary
  replicaCount: 2
  resources:
  limits:
    cpu: 1000m
    memory: 2048Mi
  # 修改6.从(节点)资源持久化配置，此处我已经搭建了动态逻辑卷。
  persistence:
    enabled: true
    storageClass: "nfs-local"
    accessModes:
      - ReadWriteOnce
    size: 10Gi
  # 修改8.从(节点)持久化配置,注意此处与primary服务节点配置不同
  service:
    type: NodePort
    ports:
      mysql: 3306
    nodePorts:
      mysql: "31008"

# 修改点9.启用 Promethues 的 mysqld-exporter
metrics:
  enabled: true
  image:
    registry: harbor.weiyigeek.top
    repository: library/mysqld-exporter
    tag: 0.14.0-debian-11-r33

docker pull bitnami/mysqld-exporter:0.14.0-debian-11-r33
docker tag bitnami/mysqld-exporter:0.14.0-debian-11-r33 harbor.weiyigeek.top/library/mysqld-exporter:0.14.0-debian-11-r33
docker push harbor.weiyigeek.top/library/mysqld-exporter:0.14.0-debian-11-r33

docker pull bitnami/mysql:8.0.30-debian-11-r15
docker tag bitnami/mysql:8.0.30-debian-11-r15 harbor.weiyigeek.top/library/mysql:8.0.30-debian-11-r15
docker push harbor.weiyigeek.top/library/mysql:8.0.30-debian-11-r15

$ helm3 install mysql ./mysql --namespace database --create-namespace
  # NAME: mysql
  # LAST DEPLOYED: Wed Sep 28 16:33:23 2022
  # NAMESPACE: database
  # STATUS: deployed
  # REVISION: 1
  # TEST SUITE: None
  # NOTES:
  # CHART NAME: mysql
  # CHART VERSION: 9.3.4
  # APP VERSION: 8.0.30

$ helm3 list -n database
  # NAME    NAMESPACE       REVISION        UPDATED                                 STATUS          CHART           APP VERSION
  # mysql   database        1               2022-09-28 16:33:23.01465975 +0800 CST  deployed        mysql-9.3.4     8.0.30

$ kubectl get sts,pod -n database -l app.kubernetes.io/name=mysql
  # NAME                               READY   AGE
  # statefulset.apps/mysql-primary     1/1     2m37s
  # statefulset.apps/mysql-secondary   2/2     2m37s
  
  # NAME                    READY   STATUS    RESTARTS   AGE
  # pod/mysql-primary-0     2/2     Running   0          2m37s
  # pod/mysql-secondary-0   2/2     Running   0          2m37s
  # pod/mysql-secondary-1   2/2     Running   0          95s

echo -n "MYSQL_ROOT_PASSWORD=";kubectl get secret --namespace database mysql -o jsonpath="{.data.mysql-root-password}" | base64 -d;echo
# MYSQL_ROOT_PASSWORD=oX7112Avng

echo -n "MYSQL_PASSWORD=";kubectl get secret --namespace database mysql -o jsonpath="{.data.mysql-password}" | base64 -d;echo
# MYSQL_PASSWORD=pdtsixSpV28

echo -n "MYSQL_REPLICATION_PASSWORD=";kubectl get secret --namespace database mysql -o jsonpath="{.data.mysql-replication-password}" | base64 -d;echo
# MYSQL_REPLICATION_PASSWORD=FJRspMupePE

-- To connect to primary service (read/write):
CREATE TABLE replication (
  id int(11) NOT NULL  PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255)  NOT NULL,
  INDEX name_in (name)
);
INSERT INTO app.replication(name) VALUES('WeiyiGeek')

-- 创建数据库、用户、并将创建的数据库所有权赋予给创建用户
CREATE DATABASE dev;
CREATE USER 'dev'@'%' IDENTIFIED BY 'dev.weiyigeek.top';  
GRANT ALL ON dev.* TO 'dev'@"%";
FLUSH PRIVILEGES;

-- To connect to secondary service (read):
kubectl run mysql-client --rm --tty -i --restart='Never' --image  harbor.weiyigeek.top/library/mysql:8.0.30-debian-11-r15 --namespace database --env MYSQL_ROOT_PASSWORD=oX7xxIovng --command -- bash
--# mysql -h mysql-primary.database.svc -u app -p"$MYSQL_PASSWORD"
mysql -h mysql-secondary.database.svc -u app -p"$MYSQL_PASSWORD"

# 通过命令查看采集数据.
kubectl get --raw http://10.66.35.76:9104/metrics
kubectl get --raw http://10.66.53.95:9104/metrics

# 通过helm3卸载安装的mysql主从.
helm3 uninstall mysql --namespace database
# kubectl delete pod -n database `kubectl get pod -n database | awk 'NR>1{print $1}'` --force

# 删除创建的名称空间, 注意删除名词空间时, 若有其他资源请谨慎执行如下命令。
kubectl patch ns database -p '{"metadata":{"finalizers":null}}'
kubectl delete ns database --force