obdumper导出数据时将其它数据库的表组tablegroup也一同导出了

【 使用环境 】测试环境
【 OB or 其他组件 】obdumper
【 使用版本 】 4.2.0-RELEASE
【问题描述】在执行db2数据库导出时,发现会将db1数据库的tablegroup也导出。这样是否合理?
【复现路径】


-- 在数据库里面,可以看到tg_test12属于db1数据库
obclient [db2]> show tablegroups like 'tg_test12';
+-----------------+------------+---------------+
| Tablegroup_name | Table_name | Database_name |
+-----------------+------------+---------------+
| tg_test12       | test01     | db1           |
| tg_test12       | test02     | db1           |
+-----------------+------------+---------------+
2 rows in set (0.007 sec)

-- 执行db2数据库的导出,发现将db1下面的tg_test12也导出了
[root@ob-single bin]# ./obdumper --host=192.168.10.91 --port=2883 --cluster=oceanbasetest --tenant=tenant_test01 --database=d                                                                                                                                                               b2 --user=lijiaman --password=lijiaman --all --ddl --sql --retain-empty-files --weak-read --thread 4 --block-size 1024MB --fi                                                                                                                                                                le-path='/root/backup'
2023-05-18 16:11:46 [INFO] Parsed args:
[--ddl] true
[--sql] true
[--file-path] /root/backup
[--host] 192.168.10.91
[--port] 2883
[--user] lijiaman
[--tenant] tenant_test01
[--cluster] oceanbasetest
[--password] ******
[--database] db2
[--sys-user] root
[--all] true
[--weak-read] true
[--retain-empty-files] true
[--block-size] 1024MB

