kubernetes 测试 Mariadb gtid 主从复制.

2022-12-01 10:34:18

k8s 为 1个master 3个node

下载镜像 : mariadb 镜像版本是10.2.13 (此时10.3还没发布正式版)

docker pull mariadb

push到私有仓库

docker tag e1dd445713ae 192.168.91.137:/mariadb

docker push 192.168.91.137:/mariadb

 -v /usr/local/docker/mariadb//conf:/etc/mysql/conf.d

挂载配置文件不靠谱,所以把容器内/etc/mysql 拷贝到glusterfs

docker cp d07feca8b0df:/etc/mysql /mnt/gv0/k8s-springcloud/mariadb/master/conf/mysql

docker cp d07feca8b0df:/etc/mysql /mnt/gv0/k8s-springcloud/mariadb/slave/conf/mysql

在master 的my.cnf中的[mysqld] 添加以下内容 server-id master ip

binlog-format=ROW

log-bin=master-bin

log-slave-updates=true

sync-master-info=

slave-parallel-threads=

binlog-checksum=CRC32

master-verify-checksum=

slave-sql-verify-checksum=

server-id=

report-port=

port=

在slave 的my.cnf中的[mysqld] 添加以下内容 server-id slave ip (第一次启动slave 时不加,待初始化好了数据库以后,第二次启动容器的时候,加入)

binlog-format=ROW

log-bin=master-bin

log-slave-updates=true

sync-master-info=

slave-parallel-threads=

binlog-checksum=CRC32

master-verify-checksum=

slave-sql-verify-checksum=

server-id=

report-port=

port=

master.yaml 以nodePort 方式访问

kubectl create -f master.yaml

apiVersion: extensions/v1beta1

kind: Deployment

metadata:

  name: mariadb-master

  namespace: k8s-springcloud

spec:

  replicas:

  selector:

    matchLabels:

      app: mariadb-master

  template:

    metadata:

      labels:

        app: mariadb-master

    spec:

      nodeName: k8s-node-

      hostNetwork: true

      terminationGracePeriodSeconds:

      containers:

      - name: mariadb-master

        image: 192.168.91.137:/mariadb

        env:

        - name: MYSQL_ROOT_PASSWORD

          value: root

        ports:

        - containerPort:

          hostPort:

        livenessProbe:

          exec:

            command:

            - mysql

            - -uroot

            - -proot

            - -e

            - "select 1"

          initialDelaySeconds:

          periodSeconds:

          timeoutSeconds:

        readinessProbe:

          exec:

            command:

            - mysql

            - -uroot

            - -proot

            - -e

            - "select 1"

          initialDelaySeconds:

          periodSeconds:

          timeoutSeconds:

        volumeMounts:

        - name: data

          mountPath: /var/lib/mysql

        - name: log

          mountPath: /var/log

        - name: conf

          mountPath: /etc/mysql

      volumes:

      - name: data

        hostPath:

          path: /mnt/gv0/k8s-springcloud/mariadb/master/data

      - name: log

        hostPath:

          path: /mnt/gv0/k8s-springcloud/mariadb/master/log

      - name: conf

        hostPath:

          path: /mnt/gv0/k8s-springcloud/mariadb/master/conf/mysql

---

apiVersion: v1

kind: Service

metadata:

  name: mariadb-master

  namespace: k8s-springcloud

  labels:

    app: mariadb-master

spec:

  type: NodePort

  ports:

  - port:

    nodePort:

    targetPort:

slave.yaml 以nodePort 方式访问

kubectl create -f slave.yaml

apiVersion: extensions/v1beta1

kind: Deployment

metadata:

  name: mariadb-slave

  namespace: k8s-springcloud

