OceanBase 2.x體驗:手動搭建OceanBase集群

此前在《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集群的原理，對後期運維功能理解會更好一些。

有關機器前置條件檢查前文已經詳細介紹，這裡就不再贅述。直接貼關鍵幾個配置文件。

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

注意：由於公司安全要求，我對我的測試ip其中部分欄位用*號打碼了，這不是安裝需求。
3.1 啟動節點observer進程

到每個節點的admin用戶下，啟動observer進程。注意每個節點啟動參數並不完全相同。

參數裡指定數據文件的大小、內存的大小，以方便個別環境資源不足想精確控制observer對資源的佔用。我的測試機器內存比較小，默認system_memory要30G內存，這裡我限制為10G。我的環境數據和日誌目錄是一個文件系統(共用空間)，所以限制一下數據文件大小datafile_size。

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

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

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)

如果 /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.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.1 準備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 啟動obproxyobproxy啟動時也要指定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

$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"

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

確認資源池使用細節

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
;

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)。

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;

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

OceanBase默認提供了命令行客戶端工具obclient，它吸收了mysql命令行格式的一些優點，在格式化方面做得比sqlplus要好很多。
對於很少用命令行的開發同學，建議使用dbeaver來連接OceanBase租戶。請查看公眾號最近文章看詳細安裝使用方法。