Tidb批量导入多个分割的SQL文件

Tidb批量导入多个分割的SQL文件

𝓓𝓸𝓷 Lv6
  2026-03-02 10:30:29 2026-03-02 10:30:29 Created   2026-03-02 10:31:12 2026-03-02 10:31:12 Updated     
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
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
(1) 查看需要恢复的备份表
[root@yhis-kube-master 20260214]# ll *.zst
-rw-r--r-- 1 root root 1674546 Mar  1 17:10 hy-yhis.drug_trace_code.0000000010000.sql.zst
-rw-r--r-- 1 root root 1736566 Mar  1 17:10 hy-yhis.drug_trace_code.0000000020000.sql.zst
-rw-r--r-- 1 root root 1961654 Mar  1 17:10 hy-yhis.drug_trace_code.0000000030000.sql.zst
-rw-r--r-- 1 root root 1943902 Mar  1 17:10 hy-yhis.drug_trace_code.0000000040000.sql.zst
-rw-r--r-- 1 root root 2238868 Mar  1 17:10 hy-yhis.drug_trace_code.0000000050000.sql.zst
-rw-r--r-- 1 root root 2168828 Mar  1 17:10 hy-yhis.drug_trace_code.0000000060000.sql.zst
-rw-r--r-- 1 root root 3288038 Mar  1 17:10 hy-yhis.drug_trace_code.0000000070000.sql.zst
-rw-r--r-- 1 root root 1895714 Mar  1 17:10 hy-yhis.drug_trace_code.0000000080000.sql.zst
-rw-r--r-- 1 root root 2423512 Mar  1 17:10 hy-yhis.drug_trace_code.0000000090000.sql.zst
-rw-r--r-- 1 root root 2194268 Mar  1 17:10 hy-yhis.drug_trace_code.0000000100000.sql.zst
-rw-r--r-- 1 root root 2295108 Mar  1 17:10 hy-yhis.drug_trace_code.0000000110000.sql.zst
-rw-r--r-- 1 root root 3054630 Mar  1 17:10 hy-yhis.drug_trace_code.0000000120000.sql.zst
-rw-r--r-- 1 root root 2974981 Mar  1 17:10 hy-yhis.drug_trace_code.0000000130000.sql.zst
-rw-r--r-- 1 root root 2962990 Mar  1 17:10 hy-yhis.drug_trace_code.0000000140000.sql.zst
-rw-r--r-- 1 root root 6657783 Mar  1 17:10 hy-yhis.drug_trace_code.0000000150000.sql.zst
-rw-r--r-- 1 root root 6234692 Mar  1 17:10 hy-yhis.drug_trace_code.0000000160000.sql.zst
-rw-r--r-- 1 root root 4733457 Mar  1 17:10 hy-yhis.drug_trace_code.0000000170000.sql.zst
-rw-r--r-- 1 root root 2121019 Mar  1 17:10 hy-yhis.drug_trace_code.0000000180000.sql.zst
-rw-r--r-- 1 root root 1076794 Mar  1 17:10 hy-yhis.drug_trace_code.0000000190000.sql.zst
-rw-r--r-- 1 root root     887 Mar  1 17:16 hy-yhis.drug_trace_code-schema.sql.zst

(2) 解压
[root@yhis-kube-master 20260214]# zstd -d *.zst

