作者简介:海芊,一个致力于当网红的OceanBase文档工程师。
个人频道:Amber loves OB https://space.bilibili.com/557737255/channel/detail?cid=189741
最近有同学反映在学习 OceanBase 数据库的过程中,遇到了普通租户无法连接OceanBase数据库的问题。为此,OB开源君特地请到了文档美少女海芊为大家进行答疑解惑,帮助有相同问题的小伙伴避坑。
视频教程
扫码查看以下视频教程,将会手把手带大家解决该问题,或参考 OceanBase 踩坑记录之普通租户连接失败。
如果想了解此问题出现的原因以及解决方案,就请往下看吧:
复现步骤
1.创建资源单元。
CREATE RESOURCE UNIT S2 max_cpu=8, min_cpu=8, max_memory='16G',min_memory='16G',max_iops=10000,min_iops=10000,max_session_num=10000,max_disk_size='1024G'; Query OK, 0 rows affected (0.028 sec)
2.创建资源池。
CREATE RESOURCE POOL amber_1 unit=S2,unit_num=1; Query OK, 0 rows affected (0.011 sec)
3.创建普通租户。
create tenant amber_babe resource_pool_list=("amber_1"); Query OK, 0 rows affected (1.087 sec)
4.连接 OceanBase 数据库。
obclient -h 172.30.198.252 -P 2883 -uroot@amber_babe -A -c
ERROR 1227 (42501): Access denied
原因排查
首先查看租户 amber_babe
是否创建成功。
SELECT * FROM oceanbase.gv$tenant; +-----------+-------------+-----------+--------------+----------------+---------------+-----------+------------------------------------+ | tenant_id | tenant_name | zone_list | primary_zone | collation_type | info | read_only | locality | +-----------+-------------+-----------+--------------+----------------+---------------+-----------+------------------------------------+ | 1 | sys | z1;z2;z3 | z1;z2,z3 | 0 | system tenant | 0 | FULL{1}@z1, FULL{1}@z2, FULL{1}@z3 | | 1001 | amber_babe | z1;z2;z3 | RANDOM | 0 | | 0 | FULL{1}@z1, FULL{1}@z2, FULL{1}@z3 | +-----------+-------------+-----------+--------------+----------------+---------------+-----------+------------------------------------+
从返回信息来看,租户已经创建成功。但是为什么连不上呢?
从报错信息来看,提示访问被拒绝。起初猜测是由于 clog 停写, 如果 clog_dir
目录达到了 clog_disk_usage_limit_percentage
参数设置的上限,那么写请求会停止。接下来验证我们的假设对不对。
执行以下命令查看集群配置:
SHOW PARAMETERS WHERE name IN ('clog_disk_usage_limit_percentage'); +------+----------+----------------+----------+----------------------------------+-----------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+---------+-------------------+ | zone | svr_type | svr_ip | svr_port | name | data_type | value | info | section | scope | source | edit_level | +------+----------+----------------+----------+----------------------------------+-----------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+---------+-------------------+ | z2 | observer | 172.30.198.247 | 33334 | clog_disk_usage_limit_percentage | NULL | 95 | maximum of clog disk usage percentage before stop submitting or receiving logs, should be greater than clog_disk_utilization_threshold. Range: [80, 100] | TRANS | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE | | z1 | observer | 172.30.198.246 | 33332 | clog_disk_usage_limit_percentage | NULL | 95 | maximum of clog disk usage percentage before stop submitting or receiving logs, should be greater than clog_disk_utilization_threshold. Range: [80, 100] | TRANS | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE | | z3 | observer | 172.30.198.248 | 33336 | clog_disk_usage_limit_percentage | NULL | 95 | maximum of clog disk usage percentage before stop submitting or receiving logs, should be greater than clog_disk_utilization_threshold. Range: [80, 100] | TRANS | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE | +------+----------+----------------+----------+----------------------------------+-----------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+---------+-------------------+
查询结果显示 clog_disk_usage_limit_percentage
参数默认值为 95%。经过查看配置,发现并没有停写。因此上述假设不正确。
真正的原因是由于 IP 不在白名单之内,默认只能回环登录。如果要开放全部IP访问,需要加上 ob_tcp_invited_nodes: '%'
。
解决方案
通过 ALTER TENANT 语句修改 ob_tcp_invited_nodes: '%'
变量以便允许任何客户端 IP 连接该租户。
ALTER TENANT amber_babe SET VARIABLES ob_tcp_invited_nodes='%';
或者在创建租户时直接修改 ob_tcp_invited_nodes
为 '%'
。
CREATE TENANT IF NOT EXISTS amber_babe resource_pool_list=('amber_1') SET ob_tcp_invited_nodes='%';
再次尝试连接,此时连接成功:
obclient -h 172.30.198.252 -P 2883 -uroot@amber_babe -A -c Welcome to the OceanBase. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.6.25 OceanBase 3.1.0 (r-00672c3c730c3df6eef3b359eae548d8c2db5ea2) (Built Jun 22 2021 12:46:28) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]>
参考
- GitHub issue
- clog_disk_usage_limit_percentage 文档
- ob_tcp_invited_nodes 文档
- 创建租户文档
- 视频教程
作者有话说
gitee:https://gitee.com/oceanbase
ps:如果您有任何疑问,可以通过以下方式与我们进行交流:
钉钉群:33254054
博客问答:https://open.oceanbase.com/answer