ERROR 2013 (HY000): Lost connection to MySQL server at 'reading authorization packet', system error: 11

OceanBase 技术问题
#1

【 使用环境 】测试环境
【 OB or 其他组件 】 proxy
【 使用版本 】(OceanBase CE 3.1.4) && obproxy (OceanBase 3.2.3 2)
【问题描述】 指定cluster name 使用proxy连接报错
【复现路径】 obclient -h 10.249.240.12 -uroot@sys#mintestob02 -P2883 -prootroot -c -A oceanbase
【问题现象及影响】

##使用代理端口 2883 ，指定cluster_name连接就报错。
[admin@pdmphb11 ~]$ obclient -h 10.249.240.12 -uroot@sys#mintestob02 -P2883 -prootroot -c -A oceanbase
ERROR 2013 (HY000): Lost connection to MySQL server at ‘reading authorization packet’, system error: 11
[admin@pdmphb11 ~]$
[admin@pdmphb11 ~]$ obclient -h 10.249.240.12 -uroot@sys#mintestob02 -P2881 -prootroot -c -A oceanbase
ERROR 1045 (42000): Access denied for user ‘root’@‘xxx.xxx.xxx.xxx’ (using password: YES)
[admin@pdmphb11 ~]$
[admin@pdmphb11 ~]$ obclient -h 10.249.240.12 -uroot@sys -P2881 -prootroot -c -A oceanbase
Welcome to the OceanBase. Commands end with ; or \g.
Your MySQL connection id is 3221667552
Server version: 5.7.25 OceanBase 3.1.4 (r10000092022071511-b4bfa011ceaef428782dcb65ae89190c40b78c2f) (Built Jul 15 2022 11:45:14)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MySQL [oceanbase]>

在obproxy_error.log日志文件中，报了以下错误。
2022-08-06 23:39:54.892748,undefined,mintestob02:tent_yang1:,OB_MYSQL,OB_MYSQL_COM_LOGIN,failed,-4152,5167us,0us,0us,0us,Y0-7F99B1D1F3A0,0,Null value
2022-08-06 23:42:07.003241,undefined,mintestob02:tent_yang1:,OB_MYSQL,OB_MYSQL_COM_LOGIN,failed,-4152,4138us,0us,0us,0us,Y0-7F99B211F3A0,0,Null value
2022-08-06 23:43:00.540336,undefined,mintestob02:sys:,OB_MYSQL,OB_MYSQL_COM_LOGIN,failed,-4152,5253us,0us,0us,0us,Y0-7F99B1D1F3A0,0,Null value
2022-08-06 23:44:06.283510,undefined,mintestob02:tent_yang1:,OB_MYSQL,OB_MYSQL_COM_LOGIN,failed,-4152,3579us,0us,0us,0us,Y0-7F99B211F3A0,0,Null value
2022-08-06 23:48:17.560646,undefined,mintestob02:tent_yang1:,OB_MYSQL,OB_MYSQL_COM_LOGIN,failed,-4152,7375us,0us,0us,0us,Y0-7F99B1D1F3A0,0,Null value
2022-08-06 23:48:31.109485,undefined,mintestob02:tent_yang1:,OB_MYSQL,OB_MYSQL_COM_LOGIN,failed,-4152,3767us,0us,0us,0us,Y0-7F99B211F3A0,0,Null value
2022-08-06 23:51:17.329350,undefined,mintestob02:tent_yang1:,OB_MYSQL,OB_MYSQL_COM_LOGIN,failed,-4152,36571us,0us,0us,0us,Y0-7F05D45183A0,0,Null value
2022-08-06 23:55:31.840853,undefined,mintestob02:tent_yang1:,OB_MYSQL,OB_MYSQL_COM_LOGIN,failed,-4152,6822us,0us,0us,0us,Y0-7F05D41183A0,0,Null value
2022-08-06 23:57:29.425705,undefined,mintestob02:tent_yang1:,OB_MYSQL,OB_MYSQL_COM_LOGIN,failed,-4152,6037us,0us,0us,0us,Y0-7F05D45183A0,0,Null value
2022-08-07 00:05:14.502626,undefined,mintestob02:sys:,OB_MYSQL,OB_MYSQL_COM_LOGIN,failed,-4152,19327us,0us,0us,0us,Y0-7FBC39C063A0,0,Null value
2022-08-07 00:07:35.448109,undefined,mintestob02:sys:,OB_MYSQL,OB_MYSQL_COM_LOGIN,failed,-4152,24857us,0us,0us,0us,Y0-7FBC398063A0,0,Null value
2022-08-07 00:22:45.277004,undefined,mintestob02:sys:,OB_MYSQL,OB_MYSQL_COM_LOGIN,failed,-4152,7522us,0us,0us,0us,Y0-7FBC398063A0,0,Null value
2022-08-07 00:25:23.089185,undefined,mintestob02:sys:,OB_MYSQL,OB_MYSQL_COM_LOGIN,failed,-4152,20416us,0us,0us,0us,Y0-7FBC39C063A0,0,Null value
2022-08-07 00:34:08.845106,undefined,mintestob02:sys:,OB_MYSQL,OB_MYSQL_COM_LOGIN,failed,-4152,15430us,0us,0us,0us,Y0-7FBC398063A0,0,Null value
2022-08-07 00:34:37.487182,undefined,mintestob02:sys:,OB_MYSQL,OB_MYSQL_COM_LOGIN,failed,-4152,6664us,0us,0us,0us,Y0-7FBC39C063A0,0,Null value
2022-08-07 00:35:32.469241,undefined,mintestob02:sys:,OB_MYSQL,OB_MYSQL_COM_LOGIN,failed,-4152,6121us,0us,0us,0us,Y0-7FBC398063A0,0,Null value

