引言

此前在《OceanBase数据库实践入门——手动搭建OceanBase集群》里介绍过OceanBase 1.4的手动搭建方法。OceanBase 2.x发布后，新增对ORACLE兼容性的支持，官网近期已提供OB2.x下载(https://oceanbase.alipay.com/download/resource)，以及OCP 2.3自动化搭建OceanBase 2.x集群的方法。这里提供手动搭建OceanBase集群可以精确控制资源的利用，同时再次熟悉一下OceanBase集群的原理，对后期运维功能理解会更好一些。后面我也会分享Docker技术部署OCP和使用OCP自动化部署OB集群经验。

1. 前置条件检查

有关机器前置条件检查前文已经详细介绍，这里就不再赘述。直接贴关键几个配置文件。

1.1 节点配置

内核参数

cat >> /etc/sysctl.conf <<EOF
fs.file-max = 655350
fs.aio-max-nr = 3145728

kernel.core_uses_pid = 1
kernel.exec-shield = 1
kernel.msgmax = 65536
kernel.msgmnb = 65536
kernel.randomize_va_space = 1
kernel.sem = 250 32000 100 128
kernel.shmall = 4294967296
kernel.shmmax = 68719476736
kernel.sysrq = 0

net.bridge.bridge-nf-call-arptables = 0
net.bridge.bridge-nf-call-ip6tables = 0
net.bridge.bridge-nf-call-iptables = 0
net.core.netdev_max_backlog = 10000
net.core.rmem_default = 262144
net.core.rmem_max = 16777216
net.core.somaxconn = 2048
net.core.wmem_default = 262144
net.core.wmem_max = 16777216
net.ipv4.conf.all.accept_redirects = 0
net.ipv4.conf.all.accept_source_route =  0
net.ipv4.conf.all.arp_announce = 2
net.ipv4.conf.all.arp_ignore = 1
net.ipv4.conf.all.rp_filter = 1
net.ipv4.conf.all.secure_redirects = 0
net.ipv4.conf.all.send_redirects = 0
net.ipv4.conf.default.accept_redirects = 0
net.ipv4.conf.default.accept_source_route = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.secure_redirects = 0
net.ipv4.conf.default.send_redirects = 0
net.ipv4.conf.lo.arp_announce = 2
net.ipv4.conf.lo.arp_ignore = 1
net.ipv4.icmp_echo_ignore_broadcasts = 1
net.ipv4.icmp_ignore_bogus_error_responses = 1
net.ipv4.ip_forward = 0
net.ipv4.ip_local_port_range = 40000 65535
net.ipv4.tcp_fin_timeout = 5
net.ipv4.tcp_max_syn_backlog = 4096
net.ipv4.tcp_mem = 8388608 12582912 16777216
net.ipv4.tcp_rmem = 8192 87380 16777216
net.ipv4.tcp_synack_retries = 2
net.ipv4.tcp_syncookies = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_wmem = 8192 65536 16777216
net.netfilter.nf_conntrack_max = 1200000
net.nf_conntrack_max = 1200000

vm.mmap_min_addr = 4096
vm.min_free_kbytes = 398520
vm.swappiness = 0
fs.bio_netoops = 0
EOF

会话限制

cat >> /etc/security/limits.conf <<EOF
* soft nofile 655360
* hard nofile 655360
* soft nproc 655360
* hard nproc 655360
* soft stack unlimited
* hard stack unlimited
EOF

1.2 节点时间同步检查(重要)

如果内网没有NTP源，就选择其中一台做NTP源。具体配置方法请参考网上文章。
检查方法如下，节点之间互相运行clockdiff命令。

clockdiff 11.*.84.79
clockdiff 11.*.84.84
clockdiff 11.*.84.78

2. 安装目录和软件

2.1 清理老的OB(第一次不用)

su - admin
kill -9 `pidof observer`
sleep 3
/bin/rm /data/1/obdemo/{etc3,sort_dir,sstable}/*
/bin/rm /data/log1/obdemo/{clog,etc2,ilog,slog,oob_clog}/*
/bin/rm /home/admin/oceanbase/store/obdemo/* /home/admin/oceanbase/log/* /home/admin/oceanbase/etc/*config*
ps -ef|grep observer
df -h |egrep home\|data

2.2 安装observer软件

首先确保admin用户已经创建，相应的文件系统目录{/home/admin/ , /data/1 , /data/log1 }都存在，并且空间大小符合要求。

$sudo rpm -ivh oceanbase-2.2.30-1855102.el7.x86_64.rpm
warning: Unable to get systemd shutdown inhibition lock: Unit is masked.
Preparing...                          ################################# [100%]
Updating / installing...
   1:oceanbase-2.2.30-1855102.el7     ################################# [100%]

oceanbase软件会安装在目录/home/admin/oceanbase下。

2.3 初始化目录(第一次用)

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

2.4 测试IO能力(第一次用)

测试数据盘IO能力，生成性能报告文件放在 /home/admin/oceanbase/etc 目录下，observer启动时会读取这个文件进而自动设置内部一些跟IO有关的参数。
每个observer节点都需要运行。这个比较费时间。

$time /home/admin/oceanbase/bin/ob_admin io_bench -c /home/admin/oceanbase/etc -d /data/1/obdemo user:root
succ to open, filename=ob_admin.log, fd=3, wf_fd=2

real    6m10.313s
user    1m27.734s
sys     1m15.621s
cat /home/admin/oceanbase/etc/io_resource.conf
version 1
io_type    io_size_byte    io_ps           io_rt_us
0          4096            68154.25        234.46
0          8192            44714.00        160.50
0          16384           26674.75        190.22
0          32768           13843.00        260.55
0          65536           7490.75         397.28
0          131072          3529.75         583.39
0          262144          1788.75         919.48
0          524288          927.00          1674.65
1          2097152         147.50          7301.02
submit_thread_cnt 8
getevent_thread_cnt 8

2.5 安装OB客户端

在下载文件里找到 obclient-*.rpm，这个是OceanBase命令行客户端，可以访问OceanBase的MySQL和ORACLE租户。

$sudo rpm -ivh obclient-1.1.6-20191211162923.el7.alios7.x86_64.rpm

$which obclient
/usr/bin/obclient

3. 初始化observer集群

注意：由于公司安全要求，我对我的测试ip其中部分字段用*号打码了，这不是安装需求。

3.1 启动节点observer进程

到每个节点的admin用户下，启动observer进程。注意每个节点启动参数并不完全相同。

参数里指定数据文件的大小、内存的大小，以方便个别环境资源不足想精确控制observer对资源的占用。我的测试机器内存比较小，默认system_memory要30G内存，这里我限制为10G。我的环境数据和日志目录是一个文件系统(共用空间)，所以限制一下数据文件大小datafile_size。

zone1: 
su - admin
cd /home/admin/oceanbase && /home/admin/oceanbase/bin/observer -i bond0 -P 2882 -p 2881 -z zone1 -d /home/admin/oceanbase/store/obdemo -r '11.*.84.78:2882:2881;11.*.84.79:2882:2881;11.*.84.84:2882:2881' -c 20200102 -n obdemo -o "datafile_size=100G,config_additional_dir=/data/1/obdemo/etc3;/data/log1/obdemo/etc2"
sleep 5
ps -ef|grep observer
zone2:
su - admin
cd /home/admin/oceanbase && /home/admin/oceanbase/bin/observer -i bond0 -P 2882 -p 2881 -z zone2 -d /home/admin/oceanbase/store/obdemo -r '11.*.84.78:2882:2881;11.*.84.79:2882:2881;11.*.84.84:2882:2881' -c 20200102 -n obdemo -o "system_memory=10G,memory_limit=61440M,datafile_size=100G,config_additional_dir=/data/1/obdemo/etc3;/data/log1/obdemo/etc2"
sleep 5
ps -ef|grep observer
zone3:
su - admin
cd /home/admin/oceanbase && /home/admin/oceanbase/bin/observer -i bond0 -P 2882 -p 2881 -z zone3 -d /home/admin/oceanbase/store/obdemo -r '11.*.84.78:2882:2881;11.*.84.79:2882:2881;11.*.84.84:2882:2881' -c 20200102 -n obdemo -o "datafile_size=100G,config_additional_dir=/data/1/obdemo/etc3;/data/log1/obdemo/etc2"
sleep 5
ps -ef|grep observer

3.2 集群bootstrap(成败关键)

随意登录一台节点，密码为空。

mysql -h127.1 -uroot -P2881 -p
Enter password:
set session ob_query_timeout=1000000000;alter system bootstrap ZONE 'zone1' SERVER '11.*.84.78:2882', ZONE 'zone2' SERVER '11.*.84.79:2882', ZONE 'zone3' SERVER '11.*.84.84:2882';

注意：如果这一步失败报错了，其原因很可能就是三节点observer进程启动参数有不对、observer相关目录权限不对、日志目录空间不足一定比例(跟数据目录合用了大目录，空间被数据目录占用了)、三节点时间不同步、节点内存资源不足等等。请先排查这些问题点后，然后清理OB(从2.1开始)从头开始。

3.3 验证集群初始化成功

$mysql -h127.1 -uroot@sys -P2881 -p -c -A
Enter password:
show databases;

能看到数据库列表里有oceanbase即可。

sys租户的root密码默认为空，初始化成功后请修改密码。

obclient> alter user root identified by "123456";
Query OK, 0 rows affected (0.01 sec)

3.4 集群参数初始化

如果 /home/admin 目录空间很紧张，则设置运行日志滚动输出。

mysql -h127.1 -uroot@sys -P2881 -p
Enter password:
-- observer log自清理设置
alter system set enable_syslog_recycle=True;
alter system set max_syslog_file_count=10;
show parameters where name in ('enable_syslog_recycle', 'max_syslog_file_count');

4. OCP API模拟(可选)

OCP API是为了模拟解决一个运维难题(三节点没有这个问题)，详情参考《自动化运维产品的命门——元数据库》。

4.1 模拟启动一个web服务器

用python搭建一个web 服务器，提供一个api用于读取和写入OceanBase参数文件。
config_server.py 没有找到合适的地方提供下载，有兴趣的可以单独公众号留言获取。

nohup python configurl_server.py 11.*.84.83 8088 2>&1 1>/tmp/configurl_server.log &

4.2 设置OceanBase集群rootservice list参数

mysql -h11.*.84.79 -uroot@sys -P2881 -p
Enter password:
alter system set obconfig_url='http://11.*.84.83:8088/services?Action=ObRootServiceInfo&ObRegion=obdemo';
exit;

等一段时间或者重启一个observer进程

$kill -9 `pidof observer`
sleep 3
cd /home/admin/oceanbase && bin/observer

验证 API效果

$curl -Ls "http://11.*.84.83:8088/services?Action=ObRootServiceInfo&ObRegion=obdemo"
{"Code":200,"Cost":1,"Data":{"ObRegion":"obdemo","ObCluster":"obdemo","ObRegionId":20200102,"ObClusterId":20200102,"Type":"PRIMARY","timestamp":1572786028035980,"RsList":[{"address":"11.*.84.78:2882","role":"LEADER","sql_port":2881},{"address":"11.*.84.79:2882","role":"FOLLOWER","sql_port":2881},{"address":"11.*.84.84:2882","role":"FOLLOWER","sql_port":2881}],"ReadonlyRsList":[]},"Message":"successful","Success":true}
config_server.py 会在同一目录生成一个 config_server.conf文件记录rootservice地址。

$cat configurl_server.conf
{"ObRegion":"obdemo","ObCluster":"obdemo","ObRegionId":20200102,"ObClusterId":20200102,"Type":"PRIMARY","timestamp":1572785994399662,"RsList":[{"address":"11.*.84.84:2882","role":"LEADER","sql_port":2881},{"address":"11.*.84.78:2882","role":"FOLLOWER","sql_port":2881},{"address":"11.*.84.79:2882","role":"FOLLOWER","sql_port":2881}],"ReadonlyRsList":[]}

5. OBProxy安装

5.1 准备obproxy相关账户

登录sys租户创建obproxy的内部用户。

CREATE USER proxyro IDENTIFIED BY password '*e9c2bcdc178a99b7b08dd25db58ded2ee5bff050' ;
GRANT SELECT ON *.* to proxyro;
show grants for proxyro;

5.2 安装obproxy软件

sudo rpm -ivh obproxy-*.rpm

obproxy安装目录在/opt/taobao/install 下，通常我们作一个到obproxy的软链接比较好

[admin@xxx /opt/taobao/install]
$pwd
/opt/taobao/install

[admin@xxx /opt/taobao/install]
$sudo ln -s obproxy-1.5.8 obproxy
[admin@xxx /opt/taobao/install]
$ll
total 12
drwxr-xr-x 9 admin admin 4096 Dec 18  2018 ajdk-8.0.0-b60
lrwxrwxrwx 1 admin admin   13 Jan  9 15:12 obproxy -> obproxy-1.5.8
drwxr-xr-x 4 admin admin 4096 Jan  6 14:06 obproxy-1.5.5
drwxr-xr-x 6 admin admin 4096 Jan  9 15:14 obproxy-1.5.8

5.3 启动obproxy

obproxy启动时也要指定rootservice_list,可以是IP列表。

$cd /opt/taobao/install/obproxy && bin/obproxy -r "11.*.84.78:2881;11.*.84.79:2881;11.*.84.84:2881" -p 2883 -o "enable_strict_kernel_release=false,enable_cluster_checkout=false,enable_metadb_used=false" -c obdemo

或是OCP API地址

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

4.修改obproxy参数配置

$mysql -h11.*.84.83 -uroot@sys#obdemo -P2883 -p -c -A oceanbase
Enter password:
--下面是obproxy的一些参数配置用于减少运行日志量，根据实际情况修改。
alter proxyconfig set slow_proxy_process_time_threshold='1000ms';
alter proxyconfig set xflush_log_level=ERROR;
alter proxyconfig set syslog_level=WARN;
alter proxyconfig set enable_compression_protocol=false;
show proxyconfig like '%compress%';

6. 创建oracle租户

6.1 集群资源池资源确认

确认OceanBase集群可用资源。

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
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
;

15788779535185

确认资源池使用细节

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
;

15788780676440

6.2 分配租户资源

由上面可以看出，sys租户使用了2.5-5个CPU，13-15G内存。大概还可以分配 25个CPU，35G内存。

先创建资源单元规格

CREATE resource unit my_unit_config max_cpu=20, min_cpu=15, max_memory='25G', min_memory='20G', max_iops=10000, min_iops=1000, max_session_num=1000000, max_disk_size='1024G';
// 如果要修改上面规格用下面sql
ALTER resource unit my_unit_config max_cpu=20, min_cpu=15, max_memory='25G', min_memory='20G';
分配资源池(关键)。如果资源定义不合理，这里可能分配不出来。

CREATE resource pool bmsql_pool unit = 'my_unit_config', unit_num = 1;

6.3 创建租户

这次我创建一个oracle租户(主要兼容oracle11g)。

create tenant obbmsql resource_pool_list=('bmsql_pool'), primary_zone='RANDOM',comment 'oracle tenant/instance', charset='utf8' set ob_tcp_invited_nodes='%', ob_compatibility_mode='oracle';

创建租户的时候指定了租户使用的资源池、数据分布策略(primary_zone为RANDOM)、租户字符集(默认utf8，也可以改为gbk)、租户访问白名单(ob_tcp_invited_nodes)、租户兼容级别(ob_compatibility_mode)。

此时再次检查租户资源分配细节
15788787090093
15788787512848

6.4 登录租户

租户访问账户格式有两种：用户名@租户名#集群名和集群名:租户名:用户名
对于ORACLE租户而言，默认用户名是sys，这点跟ORACLE一致。但是主要不要跟OB集群的SYS租户混淆。初学者经常犯错。

sys用户初始密码是空，登录后请修改密码。

obclient -h11.*.84.83 -usys@obbmsql#obdemo -P2883 -p
Enter password:

obclient> alter user sys identified by 123456;
Query OK, 0 rows affected (0.16 sec)

obclient> select username from dba_users;
+------------+
| USERNAME   |
+------------+
| SYS        |
| LBACSYS    |
| ORAAUDITOR |
+------------+
3 rows in set (0.10 sec)

obclient> show grants for sys;
+--------------------------------------------------------+
| Grants for SYS@%                                       |
+--------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'SYS' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON "LBACSYS".* TO 'SYS'           |
| GRANT ALL PRIVILEGES ON "ORAAUDITOR".* TO 'SYS'        |
| GRANT ALL PRIVILEGES ON "SYS".* TO 'SYS'               |
| GRANT ALL PRIVILEGES ON "oceanbase".* TO 'SYS'         |
| GRANT ALL PRIVILEGES ON "__public".* TO 'SYS'          |
| GRANT ALL PRIVILEGES ON "__recyclebin".* TO 'SYS'      |
+--------------------------------------------------------+
7 rows in set (0.01 sec)

6.5 创建测试用户

这里操作就跟ORACLE下差不多了，创建两个测试用户。一个是scott，一个是tpcc。

-- 用户管理
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;

15788797035035

6.6 初始化租户变量设置(重要)

以前在文章《从ORACLE/MySQL到OceanBase：数据库超时机制》里曾经介绍过OceanBase超时变量设置知识，初学者经常在这里碰到问题，所以这里修改一下ORACLE租户的默认设置，尽可能的跟ORACLE保持一致。

SHOW GLOBAL variables LIKE '%timeout%';
SET GLOBAL ob_query_timeout = 10000000000;
SET GLOBAL ob_trx_idle_timeout = 12000000000;
SET GLOBAL ob_trx_timeout = 10000000000;
SHOW GLOBAL variables WHERE variable_name IN ('ob_query_timeout', 'ob_trx_idle_timeout', 'ob_trx_timeout');

15788799339160

7. OceanBase图形化客户端工具(可选)

OceanBase默认提供了命令行客户端工具obclient，它吸收了mysql命令行格式的一些优点，在格式化方面做得比sqlplus要好很多。
对于很少用命令行的开发同学，建议使用dbeaver来连接OceanBase租户。请查看微信公众号(obpilot)最近文章看详细安装使用方法。

OceanBase 2.2 官网发布下载 和 集群手动搭建方法分享

引言