使用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       | FULL{1}@zone1, FULL{1}@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       | FULL{1}@zone1, FULL{1}@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       | FULL{1}@zone1, FULL{1}@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       | FULL{1}@zone1, FULL{1}@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