Suppose you have a database server (db.example.com) which only accepts connections from the web server (www.example.com). The web server accepts SSH connections from anywhere (so ssh user@www.example.com works). And you have 200 tables to update.
Start by opening a tunnelled SSH connection in a terminal window like so:
[shell]ssh user@www.example.com -L 31123:db.example.com:3306 [/shell]
The command starts like a normal ssh connection. The extra syntax is -L <local port number>:<database host name>:<remote port number> MySQL normally uses port 3306. I already have a mysql process running locally, so I need to use a different port. I picked 31123 at random.
This starts a normal SSH connection, AND also opens 31123 on your local computer. When something on the local computer connects to 31123, SSH forwards the connection to www.example.com, which forwards it to db.example.com on port 3306.
Now, in a second terminal window (or any MySQL client), connect to port 31123 on the local host (which magically gets forwarded twice to db.example.com).
[shell]mysql --user=nines_vps --host=127.0.0.1 --port=31123 database_name[/shell]
Boom, mysql will ask for a password.
Tips:
Be careful with the--port=31123. If you miss one of the dashes, mysql will interpret it as a password instead of a port number.
mysql will attempt to connect to a socket on the local host. To get around that you must specify an IP address or hostname (mysql will connect to the socket if you use localhost as the host name).- Log in to post comments