6 ms·

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 comm

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.

xrstf 10y ago
On Windows, I recommend using HeidiSQL, which handles SSH tunnels for you using PuTTY's plink.exe.
- jredwards 10y ago
  I like some of the HeidiSQL tools so much that I run it via WINE on my mac.
- voycey 10y ago
  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 :)
  - voycey 10y ago
    https://sourceforge.net/p/heidisql/tickets/2832/ https://sourceforge.net/p/heidisql/tickets/2832/ Found my original ticket! And yes, I truncated a few production tables :(
kgdinesh 10y ago
Apart from security, are there any other benefits?
- bdcravens 10y ago
  I presume it'd be faster, since only remote data is MySQL data, as opposed to web server/PHP rendering.
- paulannesley 10y ago
  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.
  - PavlovsCat 10y ago
    Also very good: https://github.com/webyog/sqlyog-community https://github.com/webyog/sqlyog-community
SeoxyS 10y ago
Or learn how to use the CLI client to connect. No need to install a awkward wrapper software layer.
- coldtea 10y ago
  The "awkward wrapper software layer" has so many workflow enhancements and advantages over the ho-hum CLI client that it's not even funny.
- smt88 10y ago
  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.
  - 3legcat 10y ago
    You can automate with scripts. No need to use a fancy GUI for this.
    - smt88 10y ago
      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.
      - codedokode 10y ago
        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.
RhodesianHunter 10y ago
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
Shorel 10y ago
In that case I would add HeidiSQL to that list. I use it with Wine in Ubuntu.
codedokode 10y ago
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.

xrstf 10y ago

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

jredwards 10y ago
I like some of the HeidiSQL tools so much that I run it via WINE on my mac.
voycey 10y ago
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 :)
- voycey 10y ago
  https://sourceforge.net/p/heidisql/tickets/2832/ https://sourceforge.net/p/heidisql/tickets/2832/ Found my original ticket! And yes, I truncated a few production tables :(

kgdinesh 10y ago

Apart from security, are there any other benefits?

bdcravens 10y ago
I presume it'd be faster, since only remote data is MySQL data, as opposed to web server/PHP rendering.
paulannesley 10y ago
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.
- PavlovsCat 10y ago
  Also very good: https://github.com/webyog/sqlyog-community https://github.com/webyog/sqlyog-community

SeoxyS 10y ago

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

coldtea 10y ago
The "awkward wrapper software layer" has so many workflow enhancements and advantages over the ho-hum CLI client that it's not even funny.
smt88 10y ago
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.
- 3legcat 10y ago
  You can automate with scripts. No need to use a fancy GUI for this.
  - smt88 10y ago
    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.
    - codedokode 10y ago
      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.

RhodesianHunter 10y ago

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

Shorel 10y ago

In that case I would add HeidiSQL to that list. I use it with Wine in Ubuntu.

codedokode 10y ago

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.