[root@yhis-kube-master 20260214]# ll
total 882436
-rw-r--r-- 1 root root 25580659 Mar  1 17:10 hy-yhis.drug_trace_code.0000000010000.sql
-rw-r--r-- 1 root root  1674546 Mar  1 17:10 hy-yhis.drug_trace_code.0000000010000.sql.zst
-rw-r--r-- 1 root root 25890899 Mar  1 17:10 hy-yhis.drug_trace_code.0000000020000.sql
-rw-r--r-- 1 root root  1736566 Mar  1 17:10 hy-yhis.drug_trace_code.0000000020000.sql.zst
-rw-r--r-- 1 root root 28769610 Mar  1 17:10 hy-yhis.drug_trace_code.0000000030000.sql
-rw-r--r-- 1 root root  1961654 Mar  1 17:10 hy-yhis.drug_trace_code.0000000030000.sql.zst
-rw-r--r-- 1 root root 28881790 Mar  1 17:10 hy-yhis.drug_trace_code.0000000040000.sql
-rw-r--r-- 1 root root  1943902 Mar  1 17:10 hy-yhis.drug_trace_code.0000000040000.sql.zst
-rw-r--r-- 1 root root 32989031 Mar  1 17:10 hy-yhis.drug_trace_code.0000000050000.sql
-rw-r--r-- 1 root root  2238868 Mar  1 17:10 hy-yhis.drug_trace_code.0000000050000.sql.zst
-rw-r--r-- 1 root root 33102763 Mar  1 17:10 hy-yhis.drug_trace_code.0000000060000.sql
-rw-r--r-- 1 root root  2168828 Mar  1 17:10 hy-yhis.drug_trace_code.0000000060000.sql.zst
-rw-r--r-- 1 root root 57804171 Mar  1 17:10 hy-yhis.drug_trace_code.0000000070000.sql
-rw-r--r-- 1 root root  3288038 Mar  1 17:10 hy-yhis.drug_trace_code.0000000070000.sql.zst
-rw-r--r-- 1 root root 32740794 Mar  1 17:10 hy-yhis.drug_trace_code.0000000080000.sql
-rw-r--r-- 1 root root  1895714 Mar  1 17:10 hy-yhis.drug_trace_code.0000000080000.sql.zst
-rw-r--r-- 1 root root 38288390 Mar  1 17:10 hy-yhis.drug_trace_code.0000000090000.sql
-rw-r--r-- 1 root root  2423512 Mar  1 17:10 hy-yhis.drug_trace_code.0000000090000.sql.zst
-rw-r--r-- 1 root root 33086222 Mar  1 17:10 hy-yhis.drug_trace_code.0000000100000.sql
-rw-r--r-- 1 root root  2194268 Mar  1 17:10 hy-yhis.drug_trace_code.0000000100000.sql.zst
-rw-r--r-- 1 root root 33427212 Mar  1 17:10 hy-yhis.drug_trace_code.0000000110000.sql
-rw-r--r-- 1 root root  2295108 Mar  1 17:10 hy-yhis.drug_trace_code.0000000110000.sql.zst
-rw-r--r-- 1 root root 43657832 Mar  1 17:10 hy-yhis.drug_trace_code.0000000120000.sql
-rw-r--r-- 1 root root  3054630 Mar  1 17:10 hy-yhis.drug_trace_code.0000000120000.sql.zst
-rw-r--r-- 1 root root 42941573 Mar  1 17:10 hy-yhis.drug_trace_code.0000000130000.sql
-rw-r--r-- 1 root root  2974981 Mar  1 17:10 hy-yhis.drug_trace_code.0000000130000.sql.zst
-rw-r--r-- 1 root root 42906005 Mar  1 17:10 hy-yhis.drug_trace_code.0000000140000.sql
-rw-r--r-- 1 root root  2962990 Mar  1 17:10 hy-yhis.drug_trace_code.0000000140000.sql.zst
-rw-r--r-- 1 root root 99747225 Mar  1 17:10 hy-yhis.drug_trace_code.0000000150000.sql
-rw-r--r-- 1 root root  6657783 Mar  1 17:10 hy-yhis.drug_trace_code.0000000150000.sql.zst
-rw-r--r-- 1 root root 99456474 Mar  1 17:10 hy-yhis.drug_trace_code.0000000160000.sql
-rw-r--r-- 1 root root  6234692 Mar  1 17:10 hy-yhis.drug_trace_code.0000000160000.sql.zst
-rw-r--r-- 1 root root 84860488 Mar  1 17:10 hy-yhis.drug_trace_code.0000000170000.sql
-rw-r--r-- 1 root root  4733457 Mar  1 17:10 hy-yhis.drug_trace_code.0000000170000.sql.zst
-rw-r--r-- 1 root root 43040016 Mar  1 17:10 hy-yhis.drug_trace_code.0000000180000.sql
-rw-r--r-- 1 root root  2121019 Mar  1 17:10 hy-yhis.drug_trace_code.0000000180000.sql.zst
-rw-r--r-- 1 root root 22651921 Mar  1 17:10 hy-yhis.drug_trace_code.0000000190000.sql
-rw-r--r-- 1 root root  1076794 Mar  1 17:10 hy-yhis.drug_trace_code.0000000190000.sql.zst
-rw-r--r-- 1 root root     2531 Mar  1 17:19 hy-yhis.drug_trace_code-schema.sql
-rw-r--r-- 1 root root      887 Mar  1 17:16 hy-yhis.drug_trace_code-schema.sql.zst

(3) 合并多个SQL文件到一个文件
方法一:
[root@1 20260214]# cat hy-yhis.drug_trace_code.*.sql > combined.sql

如果是windows系统，则使用type命令:
type *.sql > combined.sql

如果目标库表名不一样，则使用以下命令批量修改表名:
[root@1 20260214]# sed -i 's/INSERT INTO `drug_trace_code`/INSERT INTO `drug_trace_code_20260214`/g' combined.sql

方法二: 也可以使用shell脚本:
#!/bin/bash
DB_USER="root"
DB_PASS="password"
DB_NAME="test_db"
SQL_DIR="/path/to/sql_files"

for file in "$SQL_DIR"/*.sql; do
    mysql -u"$DB_USER" -p"$DB_PASS" "$DB_NAME" < "$file"
done

如果是windows系统，则使用bat脚本:
@echo off
set DB_USER=username
set DB_PASS=password
set DB_NAME=database_name
set SQL_DIR=C:\path\to\sql_files

for %%f in (%SQL_DIR%*.sql) do (
    echo 正在导入 %%f
    mysql -u%DB_USER% -p%DB_PASS% %DB_NAME% < "%%f"
)
echo 导入完成


方法三:将多个执行sql的命令写入一个sql文件

vi /tmp/all_data.sql
source /tmp/20260214/hy-yhis.drug_trace_code-schema.sql
source /tmp/20260214/hy-yhis.drug_trace_code.0000000010000.sql
source /tmp/20260214/hy-yhis.drug_trace_code.0000000020000.sql
source /tmp/20260214/hy-yhis.drug_trace_code.0000000030000.sql
......

mysql > use mydb
mysql > source all_data.sql

(4) 目标库恢复数据
mysql > use mydb
mysql > source combined.sql
  • Title: Tidb批量导入多个分割的SQL文件
  • Author: 𝓓𝓸𝓷
  • Created at : 2026-03-02 10:30:29
  • Updated at : 2026-03-02 10:31:12
  • Link: https://www.zhangdong.me/import-multiple-sql-scripts-into-tidb.html
  • License: This work is licensed under CC BY-NC-SA 4.0.
推荐文章
Tidb dumpling备份 Tidb dumpling备份 Oracle12c数据库克隆 Oracle12c数据库克隆 Tidb lightning恢复 Tidb lightning恢复
推荐文章
Tidb dumpling备份 Tidb dumpling备份 Oracle12c数据库克隆 Oracle12c数据库克隆
评论