记ob考obcp

记录ob学习,考obcp。

3台ECS 模拟 6节点集群

本次部署只为部署成功,部分配置不建议。
基本都是参考实战教程第二章:如何部署 OceanBase 社区版章节介绍的内容。

环境配置

ECS配置

内核参数修改

这部分没什么多说的照抄就好。

对四台机器都操作。

修改配置文件。

vim /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

让配置生效

sysctl -p

修改会话变量设置

这部分也不多解释了。抄。

对四台机器都操作。 将会话级别的最大栈空间大小设置为 unlimited,最大文件句柄数设置为 655350,Core 文件大小设置为 unlimited 。 修改 /etc/security/limits.conf 配置文件,如果已有设置值低于这个设置值

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

查看配置方法。退出当前会话,重新登录。执行以下命令,查看配置是否生效:

ulimit -a

关闭防火墙和 SELinux

ECS本身关闭了防火墙和SELiunx,所以这里就跳过了。
有需要可以参考教程中的配置。

配置时间同步服务

这个先不配置了,看看能不能部署成功。

配置安装用户;配置 SSH 免密登录;磁盘文件系统划分;

这三部分应该是比较重要的,但是作为部署学习所以就跳过了。


OBD 自动化部署集群

安装 OBD 软件

这里直接在线安装了。
仓库地址https://mirrors.aliyun.com/oceanbase/community/stable/el/
其实可以访问一下ob开源仓库看看里面都有啥。
你会看到里面只有7和8的包,还会看到一些老版本的包。

在机器1中执行。

直接添加 OceanBase 的仓库,安装OBD

yum install -y yum-utils
yum-config-manager --add-repo https://mirrors.aliyun.com/oceanbase/OceanBase.repo
yum install -y ob-deploy

OBD 自动化部署集群

重点来了哈

磁盘划分:学习ob所以没有单独做data和redo盘。配置了比较大的系统盘建了data和redo目录。

配置文件

直接用了教程里的配置文件稍微调整了一下


user:
   username: root #直接root用户操作了(老师建议admin用户这个应该和大部分的应用一样都是单独的用户)
   password: XXXXX #这里直接用密码了,生产强烈不建议这么玩儿的(老师说了很危险,密码泄露了)
oceanbase-ce:
  servers:
    - name: obce01
      ip: 172.20.249.53
    - name: obce02
      ip: 172.20.249.55
    - name: obce03
      ip: 172.20.249.56
    - name: obce04
      # 这里机器内存还可以所以同一台机器下起了2各observer 用的不同的端口
      ip: 172.20.249.53
    - name: obce05
      ip: 172.20.249.55
    - name: obce06
      ip: 172.20.249.56
  global:
    devname: eth0
    cluster_id: 1
    memory_limit: 10G
    system_memory: 3G
    stack_size: 512K
    cpu_count: 16
    cache_wash_threshold: 1G
    __min_full_resource_pool_memory: 268435456
    workers_per_cpu_quota: 10
    schema_history_expire_time: 1d
    net_thread_count: 4
    major_freeze_duty_time: Disable
    minor_warm_up_duration_time: 0
    freeze_trigger_percentage: 40
    enable_separate_sys_clog: 0
    enable_merge_by_turn: FALSE
    datafile_size: 50G
    syslog_level: WARN 
    enable_syslog_wf: false
    enable_syslog_recycle: true
    max_syslog_file_count: 10
    appname: obce-3zones
    # 这里密码就不设置了登陆测试比较方便(不建议这么做)
  obce01:
    mysql_port: 2881
    rpc_port: 2882
    # 这个就是ob的目录 具体有什么可以tree看下,你会发现里面的执行文件其实是个软连接,指向的是/root/.obd/repository/oceanbase-ce/3.1.1/f19f8bfb67723712175fb0dfd60579196b3168f1/bin/observer
    # 为啥这么做不清楚,后面试试手动部署对比这看下
    home_path: /root/oceanbase-ce
    # 这个应该是数据的目录
    data_dir: /data/1
    # 这个应该是事务日志的目录
    redo_dir: /redo/1
    zone: zone1
  obce02:
    mysql_port: 2881
    rpc_port: 2882
    home_path: /root/oceanbase-ce
    data_dir: /data/1
    redo_dir: /redo/1
    zone: zone2
  obce03:
    mysql_port: 2881
    rpc_port: 2882
    home_path: /root/oceanbase-ce
    data_dir: /data/1
    redo_dir: /redo/1
    zone: zone3
  obce04:
    mysql_port: 3881
    rpc_port: 3882
    home_path: /root/oceanbase-ce2
    data_dir: /data/2
    redo_dir: /redo/2
    zone: zone1
  obce05:
    mysql_port: 3881
    rpc_port: 3882
    home_path: /root/oceanbase-ce2
    data_dir: /data/2
    redo_dir: /redo/2
    zone: zone2
  obce06:
    mysql_port: 3881
    rpc_port: 3882
    home_path: /root/oceanbase-ce2
    data_dir: /data/2
    redo_dir: /redo/2
    zone: zone3
