ITPub博客

首页 > 数据库 > MySQL > 在k8s上部署mysql 8.0 MGR

在k8s上部署mysql 8.0 MGR

原创 MySQL 作者:lsq_008 时间:2019-04-10 10:49:35 0 删除 编辑

一。环境概述

     k8s环境:1台master节点,3台node节点。

      在三个节点上搭建MGR集群。mysql的数据文件是挂载在本地存储,在镜像制作完成后,需要把初始化后的数据文件copy到三个节点本地的挂载点/data/mysql/data/,另外需要修改auto.cnf里面的uuid,让三个节点的uuid不同。


二。搭建步骤

  1. 制作mysql 8.0.15镜像

    此步骤略过,参照mysql官方的二进制包安装步骤, https://dev.mysql.com/doc/refman/8.0/en/binary-installation.html。   安装完毕后制作成镜像。

  2. 准备生成三个节点pod的相关yaml文件。

    ---创建namespace的文件namespace.yaml:

    apiVersion: v1
    kind: Namespace
    metadata:
      name: mysqldb

    ---节点1的configmap文件mysql-mgr-cnf-0.yaml:

    apiVersion: v1
    data:
      mysql-mgr-0.cnf: |
        [mysqld]
        port = 3306
        character_set_server = utf8
        socket = /tmp/mysql.sock
        basedir = /usr/local/mysql
        log-error = /data/mysql/data/mysql.err
        pid-file = /data/mysql/data/mysql.pid
        datadir = /data/mysql/data
        server_id = 092832
        log_bin = mysql-bin
        relay-log = relay-bin
        #back_log = 500
        #max_connections = 3000
        #wait_timeout = 5022397
        interactive_timeout = 5022397
        max_connect_errors = 1000
        relay-log-recovery=1
        #max_allowed_packet = 32M
        sort_buffer_size = 4M
        read_buffer_size = 4M
        join_buffer_size = 8M
        thread_cache_size = 64
        #tmp_table_size = 256M
        log_slave_updates=1
        long_query_time = 1
        slow_query_log = 1
        slow_query_log_file = /data/mysql/data/slow_sql.log
        skip-name-resolve
        sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
        innodb_buffer_pool_size=700M
        #innodb_data_file_path = ibdata1:1024M:autoextend
        innodb_flush_log_at_trx_commit=1
        innodb_log_buffer_size = 16M
        innodb_log_file_size = 256M
        innodb_log_files_in_group = 2
        innodb_max_dirty_pages_pct = 50
        sync_binlog=1
        master_info_repository=TABLE
        relay_log_info_repository=TABLE
        log_timestamps=SYSTEM
        gtid_mode = ON
        enforce_gtid_consistency = ON
        master_info_repository = TABLE
        relay_log_info_repository = TABLE
        log_slave_updates = ON
        binlog_checksum = NONE
        log_slave_updates = ON
        slave_parallel_type=LOGICAL_CLOCK
        slave_parallel_workers=8
        slave-preserve-commit-order=on
        #group_replication_compression_threshold=200000
        transaction_write_set_extraction = XXHASH64
        loose-group_replication_group_name="01e5fb97-be64-41f7-bafd-3afc7a6ab555"
        loose-group_replication_start_on_boot=off
        loose-group_replication_local_address="mysql-mgr-0.mgrtest.mysqldb.svc.cluster.local.:13306"
        loose-group_replication_group_seeds="mysql-mgr-0.mgrtest.mysqldb.svc.cluster.local.:13306,mysql-mgr-1.mgrtest.mysqldb.svc.cluster.local.:13306,mysql-mgr-2.mgrtest.mysqldb.svc.cluster.local.:13306"
        loose-group_replication_bootstrap_group = off
        loose-group_replication_ip_whitelist='10.244.0.0/16,172.17.0.0/16,10.229.0.0/16,10.228.0.0/16'
        report_host = mysql-mgr-0.mgrtest.mysqldb.svc.cluster.local
        [mysqldump]
        quick
        max_allowed_packet = 32M
    kind: ConfigMap
    metadata:
      name: mysql-mgr-0-cnf
      namespace: mysqldb


    ---节点2的configmap文件mysql-mgr-cnf-1.yaml:

    apiVersion: v1
    data:
      mysql-mgr-1.cnf: |
        [mysqld]
        port = 3306
        character_set_server = utf8
        socket = /tmp/mysql.sock
        basedir = /usr/local/mysql
        log-error = /data/mysql/data/mysql.err
        pid-file = /data/mysql/data/mysql.pid
        datadir = /data/mysql/data
        server_id = 092231
        log_bin = mysql-bin
        relay-log = relay-bin
        #back_log = 500
        #max_connections = 3000
        #wait_timeout = 5022397
        interactive_timeout = 5022397
        max_connect_errors = 1000
        relay-log-recovery=1
        #max_allowed_packet = 32M
        sort_buffer_size = 4M
        read_buffer_size = 4M
        join_buffer_size = 8M
        thread_cache_size = 64
        #tmp_table_size = 256M
        log_slave_updates=1
        long_query_time = 1
        slow_query_log = 1
        slow_query_log_file = /data/mysql/data/slow_sql.log
        skip-name-resolve
        sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
        innodb_buffer_pool_size=700M
        #innodb_data_file_path = ibdata1:1024M:autoextend
        innodb_flush_log_at_trx_commit=1
        innodb_log_buffer_size = 16M
        innodb_log_file_size = 256M
        innodb_log_files_in_group = 2
        innodb_max_dirty_pages_pct = 50
        sync_binlog=1
        master_info_repository=TABLE
        relay_log_info_repository=TABLE
        log_timestamps=SYSTEM
        gtid_mode = ON
        enforce_gtid_consistency = ON
        master_info_repository = TABLE
        relay_log_info_repository = TABLE
        log_slave_updates = ON
        binlog_checksum = NONE
        log_slave_updates = ON
        slave_parallel_type=LOGICAL_CLOCK
        slave_parallel_workers=8
        slave-preserve-commit-order=on
        #group_replication_compression_threshold=200000
        transaction_write_set_extraction = XXHASH64
        loose-group_replication_group_name="01e5fb97-be64-41f7-bafd-3afc7a6ab555"
        loose-group_replication_start_on_boot=off
        loose-group_replication_local_address="mysql-mgr-1.mgrtest.mysqldb.svc.cluster.local.:13306"
        loose-group_replication_group_seeds="mysql-mgr-0.mgrtest.mysqldb.svc.cluster.local.:13306,mysql-mgr-1.mgrtest.mysqldb.svc.cluster.local.:13306,mysql-mgr-2.mgrtest.mysqldb.svc.cluster.local.:13306"
        loose-group_replication_bootstrap_group = off
        loose-group_replication_ip_whitelist='10.244.0.0/16,172.17.0.0/16,10.229.0.0/16,10.228.0.0/16'
        report_host = mysql-mgr-1.mgrtest.mysqldb.svc.cluster.local
        [mysqldump]
        quick
        max_allowed_packet = 32M
    kind: ConfigMap
    metadata:
      name: mysql-mgr-1-cnf
      namespace: mysqldb


    ---节点3的configmap文件mysql-mgr-cnf-2.yaml:

    apiVersion: v1
    data:
      mysql-mgr-2.cnf: |
        [mysqld]
        port = 3306
        character_set_server = utf8
        socket = /tmp/mysql.sock
        basedir = /usr/local/mysql
        log-error = /data/mysql/data/mysql.err
        pid-file = /data/mysql/data/mysql.pid
        datadir = /data/mysql/data
        server_id = 092132
        log_bin = mysql-bin
        relay-log = relay-bin
        #back_log = 500
        #max_connections = 3000
        #wait_timeout = 5022397
        interactive_timeout = 5022397
        max_connect_errors = 1000
        relay-log-recovery=1
        #max_allowed_packet = 32M
        sort_buffer_size = 4M
        read_buffer_size = 4M
        join_buffer_size = 8M
        thread_cache_size = 64
        #tmp_table_size = 256M
        log_slave_updates=1
        long_query_time = 1
        slow_query_log = 1
        slow_query_log_file = /data/mysql/data/slow_sql.log
        skip-name-resolve
        sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
        innodb_buffer_pool_size=700M
        #innodb_data_file_path = ibdata1:1024M:autoextend
        innodb_flush_log_at_trx_commit=1
        innodb_log_buffer_size = 16M
        innodb_log_file_size = 256M
        innodb_log_files_in_group = 2
        innodb_max_dirty_pages_pct = 50
        sync_binlog=1
        master_info_repository=TABLE
        relay_log_info_repository=TABLE
        log_timestamps=SYSTEM
        gtid_mode = ON
        enforce_gtid_consistency = ON
        master_info_repository = TABLE
        relay_log_info_repository = TABLE
        log_slave_updates = ON
        binlog_checksum = NONE
        log_slave_updates = ON
        slave_parallel_type=LOGICAL_CLOCK
        slave_parallel_workers=8
        slave-preserve-commit-order=on
        #group_replication_compression_threshold=200000
        transaction_write_set_extraction = XXHASH64
        loose-group_replication_group_name="01e5fb97-be64-41f7-bafd-3afc7a6ab555"
        loose-group_replication_start_on_boot=off
        loose-group_replication_local_address="mysql-mgr-2.mgrtest.mysqldb.svc.cluster.local.:13306"
        loose-group_replication_group_seeds="mysql-mgr-0.mgrtest.mysqldb.svc.cluster.local.:13306,mysql-mgr-1.mgrtest.mysqldb.svc.cluster.local.:13306,mysql-mgr-2.mgrtest.mysqldb.svc.cluster.local.:13306"
        loose-group_replication_bootstrap_group = off
        loose-group_replication_ip_whitelist='10.244.0.0/16,172.17.0.0/16,10.229.0.0/16,10.228.0.0/16'
        report_host = mysql-mgr-2.mgrtest.mysqldb.svc.cluster.local
        [mysqldump]
        quick
        max_allowed_packet = 32M
    kind: ConfigMap
    metadata:
      name: mysql-mgr-2-cnf
      namespace: mysqldb

    ----节点1的pod的yaml文件:
    apiVersion: v1
    kind: Pod
    metadata:
      name: mysql-mgr-0
      namespace: mysqldb
      labels:
        name: mysql-mgr
    spec:
      affinity:
        nodeAffinity:
          requiredDuringSchedulingIgnoredDuringExecution:
            nodeSelectorTerms:
              - matchExpressions:
                - key: mysqlrole
                  operator: In
                  values: ["mysql-mgr-0"]
      hostname: mysql-mgr-0
      subdomain: mgrtest
      containers:
      - image: 172.16.110.102:5000/mysql8.0:latest
        name: mysql-mgr-0
        imagePullPolicy: IfNotPresent
        command: [ "/bin/bash", "-ce", "cd /usr/local/mysql && bin/mysqld_safe --defaults-file=/etc/my.cnf && tail -f /dev/null" ]
        #env:
        #- name: MYSQL_ROOT_PASSWORD
        #  value: noc-mysql
        ports:
          - containerPort: 3306
        volumeMounts:
        - name: tz-config
          mountPath: /etc/localtime
        - name: mysql-data
          mountPath: /data/mysql/data/
        - name: mysql-config
          mountPath: /etc/my.cnf
          subPath: my.cnf
        env:
        - name: INNODB_BUFFER_POOL_SIZE
          value: 500M
        #- name: REPORT_HOST
        #  value: mysql-mgr-0.mgrtest.mysqldb.svc.cluster.local
      volumes:
        - name: tz-config
          hostPath:
            path: /etc/localtime
        - name: mysql-data
          hostPath:
            path: /data/mysql/data/
        - name: mysql-config
          configMap:
            name: mysql-mgr-0-cnf
            items:
              - key: mysql-mgr-0.cnf
                path: my.cnf
    ----节点2的pod的yaml文件:

    apiVersion: v1
    kind: Pod
    metadata:
      name: mysql-mgr-1
      namespace: mysqldb
      labels:
        name: mysql-mgr
    spec:
      affinity:
        nodeAffinity:
          requiredDuringSchedulingIgnoredDuringExecution:
            nodeSelectorTerms:
              - matchExpressions:
                - key: mysqlrole
                  operator: In
                  values: ["mysql-mgr-1"]
      hostname: mysql-mgr-1
      subdomain: mgrtest
      containers:
      - image: 172.16.110.102:5000/mysql8.0:latest
        name: mysql-mgr-1
        imagePullPolicy: IfNotPresent
        command: [ "/bin/bash", "-ce", "cd /usr/local/mysql && bin/mysqld_safe --defaults-file=/etc/my.cnf && tail -f /dev/null" ]
        #env:
        #- name: MYSQL_ROOT_PASSWORD
        #  value: noc-mysql
        ports:
          - containerPort: 3306
        volumeMounts:
        - name: tz-config
          mountPath: /etc/localtime
        - name: mysql-data
          mountPath: /data/mysql/data
        - name: mysql-config
          mountPath: /etc/my.cnf
          subPath: my.cnf
        env:
        - name: INNODB_BUFFER_POOL_SIZE
          value: 500M
      volumes:
        - name: tz-config
          hostPath:
            path: /etc/localtime
        - name: mysql-data
          hostPath:
            path: /data/mysql/data/
        - name: mysql-config
          configMap:
            name: mysql-mgr-1-cnf
            items:
              - key: mysql-mgr-1.cnf
                path: my.cnf

    ---节点3的pod的yaml文件:

    apiVersion: v1
    kind: Pod
    metadata:
      name: mysql-mgr-2
      namespace: mysqldb
      labels:
        name: mysql-mgr
    spec:
      affinity:
        nodeAffinity:
          requiredDuringSchedulingIgnoredDuringExecution:
            nodeSelectorTerms:
              - matchExpressions:
                - key: mysqlrole
                  operator: In
                  values: ["mysql-mgr-2"]
      hostname: mysql-mgr-2
      subdomain: mgrtest
      containers:
      - image: 172.16.110.102:5000/mysql8.0:latest
        name: mysql-mgr-2
        imagePullPolicy: IfNotPresent
        command: [ "/bin/bash", "-ce", "cd /usr/local/mysql && bin/mysqld_safe --defaults-file=/etc/my.cnf && tail -f /dev/null" ]
        #env:
        #- name: MYSQL_ROOT_PASSWORD
        #  value: noc-mysql
        ports:
          - containerPort: 3306
        volumeMounts:
        - name: tz-config
          mountPath: /etc/localtime
        - name: mysql-data
          mountPath: /data/mysql/data
        - name: mysql-config
          mountPath: /etc/my.cnf
          subPath: my.cnf
        env:
        - name: INNODB_BUFFER_POOL_SIZE
          value: 500M
      volumes:
        - name: tz-config
          hostPath:
            path: /etc/localtime
        - name: mysql-data
          hostPath:
            path: /data/mysql/data/
        - name: mysql-config
          configMap:
            name: mysql-mgr-2-cnf
            items:
              - key: mysql-mgr-2.cnf
                path: my.cnf

    ---为三个pod创建的service的yaml文件

    apiVersion: v1
    kind: Service
    metadata:
      name: mgrtest
      namespace: mysqldb
    spec:
      selector:
        name: mysql-mgr
      clusterIP:   None
      ports:
      - name:   foo
        port:   3306
        targetPort: 3306

  3.  创建namespace,service,configmap,pod

    kubectl create -f namespace.yaml

    kubectl create -f mysql-mgr-svc.yaml

    kubectl create -f mysql-mgr-cnf-0.yaml

    kubectl create -f mysql-mgr-cnf-1.yaml

    kubectl create -f mysql-mgr-cnf-2.yaml

    kubectl create -f mysql-mgr-0-pod.yaml

    kubectl create -f mysql-mgr-1-pod.yaml

    kubectl create -f mysql-mgr-2-pod.yaml

    创建后如下图所示:



  4.  配置节点1:

    root@dkm:/app/mgr# kubectl exec -it mysql-mgr-0 -n mysqldb /bin/bash

    root@mysql-mgr-0:/# /usr/local/mysql/bin/mysql -uroot -p


    alter user 'root'@'localhost' identified by 'Mysql123!@#';
    set sql_log_bin=0;
    create user rpl_user@'%' identified by 'Rpl_pass@123';
    grant replication slave on *.* to rpl_user@'%';
    flush privileges;
    set sql_log_bin=1;
    change master to master_user='rpl_user',master_password='Rpl_pass@123' for channel 'group_replication_recovery';
    install PLUGIN group_replication SONAME 'group_replication.so';

    reset master;
    set global group_replication_single_primary_mode=FALSE;
    set global group_replication_enforce_update_everywhere_checks=TRUE;
    set global group_replication_bootstrap_group=ON;
    start group_replication;
    set global group_replication_bootstrap_group=OFF;
    select * from performance_schema.replication_group_members;


  5. 配置节点2:

    set sql_log_bin=0;
    create user rpl_user@'%' identified by 'Rpl_pass@123';
    grant replication slave on *.* to rpl_user@'%';
    flush privileges;
    set sql_log_bin=1;
    change master to master_user='rpl_user',master_password='Rpl_pass@123' for channel 'group_replication_recovery';
    install PLUGIN group_replication SONAME 'group_replication.so';

    reset master;
    set global group_replication_single_primary_mode=FALSE;
    set global group_replication_enforce_update_everywhere_checks=TRUE;
    set global group_replication_recovery_get_public_key=on;
    start group_replication;
    select * from performance_schema.replication_group_members;


  6.   配置节点3:

    set sql_log_bin=0;
    create user rpl_user@'%' identified by 'Rpl_pass@123';
    grant replication slave on *.* to rpl_user@'%';
    flush privileges;
    set sql_log_bin=1;
    change master to master_user='rpl_user',master_password='Rpl_pass@123' for channel 'group_replication_recovery';
    install PLUGIN group_replication SONAME 'group_replication.so';

    reset master;
    set global group_replication_single_primary_mode=FALSE;
    set global group_replication_enforce_update_everywhere_checks=TRUE;
    set global group_replication_recovery_get_public_key=on;
    start group_replication;
    select * from performance_schema.replication_group_members;


     

  7. 总结:

    (1)节点之间是通过域名互相通讯,域名的构成: hostname.service.namespace.svc.cluster.local.

    (2)注意在每个节点的配置文件里配置report_host为该节点的域名,也就是hostname.service.namespace.svc.cluster.local。否则会报错:

    2019-04-10T09:16:53.607069+08:00 55 [ERROR] [MY-010584] [Repl] Slave I/O for channel 'group_replication_recovery': error connecting to master 'rpl_user@mysql-mgr-0:3306' - retry-time: 60  retries: 1, Error_code: MY-002005
    ...

    2019-04-10T09:17:53.682099+08:00 24 [ERROR] [MY-011582] [Repl] Plugin group_replication reported: 'There was an error when connecting to the donor server. Please check that group_replication_recovery channel credentials and all MEMBER_HOST column values of performance_schema.replication_group_members table are correct and DNS resolvable.'







来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10972173/viewspace-2640886/,如需转载,请注明出处,否则将追究法律责任。

下一篇: TG bot
请登录后发表评论 登录
全部评论
十余年大型金融及电信系统数据库管理经验,曾服务于中国建设银行、中国移动。对oracle,mysql数据库有深入了解。 擅长python开发,独立开发了开源数据库自动化监控运维平台Power Monitor。

注册时间:2008-02-29

  • 博文量
    325
  • 访问量
    1245611