OceanBase obproxy配置

1 检查集群信息

observer信息

MySQL [oceanbase]> select * from __all_server;
+----------------------------+----------------------------+---------------+----------+----+-------+------------+-----------------+--------+-----------------------+------------------------------------------------------------------------+-----------+--------------------+--------------+----------------+-------------------+
| gmt_create                 | gmt_modified               | svr_ip        | svr_port | id | zone  | inner_port | with_rootserver | status | block_migrate_in_time | build_version                                                          | stop_time | start_service_time | first_sessid | with_partition | last_offline_time |
+----------------------------+----------------------------+---------------+----------+----+-------+------------+-----------------+--------+-----------------------+------------------------------------------------------------------------+-----------+--------------------+--------------+----------------+-------------------+
| 2021-12-07 15:52:36.583969 | 2021-12-08 09:26:07.204402 | 192.168.5.200 |     2882 |  1 | zone1 |       2881 |               1 | active |                     0 | 3.1.1_4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e(Oct 21 2021 10:33:14) |         0 |   1638863566011754 |            0 |              1 |                 0 |
| 2021-12-07 15:56:07.681043 | 2021-12-08 09:26:15.207936 | 192.168.5.200 |     3882 |  2 | zone2 |       3881 |               0 | active |                     0 | 3.1.1_4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e(Oct 21 2021 10:33:14) |         0 |   1638926774220189 |            0 |              1 |                 0 |
| 2021-12-07 15:56:11.908905 | 2021-12-08 11:01:11.012604 | 192.168.5.200 |     4882 |  3 | zone3 |       4881 |               0 | active |                     0 | 3.1.1_4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e(Oct 21 2021 10:33:14) |         0 |   1638932469836543 |            0 |              1 |                 0 |
+----------------------------+----------------------------+---------------+----------+----+-------+------------+-----------------+--------+-----------------------+------------------------------------------------------------------------+-----------+--------------------+--------------+----------------+-------------------+
3 rows in set (0.001 sec)

集群名称信息

可以在启动使用使用-n指定，没有指定默认名称为obcluster。
-n,--appname APPNAME application name 对应集群名称。配置obproxy需要使用到。
observer [OPTIONS]
-h,--help print this help
-V,--version print the information of version
-z,--zone ZONE zone
-p,--mysql_port PORT mysql port
-P,--rpc_port PORT rpc port
-N,--nodaemon don't run in daemon
-n,--appname APPNAME application name
-c,--cluster_id ID cluster id
-d,--data_dir DIR OceanBase data directory
-i,--devname DEV net dev interface
-o,--optstr OPTSTR extra options string
-r,--rs_list RS_LIST root service list
-l,--log_level LOG_LEVEL server log level
-6,--ipv6 USE_IPV6 server use ipv6 address
-m,--mode MODE server mode
-f,--scn flashback_scn

查询集群名称（-n,--appname APPNAME application name）

MySQL [oceanbase]> show parameters like "cluster";
+-------+----------+---------------+----------+---------+-----------+-----------+---------------------+----------+---------+---------+-------------------+
| zone  | svr_type | svr_ip        | svr_port | name    | data_type | value     | info                | section  | scope   | source  | edit_level        |
+-------+----------+---------------+----------+---------+-----------+-----------+---------------------+----------+---------+---------+-------------------+
| zone1 | observer | 192.168.5.200 |     2882 | cluster | NULL      | obcluster | Name of the cluster | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
| zone2 | observer | 192.168.5.200 |     3882 | cluster | NULL      | obcluster | Name of the cluster | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
| zone3 | observer | 192.168.5.200 |     4882 | cluster | NULL      | obcluster | Name of the cluster | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
+-------+----------+---------------+----------+---------+-----------+-----------+---------------------+----------+---------+---------+-------------------+
3 rows in set (0.006 sec)

MySQL [oceanbase]> 

2 数据配置proxyro用户

[admin@db01 tmp]$ 
[admin@db01 tmp]$ obclient -h 192.168.5.200  -u root@sys -P 2881 -p -c -A oceanbase
Enter password: 
Welcome to the OceanBase.  Commands end with ; or \g.
Your MySQL connection id is 3221656953
Server version: 5.7.25 OceanBase 3.1.1 (r4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e) (Built Oct 21 2021 10:33: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]> grant select on oceanbase.* to proxyro identified by 'SWoLCQRH' ;
Query OK, 0 rows affected (0.054 sec)

MySQL [oceanbase]> \q
Bye

3 启动obproxy，并配置密码

