使用root用户连接sysy租户

使用root用户连接sys租户

[admin]# mysql -h 192.168.1.71 -P2881 -D oceanbase -uroot@sys -pOceanbase2881 -A

创建单元规格

查看已创有的资源单元规格

obclient [oceanbase]> select * from __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-04-24 18:33:06.566391 | 2023-04-24 18:33:06.566391 |              1 | sys_unit_config |       1 |       1 |  5368709120 |    5368709120 |    10000 |    10000 |           1 |
+----------------------------+----------------------------+----------------+-----------------+---------+---------+-------------+---------------+----------+----------+-------------+

创建单元规格语法

CREATE RESOURCE UNIT unit_name 
MEMORY_SIZE [=] \'size_value\',
MAX_CPU [=] cpu_num, 
[MAX_IOPS [=] iops_num,]
[MIN_CPU [=] cpu_num,]
[MIN_IOPS [=] iops_num,] 
[IOPS_WEIGHT [=]iopsweight,]
[LOG_DISK_SIZE [=] \'size_value\'];

创建单元规格

create resource unit s1 max_cpu=1,memory_size=\'5G\';

查看已创建的单元规格

obclient [oceanbase]> select * from __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-04-24 18:33:06.566391 | 2023-04-24 18:33:06.566391 |              1 | sys_unit_config |       1 |       1 |  5368709120 |    5368709120 |    10000 |    10000 |           1 |
| 2023-04-25 15:18:41.373470 | 2023-04-25 15:18:41.373470 |           1003 | s1              |       1 |       1 |  5368709120 |   16106127360 |    10000 |    10000 |           1 |
+----------------------------+----------------------------+----------------+-----------------+---------+---------+-------------+---------------+----------+----------+-------------+

创建资源池

创建资源池的语法

CREATE RESOURCE POOL poolname 
UNIT [=] unitname, 
UNIT_NUM [=] unitnum, 
ZONE_LIST [=] (\'zone\' [, \'zone\' ...]);

创建资源池（需要确保节点资源充裕）

create resource pool my_pool unit=\'s1\',unit_num=1,zone_list=(\'zone1\',\'zone2\',\'zone3\');

 查看资源分配细节

obclient [oceanbase]> select t1.name resource_pool_name,t2.name unit_config_name,t2.max_cpu,t2.min_cpu,truncate(t2.memory_size/1024/1024/1024,2) memory_size_gb,t3.unit_id,t3.zone,concat(t3.svr_ip,\':\',t3.svr_port) observer,t4.tenant_id,t4.tenant_name from __all_resource_pool t1 join __all_unit_config t2 on (t1.unit_config_id=t2.unit_config_id) join __all_unit t3 on (t1.resource_pool_id = t3.resource_pool_id) left join __all_tenant t4 on (t1.tenant_id=t4.tenant_id)order by t1.resource_pool_id, t2.unit_config_id, t3.unit_id;
+--------------------+------------------+---------+---------+----------------+---------+-------+-------------------+-----------+-------------+
| resource_pool_name | unit_config_name | max_cpu | min_cpu | memory_size_gb | unit_id | zone  | observer          | tenant_id | tenant_name |
+--------------------+------------------+---------+---------+----------------+---------+-------+-------------------+-----------+-------------+
| sys_pool           | sys_unit_config  |       1 |       1 |           5.00 |       1 | zone1 | 192.168.1.71:2882 |         1 | sys         |
| sys_pool           | sys_unit_config  |       1 |       1 |           5.00 |       2 | zone2 | 192.168.1.72:2882 |         1 | sys         |
| sys_pool           | sys_unit_config  |       1 |       1 |           5.00 |       3 | zone3 | 192.168.1.73:2882 |         1 | sys         |
| my_pool            | s1               |       1 |       1 |           5.00 |    1001 | zone1 | 192.168.1.71:2882 |      NULL | NULL        |
| my_pool            | s1               |       1 |       1 |           5.00 |    1002 | zone2 | 192.168.1.72:2882 |      NULL | NULL        |
| my_pool            | s1               |       1 |       1 |           5.00 |    1003 | zone3 | 192.168.1.73:2882 |      NULL | NULL        |
+--------------------+------------------+---------+---------+----------------+---------+-------+-------------------+-----------+-------------+

 创建租户

 创建租户的语法

CREATE TENANT [IF NOT EXISTS] tenant_name 
     [tenant_characteristic_list] [opt_set_sys_var];

tenant_characteristic_list: 
  tenant_characteristic [, tenant_characteristic...]

tenant_characteristic: 
      COMMENT \'string\'  
    | CHARACTER SET | CHARSET [=] charsetname 
    | COLLATE [=]  collationname
    | ZONE_LIST [=] (zone [, zone...]) 
    | PRIMARY_ZONE [=] zone  
    | DEFAULT TABLEGROUP [=] NULL | tablegroup
    | RESOURCE_POOL_LIST [=](poolname [, poolname...])
    | LOCALITY [=] \'locality description\'

opt_set_sys_var:
  SET | SET VARIABLES | VARIABLES system_var_name = expr [,system_var_name = expr] ...

 查看租户信息

mysql> select * from DBA_OB_TENANTS;
+-----------+-------------+-------------+----------------------------+----------------------------+--------------+------------------------------+-------------------+--------------------+--------+---------------+--------+
| TENANT_ID | TENANT_NAME | TENANT_TYPE | CREATE_TIME                | MODIFY_TIME                | PRIMARY_ZONE | LOCALITY                     | PREVIOUS_LOCALITY | COMPATIBILITY_MODE | STATUS | IN_RECYCLEBIN | LOCKED |
+-----------+-------------+-------------+----------------------------+----------------------------+--------------+------------------------------+-------------------+--------------------+--------+---------------+--------+
|         1 | sys         | SYS         | 2023-04-24 18:33:06.821930 | 2023-05-09 09:28:07.989177 | RANDOM       | FULL1@zone1, FULL1@zone2 | NULL              | MYSQL              | NORMAL | NO            | NO     |
+-----------+-------------+-------------+----------------------------+----------------------------+--------------+------------------------------+-------------------+--------------------+--------+---------------+--------+

创建租户

create tenant my_tenant resource_pool_list=(\'my_pool\'),primary_zone=\'RANDOM\',comment \'my tenant\',charset=\'utf8\' set ob_tcp_invited_nodes=\'%\',lower_case_table_names=0;

 查看租户信息

mysql> select * from DBA_OB_TENANTS;
+-----------+-------------+-------------+----------------------------+----------------------------+--------------+------------------------------+-------------------+--------------------+--------+---------------+--------+
| TENANT_ID | TENANT_NAME | TENANT_TYPE | CREATE_TIME                | MODIFY_TIME                | PRIMARY_ZONE | LOCALITY                     | PREVIOUS_LOCALITY | COMPATIBILITY_MODE | STATUS | IN_RECYCLEBIN | LOCKED |
+-----------+-------------+-------------+----------------------------+----------------------------+--------------+------------------------------+-------------------+--------------------+--------+---------------+--------+
|         1 | sys         | SYS         | 2023-04-24 18:33:06.821930 | 2023-05-09 09:28:07.989177 | RANDOM       | FULL1@zone1, FULL1@zone2 | NULL              | MYSQL              | NORMAL | NO            | NO     |
|      1003 | META$1004   | META        | 2023-04-25 16:54:07.990306 | 2023-05-09 09:28:31.056441 | RANDOM       | FULL1@zone1, FULL1@zone2 | NULL              | MYSQL              | NORMAL | NO            | NO     |
|      1004 | my_tenant   | USER        | 2023-04-25 16:54:07.991849 | 2023-05-09 09:28:31.056441 | RANDOM       | FULL1@zone1, FULL1@zone2 | NULL              | MYSQL              | NORMAL | NO            | NO     |
+-----------+-------------+-------------+----------------------------+----------------------------+--------------+------------------------------+-------------------+--------------------+--------+---------------+--------+

连接租户 

 新创建的租户（root@my_tenant）默认无密码

[admin]# mysql -h192.168.1.71 -P2883 -D oceanbase -uroot@my_tenant#ob_cluster -p -A
mysql> alter user root identified by \'OceanBase010\';

创建租户参考：https://www.oceanbase.com/docs/common-oceanbase-database-cn-10000000001692881