系列文章目录
第一章 oceanbase学习之docker方式部署
第二章 oceanbase学习之手动部署
第三章 oceanbase学习之迁移MySQL数据到oceanbase
第四章 oceanbase学习之查看oceanbase执行计划
前言
在上一节docker部署oceanbase单机的实验中遇到了不少问题，初始化的oceanbase占用资源过多，导致部分命令执行失败，因此本节内容采用手动方式部署，合理分配现有资源，做到知其然知其所以然。
一、服务器环境
本次实验采用单台服务器部署，服务器信息如下：
| 服务器IP | 系统 | cpu | 内存 | 磁盘 | 安装目录 |
|---|---|---|---|---|---|
| 10.40.204.170 | centos 7 | 8 | 16 | 170G | /oceanbase |
内核相关参数与上一节相似，为解决镜像使用obd启动时产生的两个waning，调整了相关参数
[root@oceanbase ~]# cat /etc/sysctl.conf | grep fs.
fs.file-max = 655350
fs.aio-max-nr=1048576
二、oceanbase相关角色说明
oceanbase相关角色说明
| 角色 | 说明 |
|---|---|
| OBD | 自动化部署软件 |
| OBSERVER | ob数据库 |
| OBPROXY | ob访问反向代理 |
| OBCLIENT | ob命令行客户断 |
安装包下载网址：
https://open.oceanbase.com/softwareCenter/community
三、安装oceanbase各软件包
[root@oceanbase oceanbase]# ls -lh
total 253M
drwxr-xr-x. 3 root root 17 Apr 27 16:52 ob
-rw-r--r--. 1 root root 174M Apr 27 08:55 obclient-2.0.1-2.el7.x86_64.rpm
-rw-r--r--. 1 root root 32M Apr 27 08:53 ob-deploy-1.3.3-11.el7.x86_64.rpm
-rw-r--r--. 1 root root 48M Apr 27 08:53 oceanbase-ce-3.1.3-10000292022032916.el7.x86_64.rpm
-rw-r--r--. 1 root root 155K Apr 27 08:52 oceanbase-ce-libs-3.1.3-10000292022032916.el7.x86_64.rpm
#安装oceanbase 到/oceanbase/ob目录下
[root@oceanbase oceanbase]# rpm -Uvh oceanbase-ce-3.1.3-10000292022032916.el7.x86_64.rpm --prefix /oceanbase/ob
error: Failed dependencies:
libmariadb.so.3()(64bit) is needed by oceanbase-ce-3.1.3-10000292022032916.el7.x86_64
libmariadb.so.3(libmysqlclient_18)(64bit) is needed by oceanbase-ce-3.1.3-10000292022032916.el7.x86_64
[root@oceanbase oceanbase]# rpm -Uvh oceanbase-ce-3.1.3-10000292022032916.el7.x86_64.rpm oceanbase-ce-libs-3.1.3-10000292022032916.el7.x86_64.rpm --prefix /oceanbase/ob
Preparing... ################################# [100%]
Updating / installing...
1:oceanbase-ce-libs-3.1.3-100002920warning: user admin does not exist - using root
warning: group admin does not exist - using root
warning: user admin does not exist - using root
warning: group admin does not exist - using root
warning: user admin does not exist - using root
warning: group admin does not exist - using root
warning: user admin does not exist - using root
warning: group admin does not exist - using root
warning: user admin does not exist - using root
warning: group admin does not exist - using root
warning: user admin does not exist - using root
warning: group admin does not exist - using root
################################# [ 50%]
2:oceanbase-ce-3.1.3-10000292022032warning: user admin does not exist - using root
warning: group admin does not exist - using root
warning: user admin does not exist - using root
warning: group admin does not exist - using root
warning: user admin does not exist - using root
warning: group admin does not exist - using root
warning: user admin does not exist - using root
warning: group admin does not exist - using root
warning: user admin does not exist - using root
warning: group admin does not exist - using root
warning: user admin does not exist - using root
warning: group admin does not exist - using root
warning: user admin does not exist - using root
warning: group admin does not exist - using root
################################# [100%]
warning: user admin does not exist - using root
warning: group admin does not exist - using root
warning: user admin does not exist - using root
warning: group admin does not exist - using root
warning: user admin does not exist - using root
warning: group admin does not exist - using root
warning: user admin does not exist - using root
warning: group admin does not exist - using root
warning: user admin does not exist - using root
warning: group admin does not exist - using root
warning: user admin does not exist - using root
warning: group admin does not exist - using root
warning: user admin does not exist - using root
warning: group admin does not exist - using root
#查看安装目录
[root@oceanbase oceanbase]# tree ob
ob
├── bin
│ ├── import_time_zone_info.py
│ └── observer
├── etc
│ ├── oceanbase_upgrade_dep.yml
│ ├── priv_checker.py
│ ├── timezone_V1.log
│ ├── upgrade_checker.py
│ ├── upgrade_cluster_health_checker.py
│ ├── upgrade_post_checker.py
│ ├── upgrade_post.py
│ ├── upgrade_pre.py
│ ├── upgrade_rolling_post.py
│ └── upgrade_rolling_pre.py
├── lib
│ ├── 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
└── var
├── cache
│ └── yum
│ └── x86_64
│ └── $releasever
│ └── base
│ ├── gen
│ └── packages
├── lib
│ ├── rpm
│ │ ├── Basenames
│ │ ├── Conflictname
│ │ ├── Dirnames
│ │ ├── Group
│ │ ├── Installtid
│ │ ├── Name
│ │ ├── Obsoletename
│ │ ├── Packages
│ │ ├── Providename
│ │ ├── Requirename
│ │ ├── Sha1header
│ │ ├── Sigmd5
│ │ └── Triggername
│ └── yum
│ ├── repos
│ │ └── x86_64
│ │ └── $releasever
│ │ └── base
│ └── yumdb
└── log
└── yum.log
20 directories, 31 files
部署过程出现warning，官方建议使用admin用户部署数据库，可再部署前创建admin用户与用户组。
安装完成后开始初始化数据目录与日志目录
| 总数据目录 | 日志目录 | 数据文件目录 |
|---|---|---|
| /oceanbase/store/obdemo | /oceanbase/store/redo/obdemo | /oceanbase/store/data/obdemo |
[root@oceanbase oceanbase]# mkdir -p store/{obdemo,redo/obdemo/{clog,ilog,slog,etc2},data/obdemo/{sstable,etc3}}
[root@oceanbase oceanbase]# for f in {clog,ilog,slog,etc2}; do ln -s /oceanbase/store/redo/obdemo/$f /oceanbase/store/obdemo/$f ; done
[root@oceanbase oceanbase]# for f in {sstable,etc3}; do ln -s /oceanbase/store/data/obdemo/$f /oceanbase/store/obdemo/$f; done
[root@oceanbase oceanbase]# tree store/
store/
├── data
│ └── obdemo
│ ├── etc3
│ └── sstable
├── obdemo
│ ├── clog -> /oceanbase/store/redo/obdemo/clog
│ ├── etc2 -> /oceanbase/store/redo/obdemo/etc2
│ ├── etc3 -> /oceanbase/store/data/obdemo/etc3
│ ├── ilog -> /oceanbase/store/redo/obdemo/ilog
│ ├── slog -> /oceanbase/store/redo/obdemo/slog
│ └── sstable -> /oceanbase/store/data/obdemo/sstable
└── redo
└── obdemo
├── clog
├── etc2
├── ilog
└── slog
17 directories, 0 files
生产环境要求数据文件目录与日志文件目录尽可能再两块独立的物理盘或逻辑盘上，以提升性能。
启动observer进程
#初始化环境变量
[root@oceanbase oceanbase]# echo 'export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/oceanbase/ob/lib' >> ~/.bash_profile
[root@oceanbase oceanbase]# . ~/.bash_profile
#启动observer
[root@oceanbase ob]# cd /oceanbase/ob && bin/observer -i ens192 -p 2881 -P 2882 -z zone1 -r '10.40.204.170:2882:2881' -d /oceanbase/store/obdemo -c 20220427 -n obdemo -o "memory_limit=8G,cache_wash_threshold=1G,__min_full_resource_pool_memory=268435456,system_memory=3G,memory_chunk_cache_size=128M,cpu_count=8,net_thread_count=4,datafile_size=10G,stack_size=1536K,config_additional_dir=/oceanbase/store/data/obdemo/etc3;/oceanbase/store/redo/obdemo/etc2" -d /oceanbase/store/obdemo
bin/observer -i ens192 -p 2881 -P 2882 -z zone1 -r 10.40.204.170:2882:2881 -d /oceanbase/store/obdemo -c 20220427 -n obdemo -o memory_limit=8G,cache_wash_threshold=1G,__min_full_resource_pool_memory=268435456,system_memory=3G,memory_chunk_cache_size=128M,cpu_count=8,net_thread_count=4,datafile_size=10G,stack_size=1536K,config_additional_dir=/oceanbase/store/data/obdemo/etc3;/oceanbase/store/redo/obdemo/etc2 -d /oceanbase/store/obdemo
devname: ens192
mysql port: 2881
rpc port: 2882
zone: zone1
rs list: 10.40.204.170:2882:2881
data_dir: /oceanbase/store/obdemo
cluster id: 20220427
appname: obdemo
optstr: memory_limit=8G,cache_wash_threshold=1G,__min_full_resource_pool_memory=268435456,system_memory=3G,memory_chunk_cache_size=128M,cpu_count=8,net_thread_count=4,datafile_size=10G,stack_size=1536K,config_additional_dir=/oceanbase/store/data/obdemo/etc3;/oceanbase/store/redo/obdemo/etc2
data_dir: /oceanbase/store/obdemo
[root@oceanbase ob]# netstat -tnlp
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:22 0.0.0.0:* LISTEN 28699/sshd
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 6299/master
tcp 0 0 0.0.0.0:2881 0.0.0.0:* LISTEN 1082/bin/observer
tcp 0 0 0.0.0.0:2882 0.0.0.0:* LISTEN 1082/bin/observer
tcp6 0 0 :::22 :::* LISTEN 28699/sshd
tcp6 0 0 ::1:25 :::* LISTEN 6299/master
#启动成功后可以看到在数据目录下生成block_file文件，文件大小10G
[root@oceanbase sstable]# ls -lh
total 10G
-rw-r--r--. 1 root root 10G Apr 27 17:18 block_file
[root@oceanbase sstable]# pwd
/oceanbase/store/obdemo/sstable
#通过MySQL client登陆root密码默认为空
[root@oceanbase software]# mysql -h 10.40.204.170 -u root -P 2881 -p -c -A
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on '10.40.204.170' (111)
#登陆失败通过排查发现observer自动退出，观察observer日志
[root@oceanbase log]# cd /oceanbase/ob/log && tail -100f observer.log
#根据报错发现提示打开文件数过多，查看本机文件数限制发现默认1024未修改
[root@oceanbase log]# ulimit -n
1024
#临时修改
[root@oceanbase log]# ulimit -n 655360
[root@oceanbase log]# ulimit -n
655360
#永久生效
[root@oceanbase log]# echo "* soft nofile 655360
> * hard nofile 655360
> * soft nproc 655360
> * hard nproc 655360
> * soft core unlimited
> * hard core unlimited
> * soft stack unlimited
> * hard stack unlimited" >> /etc/security/limits.conf
#重新启动observer并通过mysql client连接
[root@oceanbase ob]# mysql -h 10.40.204.170 -u root -P 2881 -p -c -A
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3221225472
Server version: 5.7.25 OceanBase 3.1.3 (r10000292022032916-3d79cacb37012cf61b7cb8faf00d9a6bb152bcd1) (Built Mar 29 2022 08:20:39)
Copyright (c) 2009-2021 Percona LLC and/or its affiliates
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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>
#通过obclient登陆observer
[root@oceanbase software]# obclient -P2881 -uroot -h 127.1
Welcome to the OceanBase. Commands end with ; or \g.
Your MySQL connection id is 3221225476
Server version: 5.7.25 OceanBase 3.1.3 (r10000292022032916-3d79cacb37012cf61b7cb8faf00d9a6bb152bcd1) (Built Mar 29 2022 08:20:39)
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 databases;
ERROR 1146 (42S02): Table 'oceanbase.__all_database' doesn't exist
MySQL [(none)]> set session ob_query_timeout=1000000000; alter system bootstrap ZONE 'zone1' SERVER '10.40.204.170:2882';
Query OK, 0 rows affected (0.000 sec)
ERROR 4015 (HY000): System error
observer启动后，虽然可以成功登陆但无法查看数据库内容，也无法进行参数设置，通过排查日志/oceanbase/ob/log/observer.log内容，判定为资源不足，对服务器进行升级后数据库恢复正常。
集群自举
[admin@observer1 ~]$ mysql -h 10.40.204.170 -u root -P 2881 -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; alter system bootstrap ZONE 'zone1' SERVER '10.40.204.170:2882';
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (30.40 sec)
#数据库部署完成后可以创建新的租户
MySQL [(none)]> alter resource unit sys_unit_config min_cpu=2;
Query OK, 0 rows affected (0.02 sec)
MySQL [(none)]> CREATE resource pool my_pool unit = 'S1C1G', unit_num = 1;
Query OK, 0 rows affected (0.04 sec)
MySQL [(none)]> 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 (2.97 sec)
四、安装OBPROXY各软件包
由于机子有限，obproxy与observer部署在同一台服务器
#安装obproxy
[root@oceanbase software]# rpm -Uvh obproxy-ce-3.2.3-2.el7.x86_64.rpm
Preparing... ################################# [100%]
Updating / installing...
1:obproxy-ce-3.2.3-2.el7 ################################# [100%]
#启动obproxy
[root@oceanbase software]# su - admin
-bash-4.2$ cd ~/obproxy-3.2.3/ && bin/obproxy -r "10.40.204.170:2881" -p 2883 -o "enable_strict_kernel_release=false,enable_cluster_checkout=false,enable_metadb_used=false" -c obdemo
bin/obproxy -r 10.40.204.170:2881 -p 2883 -o enable_strict_kernel_release=false,enable_cluster_checkout=false,enable_metadb_used=false -c obdemo
rs list: 10.40.204.170:2881
listen port: 2883
optstr: enable_strict_kernel_release=false,enable_cluster_checkout=false,enable_metadb_used=false
cluster_name: obdemo
-bash-4.2$ netstat -tnlp
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
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:22 0.0.0.0:* LISTEN -
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN -
tcp 0 0 0.0.0.0:2881 0.0.0.0:* LISTEN -
tcp 0 0 0.0.0.0:2882 0.0.0.0:* LISTEN -
tcp 0 0 0.0.0.0:2883 0.0.0.0:* LISTEN 13373/bin/obproxy
tcp 0 0 0.0.0.0:2884 0.0.0.0:* LISTEN 13373/bin/obproxy
tcp6 0 0 :::22 :::* LISTEN -
tcp6 0 0 ::1:25 :::* LISTEN -
#通过proxy登陆数据库
-bash-4.2$ mysql -h 10.40.204.170 -uroot@proxysys -P 2883 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.25
Copyright (c) 2009-2021 Percona LLC and/or its affiliates
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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> 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)
#修改 OBPROXY 用户密码
mysql> alter proxyconfig set obproxy_sys_password = 'wPhGddup' ;
Query OK, 0 rows affected (0.00 sec)
#验证obproxy是否部署成功
[root@oceanbase software]# mysql -h10.40.204.170 -uroot@sys#obdemo -P2883 -p4S9wDbSr -c -A oceanbase
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 [oceanbase]>
五、创建业务账户及业务表
[root@oceanbase software]# mysql -h10.40.204.170 -uroot@sys#obdemo -P2883 -p4S9wDbSr -c -A oceanbase
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 [oceanbase]> show databases;
+--------------------+
| Database |
+--------------------+
| oceanbase |
| information_schema |
| mysql |
| test |
+--------------------+
4 rows in set (0.002 sec)
#删除并创建数据库
MySQL [oceanbase]> drop database test;
Query OK, 0 rows affected (0.017 sec)
MySQL [(none)]> create database test;
Query OK, 1 row affected (0.015 sec)
#新建业务账户
MySQL [oceanbase]> create user 'test' identified by 'test';
Query OK, 0 rows affected (0.019 sec)
MySQL [oceanbase]> grant all on test.* to 'test' with grant option;
Query OK, 0 rows affected (0.010 sec)
#通过新的业务账户创建表
[root@cd87ac2364dd init_sql]# obclient -h 127.1 -utest@obmysql -P2881 -ptest
Welcome to the OceanBase. Commands end with ; or \g.
Your MySQL connection id is 3221488300
Server version: 5.7.25 OceanBase 3.1.3 (r10000292022032916-3d79cacb37012cf61b7cb8faf00d9a6bb152bcd1) (Built Mar 29 2022 08:20:39)
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 databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
2 rows in set (0.002 sec)
MySQL [(none)]> use test
Database changed
MySQL [test]> create table t1 (id int);
Query OK, 0 rows affected (0.047 sec)
MySQL [test]> insert into t1 values (10);
Query OK, 1 row affected (0.008 sec)
MySQL [test]> commit;
Query OK, 0 rows affected (0.000 sec)
MySQL [test]> select * from t1;
+------+
| id |
+------+
| 10 |
+------+
1 row in set (0.002 sec)
1430
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
