【 使用环境 】测试环境
【 OB 】
【 使用版本 】4.0.0.0
【问题描述】
±------------------------------------------------+
| observer |
±--------------±--------±-----±------±-------+
| ip | version | port | zone | status |
±--------------±--------±-----±------±-------+
| 172.16.11.134 | 4.0.0.0 | 2881 | zone1 | ACTIVE |
| 172.16.11.135 | 4.0.0.0 | 2881 | zone2 | ACTIVE |
| 172.16.11.136 | 4.0.0.0 | 2881 | zone3 | ACTIVE |
±--------------±--------±-----±------±-------+
在以上拓扑中
请问在OB里创建10张表,这10张表是均匀分布在3台物理机吗,怎么确认每个表的主在哪台机器上?
查看某个实例(租户)下库表分区主副本的位置和大小
SELECT t.tenant_id, a.tenant_name, t.table_name, d.database_name, tg.tablegroup_name , t.part_num , t2.partition_id, t2.ZONE, t2.svr_ip , round(t2.data_size/1024/1024/1024) data_size_gb
, a.primary_zone , IF(t.locality = ‘’ OR t.locality IS NULL, a.locality, t.locality) AS locality
FROM oceanbase.__all_tenant AS a
JOIN oceanbase.__all_virtual_database AS d ON ( a.tenant_id = d.tenant_id )
JOIN oceanbase.__all_virtual_table AS t ON (t.tenant_id = d.tenant_id AND t.database_id = d.database_id)
JOIN oceanbase.__all_virtual_meta_table t2 ON (t.tenant_id = t2.tenant_id AND (t.table_id=t2.table_id OR t.tablegroup_id=t2.table_id) AND t2.ROLE IN (1) )
LEFT JOIN oceanbase.__all_virtual_tablegroup AS tg ON (t.tenant_id = tg.tenant_id and t.tablegroup_id = tg.tablegroup_id)
WHERE a.tenant_id IN (1006 ) AND t.table_type IN (3)
– AND d.database_name = ‘T_FUND60PUB’
– and table_name in (‘BMSQL_HISTORY’)
ORDER BY t.tenant_id, tg.tablegroup_name, d.database_name, t.table_name, t2.partition_id
;
备注:
role : 1 表示 主副本; 2 表示备副本
tablegroup_name 为空,表示没有用到表分组。
老师,请问如何打散表的主呢?
ERROR 1146 (42S02): Table ‘oceanbase.__all_virtual_meta_table’ doesn’t exist
root@sys租户上执行
租户上设置primary-zone,多个zone上实现打散;
表建立分区,多分区,分区会自动分布到多个zone上。
[root@172-16-11-134 ~]# obclient -h172.16.11.134 -P2883 -uroot@sys
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 39
Server version: OceanBase_CE 4.0.0.0 (r100000282022112511-dd289d2407609a88b1fcdf2be9e7c384cb8e19d0) (Built Nov 25 2022 11:58:08)
Copyright (c) 2000, 2018, OB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient [(none)]> SELECT
-> t.tenant_id, a.tenant_name,
-> t.table_name, d.database_name,
-> tg.tablegroup_name , t.part_num ,
-> t2.partition_id, t2.ZONE,
-> t2.svr_ip ,
-> round(t2.data_size/1024/1024/1024) data_size_gb
-> , a.primary_zone ,
-> IF(t.locality = ‘’ OR t.locality IS NULL, a.locality, t.locality) AS locality
-> FROM
-> oceanbase.__all_tenant AS a
-> JOIN oceanbase.__all_virtual_database AS d ON ( a.tenant_id = d.tenant_id )
-> JOIN oceanbase.__all_virtual_table AS t ON (t.tenant_id = d.tenant_id AND t.database_id = d.database_id)
-> JOIN oceanbase.__all_virtual_meta_table t2 ON (t.tenant_id = t2.tenant_id AND (t.table_id=t2.table_id OR t.tablegroup_id=t2.table_id) AND t2.ROLE IN (1) )
-> LEFT JOIN oceanbase.__all_virtual_tablegroup AS tg ON (t.tenant_id = tg.tenant_id and t.tablegroup_id = tg.tablegroup_id)
->
-> WHERE
-> t.table_type IN (3)
-> AND d.database_name = 'test'
-> ORDER BY t.tenant_id, tg.tablegroup_name, d.database_name, t.table_name, t2.partition_id
-> ;
ERROR 1146 (42S02): Table 'oceanbase.__all_virtual_meta_table' doesn't exist
老师,请问怎样在 root@sys租户上执行?
比较晚了~可能得等明天的老师了
不好意思,才看到你的是4.0版本,上边SQL是3.1.x版本的。
4.0看看这个吧
单机用户分区总数量及 Leader 分布
MySQL [oceanbase]> select svr_ip,count(1) from __all_virtual_ls_meta_table where tenant_id=1002 group by svr_ip;
+---------------+----------+
| svr_ip | count(1) |
+---------------+----------+
| 192.168.30.65 | 1 |
| 192.168.30.66 | 1 |
| 192.168.30.67 | 1 |
+---------------+----------+
3 rows in set
MySQL [oceanbase]> select svr_ip,count(1) from __all_virtual_ls_meta_table where tenant_id=1001 and role=1 group by svr_ip;
+---------------+----------+
| svr_ip | count(1) |
+---------------+----------+
| 192.168.30.65 | 5 |
+---------------+----------+
1 row in set
https://www.oceanbase.com/docs/community-observer-cn-10000000000901668
直接看这个表
谢谢之前的解答;还有请问老师,怎么挪动 role : 1 主副本到 172.16.11.135 机器上?
obclient [oceanbase]> select svr_ip,count(1) from __all_virtual_ls_meta_table where tenant_id=1002 and role=1 group by svr_ip;
+---------------+----------+
| svr_ip | count(1) |
+---------------+----------+
| 172.16.11.134 | 2 |
| 172.16.11.135 | 1 |
| 172.16.11.136 | 1 |
+---------------+----------+
4.0是以日志流进行切换的 找到你table对应的ls
alter system switch replica leader ls=xxxx server=‘xxxx:xxx’ tenant=‘xxxx’
obclient [oceanbase]> select * from __all_virtual_ls_meta_table where tenant_id=1002 and role=1;
+-----------+-------+---------------+----------+----------------------------+----------------------------+----------+------+----------------------------------------------------------------+-------------+--------------+----------------+----------------+------------------+---------+-------+----------------------+-----------+---------------+
| tenant_id | ls_id | svr_ip | svr_port | gmt_create | gmt_modified | sql_port | role | member_list | proposal_id | replica_type | replica_status | restore_status | memstore_percent | unit_id | zone | paxos_replica_number | data_size | required_size |
+-----------+-------+---------------+----------+----------------------------+----------------------------+----------+------+----------------------------------------------------------------+-------------+--------------+----------------+----------------+------------------+---------+-------+----------------------+-----------+---------------+
| 1002 | 1 | 172.16.11.134 | 2882 | 2022-12-08 16:24:25.712694 | 2022-12-11 20:27:04.529314 | 2881 | 1 | 172.16.11.134:2882:1,172.16.11.135:2882:1,172.16.11.136:2882:1 | 2 | 0 | NORMAL | 0 | 100 | 1001 | zone1 | 3 | 0 | 0 |
| 1002 | 1001 | 172.16.11.134 | 2882 | 2022-12-08 16:24:36.490841 | 2022-12-11 20:27:42.360693 | 2881 | 1 | 172.16.11.134:2882:1,172.16.11.135:2882:1,172.16.11.136:2882:1 | 3 | 0 | NORMAL | 0 | 100 | 1001 | zone1 | 3 | 0 | 0 |
| 1002 | 1002 | 172.16.11.135 | 2882 | 2022-12-08 16:24:36.591647 | 2022-12-11 20:27:41.860016 | 2881 | 1 | 172.16.11.134:2882:1,172.16.11.135:2882:1,172.16.11.136:2882:1 | 2 | 0 | NORMAL | 0 | 100 | 1002 | zone2 | 3 | 0 | 0 |
| 1002 | 1003 | 172.16.11.136 | 2882 | 2022-12-08 16:24:36.663628 | 2022-12-11 20:27:52.706417 | 2881 | 1 | 172.16.11.134:2882:1,172.16.11.135:2882:1,172.16.11.136:2882:1 | 3 | 0 | NORMAL | 0 | 100 | 1003 | zone3 | 3 | 0 | 0 |
+-----------+-------+---------------+----------+----------------------------+----------------------------+----------+------+----------------------------------------------------------------+-------------+--------------+----------------+----------------+------------------+---------+-------+----------------------+-----------+---------------+
4 rows in set (0.375 sec)
obclient [oceanbase]> alter system switch replica leader ls=1002 server='172.16.11.135:2882' tenant='1002';
ERROR 5157 (HY000): Unknown tenant
老师,table对应的ls 怎么找呢?
tenant要写租户的名字不是id
首先你可以通过CDB_OBJECTS 查询表对应的Tablet ID (DATA_OBJECT_ID)
然后你可以通过CDB_OB_TABLET_TO_LS 查询Tablet对应的LS信息
最后你可以通过CDB_OB_LS_LOCATIONS 查询对应的LS位置信息
上述每一步在对应的租户下都有DBA_xxx的视图
如果你觉得上面比较繁琐可以直接通过 DBA_OB_TABLE_LOCATIONS 或者 CDB_OB_TABLE_LOCATIONS 查询
可以了谢谢老师
alter system switch replica leader ls=1003 server=‘172.16.11.135:2882’ tenant=‘mysqltenant1’;