gitsilence 的个人博客

两个数据库 default、test_move

数据库default中，表arrays_test_02 有数据

现在要将default中的表arrays_test_02 迁移到 数据库test_move中

首先找到 clickhouse 的数据存储目录

clickhouse@8511fc9bf0d8:~/data/default/arrays_test_02$ pwd
/var/lib/clickhouse/data/default/arrays_test_02
clickhouse@8511fc9bf0d8:~/data/default/arrays_test_02$ ls -l
total 12
drwxr-x---. 2 clickhouse clickhouse 194 Oct  8 07:38 1_1_1_0_5
drwxr-x---. 2 clickhouse clickhouse 194 Oct  8 07:38 2_2_2_0_5
drwxr-x---. 2 clickhouse clickhouse 194 Oct  8 07:38 3_3_3_0_5
drwxr-x---. 2 clickhouse clickhouse   6 Oct  8 07:25 detached
-rw-r-----. 1 clickhouse clickhouse   1 Oct  8 07:25 format_version.txt
-rw-r-----. 1 clickhouse clickhouse 145 Oct  8 07:37 mutation_4.txt
-rw-r-----. 1 clickhouse clickhouse 145 Oct  8 07:38 mutation_5.txt

将 arrays_test02 整个目录copy到 数据库test_move的目录下；cp -r arrays_test_02 /var/lib/clickhouse/data/test_move/

clickhouse@8511fc9bf0d8:~/data/default$ cp -r arrays_test_02 /var/lib/clickhouse/data/test_move/
clickhouse@8511fc9bf0d8:~/data/default$ ls /var/lib/clickhouse/data/test_move/
arrays_test_02

然后metadata目录下，数据库default目录下的 arrays_test cp arrays_test_02.sql /var/lib/clickhouse/metadata/test_move/

clickhouse@8511fc9bf0d8:~/metadata/default$ pwd
/var/lib/clickhouse/metadata/default
clickhouse@8511fc9bf0d8:~/metadata/default$ cp arrays_test_02.sql /var/lib/clickhouse/metadata/test_move/

最后 使用clickhouse-client 挂载 对应表；

clickhouse-client;
use test_move;
attach table arrays_test_02;

sclickhouse@8511fc9bf0d8:~/metadata/default$ clickhouse-client 
ClickHouse client version 21.9.4.35 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 21.9.4 revision 54449.

8511fc9bf0d8 :) use test_move;

USE test_move

Query id: e6df25a9-1db9-4e92-a8b9-83c6ae3134d2

Ok.

0 rows in set. Elapsed: 0.003 sec. 

8511fc9bf0d8 :) attach table arrays_test_02;
ATTACH TABLE arrays_test_02

Query id: 467370cb-ac4f-4975-91ee-a7d1cbeb33e0


0 rows in set. Elapsed: 0.027 sec. 

Received exception from server (version 21.9.4):
Code: 49. DB::Exception: Received from localhost:9000. DB::Exception: Mapping for table with UUID=22aba63a-dc9d-4333-a2ab-a63adc9d6333 already exists. (LOGICAL_ERROR)

这里报错原因应该是 因为 同一个clickhouse 不同数据库进行迁移 会出现这种问题；