OB创建业务数据对象

1. 创建业务租户及相关对象数据

查看集群可用资源

select a.zone,concat(a.svr_ip,':',a.svr_port) observer, cpu_total, cpu_assigned, (cpu_total-cpu_assigned) cpu_free, mem_total/1024/1024/1024 mem_total_gb, mem_assigned/1024/1024/1024 mem_assign_gb, (mem_total-mem_assigned)/1024/1024/1024 mem_free_gb 
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
;

已分配资源池情况

select t1.name resource_pool_name, t2.`name` unit_config_name, t2.max_cpu, t2.min_cpu, t2.max_memory/1024/1024/1024 max_mem_gb, 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），由每个 zone 里的一个节点上的资源单元（resource unit）组成，每个资源单元使用同一规格（sys_unit_config）。 从上面可以看出，资源单元规格 sys_unit_config 的定义里 CPU 和 内存的最小值和最大值定义不一样，前面统计资源里的已分配资源是按最小值计算的。

建议：资源单元规格定义的 CPU 和内存的最小值和最大值一样

alter resource unit sys_unit_config min_cpu=5,max_cpu=5,min_memory='1288490188B',max_memory='1288490188B';

创建 resource unit

create resource unit pay_unit_config max_cpu=2, min_cpu=2, max_memory='1G', min_memory='1G', max_iops=1000, min_iops=1000, max_session_num=100, max_disk_size='10G'; 

创建 resource pool

create resource pool pay_pool unit = 'pay_unit_config', unit_num = 1;

创建租户

create tenant pay_mysql_tat resource_pool_list=('pay_pool'), primary_zone='RANDOM',comment 'mysql tenant/instance' set ob_tcp_invited_nodes='192.168.10.%', ob_compatibility_mode='mysql';

ob_tcp_invited_nodes : 参数指定客户端连接主机

连接测试

obclient -h192.168.10.181 -uroot@pay_mysql_tat#obce -P2883 -c -A oceanbase

obclient -h127.1 -uroot@pay_mysql_tat#obce -P2883 -c -A oceanbase

创建数据库

使用新建的租户连接并创建数据库

CREATE DATABASE `paydb` DEFAULT CHARACTER SET = utf8mb4 REPLICA_NUM = 1;

创建用户

-- 使用新建的租户连接并创建用户
create user `payer`@`192.168.10.%` identified by '123456';
grant select,insert,update,delete,create,drop on paydb.* to `payer`@`192.168.10.%`;
grant all privileges on paydb.* to `payer`@`192.168.10.%`;

select host, user, password, plugin, authentication_string from mysql.user;

登陆测试

obclient -h192.168.10.181 -upayer@pay_mysql_tat#obce -P2883 -c -A paydb -p

# 返回当前的用户名与主机名
SELECT SESSION_USER();

# 返回登录账户名称的字符串
SELECT CURRENT_USER();

创建业务对象表

obclient -h192.168.10.181 -upayer@pay_mysql_tat#obce -P2883 -c -A paydb -p

DROP TABLE IF EXISTS `customers`;
CREATE TABLE`customers` (
  `customerNumber` bigint(11) NOT NULL,
  `customerName` varchar(50) NOT NULL,
  `contactLastName` varchar(50) NOT NULL,
  `contactFirstName` varchar(50) NOT NULL,
  `phone` varchar(50) NOT NULL,
  `addressLine1` varchar(50) NOT NULL,
  `addressLine2` varchar(50) DEFAULT NULL,
  `city` varchar(50) NOT NULL,
  `state` varchar(50) DEFAULT NULL,
  `postalCode` varchar(15) DEFAULT NULL,
  `country` varchar(50) NOT NULL,
  `salesRepEmployeeNumber` bigint(11) DEFAULT NULL,
  `creditLimit` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`customerNumber`),
  KEY `salesRepEmployeeNumber` (`salesRepEmployeeNumber`) GLOBAL
) partition by hash(`customernumber`) partitions 6
;

DROP TABLE IF EXISTS `orders`;
CREATE TABLE`orders` (
  `orderNumber` int(11) NOT NULL,
  `orderDate` date NOT NULL,
  `requiredDate` date NOT NULL,
  `shippedDate` date DEFAULT NULL,
  `status` varchar(15) NOT NULL,
  `comments` text DEFAULT NULL,
  `customerNumber` int(11) NOT NULL,
  PRIMARY KEY (`orderNumber`, `customerNumber`),
  KEY `customerNumber` (`customerNumber`) GLOBAL
) PARTITION by HASH(customerNumber) PARTITIONS 6
;

DROP TABLE IF EXISTS `orderdetails`;
CREATE TABLE `orderdetails` (
  `orderNumber` bigint(11) NOT NULL,
  `productCode` varchar(15) NOT NULL,
  `quantityOrdered` bigint(11) NOT NULL,
  `priceEach` decimal(10,2) NOT NULL,
  `orderLineNumber` smallint(6) NOT NULL,
  `customerNumber` bigint(11) NOT NULL,
  PRIMARY KEY (`customerNumber`, `orderNumber`, `productCode` ),
  KEY `productCode` (`productCode`) GLOBAL
) PARTITION by HASH(customerNumber) PARTITIONS 6;

创建表组

表组（Table Group）不是一个的物理对象，它是一个逻辑概念，表示一组表或者表的集合

create tablegroup tg_orders partition by hash partitions 6;

alter tablegroup tg_orders add customers, orders, orderdetails;
show tablegroups;

注意:

• 只有root用户可以创建tablegroup对象