离线镜像文件导入
可以将已有的镜像导出成tar文件，并且可以再次导入到docker；使用联网的docker机器，并pull下载需要的镜像文件。
下载docker镜像
[root@l7dbmerge ~]# docker search oceanbase NAME DESCRIPTION STARS OFFICIAL AUTOMATED oceanbase/oceanbase-xe OceanBase Database 2.2 Express Edition 4 oceanbase/obce-mini obce-mini is a mini standalone test image fo… 3 oceanbase/oceanbase-ce OceanBase is open source now. This is the do… 3 obpilot/oceanbase-ce 3 steps to run an OceanBase-CE docker in you… 2 oceanbase/obce-operator obce-operator 1 zibuyu886/oceanbase-ce-cluster OceanBase ce cluster 1 oceanbase/centos7 0 huweijie/oceanbase-ce-deploy 0 superbigfu/oceanbase 0 20220121/oceanbase 0 jimmyzhou623/oceanbase 0 hongweiqin/anolisos-oceanbase A tentative deploy of oceanbase. 0 stutiredboy/centos_ob Build environment for OceanBase 3.1 CE. Crea… 0 [root@l7dbmerge ~]# docker pull oceanbase/oceanbase-ce Using default tag: latest Error response from daemon: manifest for oceanbase/oceanbase-ce:latest not found: manifest unknown: manifest unknown root@l7dbmerge ~]# docker pull obpilot/oceanbase-ce Using default tag: latest latest: Pulling from obpilot/oceanbase-ce 7a0437f04f83: Downloading [==> ] 3.222MB/75.18MB 615dc48ac9f1: Download complete b10c1cdae3af: Pulling fs layer 4f4fb700ef54: Download complete c0f6c94a6a6a: Waiting 792630f35e24: Waiting [root@l7dbmerge ~]# docker pull obpilot/oceanbase-ce Using default tag: latest latest: Pulling from obpilot/oceanbase-ce 7a0437f04f83: Downloading [===========================> ] 40.88MB/75.18MB 615dc48ac9f1: Download complete b10c1cdae3af: Downloading [> ] 10.18MB/836.3MB 4f4fb700ef54: Download complete c0f6c94a6a6a: Download complete 792630f35e24: Download complete [root@l7dbmerge ~]# docker pull obpilot/oceanbase-ce Using default tag: latest latest: Pulling from obpilot/oceanbase-ce 7a0437f04f83: Pull complete 615dc48ac9f1: Pull complete b10c1cdae3af: Pull complete 4f4fb700ef54: Pull complete c0f6c94a6a6a: Pull complete 792630f35e24: Pull complete Digest: sha256:7ac28415cf27ba19cb47acb67a55ebf9848ad73a63d80b7e2e85d653233dbaeb Status: Downloaded newer image for obpilot/oceanbase-ce:latest docker.io/obpilot/oceanbase-ce:latest
查看镜像
[root@l7dbmerge ~]# docker image ls REPOSITORY TAG IMAGE ID CREATED SIZE obpilot/oceanbase-ce latest 943379e0b05b 3 months ago 2.25GB oceanbase/obce-mini latest 1a5ca6d233a7 7 months ago 690MB
导出镜像
[root@l7dbmerge ~]# docker save obpilot/oceanbase-ce -o oceanbase-ce.tar [root@l7dbmerge ~]# ll oceanbase* -rw------- 1 root root 2264088064 3月 23 10:16 oceanbase-ce.tar
将tar文件上传到内网docker服务器，导入镜像文件，模拟离线部署oceanbase-ce镜像
[root@l7dbmerge ~]# docker load -i oceanbase-ce.tar
查看导入的镜像文件
[root@l7dbmerge ~]# docker images REPOSITORY TAG IMAGE ID CREATED SIZE obpilot/oceanbase-ce latest 943379e0b05b 3 months ago 2.25GB oceanbase/obce-mini latest 1a5ca6d233a7 7 months ago 690MB
运行容器
[root@l7dbmerge container]# docker run -itd -m 30G -p 2881:2881 -p 2883:2883 --name oceanbase-ce -d -e OB_HOME_PATH="/opt/OceanBase/container/" obpilot/oceanbase-ce 513dbae8bd6fb46f299d8e7c8d8277d69bc2f383f53e47019291c9913c49a59a [root@l7dbmerge ~]# docker container ls CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES eb4400aee02d obpilot/oceanbase-ce "/bin/bash" 3 seconds ago Up 3 seconds 0.0.0.0:2881->2881/tcp, :::2881->2881/tcp, 0.0.0.0:2883->2883/tcp, :::2883->2883/tcp oceanbase-ce
查看容器启动日志
[root@l7dbmerge ~]# docker logs oceanbase-ce [root@l7dbmerge ~]# ---无日志输出
查看容器
[root@l7dbmerge ~]# docker container ls CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES eb4400aee02d obpilot/oceanbase-ce "/bin/bash" 2 minutes ago Up 2 minutes 0.0.0.0:2881->2881/tcp, :::2881->2881/tcp, 0.0.0.0:2883->2883/tcp, :::2883->2883/tcp oceanbase-ce [root@l7dbmerge ~]# docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES eb4400aee02d obpilot/oceanbase-ce "/bin/bash" 5 minutes ago Up 5 minutes 0.0.0.0:2881->2881/tcp, :::2881->2881/tcp, 0.0.0.0:2883->2883/tcp, :::2883->2883/tcp oceanbase-ce
OceanBase 集群部署
进入容器
[root@l7dbmerge ~]# docker exec -it oceanbase-ce bash [admin@eb4400aee02d ~]$ df -h Filesystem Size Used Avail Use% Mounted on overlay 183G 87G 97G 48% / tmpfs 64M 0 64M 0% /dev tmpfs 9.8G 0 9.8G 0% /sys/fs/cgroup shm 64M 0 64M 0% /dev/shm /dev/mapper/centos-root 183G 87G 97G 48% /data tmpfs 9.8G 0 9.8G 0% /proc/acpi tmpfs 9.8G 0 9.8G 0% /proc/scsi tmpfs 9.8G 0 9.8G 0% /sys/firmware
获取集群信息
[admin@eb4400aee02d ~]$ id uid=1000(admin) gid=1000(admin) groups=1000(admin) [admin@eb4400aee02d ~]$ obd cluster list +------------------------------------------------------------+ | Cluster List | +--------+---------------------------------+-----------------+ | Name | Configuration Path | Status (Cached) | +--------+---------------------------------+-----------------+ | obdemo | /home/admin/.obd/cluster/obdemo | deployed | +--------+---------------------------------+-----------------+
集群配置文件信息
[admin@eb4400aee02d ~]$ cat /home/admin/.obd/cluster/obdemo/config.yaml ## Only need to configure when remote login is required # user: # username: your username # password: your password if need # 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 - 127.0.0.1 global: # The working directory for OceanBase Database. OceanBase Database is started under this directory. This is a required field. home_path: /home/admin/oceanbase-ce # The directory for data storage. The default value is $home_path/store. # data_dir: /data/1 # The directory for clog, ilog, and slog. The default value is the same as the data_dir value. redo_dir: /data/log1 # 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: lo mysql_port: 2881 # External port for OceanBase Database. The default value is 2881. rpc_port: 2882 # Internal port for OceanBase Database. The default value is 2882. 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: 16 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: 10 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. datafile_size: 5G syslog_level: ERROR # 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: obce-single root_password: rootPWD123 # root user password, can be empty proxyro_password: proxyROPWD123 # proxyro user pasword, consistent with obproxy's observer_sys_password, can be empty obproxy: servers: - 127.0.0.1 depends: - oceanbase-ce 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: /home/admin/obproxy # oceanbase root server list # format: ip:mysql_port,ip:mysql_port rs_list: 127.0.0.1:2881 enable_cluster_checkout: false # observer cluster name, consistent with oceanbase-ce's appname cluster_name: obce-single obproxy_sys_password: proxySYSPWD123 # obproxy sys user password, can be empty observer_sys_password: proxyROPWD123 # proxyro user pasword, consistent with oceanbase-ce's proxyro_password, can be empty automatic_match_work_thread: false work_thread_num: 12 xflush_log_level: ERROR monitor_log_level: ERROR syslog_level: ERROR log_dir_size_threshold: 1G enable_compression_protocol: false
启动集群
[admin@eb4400aee02d ~]$ obd cluster start obdemo Get local repositories and plugins ok Open ssh connection ok Cluster param config check ok Check before start observer ok Check before start obproxy ok Start observer ok observer program health check ok Connect to observer ok Initialize cluster Cluster bootstrap ok Wait for observer init ok +---------------------------------------------+ | observer | +-----------+---------+------+-------+--------+ | ip | version | port | zone | status | +-----------+---------+------+-------+--------+ | 127.0.0.1 | 3.1.1 | 2881 | zone1 | active | +-----------+---------+------+-------+--------+ Start obproxy ok obproxy program health check ok Connect to obproxy ok Initialize cluster +---------------------------------------------+ | obproxy | +-----------+------+-----------------+--------+ | ip | port | prometheus_port | status | +-----------+------+-----------------+--------+ | 127.0.0.1 | 2883 | 2884 | active | +-----------+------+-----------------+--------+ obdemo running
获取集群详细信息
[admin@eb4400aee02d ~]$ obd cluster display obdemo Get local repositories and plugins ok Open ssh connection ok Cluster status check ok Connect to observer ok Wait for observer init ok +---------------------------------------------+ | observer | +-----------+---------+------+-------+--------+ | ip | version | port | zone | status | +-----------+---------+------+-------+--------+ | 127.0.0.1 | 3.1.1 | 2881 | zone1 | active | +-----------+---------+------+-------+--------+ Connect to obproxy ok +---------------------------------------------+ | obproxy | +-----------+------+-----------------+--------+ | ip | port | prometheus_port | status | +-----------+------+-----------------+--------+ | 127.0.0.1 | 2883 | 2884 | active | +-----------+------+-----------------+--------+
查看 OBSERVER 进程特点
分析一个陌生环境的 OceanBase 集群节点进程，首先通过下面命令确定其启动位置、启动文件和启动参数等。
[admin@eb4400aee02d ~]$ ps -ef|grep observer
admin 148 0 99 02:44 ? 00:17:08 /home/admin/oceanbase-ce/bin/observer -r 127.0.0.1:2882:2881 -o __min_full_resource_pool_memory=268435456,memory_limit=8G,system_memory=4G,stack_size=512K,cpu_count=16,cache_wash_threshold=1G,workers_per_cpu_quota=10,schema_history_expire_time=1d,net_thread_count=4,major_freeze_duty_time=Disable,minor_freeze_times=10,enable_separate_sys_clog=0,enable_merge_by_turn=False,datafile_size=5G,enable_syslog_wf=False,enable_syslog_recycle=True,max_syslog_file_count=4,root_password=rootPWD123 -z zone1 -p 2881 -P 2882 -n obce-single -c 1 -d /home/admin/oceanbase-ce/store -i lo -l ERROR
[admin@eb4400aee02d ~]$ ps -ef|grep obproxy
admin 734 0 3 02:45 ? 00:00:19 /home/admin/obproxy/bin/obproxy -o enable_strict_kernel_release=False,enable_cluster_checkout=False,automatic_match_work_thread=False,work_thread_num=12,xflush_log_level=ERROR,monitor_log_level=ERROR,syslog_level=ERROR,log_dir_size_threshold=1G,enable_compression_protocol=False --listen_port 2883 --prometheus_listen_port 2884 --rs_list 127.0.0.1:2881 --cluster_name obce-single
admin 784 0 0 02:45 pts/1 00:00:00 bash /home/admin/obproxy/obproxyd.sh /home/admin/obproxy 127.0.0.1 2883 daemon
[admin@eb4400aee02d ~]$ ls -l /proc/`pidof observer`/{cwd,exe,cmdline}
-r--r--r-- 1 admin admin 0 Mar 23 02:45 /proc/148/cmdline
lrwxrwxrwx 1 admin admin 0 Mar 23 02:45 /proc/148/cwd -> /home/admin/oceanbase-ce
lrwxrwxrwx 1 admin admin 0 Mar 23 02:45 /proc/148/exe -> /home/admin/.obd/repository/oceanbase-ce/3.1.1/c8d49673b8b01056ab1d6abc9d26dd6987d95a48/bin/observer
[admin@eb4400aee02d ~]$ cat /proc/`pidof observer`/cmdline
/home/admin/oceanbase-ce/bin/observer-r127.0.0.1:2882:2881-o__min_full_resource_pool_memory=268435456,memory_limit=8G,system_memory=4G,stack_size=512K,cpu_count=16,cache_wash_threshold=1G,workers_per_cpu_quota=10,schema_history_expire_time=1d,net_thread_count=4,major_freeze_duty_time=Disable,minor_freeze_times=10,enable_separate_sys_clog=0,enable_merge_by_turn=False,datafile_size=5G,enable_syslog_wf=False,enable_syslog_recycle=True,max_syslog_file_count=4,root_password=rootPWD123-zzone1-p2881-P2882-nobce-single-c1-d/home/admin/oceanbase-ce/store-ilo-lERROR[admin@eb4400aee02d ~]$
查看进程监听端口
[admin@eb4400aee02d ~]$ netstat -ntlp Active Internet connections (only servers) Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0 0.0.0.0:2881 0.0.0.0:* LISTEN 148/observer tcp 0 0 0.0.0.0:2882 0.0.0.0:* LISTEN 148/observer tcp 0 0 0.0.0.0:2883 0.0.0.0:* LISTEN 734/obproxy tcp 0 0 0.0.0.0:2884 0.0.0.0:* LISTEN 734/obproxy
observer 进程会监听 2 个端口
-
连接端口 2881。
-
RPC 通信端口 2882。
obproyx 进程会监听 2 个端口
-
连接端口 2883。
-
prometheus端口 2884。
查看 OceanBase 数据库工作目录结构
[admin@eb4400aee02d ~]$ tree /home/admin/.obd/ /home/admin/.obd/ |-- cluster | `-- obdemo | `-- config.yaml |-- log | |-- obd | `-- obd.2021-11-17 |-- mirror | |-- local | `-- remote | |-- OceanBase-community-stable-el8 | | |-- obproxy-3.2.0-1.el8.x86_64.rpm | | |-- oceanbase-ce-3.1.1-4.el8.x86_64.rpm | | |-- oceanbase-ce-libs-3.1.1-4.el8.x86_64.rpm | | |-- repodata | | | `-- c66deae1a4f159e59b489865d8c8ed3e14ab4784016b21bef6ae05fc4b69d4fe-primary.xml.gz | | `-- repomd.xml | |-- OceanBase-development-kit-el8 | | |-- repodata | | | `-- d872f5df6588b8c36bcbb545f9c1674d6d9973bdbb4adb26d1acc6694fa7a591-primary.xml.gz | | `-- repomd.xml | `-- OceanBase.repo |-- obd.conf |-- plugins | |-- mysqltest | | `-- 3.1.0 | | |-- check_opt.py | | |-- check_test.py | | |-- init.py | | |-- init_sql | | | |-- init.sql | | | |-- init_mini.sql | | | `-- init_user.sql | | |-- mysqltest_lib | | | |-- __init__.py | | | |-- case_filter.py | | | |-- psmallsource.py | | | |-- psmalltest.py | | | |-- rebootcase.py | | | `-- succtest.py | | |-- r | | | `-- mysql | | | `-- chinese.result | | |-- run_test.py | | `-- t | | `-- chinese.test | |-- ob-deploy | | `-- 1.0.0 | | `-- file_map.yaml | |-- obagent | | `-- 0.1 | | |-- bootstrap.py | | |-- connect.py | | |-- destroy.py | | |-- display.py | | |-- file_map.yaml | | |-- generate_config.py | | |-- init.py | | |-- parameter.yaml | | |-- reload.py | | |-- start.py | | |-- start_check.py | | |-- status.py | | `-- stop.py | |-- obproxy | | |-- 3.1.0 | | | |-- bootstrap.py | | | |-- connect.py | | | |-- destroy.py | | | |-- display.py | | | |-- file_map.yaml | | | |-- generate_config.py | | | |-- init.py | | | |-- obproxyd.sh | | | |-- parameter.yaml | | | |-- reload.py | | | |-- start.py | | | |-- start_check.py | | | |-- status.py | | | |-- stop.py | | | `-- upgrade.py | | `-- 3.2.0 | | `-- file_map.yaml | |-- oceanbase | | `-- 3.1.0 | | |-- bootstrap.py | | |-- connect.py | | |-- create_tenant.py | | |-- destroy.py | | |-- display.py | | |-- drop_tenant.py | | |-- file_map.yaml | | |-- generate_config.py | | |-- init.py | | |-- parameter.yaml | | |-- reload.py | | |-- start.py | | |-- start_check.py | | |-- status.py | | |-- stop.py | | `-- upgrade.py | |-- oceanbase-ce -> oceanbase | |-- oceanbase-ce-libs | | `-- 3.1.0 | | `-- file_map.yaml | |-- sysbench | | `-- 3.1.0 | | `-- run_test.py | `-- tpch | `-- 3.1.0 | |-- create_tpch_mysql_table_part.ddl | |-- pre_test.py | |-- queries | | |-- db1.sql | | |-- db10.sql | | |-- db11.sql | | |-- db12.sql | | |-- db13.sql | | |-- db14.sql | | |-- db15.sql | | |-- db16.sql | | |-- db17.sql | | |-- db18.sql | | |-- db19.sql | | |-- db2.sql | | |-- db20.sql | | |-- db21.sql | | |-- db22.sql | | |-- db3.sql | | |-- db4.sql | | |-- db5.sql | | |-- db6.sql | | |-- db7.sql | | |-- db8.sql | | `-- db9.sql | `-- run_test.py |-- repository | |-- obproxy | | `-- 3.2.0 | | |-- 42a720977963f939282a470378ca383cc5824dfd | | | `-- bin | | | `-- obproxy | | `-- obproxy -> /home/admin/.obd/repository/obproxy/3.2.0/42a720977963f939282a470378ca383cc5824dfd | |-- oceanbase-ce | | `-- 3.1.1 | | |-- c8d49673b8b01056ab1d6abc9d26dd6987d95a48 | | | |-- bin | | | | `-- observer | | | `-- lib -> /home/admin/.obd/repository/oceanbase-ce-libs/3.1.1/bcecf6b5be502bad9ee4a6bf9f9ef696f8fa538d | | `-- oceanbase-ce -> /home/admin/.obd/repository/oceanbase-ce/3.1.1/c8d49673b8b01056ab1d6abc9d26dd6987d95a48 | `-- oceanbase-ce-libs | `-- 3.1.1 | |-- bcecf6b5be502bad9ee4a6bf9f9ef696f8fa538d | | |-- libaio.so -> libaio.so.1.0.1 | | |-- libaio.so.1 -> libaio.so.1.0.1 | | |-- libaio.so.1.0.1 | | |-- libmariadb.so -> libmariadb.so.3 | | `-- libmariadb.so.3 | `-- oceanbase-ce-libs -> /home/admin/.obd/repository/oceanbase-ce-libs/3.1.1/bcecf6b5be502bad9ee4a6bf9f9ef696f8fa538d `-- version 51 directories, 108 files
连接 OceanBase
使用 observer 2881 端口登录
[admin@eb4400aee02d ~]$ obclient -h127.1 -uroot@sys -P2881 -prootPWD123 -c -A Welcome to the OceanBase. Commands end with ; or \g. Your MySQL connection id is 3221487825 Server version: 5.7.25 OceanBase 3.1.1 (r4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e) (Built Oct 21 2021 10:52:05) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> show parameters like 'cluster'; +-------+----------+-----------+----------+---------+-----------+-------------+---------------------+----------+---------+---------+-------------------+ | zone | svr_type | svr_ip | svr_port | name | data_type | value | info | section | scope | source | edit_level | +-------+----------+-----------+----------+---------+-----------+-------------+---------------------+----------+---------+---------+-------------------+ | zone1 | observer | 127.0.0.1 | 2882 | cluster | NULL | obce-single | Name of the cluster | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE | +-------+----------+-----------+----------+---------+-----------+-------------+---------------------+----------+---------+---------+-------------------+ 1 row in set (0.013 sec) MySQL [(none)]> show databases; +--------------------+ | Database | +--------------------+ | oceanbase | | information_schema | | mysql | | SYS | | LBACSYS | | ORAAUDITOR | | test | +--------------------+ 7 rows in set (0.005 sec)
使用 obproxy 2883 端口登录
[admin@eb4400aee02d ~]$ obclient -h127.1 -uroot@sys#obce-single -P2883 -prootPWD123 Welcome to the OceanBase. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.6.25 OceanBase 3.1.1 (r4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e) (Built Oct 21 2021 10:52:05) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> show parameters like 'cluster'; +-------+----------+-----------+----------+---------+-----------+-------------+---------------------+----------+---------+---------+-------------------+ | zone | svr_type | svr_ip | svr_port | name | data_type | value | info | section | scope | source | edit_level | +-------+----------+-----------+----------+---------+-----------+-------------+---------------------+----------+---------+---------+-------------------+ | zone1 | observer | 127.0.0.1 | 2882 | cluster | NULL | obce-single | Name of the cluster | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE | +-------+----------+-----------+----------+---------+-----------+-------------+---------------------+----------+---------+---------+-------------------+ 1 row in set (0.009 sec) MySQL [(none)]> show databases; +--------------------+ | Database | +--------------------+ | oceanbase | | information_schema | | mysql | | SYS | | LBACSYS | | ORAAUDITOR | | test | +--------------------+ 7 rows in set (0.016 sec)
创建业务租户、数据库及表
查询系统资源
[admin@eb4400aee02d ~]$ obclient -h127.1 -uroot@sys -P2881 -prootPWD123 -c -A oceanbase Welcome to the OceanBase. Commands end with ; or \g. Your MySQL connection id is 3221487889 Server version: 5.7.25 OceanBase 3.1.1 (r4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e) (Built Oct 21 2021 10:52:05) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [oceanbase]> select a.zone,concat(a.svr_ip,':',a.svr_port) observer, cpu_total, (cpu_total-cpu_assigned) cpu_free, -> round(mem_total/1024/1024/1024) mem_total_gb, round((mem_total-mem_assigned)/1024/1024/1024) mem_free_gb, -> round(disk_total/1024/1024/1024) disk_total_gb, -> substr(a.build_version,1,6) version,usec_to_time(b.start_service_time) start_service_time -> from __all_virtual_server_stat a join __all_server b on (a.svr_ip=b.svr_ip and a.svr_port=b.svr_port) -> order by a.zone, a.svr_ip; +-------+----------------+-----------+----------+--------------+-------------+---------------+---------+----------------------------+ | zone | observer | cpu_total | cpu_free | mem_total_gb | mem_free_gb | disk_total_gb | version | start_service_time | +-------+----------------+-----------+----------+--------------+-------------+---------------+---------+----------------------------+ | zone1 | 127.0.0.1:2882 | 14 | 11.5 | 4 | 3 | 5 | 3.1.1_ | 2022-03-23 10:45:37.473807 | +-------+----------------+-----------+----------+--------------+-------------+---------------+---------+----------------------------+ 1 row in set (0.014 sec) MySQL [oceanbase]> 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; +--------------------+------------------+---------+---------+------------+------------+---------+-------+----------------+-----------+-------------+ | resource_pool_name | unit_config_name | max_cpu | min_cpu | max_mem_gb | min_mem_gb | unit_id | zone | observer | tenant_id | tenant_name | +--------------------+------------------+---------+---------+------------+------------+---------+-------+----------------+-----------+-------------+ | sys_pool | sys_unit_config | 5 | 2.5 | 1 | 1 | 1 | zone1 | 127.0.0.1:2882 | 1 | sys | +--------------------+------------------+---------+---------+------------+------------+---------+-------+----------------+-----------+-------------+ 1 row in set (0.020 sec)
创建资源
创建资源单元
MySQL [oceanbase]> CREATE resource unit my_test_unit max_cpu=4, min_cpu=4, max_memory='1G', min_memory='1G', max_iops=10000, min_iops=1000, max_session_num=100000, max_disk_size='10G'; Query OK, 0 rows affected (0.017 sec)
创建资源池
MySQL [oceanbase]> CREATE resource pool my_test_pool unit = 'my_test_unit', unit_num = 1; Query OK, 0 rows affected (0.028 sec)
创建租户
MySQL [oceanbase]> create tenant my_test_obmysql resource_pool_list=('my_test_pool'), primary_zone='zone1',comment 'mysql tenant/instance', charset='utf8' set ob_tcp_invited_nodes='%', ob_compatibility_mode='mysql';
Query OK, 0 rows affected (2.277 sec)
登录管理租户
查看租户信息
MySQL [oceanbase]> select * from oceanbase.gv$tenant;
+-----------+--------------------+-----------+--------------+----------------+-----------------------+-----------+---------------+
| tenant_id | tenant_name | zone_list | primary_zone | collation_type | info | read_only | locality |
+-----------+--------------------+-----------+--------------+----------------+-----------------------+-----------+---------------+
| 1 | sys | zone1 | zone1 | 0 | system tenant | 0 | FULL{1}@zone1 |
| 1001 | my_test_obmysql | zone1 | zone1 | 0 | mysql tenant/instance | 0 | FULL{1}@zone1 |
+-----------+--------------------+-----------+--------------+----------------+-----------------------+-----------+---------------+
2 rows in set (0.028 sec)
MySQL [oceanbase]> select tenant_id,tenant_name,primary_zone from __all_tenant;
+-----------+--------------------+--------------+
| tenant_id | tenant_name | primary_zone |
+-----------+--------------------+--------------+
| 1 | sys | zone1 |
| 1001 | my_test_obmysql | zone1 |
+-----------+--------------------+--------------+
2 rows in set (0.006 sec)
MySQL [oceanbase]> 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;
+--------------------+------------------+---------+---------+------------+------------+---------+-------+----------------+-----------+--------------------+
| resource_pool_name | unit_config_name | max_cpu | min_cpu | max_mem_gb | min_mem_gb | unit_id | zone | observer | tenant_id | tenant_name |
+--------------------+------------------+---------+---------+------------+------------+---------+-------+----------------+-----------+--------------------+
| sys_pool | sys_unit_config | 5 | 2.5 | 1 | 1 | 1 | zone1 | 127.0.0.1:2882 | 1 | sys |
| my_test_pool | my_test_unit | 4 | 4 | 1 | 1 | 1001 | zone1 | 127.0.0.1:2882 | 1001 | my_test_obmysql |
+--------------------+------------------+---------+---------+------------+------------+---------+-------+----------------+-----------+--------------------+
2 rows in set (0.002 sec)
MySQL [oceanbase]> CREATE resource pool ob_pool unit = 'my_test_unit', unit_num = 1;
Query OK, 0 rows affected (0.068 sec)
MySQL [oceanbase]> create tenant obmysql resource_pool_list=('ob_pool'), primary_zone='zone1',comment 'mysql tenant/instance', charset='utf8' set ob_tcp_invited_nodes='%', ob_compatibility_mode='mysql';
Query OK, 0 rows affected (2.771 sec)
MySQL [oceanbase]> select * from oceanbase.gv$tenant;
+-----------+-----------------+-----------+--------------+----------------+-----------------------+-----------+---------------+
| tenant_id | tenant_name | zone_list | primary_zone | collation_type | info | read_only | locality |
+-----------+-----------------+-----------+--------------+----------------+-----------------------+-----------+---------------+
| 1 | sys | zone1 | zone1 | 0 | system tenant | 0 | FULL{1}@zone1 |
| 1004 | obmysql | zone1 | zone1 | 0 | mysql tenant/instance | 0 | FULL{1}@zone1 |
| 1005 | my_test_obmysql | zone1 | zone1 | 0 | mysql tenant/instance | 0 | FULL{1}@zone1 |
+-----------+-----------------+-----------+--------------+----------------+-----------------------+-----------+---------------+
3 rows in set (0.003 sec)
MySQL [oceanbase]> select tenant_id,tenant_name,primary_zone from __all_tenant;
+-----------+-----------------+--------------+
| tenant_id | tenant_name | primary_zone |
+-----------+-----------------+--------------+
| 1 | sys | zone1 |
| 1004 | obmysql | zone1 |
| 1005 | my_test_obmysql | zone1 |
+-----------+-----------------+--------------+
3 rows in set (0.003 sec)
登录租户
[admin@eb4400aee02d ~]$ obclient -h 127.1 -uroot@my_test_obmysql#obce-single -P2883 -c -A oceanbase Welcome to the OceanBase. Commands end with ; or \g. Your MySQL connection id is 36 Server version: 5.6.25 OceanBase 3.1.1 (r4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e) (Built Oct 21 2021 10:52:05) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [oceanbase]> exit Bye [admin@eb4400aee02d ~]$ obclient -h 127.1 -uroot@my_test_obmysql#obce-single -P2883 -p -c -A oceanbase Enter password: ---初始密码是空，直接回车登录 Welcome to the OceanBase. Commands end with ; or \g. Your MySQL connection id is 37 Server version: 5.6.25 OceanBase 3.1.1 (r4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e) (Built Oct 21 2021 10:52:05) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [oceanbase]> exit Bye [admin@eb4400aee02d ~]$ obclient -h 127.1 -uroot@my_test_obmysql#obce-single -P2881 -c -A oceanbase ERROR 1045 (42000): Access denied for user 'root'@'xxx.xxx.xxx.xxx' (using password: NO) ---登录observer 2881，不能使用#obce-single [admin@eb4400aee02d ~]$ obclient -h 127.1 -uroot@my_test_obmysql -P2881 -c -A oceanbase Welcome to the OceanBase. Commands end with ; or \g. Your MySQL connection id is 3221489741 Server version: 5.7.25 OceanBase 3.1.1 (r4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e) (Built Oct 21 2021 10:52:05) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [oceanbase]> exit Bye [admin@eb4400aee02d ~]$ obclient -h 127.1 -uroot@my_test_obmysql -P2881 -p -c -A oceanbase Enter password: ---初始密码是空，直接回车登录 Welcome to the OceanBase. Commands end with ; or \g. Your MySQL connection id is 3221489739 Server version: 5.7.25 OceanBase 3.1.1 (r4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e) (Built Oct 21 2021 10:52:05) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [oceanbase]> exit Bye
设置root密码
MySQL [oceanbase]> alter user root identified by 'pass4obs' ; Query OK, 0 rows affected (0.067 sec) [admin@eb4400aee02d ~]$ obclient -h 127.1 -uroot@my_test_obmysql -P2881 -c -A oceanbase ERROR 1045 (42000): Access denied for user 'root'@'xxx.xxx.xxx.xxx' (using password: NO) ---设置root密码后，必须使用密码登录 [admin@eb4400aee02d ~]$ obclient -h 127.1 -uroot@mysql_test_obmysql -P2881 -ppass4obs -c -A oceanbase Welcome to the OceanBase. Commands end with ; or \g. Your MySQL connection id is 3221489776 Server version: 5.7.25 OceanBase 3.1.1 (r4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e) (Built Oct 21 2021 10:52:05) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [oceanbase]> [admin@eb4400aee02d ~]$ obclient -h 127.1 -uroot@my_test_obmysql#obce-single -P2883 -ppass4obs -c -A oceanbase Welcome to the OceanBase. Commands end with ; or \g. Your MySQL connection id is 40 Server version: 5.6.25 OceanBase 3.1.1 (r4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e) (Built Oct 21 2021 10:52:05) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [oceanbase]>
创建数据库及用户
MySQL [oceanbase]> select tenant_id,tenant_name,primary_zone from __all_tenant; ERROR 1146 (42S02): Table 'oceanbase.__all_tenant' doesn,t exist MySQL [oceanbase]> show databases; +--------------------+ | Database | +--------------------+ | oceanbase | | information_schema | | mysql | | test | +--------------------+ 4 rows in set (0.010 sec) MySQL [(oceanbase)]> create database mytestdb ; Query OK, 1 row affected (0.098 sec) MySQL [oceanbase]> show databases; +--------------------+ | Database | +--------------------+ | oceanbase | | information_schema | | mysql | | test | | mytestdb | +--------------------+ 5 rows in set (0.004 sec) MySQL [(oceanbase)]> create user mytestuser@'%' identified by 'pass4usr' ; Query OK, 0 rows affected (0.151 sec) MySQL [(oceanbase)]> grant all privileges on *.* to mytestuser@'%'; Query OK, 0 rows affected (4.991 sec) MySQL [(oceanbase)]> show databases; +--------------------+ | Database | +--------------------+ | oceanbase | | information_schema | | mysql | | test | | testdb1 | +--------------------+ 5 rows in set (0.010 sec)
创建业务表
[admin@eb4400aee02d ~]$ obclient -h 127.1 -umytestuser@my_test_obmysql#obce-single -P2883 -ppass4usr -c -A mytestdb
Welcome to the OceanBase. Commands end with ; or \g.
Your MySQL connection id is 42
Server version: 5.6.25 OceanBase 3.1.1 (r4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e) (Built Oct 21 2021 10:52:05)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [mytestdb]>
MySQL [mytestdb]> CREATE TABLE `country` (
-> `Code` char(3) NOT NULL DEFAULT '',
-> `Name` char(52) NOT NULL DEFAULT '',
-> `Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia', -> `Region` char(26) NOT NULL DEFAULT '',
-> `SurfaceArea` decimal(10,2) NOT NULL DEFAULT '0.00',
-> `IndepYear` smallint DEFAULT NULL,
-> `Population` int NOT NULL DEFAULT '0',
-> `LifeExpectancy` decimal(3,1) DEFAULT NULL,
-> `GNP` decimal(10,2) DEFAULT NULL,
-> `GNPOld` decimal(10,2) DEFAULT NULL,
-> `LocalName` char(45) NOT NULL DEFAULT '',
-> `GovernmentForm` char(45) NOT NULL DEFAULT '',
-> `HeadOfState` char(60) DEFAULT NULL,
-> `Capital` int DEFAULT NULL,
-> `Code2` char(2) NOT NULL DEFAULT '',
-> PRIMARY KEY (`Code`)
-> );
Query OK, 0 rows affected (0.231 sec)
MySQL [mytestdb]> INSERT INTO `country` VALUES ('ABW','Aruba','North America','Caribbean',193.00,NULL,103000,78.4,828.00,793.00,'Aruba','Nonmetropolitan Territory of The Netherlands','Beatrix',129,'AW');
Query OK, 1 row affected (0.030 sec)
MySQL [mytestdb]> INSERT INTO `country` VALUES ('AFG','Afghanistan','Asia','Southern and Central Asia',652090.00,1919,22720000,45.9,5976.00,NULL,'Afganistan/Afqanestan','Islamic Emirate','Mohammad Omar',1,'AF');
Query OK, 1 row affected (0.004 sec)
MySQL [mytestdb]> INSERT INTO `country` VALUES ('AGO','Angola','Africa','Central Africa',1246700.00,1975,12878000,38.3,6648.00,7984.00,'Angola','Republic','José Eduardo dos Santos',56,'AO');
Query OK, 1 row affected (0.004 sec)
MySQL [mytestdb]> INSERT INTO `country` VALUES ('AIA','Anguilla','North America','Caribbean',96.00,NULL,8000,76.1,63.20,NULL,'Anguilla','Dependent Territory of the UK','Elisabeth II',62,'AI');
Query OK, 1 row affected (0.004 sec)
MySQL [mytestdb]> INSERT INTO `country` VALUES ('ALB','Albania','Europe','Southern Europe',28748.00,1912,3401200,71.6,3205.00,2500.00,'Shqipëria','Republic','Rexhep Mejdani',34,'AL');
Query OK, 1 row affected (0.003 sec)
MySQL [mytestdb]> select * from country;
+------+-------------+---------------+---------------------------+-------------+-----------+------------+----------------+---------+---------+-----------------------+----------------------------------------------+--------------------------+---------+-------+
| Code | Name | Continent | Region | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP | GNPOld | LocalName | GovernmentForm | HeadOfState | Capital | Code2 |
+------+-------------+---------------+---------------------------+-------------+-----------+------------+----------------+---------+---------+-----------------------+----------------------------------------------+--------------------------+---------+-------+
| ABW | Aruba | North America | Caribbean | 193.00 | NULL | 103000 | 78.4 | 828.00 | 793.00 | Aruba | Nonmetropolitan Territory of The Netherlands | Beatrix | 129 | AW |
| AFG | Afghanistan | Asia | Southern and Central Asia | 652090.00 | 1919 | 22720000 | 45.9 | 5976.00 | NULL | Afganistan/Afqanestan | Islamic Emirate | Mohammad Omar | 1 | AF |
| AGO | Angola | Africa | Central Africa | 1246700.00 | 1975 | 12878000 | 38.3 | 6648.00 | 7984.00 | Angola | Republic | José Eduardo dos Santos | 56 | AO |
| AIA | Anguilla | North America | Caribbean | 96.00 | NULL | 8000 | 76.1 | 63.20 | NULL | Anguilla | Dependent Territory of the UK | Elisabeth II | 62 | AI |
| ALB | Albania | Europe | Southern Europe | 28748.00 | 1912 | 3401200 | 71.6 | 3205.00 | 2500.00 | Shqipëria | Republic | Rexhep Mejdani | 34 | AL |
+------+-------------+---------------+---------------------------+-------------+-----------+------------+----------------+---------+---------+-----------------------+----------------------------------------------+--------------------------+---------+-------+
5 rows in set (0.007 sec)
MySQL [mytestdb]> CREATE TABLE `city` (
-> `ID` int NOT NULL AUTO_INCREMENT,
-> `Name` char(35) NOT NULL DEFAULT '',
-> `CountryCode` char(3) NOT NULL DEFAULT '',
-> `District` char(20) NOT NULL DEFAULT '',
-> `Population` int NOT NULL DEFAULT '0',
-> PRIMARY KEY (`ID`),
-> KEY `CountryCode` (`CountryCode`),
-> CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
-> );
Query OK, 0 rows affected (0.233 sec)
MySQL [mytestdb]> INSERT INTO `city` VALUES (1,'Kabul','AFG','Kabol',1780000);
Query OK, 1 row affected (0.044 sec)
MySQL [mytestdb]> INSERT INTO `city` VALUES (2,'Qandahar','AFG','Qandahar',237500);
Query OK, 1 row affected (0.010 sec)
MySQL [mytestdb]> INSERT INTO `city` VALUES (3,'Herat','AFG','Herat',186800);
Query OK, 1 row affected (0.058 sec)
MySQL [mytestdb]> INSERT INTO `city` VALUES (4,'Mazar-e-Sharif','AFG','Balkh',127800);
Query OK, 1 row affected (0.009 sec)
MySQL [mytestdb]> select * from city;
+----+----------------+-------------+----------+------------+
| ID | Name | CountryCode | District | Population |
+----+----------------+-------------+----------+------------+
| 1 | Kabul | AFG | Kabol | 1780000 |
| 2 | Qandahar | AFG | Qandahar | 237500 |
| 3 | Herat | AFG | Herat | 186800 |
| 4 | Mazar-e-Sharif | AFG | Balkh | 127800 |
+----+----------------+-------------+----------+------------+
4 rows in set (0.007 sec)
MySQL [mytestdb]> show tables;
+--------------------+
| Tables_in_mytestdb |
+--------------------+
| city |
| country |
+--------------------+
2 rows in set (0.072 sec)
967
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
