mysql实现mycat读写分离

先部署mysql主从

一、主从同步

1、启动mysql

  • master
---
apiVersion: v1
kind: Secret
metadata:
  name: mysql-master-secret
  namespace: xafq-api-middleware
data:
  username: cm9vdA==
  password: U2dRSHZzeTlNN0xXS0JDeg==
---
apiVersion: v1
kind: ConfigMap
metadata:
  name: mysql-master-config
  namespace: xafq-api-middleware
data:
  my.cnf: |
    [client]
    default-character-set=utf8mb4
    [mysql]
    default-character-set=utf8mb4
    [mysqld]
    default-time_zone = '+8:00'
    log-bin=mysql-bin
    skip-name-resolve
    server-id=1
    sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    pid-file        = /var/run/mysqld/mysqld.pid
    socket          = /var/run/mysqld/mysqld.sock
    datadir         = /var/lib/mysql
    secure-file-priv= NULL
    max_connections = 10000
    lower_case_table_names=1
---
apiVersion: apps/v1
kind: StatefulSet
metadata:
  annotations: {}
  labels:
    k8s.kuboard.cn/layer: db
    k8s.kuboard.cn/name: mysql-master
  name: mysql-master
  namespace: xafq-api-middleware
spec:
  podManagementPolicy: OrderedReady
  replicas: 1
  revisionHistoryLimit: 10
  selector:
    matchLabels:
      k8s.kuboard.cn/layer: db
      k8s.kuboard.cn/name: mysql-master
  serviceName: mysql-master
  template:
    metadata:
      labels:
        k8s.kuboard.cn/layer: db
        k8s.kuboard.cn/name: mysql-master
    spec:
      initContainers:
        - command:
            - sh
            - '-c'
            - >-
              until nc -w 1 -z
              mysql-slave-1.xafq-api-middleware.svc.cluster.local 3306; do echo
              waiting for mysql-slave-1; sleep 2; done;
          image: 'xxxxxxxxxxx/middleware/busybox:latest'
          imagePullPolicy: Always
          name: init-slave-1
          resources: {}
          terminationMessagePath: /dev/termination-log
          terminationMessagePolicy: File
        - command:
            - sh
            - '-c'
            - >-
              until nc -w 1 -z
              mysql-slave-2.xafq-api-middleware.svc.cluster.local 3306; do echo
              waiting for mysql-slave-2; sleep 2; done;
          image: 'xxxxxxxxxxx/middleware/busybox:latest'
          imagePullPolicy: Always
          name: init-slave-2
          resources: {}
          terminationMessagePath: /dev/termination-log
          terminationMessagePolicy: File
      containers:
        - env:
            - name: MYSQL_ROOT_PASSWORD
              valueFrom:
                secretKeyRef:
                  key: password
                  name: mysql-master-secret
          image: xxxxxxxxxxx/middleware/mysql:8.0.27
          imagePullPolicy: IfNotPresent
          name: mysql-master
          resources:
            limits:
              cpu: 1000m
              memory: 8Gi
            requests:
              cpu: 500m
              memory: 4Gi
          terminationMessagePath: /dev/termination-log
          terminationMessagePolicy: File
          volumeMounts:
            - mountPath: /etc/mysql/
              name: mysql-master
            - mountPath: /var/lib/mysql
              name: mysql-master-data
      dnsPolicy: ClusterFirst
      restartPolicy: Always
      schedulerName: default-scheduler
      securityContext: {}
      terminationGracePeriodSeconds: 30
      volumes:
        - configMap:
            defaultMode: 420
            name: mysql-master-config
          name: mysql-master
  updateStrategy:
    rollingUpdate:
      partition: 0
    type: RollingUpdate
  volumeClaimTemplates:
    - apiVersion: v1
      kind: PersistentVolumeClaim
      metadata:
        annotations:
          k8s.kuboard.cn/pvcType: Dynamic
        creationTimestamp: null
        name: mysql-master-data
      spec:
        accessModes:
          - ReadWriteMany
        resources:
          requests:
            storage: 20Gi
        storageClassName: statefu-nfs
        volumeMode: Filesystem
