bash脚本手动清空mysql表数据

---

1、bash脚本手动清空mysql表数据

#!/bin/bash# 配置区域（修改此处）
MYSQL_USER="root"
MYSQL_PASSWORD="123456"
MYSQL_HOST="localhost"
DATABASES=("hps-base:base_test_item"        # 格式：数据库名:表名1,表名2#"db2:tableA,tableB"#"db3:*"                    # *表示清空所有表
)LOG_FILE="./cleanup_$(date +%Y%m%d_%H%M%S).log"
echo "清理日志已创建: $LOG_FILE" | tee -a $LOG_FILE# 检查MySQL连接
check_mysql() {mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -h$MYSQL_HOST -e "SELECT 1;" &> /dev/nullif [ $? -ne 0 ]; thenecho "错误: 无法连接到MySQL服务器" | tee -a $LOG_FILEexit 1fi
}escape_db_name() {local db_name="$1"# 用反引号包裹整个数据库名（保留连字符等字符）echo "\`$db_name\`"
}# 清理指定数据库
cleanup_db() {local raw_db_name=$1local db_name=$(escape_db_name "$raw_db_name")  # 关键转义处理#local db_name=$1local tables_str=$2IFS=',' read -r -a tables <<< "$tables_str"  # 将逗号分隔转为数组echo "========== 正在清理数据库: $db_name ==========" | tee -a $LOG_FILE# 禁用外键检查mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -h$MYSQL_HOST -e "SET FOREIGN_KEY_CHECKS=0;" 2>> $LOG_FILEfor table in "${tables[@]}"; doif [ "$table" == "*" ]; then# 清空所有表echo "清空数据库 $db_name 所有表..." | tee -a $LOG_FILEALL_TABLES=$(mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -h$MYSQL_HOST -N -B -e "SHOW TABLES FROM $db_name")for t in $ALL_TABLES; doecho "TRUNCATE TABLE $db_name.$t" | tee -a $LOG_FILEmysql -u$MYSQL_USER -p$MYSQL_PASSWORD -h$MYSQL_HOST -e "TRUNCATE TABLE $db_name.$t" 2>> $LOG_FILEdoneelseecho "清空表 $db_name.$table" | tee -a $LOG_FILEmysql -u$MYSQL_USER -p$MYSQL_PASSWORD -h$MYSQL_HOST -e "TRUNCATE TABLE $db_name.$table" 2>> $LOG_FILEfidone# 恢复外键检查mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -h$MYSQL_HOST -e "SET FOREIGN_KEY_CHECKS=1;" 2>> $LOG_FILE
}# 主执行流程
main() {check_mysqlfor db_config in "${DATABASES[@]}"; doIFS=':' read -r db_name db_tables <<< "$db_config"  # 拆分数据库名和表名cleanup_db "$db_name" "$db_tables"doneecho "清理完成，日志保存在: $LOG_FILE" | tee -a $LOG_FILE
}main