MySQL大表数据迁移方法

提示

数据库版本：MySQL 8。

迁移需求

将MySQL数据库中的多个表XXXXXX迁移至XXXXXX_HISTORY表中，迁移的数据范围为三个月以前的所有数据。
其中XXXXXX表与XXXXXX_HISTORY表结构一致。如:

• MG_DATA_NW迁移至MG_DATA_NW_HISTORY;
• MG_DATA_LF迁移至MG_DATA_LF_HISTORY;
• ...

XXXXXX中存在时间字段COLLECT_TIME，并且该字段有索引。如果没有索引，则需要加索引。参考Mysql不锁表增加字段和索引方法

整体思路

1. 生成一个当前开始数据迁移的时间点(三个月前)，精确到毫秒，用变量t表示；
2. 使用mysqldump导出时间点t前的历史数据，生成sql文件。注意使用不锁表导出方法，避免影响业务数据处理；
3. 使用mysql语句导入sql文件，导入成功后，删除sql文件；导入失败，保留sql文件。
4. 使用DELETE ... LIMIT 100 语法增加t时间筛选循环删除原表数据，避免锁库；

注: 该方案默认认为不会再存在小于t时间点的数据写入源数据表

迁移脚本

#! /bin/bash
host="localhost"
port=3306
username="root"
password="123456"
db="data-processing"
tables='MG_DATA_BSW MG_DATA_CH MG_DATA_DBW MG_DATA_DW MG_DATA_HW MG_DATA_JS MG_DATA_LF MG_DATA_LSY MG_DATA_QJ MG_DATA_NW MG_DATA_SC MG_DATA_QY MG_DATA_SSW MG_DATA_TH MG_DATA_TY MG_DATA_JY MG_DATA_YL'

CURRENT_TIMESTAMP=$(date +%s)
### 三个月前的时间戳
CURRENT_TIMESTAMP=$[CURRENT_TIMESTAMP-7776000]
# CURRENT_TIMESTAMP=1677224979
echo $CURRENT_TIMESTAMP

t=$(date -d @$CURRENT_TIMESTAMP "+%Y-%m-%d %H:%M:%S")
echo "$(date "+%Y-%m-%d %H:%M:%S"): start time: ${t}"
filename="migrate_${CURRENT_TIMESTAMP}.sql"
echo "$(date "+%Y-%m-%d %H:%M:%S"): 开始备份sql文件到$filename"
## 导出数据
dumpcmd="mysqldump -h ${host} -u${username} -p${password} --databases ${db} --tables ${tables} --where \"COLLECT_TIME <= '${t}'\" -t --insert-ignore --complete-insert --single-transaction --quick > ${filename}"
#echo $dumpcmd
eval $dumpcmd
echo "$(date "+%Y-%m-%d %H:%M:%S"): 备份完成"
echo "$(date "+%Y-%m-%d %H:%M:%S"): 修改目的数据表"
## 修改目的库表
sed 's/`\(MG_DATA_[A\-Z]\+\)`/`\1_HISTORY`/g' $filename > "${filename}_rename" && mv "${filename}_rename" $filename
echo "$(date "+%Y-%m-%d %H:%M:%S"): 修改目的数据表完成"

echo "$(date "+%Y-%m-%d %H:%M:%S"): 开始导入目标数据表"
## 迁移到目标数据表
importcmd="mysql -h ${host} -u${username} -p${password} ${db} < $filename && rm -rf $filename"
eval $importcmd
echo "$(date "+%Y-%m-%d %H:%M:%S"): 导入目标表完成"
echo $CURRENT_TIMESTAMP > .ver

table_array=(${tables//\s/ })
echo "$(date "+%Y-%m-%d %H:%M:%S"): 开始清理历史数据"
for table in "${table_array[@]}"
do
    echo "$(date "+%Y-%m-%d %H:%M:%S"): 开始删除${table}历史数据"
    total=0
    line=1
    ## 循环删除，避免锁表
    while true; do
        if [ $line -gt 0 ]; then
            # 执行命令
            deletecmd="mysql -h ${host} -u${username} -p${password} --database ${db} -e \"DELETE FROM ${table} WHERE COLLECT_TIME <= '${t}' LIMIT 1000; select ROW_COUNT() \G\""
            line=$(eval $deletecmd | grep ROW_COUNT | cut -c 14-)
            total=$[line+total]
            echo "$(date "+%Y-%m-%d %H:%M:%S"): ${table}历史数据已删除${total}条"
        else
            break
        fi
    done
    echo "$(date "+%Y-%m-%d %H:%M:%S"): ${table}历史数据删除完成，总计删除${total}条数据"
done

echo "$(date "+%Y-%m-%d %H:%M:%S"): 历史数据清理完成"