I have production server running on Debian 9. I would like to ask you what is safe way to manage MySQL/MariaDB server (on production server).

MySQL/MariaDB server listens only on localhost, because I don't want to be accessible from internet for security reasons.

So if I want something change or look for something, then I have to login into MySQL/MariaDB server using cli and write SQL queries. But I am tired of that.

I would like to ask you what are possibilities or what is the best way to manage MySQL/MariaDB server in safe way and in comfortable way.

I find out some several solutions, but I don't know if they are right, so I write them.

Solution 1

Find some kind of frontend cli software for MySQL/MariaDB server, like Midnight Commander (yes I know that Midnight Commander is like file explorer, but it has that "GUI feel" even it is cli software), but I don't know if that software exists.

Solution 2

Install phpMyAdmin without apache (using -S parameter in PHP) to avoid some security risks, block phpMyAdmin's port using firewall (to be not reachable from internet, only localhost) and connect to phpMyAdmin only via SSH tunneling.

If you know something better, I would be happy for any advice.

  • "Install phpMyAdmin without apache (using -S parameter in PHP) to avoid some security risks" No! No no no! The PHP docs explicitly state it shouldn't be used on a production server like this. Using the PHP development server introduces security risks, it doesn't remove them. – ceejayoz Sep 7 '18 at 21:29
  • Most SQL clients (I like Sequel Pro on a Mac; there are various similar apps for Windows) can handle connecting to a server's local MySQL install via an SSH tunnel. – ceejayoz Sep 7 '18 at 21:31
  • @ceejayoz Sequel Pro is paid for SSH tunneling, how can phpMyAdmin introduce security risks if it listens only on localhost and port is blocked behind firewall. – tomsk Sep 7 '18 at 21:36
  • Sequel Pro is free and open-source. sequelpro.com php -S introduces security risks (as does phpMyAdmin, which has a long history of vulnerabilities). Listening to localhost with a blocked port is no guarantee. (Facebook discovered that when someone used their "put in a URL and we'll crawl it" Open Graph debugger tool on internal URLs that aren't directly available, but were available to the tool). This is why you're way better off with a client like Sequel Pro (or MySql Workbench, which you've accepted as an answer.) – ceejayoz Sep 7 '18 at 21:51

What you’re probably going to want is MySQL Workbench, which will work for most use cases, has a nice feature set and easy GUI, and is an industry standard.

P.s.: it works as well with MariaDB, but keep in mind that the passphrase binary encoding used by the Workbench MySQL client doesn’t always play nicely across an SSH tunnel, and may need to be transmitted as plaintext. Not a problem, but requires changing in the Advanced preferences for the client connection.

  • Oh I see that it supports SSH tunneling, thanks :) – tomsk Sep 7 '18 at 21:39
  • There are also dbeaver and datagrid with similar feature sets. Sqlyog is a bit more expensive and windows only, but a lot of people swear by it – jdog Sep 8 '18 at 4:36

mycli has ssh transport being developed so hopefully soon.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.