龙空技术网

龙空技术网

OceanBase单元、资源池和租户

数据库和运维之Style 54

前言：

现在同学们对“oracle2291”都比较关注，姐妹们都想要剖析一些“oracle2291”的相关文章。那么小编也在网摘上汇集了一些关于“oracle2291””的相关文章，希望兄弟们能喜欢，看官们一起来学习一下吧！

一、单元

OceanBase 数据库是多租户的分布式数据库，租户使用的资源建立在资源池上。资源池包含了资源单元，而资源单元则规定了具体资源的量化（如 CPU、Memory、Disk_Size 和 IOPS 等）。 创建租户前，必须规定租户使用的资源范围，资源池和资源单元就是为了满足租户资源隔离和负载均衡而存在的。

资源单元是一个容器。实际上，副本是存储在资源单元之中的，所以资源单元是副本的容器。资源单元包含了计算存储资源（Memory、CPU 和 IO 等）同时资源单元也是集群负载均衡的一个基本单位，在集群节点上下线，扩容缩容时会动态调整资源单元在节点上的分布进而达到资源的使用均衡。

二、资源池

一个资源池由具有相同资源规格（Unit Config）的若干个资源单元组成。一个资源池只能属于一个租户。每个资源单元描述了位于一个 Server 上的一组计算和存储资源，可以视为一个轻量级虚拟机，包括若干 CPU 资源、内存资源、磁盘资源等。

三、租户

一个租户拥有若干个资源池，这些资源池的集合描述了这个租户所能使用的所有资源，一个租户在同一个 Server 上面有一个资源单元。

OB集群查看

obclient [oceanbase]> select * from DBA_OB_UNITS\g;

+---------+-----------+--------+------------------+---------------+----------------------------+----------------------------+-------+-----------------+----------+---------------------+-----------------------+----------------+----------------+---------+---------+-------------+---------------+----------+----------+-------------+

| UNIT_ID | TENANT_ID | STATUS | RESOURCE_POOL_ID | UNIT_GROUP_ID | CREATE_TIME | MODIFY_TIME | ZONE | SVR_IP | SVR_PORT | MIGRATE_FROM_SVR_IP | MIGRATE_FROM_SVR_PORT | MANUAL_MIGRATE | UNIT_CONFIG_ID | MAX_CPU | MIN_CPU | MEMORY_SIZE | LOG_DISK_SIZE | MAX_IOPS | MIN_IOPS | IOPS_WEIGHT |

+---------+-----------+--------+------------------+---------------+----------------------------+----------------------------+-------+-----------------+----------+---------------------+-----------------------+----------------+----------------+---------+---------+-------------+---------------+----------+----------+-------------+

| 1 | 1 | ACTIVE | 1 | 1 | 2023-06-28 16:59:04.972291 | 2023-06-28 16:59:04.972291 | zone1 | 192.168.159.156 | 2882 | NULL | NULL | NULL | 1 | 1 | 1 | 2147483648 | 2147483648 | 10000 | 10000 | 1 |

| 2 | 1 | ACTIVE | 1 | 1 | 2023-06-28 16:59:04.974528 | 2023-06-28 16:59:04.974528 | zone2 | 192.168.159.157 | 2882 | NULL | NULL | NULL | 1 | 1 | 1 | 2147483648 | 2147483648 | 10000 | 10000 | 1 |

| 3 | 1 | ACTIVE | 1 | 1 | 2023-06-28 16:59:04.976987 | 2023-06-28 16:59:04.976987 | zone3 | 192.168.159.158 | 2882 | NULL | NULL | NULL | 1 | 1 | 1 | 2147483648 | 2147483648 | 10000 | 10000 | 1 |

+---------+-----------+--------+------------------+---------------+----------------------------+----------------------------+-------+-----------------+----------+---------------------+-----------------------+----------------+----------------+---------+---------+-------------+---------------+----------+----------+-------------+

3 rows in set (0.001 sec)

ERROR:

No query specified

obclient [oceanbase]> select * from DBA_OB_ZONES;

+-------+----------------------------+----------------------------+--------+-----+------------+-----------+

| ZONE | CREATE_TIME | MODIFY_TIME | STATUS | IDC | REGION | TYPE |

+-------+----------------------------+----------------------------+--------+-----+------------+-----------+