---
apiVersion: v1
kind: Service
metadata:
  annotations: {}
  labels:
    k8s.kuboard.cn/layer: db
    k8s.kuboard.cn/name: mysql-master
  name: mysql-master
  namespace: xafq-api-middleware
spec:
  ports:
    - name: mysql-master
      nodePort: 30490
      port: 3306
      protocol: TCP
      targetPort: 3306
  selector:
    k8s.kuboard.cn/layer: db
    k8s.kuboard.cn/name: mysql-master
  sessionAffinity: None
  type: NodePort
  • slave-1
---
apiVersion: v1
kind: Secret
metadata:
  name: mysql-slave-1-secret
  namespace: xafq-api-middleware
data:
  username: cm9vdA==
  password: U2dRSHZzeTlNN0xXS0JDeg==
---
apiVersion: v1
kind: ConfigMap
metadata:
  name: mysql-slave-1-config
  namespace: xafq-api-middleware
data:
  my.cnf: |
    [client]
    default-character-set=utf8mb4
    [mysql]
    default-character-set=utf8mb4
    [mysqld]
    default-time_zone = '+8:00'
    skip-name-resolve
    server-id=2
    sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    pid-file        = /var/run/mysqld/mysqld.pid
    socket          = /var/run/mysqld/mysqld.sock
    datadir         = /var/lib/mysql
    secure-file-priv= NULL
    max_connections = 10000
    lower_case_table_names=1
---
apiVersion: apps/v1
kind: StatefulSet
metadata:
  annotations: {}
  labels:
    k8s.kuboard.cn/layer: db
    k8s.kuboard.cn/name: mysql-slave-1
  name: mysql-slave-1
  namespace: xafq-api-middleware
spec:
  podManagementPolicy: OrderedReady
  replicas: 1
  revisionHistoryLimit: 10
  selector:
    matchLabels:
      k8s.kuboard.cn/layer: db
      k8s.kuboard.cn/name: mysql-slave-1
  serviceName: mysql-slave-1
  template:
    metadata:
      labels:
        k8s.kuboard.cn/layer: db
        k8s.kuboard.cn/name: mysql-slave-1
    spec:
      containers:
        - env:
            - name: MYSQL_ROOT_PASSWORD
              valueFrom:
                secretKeyRef:
                  key: password
                  name: mysql-slave-1-secret
          image: xxxxxxxxxxx/middleware/mysql:8.0.27
          imagePullPolicy: IfNotPresent
          name: mysql-slave-1
          resources:
            limits:
              cpu: 1000m
              memory: 8Gi
            requests:
              cpu: 500m
              memory: 4Gi
          terminationMessagePath: /dev/termination-log
          terminationMessagePolicy: File
          volumeMounts:
            - mountPath: /etc/mysql/
              name: mysql-slave-1
            - mountPath: /var/lib/mysql
              name: mysql-slave-1-data
      dnsPolicy: ClusterFirst
      restartPolicy: Always
      schedulerName: default-scheduler
      securityContext: {}
      terminationGracePeriodSeconds: 30
      volumes:
        - configMap:
            defaultMode: 420
            name: mysql-slave-1-config
          name: mysql-slave-1
  updateStrategy:
    rollingUpdate:
      partition: 0
    type: RollingUpdate
  volumeClaimTemplates:
    - apiVersion: v1
      kind: PersistentVolumeClaim
      metadata:
        annotations:
          k8s.kuboard.cn/pvcType: Dynamic
        creationTimestamp: null
        name: mysql-slave-1-data
      spec:
        accessModes:
          - ReadWriteMany
        resources:
          requests:
            storage: 20Gi
        storageClassName: statefu-nfs
        volumeMode: Filesystem
---
apiVersion: v1
kind: Service
metadata:
  annotations: {}
  labels:
    k8s.kuboard.cn/layer: db
    k8s.kuboard.cn/name: mysql-slave-1
  name: mysql-slave-1
  namespace: xafq-api-middleware