##proxy的进程和端口看上去都没有问题
[admin@pdmphb11 ~]$ obd cluster list
±---------------------------------------------------------------------+
| Cluster List |
±------------±-------------------------------------±----------------+
| Name | Configuration Path | Status (Cached) |
±------------±-------------------------------------±----------------+
| mintestob02 | /home/admin/.obd/cluster/mintestob02 | running |
| minagent | /home/admin/.obd/cluster/minagent | running |
±------------±-------------------------------------±----------------+
[admin@pdmphb11 ~]$

[admin@pdmphb12 ~]$ ps -ef|grep proxy
root 337225 1 0 Aug05 ? 00:00:00 bash ./start_proc.sh ./agent/pos_proxy.py &
root 337233 337225 0 Aug05 ? 00:01:47 pos_proxy
root 338205 1 0 Aug05 ? 00:07:38 /home/admin/ocp_agent/obagent/obstat2 -o http://10.249.240.11:8080 -c mintestob02 obproxy -f 20
admin 361323 1 0 00:03 ? 00:00:58 bash /home/admin/obproxy/obproxyd.sh /home/admin/obproxy 10.249.240.12 2883 daemon
admin 361353 1 4 00:03 ? 00:57:32 /home/admin/obproxy/bin/obproxy --listen_port 2883
admin 471702 471531 0 20:34 pts/0 00:00:00 grep --color=auto proxy
[admin@pdmphb12 ~]$ sudo netstat -nalp|grep 2883
tcp 0 0 0.0.0.0:2883 0.0.0.0:* LISTEN 361353/obproxy
[admin@pdmphb12 ~]$

1 个赞
#2

##密码肯定是没有问题， 指定2881端口就可以连接。使用proxy端口指定cluster name就连接失败
[admin@pdmphb11 ~]$ obclient -h 10.249.240.12 -uroot@sys -P2881 -prootroot -c -A oceanbase
Welcome to the OceanBase. Commands end with ; or \g.
Your MySQL connection id is 3221667552
Server version: 5.7.25 OceanBase 3.1.4 (r10000092022071511-b4bfa011ceaef428782dcb65ae89190c40b78c2f) (Built Jul 15 2022 11:45:14)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MySQL [oceanbase]>

#3

看下ODB配置有没有问题？可以把你的ymal配置发出来看下嘛，密码IP可以隐藏在发出来。

1 个赞
#4

还有试下 root@proxyro 可以登录吗？

#5

是不是代理的问题？

#6