| zone1 | 2023-06-28 16:59:05.080872 | 2023-06-28 16:59:05.080872 | ACTIVE | | sys_region | ReadWrite |

| zone2 | 2023-06-28 16:59:05.080872 | 2023-06-28 16:59:05.081985 | ACTIVE | | sys_region | ReadWrite |

| zone3 | 2023-06-28 16:59:05.081985 | 2023-06-28 16:59:05.081985 | ACTIVE | | sys_region | ReadWrite |

+-------+----------------------------+----------------------------+--------+-----+------------+-----------+

3 rows in set (0.004 sec)

obclient [oceanbase]> select * from GV$OB_SERVERS;

+-----------------+----------+-------+----------+--------------+------------------+--------------+------------------+--------------+--------------+-------------------+-------------------+-----------------+--------------------+------------------+-------------------------+--------------+-------------------------+-----------------------+

| SVR_IP | SVR_PORT | ZONE | SQL_PORT | CPU_CAPACITY | CPU_CAPACITY_MAX | CPU_ASSIGNED | CPU_ASSIGNED_MAX | MEM_CAPACITY | MEM_ASSIGNED | LOG_DISK_CAPACITY | LOG_DISK_ASSIGNED | LOG_DISK_IN_USE | DATA_DISK_CAPACITY | DATA_DISK_IN_USE | DATA_DISK_HEALTH_STATUS | MEMORY_LIMIT | DATA_DISK_ABNORMAL_TIME | SSL_CERT_EXPIRED_TIME |

+-----------------+----------+-------+----------+--------------+------------------+--------------+------------------+--------------+--------------+-------------------+-------------------+-----------------+--------------------+------------------+-------------------------+--------------+-------------------------+-----------------------+

| 192.168.159.156 | 2882 | zone1 | 2881 | 16 | 16 | 1 | 1 | 5368709120 | 2147483648 | 16106127360 | 2147483648 | 1677721600 | 21474836480 | 90177536 | NORMAL | 6442450944 | NULL | NULL |

| 192.168.159.158 | 2882 | zone3 | 2881 | 16 | 16 | 1 | 1 | 5368709120 | 2147483648 | 16106127360 | 2147483648 | 1677721600 | 21474836480 | 117440512 | NORMAL | 6442450944 | NULL | NULL |

| 192.168.159.157 | 2882 | zone2 | 2881 | 16 | 16 | 1 | 1 | 5368709120 | 2147483648 | 16106127360 | 2147483648 | 1677721600 | 21474836480 | 117440512 | NORMAL | 6442450944 | NULL | NULL |

+-----------------+----------+-------+----------+--------------+------------------+--------------+------------------+--------------+--------------+-------------------+-------------------+-----------------+--------------------+------------------+-------------------------+--------------+-------------------------+-----------------------+

3 rows in set (0.004 sec)

===============================================================================================================================================================================================

(1)资源单元练习：

1.查看资源单元通过内部表查看

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-06-28 16:59:04.964797 | 2023-06-28 16:59:04.964797 | 1 | sys_unit_config | 1 | 1 | 2147483648 | 2147483648 | 10000 | 10000 | 1 |

+----------------------------+----------------------------+----------------+-----------------+---------+---------+-------------+---------------+----------+----------+-------------+

2.管理资源单元(Resource Unit)创建资源单元语法CREATERESOURCEUNITunitname

    MAX_CPU [=] cpunum,     MAX_MEMORY [=] memsize,     MAX_IOPS [=] iopsnum,     MAX_DISK_SIZE [=] disksize,     MAX_SESSION_NUM [=] sessionnum,     [MIN_CPU [=] cpunum,]    [MIN_MEMORY [=] memsize,]     [MIN_IOPS [=] iopsnum] ;

obclient [oceanbase]> CREATE RESOURCE UNIT andyxi_unit1 MAX_CPU 1,MIN_CPU 1,MEMORY_SIZE '1G',MAX_IOPS=1024,MIN_IOPS=1024,LOG_DISK_SIZE='6G';

Query OK, 0 rows affected (0.014 sec)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-06-28 16:59:04.964797 | 2023-06-28 16:59:04.964797 | 1 | sys_unit_config | 1 | 1 | 2147483648 | 2147483648 | 10000 | 10000 | 1 |