spec:
  ports:
    - name: mysql-slave-1
      nodePort: 30491
      port: 3306
      protocol: TCP
      targetPort: 3306
  selector:
    k8s.kuboard.cn/layer: db
    k8s.kuboard.cn/name: mysql-slave-1
  sessionAffinity: None
  type: NodePort
  • slave-2
---
apiVersion: v1
kind: Secret
metadata:
  name: mysql-slave-2-secret
  namespace: xafq-api-middleware
data:
  username: cm9vdA==
  password: U2dRSHZzeTlNN0xXS0JDeg==
---
apiVersion: v1
kind: ConfigMap
metadata:
  name: mysql-slave-2-config
  namespace: xafq-api-middleware
data:
  my.cnf: |
    [client]
    default-character-set=utf8mb4
    [mysql]
    default-character-set=utf8mb4
    [mysqld]
    default-time_zone = '+8:00'
    skip-name-resolve
    server-id=3
    sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    pid-file        = /var/run/mysqld/mysqld.pid
    socket          = /var/run/mysqld/mysqld.sock
    datadir         = /var/lib/mysql
    secure-file-priv= NULL
    max_connections = 10000
    lower_case_table_names=1
---
apiVersion: apps/v1
kind: StatefulSet
metadata:
  annotations: {}
  labels:
    k8s.kuboard.cn/layer: db
    k8s.kuboard.cn/name: mysql-slave-2
  name: mysql-slave-2
  namespace: xafq-api-middleware
spec:
  podManagementPolicy: OrderedReady
  replicas: 1
  revisionHistoryLimit: 10
  selector:
    matchLabels:
      k8s.kuboard.cn/layer: db
      k8s.kuboard.cn/name: mysql-slave-2
  serviceName: mysql-slave-2
  template:
    metadata:
      labels:
        k8s.kuboard.cn/layer: db
        k8s.kuboard.cn/name: mysql-slave-2
    spec:
      containers:
        - env:
            - name: MYSQL_ROOT_PASSWORD
              valueFrom:
                secretKeyRef:
                  key: password
                  name: mysql-slave-2-secret
          image: xxxxxxxxxxx/middleware/mysql:8.0.27
          imagePullPolicy: IfNotPresent
          name: mysql-slave-2
          resources:
            limits:
              cpu: 1000m
              memory: 8Gi
            requests:
              cpu: 500m
              memory: 4Gi
          terminationMessagePath: /dev/termination-log
          terminationMessagePolicy: File
          volumeMounts:
            - mountPath: /etc/mysql/
              name: mysql-slave-2
            - mountPath: /var/lib/mysql
              name: mysql-slave-2-data
      dnsPolicy: ClusterFirst
      restartPolicy: Always
      schedulerName: default-scheduler
      securityContext: {}
      terminationGracePeriodSeconds: 30
      volumes:
        - configMap:
            defaultMode: 420
            name: mysql-slave-2-config
          name: mysql-slave-2
  updateStrategy:
    rollingUpdate:
      partition: 0
    type: RollingUpdate
  volumeClaimTemplates:
    - apiVersion: v1
      kind: PersistentVolumeClaim
      metadata:
        annotations:
          k8s.kuboard.cn/pvcType: Dynamic
        creationTimestamp: null
        name: mysql-slave-2-data
      spec:
        accessModes:
          - ReadWriteMany
        resources:
          requests:
            storage: 20Gi
        storageClassName: statefu-nfs
        volumeMode: Filesystem
---
apiVersion: v1
kind: Service
metadata:
  annotations: {}
  labels:
    k8s.kuboard.cn/layer: db
    k8s.kuboard.cn/name: mysql-slave-2
  name: mysql-slave-2
  namespace: xafq-api-middleware
spec:
  ports:
    - name: mysql-slave-2
      nodePort: 30492
      port: 3306
      protocol: TCP
      targetPort: 3306
  selector:
    k8s.kuboard.cn/layer: db
    k8s.kuboard.cn/name: mysql-slave-2
  sessionAffinity: None
  type: NodePort

从库没什么区别，就配置文件注意下

