migrate-db.sh
3.52 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
#!/usr/bin/env bash
# 把本地 parking_cloud 拷贝成 parking_cloud_yudao，并在拷贝里准备上游 system/infra/iot 表。
# 原库 parking_cloud 不会被修改。
set -euo pipefail
SRC_DB="${SRC_DB:-parking_cloud}"
DST_DB="${DST_DB:-parking_cloud_yudao}"
TMPL_DB="${TMPL_DB:-yudao_template}"
MYSQL_HOST="${MYSQL_HOST:-127.0.0.1}"
MYSQL_PORT="${MYSQL_PORT:-3306}"
MYSQL_USER="${MYSQL_USER:-root}"
MYSQL_CMD=(mysql -h"$MYSQL_HOST" -P"$MYSQL_PORT" -u"$MYSQL_USER")
DUMP_CMD=(mysqldump -h"$MYSQL_HOST" -P"$MYSQL_PORT" -u"$MYSQL_USER" --single-transaction --set-gtid-purged=OFF)
SCRIPT_DIR="$(cd "$(dirname "$0")" && pwd)"
REPO_DIR="$(dirname "$SCRIPT_DIR")"
echo "== 1/7 拷贝 $SRC_DB -> $DST_DB"
"${MYSQL_CMD[@]}" -e "DROP DATABASE IF EXISTS \`$DST_DB\`; CREATE DATABASE \`$DST_DB\` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
"${DUMP_CMD[@]}" --routines --triggers --events "$SRC_DB" | "${MYSQL_CMD[@]}" "$DST_DB"
echo "== 2/7 装载上游模板 $TMPL_DB"
"${MYSQL_CMD[@]}" -e "DROP DATABASE IF EXISTS \`$TMPL_DB\`; CREATE DATABASE \`$TMPL_DB\` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
"${MYSQL_CMD[@]}" "$TMPL_DB" < "$REPO_DIR/sql/mysql/ruoyi-vue-pro.sql"
if [ -f "$REPO_DIR/sql/mysql/quartz.sql" ]; then
"${MYSQL_CMD[@]}" "$TMPL_DB" < "$REPO_DIR/sql/mysql/quartz.sql"
fi
echo "== 3/7 拷贝里把 12 张旧 system_* 改名 pcbak_*（system_org 保留）"
"${MYSQL_CMD[@]}" "$DST_DB" -e "
RENAME TABLE
system_dict_data TO pcbak_system_dict_data,
system_dict_type TO pcbak_system_dict_type,
system_login_log TO pcbak_system_login_log,
system_menu TO pcbak_system_menu,
system_oauth2_access_token TO pcbak_system_oauth2_access_token,
system_oauth2_client TO pcbak_system_oauth2_client,
system_oauth2_refresh_token TO pcbak_system_oauth2_refresh_token,
system_operate_log TO pcbak_system_operate_log,
system_role TO pcbak_system_role,
system_role_menu TO pcbak_system_role_menu,
system_user_role TO pcbak_system_user_role,
system_users TO pcbak_system_users;
"
echo "== 4/7 从模板导入上游 system/infra/iot/QRTZ 表"
TABLES=$("${MYSQL_CMD[@]}" -N -e "SELECT GROUP_CONCAT(table_name SEPARATOR ' ') FROM information_schema.tables WHERE table_schema='$TMPL_DB' AND (table_name LIKE 'system\\_%' OR table_name LIKE 'infra\\_%' OR table_name LIKE 'iot\\_%' OR table_name LIKE 'QRTZ\\_%');")
if [ -z "$TABLES" ]; then echo "ERROR: 模板库里没找到 system/infra/iot 表"; exit 1; fi
"${DUMP_CMD[@]}" "$TMPL_DB" $TABLES | "${MYSQL_CMD[@]}" "$DST_DB"
echo "== 5/7 管理员密码沿用旧库"
"${MYSQL_CMD[@]}" "$DST_DB" -e "
UPDATE system_users u JOIN pcbak_system_users o ON o.id = 1
SET u.password = o.password,
u.nickname = COALESCE(NULLIF(o.nickname, ''), u.nickname),
u.mobile = COALESCE(NULLIF(o.mobile, ''), u.mobile)
WHERE u.id = 1;
"
echo "== 6/7 清理上游演示数据 + 迁移菜单/字典"
"${MYSQL_CMD[@]}" "$DST_DB" -e "
DELETE FROM system_user_role WHERE user_id <> 1 OR role_id <> 1;
DELETE FROM system_role_menu WHERE role_id <> 1;
DELETE FROM system_role WHERE id <> 1;
DELETE FROM system_users WHERE id <> 1;
DELETE FROM system_tenant WHERE id <> 1;
"
"${MYSQL_CMD[@]}" "$DST_DB" < "$SCRIPT_DIR/migrate-menus.sql"
echo "== 7/7 删除模板库"
"${MYSQL_CMD[@]}" -e "DROP DATABASE \`$TMPL_DB\`;"
echo "== done: $DST_DB"
"${MYSQL_CMD[@]}" -N -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='$DST_DB';"
"${MYSQL_CMD[@]}" "$DST_DB" -N -e "SELECT 'menus', COUNT(*) FROM system_menu; SELECT 'users', COUNT(*) FROM system_users; SELECT 'dict_types', COUNT(*) FROM system_dict_type;"