手动部署oceanbase 单副本集群
前言
虚拟机内存至少12G, oceanbase下载地址.
1、创建容器
[root@db3 soft]# docker run -d --name charley16 -h charley16 \
> -p 12881-12889:2881-2889 \
> -v /sys/fs/cgroup:/sys/fs/cgroup \
> --privileged=true centos76:8.5 \
> /usr/sbin/init
9dd74ab6fe599fdb28fa8e52f6a444d0afaa30d206ccf704ebfa97fca1bdeb71
2、创建ob用户
[root@db3 soft]# docker exec -it charley16 bash
[root@charley16 /]# useradd admin
[root@charley16 /]# echo "admin:lhr" | chpasswd
[root@charley16 /]# chown -R admin:admin /home/admin
[root@charley16 /]# echo "admin ALL=(ALL) NOPASSWD: ALL" >> /etc/sudoers
3、安装ob软件
[root@charley16 soft]# ll
total 55468
-rw-r--r--. 1 root root 8179432 Jan 12 14:07 obproxy-3.2.0-1.el7.x86_64.rpm
-rw-r--r--. 1 root root 48457956 Jan 12 14:07 oceanbase-ce-3.1.1-4.el7.x86_64.rpm
-rw-r--r--. 1 root root 158876 Jan 12 14:07 oceanbase-ce-libs-3.1.1-4.el7.x86_64.rpm
[root@charley16 soft]# rpm -ivh *.rpm
warning: obproxy-3.2.0-1.el7.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID e9b4a7aa: NOKEY
warning: Failed to open SELinux handle.
Preparing... ################################# [100%]
Updating / installing...
1:oceanbase-ce-libs-3.1.1-4.el7 ################################# [ 33%]
2:oceanbase-ce-3.1.1-4.el7 ################################# [ 67%]
3:obproxy-3.2.0-1.el7 ################################# [100%]
[root@charley16 soft]# echo "export PATH=$PATH:/home/admin/oceanbase/bin:/home/admin/obproxy-3.2.0/bin" >> /home/admin/.bash_profile
[root@charley16 soft]# echo "export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/home/admin/oceanbase/lib/" >> /home/admin/.bash_profile
4、安装mysql客户端
[root@charley16 soft]# source /home/admin/.bash_profile
[root@charley16 soft]# yum install -y yum-utils
Loaded plugins: fastestmirror, ovl
Determining fastest mirrors
* base: mirrors.aliyun.com
* extras: mirrors.aliyun.com
* updates: mirrors.aliyun.com
base | 3.6 kB 00:00:00
epel | 4.7 kB 00:00:00
extras | 2.9 kB 00:00:00
updates | 2.9 kB 00:00:00
(1/3): epel/x86_64/updateinfo | 1.0 MB 00:00:00
(2/3): epel/x86_64/primary_db | 7.0 MB 00:00:01
(3/3): updates/7/x86_64/primary_db | 13 MB 00:00:02
Package yum-utils-1.1.31-54.el7_8.noarch already installed and latest version
Nothing to do
[root@charley16 soft]# yum-config-manager --add-repo https://mirrors.aliyun.com/oceanbase/OceanBase.repo
Loaded plugins: fastestmirror, ovl
adding repo from: https://mirrors.aliyun.com/oceanbase/OceanBase.repo
grabbing file https://mirrors.aliyun.com/oceanbase/OceanBase.repo to /etc/yum.repos.d/OceanBase.repo
repo saved to /etc/yum.repos.d/OceanBase.repo
[root@charley16 soft]# yum install -y obclient mariadb mariadb-libs mariadb-devel
Loaded plugins: fastestmirror, ovl
Loading mirror speeds from cached hostfile
* base: mirrors.aliyun.com
* extras: mirrors.aliyun.com
* updates: mirrors.aliyun.com
oceanbase.community.stable | 3.0 kB 00:00:00
oceanbase.development-kit | 3.0 kB 00:00:00
(1/2): oceanbase.development-kit/7/x86_64/primary_db | 16 kB 00:00:00
(2/2): oceanbase.community.stable/7/x86_64/primary_db | 23 kB 00:00:00
Package 1:mariadb-libs-5.5.68-1.el7.x86_64 already installed and latest version
Resolving Dependencies
--> Running transaction check
---> Package mariadb.x86_64 1:5.5.68-1.el7 will be installed
---> Package mariadb-devel.x86_64 1:5.5.68-1.el7 will be installed
---> Package obclient.x86_64 0:2.0.0-2.el7 will be installed
--> Processing Dependency: libobclient >= 2.0.0 for package: obclient-2.0.0-2.el7.x86_64
--> Running transaction check
---> Package libobclient.x86_64 0:2.0.0-2.el7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
=============================================================================================================================================================
Package Arch Version Repository Size
=============================================================================================================================================================
Installing:
mariadb x86_64 1:5.5.68-1.el7 base 8.8 M
mariadb-devel x86_64 1:5.5.68-1.el7 base 757 k
obclient x86_64 2.0.0-2.el7 oceanbase.community.stable 40 M
Installing for dependencies:
libobclient x86_64 2.0.0-2.el7 oceanbase.community.stable 643 k
Transaction Summary
=============================================================================================================================================================
Install 3 Packages (+1 Dependent package)
Total download size: 50 M
Installed size: 240 M
Downloading packages:
(1/4): mariadb-devel-5.5.68-1.el7.x86_64.rpm | 757 kB 00:00:00
warning: /var/cache/yum/x86_64/7/oceanbase.community.stable/packages/libobclient-2.0.0-2.el7.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID e9b4a7aa: NOKEY
Public key for libobclient-2.0.0-2.el7.x86_64.rpm is not installed
(2/4): libobclient-2.0.0-2.el7.x86_64.rpm | 643 kB 00:00:00
(3/4): mariadb-5.5.68-1.el7.x86_64.rpm | 8.8 MB 00:00:01
(4/4): obclient-2.0.0-2.el7.x86_64.rpm | 40 MB 00:00:04
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 10 MB/s | 50 MB 00:00:04
Retrieving key from http://mirrors.aliyun.com/oceanbase/RPM-GPG-KEY-OceanBase
Importing GPG key 0xE9B4A7AA:
Userid : "OceanBase"
Fingerprint: ef7d e8e3 6987 b60c acf9 9a53 2ff8 45a6 e9b4 a7aa
From : http://mirrors.aliyun.com/oceanbase/RPM-GPG-KEY-OceanBase
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Warning: RPMDB altered outside of yum.
warning: Failed to open SELinux handle.
Installing : libobclient-2.0.0-2.el7.x86_64 1/4
Installing : obclient-2.0.0-2.el7.x86_64 2/4
Installing : 1:mariadb-devel-5.5.68-1.el7.x86_64 3/4
Installing : 1:mariadb-5.5.68-1.el7.x86_64 4/4
Verifying : 1:mariadb-5.5.68-1.el7.x86_64 1/4
Verifying : 1:mariadb-devel-5.5.68-1.el7.x86_64 2/4
Verifying : libobclient-2.0.0-2.el7.x86_64 3/4
Verifying : obclient-2.0.0-2.el7.x86_64 4/4
Installed:
mariadb.x86_64 1:5.5.68-1.el7 mariadb-devel.x86_64 1:5.5.68-1.el7 obclient.x86_64 0:2.0.0-2.el7
Dependency Installed:
libobclient.x86_64 0:2.0.0-2.el7
Complete!
[root@charley16 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/timezone_V1.log
[root@charley16 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@charley16 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
4、单副本集群配置
[admin@charley16 store]$ ps -ef |grep observer
admin 551 0 65 14:45 ? 00:00:09 /home/admin/oceanbase/bin/observer -r 127.0.0.1:2882:2881 -o__min_full_resource_pool_memory=268435456,memory_limit=8G,system_memory=4G,stack_size=512K,cpu_count=16,cache_wash_threshold=1G,workers_per_cpu_quota=10,schema_history_expire_time=1d,net_thread_count=4,major_freeze_duty_time=Disable,minor_freeze_times=10,enable_separate_sys_clog=0,enable_merge_by_turn=False,datafile_size=5G,enable_syslog_wf=False,enable_syslog_recycle=True,max_syslog_file_count=4 -z zone1 -p 2881 -P 2882 -n charleyob-single -c 20220108 -d /home/admin/oceanbase/store -i lo -l ERROR
admin 1125 505 0 14:45 pts/0 00:00:00 grep --color=auto observer
[admin@charley16 store]$ netstat -tulnp | grep 288
(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:2881 0.0.0.0:* LISTEN 551/observer
tcp 0 0 0.0.0.0:2882 0.0.0.0:* LISTEN 551/observer
5、集群初始化
[admin@charley16 store]$ 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.1 (r4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e) (Built Oct 21 2021 10:33:14)
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.00 sec)
MySQL [(none)]> alter system bootstrap ZONE 'zone1' SERVER '127.0.0.1:2882';
Query OK, 0 rows affected (17.06 sec)
MySQL [(none)]> alter user root identified by 'charley';
Query OK, 0 rows affected (0.03 sec)
MySQL [(none)]> create user proxyro identified by 'charley';
Query OK, 0 rows affected (0.02 sec)
MySQL [(none)]> grant select on *.* to proxyro;
Query OK, 0 rows affected (0.02 sec)
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| oceanbase |
| information_schema |
| mysql |
| SYS |
| LBACSYS |
| ORAAUDITOR |
| test |
+--------------------+
7 rows in set (0.01 sec)
MySQL [(none)]> exit
Bye
6、配置obproxy
[admin@charley16 store]$ 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@charley16 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 1171/obproxy
tcp 0 0 0.0.0.0:2884 0.0.0.0:* LISTEN 1171/obproxy
[admin@charley16 obproxy-3.2.0]$ mysql -h127.1 -uroot@proxysys -P2883 -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1
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)]> alter proxyconfig set obproxy_sys_password='charley';
Query OK, 0 rows affected (0.00 sec)
MySQL [(none)]> alter proxyconfig set observer_sys_password='charley';
Query OK, 0 rows affected (0.00 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 | a2702b31297a8bea4230cb2ebc954564264fd4b7 | password for observer sys user | false | SYS |
| obproxy_sys_password | a2702b31297a8bea4230cb2ebc954564264fd4b7 | password for obproxy sys user | false | SYS |
+------------------------+------------------------------------------+--------------------------------+-------------+---------------+
3 rows in set (0.00 sec)
MySQL [(none)]> exit
Bye
7、创建资源单元、资源池、租户
[admin@charley16 obproxy-3.2.0]$ mysql -h127.1 -uroot@sys -P2883 -pcharley -c -A
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.25 OceanBase 3.1.1 (r4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e) (Built Oct 21 2021 10:33:14)
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 |
+--------------------+
| oceanbase |
| information_schema |
| mysql |
| SYS |
| LBACSYS |
| ORAAUDITOR |
| test |
+--------------------+
7 rows in set (0.01 sec)
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-12 14:47:07.765782 | 2022-01-12 14:47:18.962420 | 127.0.0.1 | 2882 | 1 | zone1 | 2881 | 1 | active | 0 | 3.1.1_4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e(Oct 21 2021 10:33:14) | 0 | 1641970036972702 | 0 | 1 | 0 |
+----------------------------+----------------------------+-----------+----------+----+-------+------------+-----------------+--------+-----------------------+------------------------------------------------------------------------+-----------+--------------------+--------------+----------------+-------------------+
1 row in set (0.01 sec)
MySQL [(none)]> show full processlist;
+------------+---------+--------+-----------------+-----------+---------+------+--------+-----------------------+-----------+------+--------------+
| Id | User | Tenant | Host | db | Command | Time | State | Info | Ip | Port | Proxy_sessid |
+------------+---------+--------+-----------------+-----------+---------+------+--------+-----------------------+-----------+------+--------------+
| 3221487692 | root | sys | 127.0.0.1:49510 | NULL | Query | 0 | ACTIVE | show full processlist | 127.0.0.1 | 2881 | 2 |
| 3221487694 | proxyro | sys | 127.0.0.1:49514 | oceanbase | Sleep | 13 | SLEEP | NULL | 127.0.0.1 | 2881 | 3 |
+------------+---------+--------+-----------------+-----------+---------+------+--------+-----------------------+-----------+------+--------------+
2 rows in set (0.00 sec)
MySQL [(none)]> CREATE resource unit charley 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.01 sec)
MySQL [(none)]> CREATE resource pool my_charley_pool unit = 'charley', unit_num = 1;
Query OK, 0 rows affected (0.03 sec)
MySQL [(none)]> create tenant obcharley resource_pool_list=('my_charley_pool'), primary_zone='RANDOM',comment 'mysql tenant/instance', charset='utf8' set ob_tcp_invited_nodes='%', ob_compatibility_mode='mysql';
Query OK, 0 rows affected (0.48 sec)
MySQL [(none)]> exit
Bye
8、登陆租户、创建库、表
[admin@charley16 obproxy-3.2.0]$ mysql -uroot@obcharley -p -h127.1 -P2883 -c -A
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.25 OceanBase 3.1.1 (r4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e) (Built Oct 21 2021 10:33:14)
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 |
+--------------------+
| oceanbase |
| information_schema |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)
MySQL [(none)]> create database charley charset utfmb4;
ERROR 1115 (42000): Unknown character set: 'utfmb4'
MySQL [(none)]> create database charley charset utf8mb4;
Query OK, 1 row affected (0.02 sec)
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| oceanbase |
| information_schema |
| mysql |
| test |
| charley |
+--------------------+
5 rows in set (0.00 sec)
MySQL [(none)]> use charley;
Database changed
MySQL [charley]> create table Timmy(id int,name varchar(20));
Query OK, 0 rows affected (0.05 sec)
MySQL [charley]> insert into Timmy values(1,"charlye");
Query OK, 1 row affected (0.01 sec)
MySQL [charley]> insert into Timmy values(2,"tommy");
Query OK, 1 row affected (0.00 sec)
MySQL [charley]> insert into Timmy values(3,"Lucy");
Query OK, 1 row affected (0.00 sec)
MySQL [charley]> show tables;
+-------------------+
| Tables_in_charley |
+-------------------+
| timmy |
+-------------------+
1 row in set (0.00 sec)
MySQL [charley]> select * from Timmy;
+------+---------+
| id | name |
+------+---------+
| 1 | charlye |
| 2 | tommy |
| 3 | Lucy |
+------+---------+
3 rows in set (0.00 sec)
MySQL [charley]>