作者:何文超

爱可生南区交付服务部 DBA 团队成员,主要负责MySQL故障处理,MySQL高可用架构改造,OceanBase相关技术支持。爱好足球,羽毛球。

本文来源:原创投稿

*爱可生开源社区出品,原创内容未经授权不得随意使用,转载请联系小编并注明来源。


OceanBase 单机环境部署可参考:
https://opensource.actionsky....

一. 租户首次使用的步骤

| 步骤 | 作用 |
| --- | --- |
| 01.创建资源单元 | 指定每个单元要使用CPU(逻辑限制)、Memory(硬限制)、IOPS(不限制)、DISK(不限制)
资源分配时不要超过__ALL_VIRTUAL_SERVER_STAT剩余的可用资源 |
| 02.创建资源池 | 资源池需要指定资源单元以及要使用的zone |
| 03.创建租户 | 创建租户指定副本数量,指定资源池,执行租户类型oracle、mysql。社区版仅支持mysql版 |
| 04.在租户上创建用户 | 用户是最终提交给终端用户使用的账号 |
| 05.提供使用 | 将账号提供给终端用户,视实际情况赋予相应权限 |

二. 创建 wms_tenant 租户(mysql类型)

创建资源单元
create resource unit wms_unit1 max_cpu=5,min_cpu=2,memory_size='2G';
创建资源池
create resource pool wms_pool1 unit 'wms_unit1',unit_num 1;
创建wms_tenant租户(mysql类型,三副本)
CREATE TENANT IF NOT EXISTS wms_tenant charset='utf8mb4',replica_num=3, zone_list=('zone1','zone2','zone3'), primary_zone='RANDOM',comment 'mysql tenant/instance', resource_pool_list=('wms_pool1') set ob_tcp_invited_nodes='%',ob_compatibility_mode='mysql';

创建完租户后,查看现在的资源单元配置数据:sys_unit_config(sys 租户资源单元)和wms_unit1一共占用4G,加上之前500租户(系统租户)的1G,已经达到 memory_limit 的设置。

obclient [oceanbase]> select svr_ip,svr_port,zone,round((cpu_capacity_max-cpu_assigned_max),2) 'cpu_free_num',cpu_capacity_max 'cpu_total_num',round((mem_capacity-mem_assigned)/1024/1024/1024,2) 'mem_free_GB', round(memory_limit/1024/1024/1024,2) 'mem_total_GB' from gv$ob_servers;
+-----------+----------+-------+--------------+---------------+-------------+--------------+
| svr_ip    | svr_port | zone  | cpu_free_num | cpu_total_num | mem_free_GB | mem_total_GB |
+-----------+----------+-------+--------------+---------------+-------------+--------------+
| 127.0.0.1 |     2882 | zone1 |        24.00 |            30 |       15.00 |        20.00 |
+-----------+----------+-------+--------------+---------------+-------------+--------------+
1 row in set (0.002 sec)
 
obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_UNIT_CONFIGS;
+----------------+-----------------+----------------------------+----------------------------+---------+---------+-------------+---------------+----------+----------+-------------+
| UNIT_CONFIG_ID | NAME            | CREATE_TIME                | MODIFY_TIME                | MAX_CPU | MIN_CPU | MEMORY_SIZE | LOG_DISK_SIZE | MAX_IOPS | MIN_IOPS | IOPS_WEIGHT |
+----------------+-----------------+----------------------------+----------------------------+---------+---------+-------------+---------------+----------+----------+-------------+
|              1 | sys_unit_config | 2023-02-14 16:41:47.535108 | 2023-02-14 16:41:47.535108 |       1 |       1 |  2147483648 |    2147483648 |    10000 |    10000 |           1 |
|           1006 | wms_unit1       | 2023-02-17 15:28:49.420064 | 2023-02-17 15:28:49.420064 |       5 |       2 |  2147483648 |    6442450944 |    20000 |    20000 |           2 |
+----------------+-----------------+----------------------------+----------------------------+---------+---------+-------------+---------------+----------+----------+-------------+

三. 资源单元

3.1 查看资源单元

