Linux Shell脚本案例：分批删除MySQL中上亿记录大表数据并归档

原创

a772304419 2024-10-20 05:55:05 博主文章分类：Linux ©著作权

文章标签 linux mysql 运维 SQL 数据 文章分类 HarmonyOS 后端开发

©著作权归作者所有：来自51CTO博客作者a772304419的原创作品，请联系作者获取转载授权，否则将追究法律责任

对于一个包含上亿条记录的大型表，直接删除大量数据可能会导致性能问题，如长时间锁定表、事务日志过大等。为了提高删除操作的性能并减少对系统的影响，可以采用分批处理的方法，并结合一些优化技巧。以下是一个详细的Shell脚本示例，用于分批处理和删除数据。

Shell脚本示例

#!/bin/bash

# 数据库连接信息
DB_HOST="localhost"
DB_PORT="3306"
DB_NAME="your_database_name"
DB_USER="your_username"
DB_PASSWORD="your_password"
BATCH_SIZE=10000  # 每批次处理的数据量
MAX_ATTEMPTS=5   # 最大尝试次数
SLEEP_INTERVAL=10 # 每次尝试之间的等待时间（秒）

# 临时SQL文件
SQL_FILE="/tmp/hme_eo_job_sn_operations.sql"

# 创建临时表
cat > $SQL_FILE <<EOF
CREATE TABLE IF NOT EXISTS hme_eo_job_sn_split LIKE hme_eo_job_sn;
EOF

mysql -h $DB_HOST -P $DB_PORT -u $DB_USER -p$DB_PASSWORD $DB_NAME < $SQL_FILE

# 分批处理
while true; do
  # 创建临时SQL文件
  cat > $SQL_FILE <<EOF
  -- 开始一个事务
  START TRANSACTION;

  -- 插入一批数据
  INSERT INTO hme_eo_job_sn_split SELECT * FROM hme_eo_job_sn
  WHERE creation_date < '2024-01-01'
  LIMIT $BATCH_SIZE;

  -- 删除这批数据
  DELETE FROM hme_eo_job_sn WHERE creation_date < '2024-01-01' LIMIT $BATCH_SIZE；

  -- 提交事务
  COMMIT;
EOF

  # 执行SQL文件
  for attempt in $(seq 1 $MAX_ATTEMPTS); do
    mysql -h $DB_HOST -P $DB_PORT -u $DB_USER -p$DB_PASSWORD $DB_NAME < $SQL_FILE
    if [ $? -eq 0 ]; then
      break
    else
      echo "Attempt $attempt failed, retrying in $SLEEP_INTERVAL seconds..."
      sleep $SLEEP_INTERVAL
    fi
  done

  # 检查是否还有数据需要处理
  cat > $SQL_FILE <<EOF
  SELECT COUNT(*) AS count FROM hme_eo_job_sn WHERE creation_date < '2024-01-01';
EOF
  ROW_COUNT=$(mysql -h $DB_HOST -P $DB_PORT -u $DB_USER -p$DB_PASSWORD $DB_NAME -sN < $SQL_FILE)

  if [ "$ROW_COUNT" -le 0 ]; then
    break
  fi
done

# 删除临时SQL文件
rm -f $SQL_FILE

echo "操作成功完成。"