I try to keep my VPS (Virtual Private Server) as light as possible. That’s also why I don’t have PHPMyAdmin installed and wouldn’t want to install it. The only way of accessing MySQL and executing commands is through the mysql CLI (Command Line Interface), but I don’t always remember the exact queries to create a user, database or to grant privileges.
I recently decided to look for some GUI (Graphical User Interface) tools to tweak and administrate a MySQL server. I found the quite well-known MySQL Administrator (and Query Browser).
The problem is that by default (and for obvious security reasons), my MySQL server is not accessible from the internet (only accessible by localhost itself). This is where SSH tunnels enter the action!
The process is quite easy and quick to do.
First, create a ssh tunnel between the desktop computer and your server, we will listen on port 5555 locally (or whatever port you’d prefer) and redirect data to port 3306 on the server (3306 is the MySQL port).
ssh -L 5555:localhost:3306 server.domain.net
And it should connect to your server through ssh as usual except the SSH tunnel will be running the whole time your session is on.
Open MySQL Administrator. The hostname will be 127.0.0.1 (or localhost), port is 5555 (or whatever you put), and your username and password are the ones you wish to use to connect to your SQL server. Note that to access certain functions you need to connect using root (or an admin account).
And you’re done!
The advantages of this method are:
- you do not need to have MySQL be accessible from the internet
- you only need SSH and MySQL running
- This is totally secure as data is encrypted by SSH
- Really convenient compared to a CLI
- You need to create the tunnel every time you wish to connect (unless you create an alias or launcher, etc…)