共计 8379 个字符，预计需要花费 21 分钟才能阅读完成。
作者：何文超
爱可生南区交付服务部 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 |
正文完
