mysql多副本搭建,OceanBase 2.x体验：搭建OceanBase单副本集群

前言

OceanBase跟传统关系型数据库相比最大的一个优点就是自带高可用能力和三副本数据强一致能力，支持异地容灾或多活部署，故障时能做到RPO=0、RTO在30s左右。这个能力的关键就是使用了三副本(至少三副本)和Paxos协议。

不过由于observer进程目前对资源要求有点高，三副本还要三台机器，对于大多数对分布式数据库感兴趣的个人，这个条件很难满足。实际上OceanBase也可以以单副本模式运行，不过仅限于学习的目的。

本文主要分享用一台机器搭建OceanBase单副本集群的过程。目前机器资源建议16C64G以上，2020年4月底将会推出4C8G就能运行的版本，敬请期待。

单副本的缺点是没有高可用。但是单副本集群依然可以在线做弹性伸缩(集群加机器或者租户伸缩)，可以使用OceanBase的水平拆分能力(分区表)，可以在线数据迁移、换机器等。单副本集群也支持多租户，兼容ORACLE或MySQL。1. 前置条件检查

1.1 节点配置

内核参数cat >> etc/sysctl.conf

<

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

<

* soft nofile 655360

* hard nofile 655360

* soft nproc 655360

* hard nproc 655360

* soft stack unlimited

* hard stack unlimited

EOF

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.rpmwarning: 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有关的参数。$

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.621scat 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.1-20190802211743.el7.alios7.x86_64.rpm

warning: Unable to get systemd shutdown inhibition lock: Unit is masked.

Preparing... ################################# [100%]

Updating installing...

1:obclient-1.1.1-20190802211743.el7################################# [100%]

$which obclient

/usr/bin/obclient

3. 初始化observer

集群(单节点)

3.1 启动节点observer

进程

到每个节点的admin用户下，启动observer

进程。注意启动参数中IP和磁盘大小要根据机器实际情况调整。

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.***.87.5: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.***.87.5:2882';

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. OBProxy安装

4.1 准备obproxy相关账户mysql -h127.1 -uroot@sys -P2881 -p

Enter password:

CREATE USER proxyro IDENTIFIED BY password '*e9c2bcdc178a99b7b08dd25db58ded2ee5bff050' ;

GRANT SELECT ON *.* to proxyro;

show grants for proxyro;

4.2 安装obproxy软件sudo rpm -ivh obproxy-*.rpm

warning: Unable to get systemd shutdown inhibition lock: Unit is masked.

Preparing... ################################# [100%]

Updating installing...

1:obproxy-1.5.7-1850374.el7 ################################# [100%]

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

[admin@xxx opt/taobao/install]

$ll

total 12

drwxr-xr-x 9 root root 4096 Apr 11 2019 ajdk-8.0.0-b60

lrwxrwxrwx 1 root root 13 Feb 6 11:46 obproxy -> obproxy-1.5.7

drwxr-xr-x 4 admin admin 4096 Jul 28 2019 obproxy-1.4.1

drwxr-xr-x 4 admin admin 4096 Feb 6 11:46 obproxy-1.5.7

4.3 启动obproxy

obproxy启动时也要指定rootservice_list

,可以是IP列表。$cd opt/taobao/install/obproxy && bin/obproxy -r "11.xxx.87.5:2881" -p 2883 -o "enable_strict_kernel_release=false,enable_cluster_checkout=false,enable_metadb_used=false" -c obdemo

sleep 5

ps -ef|grep obproxy

修改obproxy

参数配置$mysql -h11.xxx.87.5 -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%';

5. 创建oracle租户

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

;

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

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

)。

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

5.4 登录租户

租户访问账户格式有两种：用户名@租户名#集群名

和集群名:租户名:用户名

对于ORACLE租户而言，默认用户名是sys

，这点跟ORACLE一致。但是注意不要跟OB集群的SYS租户混淆。初学者经常犯错。

sys用户初始密码是空，登录后请修改密码。obclient -h11.166.87.5 -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)

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

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

以前在文章《》里曾经介绍过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');

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

OceanBase默认提供了命令行客户端工具obclient

，它吸收了mysql命令行格式的一些优点，在格式化方面做得比sqlplus

要好很多。

对于很少用命令行的开发同学，建议使用dbeaver

来连接OceanBase租户。详情请查看《OceanBase 2.x体验：推荐用DBeaver工具连接数据库》。

推荐阅读