📢📢📢📣📣📣
哈喽!大家好,我是【IT邦德】,江湖人称jeames007,10余年DBA及大数据工作经验
一位上进心十足的【大数据领域博主】!😜😜😜
中国DBA联盟(ACDU)成员,目前服务于工业互联网
擅长主流Oracle、MySQL、PG、高斯及Greenplum运维开发,备份恢复,安装迁移,性能优化、故障应急处理等。
✨ 如果有对【数据库】感兴趣的【小可爱】,欢迎关注【IT邦德】💞💞💞
❤️❤️❤️感谢各位大可爱小可爱!❤️❤️❤️
文章目录
- 1.OceanBase 发展历程
- 📣 2.Docker安装
- ✨ 2.1 安装依赖包
- ✨ 2.2 在线安装
- 2.3 启动 Docker
- 📣 3.环境要求
- 📣 4.部署OB
- ✨ 4.1 下载镜像
- ✨ 4.2 创建容器
- ✨ 4.3 集群管理
- 📣 5.使用OceanBase
- ✨ 5.1 登陆OB
- ✨ 5.2 创建资源规格
- ✨ 5.3 创建资源池
- ✨ 5.4 创建租户
- ✨ 5.5 建库建表
- 6.报错处理
- 7.总结
本文通过OceanBase Docker容器,快速的体验 OceanBase4.2版本的自动化部署过程,以及了解 OceanBase集群安装成功后的目录特点和使用方法
1.OceanBase 发展历程
📣 2.Docker安装
✨ 2.1 安装依赖包
yum install -y yum-utils device-mapper-persistent-data lvm2
sudo yum-config-manager
–add-repo
https://download.docker.com/linux/centos/docker-ce.repo
✨ 2.2 在线安装
[root@centos7 ~] yum -y install docker-ce docker-ce-cli containerd.io
2.3 启动 Docker
[root@centos7 ~]# systemctl enable docker
[root@centos7 ~]# systemctl start docker
[root@centos7 ~]# systemctl status docker
[root@centos7 ~]# docker ps --查看容器
[root@centos7 ~]# docker version --查看版本
[root@centos7 ~]# docker info --查看版本
📣 3.环境要求
1.有笔记本或服务器,内存至少12G 。
2.操作系统不限,能安装 Docker 环境即可。
Docker 官方镜像:
https://hub.docker.com/r/obpilot/oceanbase-ce
📣 4.部署OB
✨ 4.1 下载镜像
搜索 OceanBase 数据库相关镜像
[root@centos79 ~]# docker search oceanbase
拉取 OceanBase 数据库最新镜像
[root@centos79 ~]# docker pull oceanbase/oceanbase-ce
[root@centos79 ~]# docker images | grep oceanbase
oceanbase/oceanbase-ce latest 92e357628ff3 7 days ago 652MB
✨ 4.2 创建容器
1.新容器创建
根据当前容器部署最大规格实例
docker run -p 2881:2881 --name oceanbase-ce -e MINI_MODE=0 -d oceanbase/oceanbase-ce
部署 mini 的独立实例
docker run -p 2881:2881 --name obstandalone -e MINI_MODE=1 -d oceanbase/oceanbase-ce
–以下命令执行后,需要等待3到5分钟
[root@centos79 ~]# docker run -p 2881:2881 --name oceanbase-ce -e MINI_MODE=0 -d oceanbase/oceanbase-ce
f21bf6621e1e6d404eb0dc8e945f6f9ab5a5b37e99b10a9058328ec1a44195ee
执行以下命令,如果返回 boot success!,则表示启动成功
[root@centos79 ~]# docker logs oceanbase-ce | tail -1
boot success!
2.进入容器
[root@centos79 ~]# docker exec -it oceanbase-ce bash
[root@f21bf6621e1e ~]# cat /etc/redhat-release
Anolis OS release 8.8
[root@f21bf6621e1e ~]# ls
boot dest ob obagent pkg store
[root@f21bf6621e1e ~]# obclient -uroot@sys -h127.1 -P2881
3.连接OceanBase数据库实例
–使用 root 用户登录集群的 sys 租户
docker exec -it oceanbase-ce ob-mysql sys
–使用 root 用户登录集群的 root 租户
docker exec -it oceanbase-ce ob-mysql root
–使用 root 用户登录集群的 test 租户
docker exec -it oceanbase-ce ob-mysql test
[root@centos79 ~]# docker exec -it oceanbase-ce ob-mysql sys
login as root@sys
Command is: obclient -h127.1 -uroot@sys -A -Doceanbase -P2881
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 3221506698
Server version: OceanBase_CE 4.2.2.0 (r100010012024022719-c984fe7cb7a4cef85a40323a0d073f0c9b7b8235) (Built Feb 27 2024 19:20:54)
Copyright © 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
obclient [oceanbase]>
–修改用户
ALTER USER root IDENTIFIED BY ‘123456’;
✨ 4.3 集群管理
-- 查看集群列表
[root@centos79 ~]# docker exec -it oceanbase-ce bash
[root@f21bf6621e1e ~]# obd cluster list
+------------------------------------------------------------+
| Cluster List |
+-----------+------------------------------+-----------------+
| Name | Configuration Path | Status (Cached) |
+-----------+------------------------------+-----------------+
| obcluster | /root/.obd/cluster/obcluster | running |
+-----------+------------------------------+-----------------+
Trace ID: 2703e982-e070-11ee-8e4b-0242ac110002
If you want to view detailed obd logs, please run: obd display-trace 2703e982-e070-11ee-8e4b-0242ac110002配置文件:/root/.obd/cluster/obcluster/config.yaml
[root@f21bf6621e1e ~]# ls /root/.obd/cluster/obcluster/config.yaml
/root/.obd/cluster/obcluster/config.yaml
📣 5.使用OceanBase
✨ 5.1 登陆OB
1.使用 root 用户登录到集群的 sys 租户[root@f21bf6621e1e ~]# obclient -uroot@sys -h127.1 -P2881 -p123456 -A
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 3221545291
Server version: OceanBase_CE 4.2.2.0 (r100010012024022719-c984fe7cb7a4cef85a40323a0d073f0c9b7b8235) (Built Feb 27 2024 19:20:54)Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.obclient [(none)]> 2.进入 oceanbase 数据库。
obclient [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| LBACSYS |
| mysql |
| oceanbase |
| ocs |
| ORAAUDITOR |
| SYS |
| test |
+--------------------+
8 rows in set (0.006 sec)obclient [(none)]> USE oceanbase;
Database changed
✨ 5.2 创建资源规格
资源规格是对 CPU、内存、磁盘空间、IOPS 等资源项进行的定义。1.通过 DBA_OB_UNIT_CONFIGS 视图,获取已有的资源规格信息。
obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_UNIT_CONFIGS\G
*************************** 1. row ***************************
UNIT_CONFIG_ID: 1NAME: sys_unit_configCREATE_TIME: 2024-03-12 20:51:45.967417MODIFY_TIME: 2024-03-12 20:51:45.967417MAX_CPU: 3MIN_CPU: 3MEMORY_SIZE: 2147483648LOG_DISK_SIZE: 2147483648MAX_IOPS: 9223372036854775807MIN_IOPS: 9223372036854775807IOPS_WEIGHT: 3
*************************** 2. row ***************************
UNIT_CONFIG_ID: 1001NAME: test_unitCREATE_TIME: 2024-03-12 20:52:12.652689MODIFY_TIME: 2024-03-12 20:52:12.652689MAX_CPU: 13MIN_CPU: 13MEMORY_SIZE: 3221225472LOG_DISK_SIZE: 3221225472MAX_IOPS: 9223372036854775807MIN_IOPS: 9223372036854775807IOPS_WEIGHT: 13
2 rows in set (0.000 sec)2.创建一个名称为 S1_unit_config 的资源规格,其资源配置为 CPU 为 1 核,内存 4G,日志盘空间 6G。
obclient [oceanbase]> CREATE RESOURCE UNIT S1_unit_configMEMORY_SIZE = '4G',MAX_CPU = 1, MIN_CPU = 1,LOG_DISK_SIZE = '6G',MAX_IOPS = 10000, MIN_IOPS = 10000, IOPS_WEIGHT=1;3.查询 DBA_OB_UNIT_CONFIGS 视图,确认资源规格创建成功。
obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_UNIT_CONFIGS WHERE NAME = 'S1_unit_config'\G
✨ 5.3 创建资源池
创建资源规格后,可以在创建资源池时指定资源规格,从而使用相应大小的资源单元,并最终分配给相应的租户。
1.使用 root 用户登录到集群的 sys 租户。
[root@f21bf6621e1e ~]# obclient -uroot@sys -h127.1 -P2881 -p123456 -A
obclient [oceanbase]> USE oceanbase;2.通过 DBA_OB_RESOURCE_POOLS 视图,获取资源池的配置信息。
obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_RESOURCE_POOLS\G
*************************** 1. row ***************************
RESOURCE_POOL_ID: 1NAME: sys_poolTENANT_ID: 1CREATE_TIME: 2024-03-12 20:51:45.971544MODIFY_TIME: 2024-03-12 20:51:45.982456UNIT_COUNT: 1UNIT_CONFIG_ID: 1ZONE_LIST: zone1REPLICA_TYPE: FULL
*************************** 2. row ***************************
RESOURCE_POOL_ID: 1001NAME: test_poolTENANT_ID: 1002CREATE_TIME: 2024-03-12 20:52:12.661303MODIFY_TIME: 2024-03-12 20:52:12.687355UNIT_COUNT: 1UNIT_CONFIG_ID: 1001ZONE_LIST: zone1REPLICA_TYPE: FULL
2 rows in set (0.002 sec)DROP TENANT test_pool;3.创建一个名为 mq_pool_01 的资源池,在 zone1、zone2 里各创建 1 个 Unit,每个 Unit 的资源规格为 S1_unit_config。
可以查询 oceanbase.DBA_OB_ZONES 视图进行确认Zone
obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_ZONES;
+-------+----------------------------+----------------------------+--------+-----+------------+-----------+
| ZONE | CREATE_TIME | MODIFY_TIME | STATUS | IDC | REGION | TYPE |
+-------+----------------------------+----------------------------+--------+-----+------------+-----------+
| zone1 | 2024-03-12 16:59:12.856024 | 2024-03-12 16:59:12.856024 | ACTIVE | | sys_region | ReadWrite |
+-------+----------------------------+----------------------------+--------+-----+------------+-----------+
1 row in set (0.023 sec)obclient [(none)]> ALTER SYSTEM ADD ZONE zone2 REGION 'hefei',ZONE_TYPE='ReadWrite';obclient [oceanbase]> CREATE RESOURCE POOL mq_pool_01 UNIT='S1_unit_config', UNIT_NUM=1, ZONE_LIST=('zone2');
✨ 5.4 创建租户
资源池创建后,您可以根据业务需要,将资源池分配给租户。OceanBase 数据库支持两种类型的租户,MySQL 兼容模式和 Oracle 兼容模式。创建租户时,您需要指定租户的类型。租户创建后,租户类型无法修改,因此创建租户前请规划好您的租户类型。1.使用 root 用户登录到集群的 sys 租户。
[root@f21bf6621e1e ~]# obclient -uroot@sys -h127.1 -P2881 -A -p1234562.进入 oceanbase 数据库。
obclient [(none)]> USE oceanbase;3.通过 DBA_OB_TENANTS 视图,查看所有的租户信息
obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_TENANTS\G
*************************** 1. row ***************************TENANT_ID: 1TENANT_NAME: sysTENANT_TYPE: SYSCREATE_TIME: 2024-03-12 22:36:36.751973MODIFY_TIME: 2024-03-12 22:36:36.751973PRIMARY_ZONE: RANDOMLOCALITY: FULL{1}@zone1PREVIOUS_LOCALITY: NULLCOMPATIBILITY_MODE: MYSQLSTATUS: NORMALIN_RECYCLEBIN: NOLOCKED: NOTENANT_ROLE: PRIMARYSWITCHOVER_STATUS: NORMALSWITCHOVER_EPOCH: 0SYNC_SCN: NULLREPLAYABLE_SCN: NULLREADABLE_SCN: NULLRECOVERY_UNTIL_SCN: NULLLOG_MODE: NOARCHIVELOG
ARBITRATION_SERVICE_STATUS: DISABLEDUNIT_NUM: 1COMPATIBLE: 4.2.2.0MAX_LS_ID: 1
*************************** 2. row ***************************TENANT_ID: 1001TENANT_NAME: META$1002TENANT_TYPE: METACREATE_TIME: 2024-03-12 22:37:04.034661MODIFY_TIME: 2024-03-12 22:37:41.629058PRIMARY_ZONE: RANDOMLOCALITY: FULL{1}@zone1PREVIOUS_LOCALITY: NULLCOMPATIBILITY_MODE: MYSQLSTATUS: NORMALIN_RECYCLEBIN: NOLOCKED: NOTENANT_ROLE: PRIMARYSWITCHOVER_STATUS: NORMALSWITCHOVER_EPOCH: 0SYNC_SCN: NULLREPLAYABLE_SCN: NULLREADABLE_SCN: NULLRECOVERY_UNTIL_SCN: NULLLOG_MODE: NOARCHIVELOG
ARBITRATION_SERVICE_STATUS: DISABLEDUNIT_NUM: 1COMPATIBLE: 4.2.2.0MAX_LS_ID: 1
*************************** 3. row ***************************TENANT_ID: 1002TENANT_NAME: testTENANT_TYPE: USERCREATE_TIME: 2024-03-12 22:37:04.033335MODIFY_TIME: 2024-03-12 22:37:41.783779PRIMARY_ZONE: RANDOMLOCALITY: FULL{1}@zone1PREVIOUS_LOCALITY: NULLCOMPATIBILITY_MODE: MYSQLSTATUS: NORMALIN_RECYCLEBIN: NOLOCKED: NOTENANT_ROLE: PRIMARYSWITCHOVER_STATUS: NORMALSWITCHOVER_EPOCH: 0SYNC_SCN: 1710254307122913004REPLAYABLE_SCN: 1710254307122913004READABLE_SCN: 1710254307122913004RECOVERY_UNTIL_SCN: 4611686018427387903LOG_MODE: NOARCHIVELOG
ARBITRATION_SERVICE_STATUS: DISABLEDUNIT_NUM: 1COMPATIBLE: 4.2.2.0MAX_LS_ID: 1001
3 rows in set (0.026 sec)4.通过 CREATE TENANT 语句,创建租户
obclient [oceanbase]> drop TENANT META$1002;obclient [oceanbase]>
CREATE TENANT IF NOT EXISTS mq_t1
PRIMARY_ZONE='zone1',
RESOURCE_POOL_LIST=('test_pool')
set OB_TCP_INVITED_NODES='%';
✨ 5.5 建库建表
默认管理员用户(MySQL 模式为 root,Oracle 模式为 sys)的密码为空,您需要及时修改管理员用户的密码。
MySQL 兼容模式--登录 mq_t1 租户的 root 用户。
obclient -uroot@mq_t1 -h127.1 -P2881--执行以下语句修改 root 用户的密码
obclient [(none)]> ALTER USER root IDENTIFIED BY '123456';--退出后重新登陆
obclient -uroot@mq_t1 -h127.1 -P2881 -p123456--创建数据库并指定字符集
CREATE DATABASE testdb DEFAULT CHARACTER SET UTF8;
obclient [(none)]> use testdb--建表
CREATE TABLE course_tb(
course_id int(10) NOT NULL,
course_name char(10) NOT NULL,
course_datetime char(30) NOT NULL);INSERT INTO course_tb VALUES(1, 'Python', '2021-12-1 19:00-21:00');
INSERT INTO course_tb VALUES(2, 'SQL', '2021-12-2 19:00-21:00');
INSERT INTO course_tb VALUES(3, 'R', '2021-12-3 19:00-21:00');obclient [testdb]> desc course_tb-> ;
+-----------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+----------+------+-----+---------+-------+
| course_id | int(10) | NO | | NULL | |
| course_name | char(10) | NO | | NULL | |
| course_datetime | char(30) | NO | | NULL | |
+-----------------+----------+------+-----+---------+-------+obclient [testdb]> select * from course_tb;
+-----------+-------------+-----------------------+
| course_id | course_name | course_datetime |
+-----------+-------------+-----------------------+
| 1 | Python | 2021-12-1 19:00-21:00 |
| 2 | SQL | 2021-12-2 19:00-21:00 |
| 3 | R | 2021-12-3 19:00-21:00 |
+-----------+-------------+-----------------------+
6.报错处理
1.创建资源池的报错
ERROR 4656 (HY000): resource pool unit num is bigger than zone server count
//说明:集群只有一个zone,且zone中只有一台observer ,所以无法创建 unit_num大于1的资源池;
向集群新增 OBServer xxx.xx.xxx.xx1。
obclient> ALTER SYSTEM ADD SERVER ‘127.0.0.1:2883’ ZONE ‘zone2’;
obclient [oceanbase]> select * from GV$OB_SERVERS
2.ERROR 4012 (HY000): Timeout报错
sql响应超时时间设置太短了,所以当sql执行需要一定时间时就会报错4012
obclient [oceanbase]> show variables like '%timeout%';
+---------------------+------------------+
| Variable_name | Value |
+---------------------+------------------+
| connect_timeout | 10 |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| ob_pl_block_timeout | 3216672000000000 |
| ob_query_timeout | 10000000 |
| ob_trx_idle_timeout | 86400000000 |
| ob_trx_lock_timeout | -1 |
| ob_trx_timeout | 86400000000 |
| wait_timeout | 28800 |
+---------------------+------------------+
11 rows in set (0.015 sec)# 租户下设置,防止超时
obclient> set global ob_query_timeout=86400000000000000;
obclient> set global ob_trx_timeout=86400000000000000;
ob_query_timeout用于设置查询超时时间,单位是微秒;
ob_trx_timeout用于设置事务超时时间,单位为微秒。
两个参数有GLOBAL和SESSION两种级别:
global参数的话是对全局session生效,但是不包括本次链接,重新登录生效;
如果不加global,就是仅对本次session生效,退出登录还是原来的值。
3.登陆OB如果遇到以下报错,重启容器即可
ERROR 2014 (HY000): Commands out of sync; you can’t run this command now
7.总结
通过 OceanBase Docker 容器,快速的体验 OceanBase 的 自动化部署过程