实践练习二(必选):手动部署 OceanBase 集群
练习目的
本次练习目的掌握 OceanBase 集群的手动部署技能,理解进程的相关目录、集群初始化、OBProxy和 OB 集群关系等。
练习条件
有笔记本或服务器,内存至少12G 。
练习内容
请记录并分享下列内容:
- (必选)手动部署一个 OB 单副本集群,包括一个 OBProxy 节点。
- (必选)创建一个业务租户、一个业务数据库,以及一些表等。
- (可选)如果单台服务器内存有32G,或者有三台服务器,改为部署一个 OB 三副本集群,包括一个 OBProxy 节点。
- (可选)如果有三台服务器并且服务器内存有 32 G,可以单服务器内启动 2 个节点,实现 1-1-1 扩容到 2-2-2 。
1. 部署规划
1.1 机器信息
机器类型 | 虚拟机 |
---|---|
IP | 10.18.104.14[0-3] |
网卡名 | ens160 |
OS | CentOS Linux release 7.4.170 |
CPU | 8 (Core) [lscpu] |
内存 | 15G [ free -h] |
磁盘 | /dev/mapper/vg0-lv0 492G /data [df -h] |
1.2 机器角色
机器 | 角色 | 描述 |
---|---|---|
10.18.104.140 | OBD、OBPROXY、OBCLIENT | 中控机、反向代理、命令行客户端 |
10.18.104.141 | OBSERVER | OceanBase 数据库 Zone1 |
10.18.104.142 | OBSERVER | OceanBase 数据库 Zone2 |
10.18.104.143 | OBSERVER | OceanBase 数据库 Zone3 |
2. 部署前准备
2.1 创建 admin 用户
# 在每一台机器上执行以下命令
sudo useradd admin
sudo sh -c 'echo "admin ALL=(ALL) NOPASSWD: ALL" >> /etc/sudoers'
sudo passwd admin
# 创建部署用户 admin
[aicenter@aicenter1 ~]$ sudo useradd admin
[aicenter@aicenter1 ~]$ sudo sh -c 'echo "admin ALL=(ALL) NOPASSWD: ALL" >> /etc/sudoers'
[aicenter@aicenter1 ~]$ sudo passwd admin
更改用户 admin 的密码 。
新的 密码:
无效的密码: 密码未通过字典检查 - 它基于字典单词
重新输入新的 密码:
passwd:所有的身份验证令牌已经成功更新。
[aicenter@aicenter1 ~]$ su admin
密码:
[admin@aicenter1 aicenter]$ exit
2.2 配置 SSH 互信及 sudo 免密码
ConfigureSSHMutualTrustAndSudoPasswordFree.sh
#!/bin/bash
#read -p "Please input a username:" username
#su ${username}
echo "继续执行前请确认用户已切换!"
echo "未切换时先按Ctrl+C结束,再通过 su username进行用户切换..."
sleep 3
echo "将执行 ssh-keygen -t rsa"
sleep 1
ssh-keygen -t rsa
echo "执行 ssh-copy-id命令之前请确认目标机上已创建当前相同用户"
sleep 3
echo "将执行 ssh-copy-id -i ~/.ssh/id_rsa.pub 目标机IP"
sleep 1
ssh-copy-id -i ~/.ssh/id_rsa.pub 10.18.104.140
ssh-copy-id -i ~/.ssh/id_rsa.pub 10.18.104.141
ssh-copy-id -i ~/.ssh/id_rsa.pub 10.18.104.142
ssh-copy-id -i ~/.ssh/id_rsa.pub 10.18.104.143
echo "测试SSH互信"
echo "ssh 目标IP"
echo "测试sudo免密码"
echo "sudo -su root"
# 脚本准备
[admin@aicenter1 scripts]$ vim ConfigureSSHMutualTrustAndSudoPasswordFree.sh
# 赋可执行权限
[admin@aicenter1 scripts]$ chmod +x ConfigureSSHMutualTrustAndSudoPasswordFree.sh
# 执行
[admin@aicenter1 scripts]$ ./ConfigureSSHMutualTrustAndSudoPasswordFree.sh
......
# 验证测试
[admin@aicenter1 scripts]$ ssh 10.18.104.143
[admin@aicenter4 ~]$ exit
登出
Connection to 10.18.104.143 closed.
[admin@aicenter1 scripts]$ sudo -su root
[root@aicenter1 scripts]# exit
exit
[admin@aicenter1 scripts]$
2.3 初始化服务器环境
实践练习中采用脚本初始化
- initNodes.sh
cat initNodes.sh
#!/bin/bash
close_swap() {
echo "检测及关闭系统 swap"
sudo echo "vm.swappiness = 0" >> /etc/sysctl.conf
swapoff -a && swapon -a
sysctl -p
echo "系统 swap 已关闭"
}
close_firewall() {
echo "关闭及检测目标部署机器的防火墙"
sudo systemctl stop firewalld.service
sudo systemctl disable firewalld.service
sudo systemctl status firewalld.service
echo "目标部署机器的防火墙已关闭"
}
ntp_service() {
#read -p "Please input a ntp_server_ip:" ntp_server_ip
#sudo systemctl stop ntpd.service
#sudo ntpdate ${ntp_server_ip}
sudo systemctl restart ntpd.service
sudo systemctl enable ntpd.service
echo "检查ntp服务器的连通性"
ntpq -p
}
csop() {
echo "配置系统优化参数..."
echo "查看当前操作系统的 tuned 策略"
tuned-adm list
echo "创建新的 tuned 策略"
mkdir /etc/tuned/balanced-OceanBase-optimal/
touch /etc/tuned/balanced-OceanBase-optimal/tuned.conf
echo "[main]" > /etc/tuned/balanced-OceanBase-optimal/tuned.conf
echo "include=balanced" >> /etc/tuned/balanced-OceanBase-optimal/tuned.conf
echo "[cpu]" >> /etc/tuned/balanced-OceanBase-optimal/tuned.conf
echo "governor=performance" >> /etc/tuned/balanced-OceanBase-optimal/tuned.conf
echo "[vm]" >> /etc/tuned/balanced-OceanBase-optimal/tuned.conf
echo "transparent_hugepages=never" >> /etc/tuned/balanced-OceanBase-optimal/tuned.conf
echo "[disk]" >> /etc/tuned/balanced-OceanBase-optimal/tuned.conf
echo "elevator=noop" >> /etc/tuned/balanced-OceanBase-optimal/tuned.conf
echo "新的 tuned 策略创建完成. 查看请执行 cat /etc/tuned/balanced-OceanBase-optimal/tuned.conf"
echo "应用新的 tuned 策略"
tuned-adm profile balanced-OceanBase-optimal
echo "验证透明大页的状态: 是否为never"
cat /sys/kernel/mm/transparent_hugepage/enabled
echo "验证数据目录所在磁盘的 I/O 调度器:是否为noop "
cat /sys/block/sd[bc]/queue/scheduler
echo "查看 cpufreq 模块选用的节能策略: performance模式"
cpupower frequency-info --policy
echo "如果是虚拟机或者云主机,则不需要调整,命令输出通常为 Unable to determine current policy"
echo "配置系统参数优化完成..."
}
modify_sysctl() {
echo "修改 sysctl 参数..."
echo "fs.file-max = 1000000">> /etc/sysctl.conf
echo "net.core.somaxconn = 32768">> /etc/sysctl.conf
echo "net.ipv4.tcp_tw_recycle = 0">> /etc/sysctl.conf
echo "net.ipv4.tcp_syncookies = 0">> /etc/sysctl.conf
echo "vm.overcommit_memory = 1">> /etc/sysctl.conf
echo "执行 sysctl -p 命令在不重启的情况下使配置生效"
sysctl -p
echo "修改 sysctl 参数完成..."
}
limits_conf() {
echo "配置用户的 limits.conf 文件"
echo "OceanBase soft nofile 1000000" >>/etc/security/limits.conf
echo "OceanBase hard nofile 1000000" >>/etc/security/limits.conf
echo "OceanBase soft stack 32768" >>/etc/security/limits.conf
echo "OceanBase hard stack 32768" >>/etc/security/limits.conf
echo "limits.conf 文件配置完成"
}
echo "OceanBase 环境与系统配置检查开始..."
echo "1.close_swap Start"
close_swap
echo "close_swap End"
echo " "
echo "2.close_firewall Start"
close_firewall
echo "close_firewall End"
echo " "
echo "3.ntp_service Start"
ntp_service
echo "ntp_service End"
echo " "
echo "4.csop Start"
csop
echo "csop End"
echo " "
echo "5.modify_sysctl Start"
modify_sysctl
echo "modify_sysctl End"
echo " "
echo "6.limits_conf Start"
limits_conf
echo "limits_conf End"
echo " "
echo "OceanBase 环境与系统配置检查完成......"
- do_all.sh
[admin@aicenter1 scripts]$ pwd
/home/oceanbase/scripts
[admin@aicenter1 scripts]$ ll
总用量 20
-rwxr-xr-x. 1 oceanbase oceanbase 76 2月 10 17:52 cp_all.sh
-rwxr-xr-x. 1 oceanbase oceanbase 79 2月 10 17:52 do_all.sh
-rw-r--r--. 1 oceanbase oceanbase 122 2月 10 17:53 hosts
-rw-r--r--. 1 oceanbase oceanbase 72 2月 10 17:53 ips
-rwxr-xr-x. 1 oceanbase oceanbase 15 2月 10 17:49 pwd.sh
[admin@aicenter1 scripts]$ cat do_all.sh
CMD=$@
for ip in `cat ips`
do
echo "IP $ip"
ssh admin@$ip $CMD
done
- cp_all.sh
[admin@aicenter1 scripts]$ cat cp_all.sh
SRC=$1
DST=$2
for ip in `cat ips`
do
scp -r $SRC admin@$ip:$DST
done
一次性执行所有机器的配置初始化
# 复制到所有机器节点上,然后在每台机器上执行
[admin@aicenter1 scripts]$ ./cp_all.sh initNodes.sh
# 上面已配置 ssh 免密登陆
# 执行方法
[admin@aicenter1 ~]$ ./initNodes.sh
......
2.4 准备离线安装包
# 在一台和外网相通的机器上下载需要的组件
root@kylin:/mnt/d/OfflineInstaller# ll oceanbase-ce-server-v3.1.2-linux-amd64
total 117312
-rwxrwxrwx 1 root root 21157644 Feb 10 09:15 ob-deploy-1.2.0-15.el7.x86_64.rpm*
-rwxrwxrwx 1 root root 41916564 Feb 10 09:15 obclient-2.0.0-2.el7.x86_64.rpm*
-rwxrwxrwx 1 root root 8179432 Feb 10 09:23 obproxy-3.2.0-1.el7.x86_64.rpm*
-rwxrwxrwx 1 root root 48708456 Feb 10 09:15 oceanbase-ce-3.1.2-10000392021123010.el7.x86_64.rpm*
-rwxrwxrwx 1 root root 158948 Feb 10 09:15 oceanbase-ce-libs-3.1.2-10000392021123010.el7.x86_64.rpm*
# 通过 tar 命令将该组件包打包然后发送到隔离环境的中控机
root@kylin:/mnt/d/OfflineInstaller# tar czvf oceanbase-ce-server-v3.1.2-linux-amd64.tar.gz oceanbase-ce-server-v3.1.2-linux-amd64
oceanbase-ce-server-v3.1.2-linux-amd64/
oceanbase-ce-server-v3.1.2-linux-amd64/ob-deploy-1.2.0-15.el7.x86_64.rpm
oceanbase-ce-server-v3.1.2-linux-amd64/obclient-2.0.0-2.el7.x86_64.rpm
oceanbase-ce-server-v3.1.2-linux-amd64/obproxy-3.2.0-1.el7.x86_64.rpm
oceanbase-ce-server-v3.1.2-linux-amd64/oceanbase-ce-3.1.2-10000392021123010.el7.x86_64.rpm
oceanbase-ce-server-v3.1.2-linux-amd64/oceanbase-ce-libs-3.1.2-10000392021123010.el7.x86_64.rpm
root@kylin:/mnt/d/OfflineInstaller# ll | grep oceanbase*.tar.gz
-rwxrwxrwx 1 root root 120034662 Feb 10 09:39 oceanbase-ce-server-v3.1.2-linux-amd64.tar.gz*
2.5 上传离线安装包
# 上传离线安装包
sftp> put "D:\3.Download\ChromeDownload\OfflineInstaller\oceanbase-ce-server-v3.1.2-linux-amd64.tar.gz"
Uploading oceanbase-ce-server-v3.1.2-linux-amd64.tar.gz to /home/OceanBase/oceanbase-ce-server-v3.1.2-linux-amd64.tar.gz
100% 117221KB 19536KB/s 00:00:06
D:\3.Download\ChromeDownload\OfflineInstaller\oceanbase-ce-server-v3.1.2-linux-amd64.tar.gz: 120034662 bytes transferred in 6 seconds (19536 KB/s)
sftp>
# 将安装包复制道每台机器上
[admin@aicenter1 scripts]$ ./cp_all.sh /home/oceanbase/oceanbase-ce-server-v3.1.2-linux-amd64
# 检验
[admin@aicenter1 scripts]$ ./do_all.sh ls /home/oceanbase/
IP 10.18.104.140
oceanbase-ce-server-v3.1.2-linux-amd64
oceanbase-ce-server-v3.1.2-linux-amd64.tar.gz
scripts
yaml
IP 10.18.104.141
oceanbase-ce-server-v3.1.2-linux-amd64
IP 10.18.104.142
oceanbase-ce-server-v3.1.2-linux-amd64
IP 10.18.104.143
oceanbase-ce-server-v3.1.2-linux-amd64
3. 手动安装部署
3.1 安装 OceanBase OBServer
# 在 10.18.104.14[1-3]上安装,计划部署的机器通过文件ips设定
[admin@aicenter1 scripts]$ ./do_all.sh sudo rpm -ivh /home/oceanbase/oceanbase-ce-server-v3.1.2-linux-amd64/oceanbase-ce-*.rpm
# 安装出问题的话,强制重新安装 只需要在已有的命令后面加 --nodeps --force
# 初始化数据目录 (首次部署前只做一次)
# 创建运行目录
sudo mkdir -p ~/oceanbase/store/obdemo /data/obdemo/{sstable,etc3} /redo/obdemo/{clog,ilog,slog,etc2}
for f in {clog,ilog,slog,etc2}; do ln -s /redo/obdemo/$f ~/oceanbase/store/obdemo/$f ; done
for f in {sstable,etc3}; do ln -s /data/obdemo/$f ~/oceanbase/store/obdemo/$f; done
3.2 启动 OBServer
# 所有OBServer机器
# 同步 NTP 服务端时间
sudo ntpdate 10.18.104.140
# 检查本机和目标节点时间误差
sudo clockdiff 10.18.104.140
# 配置lib信息
echo 'export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:~/oceanbase/lib' >> ~/.bash_profile
# 使配置信息生效
. ~/.bash_profile
# 启动 OB 进程
# 10.18.104.141
cd ~/oceanbase && bin/observer -i ens160 -p 2881 -P 2882 -z zone1 -d ~/oceanbase/store/obdemo -r '10.18.104.141:2882:2881;10.18.104.142:2882:2881;10.18.104.143:2882:2881' -c 20220210 -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=50G,stack_size=1536K,config_additional_dir=/data/obdemo/etc3;/redo/obdemo/etc2" -d ~/oceanbase/store/obdemo
# 10.18.104.142
cd ~/oceanbase && bin/observer -i ens160 -p 2881 -P 2882 -z zone2 -d ~/oceanbase/store/obdemo -r '10.18.104.141:2882:2881;10.18.104.142:2882:2881;10.18.104.143:2882:2881' -c 20220210 -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=50G,stack_size=1536K,config_additional_dir=/data/obdemo/etc3;/redo/obdemo/etc2" -d ~/oceanbase/store/obdemo
# 10.18.104.143
cd ~/oceanbase && bin/observer -i ens160 -p 2881 -P 2882 -z zone3 -d ~/oceanbase/store/obdemo -r '10.18.104.141:2882:2881;10.18.104.142:2882:2881;10.18.104.143:2882:2881' -c 20220210 -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=50G,stack_size=1536K,config_additional_dir=/data/obdemo/etc3;/redo/obdemo/etc2" -d ~/oceanbase/store/obdemo
# 如果三个节点机型都一致,那么启动参数里只有一个参数不一样,就是 -z 指定该节点是哪个 zone 。三个 zone 的三个节点初始化为一个三副本集群。
# 服务验证
ps -ef |grep observer # 查看进程信息
# 或
sudo netstat -tunlp | grep 2881 # 查看服务端口
3.3 bootstrap
集群
当 OceanBase 集群三个节点都正常启动,并且监听正常时,连接到任一节点(通过 2881 端口直连),进行自举(
bootstrap
集群初始化)操作。 初始密码是空。
[admin@@aicenter2 oceanbase]$ mysql -h 10.18.104.141 -u root -P 2881 -p -c -A
。。。。。。
MySQL [(none)]> set session ob_query_timeout=1000000000;
Query OK, 0 rows affected (0.001 sec)
MySQL [(none)]> alter system bootstrap ZONE 'zone1' SERVER '10.18.104.141:2882', ZONE 'zone2' SERVER '10.18.104.142:2882', ZONE 'zone3' SERVER '10.18.104.143:2882' ;
Query OK, 0 rows affected (28.839 sec)
MySQL [(none)]> exit
Bye
3.4 设置用户密码
- 集群管理员(
root@sys
)密码默认集群管理员(root@sys
)的密码是空的,这里设置一个密码。- OBPROXY 连接 OceanBase 集群使用用户
proxyro
。这个用户不存在,需要创建。
[admin@@aicenter2 oceanbase]$ mysql -h 10.18.104.141 -u root@sys -P 2881 -p -c -A
。。。。。。
MySQL [(none)]> alter user root identified by 'Manager2@';
Query OK, 0 rows affected (0.003 sec)
MySQL [(none)]> grant select on oceanbase.* to proxyro identified by 'Manager3#' ;
Query OK, 0 rows affected (0.004 sec)
3.5 安装 OBPROXY
# sudo rpm -ivh obproxy-3.2.0-1.el7.x86_64.rpm
[admin@aicenter1 oceanbase-ce-server-v3.1.2-linux-amd64]$ sudo rpm -ivh obproxy-3.2.0-1.el7.x86_64.rpm
警告:obproxy-3.2.0-1.el7.x86_64.rpm: 头V4 RSA/SHA1 Signature, 密钥 ID e9b4a7aa: NOKEY
准备中... ################################# [100%]
正在升级/安装...
1:obproxy-3.2.0-1.el7 ################################# [100%]
3.5 启动 OBPROXY 进程
[admin@aicenter1 oceanbase-ce-server-v3.1.2-linux-amd64]$ cd ..
[admin@aicenter1 ~]$ ll
总用量 0
drwxr-xr-x. 3 admin admin 17 2月 11 15:34 obproxy-3.2.0
drwxrwxr-x. 3 admin admin 19 2月 11 09:44 oceanbase
drwxrwxr-x. 2 admin admin 247 2月 10 11:02 oceanbase-ce-server-v3.1.2-linux-amd64
drwxr-xr-x. 2 admin admin 131 2月 11 10:07 scripts
[admin@aicenter1 ~]$ cd obproxy-3.2.0/
[admin@aicenter1 obproxy-3.2.0]$ ll
总用量 0
drwxr-xr-x. 2 admin admin 40 2月 11 15:34 bin
[admin@aicenter1 obproxy-3.2.0]$ ./bin/obproxy -r "10.18.104.141:2881;10.18.104.142:2881;10.18.104.143:2881" -p 2883 -o "enable_strict_kernel_release=false,enable_cluster_checkout=false,enable_metadb_used=false" -c obdemo
./bin/obproxy -r 10.18.104.141:2881;10.18.104.142:2881;10.18.104.143:2881 -p 2883 -o enable_strict_kernel_release=false,enable_cluster_checkout=false,enable_metadb_used=false -c obdemo
rs list: 10.18.104.141:2881;10.18.104.142:2881;10.18.104.143:2881
listen port: 2883
optstr: enable_strict_kernel_release=false,enable_cluster_checkout=false,enable_metadb_used=false
cluster_name: obdemo
# 查看 OBPROXY 进程
[admin@aicenter1 obproxy-3.2.0]$ ps -ef | grep obproxy
admin 31442 1 1 15:40 ? 00:00:00 ./bin/obproxy -r 10.18.104.141:2881;10.18.104.142:2881;10.18.104.143:2881 -p 2883 -o enable_strict_kernel_release=false,enable_cluster_checkout=false,enable_metadb_used=false -c obdemo
admin 31478 25715 0 15:41 pts/0 00:00:00 grep --color=auto obproxy
# 检查 OBPROXY 监听正常 :进程 obproxy 默认会监听2个端口:2883 和 2884 。
[admin@aicenter1 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 31442/./bin/obproxy
tcp 0 0 0.0.0.0:2884 0.0.0.0:* LISTEN 31442/./bin/obproxy
3.6 登录 OBPROXY
登录用户名:root@proxysys
, 端口:2883
,初始密码:空。
[admin@aicenter1 obproxy-3.2.0]$ mysql -h 10.18.104.140 -u root@proxysys -P 2883 -p
MySQL [(none)]> show proxyconfig like '%sys_password%';
+-----------------------+-------+--------------------------------+-------------+---------------+
| name | value | info | need_reboot | visible_level |
+-----------------------+-------+--------------------------------+-------------+---------------+
| observer_sys_password | | password for observer sys user | false | SYS |
| obproxy_sys_password | | password for obproxy sys user | false | SYS |
+-----------------------+-------+--------------------------------+-------------+---------------+
2 rows in set (0.000 sec)
修改 OBPROXY 用户密码是通过修改参数的方式,命令是:alter proxyconfig set
。
MySQL [(none)]> alter proxyconfig set obproxy_sys_password = 'Manager2@' ;
同时还需要修改 OBPROXY 连接 OceanBase 集群用户 proxyro
的密码。这样 OBPROXY 才能跟 OceanBase 集群正常连接。这个密码就是前面 OceanBase 集群初始化后创建的用户 proxyro
的密码。
MySQL [(none)]> alter proxyconfig set observer_sys_password = 'Manager3#' ;
退出,通过 OBPROXY 连接 OceanBase 集群看看, 如果能查看所有会话,则说明 OBPROXY 部署成功。
[admin@aicenter1 obproxy-3.2.0]$ mysql -h10.18.104.140 -uroot@sys#obdemo -P2883 -p -c -A oceanbase
......
MySQL [oceanbase]> show processlist;
+------+--------+------+---------------------+-----------+-------------+-------------------+-------------------+-------+-------+
| Id | Tenant | User | Host | db | trans_count | svr_session_count | state | tid | pid |
+------+--------+------+---------------------+-----------+-------------+-------------------+-------------------+-------+-------+
| 5 | sys | root | 10.18.104.140:41524 | oceanbase | 0 | 1 | MCS_ACTIVE_READER | 38206 | 38206 |
+------+--------+------+---------------------+-----------+-------------+-------------------+-------------------+-------+-------+
1 row in set (0.000 sec)
MySQL [oceanbase]> show full processlist;
+------------+---------+--------+---------------------+-----------+---------+------+--------+-----------------------+---------------+------+--------------+
| Id | User | Tenant | Host | db | Command | Time | State | Info | Ip | Port | Proxy_sessid |
+------------+---------+--------+---------------------+-----------+---------+------+--------+-----------------------+---------------+------+--------------+
| 3222013775 | root | sys | 10.18.104.140:57436 | oceanbase | Query | 0 | ACTIVE | show full processlist | 10.18.104.141 | 2881 | 4 |
| 3221751633 | proxyro | sys | 10.18.104.140:49344 | oceanbase | Sleep | 2 | SLEEP | NULL | 10.18.104.142 | 2881 | 3 |
+------------+---------+--------+---------------------+-----------+---------+------+--------+-----------------------+---------------+------+--------------+
2 rows in set (0.022 sec)
4 创建租户
# 使用 root 账号登陆到 sys 租户中,使用 MySQL 或 OBClient 访问 OceanBase 数据库
[admin@aicenter1 obproxy-3.2.0]$ mysql -h10.18.104.140 -uroot@sys#obdemo -P2883 -p -c -A -Doceanbase
# 查询系统资源占用情况
MySQL [oceanbase]> SELECT svr_ip,svr_port, cpu_total, mem_total, disk_total, zone FROM __all_virtual_server_stat ;
# 查询系统资源分配
MySQL [oceanbase]> SELECT sum(c.max_cpu), sum(c.max_memory) FROM __all_resource_pool as a, __all_unit_config AS c WHERE a.unit_config_id=c.unit_config_id;
# 创建资源单元
MySQL [oceanbase]> CREATE RESOURCE UNIT unit1
max_cpu = 5,
max_memory = 19043332096,
min_memory = 19043332096,
max_iops = 10000,
min_iops = 1280,
max_session_num = 3000,
max_disk_size = 21474836480 -- 20 GB
;
# 创建资源池
MySQL [oceanbase]> CREATE RESOURCE POOL pool1
UNIT = 'unit1',
UNIT_NUM = 1,
ZONE_LIST = ('zone1', 'zone2', 'zone3')
;
# 创建租户
MySQL [oceanbase]> CREATE TENANT IF NOT EXISTS test_tenant
charset='utf8mb4',
replica_num=3,
zone_list=('zone1','zone2','zone3'),
primary_zone='RANDOM',
resource_pool_list=('pool1')
# 登录租户之前,运行以下命令修改参数
MySQL [oceanbase]> ALTER TENANT test_tenant SET VARIABLES ob_tcp_invited_nodes='%';
# 使用新的租户登录系统
[admin@aicenter1 obproxy-3.2.0]$ mysql -h10.18.104.140 -uroot@test_tenant#obdemo -P2883 -p -c -A
5. 创建数据库及表
[admin@aicenter1 obproxy-3.2.0]$ mysql -h10.18.104.140 -uroot@test_tenant#obdemo -P2883 -p -c -A -Dtest
。。。。。。
# 创建一个 aicenter 业务库 ,添加 IF NOT EXISTS 可防止发生错误。
MySQL [test]> CREATE DATABASE IF NOT EXISTS aicenter;
Query OK, 1 row affected (0.931 sec)
# 使用名为 aicenter 的数据库
MySQL [test]> use aicenter;
Database changed
# 创建业务表:创建名为 person 的表,包括编号、名字、生日等字段
MySQL [aicenter]> CREATE TABLE person (
-> id INT(11),
-> name VARCHAR(255),
-> birthday DATE
-> );
Query OK, 0 rows affected (2.642 sec)
MySQL [aicenter]> exit
Bye