使用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