kubernetes 测试 Mariadb gtid 主从复制.
k8s 为 1个master 3个node
下载镜像 : mariadb 镜像版本是10.2.13 (此时10.3还没发布正式版)
docker pull mariadb
push到私有仓库
docker tag e1dd445713ae 192.168.91.137:5000/mariadb docker push 192.168.91.137:5000/mariadb
-v /usr/local/docker/mariadb/0/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=1 slave-parallel-threads=2 binlog-checksum=CRC32 master-verify-checksum=1 slave-sql-verify-checksum=1 server-id=141 report-port=3306 port=3306
在slave 的my.cnf中的[mysqld] 添加以下内容 server-id slave ip (第一次启动slave 时不加,待初始化好了数据库以后,第二次启动容器的时候,加入)
binlog-format=ROW log-bin=master-bin log-slave-updates=true sync-master-info=1 slave-parallel-threads=2 binlog-checksum=CRC32 master-verify-checksum=1 slave-sql-verify-checksum=1 server-id=142 report-port=3306 port=3306
master.yaml 以nodePort 方式访问
kubectl create -f master.yaml
apiVersion: extensions/v1beta1 kind: Deployment metadata: name: mariadb-master namespace: k8s-springcloud spec: replicas: 1 selector: matchLabels: app: mariadb-master template: metadata: labels: app: mariadb-master spec: nodeName: k8s-node-0 hostNetwork: true terminationGracePeriodSeconds: 60 containers: - name: mariadb-master image: 192.168.91.137:5000/mariadb env: - name: MYSQL_ROOT_PASSWORD value: root ports: - containerPort: 3306 hostPort: 3306 livenessProbe: exec: command: - mysql - -uroot - -proot - -e - "select 1" initialDelaySeconds: 10 periodSeconds: 30 timeoutSeconds: 1 readinessProbe: exec: command: - mysql - -uroot - -proot - -e - "select 1" initialDelaySeconds: 10 periodSeconds: 30 timeoutSeconds: 1 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: 3306 nodePort: 3306 targetPort: 3306
slave.yaml 以nodePort 方式访问
kubectl create -f slave.yaml
apiVersion: extensions/v1beta1 kind: Deployment metadata: name: mariadb-slave namespace: k8s-springcloud spec: replicas: 1 selector: matchLabels: app: mariadb-slave template: metadata: labels: app: mariadb-slave spec: nodeName: k8s-node-1 terminationGracePeriodSeconds: 60 containers: - name: mariadb-slave image: 192.168.91.137:5000/mariadb env: - name: MYSQL_ROOT_PASSWORD value: root ports: - containerPort: 3306 hostPort: 3307 livenessProbe: exec: command: - mysql - -uroot - -proot - -e - "select 1" initialDelaySeconds: 10 periodSeconds: 30 timeoutSeconds: 1 readinessProbe: exec: command: - mysql - -uroot - -proot - -e - "select 1" initialDelaySeconds: 10 periodSeconds: 30 timeoutSeconds: 1 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: 3307 nodePort: 3307 targetPort: 3307
进入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;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000005 | 359 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.000 sec)
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;
