企业网站制作免费下载,企业网站的开发流程,珠海网络公司有哪些,怎样给网站做关键词优化本文来自OceanBase 用户的体验分享 #xff08;以下简称 OB#xff09;#xff0c;已经开源了3年左右#xff0c;其间从3.x版本演进至4.x版本#xff0c;发生了许多变化。对一个DBer而言#xff0c;最为关切的是如何高效运用OB#xff0c;以及是否能实现如同应用MySQL般… 本文来自OceanBase 用户的体验分享 以下简称 OB已经开源了3年左右其间从3.x版本演进至4.x版本发生了许多变化。对一个DBer而言最为关切的是如何高效运用OB以及是否能实现如同应用MySQL般去进行开发。自开发者大会起OB便提出能够如同MySQL支持单机及主备架构部署。这里将通过以下笔记去实际体验一下是否可以实现。
首先让我们通过一张图共同了解OB所提出的单机与分布式一体化动态转换的理念。
图1 话不多说上代码
今天主要介绍OB单机和主备搭建 VS MySQL 。
1、准备两台OB单机环境
前序对于主机的准备工作就不在赘述了
参考https://open.oceanbase.com/blog/8000124
环境信息
主集群备集群zone192.168.52.85192.168.52.86主租户thn_testthn_test_b2
首先先从官方下载oceanbase-all-in-oneoceanbase-all-in-one是一个综合包包含OBD、OceanBase-ce、OBProxy、OBclient等我们接下来利用OBD工具部署OB单机。 地址OceanBase社区版下载
上传到服务器上
[roottdsql2 ~]# cd /opt/
[roottdsql2 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
[roottdsql2 opt]# tar zxvf oceanbase-all-in-one-4.2.0.0-100120230804133615.el7.x86_64.tar.gz -C /usr/local/
# 解压后进行安装
[roottdsql2 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-00224649250eadd auto set env logic to profile: /root/.bash_profile
#####################################################################Install FinishedSetup Environment: source ~/.oceanbase-all-in-one/bin/env.sh
Start Web Service: obd web
Quick Start: obd demo
More Details: obd -h#安装成功安装提示执行一下加载环境变量
[roottdsql2 opt]# source ~/.oceanbase-all-in-one/bin/env.sh
我们所需要的yaml文件都放在刚才解压的目录中,可以根据实际情况选择。
[roottdsql1 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文件根据实际情况
[roottdsql2 opt]# cat all-components.yaml
## Only need to configure when remote login is required
user:username: rootpassword: 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 dont use hostname, only IP can be supportedip: 192.168.52.85global:# Please set devname as the network adaptors 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 ips network adaptors name is eth0, please use eth0devname: enp7s0# if current hardwares 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: 5Gdatafile_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: obclusterroot_password: Abcd321# # root user passwordproxyro_password: Abcd321# # proxyro user pasword, consistent with obproxys observer_sys_password, can be emptyocp_meta_db: ocp_express # The database name of ocp express metaocp_meta_username: meta # The username of ocp express metaocp_meta_password: Abcd321# # The password of ocp express metaocp_agent_monitor_password: Abcd321# # The password for obagent monitor userocp_meta_tenant: # The config for ocp express meta tenanttenant_name: ocpmax_cpu: 1memory_size: 2Glog_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: /redozone: 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-ceservers:- 192.168.52.85global: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:2881enable_cluster_checkout: false# observer cluster name, consistent with oceanbase-ces appname. When a depends exists, OBD gets this value from the oceanbase-ce of the depends.# cluster_name: obclusterskip_proxy_sys_private_check: trueenable_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-ces proxyro_password, can be empty. When a depends exists, OBD gets thisvalue from the oceanbase-ce of the depends.
obagent:depends:- oceanbase-ceservers:- name: server1# Please dont use hostname, only IP can be supportedip: 192.168.52.85global:home_path: /data/obagent
prometheus:servers:- 192.168.52.85depends:- obagentglobal:# 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.85depends:- prometheusglobal:home_path: /data/grafanalogin_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 whats 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- obagentservers:- 192.168.52.85global:# 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 unlimited
3、进行集群的初始化
包含两个阶段
第一阶段deploy 集群
第二阶段start 集群
注如果操作失败执行obd cluster destroy cluster_name
3.1 deploy 集群
[roottdsql2 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-00224649250e
3.2 start 集群
[roottdsql2 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 -pAbcd321# -Doceanbase -A-------------------------------------------------
| obproxy |
----------------------------------------------
| ip | port | prometheus_port | status |
----------------------------------------------
| 192.168.52.86 | 2883 | 2884 | active |
----------------------------------------------
obclient -h192.168.52.86 -P2883 -uroot -pAbcd321# -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 | xRD1e)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-00224649250e
primary集群和standby集群安装方式一致重复1-3步骤既可
4、primary集群创建租户和试验数据
[roottdsql1 opt]# obclient -h192.168.52.85 -P2883 -uroot -pAbcd321# -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%;
# 使用创建的租户登录测试
[roottdsql1 ~]# obclient -h192.168.52.85 -P2883 -urootthn_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-utils
2. 设置共享目录
mkdir -p /data/ob_backupecho /data/ob_backup *(rw,sync,all_squash) /etc/exports
3. 修改文件权限
chown nfsnobody:nfsnobody -R /data/ob_backup/
4. 配置NFS参数
echo RPCNFSDCOUNT8RPCNFSDARGS-N 2 -N 3 -UNFSD_V4_GRACE90NFSD_V4_LEASE90 /etc/sysconfig/nfs
5. 重启NFS服务器
systemctl restart nfs-server
6. 设置Slot Table
echo sunrpc.tcp_max_slot_table_entries128 /etc/sysctl.confsysctl -w sunrpc.tcp_max_slot_table_entries128
NFS客户端部署
需要再所有OBServer服务器上执行
安装NFS
yum install nfs-utils
设置Slot Table
echo sunrpc.tcp_max_slot_table_entries128 /etc/sysctl.confsysctl -w sunrpc.tcp_max_slot_table_entries128
挂载目录
mkdir -p /data/backupmount -tnfs4 -o rw,nfsvers4.1,sync,lookupcachepositive,hard,timeo600,wsize1048576,rsize1048576,namlen255 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 主库设置备份和归档目录 [roottdsql1 opt]# obclient -h192.168.52.85 -P2883 -urootthn_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_DESTLOCATIONfile:///data/backup/arch_data;
Query OK, 0 rows affected (0.080 sec)obclient [oceanbase] ALTER SYSTEM SET data_backup_destfile:///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集群恢复数据
[roottdsql2 opt]# obclient -h192.168.52.86 -P2883 -uroot -pAbcd321# -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_listthn_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_listthn_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_listthn_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_listthn_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_listthn_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集群操作
[roottdsql2 ~]# obclient -h192.168.52.86 -P2881 -uroot -pAbcd321# -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集群查看[roottdsql1 ~]# obclient -h192.168.52.85 -P2883 -urootthn_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租户
[roottdsql1 ~]# obclient -h192.168.52.85 -P2883 -urootthn_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租户
[roottdsql2 ~]# obclient -h192.168.52.86 -P2881 -urootthn_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 SERVICE192.168.52.85:2881 USERrep_userthn_test PASSWORDAbcd321#;
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_LIST192.168.52.85:2881,USERrep_userthn_test,PASSWORDAbcd321#,TENANT_ID1002,CLUSTER_ID1,COMPATIBILITY_MODEMYSQL,IS_ENCRYPTEDfalse | 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 表示当前日志流副本保存的最早的日志 LSNLog 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 创建备用租户并构建主备
创建访问视图的专用用户
由于备租户在连接主租户时需要访问主租户的部分系统视图因此需要有一个访问视图专用用户具备这部分系统视图的查询权限。您可以提供一个主租户上已有相关权限的用户也可以在主租户上为备租户专门创建一个新的具备相关权限的用户。 --主库操作步骤
[roottdsql1 ~]# obclient -h192.168.52.85 -P2883 -urootthn_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关键字
[roottdsql2 ~]# obclient -h192.168.52.86 -P2881 -uroot -pAbcd321# -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 SERVICE192.168.52.85:2881 USERrep_userthn_test PASSWORDAbcd321# 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)--登录普通租户上查询
[roottdsql2 ~]# obclient -h192.168.52.86 -P2883 -urootthn_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)--登录到主库上查询
[roottdsql1 ~]# obclient -h192.168.52.85 -P2883 -urootthn_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上随着业务逐渐增长可以切换到分布式架构上后续在下篇文章将探讨如何从单机切换到分布式架构。
