【数据库中间件】数据归档

2019独角兽企业重金招聘Python工程师标准>>>

版本规划

v1.0

dbnode_tables.list 表规则配置信息

#模块    表名    数据保留天数    转出日期字段    转入历史表名    是否分库[1:分库0:未分库]    描述

如：ams  t_ams_test  5                date_time      t_ams_test_his   0                           ams测试表

模块	表名	数据保留天数	转出日期字段	转入历史表名	是否分库 [1:分库0:未分库]	描述

 

v2.0

dbnode_tables.list

#模块    表名              归档类型    归档周期      归档日期字段       归档历史表    转入历史表名    是否分库[1:分库0:未分库]    描述

                (按天DAY、按月MOUNTH、按年YEAR)

归档设计

Liunx 操作系统定时吊起Shell脚本

 

数据归档-Shell脚本整体设计

两层循环：

第一层为遍历数据库节点

第二层为遍历数据库节点对应的表规则，根据表规则进行数据迁移

 

MySQL

sh_data_node_datatrans_sit.sh

#!/bin/bash
#set -x
###########################################################
# 模块: sh_data_node_datatrans.sh
# 作者: Fx_demon
# 修改记录
# 日期
# 修改人
# 修改描述 《数据归档》
################################################################
 
filePath=/opt/datatrans

currDate=`date +"%Y%m%d"`