[admin@pdmphb11 mintestob02]$ cat config.yaml
user:
username: admin
key_file: /home/admin/.ssh/id_rsa.pub
oceanbase-ce:
servers:
- name: pdmphb12
ip: 10.10.254.12
- name: pdmphb13
ip: 10.10.254.13
- name: pdmphb14
ip: 10.10.254.14
- name: pdmphb15
ip: 10.10.254.15
- name: pdmphb16
ip: 10.10.254.16
global:
devname: bond0
cluster_id: 1111
memory_limit: 15G
system_memory: 5G
stack_size: 512K
cpu_count: 16
cache_wash_threshold: 1G
__min_full_resource_pool_memory: 268435456
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
syslog_level: WARN
enable_syslog_wf: false
enable_syslog_recycle: true
max_syslog_file_count: 10
appname: mintestob02
root_password: rootroot
proxyro_password: rootroot
pdmphb12:
mysql_port: 2881
rpc_port: 2882
home_path: /home/admin/oceanbase-ce
data_dir: /data3/data
redo_dir: /data4/redo
zone: zone1
pdmphb13:
mysql_port: 2881
rpc_port: 2882
home_path: /home/admin/oceanbase-ce
data_dir: /data3/data
redo_dir: /data4/redo
zone: zone2
pdmphb14:
mysql_port: 2881
rpc_port: 2882
home_path: /home/admin/oceanbase-ce
data_dir: /data3/data
redo_dir: /data4/redo
zone: zone3
pdmphb15:
mysql_port: 2881
rpc_port: 2882
home_path: /home/admin/oceanbase-ce
data_dir: /data3/data
redo_dir: /data4/redo
zone: zone4
pdmphb16:
mysql_port: 2881
rpc_port: 2882
home_path: /home/admin/oceanbase-ce
data_dir: /data3/data
redo_dir: /data4/redo
zone: zone5
obproxy-ce:
servers:
- 10.10.254.12
- 10.10.254.13
- 10.10.254.14
depends:
- oceanbase-ce
global:
listen_port: 2883
prometheus_listen_port: 2884
home_path: /home/admin/obproxy
rs_list: 10.10.254.12:2881;10.10.254.13:2881;10.10.254.14:2881;10.10.254.15:2881;10.10.254.16:2881
enable_cluster_checkout: false
cluster_name: mintestob02
obproxy_sys_password: rootroot
observer_sys_password: rootroot

1 个赞
#9

##最终发现是 proxyro 用户的密码有问题，识别不到cluster name。 通过重置改用户密码解决。
##这个环境只是我自己测试使用，并没有修改过密码，之前也是可以正常连接，但装好OCP后，
##在管理页面上点了一些东西，执行过合并等操作后 发现无法正常登录。

@shunwah @Kongdom 多谢二位~~

[admin@pdmphb11 mintestob02]$ obclient -h 10.10.254.12 -uroot@sys -P2881 -prootroot -c -A oceanbase
MySQL [oceanbase]> SELECT User, Host, Password FROM mysql.user;
±------------±-----±------------------------------------------+
| User | Host | Password |
±------------±-----±------------------------------------------+
| root | % | *6c362347ebeaa7df44f6d34884615a35095e80eb |
| ORAAUDITOR | % | *9753e2cf9d2dcd5e13c052f581c310ac70c62723 |
| proxyro | % | *e9c2bcdc178a99b7b08dd25db58ded2ee5bff050 |
| monitor | % | *1975d095ac033caf4e1bf94f7202a9bbfeeb66f1 |
| ocp_monitor | % | *e71e8fca7c4d9a221e2678d75f30b3796b84202b |
| yangmin | % | *3096620613872c52e676a90a8986b657591af05f |
±------------±-----±------------------------------------------+
6 rows in set (0.033 sec)

MySQL [oceanbase]>
MySQL [oceanbase]> alter user proxyro identified by ‘rootroot’;
Query OK, 0 rows affected (0.066 sec)

MySQL [oceanbase]>
[admin@pdmphb11 mintestob02]$ obclient -h 10.10.254.12 -uroot@sys#mintestob02 -P2883 -prootroot -c -A oceanbase
Welcome to the OceanBase. Commands end with ; or \g.
Your MySQL connection id is 38
Server version: 5.6.25 OceanBase 3.1.4 (r10000092022071511-b4bfa011ceaef428782dcb65ae89190c40b78c2f) (Built Jul 15 2022 11:45:14)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MySQL [oceanbase]>

1 个赞
#10

:handshake: :handshake: :handshake:

#11

这里应该是zone名，你把zone1设置为pdmphb12 zone2设置为pdmphb13。。。依次

上面的zone名，你把zone1设置为pdmphb12 zone2设置为pdmphb13。。。依次
但这里又zone设置为zone1 然后pdmphb13:
但这里又zone设置为zone2 然后pdmphb14:
。。。依次
这个zone的顺序都不对哦，名称也不对。

zone: zone1
pdmphb13:
mysql_port: 2881
rpc_port: 2882
home_path: /home/admin/oceanbase-ce
data_dir: /data3/data
redo_dir: /data4/redo
zone: zone2
pdmphb14:
mysql_port: 2881
rpc_port: 2882
home_path: /home/admin/oceanbase-ce
data_dir: /data3/data
redo_dir: /data4/redo
zone: zone3
pdmphb15:
mysql_port: 2881
rpc_port: 2882
home_path: /home/admin/oceanbase-ce
data_dir: /data3/data
redo_dir: /data4/redo

1 个赞
#12

https://open.oceanbase.com/docs/tutorials-cn/V1.0.0/10000000000012299
你可以参考一下官方zone的设置。

image
image1051×959 42.9 KB

1 个赞
#13

好的 :wave:不客气