部署OceanBase-ce全過程
1. 部署ob-deploy
sudo yum install -y yum-utils sudo yum-config-manager --add-repo https://mirrors.aliyun.com/oceanbase/OceanBase.repo sudo yum install -y ob-deploy
2. 復(fù)制配置文件并修改
github地址:
https://github.com/oceanbase/obdeploy/tree/master/example
根據(jù)部署計劃選擇相應(yīng)的文件。
## Only need to configure when remote login is required user: # username: your username username: root # password: your password if need password: '123456' # 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: # Please don't use hostname, only IP can be supported - 192.168.32.131 global: # The working directory for OceanBase Database. OceanBase Database is started under this directory. This is a required field. home_path: /home/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 # 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: ens33 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. zone: zone1 cluster_id: 1 # please set memory limit to a suitable value which is matching resource. memory_limit: 8G # The maximum running memory for an observer system_memory: 4G # The reserved system memory. system_memory is reserved for general tenants. The default value is 30G. stack_size: 512K cpu_count: 10 cache_wash_threshold: 1G __min_full_resource_pool_memory: 268435456 workers_per_cpu_quota: 10 schema_history_expire_time: 1d # The value of net_thread_count had better be same as cpu's core number. net_thread_count: 4 major_freeze_duty_time: Disable minor_freeze_times: 3 enable_separate_sys_clog: 0 enable_merge_by_turn: FALSE datafile_disk_percentage: 20 # The percentage of the data_dir space to the total disk space. This value takes effect only when datafile_size is 0. The default value is 90. syslog_level: INFO # System log level. The default value is INFO. 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. # observer cluster name, consistent with obproxy's cluster_name appname: obcluster # root_password: # root user password, can be empty root_password: admin # proxyro_password: # proxyro user pasword, consistent with obproxy's observer_sys_password, can be empty obproxy: # 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.32.131 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: /root/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.32.131: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 # 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 this value from the oceanbase-ce of the depends.
3. 使用obd部署
echo "fs.aio-max-nr=1048576" >> /etc/sysctl.conf sysctl -p echo "* soft nofile 20480" >> /etc/security/limits.conf echo "* hard nofile 20480" >> /etc/security/limits.conf obd cluster deploy obcluster -c /etc/obdeploy.yaml obd cluster start obcluster
4. 連接OB
# 連接ob obclient -h127.0.0.1 -uroot@sys#obcluster -P2883 -p'your password' -Doceanbase -A # 連接obproxy obclient -h 127.0.0.1 -P2883 -uroot@proxysys
5. 創(chuàng)建unit和資源池、租戶
create resource unit lcxunit max_cpu=2,max_memory='2G',max_iops=10000,max_disk_size='2G',max_session_num=2000; create resource pool lcxpool unit='lcxunit',unit_num=1; CREATE TENANT IF NOT EXISTS test_tenant charset='utf8mb4', replica_num=1, zone_list=('zone1'), primary_zone='zone1', resource_pool_list=('lcxpool') SET ob_tcp_invited_nodes='%'; DROP TENANT tenant_name [FORCE]
示例 1:
下述語句展示了創(chuàng)建名為 test_tenant 的一個 3 副本的 MySQL 租戶(創(chuàng)建新租戶默認是 MySQL 租戶)。
obclient> CREATE TENANT IF NOT EXISTS test_tenant charset='utf8mb4', replica_num=3, zone_list=('zone1','zone2','zone3'), primary_zone='zone1;zone2,zone3', resource_pool_list=('pool1')
示例 2:
下述語句展示了創(chuàng)建租戶后,直接通過修改變量 ob_tcp_innvited_nodes 的值為 % 以便允許任何客戶端 IP 連接該租戶。
如果不調(diào)整,默認租戶的連接方式為只允許本機的 IP 連接數(shù)據(jù)庫。
obclient> CREATE TENANT IF NOT EXISTS test_tenant charset='utf8mb4', replica_num=3, zone_list=('zone1','zone2','zone3'), primary_zone='zone1;zone2,zone3', resource_pool_list=('pool1') SET ob_tcp_invited_nodes='%'
示例說明如下:
- primary_zone 指該租戶的表的分區(qū) Leader 所在的 Zone ,例如,primary_ zone =’ zone1; zone2, zone3’ 表示該租戶的表的分區(qū) Leader 在 zone1 上, 這時通過分號來分隔。
- zone2 和 zone3 通過逗號分割,表示 zone2 和 zone3 是同一優(yōu)先級,但是比 zone1 優(yōu)先級低。
- primary_zone 設(shè)置時,其值可以為 RANDOM(必須大寫),表示隨機。
普通租戶的內(nèi)存最小規(guī)格必須大于等于 5 GB,否則創(chuàng)建租戶失敗。
如果希望建立租戶進行非常簡單的功能測試,可以修改參數(shù) alter system __min_full_resource_pool_memory 的值為 1073741824 來允許以最小 1 GB 內(nèi)存的規(guī)格創(chuàng)建租戶。
6. 常用運維語句(來自官網(wǎng))
查看服務(wù)器資源配置
可以通過以下 SQL 查看當前的服務(wù)器資源配置。
其中,__all_server 表記錄了了各 OBServer 的狀態(tài),__all_virtual_server_stat 記錄了各 OBServer 的 CPU、內(nèi)存與磁盤使用量。
SELECT a.zone,concat(a.svr_ip,':',a.svr_port) observer, cpu_total, (cpu_total-cpu_assigned) cpu_free, round(mem_total/1024/1024/1024) mem_total_gb, round((mem_total-mem_assigned)/1024/1024/1024) mem_free_gb, usec_to_time(b.last_offline_time) last_offline_time, usec_to_time(b.start_service_time) start_service_time, b.status FROM __all_virtual_server_stat a JOIN __all_server b ON (a.svr_ip=b.svr_ip AND a.svr_port=b.svr_port) ORDER BY a.zone, a.svr_ip;
查看資源池配置
可以通過以下 SQL 查看當前的資源池配置。
其中,__all_resource_pool 表記錄了資源池的信息,__all_unit_config 記錄了資源單元的配置,__all_unit 記錄了資源單元的列表。
SELECT t1.name resource_pool_name, t2.`name` unit_config_name, t2.max_cpu, t2.min_cpu, round(t2.max_memory/1024/1024/1024) max_mem_gb, round(t2.min_memory/1024/1024/1024) min_mem_gb, t3.unit_id, t3.zone, concat(t3.svr_ip,':',t3.`svr_port`) observer,t4.tenant_id, t4.tenant_name FROM __all_resource_pool t1 JOIN __all_unit_config t2 ON (t1.unit_config_id=t2.unit_config_id) JOIN __all_unit t3 on (t1.`resource_pool_id` = t3.`resource_pool_id`) LEFT JOIN __all_tenant t4 on (t1.tenant_id=t4.tenant_id) ORDER BY t1.`resource_pool_id`, t2.`unit_config_id`, t3.unit_id;
查看 RootService 執(zhí)行的管理任務(wù)
可以通過以下 SQL 查看 RootService 最近執(zhí)行的管理任務(wù)。
__all_rootservice_event_history 用于記錄集群級的歷史事件,如合并、Server 上下線、負載均衡任務(wù)執(zhí)行等。配置項 ob_event_history_recycle_interval 控制該表中記錄歷史事件的保留時間,默認為 7 天。
有關(guān) ob_event_history_recycle_interval 配置項的詳細信息,請參見《OceanBase 數(shù)據(jù)庫 參考指南》中的 系統(tǒng)配置項 章節(jié)。
SELECT DATE_FORMAT(gmt_create, '%b%d %H:%i:%s') gmt_create_ , module, event, name1, value1, name2, value2, rs_svr_ip FROM __all_rootservice_event_history WHERE 1 = 1 ORDER BY gmt_create DESC LIMIT 20;
如何查看用戶表
可以通過以下 SQL 查看指定租戶中所有用戶表。
其中,gvtenant視圖記錄了租戶信息 , gvtenant 視圖記錄了租戶信息,gv tenant視圖記錄了租戶信息,gvdatabase 記錄了數(shù)據(jù)庫信息,gvKaTeX parse error: Expected group after '_' at position 14: table 記錄了表信息,_?_all_virtual_me…tenant_id 表示租戶 ID。
SELECT t1.tenant_id,t1.tenant_name,t2.database_name,t3.table_id,t3.table_Name,t3.tablegroup_id,t3.part_num,t4.partition_Id,t4.zone,t4.svr_ip,t4.role, round(t4.data_size/1024/1024) data_size_mb FROM `gv$tenant` t1 JOIN `gv$database` t2 ON (t1.tenant_id = t2.tenant_id) JOIN gv$table t3 ON (t2.tenant_id = t3.tenant_id AND t2.database_id = t3.database_id AND t3.index_type = 0) LEFT JOIN `__all_virtual_meta_table` t4 ON (t2.tenant_id = t4.tenant_id AND ( t3.table_id = t4.table_id OR t3.tablegroup_id = t4.table_id ) AND t4.role IN (1)) WHERE t1.tenant_id = $tenant_id ORDER BY t3.tablegroup_id, t4.partition_Id, t3.table_name ;
查看指定用戶 SQL 命令執(zhí)行情況
可以通過以下 SQL 查看指定用戶執(zhí)行的 SQL 命令的執(zhí)行情況。
其中 TENANT_ID 表示租戶 ID,USER_NAME 表示用戶名,IP_ADDRESS 表示執(zhí)行 SQL 的節(jié)點的 IP 地址。
obclient> SELECT /*+ read_consistency(weak) ob_querytimeout(100000000) */ substr(usec_to_time(request_time),1,19) request_time_, s.svr_ip, s.client_Ip, s.sid,s.tenant_id, s.tenant_name, s.user_name, s.db_name, s.query_sql, s.affected_rows, s.return_rows, s.ret_code, s.event, s.elapsed_time, s.queue_time, s.execute_time, round(s.request_memory_used/1024/1024,2) req_mem_mb, plan_type, is_executor_rpc, is_inner_sql FROM gv$sql_audit s WHERE s.tenant_id=<TENANT_ID> AND user_name='<USER_NAME>' AND svr_ip IN ('<IP_ADDRESS>') ORDER BY request_time DESC LIMIT 100;
查看物理機資源使用情況
可以通過以下 SQL 查看指定租戶在物理機上的資源使用情況。
其中 gvmemstore 視圖記錄了租戶的內(nèi)存使用情況 , memstore 視圖記錄了租戶的內(nèi)存使用情況, memstore視圖記錄了租戶的內(nèi)存使用情況,tenant_id 表示租戶 ID。
obclient> SELECT tenant_id, ip, round(active/1024/1024) active_mb, round(total/1024/1024) total_mb, round(freeze_trigger/1024/1024) freeze_trg_mb, round(mem_limit/1024/1024) mem_limit_mb , freeze_cnt , round((active/freeze_trigger),2) freeze_pct, round(total/mem_limit, 2) mem_usage FROM `gv$memstore` WHERE $tenant_id IN (1001) ORDER BY tenant_id, ip;
查看合并進度
可以通過以下 SQL 查看合并進度。
其中查詢結(jié)果中的 merge_process 列表示當前合并進度的百分比。
SELECT ZONE,svr_ip,major_version,min_version,ss_store_count,merged_ss_store_count,modified_ss_store_count,merge_start_time,merge_finish_time,merge_process FROM __all_virtual_partition_sstable_image_info;
查看分區(qū)信息
可以通過以下 SQL 查詢指定租戶的分區(qū)信息。
其中 __all_virtual_partition_info 記錄了分區(qū)信息,$tenant_id 表示租戶 ID。
SELECT table_id, partition_idx, usec_to_time(min_log_service_ts), TIME_TO_SEC( now())-TIME_TO_SEC(usec_to_time(min_log_service_ts)) delta_time FROM __all_virtual_partition_info WHERE tenant_id=$tenant_id;
7. 小腳本(持續(xù)更新)
關(guān)于 shell 變量取值:
#!/bin/bash function start_cluster() { obd cluster start obcluster } function user_conn() { dbname=$2 echo ${dbname:-oceanbase} obclient -h127.0.0.1 -uroot@$1\#obcluster -P2883 -p'your password' -D${dbname:-oceanbase} -A } function stop_cluster() { obd cluster stop obcluster } case "$1" in 'start') start_cluster ;; '-u') user_conn $2 $3 ;; 'stop') stop_cluster ;; *) echo "ob-manager start|-u|stop ..." esac
總結(jié)
以上為個人經(jīng)驗,希望能給大家一個參考,也希望大家多多支持腳本之家。
相關(guān)文章
使用Dockerfile構(gòu)建java web環(huán)境
用dockerfile構(gòu)建一個Java的web環(huán)境,主要分為2步,第一步是在鏡像中安裝jdk并配置環(huán)境變量,第二步是安裝tomcat,下面分步驟給大家詳細介紹,一起看看吧2016-12-12docker實現(xiàn)跨宿主機的容器之間網(wǎng)絡(luò)互聯(lián)
本文主要介紹了docker實現(xiàn)跨宿主機的容器之間網(wǎng)絡(luò)互聯(lián),文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧2023-01-01Docker容器生命周期 | kill和 stop的區(qū)別與聯(lián)系 | d
這篇文章主要介紹了Docker容器生命周期 | kill和 stop的區(qū)別與聯(lián)系 | docker pause/ unpause,本講內(nèi)容是從?Docker入門到進階里面抽離出來的內(nèi)容,從而使原文更加有序、重點突出,需要的朋友可以參考下2023-08-08docker /var/lib/docker/aufs/mnt 目錄清理方法
在本篇文章里小編給各位整理的是關(guān)于docker /var/lib/docker/aufs/mnt 目錄清理方法,有需要的朋友們可以參考下。2020-03-03docker安裝nginx實現(xiàn)對springboot項目的負載均衡的操作方法
這篇文章主要介紹了docker安裝nginx實現(xiàn)對springboot項目的負載均衡的操作方法,本文給大家介紹的非常詳細,感興趣的朋友跟隨小編一起看看吧2024-06-06