---
apiVersion: v1
kind: ConfigMap
metadata:
  name: mysql-slave-1-config
  namespace: xafq-dev-middleware
data:
  my.cnf: |
    [client]
    default-character-set=utf8mb4
    [mysql]
    default-character-set=utf8mb4
    [mysqld]
    default-time_zone = '+8:00'
    log-bin=mysql-bin  #这个去掉就可以了，从库可以不用打开binlog
    skip-name-resolve
    server-id=2 # 唯一id
    sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    pid-file        = /var/run/mysqld/mysqld.pid
    socket          = /var/run/mysqld/mysqld.sock
    datadir         = /var/lib/mysql
    secure-file-priv= NULL
    max_connections = 10000
    lower_case_table_names=1

2、配置主从

  • master
CREATE USER 'slave-1'@'%' IDENTIFIED WITH mysql_native_password BY 'pJ4dF2oS3gN4eY4l';
grant replication slave on *.* to 'slave-1'@'%' ;
CREATE USER 'slave-2'@'%' IDENTIFIED WITH mysql_native_password BY 'pJ4dF2oS3gN4eY4l';
grant replication slave on *.* to 'slave-2'@'%' ;
flush privileges;
show master status;
  • slave-1
change master to master_host='192.168.2.60',master_port=30190,master_user='slave-1',master_password='pJ4dF2oS3gN4eY4l',
         master_log_file='mysql-bin.000003',master_log_pos=1325;
start slave;
show slave status
  • slave-2
change master to master_host='192.168.2.60',master_port=30190,master_user='slave-2',master_password='pJ4dF2oS3gN4eY4l',
         master_log_file='mysql-bin.000003',master_log_pos=1325;
start slave;
show slave status

二、准备mycat2

官网：http://dl.mycat.org.cn/2.0/

1、源码打包镜像

FROM openjdk:8-jre
 
ENV AUTO_RUN_DIR ./mycat2
ENV DEPENDENCE_FILE mycat2-1.22-release-jar-with-dependencies.jar
ENV TEMPLATE_FILE mycat2-install-template-1.21.zip
 
RUN sed -i "s@http://.*archive.ubuntu.com@http://mirrors.aliyun.com@g" /etc/apt/sources.list
RUN sed -i "s@http://.*security.ubuntu.com@http://mirrors.aliyun.com@g" /etc/apt/sources.list
 
RUN buildDeps='procps wget unzip' \
&& apt-get update \
&& apt-get install -y $buildDeps
 
# 安装地址 http://dl.mycat.org.cn/2.0/
# http://dl.mycat.org.cn/2.0/1.22-release/
# http://dl.mycat.org.cn/2.0/install-template/
RUN wget -P $AUTO_RUN_DIR/ http://dl.mycat.org.cn/2.0/1.22-release/$DEPENDENCE_FILE \
&& wget -P $AUTO_RUN_DIR/ http://dl.mycat.org.cn/2.0/install-template/$TEMPLATE_FILE
 
