OB官网又可以下载了!


有关 OceanBase 独立安装部署的方法我此前已经总结了多篇文章,并有相应的视频演示。我也专门总结了一篇文章: 《​OceanBase 2.2 安装部署问题解答​ 》。


下面主要演示两种非常规OceanBase 用法,仅用于 OceanBase 学习研究

  • 单机上起 3 个 observer 进程,模拟三个 observer 节点。机器内存比较大(大于 180G)
  • 起一个租户,租户的模式是 2F1L,即 2 个全功能副本 1 个日志副本。日志副本对资源需求要小很多。


下面的安装脚本是整体安装步骤的部分脚本,请先查看之前的安装步骤介绍:《OceanBase 2.x体验:手动搭建OceanBase集群。相关步骤的意义前文都说了,所以下面只有相关的脚本。

能看懂的,算 OceanBase 高级玩家了。????



清理老的进程和目录(第一次不用)

su - admin

kill -9 `pidof observer`
sleep 3
/bin/rm -rf /data/1/node*/obdemo
/bin/rm -rf /data/log1/node*/obdemo
/bin/rm -rf /home/admin/node*/oceanbase/store/obdemo /home/admin/node*/oceanbase/log/* /home/admin/node*/oceanbase/etc/*config*

初始化目录

先安装 oceanbase 软件,目录在 /home/admin/oceanbase 上。然后复制该软件目录为 node1 node2 node3

su - admin​​cd ~/
mkdir node1 node2 node3

/bin/cp -rf oceanbase node1/
/bin/cp -rf oceanbase node2/
/bin/cp -rf oceanbase node3/


准备数据目录和日志目录

cd /data/1
mkdir node1 node2 node3
cd /data/log1/
mkdir node1 node2 node3

mkdir -p /data/1/node1/obdemo/{etc3,sort_dir,sstable}
mkdir -p /data/log1/node1/obdemo/{clog,etc2,ilog,slog,oob_clog}
mkdir -p /home/admin/node1/oceanbase/store/obdemo
for t in {etc3,sort_dir,sstable};do ln -s /data/1/node1/obdemo/$t /home/admin/node1/oceanbase/store/obdemo/$t; done
for t in {clog,etc2,ilog,slog,oob_clog};do ln -s /data/log1/node1/obdemo/$t /home/admin/node1/oceanbase/store/obdemo/$t; done

mkdir -p /data/1/node2/obdemo/{etc3,sort_dir,sstable}
mkdir -p /data/log1/node2/obdemo/{clog,etc2,ilog,slog,oob_clog}
mkdir -p /home/admin/node2/oceanbase/store/obdemo
for t in {etc3,sort_dir,sstable};do ln -s /data/1/node2/obdemo/$t /home/admin/node2/oceanbase/store/obdemo/$t; done
for t in {clog,etc2,ilog,slog,oob_clog};do ln -s /data/log1/node2/obdemo/$t /home/admin/node2/oceanbase/store/obdemo/$t; done


mkdir -p /data/1/node3/obdemo/{etc3,sort_dir,sstable}
mkdir -p /data/log1/node3/obdemo/{clog,etc2,ilog,slog,oob_clog}
mkdir -p /home/admin/node3/oceanbase/store/obdemo
for t in {etc3,sort_dir,sstable};do ln -s /data/1/node3/obdemo/$t /home/admin/node3/oceanbase/store/obdemo/$t; done
for t in {clog,etc2,ilog,slog,oob_clog};do ln -s /data/log1/node3/obdemo/$t /home/admin/node3/oceanbase/store/obdemo/$t; done


tree /data/1/node*/
/data/1/node1/
└── obdemo
├── etc3
├── sort_dir
└── sstable
/data/1/node2/
└── obdemo
├── etc3
├── sort_dir
└── sstable
/data/1/node3/
└── obdemo
├── etc3
├── sort_dir
└── sstable

tree /data/log1/node*/
/data/log1/node1/
└── obdemo
├── clog
├── etc2
├── ilog
├── oob_clog
└── slog
/data/log1/node2/
└── obdemo
├── clog
├── etc2
├── ilog
├── oob_clog
└── slog
/data/log1/node3/
└── obdemo
├── clog
├── etc2
├── ilog
├── oob_clog
└── slog

