Oceanbase-02.创建租户和使用

02.租户的使用

租户首次使用的步骤

步骤	作用
01.创建资源单元	指定每个单元要使用CPU（逻辑限制）、Memory（硬限制）、IOPS（不限制）、DISK（不限制） 资源分配时不要超过__ALL_VIRTUAL_SERVER_STAT剩余的可用资源
02.创建资源池	资源池需要指定资源单元以及要使用的zone
03.创建租户	创建租户指定副本数量，指定资源池，执行租户类型oracle、mysql。社区版仅支持mysql版
04.在租户上创建用户	用户是最终提交给终端用户使用的账号
05.提供使用	将账号提供给终端用户，视实际情况赋予相应权限

登录数据库

obclient、mysqlclient 都可以链接到oceabase

我首次安装完后，使用sys租户管理员进入了Oracle租户模式，使用root未成功进入Mysql租户模式，应该是和部署有关。

obclient -u[用户名]@[租户名]#[集群名称] -P[端口号] -h[ip地址] -p[密码] -D[数据库名] -c

当前支持 MySQL 客户端 V5.5、V5.6 和 V5.7 版本
mysql -u[用户名]@[租户名]#[集群名称] -P[端口号] -h[ip地址] -p[密码] -D[数据库名] -c  

用户名：root 租户管理用户、user1（其它用户）
租户名：sys  系统租户名、test_tenant自建租户
集群名：部署的集群名

• 链接obproxy服务器

示例：
ob 客户端链接 
[obdba@dbdriver soft]$ obclient -h10.51.XX.65 -P2883 -uroot@sys#obtest -p -c -A oceanbase
Enter password:
Welcome to the OceanBase.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.6.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [oceanbase]> SELECT version();
+--------------------+
| version()          |
+--------------------+
| 3.1.2-OceanBase CE |
+--------------------+
1 row in set (0.001 sec)

mysql 客户端 代理服务器
如果连接语句中不带 -c 项，则连接至 MySQL 租户后 HINT 无法生效。
[obdba@dbdriver soft]$ mysql -h10.51.XX.65 -P2883 -uroot@sys#obtest -p -c -A oceanbase
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.6.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT version();
+--------------------+
| version()          |
+--------------------+
| 3.1.2-OceanBase CE |
+--------------------+
1 row in set (0.00 sec)

• 直连observer

[obdba@dbdriver soft]$ obclient -h10.51.XX.66 -P2881 -uroot@sys -p -c -A oceanbase
Enter password:
Welcome to the OceanBase.  Commands end with ; or \g.
Your MySQL connection id is 3221668585
Server version: 5.7.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [oceanbase]> show processlist;
+------------+---------+--------------------+-----------+---------+------+--------+------------------+
| Id         | User    | Host               | db        | Command | Time | State  | Info             |
+------------+---------+--------------------+-----------+---------+------+--------+------------------+
| 3221668585 | root    | 10.51.XX.65:30152 | oceanbase | Query   |    0 | ACTIVE | show processlist |
| 3221752496 | proxyro | 10.51.XX.65:56586 | oceanbase | Sleep   |    1 | SLEEP  | NULL             |
+------------+---------+--------------------+-----------+---------+------+--------+------------------+
2 rows in set (0.005 sec)

mysql 客户端同理
[obdba@dbdriver soft]$ mysql -h10.51.XX.66 -P2881 -uroot@sys -p -c -A oceanbase

创建资源单元

• 查看可分配资源

MySQL [oceanbase]> select zone,cpu_total,cpu_assigned,round(mem_total/1024/1024/1024,2) MEM_TOTAL_GB,round(mem_assigned/1024/1024/1024,2) MEM_ASSIGNED_GB FROM __ALL_VIRTUAL_SERVER_STAT;
+-------+-----------+--------------+--------------+-----------------+
| zone  | cpu_total | cpu_assigned | MEM_TOTAL_GB | MEM_ASSIGNED_GB |
+-------+-----------+--------------+--------------+-----------------+
| zone1 |        14 |          2.5 |        32.00 |            8.00 |
| zone2 |        14 |          2.5 |        32.00 |            8.00 |
| zone3 |        14 |          2.5 |        32.00 |            8.00 |
+-------+-----------+--------------+--------------+-----------------+
3 rows in set (0.001 sec)

资源主要是cpu、memory资源限制,磁盘资源(空间、iops)、SESSION_NUM并未有的限制。已分配的cpu和memory资源等于所有unit的min资源总和。
如MEM_ASSIGNED_GB = sys_unit_config.min_memory + unit1.min_memory(待unit1创建完)，所以创建资源单元时要依据可用资源来创建。
如果创建的资源超过了可用范围，那么创建pool时会遇到报错。
ERROR 4634 (HY000): resource unit 'unit1' is referenced by some resource pool

