In IP address-based routing, OceanBase Database Proxy (ODP) directly routes SQL requests to the specified OBServer node. You can specify an IP address by using the following two methods:
By using the ODP parameter
target_db_server. You can specify multiple IP addresses in the'ip:port,ip:port, ...;ip:port,ip:port, ... ;ip:port,ip:port, ...'format. You can use a comma (,) to separate two IP addresses and a semicolon (;) to group IP addresses. The IP address group on the left of a semicolon (;) takes precedence over that on the right. IP addresses in an IP address group have random priorities.By using an SQL statement with the following syntax:
/* TARGET_DB_SERVER = 'addrs' */ executable_sql;
This topic provides two examples to describe the two methods.
Example 1: Specify IP addresses by using a parameter
Run the following command to log on to ODP as the root@proxysys user:
obclient -h10.10.10.1 -uroot@proxysys -P2883 -p -cSpecify a group of invalid IP addresses and another group of valid IP addresses.
obclient [(none)]> ALTER PROXYCONFIG SET target_db_server = '127.0.0.1:2993,127.0.0.1:2994;10.10.10.1:50109';Log on to a user tenant of OceanBase Database and execute the following SQL statement to trigger routing:
obclient [test]> SELECT 888;View the routing process in the diagnostic logs.
[~/cl-deploy/log]$ tail -n1 obproxy_diagnosis.log | sed "s/\/n/\n/g"The following return result shows that the SQL statement is retried multiple times and finally routed to 10.10.10.1:50109.
[2023-08-21 16:12:50.404037] [56910][Y0-00007FDF4FB404E0] [ROUTE]((*route_diagnosis= Trans Current Query:"SELECT 888" Route Prompts > SQL_PARSE [INFO] Maybe counldn't get location cache of the query table since table name is empty. > ROUTE_INFO [INFO] Will route target db server and loopback addr (127.0.0.1:2993) wouldn't do congestion control > CONGESTION_CONTROL [INFO] This replica(127.0.0.1:2993) is no need to pass congestion control > CONGESTION_CONTROL [INFO] This replica(127.0.0.1:2994) is no need to pass congestion control Route Plan > SQL_PARSE:{cmd:"COM_QUERY", table:""} > ROUTE_INFO:{route_info_type:"USE_CONFIG_TARGET_DB", svr_addr:"127.0.0.1:2993"} > CONGESTION_CONTROL:{svr_addr:"127.0.0.1:2993", need_congestion_lookup:false} > HANDLE_RESPONSE:{is_parititon_hit:"false", state:"CONNECT_ERROR"} > RETRY:{attempts:2, retry_status:"FOUND_EXISTING_ADDR", retry_type:"CONF_TARGET_DB_SERVER", retry_addr:{127.0.0.1:2994}} > CONGESTION_CONTROL:{svr_addr:"127.0.0.1:2994", need_congestion_lookup:false} > HANDLE_RESPONSE:{is_parititon_hit:"false", state:"CONNECT_ERROR"} > RETRY:{attempts:3, retry_status:"FOUND_EXISTING_ADDR", retry_type:"CONF_TARGET_DB_SERVER", retry_addr:{10.10.10.1:50109}} > CONGESTION_CONTROL:{svr_addr:"10.10.10.1:50109"} > HANDLE_RESPONSE:{is_parititon_hit:"false", state:"CONNECTION_ALIVE"} )Log on to ODP as the root@proxysys user and execute the following statement to reset the parameter:
obclient [(none)]> ALTER PROXYCONFIG SET TARGET_DB_SERVER = '';
Example 2: Specify IP addresses in a comment of an SQL statement
Run the following command to log on to the root@test user tenant of OceanBase Database:
obclient -h10.10.10.1 -uroot@test -P2883 -p -cSpecify IP addresses for routing in a comment of the following statement and then execute the
SELECT 888;statement to trigger routing.obclient [test]> /* TARGET_DB_SERVER = '127.0.0.1:2993,127.0.0.1:2994;10.10.10.1:50109' */ SELECT 888;View the routing process in the diagnostic logs.
[~/cl-deploy/log]$ tail -n1 obproxy_diagnosis.log | sed "s/\/n/\n/g"The following return result shows that the SQL statement is retried multiple times and finally routed to 10.10.10.1:50109.
[2023-08-21 16:09:39.564334] [56910][Y0-00007FDF4FB404E0] [ROUTE]((*route_diagnosis= Trans Current Query:"/* target_db_server = '127.0.0.1:2993,127.0.0.1:2994;10.10.10.1:50109' */ SELECT 888" Route Prompts > SQL_PARSE [INFO] Maybe counldn't get location cache of the query table since table name is empty. > ROUTE_INFO [INFO] Will route target db server and loopback addr (127.0.0.1:2993) wouldn't do congestion control > CONGESTION_CONTROL [INFO] This replica(127.0.0.1:2993) is no need to pass congestion control > CONGESTION_CONTROL [INFO] This replica(127.0.0.1:2994) is no need to pass congestion control Route Plan > SQL_PARSE:{cmd:"COM_QUERY", table:""} > ROUTE_INFO:{route_info_type:"USE_COMMENT_TARGET_DB", svr_addr:"127.0.0.1:2993"} > CONGESTION_CONTROL:{svr_addr:"127.0.0.1:2993", need_congestion_lookup:false} > HANDLE_RESPONSE:{is_parititon_hit:"false", state:"CONNECT_ERROR"} > RETRY:{attempts:2, retry_status:"FOUND_EXISTING_ADDR", retry_type:"CMNT_TARGET_DB_SERVER", retry_addr:{127.0.0.1:2994}} > CONGESTION_CONTROL:{svr_addr:"127.0.0.1:2994", need_congestion_lookup:false} > HANDLE_RESPONSE:{is_parititon_hit:"false", state:"CONNECT_ERROR"} > RETRY:{attempts:3, retry_status:"FOUND_EXISTING_ADDR", retry_type:"CMNT_TARGET_DB_SERVER", retry_addr:{10.10.10.1:50109}} > CONGESTION_CONTROL:{svr_addr:"10.10.10.1:50109"} > HANDLE_RESPONSE:{is_parititon_hit:"false", state:"CONNECTION_ALIVE"} )