Oceanbase学习之---手工部署oceanbase集群
案例说明：
Oceanbase社区版，以手工方式部署单节点的集群。
一、系统主机环境
操作系统：
[kingbase@node203 bin]$ cat /etc/centos-release
CentOS Linux release 7.2.1511 (Core)
Tips：最少2核以上CPU
[root@node203 ~]# lscpu
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 8
Tips：最低12G以上内存
[root@node203 ~]# free -m
total used free shared buff/cache available
Mem: 13955 338 13280 9 337 12806
Swap: 3071 0 3071
创建和配置admin用户：（配置sudo免密）
[root@node203 ~]# cat /etc/sudoers|grep -i admin
# User_Alias ADMINS = jsmith, mikem
admin ALL=(ALL) NOPASSWD: ALL
二、oceanbase社区版安装部署
安装包下载网址：
https://open.oceanbase.com/softwareCenter/community
Oceanbase部署套件：
1、创建软件部署目录
[root@node203 ~]# cat mkdir.sh
mkdir -p /home/admin/oceanbase
mkdir -p /home/admin/oceanbase/audit
mkdir -p /home/admin/oceanbase/log
mkdir -p /home/admin/oceanbase/store/sstable
mkdir -p /home/admin/oceanbase/store/clog
mkdir -p /home/admin/oceanbase/store/ilog
mkdir -p /home/admin/oceanbase/store/slog
chown -Rh admin.admin /home/admin/oceanbase
# 创建部署目录
[root@node203 ~]# sh mkdir.sh
[root@node203 ~]# ls -lh /home/admin/oceanbase
total 0
drwxr-xr-x 2 admin admin 6 Aug 28 19:07 audit
drwxr-xr-x 2 admin admin 6 Aug 28 19:07 log
drwxr-xr-x 6 admin admin 53 Aug 28 19:07 store
2、oceanbase安装组件
[root@node203 ~]# ls /home/soft/Oceanbase/ -lh
total 3.6G
-rw-r--r-- 1 root root 12M Aug 28 18:00 obclient-2.2.2-1.el7.x86_64.rpm
-rw-r--r-- 1 root root 60M Aug 28 18:00 ob-deploy-2.2.0-2.el7.x86_64.rpm
-rw-r--r-- 1 root root 114M Aug 28 18:00 obproxy-ce-4.2.0.0-7.el7.x86_64.rpm
-rw-r--r-- 1 root root 83M Aug 28 18:00 oceanbase-ce-4.2.0.0-100010022023081817.el7.x86_64.rpm
-rw-r--r-- 1 root root 59M Aug 28 18:00 oceanbase-ce-devel-3.1.5-100010012023060910.el7.x86_64.rpm
-rw-r--r-- 1 root root 144K Aug 28 18:00 oceanbase-ce-libs-4.2.0.0-100010022023081817.el7.x86_64.rpm
3、安装oceanbase组件
[admin@node203 Oceanbase]$ sudo rpm -ivh oceanbase-ce-libs-4.2.0.0-100010022023081817.el7.x86_64.rpm
Preparing... ################################# [100%]
Updating / installing...
1:oceanbase-ce-libs-4.2.0.0-1000100################################# [100%]
[admin@node203 Oceanbase]$ sudo rpm -ivh oceanbase-ce-devel-3.1.5-100010012023060910.el7.x86_64.rpm
Preparing... ################################# [100%]
Updating / installing...
1:oceanbase-ce-devel-3.1.5-10001001################################# [100%]
[admin@node203 Oceanbase]$ sudo rpm -ivh oceanbase-ce-4.2.0.0-100010022023081817.el7.x86_64.rpm
Preparing... ################################# [100%]
Updating / installing...
1:oceanbase-ce-4.2.0.0-100010022023################################# [100%]
[admin@node203 Oceanbase]$ sudo rpm -ivh obproxy-ce-4.2.0.0-7.el7.x86_64.rpm
Preparing... ################################# [100%]
Updating / installing...
1:obproxy-ce-4.2.0.0-7.el7 ################################# [100%]
[admin@node203 Oceanbase]$ sudo rpm -ivh obclient-2.2.2-1.el7.x86_64.rpm
Preparing... ################################# [100%]
Updating / installing...
1:obclient-2.2.2-1.el7 ################################# [100%]
# 查看已部署的组件
[admin@node203 oceanbase]$ rpm -qa |grep ocean
oceanbase-ce-4.2.0.0-100010022023081817.el7.x86_64
oceanbase-ce-devel-3.1.5-100010012023060910.el7.x86_64
oceanbase-ce-libs-4.2.0.0-100010022023081817.el7.x86_64
4、配置oceanbase运行环境变量
[admin@node203 Oceanbase]$ echo 'export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:~/oceanbase/lib' >> ~/.bash_profile
5、查看oceanbase部署后目录
[admin@node203 oceanbase]$ pwd
/home/admin/oceanbase
[admin@node203 oceanbase]$ ls -lh
total 8.0K
drwxr-xr-x 2 admin admin 4.0K Aug 28 19:13 admin
drwxr-xr-x 2 admin admin 6 Aug 28 19:07 audit
drwxr-sr-x 2 admin admin 52 Aug 28 19:13 bin
drwxr-sr-x 2 admin admin 4.0K Aug 28 19:13 etc
drwxr-sr-x 2 admin admin 104 Aug 28 19:13 lib
drwxr-xr-x 2 admin admin 6 Aug 28 19:07 log
drwxr-xr-x 6 admin admin 53 Aug 28 19:07 store
[admin@node203 ~]$ ls -lh obproxy-4.2.0.0/
total 0
drwxr-sr-x 2 admin admin 38 Aug 28 19:15 bin
drwxr-xr-x 2 admin admin 27 Aug 28 19:15 lib
三、启动observer及集群服务
1、启动observer服务
Tips：
本机IP地址：192.168.1.203，observer端口：2881、2882，obproxy端口：2883、2884，集群ID：99，集群名：obdemo。其他参数为限制集群的资源使用大小(本机资源有限)。
[admin@node203 ~]$ cd ~/oceanbase && bin/observer -i enp0s3 -p 2881 -P 2882 -z zone1 -d ~/oceanbase/store -r '192.168.1.203:2882:2881' -c 99 -n obdemo -o "memory_limit=4G,cache_wash_threshold=1G,__min_full_resource_pool_memory=1073741824,system_memory=1G,memory_chunk_cache_size=128M,cpu_count=2,net_thread_count=4,datafile_size=5G,stack_size=1536K"
bin/observer -i enp0s3 -p 2881 -P 2882 -z zone1 -d /home/admin/oceanbase/store -r 192.168.1.203:2882:2881 -c 99 -n obdemo -o memory_limit=4G,cache_wash_threshold=1G,__min_full_resource_pool_memory=1073741824,system_memory=1G,memory_chunk_cache_size=128M,cpu_count=2,net_thread_count=4,datafile_size=5G,stack_size=1536K
devname: enp0s3
mysql port: 2881
rpc port: 2882
zone: zone1
data_dir: /home/admin/oceanbase/store
rs list: 192.168.1.203:2882:2881
cluster id: 99
appname: obdemo
optstr: memory_limit=4G,cache_wash_threshold=1G,__min_full_resource_pool_memory=1073741824,system_memory=1G,memory_chunk_cache_size=128M,cpu_count=2,net_thread_count=4,datafile_size=5G,stack_size=1536K
[admin@node203 oceanbase]$ netstat -an |grep 288
# 查看服务通讯端口
[admin@node203 oceanbase]$ netstat -an |grep 288
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 192.168.1.203:51762 192.168.1.203:2882 ESTABLISHED
.........
2、通过client连接访问（用户root，密码为空）
[admin@node203 oceanbase]$ obclient -h192.168.1.203 -uroot -P2881 -p -c -A
Enter password:
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 3221225472
Server version: OceanBase_CE 4.2.0.0 (r100010022023081817-0bdf1c0c5674e88c5ae9a8d0ae4f8077465d7fae) (Built Aug 18 2023 17:32:49)
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)]>
3、指定Zone和Server配置，开始集群初始化
obclient [(none)]> alter system bootstrap ZONE 'zone1' SERVER '192.168.1.203:2882';
Query OK, 0 rows affected (49.336 sec)
4、重新登录查看oceanbase库并修改root密码
[admin@node203 oceanbase]$ obclient -h192.168.1.203 -uroot@sys -P2881 -p -c -A oceanbase
Enter password:
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 3221487617
Server version: OceanBase_CE 4.2.0.0 (r100010022023081817-0bdf1c0c5674e88c5ae9a8d0ae4f8077465d7fae) (Built Aug 18 2023 17:32:49)
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 [oceanbase]> alter user root identified by 'rootPwD999';
Query OK, 0 rows affected (0.023 sec)
四、配置启动obproxy服务
Tips：
-r 参数中指定observer的地址和端口，多个用逗号分隔， -p 指定端口，-c 指定 dbproxy 集群名。
1、启动obproxy服务
[admin@node203 ~]$ cd ~/obproxy-4.2.0.0/ && bin/obproxy -r "192.168.1.203:2881" -p 2883 -o "enable_strict_kernel_release=false,enable_cluster_checkout=false,enable_metadb_used=false" -c obdemo
bin/obproxy -r 192.168.1.203:2881 -p 2883 -o enable_strict_kernel_release=false,enable_cluster_checkout=false,enable_metadb_used=false -c obdemo
rs list: 192.168.1.203:2881
listen port: 2883
optstr: enable_strict_kernel_release=false,enable_cluster_checkout=false,enable_metadb_used=false
cluster_name: obdemo
# obproxy服务端口为2883
[admin@node203 obproxy-4.2.0.0]$ netstat -an |grep 288
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
tcp 0 0 0.0.0.0:2884 0.0.0.0:* LISTEN
2、 通过obclient登录dbproxy，用户名为root@proxysys，并加上--proxy-mode参数
[admin@node203 obproxy-4.2.0.0]$ obclient -h 192.168.1.203 -uroot@proxysys -P 2883 -p --proxy-mode
Enter password:
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 2
Server version:
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)]>
3、修改root密码，以及连接observer的账号proxyro的密码
obclient [(none)]> alter proxyconfig set obproxy_sys_password='proxySyS888';
Query OK, 0 rows affected (0.004 sec)
obclient [(none)]> alter proxyconfig set observer_sys_password='proxyRo999';
Query OK, 0 rows affected (0.003 sec)
4、登录observer，创建proxyro用户并授权oceanbase库的select权限
[admin@node203 oceanbase]$ obclient -h192.168.1.203 -uroot@sys -P2881 -p -c -A oceanbase
Enter password:
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 3221487617
Server version: OceanBase_CE 4.2.0.0 (r100010022023081817-0bdf1c0c5674e88c5ae9a8d0ae4f8077465d7fae) (Built Aug 18 2023 17:32:49)
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 [oceanbase]> alter user root identified by 'rootPwD999';
Query OK, 0 rows affected (0.023 sec)
obclient [oceanbase]> grant select on oceanbase.* to proxyro identified by 'proxyRo999';
Query OK, 0 rows affected (0.072 sec)
五、创建租户及分配资源
1、创建资源单元、资源池和租户
[admin@node203 obproxy-4.2.0.0]$ obclient -h 192.168.1.203 -uroot@sys#obdemo -P 2883 -prootPwD999 -c -A oceanbase
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 5
Server version: OceanBase_CE 4.2.0.0 (r100010022023081817-0bdf1c0c5674e88c5ae9a8d0ae4f8077465d7fae) (Built Aug 18 2023 17:32:49)
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 [oceanbase]>
# 创建资源单元unit
obclient [oceanbase]> create resource unit ut1 max_cpu 1,memory_size '2G',max_iops 1024;
Query OK, 0 rows affected (0.008 sec)
# 创建资源池pool
obclient [oceanbase]> create resource pool p1 unit 'ut1',unit_num 1;
Query OK, 0 rows affected (0.007 sec)
#创建租户
obclient [oceanbase]> create tenant ob_mysql resource_pool_list=('p1'), primary_zone='RANDOM',comment 'mysql tenant/instance',charset='utf8' set ob_tcp_invited_nodes='%', ob_compatibility_mode='mysql';
Query OK, 0 rows affected (26.621 sec)
2、查看资源和租户信息
obclient [oceanbase]> select * from oceanbase.__all_unit_config;
+----------------------------+----------------------------+----------------+-----------------+---------+---------+-------------+---------------+---------------------+---------------------+-------------+
| gmt_create | gmt_modified | unit_config_id | name | max_cpu | min_cpu | memory_size | log_disk_size | max_iops | min_iops | iops_weight |
+----------------------------+----------------------------+----------------+-----------------+---------+---------+-------------+---------------+---------------------+---------------------+-------------+
| 2023-08-30 19:51:53.922536 | 2023-08-30 19:51:53.922536 | 1 | sys_unit_config | 1 | 1 | 1073741824 | 2147483648 | 9223372036854775807 | 9223372036854775807 | 1 |
| 2023-08-31 10:49:41.558756 | 2023-08-31 10:49:41.558756 | 1004 | unit2 | 4 | 2 | 2147483648 | 2147483648 | 1024 | 1024 | 0 |
| 2023-08-31 10:56:40.377225 | 2023-08-31 10:56:40.377225 | 1007 | ut1 | 1 | 1 | 2147483648 | 6442450944 | 1024 | 1024 | 0 |
+----------------------------+----------------------------+----------------+-----------------+---------+---------+-------------+---------------+---------------------+---------------------+-------------+
3 rows in set (0.002 sec)
obclient [oceanbase]> select svr_ip,svr_port,zone,round((cpu_capacity_max-cpu_assigned_max),2) 'cpu_free_num',cpu_capacity_max 'cpu_total_num',round((mem_capacity-mem_assigned)/1024/1024/1024,2) 'mem_free_GB', round(memory_limit/1024/1024/1024,2) 'mem_total_GB' from gv$ob_servers;
+---------------+----------+-------+--------------+---------------+-------------+--------------+
| svr_ip | svr_port | zone | cpu_free_num | cpu_total_num | mem_free_GB | mem_total_GB |
+---------------+----------+-------+--------------+---------------+-------------+--------------+
| 192.168.1.203 | 2882 | zone1 | 0.00 | 2 | 0.00 | 4.00 |
+---------------+----------+-------+--------------+---------------+-------------+--------------+
1 row in set (0.003 sec)
obclient [oceanbase]> SELECT * FROM DBA_OB_TENANTS;
+-----------+-------------+-------------+----------------------------+----------------------------+--------------+---------------+-------------------+--------------------+--------+---------------+--------+-------------+-------------------+------------------+---------------------+---------------------+---------------------+---------------------+--------------+----------------------------+----------+------------+-----------+
| TENANT_ID | TENANT_NAME | TENANT_TYPE | CREATE_TIME | MODIFY_TIME | PRIMARY_ZONE | LOCALITY | PREVIOUS_LOCALITY | COMPATIBILITY_MODE | STATUS | IN_RECYCLEBIN | LOCKED | TENANT_ROLE | SWITCHOVER_STATUS | SWITCHOVER_EPOCH | SYNC_SCN | REPLAYABLE_SCN | READABLE_SCN | RECOVERY_UNTIL_SCN | LOG_MODE | ARBITRATION_SERVICE_STATUS | UNIT_NUM | COMPATIBLE | MAX_LS_ID |
+-----------+-------------+-------------+----------------------------+----------------------------+--------------+---------------+-------------------+--------------------+--------+---------------+--------+-------------+-------------------+------------------+---------------------+---------------------+---------------------+---------------------+--------------+----------------------------+----------+------------+-----------+
| 1 | sys | SYS | 2023-08-30 19:51:53.958981 | 2023-08-30 19:51:53.958981 | RANDOM | FULL{1}@zone1 | NULL | MYSQL | NORMAL | NO | NO | PRIMARY | NORMAL | 0 | NULL | NULL | NULL | NULL | NOARCHIVELOG | DISABLED | 1 | 4.2.0.0 | 1 |
| 1007 | META$1008 | META | 2023-08-31 10:57:01.246930 | 2023-08-31 10:57:20.034752 | RANDOM | FULL{1}@zone1 | NULL | MYSQL | NORMAL | NO | NO | PRIMARY | NORMAL | 0 | NULL | NULL | NULL | NULL | NOARCHIVELOG | DISABLED | 1 | 4.2.0.0 | 1 |
| 1008 | ob_mysql | USER | 2023-08-31 10:57:01.248275 | 2023-08-31 10:57:20.079812 | RANDOM | FULL{1}@zone1 | NULL | MYSQL | NORMAL | NO | NO | PRIMARY | NORMAL | 0 | 1693450718773930268 | 1693450718773930268 | 1693450718773930268 | 4611686018427387903 | NOARCHIVELOG | DISABLED | 1 | 4.2.0.0 | 1001 |
+-----------+-------------+-------------+----------------------------+----------------------------+--------------+---------------+-------------------+--------------------+--------+---------------+--------+-------------+-------------------+------------------+---------------------+---------------------+---------------------+---------------------+--------------+----------------------------+----------+------------+-----------+
3 rows in set (0.048 sec)
六、创建业务数据
1、用租户连接数据库
[admin@node203 obproxy-4.2.0.0]$ obclient -h127.0.0.1 -P2883 -uroot@ob_mysql -Doceanbase
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 48
Server version: OceanBase_CE 4.2.0.0 (r100010022023081817-0bdf1c0c5674e88c5ae9a8d0ae4f8077465d7fae) (Built Aug 18 2023 17:32:49)
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 [oceanbase]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| oceanbase |
| test |
+--------------------+
4 rows in set (0.002 sec)
2、创建业务库及表和数据
# 创建数据库
obclient [oceanbase]> create database prod;
Query OK, 1 row affected (0.033 sec)
obclient [oceanbase]> use prod;
Database changed
# 创建表和数据
obclient [prod]> create table t1 (id int ,name varchar(20));
Query OK, 0 rows affected (0.059 sec)
obclient [prod]> insert into t1 values (10,'tom'),(20,'jerry'),(30,'rose');
Query OK, 3 rows affected (0.027 sec)
Records: 3 Duplicates: 0 Warnings: 0
obclient [prod]> select * from t1;
+------+-------+
| id | name |
+------+-------+
| 10 | tom |
| 20 | jerry |
| 30 | rose |
+------+-------+
3 rows in set (0.002 sec)
obclient [prod]> alter table t1 add constraint pk_id primary key(id);
Query OK, 0 rows affected (0.701 sec)
obclient [prod]> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.006 sec)
obclient [prod]> show indexes from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
| t1 | 0 | PRIMARY | 1 | id | A | NULL | NULL | NULL | | BTREE | available | | YES | NULL |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
1 row in set (0.003 sec)
# 查看查询执行计划
obclient [prod]> explain select * from t1 where id=10;
+-----------------------------------------------------------+
| Query Plan |
+-----------------------------------------------------------+
| ========================================= |
| |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| |
| ----------------------------------------- |
| |0 |TABLE GET|t1 |1 |3 | |
| ========================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([t1.id], [t1.name]), filter(nil), rowset=256 |
| access([t1.id], [t1.name]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([t1.id]), range[10 ; 10], |
| range_cond([t1.id = 10]) |
+-----------------------------------------------------------+
12 rows in set (0.003 sec)
七、查看SQL执行计划
1、创建业务数据
obclient [prod]> create table emp( empno int primary key ,ename varchar(10),sal int , deptno int);
Query OK, 0 rows affected (0.038 sec)
obclient [prod]> create table dept (deptno int primary key ,dname varchar(10), location varchar(10));
Query OK, 0 rows affected (0.035 sec)
obclient [prod]> insert into dept values (10,'caiwu','beijing'),(20,'shichang','shanghai'),(30,'jishu','guangzhou');
Query OK, 3 rows affected (0.009 sec)
Records: 3 Duplicates: 0 Warnings: 0
obclient [prod]> insert into emp values(1001,'tom',3000,10),(1002,'jerry',5000,20),(1003,'rose',3000,30);
Query OK, 3 rows affected (0.003 sec)
Records: 3 Duplicates: 0 Warnings: 0
obclient [prod]> select * from emp;
+-------+-------+------+--------+
| empno | ename | sal | deptno |
+-------+-------+------+--------+
| 1001 | tom | 3000 | 10 |
| 1002 | jerry | 5000 | 20 |
| 1003 | rose | 3000 | 30 |
+-------+-------+------+--------+
3 rows in set (0.002 sec)
obclient [prod]> select * from dept;
+--------+----------+-----------+
| deptno | dname | location |
+--------+----------+-----------+
| 10 | caiwu | beijing |
| 20 | shichang | shanghai |
| 30 | jishu | guangzhou |
+--------+----------+-----------+
3 rows in set (0.004 sec)
2、查看SQL执行计划
obclient [prod]> explain select empno,ename,dname from emp ,dept
-> where emp.deptno=dept.deptno and emp.deptno=10;
+------------------------------------------------------------------------------------+
| Query Plan |
+------------------------------------------------------------------------------------+
| =========================================================== |
| |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| |
| ----------------------------------------------------------- |
| |0 |NESTED-LOOP JOIN CARTESIAN | |1 |7 | |
| |1 |├─TABLE FULL SCAN |emp |1 |4 | |
| |2 |└─MATERIAL | |1 |3 | |
| |3 | └─TABLE GET |dept|1 |3 | |
| =========================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([emp.empno], [emp.ename], [dept.dname]), filter(nil), rowset=256 |
| conds(nil), nl_params_(nil), use_batch=false |
| 1 - output([emp.empno], [emp.ename]), filter([emp.deptno = 10]), rowset=256 |
| access([emp.empno], [emp.deptno], [emp.ename]), partitions(p0) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([emp.empno]), range(MIN ; MAX)always true |
| 2 - output([dept.dname]), filter(nil), rowset=256 |
| 3 - output([dept.dname]), filter(nil), rowset=256 |
| access([dept.dname]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([dept.deptno]), range[10 ; 10], |
| range_cond([10 = dept.deptno]) |
+------------------------------------------------------------------------------------+
22 rows in set (0.007 sec)
八、MySQL迁移到Oceanbase
1、MySQL数据库业务数据
mysql> use prod;
Database changed
mysql> create table emp( empno int primary key ,ename varchar(10),sal int , deptno int);
Query OK, 0 rows affected (0.03 sec)
mysql> create table dept (deptno int primary key ,dname varchar(10), location varchar(10));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into dept values (10,'caiwu','beijing'),(20,'shichang','shanghai'),(30,'jishu','guangzhou');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into emp values(1001,'tom',3000,10),(1002,'jerry',5000,20),(1003,'rose',3000,30);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from emp;
+-------+-------+------+--------+
| empno | ename | sal | deptno |
+-------+-------+------+--------+
| 1001 | tom | 3000 | 10 |
| 1002 | jerry | 5000 | 20 |
| 1003 | rose | 3000 | 30 |
+-------+-------+------+--------+
3 rows in set (0.00 sec)
mysql> select * from dept;
+--------+----------+-----------+
| deptno | dname | location |
+--------+----------+-----------+
| 10 | caiwu | beijing |
| 20 | shichang | shanghai |
| 30 | jishu | guangzhou |
+--------+----------+-----------+
3 rows in set (0.00 sec)
# 业务表结构信息
mysql> show create table emp\G
*************************** 1. row ***************************
Table: emp
Create Table: CREATE TABLE `emp` (
`empno` int NOT NULL,
`ename` varchar(10) DEFAULT NULL,
`sal` int DEFAULT NULL,
`deptno` int DEFAULT NULL,
PRIMARY KEY (`empno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> show create table dept\G
*************************** 1. row ***************************
Table: dept
Create Table: CREATE TABLE `dept` (
`deptno` int NOT NULL,
`dname` varchar(10) DEFAULT NULL,
`location` varchar(10) DEFAULT NULL,
PRIMARY KEY (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
2、通过mysqldump导出业务数据
[root@node202 bin]# ./mysqldump -uroot -p --databases prod > ~/prod.sql
Enter password:
3、查看导出信息
[root@node202 bin]# more ~/prod.sql
-- MySQL dump 10.13 Distrib 8.0.23, for Linux (x86_64)
--
-- Host: localhost Database: prod
-- ------------------------------------------------------
-- Server version 8.0.23
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Current Database: `prod`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `prod` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTIO
N='N' */;
USE `prod`;
--
-- Table structure for table `dept`
--
DROP TABLE IF EXISTS `dept`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `dept` (
`deptno` int NOT NULL,
`dname` varchar(10) DEFAULT NULL,
`location` varchar(10) DEFAULT NULL,
PRIMARY KEY (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `dept`
--
LOCK TABLES `dept` WRITE;
/*!40000 ALTER TABLE `dept` DISABLE KEYS */;
INSERT INTO `dept` VALUES (10,'caiwu','beijing'),(20,'shichang','shanghai'),(30,'jishu','guangzhou');
/*!40000 ALTER TABLE `dept` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `emp`
--
DROP TABLE IF EXISTS `emp`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `emp` (
`empno` int NOT NULL,
`ename` varchar(10) DEFAULT NULL,
`sal` int DEFAULT NULL,
`deptno` int DEFAULT NULL,
PRIMARY KEY (`empno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `emp`
--
LOCK TABLES `emp` WRITE;
/*!40000 ALTER TABLE `emp` DISABLE KEYS */;
INSERT INTO `emp` VALUES (1001,'tom',3000,10),(1002,'jerry',5000,20),(1003,'rose',3000,30);
/*!40000 ALTER TABLE `emp` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2023-08-31 19:17:49
3、在Oceanbase节点执行数据导入
obclient [test]> use prod;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
obclient [prod]> show tables;
+----------------+
| Tables_in_prod |
+----------------+
| t1 |
+----------------+
1 row in set (0.002 sec)
obclient [prod]> source /home/admin/prod.sql
Query OK, 0 rows affected (0.000 sec)
......
Query OK, 0 rows affected (0.000 sec)
ERROR 1007 (HY000): Can't create database 'prod'; database exists
Database changed
Query OK, 0 rows affected (0.000 sec)
.......
# 导入完成查看表信息
obclient [prod]> show tables;
+----------------+
| Tables_in_prod |
+----------------+
| dept |
| emp |
| t1 |
+----------------+
3 rows in set (0.002 sec)
obclient [prod]> select * from emp;
+-------+-------+------+--------+
| empno | ename | sal | deptno |
+-------+-------+------+--------+
| 1001 | tom | 3000 | 10 |
| 1002 | jerry | 5000 | 20 |
| 1003 | rose | 3000 | 30 |
+-------+-------+------+--------+
3 rows in set (0.002 sec)
obclient [prod]> select * from dept;
+--------+----------+-----------+
| deptno | dname | location |
+--------+----------+-----------+
| 10 | caiwu | beijing |
| 20 | shichang | shanghai |
| 30 | jishu | guangzhou |
+--------+----------+-----------+
3 rows in set (0.003 sec)
九 附件：observer启动故障
1、查看observer日志
[admin@node203 ~]$ tail -f /home/admin/oceanbase/log/observer.log.wf
[2023-08-30 19:28:04.788689] ERROR issue_dba_error (ob_log.cpp:1802) [8725][observer][T0][Y0-0000000000000000-0-0] [lt=12][errcode=-4388] Unexpected internal error happen, please checkout the internal errcode(errcode=-4290, file="ob_server_log_block_mgr.cpp", line_no=1121, info="::fallocate failed")
[2023-08-30 19:28:04.788764] ERROR issue_dba_error (ob_log.cpp:1802) [8725][observer][T0][Y0-0000000000000000-0-0] [lt=62][errcode=-4388] Unexpected internal error happen, please checkout the internal errcode(errcode=-4290, file="ob_server_log_block_mgr.cpp", line_no=1091, info="allocate_block_at_tmp_dir_ failed")
[2023-08-30 19:28:04.789264] ERROR issue_dba_error (ob_log.cpp:1802) [8725][observer][T0][Y0-0000000000000000-0-0] [lt=18][errcode=-4388] Unexpected internal error happen, please checkout the internal errcode(errcode=-4290, file="ob_server_log_block_mgr.cpp", line_no=1003, info="allocate_blocks_at_ failed")
[2023-08-30 19:28:04.795250] ERROR issue_dba_error (ob_log.cpp:1802) [8725][observer][T0][Y0-0000000000000000-0-0] [lt=15][errcode=-4388] Unexpected internal error happen, please checkout the internal errcode(errcode=-4290, file="ob_server_log_block_mgr.cpp", line_no=954, info="do_expand_ failed")
[2023-08-30 19:28:04.801430] ERROR resize_ (ob_server_log_block_mgr.cpp:210) [8725][observer][T0][Y0-0000000000000000-0-0] [lt=5343][errcode=-4290] cannot allocate disk space(possible reason="may be diskspace is not enough, please check the configuration about log disk", new log disk size(MB)=15147)
[2023-08-30 19:28:04.801445] ERROR issue_dba_error (ob_log.cpp:1802) [8725][observer][T0][Y0-0000000000000000-0-0] [lt=13][errcode=-4388] Unexpected internal error happen, please checkout the internal errcode(errcode=-4290, file="ob_server_log_block_mgr.cpp", line_no=166, info="resize failed")
2、扩容主机文件系统（通过软链接方式）
[admin@node203 ~]$ ls -lh
total 4.0K
drwxr-xr-x. 2 admin admin 6 Aug 25 15:38 Desktop
drwxr-xr-x. 2 admin admin 6 Aug 25 15:38 Documents
drwxr-xr-x. 2 admin admin 6 Aug 25 15:38 Downloads
-rw-rw-r-- 1 admin admin 319 Aug 30 19:44 mkdir.sh
drwxr-xr-x. 2 admin admin 6 Aug 25 15:38 Music
drwxr-xr-x 9 admin admin 101 Aug 30 19:56 obproxy-4.2.0.0
lrwxrwxrwx 1 admin admin 15 Aug 30 19:43 oceanbase -> /data/oceanbase
[admin@node203 ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
......
/dev/mapper/centos-home 50G 6.1G 44G 13% /home
/dev/sdb1 110G 42G 68G 39% /data
[admin@node203 ~]$ ls -lh /data
total 0
drwxrwxr-x 12 admin admin 114 Aug 30 19:51 oceanbase
[admin@node203 ~]$ ls -lh /data/oceanbase/
total 12K
drwxr-xr-x 2 admin admin 4.0K Aug 30 19:47 admin
drwxrwxr-x 2 admin admin 55 Aug 30 19:49 audit
drwxr-sr-x 2 admin admin 52 Aug 30 19:47 bin
drwxr-sr-x 2 admin admin 4.0K Aug 31 10:57 etc
drwxrwxr-x 2 admin admin 62 Aug 31 10:57 etc2
drwxrwxr-x 2 admin admin 62 Aug 31 10:57 etc3
drwxr-sr-x 2 admin admin 104 Aug 30 19:46 lib
drwxrwxr-x 2 admin admin 4.0K Aug 31 11:06 log
drwxrwxr-x 2 admin admin 55 Aug 30 19:51 run
drwxrwxr-x 6 admin admin 53 Aug 30 19:44 store
---如上所示，通过软链接方式，扩容oceanbase所在的文件系统后，observer服务启动正常。
十、总结
对于Oceanbase单节点集群手工部署相对比较容易，但是一定要满足对系统主机资源的最低需求，可以参考官方文档；在以后有条件的情况下，继续测试三节点集群的部署。