obproxy:
  servers:
    - 172.20.249.54
  depends:
    - oceanbase-ce
  global:
    listen_port: 2883
    prometheus_listen_port: 2884
    home_path: /root/obproxy
    rs_list: 172.20.249.53:2881;172.20.249.55:2881;172.20.249.56:2881
    enable_cluster_checkout: false
    # 这里密码也不设置了


OBD 部署集群

见证奇迹的时刻了。

部署使用命令:obd cluster deploy [集群名] -c 集群配置文件

[root@obd ~]# obd cluster deploy obce-3zones -c 3e6n_ob.yaml
Update OceanBase-community-stable-el7 ok
Update OceanBase-development-kit-el7 ok
Download oceanbase-ce-3.1.1-4.el7.x86_64.rpm (46.21 M): 100% [##] Time: 0:00:30   1.57 MB/s
Package oceanbase-ce-3.1.1 is available.
Download obproxy-3.2.0-1.el7.x86_64.rpm (7.80 M): 100% [##] Time: 0:00:05   1.61 MB/s
Package obproxy-3.2.0 is available.
install oceanbase-ce-3.1.1 for local ok
install obproxy-3.2.0 for local ok
+-----------------------------------------------------------------------------+
|                                   Packages                                  |
+--------------+---------+---------+------------------------------------------+
| Repository   | Version | Release | Md5                                      |
+--------------+---------+---------+------------------------------------------+
| oceanbase-ce | 3.1.1   | 4.el7   | f19f8bfb67723712175fb0dfd60579196b3168f1 |
| obproxy      | 3.2.0   | 1.el7   | 8d5c6978f988935dc3da1dbec208914668dcf3b2 |
+--------------+---------+---------+------------------------------------------+
Repository integrity check ok
Parameter check ok
Open ssh connection ok
Remote oceanbase-ce-3.1.1-f19f8bfb67723712175fb0dfd60579196b3168f1 repository install ok
Remote oceanbase-ce-3.1.1-f19f8bfb67723712175fb0dfd60579196b3168f1 repository lib check !!
[WARN] obce01(192.168.0.9) oceanbase-ce-3.1.1-f19f8bfb67723712175fb0dfd60579196b3168f1 require: libaio.so.1
。。。
[WARN] obce06(192.168.0.18) oceanbase-ce-3.1.1-f19f8bfb67723712175fb0dfd60579196b3168f1 require: libmariadb.so.3

Remote obproxy-3.2.0-8d5c6978f988935dc3da1dbec208914668dcf3b2 repository install ok
Remote obproxy-3.2.0-8d5c6978f988935dc3da1dbec208914668dcf3b2 repository lib check ok
Try to get lib-repository
Download oceanbase-ce-libs-3.1.1-4.el7.x86_64.rpm (155.15 K): 100% [##] Time: 0:00:00   1.76 MB/s
Package oceanbase-ce-libs-3.1.1 is available.
install oceanbase-ce-libs-3.1.1 for local ok
Use oceanbase-ce-libs-3.1.1-58384f7ab4ee736e9d530f4bdd63c20ced0e7aba for oceanbase-ce-3.1.1-f19f8bfb67723712175fb0dfd60579196b3168f1
Remote oceanbase-ce-libs-3.1.1-58384f7ab4ee736e9d530f4bdd63c20ced0e7aba repository install ok
Remote oceanbase-ce-3.1.1-f19f8bfb67723712175fb0dfd60579196b3168f1 repository lib check ok
Cluster status check ok
Initializes observer work home ok
Initializes obproxy work home ok
obce-3zones deployed
[root@obd ~]#
报了一箩筐警告,看着像缺少依赖,应该没太大问题。

检查集群部署状态。

[root@obd ~]# obd cluster list
+----------------------------------------------------------------+
|                          Cluster List                          |
+-------------+--------------------------------+-----------------+
| Name        | Configuration Path             | Status (Cached) |
+-------------+--------------------------------+-----------------+
| obce-3zones | /root/.obd/cluster/obce-3zones | deployed        |
+-------------+--------------------------------+-----------------+
[root@obd ~]#
成功了。


OBD 启动和初始化集群

启动一下看看。

上面 deploy 操作只是安装了软件和准备初始化目录,还需要启动集群节点并初始化集群,使用 obd cluster start 命令。

[root@obd ~]# obd cluster list
+----------------------------------------------------------------+
|                          Cluster List                          |
+-------------+--------------------------------+-----------------+
| Name        | Configuration Path             | Status (Cached) |
+-------------+--------------------------------+-----------------+
| obce-3zones | /root/.obd/cluster/obce-3zones | deployed        |
+-------------+--------------------------------+-----------------+
[root@obd ~]# ^C
[root@obd ~]# obd cluster start obce-3zones
Get local repositories and plugins ok
Open ssh connection ok
Cluster param config check ok
Check before start observer ok
[WARN] (192.168.0.9) clog and data use the same disk (/)
[WARN] (192.168.0.14) clog and data use the same disk (/)
[WARN] (192.168.0.18) clog and data use the same disk (/)

Check before start obproxy ok
Start observer ok
observer program health check ok
Connect to observer ok
Initialize cluster
Cluster bootstrap ok
Wait for observer init ok
+------------------------------------------------+
|                    observer                    |
+--------------+---------+------+-------+--------+
| ip           | version | port | zone  | status |
+--------------+---------+------+-------+--------+
| 192.168.0.14 | 3.1.1   | 2881 | zone2 | active |
| 192.168.0.14 |         | 0    | zone2 | active |
| 192.168.0.18 | 3.1.1   | 2881 | zone3 | active |
| 192.168.0.18 |         | 0    | zone3 | active |
| 192.168.0.9  | 3.1.1   | 2881 | zone1 | active |
| 192.168.0.9  | 3.1.1   | 3881 | zone1 | active |
+--------------+---------+------+-------+--------+

Start obproxy ok
obproxy program health check ok
Connect to obproxy ok
Initialize cluster
+-----------------------------------------------+
|                    obproxy                    |
+-------------+------+-----------------+--------+
| ip          | port | prometheus_port | status |
+-------------+------+-----------------+--------+
| 192.168.0.8 | 2883 | 2884            | active |
+-------------+------+-----------------+--------+
obce-3zones running
[root@obd ~]#
又有警告,这个应该是磁盘的问题没有吧数据和日志的分成功两个单独的盘。
还有同一台机器里的版本号和端口没显示明白,应该也没什么影响下往下看看。

查看启动后的集群状态。

[root@obd ~]# obd cluster list
+----------------------------------------------------------------+
|                          Cluster List                          |
+-------------+--------------------------------+-----------------+
| Name        | Configuration Path             | Status (Cached) |
+-------------+--------------------------------+-----------------+
| obce-3zones | /root/.obd/cluster/obce-3zones | running         |
+-------------+--------------------------------+-----------------+
[root@obd ~]#



连接 OceanBase 集群的内部实例(sys)

这里我直接用了obclient连接的。
前面以及添加了ob的仓库,直接yum install -y obclient 就行。
obclient可以看下社区文档有介绍。
[root@obd ~]# obclient -h192.168.0.8 -P2883 -uroot@sys
Welcome to the OceanBase.  Commands end with ; or \g.
Your MySQL connection id is 5
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 [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| oceanbase          |
| information_schema |
| mysql              |
| SYS                |
| LBACSYS            |
| ORAAUDITOR         |
| test               |
+--------------------+
7 rows in set (0.002 sec)

MySQL [(none)]> use oceanbase;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MySQL [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, b.status, usec_to_time(b.stop_time) stop_time, b.build_version
    -> 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 | stop_time                  | build_version                                                          |
+-------+-------------------+-----------+----------+--------------+-------------+----------------------------+----------------------------+--------+----------------------------+------------------------------------------------------------------------+
| zone1 | 192.168.0.9:2882  |        14 |     11.5 |            7 |           5 | 1970-01-01 08:00:00.000000 | 2021-11-26 17:02:16.250244 | active | 1970-01-01 08:00:00.000000 | 3.1.1_4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e(Oct 21 2021 10:33:14) |
| zone1 | 192.168.0.9:3882  |        14 |       14 |            7 |           7 | 1970-01-01 08:00:00.000000 | 2021-11-26 17:02:18.610342 | active | 1970-01-01 08:00:00.000000 | 3.1.1_4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e(Oct 21 2021 10:33:14) |
| zone2 | 192.168.0.14:2882 |        14 |     11.5 |            7 |           5 | 1970-01-01 08:00:00.000000 | 2021-11-26 17:02:16.389685 | active | 1970-01-01 08:00:00.000000 | 3.1.1_4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e(Oct 21 2021 10:33:14) |
| zone2 | 192.168.0.14:3882 |        14 |       14 |            7 |           7 | 1970-01-01 08:00:00.000000 | 2021-11-26 17:02:18.723446 | active | 1970-01-01 08:00:00.000000 | 3.1.1_4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e(Oct 21 2021 10:33:14) |
| zone3 | 192.168.0.18:2882 |        14 |     11.5 |            7 |           5 | 1970-01-01 08:00:00.000000 | 2021-11-26 17:02:16.519640 | active | 1970-01-01 08:00:00.000000 | 3.1.1_4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e(Oct 21 2021 10:33:14) |
| zone3 | 192.168.0.18:3882 |        14 |       14 |            7 |           7 | 1970-01-01 08:00:00.000000 | 2021-11-26 17:02:18.862892 | active | 1970-01-01 08:00:00.000000 | 3.1.1_4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e(Oct 21 2021 10:33:14) |
+-------+-------------------+-----------+----------+--------------+-------------+----------------------------+----------------------------+--------+----------------------------+------------------------------------------------------------------------+
6 rows in set (0.014 sec)

MySQL [oceanbase]>
到这里应该就算安装好了吧。
后面的话应该就是建租户使用数据库了。



1 个赞

👍🏻👍🏻👍🏻👍🏻

1台ECS 模拟 3zone集群 (手动部署)

环境配置

环境配置跳过了参照OBD自动部署

ECS配置

300G系统盘

安装 OceanBase 软件包

创建admin用户

[root@ob-1e3n ~]# adduser admin
[root@ob-1e3n ~]# passwd admin
Changing password for user admin.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.

添加sudoers文件可写权限

[root@ob-1e3n ~]# chmod -v u+w /etc/sudoers

编辑sudoers文件

[root@ob-1e3n ~]# vi /etc/sudoers
admin    ALL=(ALL)    ALL
admin    ALL=(ALL)    NOPASSWD:ALL

收回sudoers文件可写权限

[root@ob-1e3n ~]# chmod -v u-w /etc/sudoers
[root@ob-1e3n ~]# su - admin
[admin@ob-1e3n root]$ cd ~
[admin@ob-1e3n ~]$ pwd
/home/admin
[admin@ob-1e3n ~]$ ll
total 0

下载安装包

试了好多次,前几次一直用户权限有问题,包括安装、建目录都多少出现了权限问题,后面需要好好补充一下linux的用户权限相关的知识

安装包下载

这个文件地址之前有介绍就是ob仓库里的
[admin@ob-1e3n ~]$ cd /tmp/
[admin@ob-1e3n tmp]$ wget https://mirrors.aliyun.com/oceanbase/community/stable/el/7/x86_64/oceanbase-ce-libs-3.1.1-4.el7.x86_64.rpm
[admin@ob-1e3n tmp]$ wget https://mirrors.aliyun.com/oceanbase/community/stable/el/7/x86_64/oceanbase-ce-3.1.1-4.el7.x86_64.rpm

安装软件包

开始的时候用的root用户装的,新建文件夹的时候权限问题,又重新来了一遍。蛮多权限问题的,哎~~

创建一个oceanbase目录

[admin@ob-1e3n ~]$ mkdir oceanbase
[admin@ob-1e3n ~]$ sudo rpm -ivh /tmp/oceanbase-ce-*.rpm
[sudo] password for admin:
warning: /tmp/oceanbase-ce-3.1.1-4.el7.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID e9b4a7aa: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:oceanbase-ce-libs-3.1.1-4.el7    ################################# [ 50%]
   2:oceanbase-ce-3.1.1-4.el7         ################################# [100%]

因为要起三个observer

这个尝试一下复制三个不知道行不行。 (后面成功了,不知道有没有其他方式)

[admin@ob-1e3n ~]$ cp -r oceanbase oceanbase1
[admin@ob-1e3n ~]$ cp -r oceanbase oceanbase2
[admin@ob-1e3n ~]$ ls
oceanbase  oceanbase1  oceanbase2


初始化数据目录

创建dataredo目录,root用户创建并加一下权限

[root@ob-1e3n /]# mkdir data data1 data2 redo redo1 redo2
[root@ob-1e3n /]# chmod 777 data data1 data2 redo redo1 redo2

mkdir -p ~/{oceanbase,oceanbase1,oceanbase2}/store  /{data,data1,data2}/{sstable,etc3} /{redo,redo1,redo2}/{clog,ilog,slog,etc2}
for f in {clog,ilog,slog,etc2}; do ln -s /redo/$f ~/oceanbase/store/$f ; done
for f in {clog,ilog,slog,etc2}; do ln -s /redo1/$f ~/oceanbase1/store/$f ; done
for f in {clog,ilog,slog,etc2}; do ln -s /redo2/$f ~/oceanbase2/store/$f ; done
for f in {sstable,etc3}; do ln -s /data/$f ~/oceanbase/store/$f; done
for f in {sstable,etc3}; do ln -s /data1/$f ~/oceanbase1/store/$f; done
for f in {sstable,etc3}; do ln -s /data2/$f ~/oceanbase2/store/$f; done


[admin@ob-1e3n ~]$ tree
.
├── oceanbase
│   ├── bin
│   │   ├── import_time_zone_info.py
│   │   └── observer
│   ├── etc
│   │   └── timezone_V1.log
│   ├── lib
│   │   ├── libaio.so -> libaio.so.1.0.1
│   │   ├── libaio.so.1 -> libaio.so.1.0.1
│   │   ├── libaio.so.1.0.1
│   │   ├── libmariadb.so -> libmariadb.so.3
│   │   └── libmariadb.so.3
│   ├── log
│   │   ├── election.log
│   │   ├── election.log.wf
│   │   ├── observer.log
│   │   ├── observer.log.wf
│   │   ├── rootservice.log
│   │   └── rootservice.log.wf
│   ├── run
│   │   ├── mysql.sock
│   │   └── observer.pid
│   └── store
│       ├── clog -> /redo/clog
│       ├── etc2 -> /redo/etc2
│       ├── etc3 -> /data/etc3
│       ├── ilog -> /redo/ilog
│       ├── slog -> /redo/slog
│       └── sstable -> /data/sstable
├── oceanbase1
│   ...
└── oceanbase2
    ...


启动 OBSERVER 进程

这里之前应该目录问题一直失败具体也不知什么原因建立一个问答-ob目录结构问题 因为是一台机器所以只导一个环境变量就行
su - admin
echo 'export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:~/oceanbase/lib' >> ~/.bash_profile
. ~/.bash_profile
cd ~/oceanbase && bin/observer -i eth0 -p 2881 -P 2882 -z zone1 -d ~/oceanbase/store -r '192.168.0.23:2882:2881;192.168.0.23:3882:3881;192.168.0.23:4882:4881' -c 20211130 -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=512K,config_additional_dir=/data/etc3;/redo/etc2"
cd ~/oceanbase1 && bin/observer -i eth0 -p 3881 -P 3882 -z zone2 -d ~/oceanbase1/store -r '192.168.0.23:2882:2881;192.168.0.23:3882:3881;192.168.0.23:4882:4881' -c 20211130 -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=512K,config_additional_dir=/data1/etc3;/redo1/etc2"
cd ~/oceanbase2 && bin/observer -i eth0 -p 4881 -P 4882 -z zone3 -d ~/oceanbase2/store -r '192.168.0.23:2882:2881;192.168.0.23:3882:3881;192.168.0.23:4882:4881' -c 20211130 -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=512K,config_additional_dir=/data2/etc3;/redo2/etc2"

查看一下监听端口,看着一切正常

[root@ob-1e3n ~]# netstat -ntlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 0.0.0.0:3881            0.0.0.0:*               LISTEN      13094/bin/observer
tcp        0      0 0.0.0.0:3882            0.0.0.0:*               LISTEN      13094/bin/observer
tcp        0      0 0.0.0.0:4881            0.0.0.0:*               LISTEN      13666/bin/observer
tcp        0      0 0.0.0.0:4882            0.0.0.0:*               LISTEN      13666/bin/observer
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      2202/sshd
tcp        0      0 0.0.0.0:2881            0.0.0.0:*               LISTEN      12518/bin/observer
tcp        0      0 0.0.0.0:2882            0.0.0.0:*               LISTEN      12518/bin/observer


集群自举(初始化)

当 OceanBase 集群三个节点都正常启动,并且监听正常时,连接到任一节点(通过 2881 端口直连),进行自举(bootstrap 集群初始化)操作。 初始密码是空。

这里直接用obclient连接了
[root@db-test ~]# obclient -h 192.168.0.23 -P 2881 -u root -p -c -A
Enter password:
Welcome to the OceanBase.  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)]> set session ob_query_timeout=1000000000; alter system bootstrap ZONE 'zone1' SERVER '192.168.0.23:2882', ZONE 'zone2' SERVER '192.168.0.23:3882', ZONE 'zone3' SERVER '192.168.0.23:4882' ;
Query OK, 0 rows affected (0.002 sec)

Query OK, 0 rows affected (29.469 sec)

MySQL [(none)]> \q
Bye

[root@db-test ~]# obclient -h 192.168.0.23 -P 2881 -u root@sys -p -c -A
Enter password:
Welcome to the OceanBase.  Commands end with ; or \g.
Your MySQL connection id is 3221493083
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)]> show databases;
+--------------------+
| Database           |
+--------------------+
| oceanbase          |
| information_schema |
| mysql              |
| SYS                |
| LBACSYS            |
| ORAAUDITOR         |
| test               |
+--------------------+
7 rows in set (0.002 sec)

MySQL [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, b.status, usec_to_time(b.stop_time) stop_time, b.build_version
    -> 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 | stop_time                  | build_version                                                          |
+-------+-------------------+-----------+----------+--------------+-------------+----------------------------+----------------------------+--------+----------------------------+------------------------------------------------------------------------+
| zone1 | 192.168.0.23:2882 |        14 |     11.5 |            5 |           4 | 1970-01-01 08:00:00.000000 | 2021-11-30 14:01:51.144230 | active | 1970-01-01 08:00:00.000000 | 3.1.1_4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e(Oct 21 2021 10:33:14) |
| zone2 | 192.168.0.23:3882 |        14 |     11.5 |            5 |           4 | 1970-01-01 08:00:00.000000 | 2021-11-30 14:01:52.804596 | active | 1970-01-01 08:00:00.000000 | 3.1.1_4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e(Oct 21 2021 10:33:14) |
| zone3 | 192.168.0.23:4882 |        14 |     11.5 |            5 |           4 | 1970-01-01 08:00:00.000000 | 2021-11-30 14:01:52.009272 | active | 1970-01-01 08:00:00.000000 | 3.1.1_4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e(Oct 21 2021 10:33:14) |
+-------+-------------------+-----------+----------+--------------+-------------+----------------------------+----------------------------+--------+----------------------------+------------------------------------------------------------------------+
3 rows in set (0.004 sec)

MySQL [oceanbase]>


设置相关密码

集群管理员(root@sys)密码默认集群管理员(root@sys)的密码是空的,这里需要设置一个密码。

这里我没有设置密码还是为空。
alter user root identified by '' ;

OBPROXY 用户(proxyro)密码默认 OBPROXY 连接 OceanBase 集群使用用户 proxyro 。这个用户不存在,需要创建。

这个密码应该是一定要设置的,不然后面和obproxy连接不上。
grant select on oceanbase.* to proxyro identified by 'SWoLCQRH' ;

安装 OBPROXY 软件包

下载obproxy

这个文件地址之前有介绍就是ob仓库里的
[admin@ob-1e3n tmp]$ wget https://mirrors.aliyun.com/oceanbase/community/stable/el/7/x86_64/obproxy-3.2.0-1.el7.x86_64.rpm

安装obproxy

这里也提前建了目录
[admin@ob-1e3n ~]$ mkdir obproxy-3.2.0
[admin@ob-1e3n ~]$ sudo rpm -ivh /tmp/obproxy-3.2.0-1.el7.x86_64.rpm
warning: /tmp/obproxy-3.2.0-1.el7.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID e9b4a7aa: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:obproxy-3.2.0-1.el7              ################################# [100%]
[admin@ob-1e3n ~]$


启动 OBPROXY 进程

[admin@ob-1e3n ~]$ cd ~/obproxy-3.2.0/ && bin/obproxy -r "192.168.0.23:2881;192.168.0.23:3881;192.168.0.23:4881" -p 2883 -o "enable_strict_kernel_release=false,enable_cluster_checkout=false,enable_metadb_used=false" -c obdemo
bin/obproxy -r 192.168.0.23:2881;192.168.0.23:3881;192.168.0.23:4881 -p 2883 -o enable_strict_kernel_release=false,enable_cluster_checkout=false,enable_metadb_used=false -c obdemo
rs list: 192.168.0.23:2881;192.168.0.23:3881;192.168.0.23:4881
listen port: 2883
optstr: enable_strict_kernel_release=false,enable_cluster_checkout=false,enable_metadb_used=false
cluster_name: obdemo
[admin@ob-1e3n obproxy-3.2.0]$

检查 OBPROXY 监听正常

[root@ob-1e3n ~]# netstat -ntlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 0.0.0.0:3881            0.0.0.0:*               LISTEN      13094/bin/observer
tcp        0      0 0.0.0.0:3882            0.0.0.0:*               LISTEN      13094/bin/observer
tcp        0      0 0.0.0.0:4881            0.0.0.0:*               LISTEN      13666/bin/observer
tcp        0      0 0.0.0.0:4882            0.0.0.0:*               LISTEN      13666/bin/observer
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      2202/sshd
tcp        0      0 0.0.0.0:2881            0.0.0.0:*               LISTEN      12518/bin/observer
tcp        0      0 0.0.0.0:2882            0.0.0.0:*               LISTEN      12518/bin/observer
tcp        0      0 0.0.0.0:2883            0.0.0.0:*               LISTEN      15447/bin/obproxy
tcp        0      0 0.0.0.0:2884            0.0.0.0:*               LISTEN      15447/bin/obproxy


登录 OBPROXY 修改密码

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

[root@db-test ~]# obclient -h 192.168.0.23 -P 2883 -u root@proxysys -p
Enter password:
Welcome to the OceanBase.  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.000 sec)

修改 OBPROXY 用户密码是通过修改参数的方式,命令是:alter proxyconfig set 。

这里也没有修改obproxy登陆密码
alter proxyconfig set obproxy_sys_password = '' ;

同时还需要修改 OBPROXY 连接 OceanBase 集群用户 proxyro 的密码。这样 OBPROXY 才能跟 OceanBase 集群正常连接。这个密码就是前面 OceanBase 集群初始化后创建的用户 proxyro 的密码。

这个密码应该是一定要设置的,不然后面和obproxy连接不上。
alter proxyconfig set observer_sys_password = 'SWoLCQRH' ;

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

[root@db-test ~]# obclient -h 192.168.0.23 -P 2883 -u root@sys#obdemo -p
Enter password:
Welcome to the OceanBase.  Commands end with ; or \g.
Your MySQL connection id is 2
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 [(none)]> show processlist;
+------+--------+------+--------------------+------+-------------+-------------------+-------------------+-------+-------+
| Id   | Tenant | User | Host               | db   | trans_count | svr_session_count | state             | tid   | pid   |
+------+--------+------+--------------------+------+-------------+-------------------+-------------------+-------+-------+
|    2 | sys    | root | 192.168.0.89:41970 | NULL |           0 |                 1 | MCS_ACTIVE_READER | 15447 | 15447 |
+------+--------+------+--------------------+------+-------------+-------------------+-------------------+-------+-------+
1 row in set (0.000 sec)

MySQL [(none)]> show full processlist;
+------------+---------+--------+--------------------+-----------+---------+------+--------+-----------------------+--------------+------+--------------+
| Id         | User    | Tenant | Host               | db        | Command | Time | State  | Info                  | Ip           | Port | Proxy_sessid |
+------------+---------+--------+--------------------+-----------+---------+------+--------+-----------------------+--------------+------+--------------+
| 3221506372 | root    | sys    | 192.168.0.23:45052 | NULL      | Query   |    0 | ACTIVE | show full processlist | 192.168.0.23 | 2881 |            2 |
| 3222013162 | proxyro | sys    | 192.168.0.23:53506 | oceanbase | Sleep   |   17 | SLEEP  | NULL                  | 192.168.0.23 | 4881 |            3 |
+------------+---------+--------+--------------------+-----------+---------+------+--------+-----------------------+--------------+------+--------------+
2 rows in set (0.013 sec)

MySQL [(none)]> show processlist;
+------+--------+------+--------------------+------+-------------+-------------------+-------------------+-------+-------+
| Id   | Tenant | User | Host               | db   | trans_count | svr_session_count | state             | tid   | pid   |
+------+--------+------+--------------------+------+-------------+-------------------+-------------------+-------+-------+
|    2 | sys    | root | 192.168.0.89:41970 | NULL |           0 |                 1 | MCS_ACTIVE_READER | 15447 | 15447 |
+------+--------+------+--------------------+------+-------------+-------------------+-------------------+-------+-------+
1 row in set (0.000 sec)

MySQL [(none)]>


这里 show processlist; 和 show full processlist; 出现了不通的ip应该是应为我用外部的机器连的原因吧。

obproxy管理用户登陆下的sql命令只支持有限的命令。跟连接到observer支持的sql不是一回事。


这里的host就是客户端信息