SSH Tunneling

[mygon0172]

I am trying to connect to DB using SSH Tunneling. However, there is an error, as shown below.

Action : Add a DB Server Group Name : local Host Name : 192.168.56.161 Port Number : 3306 Database Name : information_schema SSH Tunneling? YES Local Host Name : 192.168.56.161 Local Port : 22 Add DB Credentail? YES DB User Name : admin DB Password : ••••• Retype DB Password : ••••• Test Connection? YES

RESULT### -> Connection test to jdbc:mysql://192.168.56.161:22/information_schema failed: Packet for query is too large (4739923 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable

What's the problem?

LOG

Sep 05, 2016 6:00:18 PM com.yahoo.dba.perf.myperf.springmvc.DbController handleRequestImpl INFO: action: 2, db [local,192.168.56.161,0,0,3306,information_schema, ssh: true,192.168.56.161,22,virtual:false,admin], by user myperf Sep 05, 2016 6:00:18 PM com.yahoo.dba.perf.myperf.common.DBUtils testConnection INFO: Test connection to ([local,192.168.56.161,0,0,3306,information_schema, ssh: true,192.168.56.161,22,virtual:false,admin]): jdbc:mysql://192.168.56.161:22/information_schema Sep 05, 2016 6:00:18 PM com.yahoo.dba.perf.myperf.common.DBUtils testConnection SEVERE: Exception com.mysql.jdbc.PacketTooBigException: Packet for query is too large (4739923 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable. at com.mysql.jdbc.MysqlIO.readPacket(MysqlIO.java:569)

[xrao]

Sorry for late reply. You need to setup ssh tunneling yourself on your machine using either ssh, openssl, putty or whatever you have. The reason mysql perf analyzer does not do that is we are reluctant to record and store the user's OS credentials.

[mygon0172]

Thank you for answer. Only 22 (ssh) port open to the DB server We are trying to access. However, I can not find a way.

[xrao]

You can setup ssh tunneling with either ssh (linux or mac), or putty on windows. For example, on a host you can ssh (for example, localhost, or we call it jump_server) to your DB server with ssh, you can do: ssh -f -L 13306:my_db_server_dns_name:3306 my_db_server_dns_name -N

This way, you can think your db is running on localhost:13306, or jump_server:13306.

When you configure mysql_perf_analyzer, you have two options:

1. Treat localhost:13306 or jump_server:13306 as your db server, so not use mysql perf analyzer ssh tunneling.
2. Use my_db_server:3306 as the db, use ssh tunneling, and fill localhost or jump_server as local host name and 13306 as local port.

For ssh tunnling with ssh (for example, on linux, mac), see http://www.revsys.com/writings/quicktips/ssh-tunnel.html.

For windows, you can use putty to setup the tunneling, see sample at https://howto.ccs.neu.edu/howto/windows/ssh-port-tunneling-with-putty/. You can also check windows openssh: https://github.com/PowerShell/Win32-OpenSSH

[mygon0172]

Sorry delayed feedback. With your help, I succeeded in tunneling. Thank xrao.

Additionally METRICSDB.DBINFOS.PORT,LOCAL_PORT, VIRTUAL_HOST column properties The UNSIGNED been added.