作者：何文超
愛可生南區交付服務部 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 |