RUN cd $AUTO_RUN_DIR/ \
&& unzip $TEMPLATE_FILE \
&& ls -al . \
&& mv $DEPENDENCE_FILE mycat/lib/ \
&& chmod +x mycat/bin/* \
&& chmod 755 mycat/lib/* \
&& mv mycat /usr/local
 
#copy mycat /usr/local/mycat/
VOLUME /usr/local/mycat/conf
VOLUME /usr/local/mycat/logs
 
EXPOSE 8066 1984
CMD ["/usr/local/mycat/bin/mycat", "console"]

2、编译

docker build -t mycat2:1.22 .

3、复制配置

docker run -d --name=mycat2 -p 8066:8066 -p 1984:1984 mycat2:1.22
 
# 复制容器内配置
docker cp mycat2:/usr/local/mycat/conf .
docker cp mycat2:/usr/local/mycat/logs .

4、修改配置

此操作只是为了启动mycat,所以只需要配置一个数据源,可以让mycat启动即可,数据源和集群配置,后续配置

.
├── conf
│   ├── clusters
│   │   └── prototype.cluster.json
│   ├── datasources
│   │   └── prototypeDs.datasource.json
│   ├── dbseq.sql
│   ├── logback.xml
│   ├── mycat.lock
│   ├── schemas
│   │   ├── information_schema.schema.json
│   │   └── mysql.schema.json
│   ├── sequences
│   ├── server.json
│   ├── simplelogger.properties
│   ├── sql
│   │   ├── db1.sql
│   │   ├── db2.sql
│   │   ├── db3.sql
│   │   ├── describe_testdb_address.sql
│   │   ├── describe_testdb_travelrecord.sql
│   │   ├── show_databases.sql
│   │   ├── show_full_tables_from_testdb2.sql
│   │   └── show_full_tables_from_testdb.sql
│   ├── sqlcaches
│   ├── state.json
│   ├── users
│   │   └── root.user.json
│   ├── version.txt
│   └── wrapper.conf
├── Dockerfile
└── logs
    ├── mycat.pid
    └── wrapper.log

需要操作的配置文件:clusters,datasources,schemas,users其他配置可以不用动，修改顺序

datasources/prototypeDs.datasource.json

{
    "dbType":"mysql",
    "idleTimeout":60000,
    "initSqls":[],
    "initSqlsGetConnection":true,
    "instanceType":"WRITE", 
    "maxCon":1000,
    "maxConnectTimeout":3000,
    "maxRetryCount":5,
    "minCon":1,
    "name":"prototypeDs",    
    "password":"123456",
    "type":"JDBC",     
    "url":"jdbc:mysql://192.168.2.60:30190/mysql?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
    "user":"root", 
    "weight":0
}

instanceType ： READ 只读，WRITE 只写，READ_WRITE 读写
name:数据源的名字，最好唯一有代表性,默认配置不用改
user：写权限用户，因为mycat会进行写操作，所以最好是写权限用户。
password：用户密码
url：只需要改IP和端口，只用于连接mysql，不写库都可以
其他可以根据需求进行修改

clusters/prototype.cluster.json

{
        // 集群类型：SINGLE_NODE（单节点）、MASTER_SLAVE（普通主从）、GARELA_CLUSTER（garela cluster/PXC集群）等
        "clusterType":"MASTER_SLAVE",
        "heartbeat":{
                "heartbeatTimeout":1000,
                "maxRetry":3,
                "minSwitchTimeInterval":300,
                "slaveThreshold":0
        },
        "masters":[
                // 主节点数据源名称
                "prototypeDs",
        ],
        // 只有主节点则从节点可以不配置
        "replicas":[
                // 从节点数据源名称
        ],
        "maxCon":200,
        // 集群名称。在后面配置物理库（schema）时会用到
        "name":"prototype",
        // 查询负载均衡策略
        "readBalanceType":"BALANCE_ALL_READ",
        // NOT_SWITCH（不进行主从切换）、SWITCH（进行主从切换）
        "switchType":"SWITCH"
}

readBalanceType 查询负载均衡策略
    可选值:
    BALANCE_ALL(默认值) #获取集群中所有数据源
    BALANCE_ALL_READ #获取集群中允许读的数据源
    BALANCE_READ_WRITE #获取集群中允许读写的数据源,但允许读的数据源优先
    BALANCE_NONE #获取集群中允许写数据源,即主节点中选择
    switchType
        NOT_SWITCH:不进行主从切换
        SWITCH:进行主从切换

5、启动服务

docker run -d --name=mycat2 -p 8066:8066 -p 1984:1984 -v $PWD/conf:/usr/local/mycat/conf -v $PWD/logs:/usr/local/mycat/logs mycat2:1.22

6、连接数据库

端口号为8066，用户名密码为users/root.user.json配置，链接上去之后，执行一下sql

  • 查询已配置数据源
/*+ mycat:showDataSources{} */;
  • 新增数据源