spec:

  replicas:

  selector:

    matchLabels:

      app: mariadb-slave

  template:

    metadata:

      labels:

        app: mariadb-slave

    spec:

      nodeName: k8s-node-

      terminationGracePeriodSeconds:

      containers:

      - name: mariadb-slave

        image: 192.168.91.137:/mariadb

        env:

        - name: MYSQL_ROOT_PASSWORD

          value: root

        ports:

        - containerPort:

          hostPort:

        livenessProbe:

          exec:

            command:

            - mysql

            - -uroot

            - -proot

            - -e

            - "select 1"

          initialDelaySeconds:

          periodSeconds:

          timeoutSeconds:

        readinessProbe:

          exec:

            command:

            - mysql

            - -uroot

            - -proot

            - -e

            - "select 1"

          initialDelaySeconds:

          periodSeconds:

          timeoutSeconds:

        volumeMounts:

        - name: data

          mountPath: /var/lib/mysql

        - name: log

          mountPath: /var/log

        - name: conf

          mountPath: /etc/mysql

      volumes:

      - name: data

        hostPath:

          path: /mnt/gv0/k8s-springcloud/mariadb/slave/data

      - name: log

        hostPath:

          path: /mnt/gv0/k8s-springcloud/mariadb/slave/log

      - name: conf

        hostPath:

          path: /mnt/gv0/k8s-springcloud/mariadb/slave/conf/mysql

---

apiVersion: v1

kind: Service

metadata:

  name: mariadb-slave

  namespace: k8s-springcloud

  labels:

    app: mariadb-slave

spec:

  type: NodePort

  ports:

  - port:

    nodePort:

    targetPort:

进入master 给slave 分配权限

grant replication slave,replication client on *.* to 'slave'@'%' identified by 'slave';

flush privileges;

进入slave

change master to MASTER_HOST='192.168.1.207',MASTER_USER='slave',MASTER_PASSWORD='slave',MASTER_USE_GTID=current_pos;

START SLAVE;

show slave status\G

在master创建表

-- ----------------------------

DROP TABLE IF EXISTS `tenant_user_info`;

CREATE TABLE `tenant_user_info`  (

  `id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,

  `creator` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,

  `create_time` datetime(0) DEFAULT NULL,

  `modifier` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,

  `modify_time` datetime(0) DEFAULT NULL,

  `version` int(20) DEFAULT NULL,

  `username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,

  `password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,

  `account_non_expired` bit(1) DEFAULT NULL,

  `account_non_locked` bit(1) DEFAULT NULL,

  `credentials_non_expired` bit(1) DEFAULT NULL,

  `enabled` bit(1) DEFAULT NULL,

  PRIMARY KEY (`id`) USING BTREE

) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

编写java程序,在插入过程中,直接把slave关机,然后插入结束后,看数据是否自动同步

@RunWith(SpringRunner.class)

@SpringBootTest

public class UserInfoServiceTest {

    @Resource

    private UserInfoService userInfoService;

    private ExecutorService executorService = Executors.newFixedThreadPool(20);

    @Test

    public void saveTest(){

        int dataCount = 10000;

        List<CompletableFuture<UserInfo>> futures = new ArrayList<>(dataCount);

        for(int i=0;i<dataCount;i++){

            UserInfo userInfo = new UserInfo();

            userInfo.setUsername("atho"+i);

            userInfo.setPassword("1232456");

            userInfo.setEnabled(true);

            userInfo.setCredentialsNonExpired(true);

            userInfo.setAccountNonExpired(true);

            userInfo.setAccountNonLocked(true);

                    futures.add(CompletableFuture.supplyAsync(() -> userInfoService.saveAndFlush(userInfo), executorService).exceptionally(e -> {

                        e.printStackTrace();

            return null;

        }));

        }

        futures.stream().map(CompletableFuture::join).collect(Collectors.toList()).forEach(u-> System.out.println(u.getId()));

    }

}

其中10条数据,是之前插入的

附上几个简单命令

#查看master gtid位置
select @@global.gtid_current_pos;
#查看slave gitd位置
select @@global.gtid_slave_pos;

#跳过几个错误
stop slave;
set global sql_slave_skip_counter=1;
start slave;

如果是已存在的数据库改为gtid,那么复制数据库文件到slave 以后操作

master 操作

MariaDB [(none)]> show master status;

+-------------------+----------+--------------+------------------+

slave 操作

change master to master_host='192.168.91.141', MASTER_PORT=3306,master_user='slave', master_password='slave', master_log_file='master-bin.000005', master_log_pos=359;

START SLAVE;

码农公寓

相关文章