启动 3 个 observer 进程

注意:ip、网卡名都要根据实际情况调整。

cd /home/admin/node1/oceanbase && /home/admin/node1/oceanbase/bin/observer -i eth3 -P 2882 -p 2881 -z zone1 -d /home/admin/node1/oceanbase/store/obdemo -r '172.16.11.93:2882:2881;172.16.11.93:3882:3881;172.16.11.93:4882:4881' -c 20200102 -n obdemo -o "system_memory=10G,memory_limit=96G,datafile_size=100G,config_additional_dir=/data/1/node1/obdemo/etc3;/data/log1/node1/obdemo/etc2"

cd /home/admin/node2/oceanbase && /home/admin/node1/oceanbase/bin/observer -i eth3 -P 3882 -p 3881 -z zone2 -d /home/admin/node2/oceanbase/store/obdemo -r '172.16.11.93:2882:2881;172.16.11.93:3882:3881;172.16.11.93:4882:4881' -c 20200102 -n obdemo -o "system_memory=10G,memory_limit=96G,datafile_size=100G,config_additional_dir=/data/1/node2/obdemo/etc3;/data/log1/node2/obdemo/etc2"

cd /home/admin/node3/oceanbase && /home/admin/node3/oceanbase/bin/observer -i eth3 -P 4882 -p 4881 -z zone3 -d /home/admin/node3/oceanbase/store/obdemo -r '172.16.11.93:2882:2881;172.16.11.93:3882:3881;172.16.11.93:4882:4881' -c 20200102 -n obdemo -o "system_memory=10G,memory_limit=96G,datafile_size=100G,config_additional_dir=/data/1/node3/obdemo/etc3;/data/log1/node3/obdemo/etc2"

bootstrap 集群

obclient -h127.1 -uroot -P2881 -p

set session ob_query_timeout=1000000000; alter system bootstrap ZONE 'zone1' SERVER '172.16.11.93:2882', ZONE 'zone2' SERVER '172.16.11.93:3882', ZONE 'zone3' SERVER '172.16.11.93:4882' ;

这一步是关键。不成功,就推到重来。看看机器环境或者前面哪里哪个步骤不对。一切都对的情况下一定能成功。

初始化集群连接账户

obclient -h127.1 -uroot@sys -P2881 -p
alter user root identified by '123456';
CREATE USER proxyro IDENTIFIED BY password '*e9c2bcdc178a99b7b08dd25db58ded2ee5bff050' ;
GRANT SELECT ON *.* to proxyro;
show grants for proxyro;

模拟 OCP API(可选)

nohup python configurl_server.py `hostname -i` 8088 2>&1 1>/tmp/configurl_server.log &​​

$netstat -ntlp |grep 8088

(Not all processes could be identified, non-owned process info

 will not be shown, you would have to be root to see it all.)

tcp        0      0 0.0.0.0:8088            0.0.0.0:*               LISTEN      287260/python


设置 OB 集群参数,指向前面 API。

alter system set obconfig_url='http://172.16.11.93:8088/services?Action=ObRootServiceInfo&ObRegion=obdemo';

启动 OBProxy

cd /opt/taobao/install/obproxy && bin/obproxy -p2883 -cobdemo -o "obproxy_config_server_url=http://172.16.11.93:8088/services?Action=GetObProxyConfig&User_ID=alibaba-inc&uid=ocpmaster,enable_cluster_checkout=false,enable_strict_kernel_release=false,enable_metadb_used=false"

查看建资源