• 创建资源单元

MySQL [oceanbase]>CREATE RESOURCE UNIT unit1 MAX_CPU 2, MAX_MEMORY '2G', MAX_IOPS 10000,MAX_DISK_SIZE '500G', MAX_SESSION_NUM 1000, MIN_CPU=2, MIN_MEMORY='2G',MIN_IOPS=1000;
Query OK, 0 rows affected (0.007 sec)

• 查看资源单元

MySQL [oceanbase]> SELECT * FROM oceanbase.__all_unit_config\G
*************************** 1. row ***************************
     gmt_create: 2022-05-15 10:28:40.361525
   gmt_modified: 2022-05-15 10:28:40.361525
 unit_config_id: 1
           name: sys_unit_config
        max_cpu: 5
        min_cpu: 2.5
     max_memory: 10307921510
     min_memory: 8589934592
       max_iops: 10000
       min_iops: 5000
  max_disk_size: 935229128704
max_session_num: 9223372036854775807
*************************** 2. row ***************************
     gmt_create: 2022-05-15 16:19:58.101575
   gmt_modified: 2022-05-15 16:32:21.134720
 unit_config_id: 1002
           name: unit1
        max_cpu: 9
        min_cpu: 2
     max_memory: 21474836480
     min_memory: 2147483648
       max_iops: 10000
       min_iops: 1000
  max_disk_size: 536870912000
max_session_num: 1000
2 rows in set (0.001 sec)

默认已经有了一个sys资源单元，新建的单元为unit1

• 修改资源单元

修改多个资源
ALTER RESOURCE UNIT unit1 MAX_CPU 10, MAX_MEMORY '20G', MAX_IOPS 128,max_disk_size '100G', MAX_SESSION_NUM 64, MIN_CPU=10, MIN_MEMORY='10G',MIN_IOPS=128;

修改某一个资源
ALTER RESOURCE UNIT unit1 MAX_CPU 64;

修改资源时MAX资源不能小于MIN资源

• 删除资源单元

删除未被使用的资源单元
MySQL [oceanbase]> DROP RESOURCE UNIT unit1;
Query OK, 0 rows affected (0.004 sec)

删除已经被分配的资源单元
如果unit1被分配且需要删除，可以先创建资源单元 unit2 ，并将 unit2 指定给 pool1 后，再删除unit1 

创建资源池

• 创建资源池

MySQL [oceanbase]> CREATE RESOURCE POOL pool1 UNIT='unit1',UNIT_NUM=1,ZONE_LIST=('zone1','zone2','zone3');
Query OK, 0 rows affected (0.012 sec)

• 删除资源池

MySQL [oceanbase]> DROP RESOURCE POOL pool1 ;

创建租户

• 创建租户

创建名为 test_tenant 的一个 3 副本的租户
CREATE TENANT IF NOT EXISTS test_tenant charset='utf8mb4',replica_num=3, zone_list=('zone1','zone2','zone3'), primary_zone='RANDOM',comment 'mysql tenant/instance', resource_pool_list=('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租户。

• 删除租户

当使用drop TENANT test_tenant时，oceanbas是延迟删除，应该加上force，实测在一段时间内登录该租户，这个具体受哪个参数控制还需要研究一下。
MySQL [oceanbase]>  drop TENANT test_tenant;
Query OK, 0 rows affected (0.013 sec)
尝试登录仍然可以登录
[root@dbdriver log]# obclient -h10.51.XX.65 -P2883 -uroot@test_tenant#obtest -p -c -A oceanbase
Enter password:
Welcome to the OceanBase.  Commands end with ; or \g.
Your MySQL connection id is 65546
Server version: 5.6.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [oceanbase]> exit
Bye
[root@dbdriver log]# obclient -h10.51.XX.65 -P2883 -uroot@test_tenant#obtest -p -c -A oceanbase

强制删除
MySQL [oceanbase]>  drop TENANT test_tenant force;
Query OK, 0 rows affected (0.020 sec)
[root@dbdriver log]# obclient -h10.51.XX.65 -P2883 -uroot@test_tenant#obtest -p -c -A oceanbase
Enter password:
ERROR 1045 (42000): Access denied for user 'root'@'xxx.xxx.xxx.xxx' (using password: NO)

• 切换租户

不退出sys租户，切换到test_tenant租户
MySQL [oceanbase]> alter system change tenant test_tenant;
当前链接的租户
MySQL [oceanbase]> SHOW TENANT;
+---------------------+
| Current_tenant_name |
+---------------------+
| test_tenant         |
+---------------------+
1 row in set (0.005 sec)


切换回sys租户
MySQL [oceanbase]> alter system change tenant sys;
当前链接的租户
MySQL [oceanbase]> SHOW TENANT;
+---------------------+
| Current_tenant_name |
+---------------------+
| sys                 |
+---------------------+
1 row in set (0.002 sec)

• 修改租户

修改租户资源
修改租户 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='%';

• 查看租户参数

登录或切换到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租户中
[obdba@dbdriver ~]$  obclient -h10.51.XX.65 -P2883 -uroot@test_tenant#obtest -p -A 

只要登录的租户正确，那么创建用户的操作基本就和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)

