mysql MGR集群数据初始化方案

MGR（MySQL Group Replication）

mysql的一个主从备份策略，达到高可用的效果。多台mysql服务器组成一个集群（一般是单数，因为有一台宕机后需要判断是否多数机器是好的），只提供一个节点供读写其余几个节点负责从主节点请求binaryLog（主节点的SQL操作记录），同步到本地执行，达到数据结构、数据都能同步的目的。

因此，binaryLog的同步效率成为数据写入速度、单次写入大小的瓶颈。一般是145M，再大就会报提交失败异常（Error on observer while running replication hook 'before_commit'）。

数据导入策略

数据源文件是csv文件，数据间用英文逗号分隔，每条数据以换行符分隔，utf-8编码，Unix换行符。

COL1,COL2,COL3,COL4
COLA,COLB,COLC,COLD

使用mysql的load data命令导入

#!/bin/bash
mysql -h$host -u$user -P$port -p$pwd --enable-local-infile <<EOF
LOAD DATA LOCAL INFILE "csv absolutely path" # 指定csv文件的绝对路径
INTO TABLE tableName                         # 指定要插入的表名称
FILELDS TERMINATED BY ','                    # 以英文逗号作为单个字段的分隔符
LINES TERMINATED BY '\n' (@aliasA, @aliasB)  # 以换行符作为每条数据的分隔符，并为每个字段起别名
SET B=@aliasA, A=@aliasB;                    # 指定表中的列和数据文件中的列如何对应
EOF                                          # EOF需要独占一行，且需要顶格写

提交失败的问题

当单个csv文件特别大，达到几百万的时候会出现提交异常：Error on observer while running replication hook 'before_commit'。导致整个导入失败。

解决

递归的思想（D&C），通过把大文件拆成小文件，分开处理的方式。

#!/bin/bash
FILE="big file absolutely path"
if [ -f "$FILE"]
then
  split -l 200000 $FILE $TARGETPATH"tmp" -d  # 将大文件按行切成小文件，输出到$TARGETPATH，后缀是tmp -d表示用数字升序作为文件结尾
  FILES=$(ls -l "$TARGETPATH"tmp* | awk '{print $9}')  # 将切出来的文件都找出来放到FILES中
  echo "$FILES" | wc -w  # 输出一共切出多少个文件
  for f in $FILES; do
    set sql_mode='';                             # 方便整表更新
    mysql -h$host -u$user -P$port -p$pwd --enable-local-infile <<EOF
    LOAD DATA LOCAL INFILE "$f"                  # 指定csv文件的绝对路径
    INTO TABLE tableName                         # 指定要插入的表名称
    FILELDS TERMINATED BY ','                    # 以英文逗号作为单个字段的分隔符
    LINES TERMINATED BY '\n' (@aliasA, @aliasB)  # 以换行符作为每条数据的分隔符，并为每个字段起别名
    SET B=@aliasA, A=@aliasB;                    # 指定表中的列和数据文件中的列如何对应
EOF                                              # EOF需要独占一行，且需要顶格写
    echo $f"process finished"
    rm $f
    echo $f"reclaimed"
  done;
else 
  echo 'file does not exists.'
fi
 

优化：上述shell可以通过使用source命令，将变量提取到外部，创建一个配置项并引入；输出日志可以通过重定向到文本文件的方式代替控制台输出，或者使用tee -a 将日志在终端输出的同时也输出到文本文件。

数据增量迁入涉及数据回滚的问题

全量的数据写入，使用上述方案即可解决问题。如果是增量的数据导入，涉及到旧数据的，如果数据导入失误则可能导致整张表不能用。

因此，需要考虑数据导入失败，如何回退的问题。

解决方案1

当历史数据量较少，且不需要自动化处理的情况下。可以先将表备份，导出整张表数据做一个全量的备份。再进行批量导入，如果失败则truncate掉重新import即可。

解决方案2

历史数据量贼大，而导入数据量不大，且不方便备份表的情况下。可以导入正式表的同时，也导入到一张临时表，如果操作失败则可以通过连表delete的方式回滚数据。

解决方案3

历史数据量大，且导入数据量也大，也不方便数据备份的情况下。这种方式比较麻烦，在导入数据的同时也插入到一张临时表，回退时由于数据量过大，连表的方式不能删除（单次提交binlog超过145M）。

目前想到的是，将临时表数据导出成一个csv（需要和导入标准一致），再通过切文件的方式导入到一张temporary 表，再用temporary表和正式表连delete的方式解决。

或者，分批次读取临时表的主键，维护临时表查询位置的偏移量，连正式表进行删除，代码样例：

delete from t_official 
       where (PK1, PK2, PK3) 
         IN ( (select PK1, 
                      PK2, 
                      PK3 
                 from t_tmp 
             order by PK1, PK2, PK3 
                limit offsetStart, offsetEnd) a );

方案3设计流程图

整体方案

通过建立临时表(xxx_tmp)来存放单批次数据，使得单次数据量可以控制，并且可以进行加工（更新，修改），加工完成后replace into 生产表，由于临时表是通过temporary创建的，所以会自动回收。

通过建立结果集表（xxx_res）来存储增量数据，用于表示本次更新的数据。如果需要回退则有迹可循。

导入流程就是： 数据文件 --> 切成小文件 --> 循环小文件 --> 插入tmp表 --> 加工tmp表 --> 将tmp表复制到res表和生产表 

回退流程就是：res表导出成csv --> 切成小文件 --> 导入到tmp表（用完mysql会回收）--> tmp表连生产表做delete --> 每个循环完成删除对应的小文件

完整时序图