while read line
do
   dbip=`echo $line | awk -F ' ' '{print $1}'`
   dbport=`echo $line | awk -F ' ' '{print $2}'`
   dbname=`echo $line | awk -F ' ' '{print $3}'`
   dbpwd=`echo $line | awk -F ' ' '{print $4}'`
   dbsch=`echo $line | awk -F ' ' '{print $5}'`
   dbnode=`echo $line | awk -F ' ' '{print $6}'`
   
   echo "==========================================================================================" >> $filePath/logs/execute_$currDate.log
   echo " 节点执行信息： mysql -h $dbip -P${dbport} -u${dbname} -p${dbpwd} ${dbsch}"
   echo "=========================================================================================="  >> $filePath/logs/execute_$currDate.log  
   
    while read line
     do
       moduleName=`echo $line | awk -F ' ' '{print $1}'`
       moduleTable=`echo $line | awk -F ' ' '{print $2}'`
       day=`echo $line | awk -F ' ' '{print $3}'`
       transFiled=`echo $line | awk -F ' ' '{print $4}'`
       moduleHisTable=`echo $line | awk -F ' ' '{print $5}'`
       moduleIsShare=`echo $line | awk -F ' ' '{print $6}'`
       moduleDes=`echo $line | awk -F ' ' '{print $7}'`
        
        echo "==========================================================================================" >> $filePath/logs/execute_$currDate.log
        echo " 归档数据表信息：模块：${moduleName} 表：${moduleTable}  ${day}天 ${moduleIsShare} < 1:分库0:不分库 " >> $filePath/logs/execute_$currDate.log
        echo "==========================================================================================" >> $filePath/logs/execute_$currDate.log  
        if [ $dbnode -eq 1 ]; then
            echo "==========================================================================================" >> $filePath/logs/execute_$currDate.log
            echo " 开始处理主节点${dbnode}归档数据表  " >> $filePath/logs/execute_$currDate.log
            if [ $moduleIsShare -eq 0 ]; then
                echo " 主节点表：${moduleTable} 不分库 " >> $filePath/logs/execute_$currDate.log
                echo "==========================================================================================" >> $filePath/logs/execute_$currDate.log
            else
                echo " 主节点表：${moduleTable} 分库" >> $filePath/logs/execute_$currDate.log
                echo "==========================================================================================" >> $filePath/logs/execute_$currDate.log
            fi
            
               echo "==========================================================================================" >> $filePath/logs/execute_$currDate.log
               echo ${moduleHisTable}" 创建历史表开始 "     >> $filePath/logs/execute_$currDate.log    
               runsql=" CREATE TABLE  IF NOT EXISTS  ${moduleHisTable}  LIKE  ${moduleTable}  "
               echo " 1. 在schame:" ${dbsch} ",dbnode:" ${dbnode} "上执行创建表sql:" $runsql
               runsqlExecute="${runsql}"
               #echo "mysql -h $dbip -P${dbport} -u${dbname} -p${dbpwd} ${dbsch}  -e \"${runsqlExecute}\" -N -s >$filePath/logs/${dbsch}_${moduleName}_create.log"
               mysql -h $dbip -P${dbport} -u${dbname} -p${dbpwd} ${dbsch} -e "${runsqlExecute}" -N -s >$filePath/logs/${dbsch}_${moduleName}_create.log
               echo "归档信息 - 数据库节点: ${dbnode} 模块：${moduleName} 表：${moduleTable}  ${day}天 " >> $filePath/logs/execute_$currDate.log
               
               echo  ${moduleHisTable}" 创建历史表结束 " >> $filePath/logs/execute_$currDate.log
               echo "==========================================================================================" >> $filePath/logs/execute_$currDate.log
               
               echo " 2. "${moduleTable}  " 迁移数据开始 AND 删除数据开始 " >> $filePath/logs/execute_$currDate.log
                    
                runTransDataSQL="
                        begin;
                          set @dt = now();
                              INSERT INTO $moduleHisTable  SELECT * FROM $moduleTable
                              WHERE $transFiled   
                                BETWEEN  DATE_SUB(@dt , interval $day day) AND  @dt
                                ORDER BY $transFiled ASC;
                                
                                DELETE FROM $moduleTable  WHERE $transFiled   
                                BETWEEN  DATE_SUB(@dt , interval $day day) AND  @dt
                            ORDER BY $transFiled ASC;
                        commit;"
                runTransDataSQLEND="${runTransDataSQL}"
                echo "运行归档SQL: ${runTransDataSQLEND}" >> $filePath/logs/execute_$currDate.log
                mysql -h $dbip -P${dbport} -u${dbname} -p${dbpwd} ${dbsch} -e "${runTransDataSQLEND}" -N -s >$filePath/logs/${dbsch}_${moduleName}_${moduleTable}_${dbnode}_datatrans.log
                
                if [ $? -ne 0 ]; then
                    echo "数据归档失败" >> $filePath/logs/execute_$currDate.log
                else
                    echo "数据归档成功" >> $filePath/logs/execute_$currDate.log
                fi
                                
               echo ${moduleTable}" 迁移数据结束 AND 删除数据结束 " >> $filePath/logs/execute_$currDate.log
               echo "==========================================================================================" >> $filePath/logs/execute_$currDate.log
                
        else
           echo "==========================================================================================" >> $filePath/logs/execute_$currDate.log
           if [ $moduleIsShare -eq 0 ]; then
                continue
            else
                echo " 开始处理分片节点${dbnode}归档数据表  " >> $filePath/logs/execute_$currDate.log
                echo "==========================================================================================" >> $filePath/logs/execute_$currDate.log
                echo "分片节点 ${moduleTable} 分库数据归档处理开始"
                echo "==========================================================================================" >> $filePath/logs/execute_$currDate.log
                echo ${moduleHisTable}" 创建历史表开始 "     >> $filePath/logs/execute_$currDate.log    
                runsql=" CREATE TABLE  IF NOT EXISTS  ${moduleHisTable}  LIKE  ${moduleTable}  "
                echo " 1. 在schame:" ${dbsch} ",dbnode:" ${dbnode} "上执行创建表sql:" $runsql  >> $filePath/logs/execute_$currDate.log
                runsqlExecute="${runsql}"
                #echo "mysql -h $dbip -P${dbport} -u${dbname} -p${dbpwd} ${dbsch}  -e \"${runsqlExecute}\" -N -s >$filePath/logs/${dbsch}_${moduleName}_create.log"
                mysql -h $dbip -P${dbport} -u${dbname} -p${dbpwd} ${dbsch} -e "${runsqlExecute}" -N -s >$filePath/logs/${dbsch}_${moduleName}_create.log
                echo "归档信息 - 数据库节点: ${dbnode} 模块：${moduleName} 表：${moduleTable}  ${day}天 " >> $filePath/logs/execute_$currDate.log
               
                echo  ${moduleHisTable}" 创建历史表结束 " >> $filePath/logs/execute_$currDate.log
                echo "==========================================================================================" >> $filePath/logs/execute_$currDate.log
               
                echo " 2. "${moduleTable}  " 迁移数据开始 AND 删除数据开始 " >> $filePath/logs/execute_$currDate.log
                    
                runTransDataSQL="
                        begin;
                          set @dt = now();
                              INSERT INTO $moduleHisTable  SELECT * FROM $moduleTable
                              WHERE $transFiled   
                                BETWEEN  DATE_SUB(@dt , interval $day day) AND  @dt
                                ORDER BY $transFiled ASC;
                                
                                DELETE FROM $moduleTable  WHERE $transFiled   
                                BETWEEN  DATE_SUB(@dt , interval $day day) AND  @dt
                            ORDER BY $transFiled ASC;
                        commit;"
                runTransDataSQLEND="${runTransDataSQL}"
                echo "运行归档SQL: ${runTransDataSQLEND}"  >> $filePath/logs/execute_$currDate.log
                mysql -h $dbip -P${dbport} -u${dbname} -p${dbpwd} ${dbsch} -e "${runTransDataSQLEND}" -N -s >$filePath/logs/${dbsch}_${moduleName}_${moduleTable}_${dbnode}_datatrans.log
                
                if [ $? -ne 0 ]; then
                    echo "数据归档失败"  >> $filePath/logs/execute_$currDate.log
                else
                    echo "数据归档成功"  >> $filePath/logs/execute_$currDate.log
                fi
                                
                echo ${moduleTable}" 迁移数据结束 AND 删除数据结束 " >> $filePath/logs/execute_$currDate.log
                echo "==========================================================================================" >> $filePath/logs/execute_$currDate.log
                
            fi
        fi
        
       echo "==========================================================================================" >> $filePath/logs/execute_$currDate.log       
   done <$filePath/sit_dbnode_tables.list
   echo "$dbip ${dbport} ${dbname} ${dbsch} 迁移数据结束" >> $filePath/logs/execute_$currDate.log
   echo "==========================================================================================" >> $filePath/logs/execute_$currDate.log
