批量恢复阿里云RDS的逻辑备份tar文件到MariaDB和MySQL5.7

Posted on: 3 5 月, 2026 5 5 月, 2026
Categories: Linux, 数据库
👁 78 次浏览

先将阿里云RDS备份下载下来，注意是逻辑备份

下载完成是一个.tar文件。

原RDS的数据库版本为MySQL5.5.18,恢复数据库版本是MariaDB

#!/bin/bash

set -euo pipefail

# 检查参数
if [ $# -lt 1 ]; then
    echo "Usage: $0 backup.tar"
    exit 1
fi

BACKUP_TAR="$1"
WORKDIR="restore_tmp"

# 清理旧目录
rm -rf "$WORKDIR"
mkdir -p "$WORKDIR"

echo "======================================"
echo "Extracting: $BACKUP_TAR"
echo "======================================"

tar -xvf "$BACKUP_TAR" -C "$WORKDIR"

cd "$WORKDIR" || exit 1

# 输入一次密码
read -s -p "Enter MariaDB root password: " DBPASS
echo

export MYSQL_PWD="$DBPASS"

echo "======================================"
echo "Start restoring..."
echo "======================================"

for f in *.sql.gz; do
    [ -f "$f" ] || continue

    db=$(echo "$f" | sed 's/_datafull_.*//')

    echo ""
    echo "--------------------------------------"
    echo "File     : $f"
    echo "Database : $db"
    echo "--------------------------------------"

    echo "Dropping old database..."
    mysql -u root -e "DROP DATABASE IF EXISTS \`$db\`;"

    echo "Creating database..."
    mysql -u root -e "CREATE DATABASE \`$db\` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;"

    echo "Importing data..."

    (
        echo "SET sql_mode='';"
        echo "SET NAMES utf8mb4;"
        echo "SET FOREIGN_KEY_CHECKS=0;"
        gunzip -c "$f"
        echo "SET FOREIGN_KEY_CHECKS=1;"
    ) | mysql -u root "$db" --force || {
        echo "ERROR: $db restore failed"
        continue
    }

    echo "OK: $db restored"
done

unset MYSQL_PWD

echo ""
echo "======================================"
echo "ALL DATABASES RESTORED"
echo "======================================"

把脚本保存成文件

nano restore.sh

Ctrl + O,回车,Ctrl + X

给脚本执行权限

chmod +x restore.sh

运行脚本

sudo ./restore.sh 你的tar文件名

备份整个MariaDB

mariadb-dump -u root -p --all-databases > alldb.sql

也可以压缩备份

mariadb-dump -u root -p --all-databases | gzip > alldb.sql.gz

下面是单独提供一个只导入.sql的备份脚本

#!/bin/bash

set -euo pipefail

# ======================================
# Alibaba Cloud RDS(MySQL5.7)
# -> Ubuntu 22.04 MariaDB Restore Script
# (SQL only version)
# ======================================

# 检查参数
if [ $# -lt 1 ]; then
    echo "Usage: $0 backup.sql"
    exit 1
fi

BACKUP_SQL="$1"

if [ ! -f "$BACKUP_SQL" ]; then
    echo "File not found: $BACKUP_SQL"
    exit 1
fi

WORKDIR="restore_tmp"

# 清理旧目录
rm -rf "$WORKDIR"
mkdir -p "$WORKDIR"

echo "======================================"
echo "Using SQL file: $BACKUP_SQL"
echo "======================================"

# 直接复制一份到工作目录（避免污染原文件）
cp "$BACKUP_SQL" "$WORKDIR/"
cd "$WORKDIR" || exit 1

SQL_FILE=$(basename "$BACKUP_SQL")

# 输入一次密码
read -s -p "Enter MariaDB root password: " DBPASS
echo

export MYSQL_PWD="$DBPASS"

echo "======================================"
echo "Start restoring..."
echo "======================================"

# 从文件名推数据库名
db=$(echo "$SQL_FILE" | sed 's/_datafull_.*//; s/\.sql$//')

echo ""
echo "--------------------------------------"
echo "File     : $SQL_FILE"
echo "Database : $db"
echo "--------------------------------------"

TMP_SQL="patched_${SQL_FILE}"

echo "Patching MySQL5.7 -> MariaDB compatibility..."

cp "$SQL_FILE" "$TMP_SQL"

# ===== 兼容修复 =====

# ROW_FORMAT 修复
sed -i 's/ROW_FORMAT=COMPACT/ROW_FORMAT=DYNAMIC/g' "$TMP_SQL"

# collation 修复
sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_general_ci/g' "$TMP_SQL"

# 删除 DEFINER
sed -i 's/DEFINER=`[^`]*`@`[^`]*`//g' "$TMP_SQL"

echo "Dropping old database..."
mysql -u root -e "DROP DATABASE IF EXISTS \`$db\`;"

echo "Creating database..."
mysql -u root -e "
    CREATE DATABASE \`$db\`
    DEFAULT CHARACTER SET utf8mb4
    COLLATE utf8mb4_general_ci;
"

echo "Importing data..."

(
    echo "SET sql_mode='NO_ENGINE_SUBSTITUTION';"
    echo "SET NAMES utf8mb4;"
    echo "SET FOREIGN_KEY_CHECKS=0;"
    cat "$TMP_SQL"
    echo "SET FOREIGN_KEY_CHECKS=1;"
) | mysql -u root "$db" --force --binary-mode=1 || {
    echo "ERROR: $db restore failed"
    rm -f "$TMP_SQL"
    exit 1
}

echo "Cleaning temp file..."
rm -f "$TMP_SQL"

unset MYSQL_PWD

echo ""
echo "======================================"
echo "RESTORE COMPLETED"
echo "======================================"

使用方法一样，先保存到.sh文件，然后设置运行权限，接着执行即可

chmod +x restore.sh

./restore.sh backup.sql

另外还提供支持MySQL5.7的脚本,导入单个.sql

#!/bin/bash

set -euo pipefail

if [ $# -lt 1 ]; then
    echo "Usage: $0 backup.sql"
    exit 1
fi

BACKUP_SQL="$1"

WORKDIR="restore_tmp"
rm -rf "$WORKDIR"
mkdir -p "$WORKDIR"

cp "$BACKUP_SQL" "$WORKDIR/"
cd "$WORKDIR" || exit 1

SQL_FILE=$(basename "$BACKUP_SQL")

read -s -p "Enter MySQL 5.7 root password: " DBPASS
echo

export MYSQL_PWD="$DBPASS"

db=$(echo "$SQL_FILE" | sed 's/_datafull_.*//; s/\.sql$//')

TMP_SQL="patched_${SQL_FILE}"

cp "$SQL_FILE" "$TMP_SQL"

echo "Patching for MySQL 5.7..."

# ===== MySQL 5.7 兼容修复 =====

sed -i 's/DEFINER=`[^`]*`@`[^`]*`//g' "$TMP_SQL"
sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_general_ci/g' "$TMP_SQL"
sed -i 's/ROW_FORMAT=COMPACT/ROW_FORMAT=DYNAMIC/g' "$TMP_SQL"

mysql -u root -e "DROP DATABASE IF EXISTS \`$db\`;"

mysql -u root -e "
CREATE DATABASE \`$db\`
DEFAULT CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;
"

(
    echo "SET sql_mode='NO_ENGINE_SUBSTITUTION';"
    echo "SET NAMES utf8mb4;"
    echo "SET FOREIGN_KEY_CHECKS=0;"
    cat "$TMP_SQL"
    echo "SET FOREIGN_KEY_CHECKS=1;"
) | mysql -u root "$db" --force --binary-mode=1

rm -f "$TMP_SQL"
unset MYSQL_PWD

echo "RESTORE DONE (MySQL 5.7)"

另外还提供支持MySQL5.7的脚本,批量导入.tar备份

#!/bin/bash

set -euo pipefail

if [ $# -lt 1 ]; then
    echo "Usage: $0 backup.tar"
    exit 1
fi

BACKUP_TAR="$1"

if [ ! -f "$BACKUP_TAR" ]; then
    echo "File not found: $BACKUP_TAR"
    exit 1
fi

WORKDIR="restore_tmp"

rm -rf "$WORKDIR"
mkdir -p "$WORKDIR"

echo "======================================"
echo "Extracting TAR: $BACKUP_TAR"
echo "======================================"

tar -xf "$BACKUP_TAR" -C "$WORKDIR"

cd "$WORKDIR" || exit 1

read -s -p "Enter MySQL 5.7 root password: " DBPASS
echo
export MYSQL_PWD="$DBPASS"

echo ""
echo "======================================"
echo "Start restoring all databases..."
echo "======================================"

for f in *.sql.gz; do

    [ -f "$f" ] || continue

    # =========================
    # 数据库名解析
    # =========================
    db=$(echo "$f" | sed 's/_datafull_.*//')

    echo ""
    echo "--------------------------------------"
    echo "File     : $f"
    echo "Database : $db"
    echo "--------------------------------------"

    TMP_SQL="${f%.gz}"

    echo "Decompressing..."
    gunzip -c "$f" > "$TMP_SQL"

    echo "Patching for MySQL 5.7..."

    # =========================
    # MySQL 5.7 兼容修复
    # =========================
    sed -i 's/DEFINER=`[^`]*`@`[^`]*`//g' "$TMP_SQL"
    sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_general_ci/g' "$TMP_SQL"
    sed -i 's/ROW_FORMAT=COMPACT/ROW_FORMAT=DYNAMIC/g' "$TMP_SQL"

    echo "Dropping database..."
    mysql -u root -e "DROP DATABASE IF EXISTS \`$db\`;"

    echo "Creating database..."
    mysql -u root -e "
        CREATE DATABASE \`$db\`
        DEFAULT CHARACTER SET utf8mb4
        COLLATE utf8mb4_general_ci;
    "

    echo "Importing..."

    (
        echo "SET sql_mode='NO_ENGINE_SUBSTITUTION';"
        echo "SET NAMES utf8mb4;"
        echo "SET FOREIGN_KEY_CHECKS=0;"
        cat "$TMP_SQL"
        echo "SET FOREIGN_KEY_CHECKS=1;"
    ) | mysql -u root "$db" --force --binary-mode=1 || {
        echo "ERROR: $db restore failed"
        rm -f "$TMP_SQL"
        continue
    }

    rm -f "$TMP_SQL"

    echo "OK: $db restored"

done

unset MYSQL_PWD

echo ""
echo "======================================"
echo "ALL DONE"
echo "======================================"

如果想备份整个MySQL数据库（逻辑备份）

mysqldump -u root -p --all-databases > alldb.sql

也可以压缩备份

mysqldump -u root -p --all-databases | gzip > alldb.sql.gz

备份整个MariaDB

mariadb-dump -u root -p --all-databases > alldb.sql

也可以压缩备份

mariadb-dump -u root -p --all-databases | gzip > alldb.sql.gz

chengcong

494