alter resource unit sys_unit_config min_cpu=5, max_memory='26G';


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, usec_to_time(b.last_offline_time) last_offline_time, usec_to_time(b.start_service_time) start_service_time, b.status
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 | last_offline_time | start_service_time | status |
+-------+-------------------+-----------+----------+--------------+-------------+----------------------------+----------------------------+--------+
| zone1 | 172.16.11.93:2882 | 62 | 57 | 86 | 65 | 1970-01-01 08:00:00.000000 | 2020-06-09 15:47:20.441569 | active |
| zone2 | 172.16.11.93:3882 | 62 | 57 | 86 | 65 | 1970-01-01 08:00:00.000000 | 2020-06-09 15:47:20.393345 | active |
| zone3 | 172.16.11.93:4882 | 62 | 57 | 86 | 65 | 1970-01-01 08:00:00.000000 | 2020-06-09 15:47:20.950705 | active |
+-------+-------------------+-----------+----------+--------------+-------------+----------------------------+----------------------------+--------+
3 rows in set (0.01 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 | 5 | 26 | 22 | 1 | zone1 | 172.16.11.93:2882 | 1 | sys |
| sys_pool | sys_unit_config | 5 | 5 | 26 | 22 | 2 | zone2 | 172.16.11.93:3882 | 1 | sys |
| sys_pool | sys_unit_config | 5 | 5 | 26 | 22 | 3 | zone3 | 172.16.11.93:4882 | 1 | sys |
+--------------------+------------------+---------+---------+------------+------------+---------+-------+-------------------+-----------+-------------+
6 rows in set (0.00 sec)

创建租户

创建一个 2F1L类型的租户。F 指 全功能副本,包含数据和日志。 L 是日志副本,只包含日志。即只接受事物日志,在多副本日志同步中参与投票,对高可用有重要意义。日志副本不包含数据, 没有回放日志的需求,所以对机器资源的要求可以低一些。

obclient -h127.1 -uroot@sys#obdemo -P2883 -p123456 -c -A oceanbase

CREATE resource unit S_UNIT_CONFIG_1 max_cpu=10, min_cpu=10, max_memory='30G', min_memory='30G', max_iops=10000, min_iops=1000, max_session_num=1000000, max_disk_size='100G';
CREATE resource unit S_UNIT_CONFIG_2 max_cpu=5, min_cpu=5, max_memory='10G', min_memory='10G', max_iops=10000, min_iops=1000, max_session_num=1000000, max_disk_size='100G';

CREATE resource pool p_data unit = 'S_UNIT_CONFIG_1', unit_num = 1, zone_list=('zone1','zone2');
CREATE resource pool p_log unit = 'S_UNIT_CONFIG_2', unit_num = 1, zone_list=('zone3'), REPLICA_TYPE='LOGONLY';

create tenant obbmsql resource_pool_list=('p_data','p_log'), primary_zone='RANDOM',comment 'oracle tenant/instance', charset='utf8', locality="FULL{1}@zone1, FULL{1}@zone2, LOGONLY{1}@zone3" set ob_tcp_invited_nodes='%', ob_compatibility_mode='oracle';

再次查看集群资源和租户资源分配细节

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, usec_to_time(b.last_offline_time) last_offline_time, usec_to_time(b.start_service_time) start_service_time, b.status
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 | last_offline_time | start_service_time | status |
+-------+-------------------+-----------+----------+--------------+-------------+----------------------------+----------------------------+--------+
| zone1 | 172.16.11.93:2882 | 62 | 47 | 86 | 35 | 1970-01-01 08:00:00.000000 | 2020-06-09 15:47:20.441569 | active |
| zone2 | 172.16.11.93:3882 | 62 | 47 | 86 | 35 | 1970-01-01 08:00:00.000000 | 2020-06-09 15:47:20.393345 | active |
| zone3 | 172.16.11.93:4882 | 62 | 52 | 86 | 55 | 1970-01-01 08:00:00.000000 | 2020-06-09 15:47:20.950705 | active |
+-------+-------------------+-----------+----------+--------------+-------------+----------------------------+----------------------------+--------+
3 rows in set (0.00 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 | 5 | 26 | 22 | 1 | zone1 | 172.16.11.93:2882 | 1 | sys |
| sys_pool | sys_unit_config | 5 | 5 | 26 | 22 | 2 | zone2 | 172.16.11.93:3882 | 1 | sys |
| sys_pool | sys_unit_config | 5 | 5 | 26 | 22 | 3 | zone3 | 172.16.11.93:4882 | 1 | sys |
| p_data | S_UNIT_CONFIG_1 | 10 | 10 | 30 | 30 | 1001 | zone1 | 172.16.11.93:2882 | 1001 | obbmsql |
| p_data | S_UNIT_CONFIG_1 | 10 | 10 | 30 | 30 | 1002 | zone2 | 172.16.11.93:3882 | 1001 | obbmsql |
| p_log | S_UNIT_CONFIG_2 | 5 | 5 | 10 | 10 | 1003 | zone3 | 172.16.11.93:4882 | 1001 | obbmsql |
+--------------------+------------------+---------+---------+------------+------------+---------+-------+-------------------+-----------+-------------+
6 rows in set (0.00 sec)

初始化租户里的数据库

初始化租户的用户。

obclient -h127.1 -usys@obbmsql -P2881 -p -c -A

alter user sys identified by 123456;
CREATE USER scott identified BY tiger;
GRANT ALL PRIVILEGES ON scott.* TO scott WITH GRANT option;
GRANT SELECT,CREATE ,DROP ON *.* TO scott;

CREATE USER tpcc identified BY 123456;
GRANT ALL PRIVILEGES ON tpcc.* TO tpcc WITH GRANT OPTION;
GRANT SELECT,CREATE ,DROP ON *.* TO tpcc;
GRANT CREATE SYNONYM ON *.* TO tpcc;

SELECT * FROM dba_users;

SHOW GRANTS FOR scott;
SHOW grants FOR tpcc;

创建业务表

obclient -h127.1 -uscott@obbmsql -P2881 -ptiger -c -A

create table t1(id number not null, name varchar2(50) not null ,gmt_create date not null default sysdate );

insert into t1(id,name) values(1,'A'),(2,'B'),(3,'C'); commit;

查看租户的分区分布

obclient -h127.1 -uroot@sys#obdemo -P2883 -p123456 -c -A oceanbase

SELECT t1.tenant_id,t1.tenant_name,t2.database_name,t3.table_id,t3.table_Name,t3.tablegroup_id,t3.part_num,t4.partition_Id, t4.zone,t4.svr_ip,t4.role, t4.replica_type, round(t4.data_size/1024/1024) data_size_mb
from `gv$tenant` t1
join `gv$database` t2 on (t1.tenant_id = t2.tenant_id)
join gv$table t3 on (t2.tenant_id = t3.tenant_id and t2.database_id = t3.database_id and t3.index_type = 0)
left join `__all_virtual_meta_table` t4 on (t2.tenant_id = t4.tenant_id and ( t3.table_id = t4.table_id or t3.tablegroup_id = t4.table_id ) and t4.role in (1,2))
where t1.tenant_id = 1001
order by t3.tablegroup_id, t4.partition_Id, t3.table_name ;

+-----------+-------------+---------------+------------------+------------+---------------+----------+--------------+-------+--------------+------+--------------+--------------+
| tenant_id | tenant_name | database_name | table_id | table_Name | tablegroup_id | part_num | partition_Id | zone | svr_ip | role | replica_type | data_size_mb |
+-----------+-------------+---------------+------------------+------------+---------------+----------+--------------+-------+--------------+------+--------------+--------------+
| 1001 | obbmsql | SCOTT | 1100611139453777 | T1 | -1 | 1 | 0 | zone1 | 172.16.11.93 | 1 | 0 | 0 |
| 1001 | obbmsql | SCOTT | 1100611139453777 | T1 | -1 | 1 | 0 | zone2 | 172.16.11.93 | 2 | 0 | 0 |
| 1001 | obbmsql | SCOTT | 1100611139453777 | T1 | -1 | 1 | 0 | zone3 | 172.16.11.93 | 2 | 5 | 0 |
+-----------+-------------+---------------+------------------+------------+---------------+----------+--------------+-------+--------------+------+--------------+--------------+
3 rows in set (0.04 sec)


其中 role=2, replica_type=5 表示是 LOGONLY 副本。OceanBase 的两地三中心五副本架构中,可以选择有 1-2 个 L 副本,以节省机器资源。