新建用户测试登录
[obdba@dbdriver ~]$  obclient -h10.51.XX.65 -P2883 -uuser1@test_tenant#obtest -p -A
Enter password:
Welcome to the OceanBase.  Commands end with ; or \g.
Your MySQL connection id is 51
Server version: 5.6.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.012 sec)

数据字典

数据库	字典	描述
oceanbase	__all_unit_config	资源单元，情况
oceanbase	__all_virtual_server_stat	虚拟服务器资源，资源单元分配要考虑从中计算
oceanbase	__all_resource_pool	资源池信息
oceanbase	__all_tenant	租户信息基表
oceanbase	gv$tenant	租户信息视图-基表是__all_tenant

摘抄几个SQL

SELECT a.zone,
       CONCAT(a.svr_ip, ':', a.svr_port) observer,
       cpu_total,
       (cpu_total - cpu_assigned) cpu_free,
       round(mem_total / 1024 / 1024 / 1024) mem_total_gb,
       round((mem_total - mem_assigned) / 1024 / 1024 / 1024) mem_free_gb,
       round(disk_total / 1024 / 1024 / 1024) disk_total_gb,
       substr(a.build_version, 1, 6) version,
       usec_to_time(b.start_service_time) start_service_time
  FROM __all_virtual_server_stat a
  JOIN __all_server b
    ON (a.svr_ip = b.svr_ip AND a.svr_port = b.svr_port)
 ORDER BY a.zone, a.svr_ip;

+-------+-------------------+-----------+----------+--------------+-------------+---------------+---------+----------------------------+
| zone  | observer          | cpu_total | cpu_free | mem_total_gb | mem_free_gb | disk_total_gb | version | start_service_time         |
+-------+-------------------+-----------+----------+--------------+-------------+---------------+---------+----------------------------+
| zone1 | 10.51.XX.66:2882 |        14 |      9.5 |           32 |          22 |           871 | 3.1.2_  | 2022-05-15 10:28:42.486979 |
| zone2 | 10.51.XX.67:2882 |        14 |      9.5 |           32 |          22 |           871 | 3.1.2_  | 2022-05-15 10:28:44.516852 |
| zone3 | 10.51.XX.68:2882 |        14 |      9.5 |           32 |          22 |           871 | 3.1.2_  | 2022-05-15 10:28:44.687763 |
+-------+-------------------+-----------+----------+--------------+-------------+---------------+---------+----------------------------+
3 rows in set (0.019 sec)

SELECT t1.name resource_pool_name,
       t2. name unit_config_name,
       t2.max_cpu,
       t2.min_cpu,
       round(t2.max_memory / 1024 / 1024 / 1024) max_mem_gb,
       round(t2.min_memory / 1024 / 1024 / 1024) min_mem_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 | max_mem_gb | min_mem_gb | unit_id | zone  | observer          | tenant_id | tenant_name |
+--------------------+------------------+---------+---------+------------+------------+---------+-------+-------------------+-----------+-------------+
| sys_pool           | sys_unit_config  |       5 |     2.5 |         10 |          8 |       1 | zone1 | 10.51.XX.66:2882 |         1 | sys         |
| sys_pool           | sys_unit_config  |       5 |     2.5 |         10 |          8 |       2 | zone2 | 10.51.XX.67:2882 |         1 | sys         |
| sys_pool           | sys_unit_config  |       5 |     2.5 |         10 |          8 |       3 | zone3 | 10.51.XX.68:2882 |         1 | sys         |
| pool1              | unit1            |       4 |       2 |          2 |          2 |    1007 | zone1 | 10.51.XX.66:2882 |      1003 | test_tenant |
| pool1              | unit1            |       4 |       2 |          2 |          2 |    1008 | zone2 | 10.51.XX.67:2882 |      1003 | test_tenant |
| pool1              | unit1            |       4 |       2 |          2 |          2 |    1009 | zone3 | 10.51.XX.68:2882 |      1003 | test_tenant |
+--------------------+------------------+---------+---------+------------+------------+---------+-------+-------------------+-----------+-------------+
6 rows in set (0.009 sec)