热门标签
热门文章
- 1unity导入视频并实现播放及进度条滑动_unity2d放视频
- 2Jenkins用户密码重置
- 3sqlmap基本信息及参数使用方法
- 4Vue版Ant Design给a-form表单-赋值及获取表单数据-案例_ant design vue 怎么获取form的值
- 5Windows平台安装Pygame报错:is not a supported wheel on this platform._error: pygame-2.1.2-cp311-cp311-win_amd64.whl is n
- 6DL-1-week2-Basics of Neural Network Programming_week 2 quiz - neural network basics
- 7VUE3使用wavesurfer.js_vue3 wavesurfer.js
- 8spine介绍和在Unity里面的应用_unity spine
- 9WPF学习之绘图和动画_shader effects buildtask and templates
- 10Python Turtle 初学者指南_turtle.bgcolor
当前位置: article > 正文
实践练习3：使用OBD部署一个三副本OceanBase社区版集群(离线部署)_oceanbase 社区版3个机器
作者：从前慢现在也慢 | 2024-02-29 20:06:30
赞
踩
oceanbase 社区版3个机器
部署规划
本文实践练习手动部署 OceanBase 集群的三节点部署方法，该部署方法需要通过中控机直接远程登录到 OceanBase 节点上部署启动 observer 和 obproxy 进程。
采用三副本部署模式，推荐使用四台机器。其中：
-
使用一台机器部署 ODP。
-
使用三台机器部署 OceanBase 集群。
机器信息
| 机器类型 | 虚拟机 |
|---|---|
| IP | 10.201.0.170, 10.201.0.171, 10.201.0.172, 10.201.0.173 |
| 网卡名 | eth0 |
| OS | CentOS Linux release 7.9.2009 |
| CPU | 4C |
| 内存 | 总内存 16G，可用内存 15G |
| 磁盘1 | 磁盘 /dev/vda 200G |
| 磁盘2 | 磁盘 /dev/vdb 300G |
机器划分
| 角色 | 机器 | 备注 |
|---|---|---|
| OBD | 10.201.0.170 | 中控机，自动化部署软件 |
| OBSERVER | 10.201.0.171 | OceanBase 数据库 zone1 |
| 10.201.0.172 | OceanBase 数据库 zone2 | |
| 10.201.0.173 | OceanBase 数据库 zone3 | |
| OBPROXY | 10.201.0.171 | OceanBase 访问反向代理 |
| 10.201.0.172 | OceanBase 访问反向代理 | |
| 10.201.0.173 | OceanBase 访问反向代理 | |
| OBCLIENT | 10.201.0.170 | OceanBase 命令行客户端 |
初始化服务器环境
安装RPM包
cd /etc/yum.repos.d/ mv *.repo /root/ vi iso.repo [iso] name=iso baseurl=file:///media enable=1 gpgcheck=0 yum clean all yum makecache yum repolist yum -y install nfs tree net-tools yum -y install mariadb-libs.x86_64 mariadb.x86_64 [root@obdeployer ~]# rpm -qa|grep maria mariadb-libs-5.5.68-1.el7.x86_64 mariadb-5.5.68-1.el7.x86_64
内核参数修改
修改配置文件：
vim /etc/sysctl.conf net.core.somaxconn = 2048 net.core.netdev_max_backlog = 10000 net.core.rmem_default = 16777216 net.core.wmem_default = 16777216 net.core.rmem_max = 16777216 net.core.wmem_max = 16777216 net.ipv4.ip_local_port_range = 3500 65535 net.ipv4.ip_forward = 0 net.ipv4.conf.default.rp_filter = 1 net.ipv4.conf.default.accept_source_route = 0 net.ipv4.tcp_syncookies = 0 net.ipv4.tcp_rmem = 4096 87380 16777216 net.ipv4.tcp_wmem = 4096 65536 16777216 net.ipv4.tcp_max_syn_backlog = 16384 net.ipv4.tcp_fin_timeout = 15 net.ipv4.tcp_max_syn_backlog = 16384 net.ipv4.tcp_tw_reuse = 1 net.ipv4.tcp_tw_recycle = 1 net.ipv4.tcp_slow_start_after_idle=0 vm.swappiness = 0 vm.min_free_kbytes = 2097152 vm.max_map_count=655360 fs.aio-max-nr=1048576
运行以下命令可让配置生效：
sysctl -p
修改会话变量设置
更改配置文件
您可将会话级别的最大栈空间大小设置为 unlimited，最大文件句柄数设置为 655350，Core 文件大小设置为 unlimited 。 如果已有设置值低于这个设置值，则按照下述命令修改 /etc/security/limits.conf 配置文件。
vi /etc/security/limits.conf * soft nofile 655360 * hard nofile 655360 * soft nproc 655360 * hard nproc 655360 * soft core unlimited * hard core unlimited * soft stack unlimited * hard stack unlimited
查看配置
您可退出当前会话，重新登录。执行以下命令，查看配置是否生效：
ulimit -a
关闭防火墙和 SELinux
查看防火墙状态。
systemctl status firewalld
如果当前防火墙状态为 inactive，则不需要关注。若当前防火墙状态为 active，则需要永久关闭。
systemctl disable firewalld systemctl stop firewalld systemctl status firewalld
关闭 SELinux
修改 SELinux 配置文件中的 SELINUX 选项。
vi /etc/selinux/config # This file controls the state of SELinux on the system. # SELINUX= can take one of these three values: # enforcing - SELinux security policy is enforced. # permissive - SELinux prints warnings instead of enforcing. # disabled - No SELinux policy is loaded. SELINUX=disabled
配置文件修改后需等到重启主机后才可生效
配置时间同步服务
安装 chrony 服务
这里采用 YUM 安装方法。您也可以下载相应的 RPM 包安装。
yum -y install chrony
chrony 配置说明
chrony 服务守护进程名为 chronyd，chronyc 是用来监控 chronyd 性能和配置参数的命令行工具。 chrony 的主配置文件为 /etc/chrony.conf 。配置方法如下：
vi /etc/chrony.conf # server 后面跟时间同步服务器 server 10.201.0.101 minpoll 4 maxpoll 10 iburst server 10.201.0.110 minpoll 4 maxpoll 10 iburst # 根据实际时间计算出服务器增减时间的比率，然后记录到一个文件中，在系统重启后为系统做出最佳时间补偿调整。 driftfile /var/lib/chrony/drift # chronyd 根据需求减慢或加速时间调整， makestep 1.0 3 # 将启用一个内核模式，在该模式中，系统时间每 11 分钟会拷贝到实时时钟（RTC）。 rtcsync # 即使没有同步到时间源，也要服务时间 local stratum 10 # 指定日志文件的目录。 logdir /var/log/chrony
最简单的配置文件如下：
server 10.201.0.101 minpoll 4 maxpoll 10 iburst server 10.201.0.110 minpoll 4 maxpoll 10 iburst local stratum 10
常用命令
使用 chrony 时间服务是为了保证 OceanBase 集群各个节点时间尽可能同步，下面这些命令供参考。具体使用请查看 chrony 官方使用说明：Chronyc Frequently Asked Questions
查看时间同步活动 chronyc activity 查看时间服务器 chronyc sources 查看同步状态 chronyc sources -v 校准时间服务器： chronyc tracking
配置安装用户
前文分析过，建议安装部署在普通用户下，后文均以用户 admin 为例。
注意: 给 admin 用户赋与 sudo 权限不是必须的，只是为了某些时候方便操作。您可以结合企业安全规范决定是否执行。
下面是创建用户 admin 并授予 sudo 权限的方法，仅供参考。
# 新增普通用户 admin useradd admin # 修改用户密码 passwd admin # 或运行下面命令指定密码，密码修改为自己的。 echo "pass4adm" |passwd admin --stdin
在 CentOS 上面给 admin 用户增加 sodu 权限有以下两个方法：
yum install -y sudo admin 添加到 /etc/sudoers 文件中 [root@obce00 ~]# cat /etc/sudoers |grep wheel ## Allows people in group wheel to run all commands %wheel ALL=(ALL) ALL # %wheel ALL=(ALL) NOPASSWD: ALL vim /etc/sudoers ## Allow root to run any commands anywhere root ALL=(ALL) ALL ---添加以下内容 admin ALL=(ALL) ALL
验证方法是否生效，切换到 admin 用户下，执行命令：sudo date 。输入密码后查看返回结果。
[root@obdelployer ~]# su - admin [admin@obdelployer ~]$ sudo date 我们信任您已经从系统管理员那里了解了日常注意事项。 总结起来无外乎这三点： #1) 尊重别人的隐私。 #2) 输入前要先考虑(后果和风险)。 #3) 权力越大，责任越大。 [sudo] admin 的密码： 2022年 03月 21日 星期一 16:54:04 CST [admin@obdelployer ~]$ sudo date 2022年 03月 21日 星期一 16:54:06 CST
磁盘文件系统划分
[root@obdeployer ~]# pvcreate /dev/vdb Physical volume "/dev/vdb" successfully created. [root@obdeployer ~]# vgcreate obvg /dev/vdb Volume group "obvg" successfully created [root@obdeployer ~]# lvcreate -L 60G obvg -n lvredo Logical volume "lvredo" created. [root@obdeployer ~]# lvcreate -l 100%FREE obvg -n lvdata Logical volume "lvdata" created. [root@obdeployer ~]# vgdisplay obvg -v --- Volume group --- VG Name obvg System ID Format lvm2 Metadata Areas 1 Metadata Sequence No 3 VG Access read/write VG Status resizable MAX LV 0 Cur LV 2 Open LV 0 Max PV 0 Cur PV 1 Act PV 1 VG Size <300.00 GiB PE Size 4.00 MiB Total PE 76799 Alloc PE / Size 76799 / <300.00 GiB Free PE / Size 0 / 0 VG UUID yiGTQI-j6KB-0HpV-93DZ-GXB1-sviX-7CIvv7 --- Logical volume --- LV Path /dev/obvg/lvredo LV Name lvredo VG Name obvg LV UUID myZEjz-V8eg-zX6q-AgfK-VjNE-4zC1-FMHOPf LV Write Access read/write LV Creation host, time obdeployer, 2022-03-21 17:17:06 +0800 LV Status available # open 0 LV Size 60.00 GiB Current LE 15360 Segments 1 Allocation inherit Read ahead sectors auto - currently set to 8192 Block device 253:2 --- Logical volume --- LV Path /dev/obvg/lvdata LV Name lvdata VG Name obvg LV UUID PebAQN-nUFe-5DLC-ofx3-irN0-FTFg-3DI2Bp LV Write Access read/write LV Creation host, time obdeployer, 2022-03-21 17:17:17 +0800 LV Status available # open 0 LV Size <240.00 GiB Current LE 61439 Segments 1 Allocation inherit Read ahead sectors auto - currently set to 8192 Block device 253:3 --- Physical volumes --- PV Name /dev/vdb PV UUID LDxzkr-WaPI-VyWU-v7pT-5mZV-O3S5-3qxLmm PV Status allocatable Total PE / Free PE 76799 / 0 [root@obdeployer ~]# mkfs.ext4 /dev/obvg/lvdata mke2fs 1.42.9 (28-Dec-2013) 文件系统标签= OS type: Linux 块大小=4096 (log=2) 分块大小=4096 (log=2) Stride=0 blocks, Stripe width=0 blocks 15728640 inodes, 62913536 blocks 3145676 blocks (5.00%) reserved for the super user 第一个数据块=0 Maximum filesystem blocks=2210398208 1920 block groups 32768 blocks per group, 32768 fragments per group 8192 inodes per group Superblock backups stored on blocks: 32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208, 4096000, 7962624, 11239424, 20480000, 23887872 Allocating group tables: 完成 正在写入inode表: 完成 Creating journal (32768 blocks): 完成 Writing superblocks and filesystem accounting information: 完成 [root@obdeployer ~]# mkfs.ext4 /dev/obvg/lvredo mke2fs 1.42.9 (28-Dec-2013) 文件系统标签= OS type: Linux 块大小=4096 (log=2) 分块大小=4096 (log=2) Stride=0 blocks, Stripe width=0 blocks 3932160 inodes, 15728640 blocks 786432 blocks (5.00%) reserved for the super user 第一个数据块=0 Maximum filesystem blocks=2164260864 480 block groups 32768 blocks per group, 32768 fragments per group 8192 inodes per group Superblock backups stored on blocks: 32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208, 4096000, 7962624, 11239424 Allocating group tables: 完成 正在写入inode表: 完成 Creating journal (32768 blocks): 完成 Writing superblocks and filesystem accounting information: 完成 [root@obdeployer ~]# mkdir -p /data /redo [root@obdeployer ~]# vim /etc/fstab # # /etc/fstab # Created by anaconda on Fri Mar 18 17:40:36 2022 # # Accessible filesystems, by reference, are maintained under '/dev/disk' # See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info # /dev/mapper/centos-root / xfs defaults 0 0 UUID=db13725b-2cf0-4dec-a0a7-a0951681a020 /boot xfs defaults 0 0 UUID=387C-FD55 /boot/efi vfat umask=0077,shortname=winnt 0 0 /dev/mapper/centos-swap swap swap defaults 0 0 /dev/obvg/lvredo /redo ext4 defaults,noatime,nodiratime,nodelalloc,barrier=0 0 0 /dev/obvg/lvdata /data ext4 defaults,noatime,nodiratime,nodelalloc,barrier=0 0 0 [root@obdeployer ~]# mount /data [root@obdeployer ~]# mount /redo [root@obdeployer ~]# df -h 文件系统 容量 已用 可用 已用% 挂载点 devtmpfs 7.9G 0 7.9G 0% /dev tmpfs 7.9G 0 7.9G 0% /dev/shm tmpfs 7.9G 43M 7.8G 1% /run tmpfs 7.9G 0 7.9G 0% /sys/fs/cgroup /dev/mapper/centos-root 180G 1.9G 179G 2% / /dev/vda2 1014M 150M 865M 15% /boot /dev/vda1 200M 12M 189M 6% /boot/efi tmpfs 1.6G 0 1.6G 0% /run/user/0 10.200.0.7:/mnt/resource 480G 298G 183G 62% /mnt /dev/loop0 9.5G 9.5G 0 100% /media /dev/mapper/obvg-lvdata 237G 61M 225G 1% /data /dev/mapper/obvg-lvredo 59G 53M 56G 1% /redo [root@obdeployer ~]# chown -R admin.admin /data /redo [root@obdeployer ~]# ll / 总用量 39 lrwxrwxrwx. 1 root root 7 3月 18 17:40 bin -> usr/bin dr-xr-xr-x. 5 root root 4096 3月 18 22:07 boot drwxr-xr-x. 3 admin admin 4096 3月 21 17:18 data drwxr-xr-x. 22 root root 3400 3月 21 17:17 dev drwxr-xr-x. 82 root root 8192 3月 21 17:20 etc drwxr-xr-x. 3 root root 19 3月 21 16:44 home lrwxrwxrwx. 1 root root 7 3月 18 17:40 lib -> usr/lib lrwxrwxrwx. 1 root root 9 3月 18 17:40 lib64 -> usr/lib64 drwxr-xr-x. 8 root root 2048 10月 30 2020 media drwxr-xr-x. 11 nobody nobody 11 3月 18 22:23 mnt drwxr-xr-x. 2 root root 6 4月 11 2018 opt dr-xr-xr-x. 143 root root 0 3月 18 22:02 proc drwxr-xr-x. 3 admin admin 4096 3月 21 17:19 redo dr-xr-x---. 3 root root 4096 3月 21 17:20 root drwxr-xr-x. 27 root root 840 3月 21 14:38 run lrwxrwxrwx. 1 root root 8 3月 18 17:40 sbin -> usr/sbin drwxr-xr-x. 2 root root 6 4月 11 2018 srv dr-xr-xr-x. 13 root root 0 3月 18 22:02 sys drwxrwxrwt. 8 root root 211 3月 21 17:03 tmp drwxr-xr-x. 13 root root 155 3月 18 17:40 usr drwxr-xr-x. 19 root root 267 3月 18 22:03 var
机器三节点之间时间同步检查
检查本机和目标节点时间误差常用命令是： clockdiff 。
示例：
[admin@obdeployer ~]$ sudo clockdiff 10.201.0.171 .. host=10.201.0.171 rtt=750(187)ms/0ms delta=0ms/0ms Tue Mar 22 09:51:32 2022 [admin@obdeployer ~]$ sudo clockdiff 10.201.0.172 .. host=10.201.0.172 rtt=563(280)ms/0ms delta=0ms/0ms Tue Mar 22 09:51:36 2022 [admin@obdeployer ~]$ sudo clockdiff 10.201.0.173 .. host=10.201.0.173 rtt=562(280)ms/0ms delta=0ms/0ms Tue Mar 22 09:51:39 2022
安装软件
安装OBD
离线安装 OBD
在登录中控机，进行安装。一般情况下应该是应用用户安装。可自行选择。
[admin@obdeployer ~]$ sudo rpm -ivh /mnt/db/OceanBase/ob-deploy-1.2.0-15.el7.x86_64.rpm [sudo] admin 的密码： 警告：/mnt/db/OceanBase/ob-deploy-1.2.0-15.el7.x86_64.rpm: 头V4 RSA/SHA1 Signature, 密钥 ID e9b4a7aa: NOKEY 准备中... ################################# [100%] 正在升级/安装... 1:ob-deploy-1.2.0-15.el7 ################################# [100%] Installation of obd finished successfully Please source /etc/profile.d/obd.sh to enable it [admin@obdeployer ~]$ which obd /bin/obd .
ob-deploy 软件默认安装在 /usr/obd 下。不同版本可能有点变化，您可通过下面命令查看位置。
[admin@obdeployer ~]$ ll /usr/obd/ 总用量 0 drwxr-xr-x 4 root root 43 3月 24 10:08 lib drwxr-xr-x 3 root root 20 3月 24 10:08 mirror drwxr-xr-x 11 root root 178 3月 24 10:08 plugins
执行obd命令前，还没有.obd目录
[admin@obdeployer ~]$ ll -a 总用量 48 drwx------. 4 admin admin 166 3月 24 10:05 . drwxr-xr-x. 3 root root 19 3月 21 16:44 .. -rw-------. 1 admin admin 847 3月 24 10:07 .bash_history -rw-r--r--. 1 admin admin 18 4月 1 2020 .bash_logout -rw-r--r--. 1 admin admin 193 4月 1 2020 .bash_profile -rw-r--r--. 1 admin admin 231 4月 1 2020 .bashrc -rw-rw-r-- 1 admin admin 5774 3月 24 10:05 obce-3zones+obproxy.yaml drwxrwxr-x 7 admin admin 4096 1月 7 19:59 obdeploy-master drwx------. 2 admin admin 80 3月 21 17:03 .ssh -rw------- 1 admin admin 17674 3月 24 10:05 .viminfo
检查安装是否成功。
[admin@obdeployer ~]$ obd cluster list Local deploy is empty
执行obd命令后，.obd目录就会产生
查看 .obd/ 目录结构
[admin@obdeployer ~]$ ll ~/.obd 总用量 4 drwxr-xr-x 2 admin admin 6 3月 24 10:08 cluster drwxr-xr-x 2 admin admin 20 3月 24 10:08 lock drwxr-xr-x 2 admin admin 17 3月 24 10:08 log drwxr-xr-x 3 admin admin 20 3月 24 10:08 mirror drwxr-xr-x 11 admin admin 178 3月 24 10:08 plugins -rw-rw-r-- 1 admin admin 5 3月 24 10:08 version
安装 OBLibs
---4台服务器都执行 yum -y install /mnt/db/OceanBase/oceanbase-ce-libs-3.1.2-10000392021123010.el7.x86_64.rpm
安装 OBClient
---4台服务器都执行 yum -y install /mnt/db/OceanBase/libobclient-2.0.0-2.el7.x86_64.rpm yum -y install /mnt/db/OceanBase/obclient-2.0.0-2.el7.x86_64.rpm
准备配置环境
禁用远程仓库
[admin@obdeployer ~]$ obd mirror list Update OceanBase-community-stable-el7 ok Update OceanBase-development-kit-el7 ok +------------------------------------------------------------------+ | Mirror Repository List | +----------------------------+--------+---------+------------------+ | SectionName | Type | Enabled | Update Time | +----------------------------+--------+---------+------------------+ | oceanbase.community.stable | remote | True | 2022-03-24 10:30 | | oceanbase.development-kit | remote | True | 2022-03-24 10:30 | | local | local | - | 2022-03-24 10:30 | +----------------------------+--------+---------+------------------+ [admin@obdeployer ~]$ obd mirror disable remote Disable remote ok [admin@obdeployer ~]$ obd mirror list +------------------------------------------------------------------+ | Mirror Repository List | +----------------------------+--------+---------+------------------+ | SectionName | Type | Enabled | Update Time | +----------------------------+--------+---------+------------------+ | oceanbase.community.stable | remote | False | 2022-03-24 10:30 | | oceanbase.development-kit | remote | False | 2022-03-24 10:30 | | local | local | - | 2022-03-24 10:31 | +----------------------------+--------+---------+------------------+
离线安装时，必须禁用远程仓库。或者删除远程仓库
/bin/rm -rf ~/.obd/mirror/remote/OceanBase.repo
配置本地仓库
使用下面命令将前面的软件包复制到本地仓库。
[admin@obdeployer ~]$ ll /mnt/db/OceanBase/ 总用量 3375908 -rw-r--r-- 1 nobody nobody 658620 3月 22 09:57 libobclient-2.0.0-2.el7.x86_64.rpm -rw-r--r-- 1 nobody nobody 8111800 3月 22 09:58 obagent-1.1.0-1.el7.x86_64.rpm -rw-r--r-- 1 nobody nobody 41916564 3月 22 10:00 obclient-2.0.0-2.el7.x86_64.rpm -rw-r--r-- 1 nobody nobody 21157644 3月 22 09:59 ob-deploy-1.2.0-15.el7.x86_64.rpm -rw-r--r-- 1 nobody nobody 300111 3月 23 16:33 obdeploy-master.zip -rw-r--r-- 1 nobody nobody 16090752 3月 22 09:58 ob-loader-dumper-3.0.0-RELEASE-ce.zip -rw-r--r-- 1 nobody nobody 1062976 3月 22 09:57 oblogproxy-1.0.0-1.el7.x86_64.rpm -rw-r--r-- 1 nobody nobody 8179432 3月 22 09:58 obproxy-3.2.0-1.el7.x86_64.rpm -rw-r--r-- 1 nobody nobody 48708456 3月 22 10:00 oceanbase-ce-3.1.2-10000392021123010.el7.x86_64.rpm -rw-r--r-- 1 nobody nobody 57361468 3月 22 10:01 oceanbase-ce-devel-3.1.2-10000392021123010.el7.x86_64.rpm -rw-r--r-- 1 nobody nobody 158948 3月 22 09:57 oceanbase-ce-libs-3.1.2-10000392021123010.el7.x86_64.rpm -rw-r--r-- 1 nobody nobody 48687968 3月 22 10:00 oceanbase-ce-utils-3.1.2-10000392021123010.el7.x86_64.rpm -rw-r--r-- 1 nobody nobody 257983296 3月 22 10:06 OceanBase Developer Center Setup 3.2.2-patch2 win64.exe -rw-r--r-- 1 nobody nobody 1242692622 3月 22 10:22 ocp-3.1.1-ce.tar.gz -rw-r--r-- 1 nobody nobody 2593184256 3月 22 10:33 oms-3.2.2-ce.tar.gz [admin@obdeployer ~]$ obd mirror clone /mnt/db/OceanBase/*.rpm name: libobclient version: 2.0.0 release:2.el7 arch: x86_64 md5: f73cae67e2ff5be0682ac2803aba33a7ed26430e add /mnt/db/OceanBase/libobclient-2.0.0-2.el7.x86_64.rpm to local mirror name: obagent version: 1.1.0 release:1.el7 arch: x86_64 md5: d2416fadeadba35944872467843d55da0999f298 add /mnt/db/OceanBase/obagent-1.1.0-1.el7.x86_64.rpm to local mirror name: obclient version: 2.0.0 release:2.el7 arch: x86_64 md5: 1d2c3ee31f40b9d2fbf97f653f549d896b7e7060 add /mnt/db/OceanBase/obclient-2.0.0-2.el7.x86_64.rpm to local mirror name: ob-deploy version: 1.2.0 release:15.el7 arch: x86_64 md5: b1cfa5fb95b6d7b06927e4ddbcdd7651f19311a9 add /mnt/db/OceanBase/ob-deploy-1.2.0-15.el7.x86_64.rpm to local mirror name: oblogproxy version: 1.0.0 release:1.el7 arch: x86_64 md5: e83fffb80f5cd848d299809850a0dcf3e95a5a00 add /mnt/db/OceanBase/oblogproxy-1.0.0-1.el7.x86_64.rpm to local mirror name: obproxy version: 3.2.0 release:1.el7 arch: x86_64 md5: 8d5c6978f988935dc3da1dbec208914668dcf3b2 add /mnt/db/OceanBase/obproxy-3.2.0-1.el7.x86_64.rpm to local mirror name: oceanbase-ce version: 3.1.2 release:10000392021123010.el7 arch: x86_64 md5: 7fafba0fac1e90cbd1b5b7ae5fa129b64dc63aed add /mnt/db/OceanBase/oceanbase-ce-3.1.2-10000392021123010.el7.x86_64.rpm to local mirror name: oceanbase-ce-devel version: 3.1.2 release:10000392021123010.el7 arch: x86_64 md5: bd3a33a83fca424a41af01264d1bbc6f2ef1e7aa add /mnt/db/OceanBase/oceanbase-ce-devel-3.1.2-10000392021123010.el7.x86_64.rpm to local mirror name: oceanbase-ce-libs version: 3.1.2 release:10000392021123010.el7 arch: x86_64 md5: 94fff0ab31de053051dba66039e3185fa390cad5 add /mnt/db/OceanBase/oceanbase-ce-libs-3.1.2-10000392021123010.el7.x86_64.rpm to local mirror name: oceanbase-ce-utils version: 3.1.2 release:10000392021123010.el7 arch: x86_64 md5: 6ca7db146fee526f4201508f9bd30901e487b7c5 add /mnt/db/OceanBase/oceanbase-ce-utils-3.1.2-10000392021123010.el7.x86_64.rpm to local mirror
查看仓库的 RPM 列表。
[admin@obdeployer ~]$ obd mirror list local +----------------------------------------------------------------------------------------------------------+ | local Package List | +--------------------+---------+-----------------------+--------+------------------------------------------+ | name | version | release | arch | md5 | +--------------------+---------+-----------------------+--------+------------------------------------------+ | libobclient | 2.0.0 | 2.el7 | x86_64 | f73cae67e2ff5be0682ac2803aba33a7ed26430e | | obagent | 1.1.0 | 1.el7 | x86_64 | d2416fadeadba35944872467843d55da0999f298 | | obclient | 2.0.0 | 2.el7 | x86_64 | 1d2c3ee31f40b9d2fbf97f653f549d896b7e7060 | | ob-deploy | 1.2.0 | 15.el7 | x86_64 | b1cfa5fb95b6d7b06927e4ddbcdd7651f19311a9 | | oblogproxy | 1.0.0 | 1.el7 | x86_64 | e83fffb80f5cd848d299809850a0dcf3e95a5a00 | | obproxy | 3.2.0 | 1.el7 | x86_64 | 8d5c6978f988935dc3da1dbec208914668dcf3b2 | | oceanbase-ce | 3.1.2 | 10000392021123010.el7 | x86_64 | 7fafba0fac1e90cbd1b5b7ae5fa129b64dc63aed | | oceanbase-ce-devel | 3.1.2 | 10000392021123010.el7 | x86_64 | bd3a33a83fca424a41af01264d1bbc6f2ef1e7aa | | oceanbase-ce-libs | 3.1.2 | 10000392021123010.el7 | x86_64 | 94fff0ab31de053051dba66039e3185fa390cad5 | | oceanbase-ce-utils | 3.1.2 | 10000392021123010.el7 | x86_64 | 6ca7db146fee526f4201508f9bd30901e487b7c5 | +--------------------+---------+-----------------------+--------+------------------------------------------+
编辑 OBD 配置文件
部署三节点 observer 和 obproxy 进程,下载该配置文件：
https://github.com/oceanbase/obdeploy/blob/master/example/mini-distributed-with-obproxy-example.yaml
编辑配置文件
[admin@obdeployer ~]$ vim obce-3zones+obproxy.yaml ## Only need to configure when remote login is required user: username: admin # password: your password if need key_file: /home/admin/.ssh/id_rsa.pub port: 22 # timeout: ssh connection timeout (second), default 30 oceanbase-ce: servers: - name: observer01 # Please don't use hostname, only IP can be supported ip: 10.201.0.171 - name: observer02 ip: 10.201.0.172 - name: observer03 ip: 10.201.0.173 global: # Please set devname as the network adaptor's name whose ip is in the setting of severs. # if set severs as "127.0.0.1", please set devname as "lo" # if current ip is 192.168.1.10, and the ip's network adaptor's name is "eth0", please use "eth0" devname: eth0 cluster_id: 1 # please set memory limit to a suitable value which is matching resource. memory_limit: 10G # The maximum running memory for an observer system_memory: 3G # The reserved system memory. system_memory is reserved for general tenants. The default value is 30G. stack_size: 512K cpu_count: 8 cache_wash_threshold: 1G __min_full_resource_pool_memory: 268435456 workers_per_cpu_quota: 10 schema_history_expire_time: 1d # The value of net_thread_count had better be same as cpu's core number. net_thread_count: 4 major_freeze_duty_time: Disable minor_freeze_times: 10 enable_separate_sys_clog: 0 enable_merge_by_turn: FALSE #datafile_disk_percentage: 20 # The percentage of the data_dir space to the total disk space. This value takes effect only when datafile_size is 0. The default value is 90. datafile_size: 50G syslog_level: INFO # System log level. The default value is INFO. enable_syslog_wf: false # Print system logs whose levels are higher than WARNING to a separate log file. The default value is true. enable_syslog_recycle: true # Enable auto system log recycling or not. The default value is false. max_syslog_file_count: 10 # The maximum number of reserved log files before enabling auto recycling. The default value is 0. # observer cluster name, consistent with obproxy's cluster_name appname: obce-3zones root_password: rootPWD123 # root user password, can be empty proxyro_password: proxyPWD123 # proxyro user pasword, consistent with obproxy's observer_sys_password, can be empty observer01: mysql_port: 2881 # External port for OceanBase Database. The default value is 2881. DO NOT change this value after the cluster is started. rpc_port: 2882 # Internal port for OceanBase Database. The default value is 2882. DO NOT change this value after the cluster is started. # The working directory for OceanBase Database. OceanBase Database is started under this directory. This is a required field. home_path: /home/admin/oceanbase-ce # The directory for data storage. The default value is $home_path/store. data_dir: /data # The directory for clog, ilog, and slog. The default value is the same as the data_dir value. redo_dir: /redo zone: zone1 observer02: mysql_port: 2881 # External port for OceanBase Database. The default value is 2881. DO NOT change this value after the cluster is started. rpc_port: 2882 # Internal port for OceanBase Database. The default value is 2882. DO NOT change this value after the cluster is started. # The working directory for OceanBase Database. OceanBase Database is started under this directory. This is a required field. home_path: /home/admin/oceanbase-ce # The directory for data storage. The default value is $home_path/store. data_dir: /data # The directory for clog, ilog, and slog. The default value is the same as the data_dir value. redo_dir: /redo zone: zone2 observer03: mysql_port: 2881 # External port for OceanBase Database. The default value is 2881. DO NOT change this value after the cluster is started. rpc_port: 2882 # Internal port for OceanBase Database. The default value is 2882. DO NOT change this value after the cluster is started. # The working directory for OceanBase Database. OceanBase Database is started under this directory. This is a required field. home_path: /home/admin/oceanbase-ce # The directory for data storage. The default value is $home_path/store. data_dir: /data # The directory for clog, ilog, and slog. The default value is the same as the data_dir value. redo_dir: /redo zone: zone3 obproxy: # Set dependent components for the component. # When the associated configurations are not done, OBD will automatically get the these configurations from the dependent components. depends: - oceanbase-ce servers: - 10.201.0.171 - 10.201.0.172 - 10.201.0.173 global: listen_port: 2883 # External port. The default value is 2883. prometheus_listen_port: 2884 # The Prometheus port. The default value is 2884. home_path: /home/admin/obproxy # oceanbase root server list # format: ip:mysql_port;ip:mysql_port. When a depends exists, OBD gets this value from the oceanbase-ce of the depends. rs_list: 10.201.0.171:2881;10.201.0.172:2881;10.201.0.173:2881 enable_cluster_checkout: false # observer cluster name, consistent with oceanbase-ce's appname. When a depends exists, OBD gets this value from the oceanbase-ce of the depends. cluster_name: obce-3zones skip_proxy_sys_private_check: true obproxy_sys_password: obpPWD123 # obproxy sys user password, can be empty. When a depends exists, OBD gets this value from the oceanbase-ce of the depends. observer_sys_password: proxyPWD123 # proxyro user pasword, consistent with oceanbase-ce's proxyro_password, can be empty. When a depends exists, OBD gets this value from the oceanbase-ce of the depends.
部署成功后，OBD 会将配置文件 obce-3zones+obproxy.yaml 复制到自己的工作目录里（~/.obd/cluster/obce-3zones/config.yaml ），后期再对 obce-3zones.yaml 文件进行修改是不生效的。
安装 OceanBase 数据库和 ODP
配置文件准备好后，就可以部署该配置文件对应的集群，部署内容主要包含：
-
复制软件到相应节点，并安装软件。
-
在相应节点创建相关目录。
部署集群
[admin@obdeployer ~]$ obd cluster deploy obce-3zones -c obce-3zones+obproxy.yaml Package oceanbase-ce-3.1.2 is available. Package obproxy-3.2.0 is available. install oceanbase-ce-3.1.2 for local ok install obproxy-3.2.0 for local ok +-------------------------------------------------------------------------------------------+ | Packages | +--------------+---------+-----------------------+------------------------------------------+ | Repository | Version | Release | Md5 | +--------------+---------+-----------------------+------------------------------------------+ | oceanbase-ce | 3.1.2 | 10000392021123010.el7 | 7fafba0fac1e90cbd1b5b7ae5fa129b64dc63aed | | obproxy | 3.2.0 | 1.el7 | 8d5c6978f988935dc3da1dbec208914668dcf3b2 | +--------------+---------+-----------------------+------------------------------------------+ Repository integrity check ok Parameter check ok Open ssh connection ok Remote oceanbase-ce-3.1.2-7fafba0fac1e90cbd1b5b7ae5fa129b64dc63aed repository install ok Remote oceanbase-ce-3.1.2-7fafba0fac1e90cbd1b5b7ae5fa129b64dc63aed repository lib check !! [WARN] observer01(10.201.0.171) oceanbase-ce-3.1.2-7fafba0fac1e90cbd1b5b7ae5fa129b64dc63aed require: libmariadb.so.3 [WARN] observer02(10.201.0.172) oceanbase-ce-3.1.2-7fafba0fac1e90cbd1b5b7ae5fa129b64dc63aed require: libmariadb.so.3 [WARN] observer03(10.201.0.173) oceanbase-ce-3.1.2-7fafba0fac1e90cbd1b5b7ae5fa129b64dc63aed require: libmariadb.so.3 Remote obproxy-3.2.0-8d5c6978f988935dc3da1dbec208914668dcf3b2 repository install ok Remote obproxy-3.2.0-8d5c6978f988935dc3da1dbec208914668dcf3b2 repository lib check ok Try to get lib-repository Package oceanbase-ce-libs-3.1.2 is available. install oceanbase-ce-libs-3.1.2 for local ok Use oceanbase-ce-libs-3.1.2-94fff0ab31de053051dba66039e3185fa390cad5 for oceanbase-ce-3.1.2-7fafba0fac1e90cbd1b5b7ae5fa129b64dc63aed Remote oceanbase-ce-libs-3.1.2-94fff0ab31de053051dba66039e3185fa390cad5 repository install ok Remote oceanbase-ce-3.1.2-7fafba0fac1e90cbd1b5b7ae5fa129b64dc63aed repository lib check ok Cluster status check ok Initializes observer work home ok Initializes obproxy work home ok obce-3zones deployed
查看部署后的集群状态
[admin@obdeployer ~]$ obd cluster list +----------------------------------------------------------------------+ | Cluster List | +-------------+--------------------------------------+-----------------+ | Name | Configuration Path | Status (Cached) | +-------------+--------------------------------------+-----------------+ | obce-3zones | /home/admin/.obd/cluster/obce-3zones | deployed | +-------------+--------------------------------------+-----------------+
查看目录结构
[admin@observer01 ~]$ tree oceanbase-ce/ oceanbase-ce/ ├── admin ├── bin │ └── observer -> /home/admin/.obd/repository/oceanbase-ce/3.1.2/7fafba0fac1e90cbd1b5b7ae5fa129b64dc63aed/bin/observer ├── etc ├── lib │ ├── libaio.so -> /home/admin/.obd/repository/oceanbase-ce/3.1.2/7fafba0fac1e90cbd1b5b7ae5fa129b64dc63aed/lib/libaio.so │ ├── libaio.so.1 -> /home/admin/.obd/repository/oceanbase-ce/3.1.2/7fafba0fac1e90cbd1b5b7ae5fa129b64dc63aed/lib/libaio.so.1 │ ├── libaio.so.1.0.1 -> /home/admin/.obd/repository/oceanbase-ce/3.1.2/7fafba0fac1e90cbd1b5b7ae5fa129b64dc63aed/lib/libaio.so.1.0.1 │ ├── libmariadb.so -> /home/admin/.obd/repository/oceanbase-ce/3.1.2/7fafba0fac1e90cbd1b5b7ae5fa129b64dc63aed/lib/libmariadb.so │ └── libmariadb.so.3 -> /home/admin/.obd/repository/oceanbase-ce/3.1.2/7fafba0fac1e90cbd1b5b7ae5fa129b64dc63aed/lib/libmariadb.so.3 ├── log └── store -> /data 6 directories, 6 files
启动和初始化集群
OBD 启动和初始化集群
[admin@obdeployer ~]$ obd cluster start obce-3zones Get local repositories and plugins ok Open ssh connection ok Load cluster param plugin ok Check before start observer ok Check before start obproxy ok Start observer ok observer program health check ok Connect to observer ok Initialize cluster Cluster bootstrap ok Wait for observer init ok +------------------------------------------------+ | observer | +--------------+---------+------+-------+--------+ | ip | version | port | zone | status | +--------------+---------+------+-------+--------+ | 10.201.0.171 | 3.1.2 | 2881 | zone1 | active | | 10.201.0.172 | 3.1.2 | 2881 | zone2 | active | | 10.201.0.173 | 3.1.2 | 2881 | zone3 | active | +--------------+---------+------+-------+--------+ Start obproxy ok obproxy program health check ok Connect to obproxy ok Initialize cluster +------------------------------------------------+ | obproxy | +--------------+------+-----------------+--------+ | ip | port | prometheus_port | status | +--------------+------+-----------------+--------+ | 10.201.0.171 | 2883 | 2884 | active | | 10.201.0.172 | 2883 | 2884 | active | | 10.201.0.173 | 2883 | 2884 | active | +--------------+------+-----------------+--------+ obce-3zones running
查看启动后的集群状态
[admin@obdeployer ~]$ obd cluster list +----------------------------------------------------------------------+ | Cluster List | +-------------+--------------------------------------+-----------------+ | Name | Configuration Path | Status (Cached) | +-------------+--------------------------------------+-----------------+ | obce-3zones | /home/admin/.obd/cluster/obce-3zones | running | +-------------+--------------------------------------+-----------------+
检查 OceanBase 集群各个节点进程信息
[admin@obdeployer ~]$ IPS="10.201.0.171 10.201.0.172 10.201.0.173" [admin@obdeployer ~]$ for ob in $IPS;do echo $ob; ssh $ob "ps -ef | grep observer | grep -v grep "; done 10.201.0.171 admin 26872 1 99 11:15 ? 00:15:25 /home/admin/oceanbase-ce/bin/observer -r 10.201.0.171:2882:2881;10.201.0.172:2882:2881;10.201.0.173:2882:2881 -o __min_full_resource_pool_memory=268435456,memory_limit=10G,system_memory=3G,stack_size=512K,cpu_count=8,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=50G,enable_syslog_wf=False,enable_syslog_recycle=True,max_syslog_file_count=10 -z zone1 -p 2881 -P 2882 -n obce-3zones -c 1 -d /data -i eth0 -l INFO 10.201.0.172 admin 29089 1 99 11:15 ? 00:17:27 /home/admin/oceanbase-ce/bin/observer -r 10.201.0.171:2882:2881;10.201.0.172:2882:2881;10.201.0.173:2882:2881 -o __min_full_resource_pool_memory=268435456,memory_limit=10G,system_memory=3G,stack_size=512K,cpu_count=8,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=50G,enable_syslog_wf=False,enable_syslog_recycle=True,max_syslog_file_count=10 -z zone2 -p 2881 -P 2882 -n obce-3zones -c 1 -d /data -i eth0 -l INFO 10.201.0.173 admin 32334 1 99 11:15 ? 00:17:15 /home/admin/oceanbase-ce/bin/observer -r 10.201.0.171:2882:2881;10.201.0.172:2882:2881;10.201.0.173:2882:2881 -o __min_full_resource_pool_memory=268435456,memory_limit=10G,system_memory=3G,stack_size=512K,cpu_count=8,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=50G,enable_syslog_wf=False,enable_syslog_recycle=True,max_syslog_file_count=10 -z zone3 -p 2881 -P 2882 -n obce-3zones -c 1 -d /data -i eth0 -l INFO
从进程里看，可执行文件是 /home/admin/oceanbase-ce/bin/observer ，实际上它是个软链接。
[admin@observer01 ~]$ ll /home/admin/oceanbase-ce/bin/observer lrwxrwxrwx 1 admin admin 100 3月 24 10:44 /home/admin/oceanbase-ce/bin/observer -> /home/admin/.obd/repository/oceanbase-ce/3.1.2/7fafba0fac1e90cbd1b5b7ae5fa129b64dc63aed/bin/observer
检查 OceanBase 集群各个节点监听状况
[admin@obdeployer ~]$ IPS="10.201.0.171 10.201.0.172 10.201.0.173" [admin@obdeployer ~]$ for ob in $IPS;do echo $ob; ssh $ob "netstat -ntlp"; done 10.201.0.171 (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:2881 0.0.0.0:* LISTEN 26872/observer tcp 0 0 0.0.0.0:2882 0.0.0.0:* LISTEN 26872/observer tcp 0 0 0.0.0.0:2883 0.0.0.0:* LISTEN 28028/obproxy tcp 0 0 0.0.0.0:2884 0.0.0.0:* LISTEN 28028/obproxy tcp 0 0 0.0.0.0:111 0.0.0.0:* LISTEN - tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN - tcp6 0 0 :::111 :::* LISTEN - tcp6 0 0 :::22 :::* LISTEN - 10.201.0.172 (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:111 0.0.0.0:* LISTEN - tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN - tcp 0 0 0.0.0.0:2881 0.0.0.0:* LISTEN 29089/observer tcp 0 0 0.0.0.0:2882 0.0.0.0:* LISTEN 29089/observer tcp 0 0 0.0.0.0:2883 0.0.0.0:* LISTEN 30208/obproxy tcp 0 0 0.0.0.0:2884 0.0.0.0:* LISTEN 30208/obproxy tcp6 0 0 :::111 :::* LISTEN - tcp6 0 0 :::22 :::* LISTEN - 10.201.0.173 (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:2881 0.0.0.0:* LISTEN 32334/observer tcp 0 0 0.0.0.0:2882 0.0.0.0:* LISTEN 32334/observer tcp 0 0 0.0.0.0:2883 0.0.0.0:* LISTEN 33483/obproxy tcp 0 0 0.0.0.0:2884 0.0.0.0:* LISTEN 33483/obproxy tcp 0 0 0.0.0.0:111 0.0.0.0:* LISTEN - tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN - tcp6 0 0 :::111 :::* LISTEN - tcp6 0 0 :::22 :::* LISTEN -
连接 OceanBase
使用 observer 2881 端口登录
[admin@obdeployer ~]$ obclient -h 10.201.0.171 -uroot@sys -P2881 -prootPWD123 -c -A oceanbase Welcome to the OceanBase. Commands end with ; or \g. Your MySQL connection id is 3221604921 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]> exit Bye [admin@obdeployer ~]$ obclient -h 10.201.0.172 -uroot@sys -P2881 -ppass4obs -c -A oceanbase Welcome to the OceanBase. Commands end with ; or \g. Your MySQL connection id is 3221750804 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]> exit Bye [admin@obdeployer ~]$ obclient -h 10.201.0.173 -uroot@sys -P2881 -prootPWD123 -c -A oceanbase Welcome to the OceanBase. Commands end with ; or \g. Your MySQL connection id is 3222016725 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 | | SYS | | LBACSYS | | ORAAUDITOR | | test | +--------------------+ 7 rows in set (0.013 sec) MySQL [oceanbase]> exit Bye
使用 obproxy 2883 端口登录
[admin@obdeployer ~]$ obclient -h 10.201.0.171 -uroot@sys#obce-3zones -P2883 -prootPWD123 -c -A oceanbase Welcome to the OceanBase. Commands end with ; or \g. Your MySQL connection id is 1048578 Server version: 5.6.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]> exit Bye [admin@obdeployer ~]$ obclient -h 10.201.0.172 -uroot@sys#obce-3zones -P2883 -prootPWD123 -c -A oceanbase Welcome to the OceanBase. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.6.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]> exit Bye [admin@obdeployer ~]$ obclient -h 10.201.0.173 -uroot@sys#obce-3zones -P2883 -proot -c -A oceanbase Welcome to the OceanBase. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.6.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 parameters like 'cluster'; +-------+----------+--------------+----------+---------+-----------+-------------+---------------------+----------+---------+---------+-------------------+ | zone | svr_type | svr_ip | svr_port | name | data_type | value | info | section | scope | source | edit_level | +-------+----------+--------------+----------+---------+-----------+-------------+---------------------+----------+---------+---------+-------------------+ | zone3 | observer | 10.201.0.173 | 2882 | cluster | NULL | obce-3zones | Name of the cluster | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE | | zone1 | observer | 10.201.0.171 | 2882 | cluster | NULL | obce-3zones | Name of the cluster | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE | | zone2 | observer | 10.201.0.172 | 2882 | cluster | NULL | obce-3zones | Name of the cluster | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE | +-------+----------+--------------+----------+---------+-----------+-------------+---------------------+----------+---------+---------+-------------------+ 3 rows in set (0.022 sec) MySQL [oceanbase]> show databases; +--------------------+ | Database | +--------------------+ | oceanbase | | information_schema | | mysql | | SYS | | LBACSYS | | ORAAUDITOR | | test | +--------------------+ 7 rows in set (0.018 sec) MySQL [oceanbase]> exit Bye
创建业务租户、数据库及表
查看资源
查看OB集群所有节点信息
MySQL [oceanbase]> select zone,svr_ip,svr_port,inner_port,with_rootserver,status,gmt_create from __all_server order by zone, svr_ip; +-------+--------------+----------+------------+-----------------+--------+----------------------------+ | zone | svr_ip | svr_port | inner_port | with_rootserver | status | gmt_create | +-------+--------------+----------+------------+-----------------+--------+----------------------------+ | zone1 | 10.201.0.171 | 2882 | 2881 | 1 | active | 2022-03-24 11:15:34.824813 | | zone2 | 10.201.0.172 | 2882 | 2881 | 0 | active | 2022-03-24 11:15:33.865152 | | zone3 | 10.201.0.173 | 2882 | 2881 | 0 | active | 2022-03-24 11:15:33.838988 | +-------+--------------+----------+------------+-----------------+--------+----------------------------+ 3 rows in set (0.017 sec)
查看当前租户
MySQL [oceanbase]> show tenant; +---------------------+ | Current_tenant_name | +---------------------+ | sys | +---------------------+ 1 row in set (0.019 sec)
查询系统资源
MySQL [oceanbase]> select svr_ip,svr_port,cpu_total,mem_total/1024/1024/1024,disk_total/1024/1024/1024,zone from __all_virtual_server_stat; +--------------+----------+-----------+--------------------------+---------------------------+-------+ | svr_ip | svr_port | cpu_total | mem_total/1024/1024/1024 | disk_total/1024/1024/1024 | zone | +--------------+----------+-----------+--------------------------+---------------------------+-------+ | 10.201.0.171 | 2882 | 6 | 7.000000000000 | 50.000000000000 | zone1 | | 10.201.0.172 | 2882 | 6 | 7.000000000000 | 50.000000000000 | zone2 | | 10.201.0.173 | 2882 | 6 | 7.000000000000 | 50.000000000000 | zone3 | +--------------+----------+-----------+--------------------------+---------------------------+-------+ 3 rows in set (0.032 sec)
查询租户已分配资源
MySQL [oceanbase]> select sum(c.max_cpu),sum(c.max_memory)/1024/1024/1024 from __all_resource_pool as a,__all_unit_config as c where a.unit_config_id=c.unit_config_id; +----------------+----------------------------------+ | sum(c.max_cpu) | sum(c.max_memory)/1024/1024/1024 | +----------------+----------------------------------+ | 5 | 2.099999999627 | +----------------+----------------------------------+ 1 row in set (0.012 sec)
查看OceanBase集群可用资源情况
MySQL [oceanbase]> select a.zone,concat(a.svr_ip,':',a.svr_port) observer, cpu_total, (cpu_total-cpu_assigned) cpu_free, -> round(mem_total/1024/1024/1024) mem_total_gb, round((mem_total-mem_assigned)/1024/1024/1024) mem_free_gb, -> round(disk_total/1024/1024/1024) disk_total_gb, -> substr(a.build_version,1,6) version,usec_to_time(b.start_service_time) start_service_time -> from __all_virtual_server_stat a join __all_server b on (a.svr_ip=b.svr_ip and a.svr_port=b.svr_port) -> order by a.zone, a.svr_ip; +-------+-------------------+-----------+----------+--------------+-------------+---------------+---------+----------------------------+ | zone | observer | cpu_total | cpu_free | mem_total_gb | mem_free_gb | disk_total_gb | version | start_service_time | +-------+-------------------+-----------+----------+--------------+-------------+---------------+---------+----------------------------+ | zone1 | 10.201.0.171:2882 | 6 | 3.5 | 7 | 5 | 50 | 3.1.2_ | 2022-03-24 11:16:29.611772 | | zone2 | 10.201.0.172:2882 | 6 | 3.5 | 7 | 5 | 50 | 3.1.2_ | 2022-03-24 11:16:32.277955 | | zone3 | 10.201.0.173:2882 | 6 | 3.5 | 7 | 5 | 50 | 3.1.2_ | 2022-03-24 11:16:33.416943 | +-------+-------------------+-----------+----------+--------------+-------------+---------------+---------+----------------------------+ 3 rows in set (0.233 sec) MySQL [oceanbase]> select t1.name resource_pool_name, t2.`name` unit_config_name, t2.max_cpu, t2.min_cpu, -> round(t2.max_memory/1024/1024/1024) max_mem_gb, round(t2.min_memory/1024/1024/1024) min_mem_gb, -> t3.unit_id, t3.zone, concat(t3.svr_ip,':',t3.`svr_port`) observer,t4.tenant_id, t4.tenant_name -> from __all_resource_pool t1 join __all_unit_config t2 on (t1.unit_config_id=t2.unit_config_id) -> join __all_unit t3 on (t1.`resource_pool_id` = t3.`resource_pool_id`) -> left join __all_tenant t4 on (t1.tenant_id=t4.tenant_id) -> order by t1.`resource_pool_id`, t2.`unit_config_id`, t3.unit_id; +--------------------+------------------+---------+---------+------------+------------+---------+-------+-------------------+-----------+-------------+ | resource_pool_name | unit_config_name | max_cpu | min_cpu | max_mem_gb | min_mem_gb | unit_id | zone | observer | tenant_id | tenant_name | +--------------------+------------------+---------+---------+------------+------------+---------+-------+-------------------+-----------+-------------+ | sys_pool | sys_unit_config | 5 | 2.5 | 2 | 2 | 1 | zone1 | 10.201.0.171:2882 | 1 | sys | | sys_pool | sys_unit_config | 5 | 2.5 | 2 | 2 | 2 | zone2 | 10.201.0.172:2882 | 1 | sys | | sys_pool | sys_unit_config | 5 | 2.5 | 2 | 2 | 3 | zone3 | 10.201.0.173:2882 | 1 | sys | +--------------------+------------------+---------+---------+------------+------------+---------+-------+-------------------+-----------+-------------+ 3 rows in set (0.015 sec)
创建资源
创建资源单元
MySQL [oceanbase]> create resource unit my_test_unit max_cpu=1, min_cpu=1, max_memory='2G', min_memory='1G', max_iops=10000, min_iops=1000, max_session_num=1000000, max_disk_size='50G'; Query OK, 0 rows affected (0.378 sec) MySQL [oceanbase]> select unit_config_id,name,max_cpu,min_cpu,max_memory,min_memory,max_disk_size from __all_unit_config; +----------------+-----------------+---------+---------+------------+------------+---------------+ | unit_config_id | name | max_cpu | min_cpu | max_memory | min_memory | max_disk_size | +----------------+-----------------+---------+---------+------------+------------+---------------+ | 1 | sys_unit_config | 5 | 2.5 | 2254857830 | 1879048192 | 53687091200 | | 1002 | my_test_unit | 1 | 1 | 2147483648 | 1073741824 | 53687091200 | +----------------+-----------------+---------+---------+------------+------------+---------------+ 2 rows in set (0.004 sec)
创建资源池
MySQL [oceanbase]> create resource pool my_test_pool unit='my_test_unit', unit_num=1; Query OK, 0 rows affected (0.076 sec) MySQL [oceanbase]> SELECT unit_id,unit_config_id,unit_config_name,resource_pool_id,resource_pool_name,zone FROM oceanbase.gv$unit WHERE resource_pool_name='my_test_pool'; +---------+----------------+------------------+------------------+--------------------+-------+ | unit_id | unit_config_id | unit_config_name | resource_pool_id | resource_pool_name | zone | +---------+----------------+------------------+------------------+--------------------+-------+ | 1001 | 1002 | my_test_unit | 1002 | my_test_pool | zone1 | | 1002 | 1002 | my_test_unit | 1002 | my_test_pool | zone2 | | 1003 | 1002 | my_test_unit | 1002 | my_test_pool | zone3 | +---------+----------------+------------------+------------------+--------------------+-------+ 3 rows in set (0.058 sec)
创建普通租户
测试环境资源有限，使用SQL语句减配资源创建普通租户
MySQL [oceanbase]> select tenant_id,tenant_name,primary_zone from __all_tenant;
+-----------+-------------+-------------------+
| tenant_id | tenant_name | primary_zone |
+-----------+-------------+-------------------+
| 1 | sys | zone1;zone2,zone3 |
+-----------+-------------+-------------------+
1 row in set (0.012 sec)
MySQL [oceanbase]> create tenant my_test_obtenant resource_pool_list=('my_test_pool'), primary_zone='RANDOM',comment 'oceanbase tenant/instance', charset='utf8mb4' set ob_tcp_invited_nodes='%', ob_compatibility_mode='mysql';
Query OK, 0 rows affected (11.304 sec)
查看租户已分配资源
MySQL [oceanbase]> select svr_ip,svr_port,cpu_total,mem_total/1024/1024/1024,disk_total/1024/1024/1024,zone from __all_virtual_server_stat; +--------------+----------+-----------+--------------------------+---------------------------+-------+ | svr_ip | svr_port | cpu_total | mem_total/1024/1024/1024 | disk_total/1024/1024/1024 | zone | +--------------+----------+-----------+--------------------------+---------------------------+-------+ | 10.201.0.171 | 2882 | 6 | 7.000000000000 | 50.000000000000 | zone1 | | 10.201.0.172 | 2882 | 6 | 7.000000000000 | 50.000000000000 | zone2 | | 10.201.0.173 | 2882 | 6 | 7.000000000000 | 50.000000000000 | zone3 | +--------------+----------+-----------+--------------------------+---------------------------+-------+ 3 rows in set (0.004 sec) MySQL [oceanbase]> select sum(c.max_cpu),sum(c.max_memory)/1024/1024/1024 from __all_resource_pool as a,__all_unit_config as c where a.unit_config_id=c.unit_config_id; +----------------+----------------------------------+ | sum(c.max_cpu) | sum(c.max_memory)/1024/1024/1024 | +----------------+----------------------------------+ | 6 | 4.099999999627 | +----------------+----------------------------------+ 1 row in set (0.003 sec) MySQL [oceanbase]> select a.zone,concat(a.svr_ip,':',a.svr_port) observer, cpu_total, (cpu_total-cpu_assigned) cpu_free, -> round(mem_total/1024/1024/1024) mem_total_gb, round((mem_total-mem_assigned)/1024/1024/1024) mem_free_gb, -> round(disk_total/1024/1024/1024) disk_total_gb, -> substr(a.build_version,1,6) version,usec_to_time(b.start_service_time) start_service_time -> from __all_virtual_server_stat a join __all_server b on (a.svr_ip=b.svr_ip and a.svr_port=b.svr_port) -> order by a.zone, a.svr_ip; +-------+-------------------+-----------+----------+--------------+-------------+---------------+---------+----------------------------+ | zone | observer | cpu_total | cpu_free | mem_total_gb | mem_free_gb | disk_total_gb | version | start_service_time | +-------+-------------------+-----------+----------+--------------+-------------+---------------+---------+----------------------------+ | zone1 | 10.201.0.171:2882 | 6 | 2.5 | 7 | 4 | 50 | 3.1.2_ | 2022-03-24 11:16:29.611772 | | zone2 | 10.201.0.172:2882 | 6 | 2.5 | 7 | 4 | 50 | 3.1.2_ | 2022-03-24 11:16:32.277955 | | zone3 | 10.201.0.173:2882 | 6 | 2.5 | 7 | 4 | 50 | 3.1.2_ | 2022-03-24 11:16:33.416943 | +-------+-------------------+-----------+----------+--------------+-------------+---------------+---------+----------------------------+ 3 rows in set (0.181 sec) MySQL [oceanbase]> select t1.name resource_pool_name, t2.`name` unit_config_name, t2.max_cpu, t2.min_cpu, -> round(t2.max_memory/1024/1024/1024) max_mem_gb, round(t2.min_memory/1024/1024/1024) min_mem_gb, -> t3.unit_id, t3.zone, concat(t3.svr_ip,':',t3.`svr_port`) observer,t4.tenant_id, t4.tenant_name -> from __all_resource_pool t1 join __all_unit_config t2 on (t1.unit_config_id=t2.unit_config_id) -> join __all_unit t3 on (t1.`resource_pool_id` = t3.`resource_pool_id`) -> left join __all_tenant t4 on (t1.tenant_id=t4.tenant_id) -> order by t1.`resource_pool_id`, t2.`unit_config_id`, t3.unit_id; +--------------------+------------------+---------+---------+------------+------------+---------+-------+-------------------+-----------+------------------+ | resource_pool_name | unit_config_name | max_cpu | min_cpu | max_mem_gb | min_mem_gb | unit_id | zone | observer | tenant_id | tenant_name | +--------------------+------------------+---------+---------+------------+------------+---------+-------+-------------------+-----------+------------------+ | sys_pool | sys_unit_config | 5 | 2.5 | 2 | 2 | 1 | zone1 | 10.201.0.171:2882 | 1 | sys | | sys_pool | sys_unit_config | 5 | 2.5 | 2 | 2 | 2 | zone2 | 10.201.0.172:2882 | 1 | sys | | sys_pool | sys_unit_config | 5 | 2.5 | 2 | 2 | 3 | zone3 | 10.201.0.173:2882 | 1 | sys | | my_test_pool | my_test_unit | 1 | 1 | 2 | 1 | 1001 | zone1 | 10.201.0.171:2882 | 1001 | my_test_obtenant | | my_test_pool | my_test_unit | 1 | 1 | 2 | 1 | 1002 | zone2 | 10.201.0.172:2882 | 1001 | my_test_obtenant | | my_test_pool | my_test_unit | 1 | 1 | 2 | 1 | 1003 | zone3 | 10.201.0.173:2882 | 1001 | my_test_obtenant | +--------------------+------------------+---------+---------+------------+------------+---------+-------+-------------------+-----------+------------------+ 6 rows in set (0.004 sec)
管理普通租户
查看租户信息
MySQL [oceanbase]> select tenant_id,tenant_name,primary_zone from __all_tenant;
+-----------+------------------+-------------------+
| tenant_id | tenant_name | primary_zone |
+-----------+------------------+-------------------+
| 1 | sys | zone1;zone2,zone3 |
| 1001 | my_test_obtenant | RANDOM |
+-----------+------------------+-------------------+
2 rows in set (0.005 sec)
MySQL [oceanbase]> select * from oceanbase.gv$tenant;
+-----------+------------------+-------------------+-------------------+----------------+---------------------------+-----------+---------------------------------------------+
| tenant_id | tenant_name | zone_list | primary_zone | collation_type | info | read_only | locality |
+-----------+------------------+-------------------+-------------------+----------------+---------------------------+-----------+---------------------------------------------+
| 1 | sys | zone1;zone2;zone3 | zone1;zone2,zone3 | 0 | system tenant | 0 | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 |
| 1001 | my_test_obtenant | zone1;zone2;zone3 | RANDOM | 0 | oceanbase tenant/instance | 0 | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 |
+-----------+------------------+-------------------+-------------------+----------------+---------------------------+-----------+---------------------------------------------+
2 rows in set (0.039 sec)
登录新创建的普通租户
[admin@obdeployer ~]$ obclient -h 10.201.0.173 -uroot@my_test_obtenant#obce-3zones -P2883 -c -A oceanbase Welcome to the OceanBase. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.6.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密码
MySQL [oceanbase]> select user, host, authentication_string,password_expired from mysql.user; +------------+------+-----------------------+------------------+ | user | host | authentication_string | password_expired | +------------+------+-----------------------+------------------+ | root | % | | | | ORAAUDITOR | % | | | +------------+------+-----------------------+------------------+ 2 rows in set (0.062 sec) MySQL [oceanbase]> alter user root identified by 'rootPWD123' ; Query OK, 0 rows affected (0.189 sec) MySQL [oceanbase]> exit Bye [admin@obdeployer ~]$ obclient -h 10.201.0.173 -uroot@my_test_obtenant#obce-3zones -P2883 -prootPWD123 -c -A oceanbase Welcome to the OceanBase. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 5.6.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]>
创建数据库及用户
[admin@obdeployer ~]$ obclient -h 10.201.0.173 -uroot@my_test_obtenant#obce-3zones -P2883 -prootPWD123 -c -A oceanbase Welcome to the OceanBase. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 5.6.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.017 sec) MySQL [oceanbase]> create database mytestdb ; Query OK, 1 row affected (0.150 sec) MySQL [oceanbase]> show databases; +--------------------+ | Database | +--------------------+ | oceanbase | | information_schema | | mysql | | test | | mytestdb | +--------------------+ 5 rows in set (0.065 sec) MySQL [oceanbase]> create user mytestuser@'%' identified by 'userPWD123' ; Query OK, 0 rows affected (0.243 sec) MySQL [oceanbase]> grant all privileges on *.* to mytestuser@'%'; Query OK, 0 rows affected (0.138 sec) MySQL [oceanbase]> select user, host, authentication_string,password_expired from mysql.user; +------------+------+-----------------------+------------------+ | user | host | authentication_string | password_expired | +------------+------+-----------------------+------------------+ | root | % | | | | ORAAUDITOR | % | | | | mytestuser | % | | | +------------+------+-----------------------+------------------+ 3 rows in set (0.321 sec) MySQL [oceanbase]> show grants for mytestuser; +---------------------------------------------+ | Grants for mytestuser@% | +---------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'mytestuser' | +---------------------------------------------+ 1 row in set (0.057 sec)
创建业务表
[admin@obdeployer ~]$ obclient -h 10.201.0.171 -umytestuser@my_test_obtenant#obce-3zones -P2883 -puserPWD123 -c -A mytestdb
Welcome to the OceanBase. Commands end with ; or \g.
Your MySQL connection id is 1048581
Server version: 5.6.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 [mytestdb]> show database;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'database' at line 1
MySQL [mytestdb]> select database();
+------------+
| database() |
+------------+
| mytestdb |
+------------+
1 row in set (0.004 sec)
MySQL [mytestdb]> show tables;
Empty set (0.038 sec)
MySQL [mytestdb]> CREATE TABLE `country` (
-> `Code` char(3) NOT NULL DEFAULT '',
-> `Name` char(52) NOT NULL DEFAULT '',
-> `Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia', -> `Region` char(26) NOT NULL DEFAULT '',
-> `SurfaceArea` decimal(10,2) NOT NULL DEFAULT '0.00',
-> `IndepYear` smallint DEFAULT NULL,
-> `Population` int NOT NULL DEFAULT '0',
-> `LifeExpectancy` decimal(3,1) DEFAULT NULL,
-> `GNP` decimal(10,2) DEFAULT NULL,
-> `GNPOld` decimal(10,2) DEFAULT NULL,
-> `LocalName` char(45) NOT NULL DEFAULT '',
-> `GovernmentForm` char(45) NOT NULL DEFAULT '',
-> `HeadOfState` char(60) DEFAULT NULL,
-> `Capital` int DEFAULT NULL,
-> `Code2` char(2) NOT NULL DEFAULT '',
-> PRIMARY KEY (`Code`)
-> );
Query OK, 0 rows affected (0.872 sec)
MySQL [mytestdb]> INSERT INTO `country` VALUES ('ABW','Aruba','North America','Caribbean',193.00,NULL,103000,78.4,828.00,793.00,'Aruba','Nonmetropolitan Territory of The Netherlands','Beatrix',129,'AW');
Query OK, 1 row affected (0.107 sec)
MySQL [mytestdb]> INSERT INTO `country` VALUES ('AFG','Afghanistan','Asia','Southern and Central Asia',652090.00,1919,22720000,45.9,5976.00,NULL,'Afganistan/Afqanestan','Islamic Emirate','Mohammad Omar',1,'AF');
Query OK, 1 row affected (0.011 sec)
MySQL [mytestdb]> INSERT INTO `country` VALUES ('AGO','Angola','Africa','Central Africa',1246700.00,1975,12878000,38.3,6648.00,7984.00,'Angola','Republic','José Eduardo dos Santos',56,'AO');
Query OK, 1 row affected (0.018 sec)
MySQL [mytestdb]> select * from country;
+------+-------------+---------------+---------------------------+-------------+-----------+------------+----------------+---------+---------+-----------------------+----------------------------------------------+--------------------------+---------+-------+
| Code | Name | Continent | Region | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP | GNPOld | LocalName | GovernmentForm | HeadOfState | Capital | Code2 |
+------+-------------+---------------+---------------------------+-------------+-----------+------------+----------------+---------+---------+-----------------------+----------------------------------------------+--------------------------+---------+-------+
| ABW | Aruba | North America | Caribbean | 193.00 | NULL | 103000 | 78.4 | 828.00 | 793.00 | Aruba | Nonmetropolitan Territory of The Netherlands | Beatrix | 129 | AW |
| AFG | Afghanistan | Asia | Southern and Central Asia | 652090.00 | 1919 | 22720000 | 45.9 | 5976.00 | NULL | Afganistan/Afqanestan | Islamic Emirate | Mohammad Omar | 1 | AF |
| AGO | Angola | Africa | Central Africa | 1246700.00 | 1975 | 12878000 | 38.3 | 6648.00 | 7984.00 | Angola | Republic | José Eduardo dos Santos | 56 | AO |
+------+-------------+---------------+---------------------------+-------------+-----------+------------+----------------+---------+---------+-----------------------+----------------------------------------------+--------------------------+---------+-------+
3 rows in set (0.007 sec)
MySQL [mytestdb]> CREATE TABLE `city` (
-> `ID` int NOT NULL AUTO_INCREMENT,
-> `Name` char(35) NOT NULL DEFAULT '',
-> `CountryCode` char(3) NOT NULL DEFAULT '',
-> `District` char(20) NOT NULL DEFAULT '',
-> `Population` int NOT NULL DEFAULT '0',
-> PRIMARY KEY (`ID`),
-> KEY `CountryCode` (`CountryCode`),
-> CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
-> );
Query OK, 0 rows affected (0.916 sec)
MySQL [mytestdb]> INSERT INTO `city` VALUES (1,'Kabul','AFG','Kabol',1780000);
Query OK, 1 row affected (0.357 sec)
MySQL [mytestdb]> INSERT INTO `city` VALUES (2,'Qandahar','AFG','Qandahar',237500);
Query OK, 1 row affected (0.100 sec)
MySQL [mytestdb]> INSERT INTO `city` VALUES (3,'Herat','AFG','Herat',186800);
Query OK, 1 row affected (0.243 sec)
MySQL [mytestdb]> INSERT INTO `city` VALUES (4,'Mazar-e-Sharif','AFG','Balkh',127800);
Query OK, 1 row affected (0.121 sec)
MySQL [mytestdb]> select * from city;
+----+----------------+-------------+----------+------------+
| ID | Name | CountryCode | District | Population |
+----+----------------+-------------+----------+------------+
| 1 | Kabul | AFG | Kabol | 1780000 |
| 2 | Qandahar | AFG | Qandahar | 237500 |
| 3 | Herat | AFG | Herat | 186800 |
| 4 | Mazar-e-Sharif | AFG | Balkh | 127800 |
+----+----------------+-------------+----------+------------+
4 rows in set (0.004 sec)
MySQL [mytestdb]> show tables;
+--------------------+
| Tables_in_mytestdb |
+--------------------+
| city |
| country |
+--------------------+
2 rows in set (0.004 sec)
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/从前慢现在也慢/article/detail/168040
推荐阅读
相关标签