(默认已经有了一个sys资源单元,新建的单元为wms_unit1)
obclient [oceanbase]> SELECT * FROM oceanbase.__all_unit_config;
+----------------------------+----------------------------+----------------+-----------------+---------+---------+-------------+---------------+----------+----------+-------------+
| gmt_create                 | gmt_modified               | unit_config_id | name            | max_cpu | min_cpu | memory_size | log_disk_size | max_iops | min_iops | iops_weight |
+----------------------------+----------------------------+----------------+-----------------+---------+---------+-------------+---------------+----------+----------+-------------+
| 2023-02-14 16:41:47.535108 | 2023-02-14 16:41:47.535108 |              1 | sys_unit_config |       1 |       1 |  2147483648 |    2147483648 |    10000 |    10000 |           1 |
| 2023-02-17 15:28:49.420064 | 2023-02-17 15:28:49.420064 |           1006 | wms_unit1       |       5 |       2 |  2147483648 |    6442450944 |    20000 |    20000 |           2 |
+----------------------------+----------------------------+----------------+-----------------+---------+---------+-------------+---------------+----------+----------+-------------+
2 rows in set (0.002 sec)
 
obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_UNIT_CONFIGS;
+----------------+-----------------+----------------------------+----------------------------+---------+---------+-------------+---------------+----------+----------+-------------+
| UNIT_CONFIG_ID | NAME            | CREATE_TIME                | MODIFY_TIME                | MAX_CPU | MIN_CPU | MEMORY_SIZE | LOG_DISK_SIZE | MAX_IOPS | MIN_IOPS | IOPS_WEIGHT |
+----------------+-----------------+----------------------------+----------------------------+---------+---------+-------------+---------------+----------+----------+-------------+
|              1 | sys_unit_config | 2023-02-14 16:41:47.535108 | 2023-02-14 16:41:47.535108 |       1 |       1 |  2147483648 |    2147483648 |    10000 |    10000 |           1 |
|           1006 | wms_unit1       | 2023-02-17 15:28:49.420064 | 2023-02-17 15:28:49.420064 |       5 |       2 |  2147483648 |    6442450944 |    20000 |    20000 |           2 |
+----------------+-----------------+----------------------------+----------------------------+---------+---------+-------------+---------------+----------+----------+-------------+

3.2 修改资源单元

修改多个资源
ALTER RESOURCE UNIT wms_unit1 MAX_CPU 8,MIN_CPU=3,MAX_IOPS=30000;
修改某一个资源
ALTER RESOURCE UNIT wms_unit1 MAX_CPU 5;
修改资源时MAX资源不能小于MIN资源

3.3 删除资源单元

删除未被使用的资源单元
MySQL [oceanbase]> drop resource unit wms_unit1;
Query OK, 0 rows affected (0.004 sec)
 
删除已经被分配的资源单元
obclient [oceanbase]> DROP RESOURCE UNIT wms_unit1;
ERROR 4634 (HY000): resource unit 'wms_unit1' is referenced by some resource pool
 
 
一:如果ut1被分配且需要删除,可以先创建资源单元 wms_unit2 ,并将 wms_unit2 指定给 wms_pool1  后,再删除wms_unit1;
obclient [oceanbase]> create resource unit wms_unit2 max_cpu=5,min_cpu=3,memory_size='2G';
Query OK, 0 rows affected (0.012 sec)
 
obclient [oceanbase]> alter resource pool wms_pool1  unit 'wms_unit2';
Query OK, 0 rows affected (0.009 sec)
 
obclient [oceanbase]> drop resource unit wms_unit1;
Query OK, 0 rows affected (0.005 sec)
 
二:或者先删租户,再删资源池,再删资源单元;

四. 资源池

4.1 创建资源池

MySQL [oceanbase]> create resource pool wms_pool2 unit 'wms_unit2',unit_num 1;
Query OK, 0 rows affected (0.012 sec)

4.2 删除资源池

MySQL [oceanbase]> drop resource pool wms_pool2;

五. 租户

5.1 创建租户

创建名为 test_tenant 的一个 3 副本的租户
CREATE TENANT IF NOT EXISTS wms_tenant charset='utf8mb4',replica_num=3, zone_list=('zone1','zone2','zone3'), primary_zone='RANDOM',comment 'mysql tenant/instance', resource_pool_list=('wms_pool1') set ob_tcp_invited_nodes='%',ob_compatibility_mode='mysql';
 