2023-05-18 16:11:47 [INFO] Load jdbc driver class: "org.mariadb.jdbc.Driver" finished
2023-05-18 16:11:47 [WARN] No VIEW are exist in the schema: "db2"
2023-05-18 16:11:47 [WARN] No SEQUENCE are exist in the schema: "db2"
2023-05-18 16:11:47 [WARN] No FUNCTION are exist in the schema: "db2"
2023-05-18 16:11:47 [WARN] No PROCEDURE are exist in the schema: "db2"
2023-05-18 16:11:47 [INFO] Generate 2 dump tasks finished. Total Elapsed: 4.852 ms
2023-05-18 16:11:47 [INFO] Start 3 schema dump threads for 2 dump tasks finished.
2023-05-18 16:11:47 [INFO] Return the latest compatible version: 4.1.0.0 -> 4.0.0.0
2023-05-18 16:11:47 [INFO] Return the latest compatible version: 4.1.0.0 -> 4.0.0.0
2023-05-18 16:11:47 [INFO] Dump create objects success. DbType: OBMYSQL Version: 4.1.0.0
2023-05-18 16:11:47 [INFO] Dump create objects success. DbType: OBMYSQL Version: 4.1.0.0
2023-05-18 16:11:47 [INFO] ObMySql(4.1.0.0) is older than 4.0 ? false
2023-05-18 16:11:47 [INFO] ObMySql(4.1.0.0) is older than 4.0 ? false
2023-05-18 16:11:47 [INFO] Load meta/obmysql/obmysql14x.xml, meta/obmysql/obmysql22x.xml, meta/obmysql/obmysql2271.xml, meta/                                                                                                                                                                obmysql/obmysql3230.xml, meta/obmysql/obmysql40x.xml successed
2023-05-18 16:11:47 [INFO] Load meta/obmysql/obmysql14x.xml, meta/obmysql/obmysql22x.xml, meta/obmysql/obmysql2271.xml, meta/                                                                                                                                                                obmysql/obmysql3230.xml, meta/obmysql/obmysql40x.xml successed
2023-05-18 16:11:47 [INFO] Query 0 dependencies elapsed 44.96 ms
2023-05-18 16:11:47 [INFO] Query 0 dependencies elapsed 6.806 ms
2023-05-18 16:11:47 [INFO] Query 1 tablegroups elapsed 17.55 ms
2023-05-18 16:11:47 [WARN] c.o.t.l.s.o.ObMySqlDatabase does't implement acquireTablespaceMapping()
2023-05-18 16:11:47 [WARN] c.o.t.l.s.o.ObMySqlDatabase does't implement acquireSynonymMapping()
2023-05-18 16:11:47 [WARN] c.o.t.l.s.o.ObMySqlDatabase does't implement acquireTypeMapping()
2023-05-18 16:11:47 [WARN] c.o.t.l.s.o.ObMySqlDatabase does't implement acquireTypeBodyMapping()
2023-05-18 16:11:47 [WARN] c.o.t.l.s.o.ObMySqlDatabase does't implement acquirePackageMapping()
2023-05-18 16:11:47 [WARN] c.o.t.l.s.o.ObMySqlDatabase does't implement acquirePackageBodyMapping()
2023-05-18 16:11:47 [WARN] c.o.t.l.s.o.ObMySqlDatabase does't implement acquireTriggerMapping()
2023-05-18 16:11:47 [WARN] c.o.t.l.s.o.ObMySqlDatabase does't implement acquireDatabaseLinkMapping()
2023-05-18 16:11:47 [INFO] Dump [TABLEGROUP] tg_test12 to "/root/backup/data/db2/TABLEGROUP/tg_test12-schema.sql " finished
2023-05-18 16:11:47 [INFO] No.1 It has dumped 1 TABLE_GROUPs finished. Remain: 0
2023-05-18 16:11:47 [INFO] Total dumped 1 TABLE_GROUPs finished. Elapsed: 171.2 ms
2023-05-18 16:11:47 [INFO] Dump the ddl of schema: "db2" finished
2023-05-18 16:11:47 [INFO] Query table: "db2_t1" attr finished. Remain: 0
2023-05-18 16:11:47 [INFO] Query 1 tables elapsed 262.3 ms
2023-05-18 16:11:47 [WARN] c.o.t.l.s.o.ObMySqlDatabase does't implement acquireTablespaceMapping()
2023-05-18 16:11:47 [WARN] c.o.t.l.s.o.ObMySqlDatabase does't implement acquireSynonymMapping()
2023-05-18 16:11:47 [WARN] c.o.t.l.s.o.ObMySqlDatabase does't implement acquireTypeMapping()
2023-05-18 16:11:47 [WARN] c.o.t.l.s.o.ObMySqlDatabase does't implement acquireTypeBodyMapping()
2023-05-18 16:11:47 [WARN] c.o.t.l.s.o.ObMySqlDatabase does't implement acquirePackageMapping()
2023-05-18 16:11:47 [WARN] c.o.t.l.s.o.ObMySqlDatabase does't implement acquirePackageBodyMapping()
2023-05-18 16:11:47 [WARN] c.o.t.l.s.o.ObMySqlDatabase does't implement acquireTriggerMapping()
2023-05-18 16:11:47 [WARN] c.o.t.l.s.o.ObMySqlDatabase does't implement acquireDatabaseLinkMapping()
2023-05-18 16:11:47 [INFO] Dump [TABLE] db2_t1 to "/root/backup/data/db2/TABLE/db2_t1-schema.sql " finished
2023-05-18 16:11:47 [INFO] No.1 It has dumped 1 TABLEs finished. Remain: 0
2023-05-18 16:11:47 [INFO] Total dumped 1 TABLEs finished. Elapsed: 393.8 ms
2023-05-18 16:11:47 [INFO] Dump the ddl of schema: "db2" finished
2023-05-18 16:11:48 [INFO] Close connection count: 24 of the DataSource. Key: BIZ
2023-05-18 16:11:48 [INFO] Shutdown task context finished
2023-05-18 16:11:48 [INFO] ----------   Finished Tasks: 2       Running Tasks: 0        Progress: 100.00%       ----------
2023-05-18 16:11:48 [INFO]

All Dump Tasks Finished:

----------------------------------------------------------------------------------------------------------------------------
        No.#        |        Type        |             Name             |            Count             |       Status
----------------------------------------------------------------------------------------------------------------------------
         1          |       TABLE        |            db2_t1            |            1 -> 1            |      SUCCESS
         2          |     TABLEGROUP     |          tg_test12           |            1 -> 1            |      SUCCESS
----------------------------------------------------------------------------------------------------------------------------

Total Count: 2          End Time: 2023-05-18 16:11:48


2023-05-18 16:11:48 [INFO] Dump schema finished. Total Elapsed: 1.064 s
2023-05-18 16:11:48 [INFO] Query partition names for table: "db2_t1" success. (Non-partitioned)
2023-05-18 16:11:48 [INFO] Try to query unique keys for table: "db2_t1".... (But no-primary constraints).
2023-05-18 16:11:48 [INFO] Query unique keys for table: "db2_t1" success. (No-unique constraints). Elapsed: 6.936 ms
2023-05-18 16:11:48 [INFO] Query table entry for table: "db2_t1" success. Remain: 0. Elapsed: 1.666 ms
2023-05-18 16:11:48 [INFO] Query all table entries success. Total: 1. Elapsed: 48.35 ms
2023-05-18 16:11:48 [INFO] Split rows for non-partitioned table(without primary key): "db2_t1" success. Ranges: 1
2023-05-18 16:11:48 [INFO] Generate 1 dump tasks finished. Total Elapsed: 21.32 ms
2023-05-18 16:11:48 [INFO] Start 4 record dump threads for 1 dump tasks finished
2023-05-18 16:11:48 [INFO] Dump 1 rows db2.db2_t1 to "/root/backup/data/db2/TABLE/db2_t1.1.*.sql" finished
2023-05-18 16:11:49 [INFO] Close connection count: 5 of the DataSource. Key: BIZ
2023-05-18 16:11:49 [INFO] Shutdown task context finished
2023-05-18 16:11:49 [INFO] ----------   Finished Tasks: 1       Running Tasks: 0        Progress: 100.00%       ----------
2023-05-18 16:11:49 [INFO]

