Hacker News Reader

Comment by Xeoncross | Hacker News Reader

I encourage people to google how to run phpMyAdmin, MySQL Workbench, or Sequel Pro locally, and use port forwarding over SSH. It's super simple.

Here is a command that forwards all traffic to localhost:3306 across the ssh tunnel to example.com:3306 (the mysql default port).

    ssh user@example.com -L 3306:localhost:3306

I would never run a DB admin application on the live server because it's just one more piece that might open a security hole.

View on HN

On Windows, I recommend using HeidiSQL, which handles SSH tunnels for you using PuTTY's plink.exe.

I like some of the HeidiSQL tools so much that I run it via WINE on my mac.

Be wary of Heidi and SSH tunnelling on windows - I'm sure the bugs have been fixed but I first hand realised that it re-used the tunnel for subsequent connections meaning you were not making changes on the database you thought you were - definitely caused some problems! However it is such a good client that I now use it on Linux under Wine :)

https://sourceforge.net/p/heidisql/tickets/2832/

Found my original ticket! And yes, I truncated a few production tables :(

Take it a step further. Set up a locked down bastion server that can only be reached from your IP address, and disable all access to your DB from anything but your application and the bastion server. Then tunnel your local queries through your bastion server.

  ssh -fNg -L 3306:my-secure-db.com:3306 user@bastion-server.com

Another option is to install these tools at a separate domain and setup HTTP authorization on a webserver. So automated bots scanning for vulnerable apps will not get even to the login page.

Apart from security, are there any other benefits?

I presume it'd be faster, since only remote data is MySQL data, as opposed to web server/PHP rendering.

Sequel Pro (free open source, despite name) is a native application which is much nicer to use than phpMyAdmin. So, yes there's benefits other than security.

Also very good: https://github.com/webyog/sqlyog-community

Or learn how to use the CLI client to connect. No need to install a awkward wrapper software layer.

The "awkward wrapper software layer" has so many workflow enhancements and advantages over the ho-hum CLI client that it's not even funny.

You seem to be a software developer who is advocating that people not automate something that's tedious (writing and running SELECT and UPDATE queries). Weird.

You can automate with scripts. No need to use a fancy GUI for this.

And what if I don't want to write scripts because other people have already automated all common database browsing/editing tasks for me? What if I don't want to reinvent the wheel?

With a GUI, you can click 3 times and browse 3 tables. That's just not possible without a GUI.

If GUIs didn't save people (lots of) time, no one would have invented them and the software industry would be moving away from them.

Besides being way faster and more efficient, GUIs do things that CLIs don't:

- linting/error-checking/autocomplete for SQL

- checking query sanity (warn before an unconstrained DELETE for example)

- copy/paste data (within the database and between applications)

- jump from FK cell to the row it references

The list goes on.

Scripted migrations have their advantages too:

- they can be stored in a version control system

- your coworkers can run them at their dev environment

- they can be used for automatic deployment

- you can test it in a dev environment before running at a production server. With GUI tools you have to remember and repeat exact steps you did before

- You can review the script or ask someone to look at it

I think GUI tools might be good for browsing and exploring database but not for making changes.

phpMyAdmin is not automation. It's a less-painful manual interface.

Tedium in a GUI is still tedium.

In that case I would add HeidiSQL to that list.

I use it with Wine in Ubuntu.

Reply on news.ycombinator.com