如何手动部署OceanBase的单节点集群

本文介绍了手动部署OceanBase单节点集群，到创建MySQL租户的实操步骤，供大家参考。

一、环境准备

[root@10 ~]# cat /etc/redhat-release
CentOS Linux release 7.6.1810 (Core)
[root@10 ~]# free -g
              total        used        free      shared  buff/cache   available
Mem:             11           7           0           0           4           3
Swap:             4           0           4

因为docker安装经常会出现警告的提示，需要修改资源限制文件 vi /etc/security/limits.conf，添加以下内容

* soft nofile 655360
* hard nofile 655360
* soft nproc 655360
* hard nproc 655360
* soft core unlimited
* hard core unlimited
* soft stack unlimited
* hard stack unlimited

修改[/etc/sysctl.conf

net.core.somaxconn = 2048
net.core.netdev_max_backlog = 10000
net.core.rmem_default = 16777216
net.core.wmem_default = 16777216
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216
 
net.ipv4.ip_local_port_range = 3500 65535
net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
net.ipv4.tcp_syncookies = 0
net.ipv4.tcp_rmem = 4096 87380 16777216
net.ipv4.tcp_wmem = 4096 65536 16777216
net.ipv4.tcp_max_syn_backlog = 16384
net.ipv4.tcp_fin_timeout = 15
net.ipv4.tcp_max_syn_backlog = 16384
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_slow_start_after_idle=0
 
vm.swappiness = 0
vm.min_free_kbytes = 2097152
vm.max_map_count=655360
fs.aio-max-nr=1048576

授权admin用户拥有sudo权限

[root@10 ~]# chmod u+w /etc/sudoers
[root@10 ~]#
[root@10 ~]# vi /etc/sudoers

二、安装OBSERVER

需要安装OceanBase 的 OBSERVER 软件来手动部署

[admin@10 ~]$ sudo rpm -ivh /tmp/oceanbase-ce-*.rpm
 
We trust you have received the usual lecture from the local System
Administrator. It usually boils down to these three things:
 
    #1) Respect the privacy of others.
    #2) Think before you type.
    #3) With great power comes great responsibility.
 
[sudo] password for admin:
Preparing...                          ################################# [100%]
Updating / installing...
   1:oceanbase-ce-libs-3.1.4-100000920################################# [ 50%]
   2:oceanbase-ce-3.1.4-10000092022071################################# [100%]

初始化数据目录

OceanBase 节点上的相关目录都需要手动创建。

[admin@10 ~]$ sudo mkdir -p /data
[admin@10 ~]$ sudo mkdir -p /redo
[admin@10 ~]$ sudo chown -R admin:admin /data
[admin@10 ~]$ sudo chown -R admin:admin /redo
mkdir -p ~/oceanbase/store/obdemo  /data/obdemo/{sstable,etc3} /redo/obdemo/{clog,ilog,slog,etc2}
for f in {clog,ilog,slog,etc2}; do ln -s /redo/obdemo/$f ~/oceanbase/store/obdemo/$f ; done
for f in {sstable,etc3}; do ln -s /data/obdemo/$f ~/oceanbase/store/obdemo/$f; done

启动 OBSERVER 进程

安装客户端
yum install -y yum-utils
yum-config-manager --add-repo https://mirrors.aliyun.com/oceanbase/OceanBase.repo
yum install -y obclient mariadb mariadb-libs mariadb-devel
 
配置环境变量
export OB_HOME=/data/oceanbase
export PATH=$PATH:$OB_HOME/bin
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$OB_HOME/lib
 
[admin@10 oceanbase]$ cd ~/oceanbase && bin/observer -i enp0s3 -p 2881 -P 2882 -z zone1 -d ~/oceanbase/store/obdemo -r '10.0.2.4:2882:2881' -c 20210912 -n obdemo -o "memory_limit=8G,cache_wash_threshold=1G,__min_full_resource_pool_memory=268435456,system_memory=3G,memory_chunk_cache_size=128M,cpu_count=16,net_thread_count=4,datafile_size=50G,stack_size=1536K,config_additional_dir=/data/obdemo/etc3;/redo/obdemo/etc2" -d ~/oceanbase/store/obdemo
bin/observer -i enp0s3 -p 2881 -P 2882 -z zone1 -d /home/admin/oceanbase/store/obdemo -r 10.0.2.4:2882:2881 -c 20210912 -n obdemo -o memory_limit=8G,cache_wash_threshold=1G,__min_full_resource_pool_memory=268435456,system_memory=3G,memory_chunk_cache_size=128M,cpu_count=16,net_thread_count=4,datafile_size=50G,stack_size=1536K,config_additional_dir=/data/obdemo/etc3;/redo/obdemo/etc2 -d /home/admin/oceanbase/store/obdemo
devname: enp0s3
mysql port: 2881
rpc port: 2882
zone: zone1
data_dir: /home/admin/oceanbase/store/obdemo
rs list: 10.0.2.4:2882:2881
cluster id: 20210912
appname: obdemo
optstr: memory_limit=8G,cache_wash_threshold=1G,__min_full_resource_pool_memory=268435456,system_memory=3G,memory_chunk_cache_size=128M,cpu_count=16,net_thread_count=4,datafile_size=50G,stack_size=1536K,config_additional_dir=/data/obdemo/etc3;/redo/obdemo/etc2
data_dir: /home/admin/oceanbase/store/obdemo

集群自举（初始化）

[admin@10 oceanbase]$ mysql -h127.1 -uroot -P2881 -p -c -A
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 3221225472
Server version: 5.7.25 OceanBase 3.1.1 (r4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e) (Built Oct 21 2021 10:33:14)
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MySQL [(none)]>
MySQL [(none)]> set session ob_query_timeout=1000000000; alter system bootstrap ZONE 'zone1' SERVER '10.0.2.4:2882';
Query OK, 0 rows affected (0.00 sec)
 
Query OK, 0 rows affected (34.73 sec)

三、安装OBPROXY

使用sys@root登陆数据库

• 集群管理员（root@sys）密码默认集群管理员（root@sys）的密码是空的，这里需要设置一个密码。

alter user root identified by '4S9wDbSr' ;

[admin@10 oceanbase]$ mysql -h 127.1 -u root@sys -P 2881 -p -c -A
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 3221487690
Server version: 5.7.25 OceanBase 3.1.1 (r4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e) (Built Oct 21 2021 10:33:14)
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MySQL [(none)]> grant select on oceanbase.* to proxyro identified by 'SWoLCQRH' ;
Query OK, 0 rows affected (0.08 sec)

安装obproxy软件包

[admin@10 oceanbase]$ sudo rpm -ivh /tmp/obproxy-3.2.0-1.el7.x86_64.rpm
[sudo] password for admin:
Preparing...                          ################################# [100%]
Updating / installing...
   1:obproxy-3.2.0-1.el7              ################################# [100%]

启动 OBPROXY 进程

启动 OBPROXY 进程也推荐在软件安装目录，进程 pbproxy 会在该目录下生成目录 etc 保存 OBPROXY 的运行参数，以及目录 log 保存运行日志。

[admin@10 oceanbase]$ cd ~/obproxy-3.2.0/ && bin/obproxy -r "10.0.2.4:2881" -p 2883 -o "enable_strict_kernel_release=false,enable_cluster_checkout=false,enable_metadb_used=false" -c obdemo
bin/obproxy -r 10.0.2.4:2881 -p 2883 -o enable_strict_kernel_release=false,enable_cluster_checkout=false,enable_metadb_used=false -c obdemo
rs list: 10.0.2.4:2881
listen port: 2883
optstr: enable_strict_kernel_release=false,enable_cluster_checkout=false,enable_metadb_used=false
cluster_name: obdemo[admin@10 obproxy-3.2.0]$ ps -ef|grep obproxyadmin    29503     1  1 02:12 ?        00:00:00 bin/obproxy -r 10.0.2.4:2881 -p 2883 -o enable_strict_kernel_release=false,enable_cluster_checkout=false,enable_metadb_used=false -c obdemoadmin    29535 27728  0 02:13 pts/2    00:00:00 grep --color=auto obproxy

• 登录 OBPROXY 修改密码

登录用户名：root@proxysys， 端口：2883 ，初始密码：空。

[admin@10 obproxy-3.2.0]$ mysql -h 10.0.2.4 -u root@proxysys -P 2883 -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.25
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MySQL [(none)]> show proxyconfig like '%sys_password%';
+------------------------+-------+--------------------------------+-------------+---------------+
| name                   | value | info                           | need_reboot | visible_level |
+------------------------+-------+--------------------------------+-------------+---------------+
| observer_sys_password1 |       | password for observer sys user | false       | SYS           |
| observer_sys_password  |       | password for observer sys user | false       | SYS           |
| obproxy_sys_password   |       | password for obproxy sys user  | false       | SYS           |
+------------------------+-------+--------------------------------+-------------+---------------+
3 rows in set (0.00 sec)
 
MySQL [(none)]> alter proxyconfig set obproxy_sys_password = 'wPhGddup' ;
Query OK, 0 rows affected (0.01 sec)
 
MySQL [(none)]> alter proxyconfig set observer_sys_password = 'SWoLCQRH' ;
Query OK, 0 rows affected (0.00 sec)

退出，通过 OBPROXY 连接 OceanBase 集群看看, 如果能查看所有会话，则说明 OBPROXY 部署成功。

[admin@10 obproxy-3.2.0]$ mysql -h10.0.2.4 -uroot@sys#obdemo -P2883 -p4S9wDbSr -c -A oceanbase
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.6.25 OceanBase 3.1.1 (r4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e) (Built Oct 21 2021 10:33:14)
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MySQL [oceanbase]> show processlist;
+------+--------+------+----------------+-----------+-------------+-------------------+-------------------+-------+-------+
| Id   | Tenant | User | Host           | db        | trans_count | svr_session_count | state             | tid   | pid   |
+------+--------+------+----------------+-----------+-------------+-------------------+-------------------+-------+-------+
|    8 | sys    | root | 10.0.2.4:37142 | oceanbase |           0 |                 1 | MCS_ACTIVE_READER | 29503 | 29503 |
+------+--------+------+----------------+-----------+-------------+-------------------+-------------------+-------+-------+
1 row in set (0.00 sec)

四、创建租户并导入数据

登录ob数据库并创建租户

[admin@10 obproxy-3.2.0]$ mysql -h 127.1 -u root@sys -P 2881 -p4S9wDbSr -c -A
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 3221487862
Server version: 5.7.25 OceanBase 3.1.1 (r4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e) (Built Oct 21 2021 10:33:14)
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MySQL [(none)]> CREATE resource unit S4C1G max_cpu=4, min_cpu=4, max_memory='1G', min_memory='1G', max_iops=10000, min_iops=1000, max_session_num=1000000, max_disk_size='1024G';
CREATE resource pool my_pool unit = 'S4C1G', unit_num = 1;
create tenant obmysql resource_pool_list=('my_pool'), primary_zone='RANDOM',comment 'mysql tenant/instance', charset='utf8' set ob_tcp_invited_nodes='%', ob_compatibility_mode='mysql';Query OK, 0 rows affected (0.02 sec)
 
MySQL [(none)]> CREATE resource pool my_pool unit = 'S4C1G', unit_num = 1;
Query OK, 0 rows affected (0.04 sec)
 
MySQL [(none)]> create tenant obmysql resource_pool_list=('my_pool'), primary_zone='RANDOM',comment 'mysql tenant/instance', charset='utf8' set ob_tcp_invited_nodes='%', ob_compatibility_mode='mysql';
Query OK, 0 rows affected (2.22 sec)

登录OB MySQL租户

[admin@1cb6a05746e9 ~]$ obclient -h 127.1 -uroot@obmysql#obce-single -P2883 -p -c -A test
Enter password:
Welcome to the OceanBase.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.25 OceanBase 3.1.1 (r4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e) (Built Oct 21 2021 10:52:05)
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [test]> show databases;
+--------------------+
| Database           |
+--------------------+
| oceanbase          |
| information_schema |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.146 sec)

创建数据库和表等：

[admin@1cb6a05746e9 ~]$ obclient -h 127.1 -uroot@obmysql#obce-single -P2883 -p -c -A test
Enter password:
Welcome to the OceanBase.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.6.25 OceanBase 3.1.1 (r4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e) (Built Oct 21 2021 10:52:05)
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MySQL [test]> source /tmp/test_db-master/employees.sql;
 
MySQL [employees]> show databases;
+--------------------+
| Database           |
+--------------------+
| oceanbase          |
| information_schema |
| mysql              |
| test               |
| royce              |
| world              |
| employees          |
+--------------------+
7 rows in set (0.014 sec)
 
MySQL [employees]> show tables;
+----------------------+
| Tables_in_employees  |
+----------------------+
| current_dept_emp     |
| departments          |
| dept_emp             |
| dept_emp_latest_date |
| dept_manager         |
| employees            |
| salaries             |
| titles               |
+----------------------+
8 rows in set (0.008 sec)