#命令使用说明
./bin/obproxy -h
----------------------------------------------------------------------------------
obproxy [OPTIONS]
  -h,--help                              print this help
  -p,--listen_port         LPORT         obproxy listen port
  -o,--optstr              OPTSTR        extra options string
  -n,--appname             APPNAME       application name
  -r,--rs_list             RS_LIST       root server list(format ip:sql_prot)
  -c,--cluster_name        CLUSTER_NAME  root server cluster name
  -N,--nodaemon                          don't run in daemon
  -V,--version             VERSION       current obproxy version
  -R,--releaseid           RELEASEID     current obproxy kernel release id

使用admin用户启动obproxy。

• 当没有 ocp 提供 config server 服务时, proxy 可以依赖 observer 的 rslist 直接启动。
• proxy 监听端口为 2883，使用rslist启动的集群名为 obcluster，proxy所属应用的名称为 obcluster。
• 集群名称建议和应用名称一致。
• 应用的名称名称为 observer 的 cluster 参数数值。

3.1启动obproxy

[admin@db01 tmp]$ /home/admin/obproxy-3.1.0/bin/obproxy -p 2883 -r '192.168.5.200:2881;192.168.5.200:3881;192.168.5.200:4881'  -c obcluster -n obcluster
/home/admin/obproxy-3.1.0/bin/obproxy -p 2883 -r 192.168.5.200:2881;192.168.5.200:3881;192.168.5.200:4881 -c obcluster -n obcluster
listen port: 2883
rs list: 192.168.5.200:2881;192.168.5.200:3881;192.168.5.200:4881
cluster_name: obclu

3.2 配置密码

使用root@proxysys连接，默认密码为空。

[admin@db01 tmp]$ obclient -h 192.168.5.200 -u root@proxysys -P 2883 -p
Enter password: 
Welcome to the OceanBase.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.25

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)]> alter proxyconfig set observer_sys_password = 'SWoLCQRH' ;
Query OK, 0 rows affected (0.001 sec)

MySQL [(none)]> show proxyconfiglike '%password%';
+---------------------------------+------------------------------------------+--------------------------------+-------------+---------------+
| name                            | value                                    | info                           | need_reboot | visible_level |
+---------------------------------+------------------------------------------+--------------------------------+-------------+---------------+
| observer_sys_password           | acd251af8f718bac0889a4b1e499fd3b465bfbc2 | password for observer sys user | false       | SYS           |
| obproxy_sys_password            |                                          | password for obproxy sys user  | false       | SYS           |
| inspector_password              |                                          | password for inspector user    | false       | SYS           |
| json_config_meta_table_password |                                          | meta table password            | true        | virtual       |
+---------------------------------+------------------------------------------+--------------------------------+-------------+---------------+
4 rows in set (0.000 sec)

MySQL [(none)]>

3.3 通过obproxy连接数据

用户@租户#集群名称

[admin@db01 tmp]$ obclient -h 192.168.5.200 -u root@sys#obcluster -P 2883 -p
Enter password: 
Welcome to the OceanBase.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.25 OceanBase 3.1.1 (r4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e) (Built Oct 21 2021 10:33: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 [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| oceanbase          |
| information_schema |
| mysql              |
| SYS                |
| LBACSYS            |
| ORAAUDITOR         |
| test               |
+--------------------+
7 rows in set (0.015 sec)

MySQL [(none)]>

3.4 测试集群名称和appname不同---连接失败

端口：2888
集群名称：obtest
appname：ocluster

[admin@db01 tmp1]$ /home/admin/obproxy-3.1.0/bin/obproxy -p 2888 -r '192.168.5.200:2881;192.168.5.200:3881;192.168.5.200:4881'  -c obtest -n obcluster
/home/admin/obproxy-3.1.0/bin/obproxy -p 2888 -r 192.168.5.200:2881;192.168.5.200:3881;192.168.5.200:4881 -c obtest -n obcluster
listen port: 2888
rs list: 192.168.5.200:2881;192.168.5.200:3881;192.168.5.200:4881
cluster_name: obtest
appname: obcluster
#连接失败！！！
[admin@db01 tmp1]$ obclient -h 192.168.5.200 -u root@sys#obtest -P 2888 -p
Enter password: 
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading authorization packet', system error: 11

3.5 指定不同的集群名，不指定appname--连接失败

端口：2899
集群名称：obtest
appname：空

[admin@db01 tmp1]$ /home/admin/obproxy-3.1.0/bin/obproxy -p 2899 -r '192.168.5.200:2881;192.168.5.200:3881;192.168.5.200:4881'  -c obtest
/home/admin/obproxy-3.1.0/bin/obproxy -p 2899 -r 192.168.5.200:2881;192.168.5.200:3881;192.168.5.200:4881 -c obtest
listen port: 2899
rs list: 192.168.5.200:2881;192.168.5.200:3881;192.168.5.200:4881
cluster_name: obtest

[admin@db01 tmp1]$ obclient -h 192.168.5.200 -u root@sys#obtest -P 2899
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading authorization packet', system error: 11