logo
阿辉
banner
NEWS LETTER

部署Mysql主从

Scroll down

本文作者：丁辉

部署Mysql主从

基础准备

创建命名空间

1
kubectl create ns mysql

开始部署

主节点配置

  1. 编辑 mysql 配置文件

    1
    vi my.cnf

    内容如下

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    [mysqld]
    pid-file        = /var/run/mysqld/mysqld.pid
    socket          = /var/run/mysqld/mysqld.sock
    datadir         = /var/lib/mysql
    secure-file-priv= NULL
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
    # 服务端默认utf8编码
    character-set-server=utf8mb4
    # 默认存储引擎
    default-storage-engine=INNODB

    # 主从配置
    log-bin=binlog
    server-id=121
    gtid-mode=on
    enforce-gtid-consistency=on
    log-slave-updates=on
    expire_logs_days=14

    # Compatible with versions before 8.0
    default_authentication_plugin=mysql_native_password
    skip-host-cache
    skip-name-resolve

    [client]
    #设置客户端编码
    default-character-set=utf8mb4
    [mysql]
    # 设置mysql客户端默认编码
    default-character-set=utf8mb4

    # Custom config should go here
    !includedir /etc/mysql/conf.d/
    # Custom config should go here
    !includedir /etc/mysql/conf.d/

  2. 创建 configmap

    1
    kubectl create configmap mysql-master-conf --from-file=./my.cnf -n mysql

  3. 编辑 Yaml

    1
    vi mysql-master.yaml

    内容如下

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    apiVersion: apps/v1
    kind: StatefulSet
    metadata:
      namespace: mysql
      name: mysql-master
    spec:
      replicas: 1
      serviceName: mysql-master-service
      selector:
        matchLabels:
          app: mysql-master
      template:
        metadata:
          labels:
            app: mysql-master
        spec:
          containers:
          - name: mysql-master
            image: mysql:8.0
            env:
            - name: MYSQL_ROOT_PASSWORD
              value: password
            volumeMounts:
            - mountPath: /var/lib/mysql
              name: data
            - name: file
              mountPath: /etc/mysql/my.cnf
              subPath: my.cnf
            ports:
            - containerPort: 3306
              protocol: TCP
            livenessProbe:
               exec:
                 command:
                   - mysql
                   - --user=root
                   - --password=password
                   - --execute=SELECT 1
               initialDelaySeconds: 10 #启动后等待10秒开始检测
               periodSeconds: 10 #每隔10秒检测一次
          nodeName: #node1
          volumes:
          - name: data
            hostPath:
              path: /opt/mysql/data
          - name: file
            configMap:
              name: mysql-master-conf
    ---
    apiVersion: v1
    kind: Service
    metadata:
      namespace: mysql
      name: mysql-master-service
    spec:
      selector:
        app: mysql-master
      ports:
      - port: 3306
        targetPort: 3306
        protocol: TCP
      type: ClusterIP

  4. 部署数据库主

    1
    kubectl apply -f mysql-master.yaml

从节点配置

  1. 编辑 mysql 配置文件

    1
    vi my.cnf

    内容如下

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    [mysqld]
    pid-file        = /var/run/mysqld/mysqld.pid
    socket          = /var/run/mysqld/mysqld.sock
    datadir         = /var/lib/mysql
    secure-file-priv= NULL
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
    # 服务端默认utf8编码
    character-set-server=utf8mb4
    # 默认存储引擎
    default-storage-engine=INNODB

    # 主从配置
    server-id=122
    gtid-mode=on
    enforce-gtid-consistency=on
    log-slave-updates=on
    expire_logs_days=14

    # Compatible with versions before 8.0
    default_authentication_plugin=mysql_native_password
    skip-host-cache
    skip-name-resolve

    [client]
    #设置客户端编码
    default-character-set=utf8mb4
    [mysql]
    # 设置mysql客户端默认编码
    default-character-set=utf8mb4

    # Custom config should go here
    !includedir /etc/mysql/conf.d/
    # Custom config should go here
    !includedir /etc/mysql/conf.d/

  2. 创建 configmap

    1
    kubectl create configmap mysql-slave-conf --from-file=./my.cnf -n mysql

  3. 编辑 Yaml

    1
    vi mysql-slave.yaml

    内容如下

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    apiVersion: apps/v1
    kind: StatefulSet
    metadata:
      namespace: mysql
      name: mysql-slave
    spec:
      replicas: 1
      serviceName: mysql-slave-service
      selector:
        matchLabels:
          app: mysql-slave
      template:
        metadata:
          labels:
            app: mysql-slave
        spec:
          containers:
          - name: mysql-slave
            image: mysql:8.0
            env:
            - name: MYSQL_ROOT_PASSWORD
              value: password
            volumeMounts:
            - mountPath: /var/lib/mysql
              name: data
            - name: file
              mountPath: /etc/mysql/my.cnf
              subPath: my.cnf
            ports:
            - containerPort: 3306
              protocol: TCP
            livenessProbe:
               exec:
                 command:
                   - mysql
                   - --user=root
                   - --password=password
                   - --execute=SELECT 1
               initialDelaySeconds: 10 #启动后等待10秒开始检测
               periodSeconds: 10 #每隔10秒检测一次
          nodeName: #node2
          volumes:
          - name: data
            hostPath:
              path: /opt/mysql/data
          - name: file
            configMap:
              name: mysql-slave-conf
    ---
    apiVersion: v1
    kind: Service
    metadata:
      namespace: mysql
      name: mysql-slave-service
    spec:
      selector:
        app: mysql-slave
      ports:
      - port: 3306
        targetPort: 3306
        protocol: TCP
      type: ClusterIP

  4. 部署数据库从

    1
    kubectl apply -f mysql-slave.yaml

配置主从同步

  1. 登录主节点

    1
    kubectl exec -it mysql-master-0 -n mysql bash

  2. 登录数据库

    1
    mysql -u root -ppassword

  3. 配置

    1
    2
    3
    4
    CREATE USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'slave';
    GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
    flush privileges;
    SHOW MASTER STATUS;

  4. 登录从节点

    1
    kubectl exec -it mysql-slave-0 -n mysql bash

  5. 登录数据库

    1
    mysql -u root -ppassword

  6. 配置

    1
    2
    3
    4
    5
    6
    7
    CHANGE MASTER TO
    MASTER_HOST='IP',
    MASTER_USER='slave',
    MASTER_PASSWORD='slave',
    MASTER_PORT=3306,
    MASTER_LOG_FILE='binlog.00000*',   #列：binlog.000001
    MASTER_LOG_POS=***;   #列：868

  7. 开启主从同步

    1
    start slave;

  8. 查看同步状态

    1
    show slave status\G;

    查看到这两个参数为 Yes 则代表配置成功

    • Slave_IO_Running: Yes
    • Slave_SQL_Running: Yes

  9. 登录主节点，创建数据库

    1
    2
    create database console;
    create database region;

    从节点查看仓库是否已同步

I'm so cute. Please give me money.

其他文章
cover
阿里云盘配合容器化客户端使用
  • 70/01/01
  • 00:00
  • Kubernetes-资源部署
cover
Windows优化
  • 70/01/01
  • 00:00
  • 系统-Windows
目录导航 置顶
  1. 1. 部署Mysql主从
    1. 1.1. 基础准备
    2. 1.2. 开始部署
      1. 1.2.1. 主节点配置
      2. 1.2.2. 从节点配置
      3. 1.2.3. 配置主从同步
请输入关键词进行搜索
  • to close