All Dump Tasks Finished:

----------------------------------------------------------------------------------------------------------------------------
        No.#        |        Type        |             Name             |            Count             |       Status
----------------------------------------------------------------------------------------------------------------------------
         1          |       TABLE        |            db2_t1            |              1               |      SUCCESS
----------------------------------------------------------------------------------------------------------------------------

Total Count: 1          End Time: 2023-05-18 16:11:49


2023-05-18 16:11:49 [INFO] Dump record finished. Total Elapsed: 1.129 s
2023-05-18 16:11:49 [INFO] System exit 0

-- 操作系统文件
[root@ob-single backup]# ls
data  logs
[root@ob-single backup]# cd data/
[root@ob-single data]# ls
db2  dump.ckpt
[root@ob-single data]# tree
.
├── db2
│   ├── TABLE
│   │   ├── db2_t1.1.0.sql
│   │   └── db2_t1-schema.sql
│   └── TABLEGROUP
│       └── tg_test12-schema.sql
└── dump.ckpt

3 directories, 4 files


【附件】

tg_test12和db2_t1之间是否有关联呢?比如存在外键约束?

没有任何联系的。

[root@ob-single ~]# obclient -uroot@tenant_test01#oceanbasetest -paaAA11__ -P2883 -h192.168.10.91
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 16
Server version: OceanBase_CE 4.1.0.0 (r100000202023040520-0765e69043c31bf86e83b5d618db0530cf31b707) (Built Apr  5 2023 20:26:14)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

obclient [(none)]>
obclient [(none)]>
obclient [(none)]>
obclient [(none)]> use db2
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
obclient [db2]>
obclient [db2]>
obclient [db2]> show tables;
+---------------+
| Tables_in_db2 |
+---------------+
| db2_t1        |
+---------------+
1 row in set (0.002 sec)

obclient [db2]> show create table db2_t1 \G
*************************** 1. row ***************************
       Table: db2_t1
Create Table: CREATE TABLE `db2_t1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(200) DEFAULT NULL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
1 row in set (0.005 sec)

obclient [db2]>

另外还发现一个问题,我把数据库db1删除了,发线db1里面创建的tablegroup并没有删除。数据库版本:社区版4.1

麻烦贴一下截图吧。也可能删除db后删除table有一些延时,过几分钟看看还在吗?

这是一个 feature,obdumper 认为 Table Group 属于租户集的对象。可以通过 “show tablegroups” 查看所有的 Table Group,obdumper 会导出除了名为 ‘oceanbase’ 之外的所有表组。

原来是这样,明白了

–sys-user,–sys-password 去掉这两个选项,用–no-sys选项再导出试试。

我用的4.1版本的数据库,导出脚本放在上面了,没有使用--sys-user--sys-password选项,我刚刚尝试加了--no-sys选项,还是一样的

tablegroup(表组) 是另外一个逻辑概念,它与schema没有什么关系。可以选择导出指定的表组,例如:–table-group ‘xxx’。也可以导出所有的表组。

1 个赞

这两个选项是启用 sys 租户查询系统表,从而获取对象定义元数据。对于 observer 4.0 及以上版本,obdumper 没有对 sys 租户的要求了,这两个选项对当前问题是无关紧要的。

1 个赞

了解了

1 个赞

明白,但是有个尴尬的地方:我在导出的时候,默认导出某个数据库db1,然后执行drop database db1;create database db1,重新导入db1数据库,会报错tablegroup已经存在。

有两种解决方式,你可以根据使用场景来判断哪种更合适。

  1. 导出时不要选择 --all,而是手动指定你需要的对象,如 --table=’’ --view=’’, etc.
  2. 添加命令行选项 --replace-object,指替换掉原有的数据库对象。

好的,感谢,我试试看哈

确定是feature么?感觉并不完全合理,我只是需要表A的数据,并不需要他同组的其他表数据啊,如果同表组的数据量很大,这样都导出就会很耗时

对于导出表组,这里的场景是导出其对象定义(即 DDL),而非导出相关表的数据,也并不会导出相关表的对象定义,除非您显式指定 --table 或 --all 选项。

了解了!