Connecting to phpMyAdmin Database Remotely Using R.

[SplashDance]

My Issue

Basically, I want to be able to use the R programming language to interact
with a MySQL database available via phpMyAdmin. Right now I'm trying to
determine whether the errors I'm getting trying to connect to this database are
because I'm passing in the wrong arguments or whether there is just something
fundamentally wrong with the way I'm going about everything.

Preliminary Information

I was given the following information to work with:

• host: tempdb.companyname.com/phpMyAdmin
• username: dbs_guest
• password: guestpassword
• database name: companyname

What I've Tried and What Has Worked

Some things worth noting:

1. I have been able to log into phpMyAdmin via Chrome and have successfully run
   queries so I know that the MySQL server is working and the login information is
   correct.
2. I've also been able to export the database as an .sql file; have set it up
   locally; have accessed it directly using MariaDB; and have accessed it using
   R and the relevant R packages.

In other words, it doesn't appear to be an issue with either the remote database
itself, my installation of MariaDB,
or with R and its packages. [Note: the database I currently have access to
represents only a small sample of the full database so downloading everything
locally isn't a viable option].

The Solution I'm looking For

Right now I'm trying to figure out if the variables I'm using in the R
function to establish a connection are wrong (and, if this is the case, what
should I be using instead) or if I'm just going about it all wrong--perhaps
because there's just something fundamentally different about phpMyAdmin that I'm
missing. If this is indeed the case, I'd like to know what I would need
to establish a connection (for instance, would I need the ip address of the
MySQL server itself to be able to connect remotely?).

To give you a sense of the values I've tried using, here is an example of the
R code I've tried:

con <- dbConnect(RMariaDB::MariaDB(),
                 host = 'tempdb.companyname.com/phpMyAdmin',
                 dbname = 'companyname',
                 user = 'dbs_guest',
                 password = 'guestpassword')
temptable <- tbl(con, "temptable")

In particular, I'm not sure if the value of "host" is correct, especially
since when logged I'm logged in the server is given as "Server: Localhost via UNIX socket".
Also, would "user" need to be listed as "dbs_guest@localhost" (as it is when
logged into phpMyAdmin) or would that be implied by the value of "host". Lastly,
I could also pass in a "port" argument but I have no idea what that would be
anyway. Oh, and I'm unsure if any of this would depend on whether or not I'm currently
signed into phpMyAdmin (using Chrome, say) while running these commands.

To reiterate, I'm just trying to find out if there's something about phpMyAdmin
that makes what I'm trying to do impossible (and what I would need to do instead)
or if I've just got the variable values all wrong.

Thanks for your patience.

Sincerely,
Steve

Additional context

phpMyAdmin

• phpMyAdmin version: 4.0.10deb1

Database server

• Server: Localhost via UNIX socket
• Server type: MySQL
• Server version: 5.5.41-0ubuntu0.14.04.1 - (Ubuntu)
• Protocol version: 10
• User: dbs_guest@localhost
• Server charset: UTF-8 Unicode (utf8)

Web server

• Apache/2.4.7 (Ubuntu)
• Database client version: libmysql - 5.5.41
• PHP extension: mysqli

[williamdes]

@SplashDance I am confused, you know phpMyAdmin is only a web interface and not a database server, right ?

I do not understand why R would have anything to do with phpMyAdmin ?

cc @ibennetch
cc @MauricioFauth

Did I misunderstand your issue ?

[SplashDance]

Right. I get that phpMyAdmin an interface for interacting with a database server and not the server itself. Or, at least, I have some vague understanding that to interact with the server I have to go through phpMyAdmin.

But right now I only have the phpMyAdmin address to work with and I was thinking (and I get that this could be way off) that I would have to, in effect, connect to said database through phpMyAdmin somehow.

[williamdes]

I have to go through phpMyAdmin.

I think not, and from what I know we no not offer any api or way to interact with databases.
The only way is human interaction with the interface

Did you try to connect with R to mysql like http://stla.github.io/stlapblog/posts/RMySQL.html suggests ?

But right now I only have the phpMyAdmin address to work with

Maybe the database server is on the same machine than the database, this would be why you only have the hostname of the "phpmyadmin"/database? server

Does that answer your question @SplashDance ?

[ibennetch]

Most MySQL installations prevent incoming network connections by default. In order to directly connect from an external address, such as an application running on your local computer, you'd have to expose the MySQL port to the internet. That's generally a bad security practice. Many times, phpMyAdmin runs on the same server as the MySQL instance, so it connects either to the localhost address (127.0.0.1) or via a UNIX socket file (which is what your phpMyAdmin is doing).

Within phpMyAdmin itself, we deliberately don't include any method of reflecting or redirecting incoming connections directly to MySQL because that would pretty much be exposing MySQL the same as opening up the MySQL port directly to the internet, again a very bad idea. That's why using the phpMyAdmin URL itself as your connection string isn't working. You're correct that you'd need the server IP address (or hostname) here instead.

Generally, you've got three options for connecting to a remote MySQL/MariaDB instance:

1. exposing the port directly (which I recommend against),
2. hosting your application directly on the server and accessing it remotely (such as is the case with phpMyAdmin, WordPress, or many other tools, although I don't know whether R can be used in this way), or
3. exposing an API on the server through some custom application, which is hosted on the MySQL server, and which your local application can directly connect to.

Unfortunately, phpMyAdmin can't help you with any of those.

[SplashDance]

First of all, let me just say that I really appreciate all of your responses (especially considering the amateurishness of my initial question) and I especially appreciate Isaac's response--it was particularly thoughtful and very helpful.

If anything, it's a relief to finally get a definitive answer so I won't have to waste any more time trying different combinations of function arguments, etc.

Again, thanks for your time.
Sincerely,
Steve