ob_tcp_invited_nodes='%' 白名单这个最好设上,否则首次登录报错ERROR 1227 (42501): Access denied。不过也可以用命令改一下这个参数。ALTER TENANT test_tenant SET VARIABLES ob_tcp_invited_nodes='%';
ob_compatibility_mode='mysql' 社区版只支持mysql租户。

5.2 删除租户

(1)当系统租户开启回收站功能时:表示删除的租户会进入回收站
obclient> DROP TENANT tenant_name;
(2)当系统租户关闭回收站功能时:表示延迟删除租户
obclient> DROP TENANT tenant_name;
(3)无论系统租户是否开启回收站功能:删除的租户均不进入回收站,仅延迟删除租户
obclient> DROP TENANT tenant_name PURGE;
(4)无论系统租户是否开启回收站功能:均可以立刻删除租户
obclient> DROP TENANT tenant_name FORCE;

5.3 切换租户

不退出sys租户,切换到wms_tenant租户
obclient [oceanbase]>  alter system change tenant wms_tenant;
Query OK, 0 rows affected (0.002 sec)
 
obclient [oceanbase]> SHOW TENANT;
+---------------------+
| Current_tenant_name |
+---------------------+
| wms_tenant          |
+---------------------+
1 row in set (0.025 sec)
 
切换回sys租户
obclient [oceanbase]> alter system change tenant sys;
Query OK, 0 rows affected (0.001 sec)
 
obclient [oceanbase]> SHOW TENANT;
+---------------------+
| Current_tenant_name |
+---------------------+
| sys                 |
+---------------------+

5.4 修改租户

修改租户资源
修改租户 tenant1 的 Primary Zone 为 zone2
ALTER TENANT tenant1 primary_zone='zone2';
 
其中 F 表示副本类型为全功能型副本, B_4 为新增的 Zone 名称。、
ALTER TENANT tenant1 locality="F@B_1,F@B_2,F@B_3,F@B_4";
 
不支持修改租户资源池
ALTER TENANT tenant1 resource_pool_list=('pool2');
ERROR 1210 (HY000): Incorrect arguments to resource pool list
 
修改租户变量
ALTER TENANT test_tenant SET VARIABLES ob_tcp_invited_nodes='%';

5.5 查看租户参数

登录或切换到test_tenant租户
查看所有参数
MySQL [oceanbase]> show variables ;
 
MySQL [oceanbase]> show variables like 'ob_tcp_invited_nodes';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| ob_tcp_invited_nodes | %     |
+----------------------+-------+
1 row in set (0.002 sec)

六. 创建用户链接租户

使用root登录到新建的test_tenant租户中
[admin@dbdriver ~]$ obclient -h127.0.0.3 -P2881 -uroot@wms_tenant -c  -Doceanbase -p
 
只要登录的租户正确,那么创建用户的操作基本就和myql道理相同了
MySQL [(none)]> CREATE USER 'user1'@'%' IDENTIFIED BY 'welcome1';
Query OK, 0 rows affected (0.011 sec)
MySQL [oceanbase]> grant select on test.* to user1;
Query OK, 0 rows affected (0.014 sec)
 
新建用户测试登录
[root@localhost ~]# obclient -h127.0.0.1 -P2881 -uuser1@wms_tenant  -p -A
Enter password:
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 3221703464
Server version: OceanBase_CE 4.0.0.0 (r103000022023011215-05bbad0279302d7274e1b5ab79323a2c915c1981) (Built Jan 12 2023 15:28:27)
 
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)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+

七. 数据字典

| 数据库 | 字典 | 描述 |
| --- | --- | --- |
| oceanbase | __all_unit_config | 资源单元,情况 |
| oceanbase | __all_virtual_server_stat | 虚拟服务器资源,资源单元分配要考虑从中计算 |
| oceanbase | __all_resource_pool | 资源池信息 |
| oceanbase | __all_tenant | 租户信息基表 |
| oceanbase | gv$tenant | 租户信息视图-基表是__all_tenant |


爱可生开源社区
426 声望209 粉丝

成立于 2017 年,以开源高质量的运维工具、日常分享技术干货内容、持续的全国性的社区活动为社区己任;目前开源的产品有:SQL审核工具 SQLE,分布式中间件 DBLE、数据传输组件DTLE。