done <$filePath/sit_dbnode.list   

exit;

 

 

sit_dbnode.list
9.1.10.52 3307 root  123  dt  1
9.1.10.53 3307 root  123  dt  2

sit_dbnode_tables.list
ams  T_AMS_TEST1  5  MODIFY_TIME  T_AMS_TEST1_HIS  1  描述
ams  T_AMS_TEST2  6  MODIFY_TIME  T_AMS_TEST2_HIS  0  描述

测试表SQL :
CREATE TABLE T_AMS_TEST1 (
   ID  int(11) NOT NULL AUTO_INCREMENT ,
   BANK_NO  varchar(12) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
   USER_ID  char(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
   CREAT_TIME  timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
   MODIFY_TIME  timestamp NOT NULL   ,
   REMARK1  varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
   REMARK2  varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
     PRIMARY KEY (ID)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
COMMENT='测试'
AUTO_INCREMENT=0
ROW_FORMAT=DYNAMIC
;

INSERT INTO t_ams_test1 (BANK_NO, USER_ID,CREAT_TIME,MODIFY_TIME,REMARK1,REMARK2)
VALUES ( '1', '1', '2017-12-20 16:34:45', '2017-12-20 16:34:48', '11', '22');

转载于:https://my.oschina.net/fxdemon/blog/1593320