部署规划
本文实践练习手动部署 OceanBase 集群副本的方法，3台OceanBase 节点上部署启动 observer 进程，并在中控机上部署 obproxy 进程。
机器信息
| 机器类型 | 虚拟机 |
|---|---|
| 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 | 手动部署OBPROXY |
| OBSERVER | 10.201.0.171 | OceanBase 数据库 zone1 |
| 10.201.0.172 | OceanBase 数据库 zone2 | |
| 10.201.0.173 | OceanBase 数据库 zone3 | |
| OBPROXY | 10.201.0.170 | 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 mariadb-devel mariadb
内核参数修改
修改配置文件：
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
安装 OceanBase 软件包
手动部署 OceanBase 集群时需要安装 OceanBase 数据库的 OBSERVER 软件。
[admin@observer01 ~]$ cd /mnt/db/OceanBase/ [admin@observer01 OceanBase]$ ll -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 158948 3月 22 09:57 oceanbase-ce-libs-3.1.2-10000392021123010.el7.x86_64.rpm [admin@observer01 OceanBase]$ sudo rpm -ivh oceanbase-ce-libs-3.1.2-10000392021123010.el7.x86_64.rpm [sudo] admin 的密码： 警告：oceanbase-ce-libs-3.1.2-10000392021123010.el7.x86_64.rpm: 头V4 RSA/SHA1 Signature, 密钥 ID e9b4a7aa: NOKEY 准备中... ################################# [100%] 正在升级/安装... 1:oceanbase-ce-libs-3.1.2-100003920################################# [100%] [admin@observer01 OceanBase]$ sudo rpm -ivh oceanbase-ce-3.1.2-10000392021123010.el7.x86_64.rpm 警告：oceanbase-ce-3.1.2-10000392021123010.el7.x86_64.rpm: 头V4 RSA/SHA1 Signature, 密钥 ID e9b4a7aa: NOKEY 准备中... ################################# [100%] 正在升级/安装... 1:oceanbase-ce-3.1.2-10000392021123################################# [100%] [admin@observer02 ~]$ cd /mnt/db/OceanBase/ [admin@observer02 OceanBase]$ sudo rpm -ivh oceanbase-ce-libs-3.1.2-10000392021123010.el7.x86_64.rpm [sudo] admin 的密码： 警告：oceanbase-ce-libs-3.1.2-10000392021123010.el7.x86_64.rpm: 头V4 RSA/SHA1 Signature, 密钥 ID e9b4a7aa: NOKEY 准备中... ################################# [100%] 正在升级/安装... 1:oceanbase-ce-libs-3.1.2-100003920################################# [100%] [admin@observer02 OceanBase]$ sudo rpm -ivh oceanbase-ce-3.1.2-10000392021123010.el7.x86_64.rpm 警告：oceanbase-ce-3.1.2-10000392021123010.el7.x86_64.rpm: 头V4 RSA/SHA1 Signature, 密钥 ID e9b4a7aa: NOKEY 准备中... ################################# [100%] 正在升级/安装... 1:oceanbase-ce-3.1.2-10000392021123################################# [100%] [admin@observer03 ~]$ cd /mnt/db/OceanBase/ [admin@observer03 OceanBase]$ sudo rpm -ivh oceanbase-ce-libs-3.1.2-10000392021123010.el7.x86_64.rpm [sudo] admin 的密码： 警告：oceanbase-ce-libs-3.1.2-10000392021123010.el7.x86_64.rpm: 头V4 RSA/SHA1 Signature, 密钥 ID e9b4a7aa: NOKEY 准备中... ################################# [100%] 正在升级/安装... 1:oceanbase-ce-libs-3.1.2-100003920################################# [100%] [admin@observer03 OceanBase]$ sudo rpm -ivh oceanbase-ce-3.1.2-10000392021123010.el7.x86_64.rpm 警告：oceanbase-ce-3.1.2-10000392021123010.el7.x86_64.rpm: 头V4 RSA/SHA1 Signature, 密钥 ID e9b4a7aa: NOKEY 准备中... ################################# [100%] 正在升级/安装... 1:oceanbase-ce-3.1.2-10000392021123################################# [100%]
软件包默认安装目录是：/home/admin/oceanbase。目录结构如下：
[admin@observer01 ~]$ pwd /home/admin [admin@observer01 ~]$ ll 总用量 0 drwxr-xr-x 5 root root 39 3月 22 10:04 oceanbase [admin@observer01 ~]$ tree oceanbase oceanbase ├── bin │ ├── import_time_zone_info.py │ └── observer ├── etc │ ├── oceanbase_upgrade_dep.yml │ ├── priv_checker.py │ ├── timezone_V1.log │ ├── upgrade_checker.py │ ├── upgrade_cluster_health_checker.py │ ├── upgrade_post_checker.py │ ├── upgrade_post.py │ ├── upgrade_pre.py │ ├── upgrade_rolling_post.py │ └── upgrade_rolling_pre.py └── lib ├── libaio.so -> libaio.so.1.0.1 ├── libaio.so.1 -> libaio.so.1.0.1 ├── libaio.so.1.0.1 ├── libmariadb.so -> libmariadb.so.3 └── libmariadb.so.3 3 directories, 17 files 使用admin安装RPM包后，oceanbase目录用户和组都为root，需修改为admin [root@observer01 ~]# chown -R admin:admin /home/admin/oceanbase [root@observer02 ~]# chown -R admin:admin /home/admin/oceanbase [root@observer03 ~]# chown -R admin:admin /home/admin/oceanbase
初始化数据目录
手动部署时，OceanBase 节点上的相关目录都需要手动创建。
su - admin
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启动 OBSERVER 进程
每个机器的启动参数大部分都相同，只有少数不一样，需要特别留意。
su - admin echo 'export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:~/oceanbase/lib' >> ~/.bash_profile . ~/.bash_profile ---10.201.0.171 cd ~/oceanbase && bin/observer -i eth0 -p 2881 -P 2882 -z zone1 -d ~/oceanbase/store/obdemo -r '10.201.0.171:2882:2881;10.201.0.172:2882:2881;10.201.0.173:2882:2881' -c 20220322 -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=100G,stack_size=1536K,config_additional_dir=/data/obdemo/etc3;/redo/obdemo/etc2" -d ~/oceanbase/store/obdemo ---10.201.0.172 cd ~/oceanbase && bin/observer -i eth0 -p 2881 -P 2882 -z zone2 -d ~/oceanbase/store/obdemo -r '10.201.0.171:2882:2881;10.201.0.172:2882:2881;10.201.0.173:2882:2881' -c 20220322 -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=100G,stack_size=1536K,config_additional_dir=/data/obdemo/etc3;/redo/obdemo/etc2" -d ~/oceanbase/store/obdemo ---10.201.0.173 cd ~/oceanbase && bin/observer -i eth0 -p 2881 -P 2882 -z zone3 -d ~/oceanbase/store/obdemo -r '10.201.0.171:2882:2881;10.201.0.172:2882:2881;10.201.0.173:2882:2881' -c 20220322 -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=100G,stack_size=1536K,config_additional_dir=/data/obdemo/etc3;/redo/obdemo/etc2" -d ~/oceanbase/store/obdemo
10.201.0.171[admin@observer01 ~]$ echo 'export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:~/oceanbase/lib' >> ~/.bash_profile [admin@observer01 ~]$ . ~/.bash_profile [admin@observer01 ~]$ env|grep LD_LIBRARY LD_LIBRARY_PATH=:/home/admin/oceanbase/lib [admin@observer01 ~]$ cd ~/oceanbase && bin/observer -i eth0 -p 2881 -P 2882 -z zone1 -d ~/oceanbase/store/obdemo -r '10.201.0.171:2882:2881;10.201.0.172:2882:2881;10.201.0.173:2882:2881' -c 20220322 -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=4,net_thread_count=4,datafile_size=100G,stack_size=1536K,config_additional_dir=/data/obdemo/etc3;/redo/obdemo/etc2" -d ~/oceanbase/store/obdemo bin/observer -i eth0 -p 2881 -P 2882 -z zone1 -d /home/admin/oceanbase/store/obdemo -r 10.201.0.171:2882:2881;10.201.0.172:2882:2881;10.201.0.173:2882:2881 -c 20220322 -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=4,net_thread_count=4,datafile_size=100G,stack_size=1536K,config_additional_dir=/data/obdemo/etc3;/redo/obdemo/etc2 -d /home/admin/oceanbase/store/obdemo devname: eth0 mysql port: 2881 rpc port: 2882 zone: zone1 data_dir: /home/admin/oceanbase/store/obdemo rs list: 10.201.0.171:2882:2881;10.201.0.172:2882:2881;10.201.0.173:2882:2881 cluster id: 20220322 appname: obdemo optstr: memory_limit=8G,cache_wash_threshold=1G,__min_full_resource_pool_memory=268435456,system_memory=3G,memory_chunk_cache_size=128M,cpu_count=4,net_thread_count=4,datafile_size=100G,stack_size=1536K,config_additional_dir=/data/obdemo/etc3;/redo/obdemo/etc2 data_dir: /home/admin/oceanbase/store/obdemo
10.201.0.172[admin@observer02 ~]$ echo 'export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:~/oceanbase/lib' >> ~/.bash_profile [admin@observer02 ~]$ . ~/.bash_profile [admin@observer02 ~]$ env|grep LD_LIBRARY LD_LIBRARY_PATH=:/home/admin/oceanbase/lib [admin@observer02 ~]$ cd ~/oceanbase && bin/observer -i eth0 -p 2881 -P 2882 -z zone2 -d ~/oceanbase/store/obdemo -r '10.201.0.171:2882:2881;10.201.0.172:2882:2881;10.201.0.173:2882:2881' -c 20220322 -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=4,net_thread_count=4,datafile_size=100G,stack_size=1536K,config_additional_dir=/data/obdemo/etc3;/redo/obdemo/etc2" -d ~/oceanbase/store/obdemo bin/observer -i eth0 -p 2881 -P 2882 -z zone2 -d /home/admin/oceanbase/store/obdemo -r 10.201.0.171:2882:2881;10.201.0.172:2882:2881;10.201.0.173:2882:2881 -c 20220322 -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=4,net_thread_count=4,datafile_size=100G,stack_size=1536K,config_additional_dir=/data/obdemo/etc3;/redo/obdemo/etc2 -d /home/admin/oceanbase/store/obdemo devname: eth0 mysql port: 2881 rpc port: 2882 zone: zone2 data_dir: /home/admin/oceanbase/store/obdemo rs list: 10.201.0.171:2882:2881;10.201.0.172:2882:2881;10.201.0.173:2882:2881 cluster id: 20220322 appname: obdemo optstr: memory_limit=8G,cache_wash_threshold=1G,__min_full_resource_pool_memory=268435456,system_memory=3G,memory_chunk_cache_size=128M,cpu_count=4,net_thread_count=4,datafile_size=100G,stack_size=1536K,config_additional_dir=/data/obdemo/etc3;/redo/obdemo/etc2 data_dir: /home/admin/oceanbase/store/obdemo
10.201.0.173[admin@observer03 ~]$ echo 'export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:~/oceanbase/lib' >> ~/.bash_profile [admin@observer03 ~]$ . ~/.bash_profile [admin@observer03 ~]$ env|grep LD_LIBRARY LD_LIBRARY_PATH=:/home/admin/oceanbase/lib [admin@observer03 ~]$ cd ~/oceanbase && bin/observer -i eth0 -p 2881 -P 2882 -z zone3 -d ~/oceanbase/store/obdemo -r '10.201.0.171:2882:2881;10.201.0.172:2882:2881;10.201.0.173:2882:2881' -c 20220322 -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=4,net_thread_count=4,datafile_size=100G,stack_size=1536K,config_additional_dir=/data/obdemo/etc3;/redo/obdemo/etc2" -d ~/oceanbase/store/obdemo bin/observer -i eth0 -p 2881 -P 2882 -z zone3 -d /home/admin/oceanbase/store/obdemo -r 10.201.0.171:2882:2881;10.201.0.172:2882:2881;10.201.0.173:2882:2881 -c 20220322 -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=4,net_thread_count=4,datafile_size=100G,stack_size=1536K,config_additional_dir=/data/obdemo/etc3;/redo/obdemo/etc2 -d /home/admin/oceanbase/store/obdemo devname: eth0 mysql port: 2881 rpc port: 2882 zone: zone3 data_dir: /home/admin/oceanbase/store/obdemo rs list: 10.201.0.171:2882:2881;10.201.0.172:2882:2881;10.201.0.173:2882:2881 cluster id: 20220322 appname: obdemo optstr: memory_limit=8G,cache_wash_threshold=1G,__min_full_resource_pool_memory=268435456,system_memory=3G,memory_chunk_cache_size=128M,cpu_count=4,net_thread_count=4,datafile_size=100G,stack_size=1536K,config_additional_dir=/data/obdemo/etc3;/redo/obdemo/etc2 data_dir: /home/admin/oceanbase/store/obdemo
检查三个节点进程启动正常，主要看端口监听是否正常。在中控机上批量查询。
[admin@observer01 oceanbase]$ export IPS="10.201.0.171 10.201.0.172 10.201.0.173" [admin@observer01 oceanbase]$ for ob in $IPS;do echo $ob; ssh $ob "netstat -ntlp|grep 288"; 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.) tcp 0 0 0.0.0.0:2881 0.0.0.0:* LISTEN 192584/bin/observer tcp 0 0 0.0.0.0:2882 0.0.0.0:* LISTEN 192584/bin/observer 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.) tcp 0 0 0.0.0.0:2881 0.0.0.0:* LISTEN 198355/bin/observer tcp 0 0 0.0.0.0:2882 0.0.0.0:* LISTEN 198355/bin/observer 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.) tcp 0 0 0.0.0.0:2881 0.0.0.0:* LISTEN 206714/bin/observer tcp 0 0 0.0.0.0:2882 0.0.0.0:* LISTEN 206714/bin/observer
集群自举（初始化）
当 OceanBase 集群三个节点都正常启动，并且监听正常时，连接到任一节点（通过 2881 端口直连），进行自举（bootstrap 集群初始化）操作。 初始密码是空。
mysql -h 10.201.0.171 -u root -P 2881 -p -c -A set session ob_query_timeout=1000000000; alter system bootstrap ZONE 'zone1' SERVER '10.201.0.171:2882', ZONE 'zone2' SERVER '10.201.0.172:2882', ZONE 'zone3' SERVER '10.201.0.173:2882' ;
输出：
[admin@obdeployer ~]$ mysql -h 10.201.0.171 -u root -P 2881 -p -c -A Enter password: ---初始密码是空，直接回车登录 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3221225472 Server version: 5.7.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29) Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; ERROR 1146 (42S02): Table 'oceanbase.__all_database' doesn't exist [admin@observer01 oceanbase]$ mysql -h 10.201.0.172 -u root -P 2881 -p -c -A Enter password: ---初始密码是空，直接回车登录 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3221225472 Server version: 5.7.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29) Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; ERROR 1146 (42S02): Table 'oceanbase.__all_database' doesn't exist [admin@observer02 oceanbase]$ mysql -h 10.201.0.173 -u root -P 2881 -p -c -A Enter password: ---初始密码是空，直接回车登录 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3221225472 Server version: 5.7.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29) Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; ERROR 1146 (42S02): Table 'oceanbase.__all_database' doesn't exist mysql> set session ob_query_timeout=1000000000; alter system bootstrap ZONE 'zone1' SERVER '10.201.0.171:2882', ZONE 'zone2' SERVER '10.201.0.172:2882', ZONE 'zone3' SERVER '10.201.0.173:2882' ; Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (1 min 21.13 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | oceanbase | | information_schema | | mysql | | SYS | | LBACSYS | | ORAAUDITOR | | test | +--------------------+ 7 rows in set (0.00 sec) [admin@obdeployer ~]$ mysql -h 10.201.0.171 -u root@sys -P 2881 -p -c -A Enter password: ---初始密码是空，直接回车登录 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3221495378 Server version: 5.7.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29) Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | oceanbase | | information_schema | | mysql | | SYS | | LBACSYS | | ORAAUDITOR | | test | +--------------------+ 7 rows in set (0.01 sec)
设置相关密码
默认集群管理员（root@sys）的密码为空，这里需要设置一个密码。
mysql> alter user root identified by 'rootPWD123' ; Query OK, 0 rows affected (0.10 sec)
OBPROXY 用户（
proxyro）密码默认 OBPROXY 连接 OceanBase 集群时使用用户
proxyro。该用户不存在，需要手动创建。mysql> grant select on oceanbase.* to proxyro identified by 'proxyPWD123' ; Query OK, 0 rows affected (0.15 sec) mysql> select user, host, authentication_string,password_expired from mysql.user; +------------+------+-----------------------+------------------+ | user | host | authentication_string | password_expired | +------------+------+-----------------------+------------------+ | root | % | | | | ORAAUDITOR | % | | | | proxyro | % | | | +------------+------+-----------------------+------------------+ 3 rows in set (0.01 sec)
安装 OBPROXY 软件包
手动部署时需要安装 OceanBase 数据库的 OBPROXY 软件。
[admin@obdeployer OceanBase]$ sudo rpm -ivh obproxy-3.2.0-1.el7.x86_64.rpm [sudo] admin 的密码： 警告：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%]
社区版的 OBPROXY 软件默认安装到 /home/admin/obproxy-版本号 下。
[admin@obdeployer ~]$ ll 总用量 0 drwxr-xr-x 3 admin admin 17 3月 22 14:45 obproxy-3.2.0 [admin@obdeployer ~]$ tree ~/obproxy-3.2.0/ /home/admin/obproxy-3.2.0/ └── bin ├── obproxy └── obproxyd.sh 1 directory, 2 files
启动 OBPROXY 进程
启动 OBPROXY 进程推荐放在软件安装目录，进程 obproxy 会在该目录下生成目录 etc 用以保存 OBPROXY 的运行参数，以及目录 log 用以保存运行日志。
cd ~/obproxy-3.2.0/ && bin/obproxy -r "10.201.0.171:2881;10.201.0.172:2881;10.201.0.173:2881" -p 2883 -o "enable_strict_kernel_release=false,enable_cluster_checkout=false,enable_metadb_used=false" -c obdemo
输出：
[admin@obdeployer ~]$ cd ~/obproxy-3.2.0/ && bin/obproxy -r "10.201.0.171:2881;10.201.0.172:2881;10.201.0.173:2881" -p 2883 -o "enable_strict_kernel_release=false,enable_cluster_checkout=false,enable_metadb_used=false" -c obdemo bin/obproxy -r 10.201.0.171:2881;10.201.0.172:2881;10.201.0.173:2881 -p 2883 -o enable_strict_kernel_release=false,enable_cluster_checkout=false,enable_metadb_used=false -c obdemo rs list: 10.201.0.171:2881;10.201.0.172:2881;10.201.0.173:2881 listen port: 2883 optstr: enable_strict_kernel_release=false,enable_cluster_checkout=false,enable_metadb_used=false cluster_name: obdemo [admin@obdeployer obproxy-3.2.0]$ ps -ef|grep obproxy admin 249160 1 2 14:46 ? 00:00:00 bin/obproxy -r 10.201.0.171:2881;10.201.0.172:2881;10.201.0.173:2881 -p 2883 -o enable_strict_kernel_release=false,enable_cluster_checkout=false,enable_metadb_used=false -c obdemo admin 249250 241374 0 14:47 pts/0 00:00:00 grep --color=auto obproxy
检查 OBPROXY 监听是否正常
进程
obproxy默认会监听2个端口：2883 和 2884。[admin@obdeployer 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 249160/bin/obproxy tcp 0 0 0.0.0.0:2884 0.0.0.0:* LISTEN 249160/bin/obproxy
登录 OBPROXY 修改密码
登录 OBPROXY
登录用户名：
root@proxysys，端口：2883，初始密码：空。[admin@obdeployer obproxy-3.2.0]$ mysql -h 10.201.0.170 -u root@proxysys -P 2883 -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.25 Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> 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 | | password for observer sys user | false | SYS | | obproxy_sys_password | | password for obproxy sys user | false | SYS | +------------------------+-------+--------------------------------+-------------+---------------+ 3 rows in set (0.00 sec)
修改 OBPROXY 用户密码
您可通过修改参数的方式来修改 OBPROXY 用户密码，使用命令为
alter proxyconfig set。mysql> alter proxyconfig set obproxy_sys_password = 'obproxyPWD123' ; Query OK, 0 rows affected (0.00 sec)
修改 OBPROXY 连接 OceanBase 集群用户
proxyro的密码在修改 OBPROXY 用户密码的同时还需要修改 OBPROXY 连接 OceanBase 集群用户
proxyro的密码，这样 OBPROXY 才能和 OceanBase 集群正常连接。OBPROXY 连接 OceanBase 集群用户
proxyro的密码就是前面 OceanBase 集群初始化后创建的用户proxyro的密码。mysql> alter proxyconfig set observer_sys_password = 'proxyPWD123' ; Query OK, 0 rows affected (0.00 sec)
查看是否部署成功
退出后，您可尝试通过 OBPROXY 连接 OceanBase 集群, 如果能查看所有会话，则说明 OBPROXY 部署成功。
[admin@observer01 oceanbase]$ mysql -h10.201.0.170 -uroot@sys#obdemo -P2883 -ppass4obs -c -A oceanbase mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1048577 Server version: 5.6.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29) Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show processlist; +---------+----------+------+--------------------+-----------+-------------+-------------------+-------------------+--------+--------+ | Id | Tenant | User | Host | db | trans_count | svr_session_count | state | tid | pid | +---------+----------+------+--------------------+-----------+-------------+-------------------+-------------------+--------+--------+ | 1048577 | sys | root | 10.201.0.171:12334 | oceanbase | 0 | 1 | MCS_ACTIVE_READER | 249165 | 249160 | | 1 | proxysys | root | 10.201.0.170:56030 | NULL | 0 | 0 | MCS_ACTIVE_READER | 249160 | 249160 | +---------+----------+------+--------------------+-----------+-------------+-------------------+-------------------+--------+--------+ 2 rows in set (0.00 sec) mysql> show full processlist; +------------+---------+--------+--------------------+-----------+---------+------+--------+-----------------------+--------------+------+--------------+ | Id | User | Tenant | Host | db | Command | Time | State | Info | Ip | Port | Proxy_sessid | +------------+---------+--------+--------------------+-----------+---------+------+--------+-----------------------+--------------+------+--------------+ | 3221519076 | proxyro | sys | 10.201.0.170:11338 | oceanbase | Sleep | 8 | SLEEP | NULL | 10.201.0.171 | 2881 | 2 | | 3221750020 | root | sys | 10.201.0.170:38160 | oceanbase | Query | 0 | ACTIVE | show full processlist | 10.201.0.172 | 2881 | 3 | | 3222012158 | root | sys | 10.201.0.172:60156 | NULL | Sleep | 2364 | SLEEP | NULL | 10.201.0.173 | 2881 | NULL | +------------+---------+--------+--------------------+-----------+---------+------+--------+-----------------------+--------------+------+--------------+ 3 rows in set (0.12 sec)
连接 OceanBase
使用 observer 2881 端口登录
[admin@obdeployer ~]$ mysql -h10.201.0.171 -uroot@sys -P2881 -ppass4obs -c -A oceanbase mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3221668685 Server version: 5.7.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29) Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> exit Bye [admin@obdeployer ~]$ mysql -h10.201.0.172 -uroot@sys -P2881 -ppass4obs -c -A oceanbase mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3221786170 Server version: 5.7.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29) Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> exit Bye [admin@obdeployer ~]$ mysql -h10.201.0.173 -uroot@sys -P2881 -ppass4obs -c -A oceanbase mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3222017146 Server version: 5.7.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29) Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | oceanbase | | information_schema | | mysql | | SYS | | LBACSYS | | ORAAUDITOR | | test | +--------------------+ 7 rows in set (0.01 sec) mysql> exit Bye
使用 obproxy 2883 端口登录
[admin@obdeployer ~]$ mysql -h10.201.0.170 -uroot@sys -P2883 -ppass4obs -c -A oceanbase mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.6.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29) Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> exit Bye [admin@obdeployer ~]$ [admin@obdeployer ~]$ mysql -h10.201.0.170 -uroot@sys#obdemo -P2883 -ppass4obs -c -A oceanbase mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.6.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29) Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show parameters like 'cluster'; +-------+----------+--------------+----------+---------+-----------+--------+---------------------+----------+---------+---------+-------------------+ | zone | svr_type | svr_ip | svr_port | name | data_type | value | info | section | scope | source | edit_level | +-------+----------+--------------+----------+---------+-----------+--------+---------------------+----------+---------+---------+-------------------+ | zone1 | observer | 10.201.0.171 | 2882 | cluster | NULL | obdemo | Name of the cluster | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE | | zone3 | observer | 10.201.0.173 | 2882 | cluster | NULL | obdemo | Name of the cluster | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE | | zone2 | observer | 10.201.0.172 | 2882 | cluster | NULL | obdemo | Name of the cluster | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE | +-------+----------+--------------+----------+---------+-----------+--------+---------------------+----------+---------+---------+-------------------+ 3 rows in set (0.95 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | oceanbase | | information_schema | | mysql | | SYS | | LBACSYS | | ORAAUDITOR | | test | +--------------------+ 7 rows in set (0.01 sec) mysql> exit Bye
创建业务租户、数据库及表
查看资源
查看OB集群所有节点信息
mysql> 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-22 14:09:49.549607 | | zone2 | 10.201.0.172 | 2882 | 2881 | 0 | active | 2022-03-22 14:09:50.308641 | | zone3 | 10.201.0.173 | 2882 | 2881 | 0 | active | 2022-03-22 14:09:49.599129 | +-------+--------------+----------+------------+-----------------+--------+----------------------------+ 3 rows in set (0.01 sec)
查看当前租户
mysql> show tenant; +---------------------+ | Current_tenant_name | +---------------------+ | sys | +---------------------+ 1 row in set (0.16 sec)
查询系统资源
mysql> 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 | 5.000000000000 | 100.000000000000 | zone1 | | 10.201.0.172 | 2882 | 6 | 5.000000000000 | 100.000000000000 | zone2 | | 10.201.0.173 | 2882 | 6 | 5.000000000000 | 100.000000000000 | zone3 | +--------------+----------+-----------+--------------------------+---------------------------+-------+ 3 rows in set (0.03 sec)
查询租户已分配资源
mysql> 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 | 2.500000000000 | +----------------+----------------------------------+ 1 row in set (0.14 sec)
查看OceanBase集群可用资源情况
mysql> 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 | 5 | 4 | 100 | 3.1.2_ | 2022-03-22 16:50:30.691477 | | zone2 | 10.201.0.172:2882 | 6 | 3.5 | 5 | 4 | 100 | 3.1.2_ | 2022-03-22 16:49:39.022658 | | zone3 | 10.201.0.173:2882 | 6 | 3.5 | 5 | 4 | 100 | 3.1.2_ | 2022-03-22 16:47:36.718398 | +-------+-------------------+-----------+----------+--------------+-------------+---------------+---------+----------------------------+ 3 rows in set (0.10 sec) mysql> 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 | 1 | 1 | zone1 | 10.201.0.171:2882 | 1 | sys | | sys_pool | sys_unit_config | 5 | 2.5 | 2 | 1 | 2 | zone2 | 10.201.0.172:2882 | 1 | sys | | sys_pool | sys_unit_config | 5 | 2.5 | 2 | 1 | 3 | zone3 | 10.201.0.173:2882 | 1 | sys | +--------------------+------------------+---------+---------+------------+------------+---------+-------+-------------------+-----------+-------------+ 3 rows in set (0.02 sec)
创建资源
创建资源单元
mysql> create resource unit my_test_unit max_cpu=1, min_cpu=1, max_memory='1G', min_memory='1G', max_iops=10000, min_iops=1000, max_session_num=1000000, max_disk_size='10G'; Query OK, 0 rows affected (0.03 sec) mysql> 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 | 1610612736 | 1342177280 | 107374182400 | | 1017 | my_test_unit | 1 | 1 | 1073741824 | 1073741824 | 10737418240 | +----------------+-----------------+---------+---------+------------+------------+---------------+ 2 rows in set (0.00 sec)
创建资源池
mysql> create resource pool my_test_pool unit='my_test_unit', unit_num=1; Query OK, 0 rows affected (0.21 sec) mysql> 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 | +---------+----------------+------------------+------------------+--------------------+-------+ | 1007 | 1017 | my_test_unit | 1018 | my_test_pool | zone1 | | 1008 | 1017 | my_test_unit | 1018 | my_test_pool | zone2 | | 1009 | 1017 | my_test_unit | 1018 | my_test_pool | zone3 | +---------+----------------+------------------+------------------+--------------------+-------+ 3 rows in set (0.00 sec
创建租户
mysql> 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.01 sec)
mysql> create tenant my_test_obtenant resource_pool_list=('my_test_pool'), primary_zone='zone1,zone2,zone3',comment 'oceanbase tenant/instance', charset='utf8' set ob_tcp_invited_nodes='%', ob_compatibility_mode='mysql';
Query OK, 0 rows affected (8.28 sec)
mysql> select tenant_id,tenant_name,primary_zone from __all_tenant;
+-----------+------------------+-------------------+
| tenant_id | tenant_name | primary_zone |
+-----------+------------------+-------------------+
| 1 | sys | zone1;zone2,zone3 |
| 1002 | my_test_obtenant | zone1,zone2,zone3 |
+-----------+------------------+-------------------+
2 rows in set (0.01 sec)再次检查租户资源分配细节
mysql> 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 | 5 | 3 | 100 | 3.1.2_ | 2022-03-22 16:50:30.691477 | | zone2 | 10.201.0.172:2882 | 6 | 2.5 | 5 | 3 | 100 | 3.1.2_ | 2022-03-22 16:49:39.022658 | | zone3 | 10.201.0.173:2882 | 6 | 2.5 | 5 | 3 | 100 | 3.1.2_ | 2022-03-22 16:47:36.718398 | +-------+-------------------+-----------+----------+--------------+-------------+---------------+---------+----------------------------+ 3 rows in set (0.00 sec) mysql> mysql> 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 | 1 | 1 | zone1 | 10.201.0.171:2882 | 1 | sys | | sys_pool | sys_unit_config | 5 | 2.5 | 2 | 1 | 2 | zone2 | 10.201.0.172:2882 | 1 | sys | | sys_pool | sys_unit_config | 5 | 2.5 | 2 | 1 | 3 | zone3 | 10.201.0.173:2882 | 1 | sys | | my_test_pool | my_test_unit | 1 | 1 | 1 | 1 | 1007 | zone1 | 10.201.0.171:2882 | 1002 | my_test_obtenant | | my_test_pool | my_test_unit | 1 | 1 | 1 | 1 | 1008 | zone2 | 10.201.0.172:2882 | 1002 | my_test_obtenant | | my_test_pool | my_test_unit | 1 | 1 | 1 | 1 | 1009 | zone3 | 10.201.0.173:2882 | 1002 | my_test_obtenant | +--------------------+------------------+---------+---------+------------+------------+---------+-------+-------------------+-----------+------------------+ 6 rows in set (0.05 sec)
登录普通租户
查看租户信息
mysql> 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 |
| 1002 | my_test_obtenant | zone1;zone2;zone3 | zone1,zone2,zone3 | 0 | oceanbase tenant/instance | 0 | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 |
+-----------+------------------+-------------------+-------------------+----------------+---------------------------+-----------+---------------------------------------------+
2 rows in set (0.01 sec)
mysql> select tenant_id,tenant_name,primary_zone from __all_tenant;
+-----------+------------------+-------------------+
| tenant_id | tenant_name | primary_zone |
+-----------+------------------+-------------------+
| 1 | sys | zone1;zone2,zone3 |
| 1002 | my_test_obtenant | zone1,zone2,zone3 |
+-----------+------------------+-------------------+
2 rows in set (0.00 sec)
mysql> exit
Bye登录租户
[admin@obdeployer ~]$ mysql -h10.201.0.170 -uroot@my_test_obtenant#obdemo -P2883 -c -A oceanbase Welcome to the MySQL monitor. 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, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> exit Bye [admin@obdeployer ~]$ mysql -h10.201.0.171 -uroot@my_test_obtenant -P2881 -c -A oceanbase Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3221698441 Server version: 5.7.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29) Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> exit Bye [admin@obdeployer ~]$ mysql -h10.201.0.172 -uroot@my_test_obtenant -P2881 -c -A oceanbase Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3221786662 Server version: 5.7.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29) Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> exit Bye [admin@obdeployer ~]$ mysql -h10.201.0.173 -uroot@my_test_obtenant -P2881 -c -A oceanbase Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3222017149 Server version: 5.7.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29) Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> exit Bye
设置root密码
mysql> select user, host, authentication_string,password_expired from mysql.user; +------------+------+-----------------------+------------------+ | user | host | authentication_string | password_expired | +------------+------+-----------------------+------------------+ | root | % | | | | ORAAUDITOR | % | | | +------------+------+-----------------------+------------------+ 2 rows in set (0.22 sec) mysql> alter user root identified by 'pass4obs' ; Query OK, 0 rows affected (0.22 sec) [admin@obdeployer ~]$ mysql -h10.201.0.170 -uroot@my_test_obtenant#obdemo -P2883 -c -A oceanbase ERROR 1045 (42000): Access denied for user 'root'@'xxx.xxx.xxx.xxx' (using password: NO) ---设置root密码后，必须使用密码登录 [admin@obdeployer ~]$ mysql -h10.201.0.170 -uroot@my_test_obtenant#obdemo -P2883 -ppass4obs -c -A oceanbase mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 5.6.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29) Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
创建数据库及用户
mysql> select tenant_id,tenant_name,primary_zone from __all_tenant; ERROR 1146 (42S02): Table 'oceanbase.__all_tenant' doesn，t exist mysql> show databases; +--------------------+ | Database | +--------------------+ | oceanbase | | information_schema | | mysql | | test | +--------------------+ 4 rows in set (0.06 sec) mysql> create database mytestdb ; Query OK, 1 row affected (0.27 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | oceanbase | | information_schema | | mysql | | test | | mytestdb | +--------------------+ 5 rows in set (0.03 sec) mysql> create user mytestuser@'%' identified by 'pass4usr' ; Query OK, 0 rows affected (0.30 sec) mysql> grant all privileges on *.* to mytestuser@'%'; Query OK, 0 rows affected (0.11 sec) mysql> select user, host, authentication_string,password_expired from mysql.user; +------------+------+-----------------------+------------------+ | user | host | authentication_string | password_expired | +------------+------+-----------------------+------------------+ | root | % | | | | ORAAUDITOR | % | | | | mytestuser | % | | | +------------+------+-----------------------+------------------+ 3 rows in set (0.22 sec) mysql> show grants for mytestuser; +---------------------------------------------+ | Grants for mytestuser@% | +---------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'mytestuser' | +---------------------------------------------+ 1 row in set (0.11 sec)
创建业务表
[admin@obdeployer ~]$ mysql -h10.201.0.170 -umytestuser@my_test_obtenant#obdemo -P2883 -ppass4usr -c -A mytestdb
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.6.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> 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.74 sec)
mysql> 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.02 sec)
mysql> 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.02 sec)
mysql> 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.04 sec)
mysql> 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.01 sec)
mysql> 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 (1.47 sec)
mysql> INSERT INTO `city` VALUES (1,'Kabul','AFG','Kabol',1780000);
Query OK, 1 row affected (0.29 sec)
mysql> INSERT INTO `city` VALUES (2,'Qandahar','AFG','Qandahar',237500);
Query OK, 1 row affected (0.07 sec)
mysql> INSERT INTO `city` VALUES (3,'Herat','AFG','Herat',186800);
Query OK, 1 row affected (0.06 sec)
mysql> INSERT INTO `city` VALUES (4,'Mazar-e-Sharif','AFG','Balkh',127800);
Query OK, 1 row affected (0.02 sec)
mysql> 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.04 sec)
mysql> show tables;
+--------------------+
| Tables_in_mytestdb |
+--------------------+
| city |
| country |
+--------------------+
2 rows in set (0.23 sec)版权声明：本文为qinwen740原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。