手动部署 OceanBase 三副本集群（在同一个节点）
Tags： OceanBase同一个主机同一个节点多副本集群
👉 本文共约1312个字，系统预计阅读时间或需5分钟。
- OceanBase Docker安装体验：https://www.xmmup.com/oceanbase-dockeranzhuangtiyan.html
- 手动部署 OceanBase 单副本集群：https://www.xmmup.com/shoudongbushu-oceanbase-danfubenjiqun.html
说明
相关连接：
https://open.oceanbase.com/blog/10900159
https://open.oceanbase.com/answer/detail?id=13700696
练习目的
本次练习目的掌握 OceanBase 集群的手动部署技能，理解进程的相关目录、集群初始化、OBProxy和 OB 集群关系等。
练习条件
有笔记本或服务器，内存至少12G 。
练习内容
请记录并分享下列内容：
- （必选）手动部署一个 OB 单副本集群，包括一个 OBProxy 节点。
- （必选）创建一个业务租户、一个业务数据库，以及一些表等。
- （可选）如果单台服务器内存有32G，或者有三台服务器，改为部署一个 OB 三副本集群，包括一个 OBProxy 节点。
- （可选）如果有三台服务器并且服务器内存有 32 G，可以单服务器内启动 2 个节点，实现 1-1-1 扩容到 2-2-2 。
软件下载
下载地址：https://open.oceanbase.com/softwareCenter/community
从https://www.oceanbase.com/softwareCenter/community下载3个软件，分别是obproxy-3.2.0-1.el7.x86_64.rpm、oceanbase-ce-3.1.1-4.el7.x86_64.rpm、oceanbase-ce-libs-3.1.1-4.el7.x86_64.rpm。
1 2 3 | wget https://mdn.alipayobjects.com/ob_portal/afts/file/A*4VfBRbz_ncIAAAAAAAAAAAAADmF2AQ?af_fileName=oceanbase-ce-3.1.2-10000392021123010.el7.x86_64.rpm wget https://mdn.alipayobjects.com/ob_portal/afts/file/A*obcATbEfH_QAAAAAAAAAAAAADmF2AQ?af_fileName=oceanbase-ce-libs-3.1.2-10000392021123010.el7.x86_64.rpm wget https://mdn.alipayobjects.com/ob_portal/afts/file/A*mMziSJneiZcAAAAAAAAAAAAADmF2AQ?af_fileName=obproxy-3.2.0-1.el7.x86_64.rpm |
手动部署一个 OB 三副本集群（在同一个节点）
环境准备
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | docker rm -f lhrob312 docker run -d --name lhrob312 -h lhrob312 \ -p 12881-12889:2881-2889 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ --privileged=true lhrbest/lhrcentos76:8.5 \ /usr/sbin/init docker exec -it lhrob312 bash -- 创建用户 useradd admin echo "admin:lhr" | chpasswd chown -R admin:admin /home/admin echo "admin ALL=(ALL) NOPASSWD: ALL" >> /etc/sudoers -- 安装OB软件 rpm -ivh *.rpm echo "export PATH=$PATH:/home/admin/oceanbase/bin:/home/admin/obproxy-3.2.0/bin" >> /home/admin/.bash_profile echo "export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/home/admin/oceanbase/lib/" >> /home/admin/.bash_profile source /home/admin/.bash_profile -- 安装OB和MySQL客户端 yum install -y yum-utils yum-config-manager --add-repo https://mirrors.aliyun.com/oceanbase/OceanBase.repo yum install -y obclient mariadb mariadb-libs mariadb-devel |
安装过程：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 | [root@lhrob312 soft]# ll -h total 55M -rw-r--r-- 1 root root 7.9M Nov 5 09:10 obproxy-3.2.0-1.el7.x86_64.rpm -rw-r--r-- 1 root root 47M Dec 31 17:57 oceanbase-ce-3.1.2-10000392021123010.el7.x86_64.rpm -rw-r--r-- 1 root root 156K Dec 31 19:40 oceanbase-ce-libs-3.1.2-10000392021123010.el7.x86_64.rpm [root@lhrob312 soft]# useradd admin [root@lhrob312 soft]# echo "admin:lhr" | chpasswd [root@lhrob312 soft]# [root@lhrob312 soft]# chown -R admin:admin /home/admin [root@lhrob312 soft]# echo "admin ALL=(ALL) NOPASSWD: ALL" >> /etc/sudoers [root@lhrob312 soft]# [root@lhrob312 soft]# [root@lhrob312 soft]# rpm -ivh *.rpm warning: obproxy-3.2.0-1.el7.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID e9b4a7aa: NOKEY Preparing... ################################# [100%] Updating / installing... 1:oceanbase-ce-libs-3.1.2-100003920################################# [ 33%] 2:oceanbase-ce-3.1.2-10000392021123################################# [ 67%] 3:obproxy-3.2.0-1.el7 ################################# [100%] [root@lhrob312 soft]# echo "export PATH=$PATH:/home/admin/oceanbase/bin:/home/admin/obproxy-3.2.0/bin" >> /home/admin/.bash_profile [root@lhrob312 soft]# echo "export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/home/admin/oceanbase/lib/" >> /home/admin/.bash_profile [root@lhrob312 soft]# source /home/admin/.bash_profile [root@lhrob312 soft]# [root@lhrob312 soft]# rpm -ql oceanbase-ce /home/admin/oceanbase/bin /home/admin/oceanbase/bin/import_time_zone_info.py /home/admin/oceanbase/bin/observer /home/admin/oceanbase/etc /home/admin/oceanbase/etc/oceanbase_upgrade_dep.yml /home/admin/oceanbase/etc/priv_checker.py /home/admin/oceanbase/etc/timezone_V1.log /home/admin/oceanbase/etc/upgrade_checker.py /home/admin/oceanbase/etc/upgrade_cluster_health_checker.py /home/admin/oceanbase/etc/upgrade_post.py /home/admin/oceanbase/etc/upgrade_post_checker.py /home/admin/oceanbase/etc/upgrade_pre.py /home/admin/oceanbase/etc/upgrade_rolling_post.py /home/admin/oceanbase/etc/upgrade_rolling_pre.py [root@lhrob312 soft]# rpm -ql oceanbase-ce-libs /home/admin/oceanbase/lib /home/admin/oceanbase/lib/libaio.so /home/admin/oceanbase/lib/libaio.so.1 /home/admin/oceanbase/lib/libaio.so.1.0.1 /home/admin/oceanbase/lib/libmariadb.so /home/admin/oceanbase/lib/libmariadb.so.3 [root@lhrob312 soft]# rpm -ql obproxy /home/admin/obproxy-3.2.0/bin /home/admin/obproxy-3.2.0/bin/obproxy /home/admin/obproxy-3.2.0/bin/obproxyd.sh [root@lhrob312 ~]# chown -R admin:admin /home/admin |
配置启动OB并初始化
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | su - admin rm -rf /home/admin/oceanbase/store1 /home/admin/oceanbase/store2 /home/admin/oceanbase/store3 mkdir -p /home/admin/oceanbase/store1/sstable mkdir -p /home/admin/oceanbase/store2/sstable mkdir -p /home/admin/oceanbase/store3/sstable cd /home/admin/oceanbase/store1 && /home/admin/oceanbase/bin/observer -r "127.0.0.1:2882:2881;127.0.0.1:3882:3881;127.0.0.1:4882:4881" -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" -z zone1 -p 2881 -P 2882 -c 20220108 -n ob1 -d /home/admin/oceanbase/store1 -i lo -l ERROR cd /home/admin/oceanbase/store2 && /home/admin/oceanbase/bin/observer -r "127.0.0.1:2882:2881;127.0.0.1:3882:3881;127.0.0.1:4882:4881" -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" -z zone2 -p 3881 -P 3882 -c 20220108 -n ob2 -d /home/admin/oceanbase/store2 -i lo -l ERROR cd /home/admin/oceanbase/store3 && /home/admin/oceanbase/bin/observer -r "127.0.0.1:2882:2881;127.0.0.1:3882:3881;127.0.0.1:4882:4881" -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" -z zone3 -p 4881 -P 4882 -c 20220108 -n ob3 -d /home/admin/oceanbase/store3 -i lo -l ERROR #以上命令中指定的参数作用可参考obd自动部署过程yaml文件的说明，请移步 https://gitee.com/oceanbase/obdeploy/tree/master/example # 查看进程信息 ps -ef |grep observer netstat -tulnp | grep 88 -- bootstrap集群(初始化集群) mysql -h127.1 -uroot -P2881 -p -c -A # 默认空密码 set session ob_query_timeout=1000000000; alter system bootstrap ZONE 'zone1' SERVER '127.0.0.1:2882',ZONE 'zone2' SERVER '127.0.0.1:3882',ZONE 'zone3' SERVER '127.0.0.1:4882'; # 修改root密码 alter user root identified by 'lhr'; create user proxyro identified by 'lhr'; grant select on *.* to proxyro; create database lhrdb; -- 查询 mysql -h127.1 -uroot -P2881 -plhr -c -A -e "show databases;" mysql -h127.1 -uroot -P3881 -plhr -c -A -e "show databases;" mysql -h127.1 -uroot -P4881 -plhr -c -A -e "show databases;" |
过程：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 | [root@lhrob312 ~]# su - admin Last login: Sat Jan 8 17:32:26 CST 2022 on pts/0 [admin@lhrob312 ~]$ [admin@lhrob312 ~]$ rm -rf /home/admin/oceanbase/store1 /home/admin/oceanbase/store2 /home/admin/oceanbase/store3 [admin@lhrob312 ~]$ mkdir -p /home/admin/oceanbase/store1/sstable [admin@lhrob312 ~]$ mkdir -p /home/admin/oceanbase/store2/sstable [admin@lhrob312 ~]$ mkdir -p /home/admin/oceanbase/store3/sstable [admin@lhrob312 ~]$ [admin@lhrob312 ~]$ cd /home/admin/oceanbase/store1 && /home/admin/oceanbase/bin/observer -r "127.0.0.1:2882:2881;127.0.0.1:3882:3881;127.0.0.1:4882:4881" -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" -z zone1 -p 2881 -P 2882 -c 20220108 -n ob1 -d /home/admin/oceanbase/store1 -i lo -l ERROR /home/admin/oceanbase/bin/observer -r 127.0.0.1:2882:2881;127.0.0.1:3882:3881;127.0.0.1:4882:4881 -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 -z zone1 -p 2881 -P 2882 -c 20220108 -n ob1 -d /home/admin/oceanbase/store1 -i lo -l ERROR rs list: 127.0.0.1:2882:2881;127.0.0.1:3882:3881;127.0.0.1:4882:4881 optstr: __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 zone: zone1 mysql port: 2881 rpc port: 2882 cluster id: 20220108 appname: ob1 data_dir: /home/admin/oceanbase/store1 devname: lo log level: ERROR [admin@lhrob312 store1]$ cd /home/admin/oceanbase/store2 && /home/admin/oceanbase/bin/observer -r "127.0.0.1:2882:2881;127.0.0.1:3882:3881;127.0.0.1:4882:4881" -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" -z zone2 -p 3881 -P 3882 -c 20220108 -n ob2 -d /home/admin/oceanbase/store2 -i lo -l ERROR /home/admin/oceanbase/bin/observer -r 127.0.0.1:2882:2881;127.0.0.1:3882:3881;127.0.0.1:4882:4881 -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 -z zone2 -p 3881 -P 3882 -c 20220108 -n ob2 -d /home/admin/oceanbase/store2 -i lo -l ERROR rs list: 127.0.0.1:2882:2881;127.0.0.1:3882:3881;127.0.0.1:4882:4881 optstr: __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 zone: zone2 mysql port: 3881 rpc port: 3882 cluster id: 20220108 appname: ob2 data_dir: /home/admin/oceanbase/store2 devname: lo log level: ERROR [admin@lhrob312 store2]$ cd /home/admin/oceanbase/store3 && /home/admin/oceanbase/bin/observer -r "127.0.0.1:2882:2881;127.0.0.1:3882:3881;127.0.0.1:4882:4881" -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" -z zone3 -p 4881 -P 4882 -c 20220108 -n ob3 -d /home/admin/oceanbase/store3 -i lo -l ERROR /home/admin/oceanbase/bin/observer -r 127.0.0.1:2882:2881;127.0.0.1:3882:3881;127.0.0.1:4882:4881 -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 -z zone3 -p 4881 -P 4882 -c 20220108 -n ob3 -d /home/admin/oceanbase/store3 -i lo -l ERROR rs list: 127.0.0.1:2882:2881;127.0.0.1:3882:3881;127.0.0.1:4882:4881 optstr: __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 zone: zone3 mysql port: 4881 rpc port: 4882 cluster id: 20220108 appname: ob3 data_dir: /home/admin/oceanbase/store3 devname: lo log level: ERROR [admin@lhrob312 store3]$ [admin@lhrob312 store3]$ ps -ef |grep observer netstat -tulnp | grep 288admin 6016 0 99 17:34 ? 00:00:14 /home/admin/oceanbase/bin/observer -r 127.0.0.1:2882:2881;127.0.0.1:3882:3881;127.0.0.1:4882:4881 -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 -z zone1 -p 2881 -P 2882 -c 20220108 -n ob1 -d /home/admin/oceanbase/store1 -i lo -l ERROR admin 6066 0 99 17:34 ? 00:00:13 /home/admin/oceanbase/bin/observer -r 127.0.0.1:2882:2881;127.0.0.1:3882:3881;127.0.0.1:4882:4881 -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 -z zone2 -p 3881 -P 3882 -c 20220108 -n ob2 -d /home/admin/oceanbase/store2 -i lo -l ERROR admin 6137 0 99 17:34 ? 00:00:12 /home/admin/oceanbase/bin/observer -r 127.0.0.1:2882:2881;127.0.0.1:3882:3881;127.0.0.1:4882:4881 -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 -z zone3 -p 4881 -P 4882 -c 20220108 -n ob3 -d /home/admin/oceanbase/store3 -i lo -l ERROR admin 7751 5933 0 17:34 pts/0 00:00:00 grep --color=auto observer [admin@lhrob312 store3]$ netstat -tulnp | grep 88 (Not all processes could be identified, non-owned process info will not be shown, you would have to be root to see it all.) tcp 0 0 0.0.0.0:3882 0.0.0.0:* LISTEN 6066/observer tcp 0 0 0.0.0.0:4881 0.0.0.0:* LISTEN 6137/observer tcp 0 0 0.0.0.0:4882 0.0.0.0:* LISTEN 6137/observer tcp 0 0 0.0.0.0:2881 0.0.0.0:* LISTEN 6016/observer tcp 0 0 0.0.0.0:2882 0.0.0.0:* LISTEN 6016/observer tcp 0 0 0.0.0.0:3881 0.0.0.0:* LISTEN 6066/observer [admin@lhrob312 store3]$ [admin@lhrob312 store3]$ mysql -h127.1 -uroot -P2881 -p -c -A Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 3221225472 Server version: 5.7.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> set session ob_query_timeout=1000000000; Query OK, 0 rows affected (0.01 sec) MySQL [(none)]> MySQL [(none)]> alter system bootstrap ZONE 'zone1' SERVER '127.0.0.1:2882',ZONE 'zone2' SERVER '127.0.0.1:3882',ZONE 'zone3' SERVER '127.0.0.1:4882'; Query OK, 0 rows affected (5 min 22.88 sec) MySQL [(none)]> show databases; +--------------------+ | Database | +--------------------+ | oceanbase | | information_schema | | mysql | | SYS | | LBACSYS | | ORAAUDITOR | | test | +--------------------+ 7 rows in set (0.10 sec) MySQL [(none)]> create database lhrdb; Query OK, 1 row affected (0.56 sec) MySQL [(none)]> exit Bye [admin@lhrob312 store3]$ mysql -h127.1 -uroot -P3881 -plhr -c -A -e "show databases;" +--------------------+ | Database | +--------------------+ | oceanbase | | information_schema | | mysql | | SYS | | LBACSYS | | ORAAUDITOR | | test | | lhrdb | +--------------------+ [admin@lhrob312 store3]$ mysql -h127.1 -uroot -P4881 -plhr -c -A -e "show databases;" +--------------------+ | Database | +--------------------+ | oceanbase | | information_schema | | mysql | | SYS | | LBACSYS | | ORAAUDITOR | | test | | lhrdb | +--------------------+ [admin@lhrob312 store3]$ mysql -h127.1 -uroot -P2881 -plhr -c -A -e "show databases;" +--------------------+ | Database | +--------------------+ | oceanbase | | information_schema | | mysql | | SYS | | LBACSYS | | ORAAUDITOR | | test | | lhrdb | +--------------------+ [admin@lhrob312 store3]$ mysql -h127.1 -uroot -P2881 -plhr -c -A Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 3221503548 Server version: 5.7.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29) 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)]> MySQL [(none)]> select * from oceanbase.__all_server; +----------------------------+----------------------------+-----------+----------+----+-------+------------+-----------------+--------+-----------------------+----------------------------------------------------------------------------------------+-----------+--------------------+--------------+----------------+-------------------+ | gmt_create | gmt_modified | svr_ip | svr_port | id | zone | inner_port | with_rootserver | status | block_migrate_in_time | build_version | stop_time | start_service_time | first_sessid | with_partition | last_offline_time | +----------------------------+----------------------------+-----------+----------+----+-------+------------+-----------------+--------+-----------------------+----------------------------------------------------------------------------------------+-----------+--------------------+--------------+----------------+-------------------+ | 2022-01-08 17:36:09.238917 | 2022-01-08 17:40:30.082117 | 127.0.0.1 | 2882 | 1 | zone1 | 2881 | 1 | active | 0 | 3.1.2_10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d(Dec 30 2021 02:47:29) | 0 | 1641634820217902 | 0 | 1 | 0 | | 2022-01-08 17:36:09.264192 | 2022-01-08 17:40:30.487088 | 127.0.0.1 | 3882 | 2 | zone2 | 3881 | 0 | active | 0 | 3.1.2_10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d(Dec 30 2021 02:47:29) | 0 | 1641634826295983 | 0 | 1 | 0 | | 2022-01-08 17:36:10.138615 | 2022-01-08 17:40:30.187890 | 127.0.0.1 | 4882 | 3 | zone3 | 4881 | 0 | active | 0 | 3.1.2_10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d(Dec 30 2021 02:47:29) | 0 | 1641634826513439 | 0 | 1 | 0 | +----------------------------+----------------------------+-----------+----------+----+-------+------------+-----------------+--------+-----------------------+----------------------------------------------------------------------------------------+-----------+--------------------+--------------+----------------+-------------------+ 3 rows in set (0.14 sec) |
配置obproxy
https://open.oceanbase.com/articles/1100243
obproxy跟OB集群通信是使用sys租户内的一个内部账户proxyro。这个账户需要创建。
obproxy启动后，默认用 root@proxysys 登录，密码为空。需要改密码（通过proxy参数obproxy_sys_password指定）。
obproxy启动后，还需要修改proxyro的密码（通过proxy参数observer_sys_password指定），设置为跟OB集群里创建的proxyro密码一致才能链接那个OB集群。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | -- 启动obproxy，其中-c 对应集群的名称，和前面的observer的启动参数对应。 cd /home/admin/obproxy-3.2.0/ && /home/admin/obproxy-3.2.0/bin/obproxy -r "127.0.0.1:2881" -p 2883 -o "enable_strict_kernel_release=false,enable_cluster_checkout=false,enable_metadb_used=false" -c 20220108 netstat -ntlp | grep obproxy netstat -ntlp | grep 88 ps -ef|grep ob -- 通过obproxy登录，初始密码为空 mysql -h127.1 -uroot@proxysys -P2883 -p alter proxyconfig set obproxy_sys_password='lhr'; alter proxyconfig set observer_sys_password='lhr'; show proxyconfig like '%sys_password%'; mysql -h127.1 -uroot@sys -P2883 -plhr -c -A oceanbase mysql -uroot@sys -plhr -h192.168.66.35 -P12883 select * from oceanbase.__all_server; show full processlist; |
执行过程：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 | [admin@lhrob312 ~]$ cd /home/admin/obproxy-3.2.0/ && /home/admin/obproxy-3.2.0/bin/obproxy -r "127.0.0.1:2881" -p 2883 -o "enable_strict_kernel_release=false,enable_cluster_checkout=false,enable_metadb_used=false" -c 20220108 /home/admin/obproxy-3.2.0/bin/obproxy -r 127.0.0.1:2881 -p 2883 -o enable_strict_kernel_release=false,enable_cluster_checkout=false,enable_metadb_used=false -c 20220108 rs list: 127.0.0.1:2881 listen port: 2883 optstr: enable_strict_kernel_release=false,enable_cluster_checkout=false,enable_metadb_used=false cluster_name: 20220108 [admin@lhrob312 obproxy-3.2.0]$ [admin@lhrob312 obproxy-3.2.0]$ netstat -ntlp | grep obproxy (Not all processes could be identified, non-owned process info will not be shown, you would have to be root to see it all.) tcp 0 0 0.0.0.0:2883 0.0.0.0:* LISTEN 8574/obproxy tcp 0 0 0.0.0.0:2884 0.0.0.0:* LISTEN 8574/obproxy [admin@lhrob312 obproxy-3.2.0]$ ps -ef|grep ob admin 6016 0 99 17:34 ? 00:34:34 /home/admin/oceanbase/bin/observer -r 127.0.0.1:2882:2881;127.0.0.1:3882:3881;127.0.0.1:4882:4881 -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 -z zone1 -p 2881 -P 2882 -c 20220108 -n ob1 -d /home/admin/oceanbase/store1 -i lo -l ERROR admin 6066 0 99 17:34 ? 00:30:42 /home/admin/oceanbase/bin/observer -r 127.0.0.1:2882:2881;127.0.0.1:3882:3881;127.0.0.1:4882:4881 -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 -z zone2 -p 3881 -P 3882 -c 20220108 -n ob2 -d /home/admin/oceanbase/store2 -i lo -l ERROR admin 6137 0 99 17:34 ? 00:30:38 /home/admin/oceanbase/bin/observer -r 127.0.0.1:2882:2881;127.0.0.1:3882:3881;127.0.0.1:4882:4881 -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 -z zone3 -p 4881 -P 4882 -c 20220108 -n ob3 -d /home/admin/oceanbase/store3 -i lo -l ERROR admin 8574 0 5 17:50 ? 00:00:01 /home/admin/obproxy-3.2.0/bin/obproxy -r 127.0.0.1:2881 -p 2883 -o enable_strict_kernel_release=false,enable_cluster_checkout=false,enable_metadb_used=false -c 20220108 admin 8627 5933 0 17:50 pts/0 00:00:00 grep --color=auto ob [admin@lhrob312 obproxy-3.2.0]$ netstat -ntlp | grep 88 (Not all processes could be identified, non-owned process info will not be shown, you would have to be root to see it all.) tcp 0 0 0.0.0.0:3882 0.0.0.0:* LISTEN 6066/observer tcp 0 0 0.0.0.0:4881 0.0.0.0:* LISTEN 6137/observer tcp 0 0 0.0.0.0:4882 0.0.0.0:* LISTEN 6137/observer tcp 0 0 0.0.0.0:2881 0.0.0.0:* LISTEN 6016/observer tcp 0 0 0.0.0.0:2882 0.0.0.0:* LISTEN 6016/observer tcp 0 0 0.0.0.0:2883 0.0.0.0:* LISTEN 8574/obproxy tcp 0 0 0.0.0.0:2884 0.0.0.0:* LISTEN 8574/obproxy tcp 0 0 0.0.0.0:3881 0.0.0.0:* LISTEN 6066/observer [admin@lhrob312 ~]$ mysql -h127.1 -uroot@proxysys -P2883 -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.6.25 Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> MySQL [(none)]> show proxyconfig like '%sys_password%'; +------------------------+-------+--------------------------------+-------------+---------------+ | name | value | info | need_reboot | visible_level | +------------------------+-------+--------------------------------+-------------+---------------+ | observer_sys_password1 | | password for observer sys user | false | SYS | | observer_sys_password | | password for observer sys user | false | SYS | | obproxy_sys_password | | password for obproxy sys user | false | SYS | +------------------------+-------+--------------------------------+-------------+---------------+ 3 rows in set (0.00 sec) MySQL [(none)]> alter proxyconfig set obproxy_sys_password='lhr'; Query OK, 0 rows affected (0.01 sec) MySQL [(none)]> alter proxyconfig set observer_sys_password='lhr'; Query OK, 0 rows affected (0.03 sec) MySQL [(none)]> show proxyconfig like '%sys_password%'; +------------------------+------------------------------------------+--------------------------------+-------------+---------------+ | name | value | info | need_reboot | visible_level | +------------------------+------------------------------------------+--------------------------------+-------------+---------------+ | observer_sys_password1 | | password for observer sys user | false | SYS | | observer_sys_password | 6095142f4b755fb18e0ca1edc3fa38fe0bdc78b9 | password for observer sys user | false | SYS | | obproxy_sys_password | 6095142f4b755fb18e0ca1edc3fa38fe0bdc78b9 | password for obproxy sys user | false | SYS | +------------------------+------------------------------------------+--------------------------------+-------------+---------------+ 3 rows in set (0.01 sec) [admin@lhrob312 ~]$ mysql -h127.1 -uroot@sys -P2883 -plhr -A oceanbase Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.6.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29) 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 * from oceanbase.__all_server; +----------------------------+----------------------------+-----------+----------+----+-------+------------+-----------------+--------+-----------------------+----------------------------------------------------------------------------------------+-----------+--------------------+--------------+----------------+-------------------+ | gmt_create | gmt_modified | svr_ip | svr_port | id | zone | inner_port | with_rootserver | status | block_migrate_in_time | build_version | stop_time | start_service_time | first_sessid | with_partition | last_offline_time | +----------------------------+----------------------------+-----------+----------+----+-------+------------+-----------------+--------+-----------------------+----------------------------------------------------------------------------------------+-----------+--------------------+--------------+----------------+-------------------+ | 2022-01-08 17:36:09.238917 | 2022-01-08 17:40:30.082117 | 127.0.0.1 | 2882 | 1 | zone1 | 2881 | 1 | active | 0 | 3.1.2_10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d(Dec 30 2021 02:47:29) | 0 | 1641634820217902 | 0 | 1 | 0 | | 2022-01-08 17:36:09.264192 | 2022-01-08 17:40:30.487088 | 127.0.0.1 | 3882 | 2 | zone2 | 3881 | 0 | active | 0 | 3.1.2_10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d(Dec 30 2021 02:47:29) | 0 | 1641634826295983 | 0 | 1 | 0 | | 2022-01-08 17:36:10.138615 | 2022-01-08 17:40:30.187890 | 127.0.0.1 | 4882 | 3 | zone3 | 4881 | 0 | active | 0 | 3.1.2_10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d(Dec 30 2021 02:47:29) | 0 | 1641634826513439 | 0 | 1 | 0 | +----------------------------+----------------------------+-----------+----------+----+-------+------------+-----------------+--------+-----------------------+----------------------------------------------------------------------------------------+-----------+--------------------+--------------+----------------+-------------------+ 3 rows in set (0.05 sec) |
登录ob数据库并创建租户
1 2 3 4 5 6 7 | mysql -uroot@sys -plhr -h192.168.66.35 -P12883 CREATE resource unit S4C1G max_cpu=4, min_cpu=4, max_memory='1G', min_memory='1G', max_iops=10000, min_iops=1000, max_session_num=1000000, max_disk_size='1024G'; CREATE resource pool my_pool unit = 'S4C1G', unit_num = 1; create tenant obmysql resource_pool_list=('my_pool'), primary_zone='RANDOM',comment 'mysql tenant/instance', charset='utf8' set ob_tcp_invited_nodes='%', ob_compatibility_mode='mysql'; -- 密码为空 mysql -uroot@obmysql -p -h192.168.66.35 -P12883 |
过程：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | C:\Users\lhrxxt>mysql -uroot@sys -plhr -h192.168.66.35 -P12883 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 262145 Server version: 5.6.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> show databases; +--------------------+ | Database | +--------------------+ | oceanbase | | information_schema | | mysql | | SYS | | LBACSYS | | ORAAUDITOR | | test | | lhrdb | +--------------------+ 8 rows in set (0.25 sec) MySQL [(none)]> MySQL [(none)]> select * from oceanbase.__all_server; +----------------------------+----------------------------+-----------+----------+----+-------+------------+-----------------+--------+-----------------------+----------------------------------------------------------------------------------------+-----------+--------------------+--------------+----------------+-------------------+ | gmt_create | gmt_modified | svr_ip | svr_port | id | zone | inner_port | with_rootserver | status | block_migrate_in_time | build_version | stop_time | start_service_time | first_sessid | with_partition | last_offline_time | +----------------------------+----------------------------+-----------+----------+----+-------+------------+-----------------+--------+-----------------------+----------------------------------------------------------------------------------------+-----------+--------------------+--------------+----------------+-------------------+ | 2022-01-08 17:36:09.238917 | 2022-01-08 17:40:30.082117 | 127.0.0.1 | 2882 | 1 | zone1 | 2881 | 1 | active | 0 | 3.1.2_10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d(Dec 30 2021 02:47:29) | 0 | 1641634820217902 | 0 | 1 | 0 | | 2022-01-08 17:36:09.264192 | 2022-01-08 17:40:30.487088 | 127.0.0.1 | 3882 | 2 | zone2 | 3881 | 0 | active | 0 | 3.1.2_10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d(Dec 30 2021 02:47:29) | 0 | 1641634826295983 | 0 | 1 | 0 | | 2022-01-08 17:36:10.138615 | 2022-01-08 17:40:30.187890 | 127.0.0.1 | 4882 | 3 | zone3 | 4881 | 0 | active | 0 | 3.1.2_10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d(Dec 30 2021 02:47:29) | 0 | 1641634826513439 | 0 | 1 | 0 | +----------------------------+----------------------------+-----------+----------+----+-------+------------+-----------------+--------+-----------------------+----------------------------------------------------------------------------------------+-----------+--------------------+--------------+----------------+-------------------+ 3 rows in set (0.07 sec) |
创建资源单元、资源池、租户
1 2 3 4 5 6 7 8 9 10 | MySQL [oceanbase]> CREATE resource unit S4C1G max_cpu=4, min_cpu=4, max_memory='1G', min_memory='1G', max_iops=10000, min_iops=1000, max_session_num=1000000, max_disk_size='1024G'; Query OK, 0 rows affected (0.006 sec) MySQL [oceanbase]> CREATE resource pool my_pool unit = 'S4C1G', unit_num = 1; Query OK, 0 rows affected (0.011 sec) MySQL [oceanbase]> create tenant obmysql resource_pool_list=('my_pool'), primary_zone='RANDOM',comment 'mysql tenant/instance', charset='utf8' set ob_tcp_invited_nodes='%', ob_compatibility_mode='mysql'; Query OK, 0 rows affected (37.21 sec) MySQL [oceanbase]> |
登录obmysql tenant并创建数据库及表等
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 | C:\Users\lhrxxt>mysql -uroot@obmysql -p -h192.168.66.35 -P12883 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 262147 Server version: 5.6.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> show databases; +--------------------+ | Database | +--------------------+ | oceanbase | | information_schema | | mysql | | test | +--------------------+ 4 rows in set (0.14 sec) MySQL [test]> create database lhrdb charset utf8mb4; Query OK, 1 row affected (0.168 sec) MySQL [test]> use lhrdb; Database changed MySQL [lhrdb]> source /tmp/mysql_employees.sql Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected, 1 warning (0.004 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.001 sec) ..... MySQL [lhrdb]> show tables; +-----------------+ | Tables_in_lhrdb | +-----------------+ | departments | | job_grades | | jobs | | late | | locations | +-----------------+ 5 rows in set (0.018 sec) MySQL [lhrdb]> select * from jobs; +------------+---------------------------------+------------+------------+ | job_id | job_title | min_salary | max_salary | +------------+---------------------------------+------------+------------+ | AC_ACCOUNT | Public Accountant | 4200 | 9000 | | AC_MGR | Accounting Manager | 8200 | 16000 | | AD_ASST | Administration Assistant | 3000 | 6000 | | AD_PRES | President | 20000 | 40000 | | AD_VP | Administration Vice President | 15000 | 30000 | | FI_ACCOUNT | Accountant | 4200 | 9000 | | FI_MGR | Finance Manager | 8200 | 16000 | | HR_REP | Human Resources Representative | 4000 | 9000 | | IT_PROG | Programmer | 4000 | 10000 | | MK_MAN | Marketing Manager | 9000 | 15000 | | MK_REP | Marketing Representative | 4000 | 9000 | | PR_REP | Public Relations Representative | 4500 | 10500 | | PU_CLERK | Purchasing Clerk | 2500 | 5500 | | PU_MAN | Purchasing Manager | 8000 | 15000 | | SA_MAN | Sales Manager | 10000 | 20000 | | SA_REP | Sales Representative | 6000 | 12000 | | SH_CLERK | Shipping Clerk | 2500 | 5500 | | ST_CLERK | Stock Clerk | 2000 | 5000 | | ST_MAN | Stock Manager | 5500 | 8500 | +------------+---------------------------------+------------+------------+ 19 rows in set (0.003 sec) |
本人提供Oracle、MySQL、PG等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
问题
搞不懂，为啥OB的CPU占用率总是这么高：
本实验结束！