| 2023-07-04 09:18:39.665772 | 2023-07-04 09:18:39.665772 | 1001 | andyxi_unit1 | 1 | 1 | 1073741824 | 6442450944 | 1024 | 1024 | 0 |

+----------------------------+----------------------------+----------------+-----------------+---------+---------+-------------+---------------+----------+----------+-------------+

2 rows in set (0.001 sec)

3.修改资源单元动态调整租户的资源单元大小
obclient [oceanbase]> alter resource unit andyxi_unit1 max_cpu 2;
Query OK, 0 rows affected (0.005 sec)
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-06-28 16:59:04.964797 | 2023-06-28 16:59:04.964797 | 1 | sys_unit_config | 1 | 1 | 2147483648 | 2147483648 | 10000 | 10000 | 1 |
| 2023-07-04 09:18:39.665772 | 2023-07-04 09:30:49.313921 | 1001 | andyxi_unit1 | 2 | 1 | 1073741824 | 6442450944 | 1024 | 1024 | 0 |
+----------------------------+----------------------------+----------------+-----------------+---------+---------+-------------+---------------+----------+----------+-------------+
2 rows in set (0.001 sec)
4.删除资源单元删除资源单元前必须确保当前资源单元未被使用。如果资源单元正在被使用，则需要先将资源单元从资源池中移除后再删除资源单元。obclient [oceanbase]>drop resource unit andyxi_unit1;Query OK, 0 rows affected (0.009 sec)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-06-28 16:59:04.964797 | 2023-06-28 16:59:04.964797 | 1 | sys_unit_config | 1 | 1 | 2147483648 | 2147483648 | 10000 | 10000 | 1 |+----------------------------+----------------------------+----------------+-----------------+---------+---------+-------------+---------------+----------+----------+-------------+1 row in set (0.001 sec)
=========================================================================================================================================================================================================
(2)资源池管理练习：1.资源池管理(Resource Pool)创建资源池在创建新租户时，如果当前的资源池均被使用（被其他租户使用），需要创建新的资源池。语法
CREATE RESOURCE POOL poolname UNIT [=] unitname, UNIT_NUM [=] unitnum, ZONE_LIST [=] ('zone' [, 'zone' ...]);
参数 unit_num 表示在集群的一个 Zone 里面包含的资源单元个数。该值小于等于一个 Zone 中的 OBServer 的个数。参数 zone_list 表示资源池的 Zone 列表，显示该资源池的资源在哪些 Zone 中被使用。
obclient [oceanbase]> select * from __all_resource_pool \G
*************************** 1. row ***************************
gmt_create: 2023-06-28 16:59:04.968734
gmt_modified: 2023-06-28 16:59:04.978083
resource_pool_id: 1
name: sys_pool
unit_count: 1
unit_config_id: 1
zone_list: zone1;zone2;zone3
tenant_id: 1
replica_type: 0
is_tenant_sys_pool: 0
*************************** 2. row ***************************
gmt_create: 2023-07-04 10:01:14.932970
gmt_modified: 2023-07-04 10:13:34.682446
resource_pool_id: 1001
name: andyxi_pool1
unit_count: 1
unit_config_id: 1004
zone_list: zone1;zone2;zone3
tenant_id: -1
replica_type: 0
is_tenant_sys_pool: 0
2 rows in set (0.002 sec)
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-06-28 16:59:04.964797 | 2023-06-28 16:59:04.964797 | 1 | sys_unit_config | 1 | 1 | 2147483648 | 2147483648 | 10000 | 10000 | 1 |
| 2023-07-04 09:41:33.868264 | 2023-07-04 09:41:33.868264 | 1002 | andyxi_unit1 | 1 | 1 | 1073741824 | 6442450944 | 1024 | 1024 | 0 |
| 2023-07-04 09:41:58.180737 | 2023-07-04 09:41:58.180737 | 1003 | andyxi_unit2 | 2 | 2 | 2147483648 | 6442450944 | 1024 | 1024 | 0 |
| 2023-07-04 09:42:14.796566 | 2023-07-04 09:42:14.796566 | 1004 | andyxi_unit3 | 3 | 3 | 3221225472 | 6442450944 | 1024 | 1024 | 0 |
+----------------------------+----------------------------+----------------+-----------------+---------+---------+-------------+---------------+----------+----------+-------------+
4 rows in set (0.001 sec)
obclient [oceanbase]> alter resource pool andyxi_pool1 unit='andyxi_unit2';
Query OK, 0 rows affected (0.005 sec)
obclient [oceanbase]> select * from __all_resource_pool \G
*************************** 1. row ***************************
gmt_create: 2023-06-28 16:59:04.968734
gmt_modified: 2023-06-28 16:59:04.978083
resource_pool_id: 1
name: sys_pool
unit_count: 1
unit_config_id: 1
zone_list: zone1;zone2;zone3
tenant_id: 1
replica_type: 0
is_tenant_sys_pool: 0
*************************** 2. row ***************************
gmt_create: 2023-07-04 10:01:14.932970
gmt_modified: 2023-07-04 10:18:26.512301
resource_pool_id: 1001
name: andyxi_pool1
unit_count: 1
unit_config_id: 1003
zone_list: zone1;zone2;zone3
tenant_id: -1
replica_type: 0
is_tenant_sys_pool: 0
2 rows in set (0.001 sec)
obclient [oceanbase]> CREATE RESOURCE UNIT andyxi_unit1 MAX_CPU 1,MIN_CPU 1,MEMORY_SIZE '1G',MAX_IOPS=1024,MIN_IOPS=1024,LOG_DISK_SIZE='6G';
Query OK, 0 rows affected (0.008 sec)
obclient [oceanbase]> CREATE RESOURCE UNIT andyxi_unit2 MAX_CPU 2,MIN_CPU 2,MEMORY_SIZE '2G',MAX_IOPS=1024,MIN_IOPS=1024,LOG_DISK_SIZE='6G';
Query OK, 0 rows affected (0.008 sec)
obclient [oceanbase]> CREATE RESOURCE UNIT andyxi_unit3 MAX_CPU 3,MIN_CPU 3,MEMORY_SIZE '3G',MAX_IOPS=1024,MIN_IOPS=1024,LOG_DISK_SIZE='6G';
Query OK, 0 rows affected (0.007 sec)
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-06-28 16:59:04.964797 | 2023-06-28 16:59:04.964797 | 1 | sys_unit_config | 1 | 1 | 2147483648 | 2147483648 | 10000 | 10000 | 1 |
| 2023-07-04 09:41:33.868264 | 2023-07-04 09:41:33.868264 | 1002 | andyxi_unit1 | 1 | 1 | 1073741824 | 6442450944 | 1024 | 1024 | 0 |
| 2023-07-04 09:41:58.180737 | 2023-07-04 09:41:58.180737 | 1003 | andyxi_unit2 | 2 | 2 | 2147483648 | 6442450944 | 1024 | 1024 | 0 |
| 2023-07-04 09:42:14.796566 | 2023-07-04 09:42:14.796566 | 1004 | andyxi_unit3 | 3 | 3 | 3221225472 | 6442450944 | 1024 | 1024 | 0 |
+----------------------------+----------------------------+----------------+-----------------+---------+---------+-------------+---------------+----------+----------+-------------+
4 rows in set (0.001 sec)
obclient [oceanbase]> select * from __all_unit;
+----------------------------+----------------------------+---------+------------------+---------------+-------+-----------------+----------+---------------------+-----------------------+----------------+--------+
| gmt_create | gmt_modified | unit_id | resource_pool_id | unit_group_id | zone | svr_ip | svr_port | migrate_from_svr_ip | migrate_from_svr_port | manual_migrate | status |
+----------------------------+----------------------------+---------+------------------+---------------+-------+-----------------+----------+---------------------+-----------------------+----------------+--------+
| 2023-06-28 16:59:04.972291 | 2023-06-28 16:59:04.972291 | 1 | 1 | 1 | zone1 | 192.168.159.156 | 2882 | | 0 | 0 | ACTIVE |
| 2023-06-28 16:59:04.974528 | 2023-06-28 16:59:04.974528 | 2 | 1 | 1 | zone2 | 192.168.159.157 | 2882 | | 0 | 0 | ACTIVE |
| 2023-06-28 16:59:04.976987 | 2023-06-28 16:59:04.976987 | 3 | 1 | 1 | zone3 | 192.168.159.158 | 2882 | | 0 | 0 | ACTIVE |
+----------------------------+----------------------------+---------+------------------+---------------+-------+-----------------+----------+---------------------+-----------------------+----------------+--------+
3 rows in set (0.001 sec)
obclient [oceanbase]> create resource pool andyxi_pool1 unit='andyxi_unit1',unit_num=1,zone_list=('zone1','zone2','zone3');
ERROR 1235 (0A000): unit MEMORY_SIZE less than __min_full_resource_pool_memory not supported
obclient [oceanbase]> create resource pool andyxi_pool1 unit='andyxi_unit2',unit_num=1,zone_list=('zone1','zone2','zone3');
Query OK, 0 rows affected (0.024 sec)
obclient [oceanbase]> select * from __all_unit;
+----------------------------+----------------------------+---------+------------------+---------------+-------+-----------------+----------+---------------------+-----------------------+----------------+--------+
| gmt_create | gmt_modified | unit_id | resource_pool_id | unit_group_id | zone | svr_ip | svr_port | migrate_from_svr_ip | migrate_from_svr_port | manual_migrate | status |
+----------------------------+----------------------------+---------+------------------+---------------+-------+-----------------+----------+---------------------+-----------------------+----------------+--------+
| 2023-06-28 16:59:04.972291 | 2023-06-28 16:59:04.972291 | 1 | 1 | 1 | zone1 | 192.168.159.156 | 2882 | | 0 | 0 | ACTIVE |
| 2023-06-28 16:59:04.974528 | 2023-06-28 16:59:04.974528 | 2 | 1 | 1 | zone2 | 192.168.159.157 | 2882 | | 0 | 0 | ACTIVE |
| 2023-06-28 16:59:04.976987 | 2023-06-28 16:59:04.976987 | 3 | 1 | 1 | zone3 | 192.168.159.158 | 2882 | | 0 | 0 | ACTIVE |
| 2023-07-04 10:01:14.940507 | 2023-07-04 10:01:14.940507 | 1001 | 1001 | 0 | zone1 | 192.168.159.156 | 2882 | | 0 | 0 | ACTIVE |
| 2023-07-04 10:01:14.946358 | 2023-07-04 10:01:14.946358 | 1002 | 1001 | 0 | zone2 | 192.168.159.157 | 2882 | | 0 | 0 | ACTIVE |
| 2023-07-04 10:01:14.948602 | 2023-07-04 10:01:14.948602 | 1003 | 1001 | 0 | zone3 | 192.168.159.158 | 2882 | | 0 | 0 | ACTIVE |
+----------------------------+----------------------------+---------+------------------+---------------+-------+-----------------+----------+---------------------+-----------------------+----------------+--------+
6 rows in set (0.001 sec)
2.修改资源池
语法
ALTER RESOURCE POOL poolname     UNIT [=] unitname,     UNIT_NUM [=] unitnum,     ZONE_LIST [=] ('zone'[, 'zone' ...]);
说明修改资源池的命令每次仅支持修改一个参数值。被修改的资源池必须是没有被使用的或者不包含任何资源单元的空的资源池。新创建的资源池不能更改 zone_list。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-06-28 16:59:04.964797 | 2023-06-28 16:59:04.964797 | 1 | sys_unit_config | 1 | 1 | 2147483648 | 2147483648 | 10000 | 10000 | 1 |
| 2023-07-04 09:41:33.868264 | 2023-07-04 09:41:33.868264 | 1002 | andyxi_unit1 | 1 | 1 | 1073741824 | 6442450944 | 1024 | 1024 | 0 |
| 2023-07-04 09:41:58.180737 | 2023-07-04 09:41:58.180737 | 1003 | andyxi_unit2 | 2 | 2 | 2147483648 | 6442450944 | 1024 | 1024 | 0 |
| 2023-07-04 09:42:14.796566 | 2023-07-04 09:42:14.796566 | 1004 | andyxi_unit3 | 3 | 3 | 3221225472 | 6442450944 | 1024 | 1024 | 0 |
+----------------------------+----------------------------+----------------+-----------------+---------+---------+-------------+---------------+----------+----------+-------------+
4 rows in set (0.001 sec)obclient [oceanbase]> alter resource pool andyxi_pool1 unit='andyxi_unit2';
Query OK, 0 rows affected (0.005 sec)obclient [oceanbase]> select * from __all_resource_pool \G
*************************** 1. row ***************************
gmt_create: 2023-06-28 16:59:04.968734
gmt_modified: 2023-06-28 16:59:04.978083
resource_pool_id: 1
name: sys_pool
unit_count: 1
unit_config_id: 1
zone_list: zone1;zone2;zone3
tenant_id: 1
replica_type: 0
is_tenant_sys_pool: 0
*************************** 2. row ***************************
gmt_create: 2023-07-04 10:01:14.932970
gmt_modified: 2023-07-04 10:18:26.512301
resource_pool_id: 1001
name: andyxi_pool1
unit_count: 1
unit_config_id: 1003
zone_list: zone1;zone2;zone3
tenant_id: -1
replica_type: 0
is_tenant_sys_pool: 0
2 rows in set (0.001 sec)
3.删除资源池
obclient [oceanbase]> select * from __all_resource_pool \G
*************************** 1. row ***************************
gmt_create: 2023-06-28 16:59:04.968734
gmt_modified: 2023-06-28 16:59:04.978083
resource_pool_id: 1
name: sys_pool
unit_count: 1
unit_config_id: 1
zone_list: zone1;zone2;zone3
tenant_id: 1
replica_type: 0
is_tenant_sys_pool: 0
*************************** 2. row ***************************
gmt_create: 2023-07-04 10:01:14.932970
gmt_modified: 2023-07-04 10:18:26.512301
resource_pool_id: 1001
name: andyxi_pool1
unit_count: 1
unit_config_id: 1003
zone_list: zone1;zone2;zone3
tenant_id: -1
replica_type: 0
is_tenant_sys_pool: 0
2 rows in set (0.001 sec)
obclient [oceanbase]> drop resource pool andyxi_pool1;
Query OK, 0 rows affected (0.007 sec)
obclient [oceanbase]> select * from __all_resource_pool \G
*************************** 1. row ***************************
gmt_create: 2023-06-28 16:59:04.968734
gmt_modified: 2023-06-28 16:59:04.978083
resource_pool_id: 1
name: sys_pool
unit_count: 1
unit_config_id: 1
zone_list: zone1;zone2;zone3
tenant_id: 1
replica_type: 0
is_tenant_sys_pool: 0
1 row in set (0.002 sec)
4.查看资源的分布
select pool.tenant_id, tenant.tenant_name,name as pool_name,unit_config_id, unit_count,
unit.unit_id,pool.zone_list, unit.svr_ip
from
__all_resource_pool pool inner join __all_tenant tenant on pool.tenant_id=tenant.tenant_id
inner join __all_unit unit on pool.resource_pool_id=unit.resource_pool_id
where pool.tenant_id>1000
order by tenant.tenant_name, zone_list;
+-----------+----------------+--------------+----------------+------------+---------+-------------------+-----------------+
| tenant_id | tenant_name | pool_name | unit_config_id | unit_count | unit_id | zone_list | svr_ip |
+-----------+----------------+--------------+----------------+------------+---------+-------------------+-----------------+
| 1006 | andyxi1_tenant | andyxi_pool3 | 1004 | 1 | 1004 | zone1;zone2;zone3 | 192.168.159.156 |
| 1006 | andyxi1_tenant | andyxi_pool3 | 1004 | 1 | 1005 | zone1;zone2;zone3 | 192.168.159.157 |
| 1006 | andyxi1_tenant | andyxi_pool3 | 1004 | 1 | 1006 | zone1;zone2;zone3 | 192.168.159.158 |
+-----------+----------------+--------------+----------------+------------+---------+-------------------+-----------------+
3 rows in set (0.011 sec)
（3）租户
#创建租户
obclient [oceanbase]> create tenant if not exists andyxi1_tenant charset='utf8mb4',replica_num=3,zone_list('zone1','zone2','zone3'),primary_zone='RANDOM',resource_pool_list=('andyxi_pool3') set ob_tcp_invited_nodes='%';
#查看租户
obclient [oceanbase]> select * from __all_tenant\G;
*************************** 1. row ***************************
gmt_create: 2023-06-28 16:59:04.987193
gmt_modified: 2023-06-28 16:59:04.987193
tenant_id: 1
tenant_name: sys
zone_list: zone1;zone2;zone3
primary_zone: RANDOM
locked: 0
collation_type: 0
info: system tenant
locality: FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3
previous_locality:
default_tablegroup_id: -1
compatibility_mode: 0
drop_tenant_time: -1
status: NORMAL
in_recyclebin: 0
arbitration_service_status: DISABLED
*************************** 2. row ***************************
gmt_create: 2023-07-04 10:39:17.546628
gmt_modified: 2023-07-04 10:39:38.975251
tenant_id: 1005
tenant_name: META$1006
zone_list: zone1;zone2;zone3
primary_zone: RANDOM
locked: 0
collation_type: 0
info:
locality: FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3
previous_locality:
default_tablegroup_id: -1
compatibility_mode: 0
drop_tenant_time: -1
status: NORMAL
in_recyclebin: 0
arbitration_service_status: DISABLED
*************************** 3. row ***************************
gmt_create: 2023-07-04 10:39:17.546628
gmt_modified: 2023-07-04 10:39:39.101071
tenant_id: 1006
tenant_name: andyxi1_tenant
zone_list: zone1;zone2;zone3
primary_zone: RANDOM
locked: 0
collation_type: 0
info:
locality: FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3
previous_locality:
default_tablegroup_id: -1
compatibility_mode: 0
drop_tenant_time: -1
status: NORMAL
in_recyclebin: 0
arbitration_service_status: DISABLED
3 rows in set (0.003 sec)
ERROR:
No query specified
obclient [oceanbase]>
#删除租户
示例语句如下所示：
延迟删除租户 t1，删除的租户可进入回收站
obclient> DROP TENANT andyxi1_tenant;
延迟删除租户 t1，删除的租户不进入回收站
obclient> DROP TENANT andyxi1_tenant PURGE;
立刻删除租户 t1
obclient> DROP TENANT andyxi1_tenant FORCE;
#设置口令 mysql模式
[root@odp conf]# obclient -h192.168.159.156 -uroot@andyxi1_tenant#Baoge_obcluster -P2883 -A
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 102
Server version: OceanBase_CE 4.1.0.1 (r102000042023061314-43bca414d5065272a730c92a645c3e25768c1d05) (Built Jun 13 2023 14:26:23)
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)]> set password=password('anydxi123');
Query OK, 0 rows affected (0.140 sec)
[root@odp conf]# obclient -h192.168.159.156 -uroot@andyxi1_tenant#Baoge_obcluster -P2883 -A -p
Enter password:
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 108
Server version: OceanBase_CE 4.1.0.1 (r102000042023061314-43bca414d5065272a730c92a645c3e25768c1d05) (Built Jun 13 2023 14:26:23)
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)]>
#设置口令 oracle模式
[root@odp conf]# obclient -h192.168.159.156 -usys@andyxi1_tenant#Baoge_obcluster -P2883 -c --prompt "\u > "
sys > alter user sys identified by oracle;
[root@obcontrol ~]# obclient -h192.168.159.156 -usys@andyxi1_tenant#Baoge_obcluster -P2883 -c --prompt "\u > " -poracle
#查看创建租户
MySQL [oceanbase]> select * from __all_tenant\G
*************************** 1. row ***************************
gmt_create: 2023-06-28 16:59:04.987193
gmt_modified: 2023-06-28 16:59:04.987193
tenant_id: 1
tenant_name: sys
zone_list: zone1;zone2;zone3
primary_zone: RANDOM
locked: 0
collation_type: 0
info: system tenant
locality: FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3
previous_locality:
default_tablegroup_id: -1
compatibility_mode: 0
drop_tenant_time: -1
status: NORMAL
in_recyclebin: 0
arbitration_service_status: DISABLED
*************************** 2. row ***************************
gmt_create: 2023-07-04 10:39:17.546628
gmt_modified: 2023-07-04 10:39:38.975251
tenant_id: 1005
tenant_name: META$1006
zone_list: zone1;zone2;zone3
primary_zone: RANDOM
locked: 0
collation_type: 0
info:
locality: FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3
previous_locality:
default_tablegroup_id: -1
compatibility_mode: 0
drop_tenant_time: -1
status: NORMAL
in_recyclebin: 0
arbitration_service_status: DISABLED
*************************** 3. row ***************************
gmt_create: 2023-07-04 10:39:17.546628
gmt_modified: 2023-07-04 10:39:39.101071
tenant_id: 1006
tenant_name: andyxi1_tenant
zone_list: zone1;zone2;zone3
primary_zone: RANDOM
locked: 0
collation_type: 0
info:
locality: FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3
previous_locality:
default_tablegroup_id: -1
compatibility_mode: 0
drop_tenant_time: -1
status: NORMAL
in_recyclebin: 0
arbitration_service_status: DISABLED
3 rows in set (0.001 sec)
=====================================================================================================
创建库、表、用户等
3.1创建数据库
使用新建的租户连接并创建数据库
CREATE DATABASE `andyxi` DEFAULT CHARACTER SET = utf8mb4 REPLICA_NUM = 1;
3.2创建用户
-- 使用新建的租户连接并创建用户create user `payer`@`192.168.159.%` identified by 'andyxi123';grant select,insert,update,delete,create,drop on paydb.* to `payer`@`192.168.159.%`;grant all privileges on paydb.* to `payer`@`192.168.159.%`;
obclient [oceanbase]> select host, user, password, plugin, authentication_string from mysql.user;
+------+---------+-------------------------------------------+--------------------+-----------------------+
| host | user | password | plugin | authentication_string |
+------+---------+-------------------------------------------+--------------------+-----------------------+
| % | root | *3145388d6e912bba3f1842fc5331ef6c19bc24c9 | ob_native_password | |
| % | proxyro | *3145388d6e912bba3f1842fc5331ef6c19bc24c9 | ob_native_password | |
| % | monitor | *09160bc7bc674566f8a9c591315e7b4a338136e1 | ob_native_password | |
+------+---------+-------------------------------------------+--------------------+-----------------------+
3 rows in set (0.037 sec)
3.3 创建表
create database t1;
use t1;
create table t1 (c1 int,c2 int ) ;
create table t2 (c1 int,c2 int ) primary_zone='zone2' ;
create table t3 (c1 int,c2 int ) partition by hash (c1) partitions 3;
3.4 创建hash分区表
#mysql 模式的限制：分区表达式的结果必须是 int 类型
MySQL [t1]> create table t_p_hash (c1 varchar(20),c2 int, c3 varchar(20)) partition by hash(c1) partitions 3;
ERROR 1659 (HY000): Field 'c1' is of a not allowed type for this type of partitioning
MySQL [t1]> create table t_p_hash (c1 varchar(20), c2 int,c3 varchar(20) ) partition by hash(c2+1) partitions 3;
3.5 创建key分区表
MySQL [t1]> create table t_p_key (c1 varchar(20),c2 int,c3 varchar(20)) partition by key (c2) partitions 3;
3.6 创建rang分区表
CREATE TABLE t_p_range (id INT, gmt_create DATETIME, info VARCHAR(20), PRIMARY KEY (gmt_create))
PARTITION BY RANGE COLUMNS(gmt_create)
(PARTITION p0 VALUES LESS THAN ('2015-01-01 00:00:00'),
PARTITION p1 VALUES LESS THAN ('2016-01-01 00:00:00'),
PARTITION p2 VALUES LESS THAN ('2017-01-01 00:00:00'),
PARTITION p3 VALUES LESS THAN (MAXVALUE));
#range 分区表的最后 maxvalue 分区，我们尝试增加一个分区看是否可以
#可以看到最后是 maxvalue 分区的时候，add 分区不能成功
MySQL [t1]> alter table t_p_range add partition (partition p4 values less than ('2018-01-01 00:00:00'));
ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition
#先删除最后的 maxvalue 分区，然后再尝试刚才的 add 分区操作
MySQL [t1]> alter table t_p_range drop partition (p3);
MySQL [t1]> alter table t_p_range add partition (partition p4 values less than ('2018-01-01 00:00:00'));
3.5 创建list分区表
# int字段list分区表
create table t_p_list (c1 varchar(20), c2 int)
partition by list(c2) (
partition p0 values in (1,2,3),
partition p1 values in (4,5),
partition p2 values in (default) );
# varchar字段list分区表
create table t_p_list (c1 varchar(20), c2 int)
partition by list(c1) (
partition p0 values in (1,2,3),
partition p1 values in (4,5),
partition p2 values in (default) );
      

      

       
       
版权声明：
        本站文章均来自互联网搜集，如有侵犯您的权益，请联系我们删除，谢谢。
       

      

	   
      
 标签：
        #oracle2291      

	   
     
 
    
 
     
    
 
     
 上一篇Rancher2.1.4使用记录 

     
 下一篇网购平台故意删除用户购物记录 法院：订单属个人信息，用户有权要求恢复 

    
 
	 
	 
    

	        
 
     
相关文章