/*+ mycat:createDatasource{"dbType":"mysql","idleTimeout":60000,"initSqls":[],"initSqlsGetConnection":true,"instanceType":"WRITE","maxCon":1000,"maxConnectTimeout":3000,"maxRetryCount":5,"minCon":1,"name":"master","password":"SgQHvsy9M7LWKBCz","type":"JDBC","url":"jdbc:mysql://mysql-master:3306/mysql?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8","user":"root","weight":0} */;

/*+ mycat:createDatasource{"dbType":"mysql","idleTimeout":60000,"initSqls":[],"initSqlsGetConnection":true,"instanceType":"READ","maxCon":1000,"maxConnectTimeout":3000,"maxRetryCount":5,"minCon":1,"name":"slave-1","password":"SgQHvsy9M7LWKBCz","type":"JDBC","url":"jdbc:mysql://mysql-slave-1:3306/mysql?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8","user":"root","weight":0} */;

/*+ mycat:createDatasource{"dbType":"mysql","idleTimeout":60000,"initSqls":[],"initSqlsGetConnection":true,"instanceType":"READ","maxCon":1000,"maxConnectTimeout":3000,"maxRetryCount":5,"minCon":1,"name":"slave-2","password":"SgQHvsy9M7LWKBCz","type":"JDBC","url":"jdbc:mysql://mysql-slave-2:3306/mysql?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8","user":"root","weight":0} */;

创建完成之后，执行查询数据源sql，就会出现3个数据源

  • 创建集群
/*+ mycat:createCluster{"name":"prototype","masters":["master"],"replicas":["slave-1","slave-2"],"readBalanceType":"BALANCE_ALL_READ"} */;

多个节点逗号隔开，执行完成后执行查询集群名

/*+ mycat:showClusters{} */;
  • 创建逻辑库
create database test;

如果主从做好之后，那么所有的主从数据库都会创建这个数据库

  • 修改配置文件

绑定集群
schemas/test.schema.json
只需要加一句"targetName":"prototype"就可以了

    "schemaName":"test",
    "shardingTables":{},
    "targetName":"prototype",
    "views":{}

---
apiVersion: apps/v1
kind: Deployment
metadata:
  annotations: {}
  labels:
    k8s.kuboard.cn/name: mycat2
  name: mycat2
  namespace: middleware
spec:
  progressDeadlineSeconds: 600
  replicas: 1
  revisionHistoryLimit: 10
  selector:
    matchLabels:
      k8s.kuboard.cn/name: mycat2
  strategy:
    rollingUpdate:
      maxSurge: 25%
      maxUnavailable: 25%
    type: RollingUpdate
  template:
    metadata:
      creationTimestamp: null
      labels:
        k8s.kuboard.cn/name: mycat2
    spec:
      containers:
        - image: 'xxxxxxxxxxx/middleware/mycat2:1.22'
          imagePullPolicy: IfNotPresent
          name: mycat2
          resources: {}
          terminationMessagePath: /dev/termination-log
          terminationMessagePolicy: File
          volumeMounts:
            - mountPath: /usr/local/mycat/conf
              name: conf
      dnsPolicy: ClusterFirst
      restartPolicy: Always
      schedulerName: default-scheduler
      securityContext: {}
      terminationGracePeriodSeconds: 30
      volumes:
        - name: conf
          nfs:
            path: /data/project/mycat/conf
            server: 172.16.0.46

---
apiVersion: v1
kind: Service
metadata:
  annotations: {}
  labels:
    k8s.kuboard.cn/name: mycat2
  name: mycat2
  namespace: middleware
spec:
  externalTrafficPolicy: Cluster
  internalTrafficPolicy: Cluster
  ipFamilies:
    - IPv4
  ipFamilyPolicy: SingleStack
  ports:
    - name: mysql
      nodePort: 30495
      port: 8066
      protocol: TCP
      targetPort: 8066
  selector:
    k8s.kuboard.cn/name: mycat2
  sessionAffinity: None
  type: NodePort

到此部署结束了。

3、接入prometheus

官方网文档：https://www.yuque.com/ccazhw/ml3nkf/gwx15x

  • server.json
{
....
  "properties":{"prometheusPort":7066},
....
}
©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容