OceanBase （OB）开源已经有两年，从3.x到4.x有了许多变化，做一个dber最关心的是怎么去使用OB？怎么像使用MySQL一样去使用OB，从开发大会开始就对外宣称可以像MySQL一样做到单机和主备的概念，那么通过下面的一篇笔记，让我们看一下是尊嘟假嘟？
下面我们先通用一张图了解单机分布式一体化的动态转换概念！
图1：
话不多说，上代码
今天主要介绍OB单机和主备搭建 VS MySQL 尊嘟假嘟？
1、准备两台OB单机环境
前序对于主机的准备工作就不在赘述了，
参考：https://open.oceanbase.com/blog/8000124
环境信息：
| 主集群 | 备集群 | |
|---|---|---|
| zone | 192.168.52.85 | 192.168.52.86 |
| 主租户 | thn_test | thn_test_b2 |
首先先从官方下载oceanbase-all-in-one，oceanbase-all-in-one是一个综合包，包含：OBD、OceanBase-ce、OBProxy、OBclient等，我们接下来利用OBD工具部署OB单机。
地址：https://www.oceanbase.com/softwarecenter
上传到服务器上：
[root@tdsql2 ~]# cd /opt/
[root@tdsql2 opt]# ll
total 493156
-rw-r--r-- 1 root root 504989208 Aug 9 11:25 oceanbase-all-in-one-4.2.0.0-100120230804133615.el7.x86_64.tar.gz
drwxr-xr-x. 2 root root 6 Oct 31 2018 rh
[root@tdsql2 opt]# tar zxvf oceanbase-all-in-one-4.2.0.0-100120230804133615.el7.x86_64.tar.gz -C /usr/local/
# 解压后进行安装
[root@tdsql2 opt]# /usr/local/oceanbase-all-in-one/bin/install.sh
name: grafana
version: 7.5.17
release:1
arch: x86_64
md5: 1bf1f338d3a3445d8599dc6902e7aeed4de4e0d6
add /usr/local/oceanbase-all-in-one/rpms/grafana-7.5.17-1.el7.x86_64.rpm to local mirror
name: obagent
version: 4.2.0
release:3.el7
arch: x86_64
md5: 30793df12dc6b8ec5ccdc93262e5e9f1d51ed50a
add /usr/local/oceanbase-all-in-one/rpms/obagent-4.2.0-3.el7.x86_64.rpm to local mirror
name: obproxy-ce
version: 4.2.0.0
release:7.el7
arch: x86_64
md5: b3ead2f667d69fe3195fd22002de83df6eb19382
add /usr/local/oceanbase-all-in-one/rpms/obproxy-ce-4.2.0.0-7.el7.x86_64.rpm to local mirror
name: oceanbase-ce
version: 4.2.0.0
release:100000152023080109.el7
arch: x86_64
md5: 5cc69b0ce9944adb57e36deb449bb70786d3ddc5
add /usr/local/oceanbase-all-in-one/rpms/oceanbase-ce-4.2.0.0-100000152023080109.el7.x86_64.rpm to local mirror
name: oceanbase-ce-libs
version: 4.2.0.0
release:100000152023080109.el7
arch: x86_64
md5: 6368f1d3c05f9add8c11d0c9c3b87a2fac2055b1
add /usr/local/oceanbase-all-in-one/rpms/oceanbase-ce-libs-4.2.0.0-100000152023080109.el7.x86_64.rpm to local mirror
name: ocp-express
version: 4.2.0
release:100000042023073111.el7
arch: x86_64
md5: ccec08112a29067633797d20685b6e6d70e890d9
add /usr/local/oceanbase-all-in-one/rpms/ocp-express-4.2.0-100000042023073111.el7.x86_64.rpm to local mirror
name: prometheus
version: 2.37.1
release:10000102022110211.el7
arch: x86_64
md5: 58913c7606f05feb01bc1c6410346e5fc31cf263
add /usr/local/oceanbase-all-in-one/rpms/prometheus-2.37.1-10000102022110211.el7.x86_64.rpm to local mirror
Trace ID: 1798d308-3667-11ee-ab42-00224649250e
If you want to view detailed obd logs, please run: obd display-trace 1798d308-3667-11ee-ab42-00224649250e
Disable remote ok
Trace ID: 1874f2e8-3667-11ee-9c9d-00224649250e
If you want to view detailed obd logs, please run: obd display-trace 1874f2e8-3667-11ee-9c9d-00224649250e
add auto set env logic to profile: /root/.bash_profile
#####################################################################
Install Finished
=====================================================================
Setup Environment: source ~/.oceanbase-all-in-one/bin/env.sh
Start Web Service: obd web
Quick Start: obd demo
More Details: obd -h
=====================================================================
#安装成功安装提示执行一下加载环境变量
[root@tdsql2 opt]# source ~/.oceanbase-all-in-one/bin/env.sh我们所需要的yaml文件都放在刚才解压的目录中,可以根据实际情况选择。
[root@tdsql1 opt]# ll /usr/local/oceanbase-all-in-one/obd/usr/obd/example/
total 100
-rw-r--r-- 1 root root 13329 Aug 4 13:36 all-components-min.yaml
-rw-r--r-- 1 root root 13532 Aug 4 13:36 all-components.yaml
drwxrwxrwx 2 root root 262 Aug 4 13:36 autodeploy
-rw-r--r-- 1 root root 6858 Aug 4 13:36 default-components-min.yaml
-rw-r--r-- 1 root root 7061 Aug 4 13:36 default-components.yaml
-rw-r--r-- 1 root root 3905 Aug 4 13:36 distributed-example.yaml
-rw-r--r-- 1 root root 5430 Aug 4 13:36 distributed-with-obproxy-example.yaml
drwxrwxrwx 2 root root 129 Aug 4 13:36 grafana
-rw-r--r-- 1 root root 1952 Aug 4 13:36 local-example.yaml
-rw-r--r-- 1 root root 3891 Aug 4 13:36 mini-distributed-example.yaml
-rw-r--r-- 1 root root 5401 Aug 4 13:36 mini-distributed-with-obproxy-example.yaml
-rwxr-xr-x 1 root root 2116 Aug 4 13:36 mini-local-example.yaml
-rwxr-xr-x 1 root root 2386 Aug 4 13:36 mini-single-example.yaml
-rw-r--r-- 1 root root 3862 Aug 4 13:36 mini-single-with-obproxy-example.yaml
drwxrwxrwx 2 root root 135 Aug 4 13:36 obagent
drwxrwxrwx 2 root root 84 Aug 4 13:36 obproxy
drwxrwxrwx 2 root root 4096 Aug 4 13:36 oceanbase-3.x
drwxrwxrwx 2 root root 35 Aug 4 13:36 ocp-express
drwxrwxrwx 2 root root 102 Aug 4 13:36 prometheus
-rw-r--r-- 1 root root 2222 Aug 4 13:36 single-example.yaml
-rw-r--r-- 1 root root 3733 Aug 4 13:36 single-with-obproxy-example.yaml目录下查看 OBD 提供的配置文件示例。请根据您的资源条件选择相应的配置文件。
小规格开发模式，适用于个人设备（内存不低于 8 GB）
- 本地单机部署配置样例：mini-local-example.yaml
- 单机部署配置样例：mini-single-example.yaml
- 单机部署 + ODP 配置样例：mini-single-with-obproxy-example.yaml
- 分布式部署 + ODP 配置样例：mini-distributed-with-obproxy-example.yaml
- 分布式部署 + ODP + OCP Express 配置样例：default-components-min.yaml
- 分布式部署全部组件：all-components-min.yaml
专业开发模式，适用于高配置 ECS 或物理服务器（可用资源不低于 16 核 64 GB）
- 本地单机部署配置样例：local-example.yaml
- 单机部署配置样例：single-example.yaml
- 单机部署 + ODP 配置样例：single-with-obproxy-example.yaml
- 分布式部署 + ODP 配置样例：distributed-with-obproxy-example.yaml
- 分布式部署 + ODP + OCP Express 配置样例：default-components.yaml
- 分布式部署全部组件：all-components.yaml
2、修改yaml文件根据实际情况
[root@tdsql2 opt]# cat all-components.yaml
## Only need to configure when remote login is required
user:
username: root
password: HzMc122222
# key_file: your ssh-key file path if need
# port: your ssh port, default 22
# timeout: ssh connection timeout (second), default 30
oceanbase-ce:
servers:
- name: server1
# Please don't use hostname, only IP can be supported
ip: 192.168.52.85
global:
# Please set devname as the network adaptor's name whose ip is in the setting of severs.
# if set severs as "127.0.0.1", please set devname as "lo"
# if current ip is 192.168.1.10, and the ip's network adaptor's name is "eth0", please use "eth0"
devname: enp7s0
# if current hardware's memory capacity is smaller than 50G, please use the setting of "mini-single-example.yaml" and do a small adjustment.
memory_limit: 20G # The maximum running memory for an observer
# The reserved system memory. system_memory is reserved for general tenants. The default value is 30G.
system_memory: 5G
datafile_size: 192G # Size of the data file.
log_disk_size: 192G # The size of disk space used by the clog files.
enable_syslog_wf: false # Print system logs whose levels are higher than WARNING to a separate log file. The default value is true.
enable_syslog_recycle: true # Enable auto system log recycling or not. The default value is false.
max_syslog_file_count: 4 # The maximum number of reserved log files before enabling auto recycling. The default value is 0.
# Cluster name for OceanBase Database. The default value is obcluster. When you deploy OceanBase Database and obproxy, this value must be the same as the cluster_name for obproxy.
appname: obcluster
root_password: Abcd321# # root user password
proxyro_password: Abcd321# # proxyro user pasword, consistent with obproxy's observer_sys_password, can be empty
ocp_meta_db: ocp_express # The database name of ocp express meta
ocp_meta_username: meta # The username of ocp express meta
ocp_meta_password: 'Abcd321#' # The password of ocp express meta
ocp_agent_monitor_password: 'Abcd321#' # The password for obagent monitor user
ocp_meta_tenant: # The config for ocp express meta tenant
tenant_name: ocp
max_cpu: 1
memory_size: 2G
log_disk_size: 7680M # The recommend value is (4608 + (expect node num + expect tenant num) * 512) M.
# In this example , support multiple ob process in single node, so different process use different ports.
# If deploy ob cluster in multiple nodes, the port and path setting can be same.
server1:
mysql_port: 2881 # External port for OceanBase Database. The default value is 2881. DO NOT change this value after the cluster is started.
rpc_port: 2882 # Internal port for OceanBase Database. The default value is 2882. DO NOT change this value after the cluster is started.
# The working directory for OceanBase Database. OceanBase Database is started under this directory. This is a required field.
home_path: /data/observer
# The directory for data storage. The default value is $home_path/store.
# data_dir: /data
# The directory for clog, ilog, and slog. The default value is the same as the data_dir value.
# redo_dir: /redo
zone: zone1
obproxy-ce:
# Set dependent components for the component.
# When the associated configurations are not done, OBD will automatically get the these configurations from the dependent components.
depends:
- oceanbase-ce
servers:
- 192.168.52.85
global:
listen_port: 2883 # External port. The default value is 2883.
prometheus_listen_port: 2884 # The Prometheus port. The default value is 2884.
home_path: /data/obproxy
# oceanbase root server list
# format: ip:mysql_port;ip:mysql_port. When a depends exists, OBD gets this value from the oceanbase-ce of the depends.
# rs_list: 192.168.1.2:2881;192.168.1.3:2881;192.168.1.4:2881
enable_cluster_checkout: false
# observer cluster name, consistent with oceanbase-ce's appname. When a depends exists, OBD gets this value from the oceanbase-ce of the depends.
# cluster_name: obcluster
skip_proxy_sys_private_check: true
enable_strict_kernel_release: false
# obproxy_sys_password: # obproxy sys user password, can be empty. When a depends exists, OBD gets this value from the oceanbase-ce of the depends.
# observer_sys_password: # proxyro user pasword, consistent with oceanbase-ce's proxyro_password, can be empty. When a depends exists, OBD gets thisvalue from the oceanbase-ce of the depends.
obagent:
depends:
- oceanbase-ce
servers:
- name: server1
# Please don't use hostname, only IP can be supported
ip: 192.168.52.85
global:
home_path: /data/obagent
prometheus:
servers:
- 192.168.52.85
depends:
- obagent
global:
# The working directory for prometheus. prometheus is started under this directory. This is a required field.
home_path: /data/prometheus
# address: 0.0.0.0 # The ip address to bind to. Along with port, corresponds to the `web.listen-address` parameter.
port: 9092 # The http port to use. Along with address, corresponds to the `web.listen-address` parameter.
# enable_lifecycle: true # Enable shutdown and reload via HTTP request. Corresponds to the `web.enable-lifecycle` parameter.
# data_dir: /root/prometheus/data # Base path for metrics storage. Corresponds to the `storage.tsdb.path` parameter.
# basic_auth_users: # Usernames and passwords that have full access to the web server via basic authentication. Corresponds to the `basic_auth_users` parameter.
# <username>: <password> # The format of `basic_auth_users` : the key is the user name and the value is the password.
# web_config: # Content of Prometheus web service config file. The format is consistent with the file. However, `basic_auth_users` cannot be set in it. Please set `basic_auth_users` above if needed. Corresponds to the `web.config.file` parameter.
# tls_server_config:
# # Certificate and key files for server to use to authenticate to client.
# cert_file: <filename>
# key_file: <filename>
# config: # Configuration of the Prometheus service. The format is consistent with the Prometheus config file. Corresponds to the `config.file` parameter.
# rule_files:
# - rules/*rules.yaml
# scrape_configs:
# - job_name: prometheus
# metrics_path: /metrics
# scheme: http
# static_configs:
# - targets:
# - localhost:9090
# - job_name: node
# basic_auth:
# username: admin
# password: root
# metrics_path: /metrics/node/host
# scheme: http
# file_sd_configs: # Set the targets to be collected by reading local files. The example is to collect targets corresponding to all yaml files in the 'targets' directory under $home_path.
# - files:
# - 'targets/*.yaml'
# - job_name: ob_basic
# basic_auth:
# username: admin
# password: root
# metrics_path: /metrics/ob/basic
# scheme: http
# file_sd_configs:
# - files:
# - 'targets/*.yaml'
# - job_name: ob_extra
# basic_auth:
# username: admin
# password: root
# metrics_path: /metrics/ob/extra
# scheme: http
# file_sd_configs:
# - files:
# - 'targets/*.yaml'
# - job_name: agent
# basic_auth:
# username: admin
# password: root
# metrics_path: /metrics/stat
# scheme: http
# file_sd_configs:
# - files:
# - 'targets/*.yaml'
# additional_parameters: # Additional parameters for Prometheus service, among which `web.listen-address`, `web.enable-lifecycle`, `storage.tsdb.path`, `config.file` and `web.config.file` cannot be set. Please set them in the corresponding configuration above if needed.
# - log.level: debug
grafana:
servers:
- 192.168.52.85
depends:
- prometheus
global:
home_path: /data/grafana
login_password: Abcd321# # Grafana login password.
# data_dir: # Path to where grafana can store temp files, sessions, and the sqlite3 db (if that is used).$data_dir can be empty. The default value is $home_path/data.
# logs_dir: # Directory where grafana can store logs, can be empty. The default value is $data_dir/log.
# plugins_dir: # Directory where grafana will automatically scan and look for plugins, can be empty. The default value is $data_dir/plugins.
# provisioning_dir: # folder that contains provisioning config files that grafana will apply on startup and while running, can be empty. The defaultvalue is $home_path/conf/provisioning.
# temp_data_lifetime: # How long temporary images in data directory should be kept. Supported modifiers h (hours), m (minutes), Use 0 to never cleanup temporary files, can be empty. The default value is 24h.
# log_max_days: # Expired days of log file(delete after max days), can be empty. The default value is 7.
# domian: # The public facing domain name used to access grafana from a browser, can be empty. The default value is $server.ip.
port: 3031 # The http port to use, can be empty. The default value is 3000.
# # list of datasources to insert/update depending on what's available in the database, can be empty.
# # For more parameter settings, please refer to https://grafana.com/docs/grafana/latest/administration/provisioning/#datasources
# datasources:
# name: # name of the datasource. Required and should not be 'OB-Prometheus'
# type: # datasource type. Required
# access: # access mode. direct or proxy. Required
# url: # the url of datasource
# list of dashboards providers that load dashboards into Grafana from the local filesystem, can be empty.
# For more information, please refer to https://grafana.com/docs/grafana/latest/administration/provisioning/#dashboards
# providers:
# name: # an unique provider name. Required and should not be 'OceanBase Metrics'
# type: # provider type. Default to 'file'
# options:
# path: # path to dashboard files on disk. Required when using the 'file' type
# # customize your Grafana instance by adding/modifying the custom configuration as follows
# # for more information, please refer to https://grafana.com/docs/grafana/latest/setup-grafana/configure-grafana/#configure-grafana
# # Here, setting parameters is required for format conversion.
# # For example, if the original grafana configuration format is
# #
# # [section1.section2]
# # key1 = value1
# # key2 = value2
# #
# # Then when writing the configuration below, you need to write it as
# #
# # section1:
# # section2:
# # key1: value1
# # key2: value2
# #
# # Here we only list one item, because there are more than 500 items. Please add them according to your own needs.
# customize_config:
# # original grafana configuration format is
# # [server]
# # protocol = http
# server:
# protocol: http
ocp-express:
depends:
- oceanbase-ce
- obproxy-ce
- obagent
servers:
- 192.168.52.85
global:
# The working directory for prometheus. prometheus is started under this directory. This is a required field.
home_path: /data/ocp-express
# log_dir: /home/oceanbase/ocp-express/log # The log directory of ocp express server. The default value is {home_path}/log.
memory_size: 1G # The memory size of ocp-express server. The recommend value is 512MB * (expect node num + expect tenant num) * 60MB.
# logging_file_total_size_cap: 10G # The total log file size of ocp-express server
# logging_file_max_history: 1 # The maximum of retention days the log archive log files to keep. The default value is unlimited3、进行集群的初始化
包含两个阶段：
第一阶段：deploy 集群
第二阶段：start 集群
注：如果操作失败执行obd cluster destroy cluster_name
3.1 deploy 集群
[root@tdsql2 opt]# obd cluster deploy obtest -c all-components.yaml
Package oceanbase-ce-4.2.0.0-100000152023080109.el7 is available.
Package obproxy-ce-4.2.0.0-7.el7 is available.
Package obagent-4.2.0-3.el7 is available.
Package prometheus-2.37.1-10000102022110211.el7 is available.
Package grafana-7.5.17-1 is available.
Package ocp-express-4.2.0-100000042023073111.el7 is available.
install oceanbase-ce-4.2.0.0 for local ok
install obproxy-ce-4.2.0.0 for local ok
install obagent-4.2.0 for local ok
install prometheus-2.37.1 for local ok
install grafana-7.5.17 for local ok
install ocp-express-4.2.0 for local ok
+--------------------------------------------------------------------------------------------+
| Packages |
+--------------+---------+------------------------+------------------------------------------+
| Repository | Version | Release | Md5 |
+--------------+---------+------------------------+------------------------------------------+
| oceanbase-ce | 4.2.0.0 | 100000152023080109.el7 | 5cc69b0ce9944adb57e36deb449bb70786d3ddc5 |
| obproxy-ce | 4.2.0.0 | 7.el7 | b3ead2f667d69fe3195fd22002de83df6eb19382 |
| obagent | 4.2.0 | 3.el7 | 30793df12dc6b8ec5ccdc93262e5e9f1d51ed50a |
| prometheus | 2.37.1 | 10000102022110211.el7 | 58913c7606f05feb01bc1c6410346e5fc31cf263 |
| grafana | 7.5.17 | 1 | 1bf1f338d3a3445d8599dc6902e7aeed4de4e0d6 |
| ocp-express | 4.2.0 | 100000042023073111.el7 | ccec08112a29067633797d20685b6e6d70e890d9 |
+--------------+---------+------------------------+------------------------------------------+
Repository integrity check ok
Parameter check ok
Cluster status check ok
Initializes observer work home ok
Initializes obproxy work home ok
Initializes obagent work home ok
Initializes prometheus work home ok
Initializes grafana work home ok
Initializes ocp-express work home ok
Remote oceanbase-ce-4.2.0.0-100000152023080109.el7-5cc69b0ce9944adb57e36deb449bb70786d3ddc5 repository install ok
Remote oceanbase-ce-4.2.0.0-100000152023080109.el7-5cc69b0ce9944adb57e36deb449bb70786d3ddc5 repository lib check !!
Remote obproxy-ce-4.2.0.0-7.el7-b3ead2f667d69fe3195fd22002de83df6eb19382 repository install ok
Remote obproxy-ce-4.2.0.0-7.el7-b3ead2f667d69fe3195fd22002de83df6eb19382 repository lib check ok
Remote obagent-4.2.0-3.el7-30793df12dc6b8ec5ccdc93262e5e9f1d51ed50a repository install ok
Remote obagent-4.2.0-3.el7-30793df12dc6b8ec5ccdc93262e5e9f1d51ed50a repository lib check ok
Remote prometheus-2.37.1-10000102022110211.el7-58913c7606f05feb01bc1c6410346e5fc31cf263 repository install ok
Remote prometheus-2.37.1-10000102022110211.el7-58913c7606f05feb01bc1c6410346e5fc31cf263 repository lib check ok
Remote grafana-7.5.17-1-1bf1f338d3a3445d8599dc6902e7aeed4de4e0d6 repository install ok
Remote grafana-7.5.17-1-1bf1f338d3a3445d8599dc6902e7aeed4de4e0d6 repository lib check ok
Remote ocp-express-4.2.0-100000042023073111.el7-ccec08112a29067633797d20685b6e6d70e890d9 repository install ok
Remote ocp-express-4.2.0-100000042023073111.el7-ccec08112a29067633797d20685b6e6d70e890d9 repository lib check ok
Try to get lib-repository
Package oceanbase-ce-libs-4.2.0.0-100000152023080109.el7 is available.
install oceanbase-ce-libs-4.2.0.0 for local ok
Remote oceanbase-ce-libs-4.2.0.0-100000152023080109.el7-6368f1d3c05f9add8c11d0c9c3b87a2fac2055b1 repository install ok
Remote oceanbase-ce-4.2.0.0-100000152023080109.el7-5cc69b0ce9944adb57e36deb449bb70786d3ddc5 repository lib check ok
obtest deployed
Trace ID: 51aceb74-3667-11ee-96f5-00224649250e
If you want to view detailed obd logs, please run: obd display-trace 51aceb74-3667-11ee-96f5-00224649250e3.2 start 集群
[root@tdsql2 opt]# obd cluster start obtest
Get local repositories ok
Search plugins ok
Open ssh connection ok
Load cluster param plugin ok
Check before start observer ok
[WARN] OBD-1007: (192.168.52.86) The recommended number of open files is 655350 (Current value: 400000)
[WARN] OBD-1012: (192.168.52.86) clog and data use the same disk (/data)
Check before start obproxy ok
Check before start obagent ok
Check before start prometheus ok
Check before start grafana ok
Check before start ocp-express ok
Start observer ok
observer program health check ok
Connect to observer ok
Initialize oceanbase-ce ok
Create tenant ocp ok
Start obproxy ok
obproxy program health check ok
Connect to obproxy ok
Initialize obproxy-ce ok
Start obagent ok
obagent program health check ok
Connect to Obagent ok
Start promethues ok
prometheus program health check ok
Connect to Prometheus ok
Initialize prometheus ok
Start grafana ok
grafana program health check ok
Connect to grafana ok
Initialize grafana ok
Start ocp-express ok
ocp-express program health check ok
Connect to ocp-express ok
Wait for observer init ok
+-------------------------------------------------+
| observer |
+---------------+---------+------+-------+--------+
| ip | version | port | zone | status |
+---------------+---------+------+-------+--------+
| 192.168.52.86 | 4.2.0.0 | 2881 | zone1 | ACTIVE |
+---------------+---------+------+-------+--------+
obclient -h192.168.52.86 -P2881 -uroot -p'Abcd321#' -Doceanbase -A
+-------------------------------------------------+
| obproxy |
+---------------+------+-----------------+--------+
| ip | port | prometheus_port | status |
+---------------+------+-----------------+--------+
| 192.168.52.86 | 2883 | 2884 | active |
+---------------+------+-----------------+--------+
obclient -h192.168.52.86 -P2883 -uroot -p'Abcd321#' -Doceanbase -A
+------------------------------------------------------------------+
| obagent |
+---------------+--------------------+--------------------+--------+
| ip | mgragent_http_port | monagent_http_port | status |
+---------------+--------------------+--------------------+--------+
| 192.168.52.86 | 8089 | 8088 | active |
+---------------+--------------------+--------------------+--------+
+---------------------------------------------------------+
| prometheus |
+---------------------------+-------+------------+--------+
| url | user | password | status |
+---------------------------+-------+------------+--------+
| http://192.168.52.86:9092 | admin | GvEC9GNnYu | active |
+---------------------------+-------+------------+--------+
+-------------------------------------------------------------------+
| grafana |
+---------------------------------------+-------+----------+--------+
| url | user | password | status |
+---------------------------------------+-------+----------+--------+
| http://192.168.52.86:3031/d/oceanbase | admin | Abcd321# | active |
+---------------------------------------+-------+----------+--------+
+------------------------------------------------------------------+
| ocp-express |
+---------------------------+----------+------------------+--------+
| url | username | initial password | status |
+---------------------------+----------+------------------+--------+
| http://192.168.52.86:8180 | admin | xRD1@e)1 | active |
+---------------------------+----------+------------------+--------+
obtest running
Trace ID: b86e4754-3667-11ee-9f6a-00224649250e
If you want to view detailed obd logs, please run: obd display-trace b86e4754-3667-11ee-9f6a-00224649250eprimary集群和standby集群安装方式一致，重复1-3步骤既可
4、primary集群创建租户和试验数据：
[root@tdsql1 opt]# obclient -h192.168.52.85 -P2883 -uroot -p'Abcd321#' -Doceanbase -A
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 1310740
Server version: OceanBase_CE 4.2.0.0 (r100000152023080109-8024d8ff45c45cf7c62a548752b985648a5795c3) (Built Aug 1 2023 09:54:19)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient [oceanbase]> create resource unit thn_ut1 max_cpu 10,memory_size '8G';
obclient [oceanbase]> create resource pool thn_p1 unit 'ut1',unit_num 1;
obclient [oceanbase]> create tenant thn_test resource_pool_list=('thn_p1') set ob_tcp_invited_nodes='%';
# 使用创建的租户登录测试
[root@tdsql1 ~]# obclient -h192.168.52.85 -P2883 -uroot@thn_test -p -Dtest -A
Enter password:
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 1048606
Server version: OceanBase_CE 4.2.0.0 (r100000152023080109-8024d8ff45c45cf7c62a548752b985648a5795c3) (Built Aug 1 2023 09:54:19)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient [test]> create table t1(id int);
Query OK, 0 rows affected (0.334 sec)
obclient [test]> insert into t1 values(1);
Query OK, 1 row affected (0.115 sec)
obclient [test]> insert into t1 values(2);
Query OK, 1 row affected (0.002 sec)
obclient [test]> insert into t1 values(3);
Query OK, 1 row affected (0.001 sec)
obclient [test]> insert into t1 values(4);
Query OK, 1 row affected (0.002 sec)
obclient [test]> insert into t1 values(5);
Query OK, 1 row affected (0.002 sec)
obclient [test]> insert into t1 values(6);
Query OK, 1 row affected (0.003 sec)
obclient [test]> insert into t1 values(7);
Query OK, 1 row affected (0.001 sec)
obclient [test]> insert into t1 values(8);
Query OK, 1 row affected (0.002 sec)primary集群一切正常，standby集群我们只需要安装好既可，不需要创建租户
5、构建主备库
5.1 安装nfs，用于后续的数据归档和备份
NFS服务端部署
1. 安装NFS
yum install nfs-utils2. 设置共享目录
mkdir -p /data/ob_backupecho "/data/ob_backup *(rw,sync,all_squash)" > /etc/exports3. 修改文件权限
chown nfsnobody:nfsnobody -R /data/ob_backup/4. 配置NFS参数
echo "RPCNFSDCOUNT=8RPCNFSDARGS="-N 2 -N 3 -U"NFSD_V4_GRACE=90NFSD_V4_LEASE=90" > /etc/sysconfig/nfs5. 重启NFS服务器
systemctl restart nfs-server6. 设置Slot Table
echo "sunrpc.tcp_max_slot_table_entries=128" >> /etc/sysctl.confsysctl -w sunrpc.tcp_max_slot_table_entries=128NFS客户端部署
需要再所有OBServer服务器上执行
- 安装NFS
yum install nfs-utils- 设置Slot Table
echo "sunrpc.tcp_max_slot_table_entries=128" >> /etc/sysctl.confsysctl -w sunrpc.tcp_max_slot_table_entries=128- 挂载目录
mkdir -p /data/backupmount -tnfs4 -o rw,nfsvers=4.1,sync,lookupcache=positive,hard,timeo=600,wsize=1048576,rsize=1048576,namlen=255 192.168.52.87:/data/ob_backup/ /data/backup官网参考：https://www.oceanbase.com/docs/community-observer-cn-10000000000901603
5、构建主备库
简单描述一下主备原理：
物理备库通过日志传输服务在主租户和备租户之间实时同步 Redo 日志。特别的，主租户不会主动给备租户推送日志，仅依赖备租户从主租户拉取日志。
日志传输服务会自动寻址日志位置信息，并处理日志落后及主租户所在集群节点故障等高可用问题。备租户既可以通过主租户的日志归档来获取日志，也可以通过网络直连主租户所在的集群来获取日志。
日志传输服务提供了两种不同的使用模式，这两种使用模式决定了物理备库的两种不同的部署方案：基于日志归档的物理备库和基于网络的物理备库。基于日志归档的物理备库:
基于日志归档的物理备库中，物理备库的 Redo 日志来源于主租户或其他备租户的日志归档，类似于 Oracle 数据库的 Far Sync，备租户仅与日志归档交互，而不会和上游的主租户或备租户有任何其他形式的交互。在该部署模式下，备租户与上游租户不需要网络联通，但其同步性能和可用性会受到日志归档介质的影响。基于日志归档的物理备库模式的部署架构图如下所示。图中，Log Archive（日志归档）、Log Archive Dest（日志归档目的端） 以及 Log Restore（日志恢复） 共同构成了该部署模式下的日志传输服务。
基于网络的物理备库：
基于网络的物理备库中，备租户直接通过网络连接主租户或其他备租户读取日志，类似于 MySQL 数据库的 Replication。在该部署模式下，备租户和主租户的网络需要联通。备租户会通过网络发送 RPC 请求读取主租户集群中的 Redo 日志，同时为了支持在主租户节点故障、日志回收等场景下备租户的高可用，备租户也需要少量主租户系统视图的查询权限。在该部署模式下，备租户从主租户读取的日志，既可以是主租户的在线日志，也可以是主租户的归档日志（主租户开启了日志归档模式的前提下），两种日志来源支持自动切换，对备租户以及业务的使用者透明。基于网络的物理备库模式的部署架构图如下所示。图中，Primary Tenant1 未开启日志归档，Standby Tenant1 仅通过网络同步 Primary Tenant1 的在线日志；Primary Tenant2 开启了日志归档，Standby Tenant2 既可以同步 Primary Tenant2 的在线日志，也可以在主租户的在线日志回收后自动切换为同步 Primary Tenant2 的归档日志。
两种部署模式对比：
基于日志归档的物理备库与基于网络的物理备库在功能的使用上会有一些差异，具体差异如下表所示。
| 功能项 | 基于日志归档的物理备库 | 基于网络的物理备库 |
|---|---|---|
| 是否支持 Switchover | 支持 | 支持 |
| 是否支持 Failover | 支持 | 支持 |
| 是否支持一个主库对接多个备库 | 支持 | 支持 |
| 是否支持级联备库 | 支持 | 支持 |
| 是否为异步同步 | 是 | 是 |
| 是否支持最大可用或最大保护模式 | 不支持 | 不支持 |
| 是否支持备库限速 | 不支持 | 支持，集群级限速 |
| 备库的数据源 | 归档日志 | 主库的在线日志或归档日志，支持自动切换 |
| 是否要求主库开启归档模式 | 要求 | 不要求 |
| 是否要求备库开启归档模式 | 要求，否则无法执行 Switchover | 不要求 |
| 实时性 | 秒级 ~ 分钟级 | 秒级 |
| 日志归档支持的存储介质 | OSS/NFS | 不涉及 |
由上可见：基于网络的物理备库更好一些，下面我们针对这两种模式进行测试
5.1 主库设置备份和归档目录
[root@tdsql1 opt]# obclient -h192.168.52.85 -P2883 -uroot@thn_test -p -Doceanbase -A
Enter password:
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 33
Server version: OceanBase_CE 4.2.0.0 (r100000152023080109-8024d8ff45c45cf7c62a548752b985648a5795c3) (Built Aug 1 2023 09:54:19)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient [oceanbase]> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=file:///data/backup/arch_data';
Query OK, 0 rows affected (0.080 sec)
obclient [oceanbase]> ALTER SYSTEM SET data_backup_dest='file:///data/backup/back_data';
Query OK, 0 rows affected (0.116 sec)5.2 进行归档和备份
归档：
obclient [oceanbase]> ALTER SYSTEM ARCHIVELOG;
Query OK, 0 rows affected (0.005 sec)
obclient [oceanbase]>
obclient [oceanbase]>
obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_ARCHIVELOG;
+---------+----------+-------------+---------+--------+---------------------+----------------------------+---------------------+----------------------------+------------+---------------+---------------+-----------------------+-----------+-------------+-------------+---------------------+--------------+----------------------+-------------------+---------------------+-----------------------------+----------------------+------------------------------+---------+-------------------------------+
| DEST_ID | ROUND_ID | INCARNATION | DEST_NO | STATUS | START_SCN | START_SCN_DISPLAY | CHECKPOINT_SCN | CHECKPOINT_SCN_DISPLAY | COMPATIBLE | BASE_PIECE_ID | USED_PIECE_ID | PIECE_SWITCH_INTERVAL | UNIT_SIZE | COMPRESSION | INPUT_BYTES | INPUT_BYTES_DISPLAY | OUTPUT_BYTES | OUTPUT_BYTES_DISPLAY | COMPRESSION_RATIO | DELETED_INPUT_BYTES | DELETED_INPUT_BYTES_DISPLAY | DELETED_OUTPUT_BYTES | DELETED_OUTPUT_BYTES_DISPLAY | COMMENT | PATH |
+---------+----------+-------------+---------+--------+---------------------+----------------------------+---------------------+----------------------------+------------+---------------+---------------+-----------------------+-----------+-------------+-------------+---------------------+--------------+----------------------+-------------------+---------------------+-----------------------------+----------------------+------------------------------+---------+-------------------------------+
| 1002 | 1 | 1 | 0 | DOING | 1691563160222959687 | 2023-08-09 14:39:20.222959 | 1691563160601525616 | 2023-08-09 14:39:20.601525 | 1 | 1 | 1 | 86400000000 | 1 | none | 9969368 | 9.51MB | 9969368 | 9.51MB | 1.00 | 0 | 0.00MB | 0 | 0.00MB | | file:///data/backup/arch_data |
+---------+----------+-------------+---------+--------+---------------------+----------------------------+---------------------+----------------------------+------------+---------------+---------------+-----------------------+-----------+-------------+-------------+---------------------+--------------+----------------------+-------------------+---------------------+-----------------------------+----------------------+------------------------------+---------+-------------------------------+
1 row in set (0.024 sec)
状态为DOING进行数据库备份
备份：
obclient [oceanbase]> ALTER SYSTEM BACKUP DATABASE;
Query OK, 0 rows affected (0.122 sec)
obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_BACKUP_TASK_HISTORY;
+---------+--------+-------------+---------------+----------------------------+----------------------------+-----------+---------------------+---------------------+---------------------+-----------------+--------+-------------+--------------+-------------------+------------------+--------------+---------------------+-------------------+--------------------------+------------+--------------+--------------+--------+---------+-------------------------------+---------------+---------------+
| TASK_ID | JOB_ID | INCARNATION | BACKUP_SET_ID | START_TIMESTAMP | END_TIMESTAMP | STATUS | START_SCN | END_SCN | USER_LS_START_SCN | ENCRYPTION_MODE | PASSWD | INPUT_BYTES | OUTPUT_BYTES | OUTPUT_RATE_BYTES | EXTRA_META_BYTES | TABLET_COUNT | FINISH_TABLET_COUNT | MACRO_BLOCK_COUNT | FINISH_MACRO_BLOCK_COUNT | FILE_COUNT | META_TURN_ID | DATA_TURN_ID | RESULT | COMMENT | PATH | MINOR_TURN_ID | MAJOR_TURN_ID |
+---------+--------+-------------+---------------+----------------------------+----------------------------+-----------+---------------------+---------------------+---------------------+-----------------+--------+-------------+--------------+-------------------+------------------+--------------+---------------------+-------------------+--------------------------+------------+--------------+--------------+--------+---------+-------------------------------+---------------+---------------+
| 1 | 1 | 1 | 1 | 2023-08-09 14:39:57.627237 | 2023-08-09 14:41:54.818341 | COMPLETED | 1691563211165416311 | 1691563314749171580 | 1691563223891388751 | NONE | | 93144716 | 18430085 | 157265.2221 | 0 | 581 | 581 | 44 | 44 | 0 | 1 | 0 | 0 | | file:///data/backup/back_data | 1 | 1 |
+---------+--------+-------------+---------------+----------------------------+----------------------------+-----------+---------------------+---------------------+---------------------+-----------------+--------+-------------+--------------+-------------------+------------------+--------------+---------------------+-------------------+--------------------------+------------+--------------+--------------+--------+---------+-------------------------------+---------------+---------------+
1 row in set (0.002 sec)备份成功后DBA_OB_BACKUP_TASK_HISTORY，才可以查到数据。
5.3 standby集群恢复数据
[root@tdsql2 opt]# obclient -h192.168.52.86 -P2883 -uroot -p'Abcd321#' -Doceanbase -A
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 1704009
Server version: OceanBase_CE 4.2.0.0 (r100000152023080109-8024d8ff45c45cf7c62a548752b985648a5795c3) (Built Aug 1 2023 09:54:19)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
# 先创建租户和资源池
obclient [oceanbase]> create resource unit thn_ut1 max_cpu 10,memory_size '8G';
obclient [oceanbase]> create resource pool thn_b_p1 unit 'thn_ut1',unit_num 1;
obclient [oceanbase]> ALTER SYSTEM RESTORE thn_test_b2 FROM 'file:///data/backup/back_data,file:///data/backup/arch_data' WITH 'pool_list=thn_b_p1';
#查询恢复进度
obclient [oceanbase]> SELECT * FROM CDB_OB_RESTORE_PROGRESS;
+-----------+--------+---------------------+-------------------+--------------------+------------------+---------------------+-------------------------------------------------------------+--------------------+---------------------+----------------------------+----------------------------+----------------------------+-------------------------------------------------+-----------------------------------------------+-------------+---------------------+--------------+----------------------+-------------+
| TENANT_ID | JOB_ID | RESTORE_TENANT_NAME | RESTORE_TENANT_ID | BACKUP_TENANT_NAME | BACKUP_TENANT_ID | BACKUP_CLUSTER_NAME | BACKUP_DEST | RESTORE_OPTION| RESTORE_SCN | RESTORE_SCN_DISPLAY | STATUS | START_TIMESTAMP | BACKUP_SET_LIST | BACKUP_PIECE_LIST | TOTAL_BYTES | TOTAL_BYTES_DISPLAY | FINISH_BYTES | FINISH_BYTES_DISPLAY | DESCRIPTION |
+-----------+--------+---------------------+-------------------+--------------------+------------------+---------------------+-------------------------------------------------------------+--------------------+---------------------+----------------------------+----------------------------+----------------------------+-------------------------------------------------+-----------------------------------------------+-------------+---------------------+--------------+----------------------+-------------+
| 1 | 5 | thn_test_b2 | 1012 | thn_test | 1002 | obcluster | file:///data/backup/back_data,file:///data/backup/arch_data | pool_list=thn_b_p1| 1691570843861797616 | 2023-08-09 16:47:23.861797 | WAIT_TENANT_RESTORE_FINISH | 2023-08-09 16:48:11.321623 | file:///data/backup/back_data/backup_set_1_full | file:///data/backup/arch_data/piece_d1002r1p1 | NULL | NULL | NULL | NULL | |
| 1012 | 5 | thn_test_b2 | 1012 | thn_test | 1002 | obcluster | file:///data/backup/back_data,file:///data/backup/arch_data | pool_list=thn_b_p1| 1691570843861797616 | 2023-08-09 16:47:23.861797 | RESTORING | 2023-08-09 16:48:11.321623 | file:///data/backup/back_data/backup_set_1_full | file:///data/backup/arch_data/piece_d1002r1p1 | 18430085 | 17.58MB | 0 | 0.00MB | |
+-----------+--------+---------------------+-------------------+--------------------+------------------+---------------------+-------------------------------------------------------------+--------------------+---------------------+----------------------------+----------------------------+----------------------------+-------------------------------------------------+-----------------------------------------------+-------------+---------------------+--------------+----------------------+-------------+
2 rows in set (0.005 sec)
obclient [oceanbase]> SELECT * FROM CDB_OB_RESTORE_HISTORY where RESTORE_TENANT_NAME = 'thn_test_b2';
+-----------+--------+---------------------+-------------------+--------------------+------------------+---------------------+-------------------------------------------------------------+---------------------+----------------------------+--------------------+----------------------------+----------------------------+---------+---------------------------------------------------------------------------------------------+-------------------------------------------------+------------------------+----------+-----------------+--------------+---------------------+-------------+---------------------+--------------+----------------------+-------------+---------+
| TENANT_ID | JOB_ID | RESTORE_TENANT_NAME | RESTORE_TENANT_ID | BACKUP_TENANT_NAME | BACKUP_TENANT_ID | BACKUP_CLUSTER_NAME | BACKUP_DEST | RESTORE_SCN | RESTORE_SCN_DISPLAY | RESTORE_OPTION | START_TIMESTAMP | FINISH_TIMESTAMP | STATUS | BACKUP_PIECE_LIST | BACKUP_SET_LIST | BACKUP_CLUSTER_VERSION | LS_COUNT | FINISH_LS_COUNT | TABLET_COUNT | FINISH_TABLET_COUNT | TOTAL_BYTES | TOTAL_BYTES_DISPLAY | FINISH_BYTES | FINISH_BYTES_DISPLAY | DESCRIPTION | COMMENT |
+-----------+--------+---------------------+-------------------+--------------------+------------------+---------------------+-------------------------------------------------------------+---------------------+----------------------------+--------------------+----------------------------+----------------------------+---------+---------------------------------------------------------------------------------------------+-------------------------------------------------+------------------------+----------+-----------------+--------------+---------------------+-------------+---------------------+--------------+----------------------+-------------+---------+
| 1 | 1 | thn_test_b2 | 1002 | thn_test | 1002 | obcluster | file:///data/backup/back_data,file:///data/backup/arch_data | 1691658106734643432 | 2023-08-10 17:01:46.734643 | pool_list=thn_b_p1 | 2023-08-10 17:01:55.168002 | 2023-08-10 17:09:15.020304 | SUCCESS | file:///data/backup/arch_data/piece_d1002r1p1,file:///data/backup/arch_data/piece_d1002r1p2 | file:///data/backup/back_data/backup_set_3_full | 17180000256 | 2 | 0 | 589 | 0 | 16726776 | 15.95MB | 0 | 0.00MB | NULL | |
| 1002 | 1 | thn_test_b2 | 1002 | thn_test | 1002 | obcluster | file:///data/backup/back_data,file:///data/backup/arch_data | 1691658106734643432 | 2023-08-10 17:01:46.734643 | pool_list=thn_b_p1 | 2023-08-10 17:01:55.168002 | 2023-08-10 17:09:04.371606 | SUCCESS | file:///data/backup/arch_data/piece_d1002r1p1,file:///data/backup/arch_data/piece_d1002r1p2 | file:///data/backup/back_data/backup_set_3_full | 17180000256 | 2 | 0 | 589 | 0 | 16726776 | 15.95MB | 0 | 0.00MB | NULL | |
+-----------+--------+---------------------+-------------------+--------------------+------------------+---------------------+-------------------------------------------------------------+---------------------+----------------------------+--------------------+----------------------------+----------------------------+---------+---------------------------------------------------------------------------------------------+-------------------------------------------------+------------------------+----------+-----------------+--------------+---------------------+-------------+---------------------+--------------+----------------------+-------------+---------+
当CDB_OB_RESTORE_PROGRESS表查询为空时，CDB_OB_RESTORE_HISTORY可以查到历史任务时，即已经恢复成功。
此时我们的准备工作都已经做好了，无论基于网络和日志归档均可使用备份方式去构建物理备库，同时还有第二种方式构建，我们在文章最后揭晓！！
6、基于归档日志构建物理备库
登录root用户执行recover，设置UNLIMITED表示一直进行同步归档日志
--standby集群操作
[root@tdsql2 ~]# obclient -h192.168.52.86 -P2881 -uroot -p'Abcd321#' -Doceanbase -A
Enter password:
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 3221507037
Server version: OceanBase_CE 4.2.0.0 (r100000152023080109-8024d8ff45c45cf7c62a548752b985648a5795c3) (Built Aug 1 2023 09:54:19)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient [oceanbase]> ALTER SYSTEM RECOVER STANDBY tenant = thn_test_b2 UNTIL UNLIMITED;
obclient [oceanbase]> SELECT TENANT_NAME, TENANT_ROLE, SCN_TO_TIMESTAMP(SYNC_SCN) FROM oceanbase.DBA_OB_TENANTS WHERE TENANT_NAME = 'thn_test_b2';
+-------------+-------------+----------------------------+
| TENANT_NAME | TENANT_ROLE | SCN_TO_TIMESTAMP(SYNC_SCN) |
+-------------+-------------+----------------------------+
| thn_test_b2 | STANDBY | 2023-08-09 16:58:10.771862 |
+-------------+-------------+----------------------------+
1 row in set (5.010 sec)
--primary集群查看：
[root@tdsql1 ~]# obclient -h192.168.52.85 -P2883 -uroot@thn_test -p -Doceanbase -A
Enter password:
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 42
Server version: OceanBase_CE 4.2.0.0 (r100000152023080109-8024d8ff45c45cf7c62a548752b985648a5795c3) (Built Aug 1 2023 09:54:19)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient [oceanbase]> CREATE USER rep_user IDENTIFIED BY 'Abcd321#';
Query OK, 0 rows affected (0.117 sec)
obclient [oceanbase]> SELECT TENANT_NAME, TENANT_ROLE, SCN_TO_TIMESTAMP(SYNC_SCN) FROM oceanbase.DBA_OB_TENANTS WHERE TENANT_NAME = 'thn_test_b2';SELECT TENANT_NAME, TENANT_ROLE, SCN_TO_TIMESTAMP(SYNC_SCN) FROM oceanbase.DBA_OB_TENANTS;
Empty set (0.002 sec)
+-------------+-------------+----------------------------+
| TENANT_NAME | TENANT_ROLE | SCN_TO_TIMESTAMP(SYNC_SCN) |
+-------------+-------------+----------------------------+
| thn_test | PRIMARY | 2023-08-09 16:58:52.321445 |
+-------------+-------------+----------------------------+
1 row in set (0.013 sec)插入一些数据测试，如下图：
经过测试，基于日志方式构建的物理备库实时性在秒级~分钟级，可以做一个异步的容灾进行数据的冗余，并不保证一定会在故障恢复时使用。
7、基于网络的构建物理备库
基于网络方式和MySQL主从方式比较相似，也是通过IP和端口去实现连接
--创建连接使用的用户并且授权，登录thn_test租户
[root@tdsql1 ~]# obclient -h192.168.52.85 -P2883 -uroot@thn_test -p -Doceanbase -A
Enter password:
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 48
Server version: OceanBase_CE 4.2.0.0 (r100000152023080109-8024d8ff45c45cf7c62a548752b985648a5795c3) (Built Aug 1 2023 09:54:19)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient [oceanbase]> CREATE USER rep_user IDENTIFIED BY 'Abcd321#';
obclient [oceanbase]> GRANT SELECT ON oceanbase.* TO rep_user;
--查询需要用的IP、端口信息
obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_ACCESS_POINT;
+-----------+-------------+---------------+----------+
| TENANT_ID | TENANT_NAME | SVR_IP | SQL_PORT |
+-----------+-------------+---------------+----------+
| 1002 | thn_test | 192.168.52.85 | 2881 |
+-----------+-------------+---------------+----------+
1 row in set (0.022 sec)授权可以选择 oceanbase 数据库下所有表的 SELECT 权限授予该用户。也可以仅授予 oceanbase 库下 GV$OB_LOG_STAT、GV$OB_UNITS、GV$OB_PARAMETERS、DBA_OB_ACCESS_POINT、DBA_OB_TENANTS、DBA_OB_LS 等视图的 SELECT 权限。
7.1 设置恢复源 （也可以称为构建主备）
--登录刚才通过备份恢复时创建的thn_test_b2租户
[root@tdsql2 ~]# obclient -h192.168.52.86 -P2881 -uroot@thn_test_b2 -p -Dtest -A
Enter password:
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 3221507037
Server version: OceanBase_CE 4.2.0.0 (r100000152023080109-8024d8ff45c45cf7c62a548752b985648a5795c3) (Built Aug 1 2023 09:54:19)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient [test]> ALTER SYSTEM SET LOG_RESTORE_SOURCE ="SERVICE=192.168.52.85:2881 USER=rep_user@thn_test PASSWORD=Abcd321#";
Query OK, 0 rows affected (0.018 sec)
obclient [test]> ALTER SYSTEM RECOVER STANDBY UNTIL UNLIMITED;
Query OK, 0 rows affected (0.004 sec)
--查询数据源信息
obclient [test]> SELECT * FROM oceanbase.DBA_OB_LOG_RESTORE_SOURCE;
+-----------+----+---------+---------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
| TENANT_ID | ID | TYPE | VALUE | RECOVERY_UNTIL_SCN |
+-----------+----+---------+---------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
| 1012 | 1 | SERVICE | IP_LIST=192.168.52.85:2881,USER=rep_user@thn_test,PASSWORD=Abcd321#,TENANT_ID=1002,CLUSTER_ID=1,COMPATIBILITY_MODE=MYSQL,IS_ENCRYPTED=false | 4611686018427387903 |
+-----------+----+---------+---------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
1 row in set (0.019 sec)
obclient [test]> SELECT TENANT_NAME, TENANT_ROLE, SCN_TO_TIMESTAMP(SYNC_SCN) FROM oceanbase.DBA_OB_TENANTS;
+-------------+-------------+----------------------------+
| TENANT_NAME | TENANT_ROLE | SCN_TO_TIMESTAMP(SYNC_SCN) |
+-------------+-------------+----------------------------+
| thn_test_b2 | STANDBY | 2023-08-09 16:58:45.812557 |
+-------------+-------------+----------------------------+
1 row in set (0.003 sec)测试步骤和6是一样的，经过测试基于网络方式构建的物理备库实时性在秒级（本地测试为1-5s），和MySQL的异步复制类似，就目前来看网络方式更让人可以接受一些。
8、利用备用租户方式构建
备租户既可以从主租户同步日志，也可以从另外一个备租户同步日志，从另一个备租户同步日志的部署架构，称为级联备库或级联备租户。
8.1 选择创建备租户的方式
OceanBase 数据库提供了三种方式创建备租户，可以根据实际的业务场景，选择合适的方式创建备租户。
- 创建空备租户
- 若主租户为刚刚新创建的主租户，或者用户可以确认主租户所在集群或日志归档介质上保存有该主租户自创建后完整的日志，则其备租户在创建时不需要依赖除日志外的其他基线或转储数据。此时，可以通过 CREATE STANDBY TENANT 语句创建备租户。
- 检查主租户是否拥有完整日志的方法如下：
- 管理员用户登录主租户或主租户所在集群的 sys 租户。
- 执行以下命令，查看主租户上的日志流对比信息。
- 主租户所在集群的 sys 租户查询主租户的日志流对比信息
(SELECT LS_ID FROM oceanbase.CDB_OB_LS_HISTORY WHERE TENANT_ID = xxxx) EXCEPT (SELECT LS_ID FROM oceanbase.CDB_OB_LS WHERE TENANT_ID = xxxx);
--或
(SELECT LS_ID FROM oceanbase.CDB_OB_LS_HISTORY WHERE TENANT_ID = xxxx) MINUS (SELECT LS_ID FROM oceanbase.CDB_OB_LS WHERE TENANT_ID = xxxx); 其中，主租户的 TENANT_ID 可通过 DBA_OB_TENANTS 视图获取。
2. 主租户查询本租户
(SELECT LS_ID FROM oceanbase.DBA_OB_LS_HISTORY) EXCEPT (SELECT LS_ID FROM oceanbase.DBA_OB_LS);
--或
(SELECT LS_ID FROM oceanbase.DBA_OB_LS_HISTORY) MINUS (SELECT LS_ID FROM oceanbase.DBA_OB_LS);其中，DBA_OB_LS_HISTORY 或 CDB_OB_LS_HISTORY 视图用于展示租户上曾经创建过的所有日志流；DBA_OB_LS 或 CDB_OB_LS 视图用于展示租户上当前提供服务的日志流。两个 SELECT 查询语句的差值用于表示主租户上曾经创建过，但后续由于负载均衡或扩缩容等原因被删掉的日志流。
若上述语句的返回结果不为空，则表明主租户上曾经有日志流创建后被删除，此时主租户上的日志不完整，不能使用 CREATE STANDBY TENANT 语句创建备租户。若查询结果为空，则继续执行下一步的检查。
3. 执行以下命令，查看主租户上的日志流信息。
3.1 主租户所在集群的 sys 租户查询主租户的日志流信息
SELECT LS_ID, BEGIN_LSN FROM oceanbase.GV$OB_LOG_STAT WHERE TENANT_ID = xxxx AND ROLE = 'LEADER' ; 其中，主租户的 TENANT_ID 可通过 DBA_OB_TENANTS 视图获取。
3.2 主租户查询本租户
SELECT LS_ID, BEGIN_LSN FROM oceanbase.GV$OB_LOG_STAT WHERE ROLE = 'LEADER' ;查询结果的示例如下：
obclient [oceanbase]> SELECT LS_ID, BEGIN_LSN FROM oceanbase.GV$OB_LOG_STAT WHERE ROLE = 'LEADER' ;
+-------+-----------+
| LS_ID | BEGIN_LSN |
+-------+-----------+
| 1 | 0 |
| 1001 | 0 |
+-------+-----------+
2 rows in set (0.008 sec) 其中，BEGIN_LSN 表示当前日志流副本保存的最早的日志 LSN（Log Sequence Number），如果 BEGIN_LSN 的值为 0，则表示当前日志流副本拥有自创建以来完整的日志。
根据查询结果，日志流副本所对应的 BEGIN_LSN 的值为 0，表示当前日志流副本拥有自创建以来完整的日志；如果该租户的所有日志流副本对应的 BEGIN_LSN 的值均为 0，则表示该租户的所有日志流均拥有完整日志，可以通过 CREATE STANDBY TENANT 语句创建空备租户。
官方链接：https://www.oceanbase.com/docs/common-oceanbase-database-1000000000033892
8.2 创建备用租户并构建主备
创建访问视图的专用用户：
由于备租户在连接主租户时，需要访问主租户的部分系统视图，因此需要有一个访问视图专用用户具备这部分系统视图的查询权限。您可以提供一个主租户上已有相关权限的用户，也可以在主租户上为备租户专门创建一个新的具备相关权限的用户。
--主库操作步骤
[root@tdsql1 ~]# obclient -h192.168.52.85 -P2883 -uroot@thn_test -p -Doceanbase -A
Enter password:
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 48
Server version: OceanBase_CE 4.2.0.0 (r100000152023080109-8024d8ff45c45cf7c62a548752b985648a5795c3) (Built Aug 1 2023 09:54:19)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient [oceanbase]> CREATE USER rep_user IDENTIFIED BY 'Abcd321#';
obclient [oceanbase]> GRANT SELECT ON oceanbase.* TO rep_user;
--备库操作步骤
--创建和资源池备租户，这块区别区别于普通租户创建，需要加standby关键字
[root@tdsql2 ~]# obclient -h192.168.52.86 -P2881 -uroot -p'Abcd321#' -Doceanbase -A
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 3221621541
Server version: OceanBase_CE 4.2.0.0 (r100000152023080109-8024d8ff45c45cf7c62a548752b985648a5795c3) (Built Aug 1 2023 09:54:19)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
--创建规格和资源池
obclient [oceanbase]> create resource unit thn_ut1 max_cpu 10,memory_size '8G';
obclient [oceanbase]> create resource pool thn_b_p1 unit 'thn_ut1',unit_num 1;
--利用创建备租户
obclient [oceanbase]> CREATE STANDBY TENANT IF NOT EXISTS thn_test_b2 LOG_RESTORE_SOURCE = "SERVICE=192.168.52.85:2881 USER=rep_user@thn_test PASSWORD=Abcd321#" RESOURCE_POOL_LIST=('thn_b_p1');
Query OK, 0 rows affected (25.953 sec)
obclient [oceanbase]> SELECT TENANT_NAME, TENANT_TYPE, CREATE_TIME, STATUS, TENANT_ROLE,SCN_TO_TIMESTAMP(SYNC_SCN) FROM oceanbase.DBA_OB_TENANTS WHERE TENANT_NAME = 'thn_test_b2';
+-------------+-------------+----------------------------+--------+-------------+----------------------------+
| TENANT_NAME | TENANT_TYPE | CREATE_TIME | STATUS | TENANT_ROLE | SCN_TO_TIMESTAMP(SYNC_SCN) |
+-------------+-------------+----------------------------+--------+-------------+----------------------------+
| thn_test_b2 | USER | 2023-08-10 10:23:52.417378 | NORMAL | STANDBY | 2023-08-09 14:40:31.811067 |
+-------------+-------------+----------------------------+--------+-------------+----------------------------+
1 row in set (0.007 sec)
--登录普通租户上查询
[root@tdsql2 ~]# obclient -h192.168.52.86 -P2883 -uroot@thn_test_b2 -p -Doceanbase -A
Enter password:
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 1704150
Server version: OceanBase_CE 4.2.0.0 (r100000152023080109-8024d8ff45c45cf7c62a548752b985648a5795c3) (Built Aug 1 2023 09:54:19)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient [oceanbase]> SELECT TENANT_NAME, TENANT_ROLE, SCN_TO_TIMESTAMP(SYNC_SCN) FROM oceanbase.DBA_OB_TENANTS;
+-------------+-------------+----------------------------+
| TENANT_NAME | TENANT_ROLE | SCN_TO_TIMESTAMP(SYNC_SCN) |
+-------------+-------------+----------------------------+
| thn_test_b2 | STANDBY | 2023-08-09 16:05:37.527272 |
+-------------+-------------+----------------------------+
1 row in set (0.032 sec)
--登录到主库上查询
[root@tdsql1 ~]# obclient -h192.168.52.85 -P2883 -uroot@thn_test -p -Doceanbase -A
Enter password:
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 131074
Server version: OceanBase_CE 4.2.0.0 (r100000152023080109-8024d8ff45c45cf7c62a548752b985648a5795c3) (Built Aug 1 2023 09:54:19)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient [oceanbase]> SELECT TENANT_NAME, TENANT_ROLE, SCN_TO_TIMESTAMP(SYNC_SCN) FROM oceanbase.DBA_OB_TENANTS;
+-------------+-------------+----------------------------+
| TENANT_NAME | TENANT_ROLE | SCN_TO_TIMESTAMP(SYNC_SCN) |
+-------------+-------------+----------------------------+
| thn_test | PRIMARY | 2023-08-10 10:27:57.258476 |
+-------------+-------------+----------------------------+
1 row in set (0.017 sec)验证数据同步：
第一张图没有同步的原因是日志还没有完全应用完，通过视图表查询可以看到，数据才同步到昨天，第二张图为同步成功后，新创建的t9和删除的t1已经生效，至此基于备租户方式已经完成。本人比较推荐使用备份的方式去还原在构建主备。
官网还介绍其他两种方式恢复，在这不一一赘述了，详情请移步官网。
链接：https://www.oceanbase.com/docs/common-oceanbase-database-1000000000033892
小结：
OB 4.x的单机一体化概念，可以在业务允许的情况下从MySQL替换到OB上，随着业务逐渐增长可以切换到分布式架构上，这也留到我们下篇文章在去介绍了，如何从单机切换到分布式架构。
作为一个DBer，只有纯粹的热爱技术和DB行业，本文旨在介绍OB新的架构，不为任何数据库去做站台和宣传，只做技术的分享和讨论。
