phpMyAdmin to manage remote database

February 26, 2017 9.6k views
MySQL Ubuntu 16.04
dmnkhhn
By:
dmnkhhn

Hello guys,

I am using two droplets for my application: one is running mySQL only, the other one runs Nginx (PHP, mysql-client etc.).
I have followed this tutorial: https://www.digitalocean.com/community/tutorials/how-to-set-up-a-remote-database-to-optimize-site-performance-with-mysql to set things up.

Everything is running fine but I would love to manage the MySQL database from the second droplet where I already have a running webserver.
I can install it but how can I tell phpMyAdmin to use the remote datebase? It looks like it tries to create a new database on the very droplet to manage its settings but the installation routine leaves no place to change that behaviour.

Any help? Thanks, Dominik

Log In to Comment
2 Answers
jtittle1 February 26, 2017

@dmnkhhn

phpMyAdmin is definitely one way to manage your databases, though what I recommend to my own clients is a slightly smaller tool that offers the majority of the functionality that phpMyAdmin does in a single file. That tool would be Adminer.

https://www.adminer.org/#download

It’s a single PHP file, requires no installation and it just works regardless of what directory you drop it in to. The biggest benefit is that unlike phpMyAdmin, you can just delete the file when you’re done and then you no longer need to worry about security or removing other configuration.

You can use wget to pull down the file from a server, or you can upload it manually. As long as PHP is running on the server you pull it to, it’ll just work.

As far as connecting to a remote MySQL server, that too is possible, as long as the server you’re connecting to has been configured to accept remote connections – i.e. port 3306 is open and the bind-address is set to either a public IPv4 address or a private network IPv4 address (which is useful when the Droplets are in the same data center).

Reply
  • newbie February 27, 2017

    @dmnkhhn
    that is an excellent tool you have shared. i like it very much.

    really simple and fast web interface for managing Mysql. im gonna give it a try for my own production environment.

    by the way, can you tell me how can i increase db import limit? the default is 2mb.

    really appreciate it.

    • jtittle1 February 27, 2017

      @dmnkhhn

      The limit you’re referring to would be controlled by PHP, so you’d need to adjust the config of your php.ini file. You’ll want to look at upload_max_filesize and post_max_size.

      By default, upload_max_size is set to 2M and post_max_size is set to 8M, so you’ll want to adjust those accordingly and then either restart Apache or restart PHP-FPM.

      As an example, if the SQL file you’re trying to upload is 32MB, I would set:

      upload_max_size = 48M

      and

      post_max_size = 64M

      One thing to keep in mind, larger SQL dumps should be restored from the CLI, not through a web-based interface. If you’re trying to restore 100M-1GB+ files, you should use to the CLI.

newbie February 26, 2017

@dmnkhhn
i also want to know that.

my own project is under development and when ill put it on production im gonna need same thing you are looking.

take a look at this and please let me know it works or not.
http://stackoverflow.com/questions/16801573/how-to-access-remote-server-with-local-phpmyadmin-client

Reply
  • hansen February 26, 2017

    That’s exactly how you do it. Add the server to the config.inc.php - I believe you can skip the user/password and then it’ll force you to input that after you select the server from the dropdown on login.

Have another answer? Share your knowledge.

Related