Inexpensive shared servers, like the one Open-Meta.org is using, include MySQL database software that you might be able to access from other servers or computers. But when you try to look up how to do this, you are likely to get instructions that are out-of-date and that appear more complicated than the process actually is.
My host is using cPanel version 62.0.20. The cPanel Databases area looks like this:
The key item for allowing remote access is Remote MySQL®. Use it to tell cPanel the IP address of the computer you want to give access to, which in this context is called the access host. In network-administration-talk a “host” means some kind of online computing resource. So, in this case, the computer with MySQL running on it is the “host,” and the computer you’re granting access to is the “access host.”
If that’s your own computer, its IP address is likely to be a dynamic, rather than static, IP address (one that changes every time your computer or its router restarts). To find out what your IP address is, enter what is my IP address in a Google search.
An IP address consists of four numbers separated by dots. If you know you have a static IP address, use the number Google gives you. Otherwise, you almost certainly have a dynamic IP address.
In that case, you should replace the number after the final dot with a percent sign (n.n.n.%). This is a wildcard that will match any final number. Typically the first three numbers of a dynamic IP address don’t change, just the final one.
Next, put that number in the Remote MySQL® Add Access Host box and click Add Host. That button should really say Add Access Host.
To be clear – when you try to use a MySQL database from the computer you’ve added as an “access host,” you have to provide the name of your “host.” But now it wants the name the computer running MySQL, which in this case is your web server – for example, in my case, my “host” is open-meta.org. With the right additional credentials (database name, user name, password), you can now access a MySQL database on your web server from your own computer.
Use the MySQL® Database Wizard to set up new databases and users. In this context, a database is a discrete set of tables with optional characteristics. A user gets a username and password combination and is allowed some level of access to a particular database. MySQL has 18 different privileges you can give or deny each user on each database.
MySQL® Databases will also set up new databases and users, but is a little more complicated. It also allows you to check, repair, and delete databases and to add and delete users. You can change user passwords, add or remove a specific user to or from a specific database and modify the user’s privileges on a specific database (the last one isn’t obvious, but if you add an existing user to an existing database, you’ll get a chance to modify the user’s privileges).
Finally, phpMyAdmin is a very cool program that gives you a visual interface to your MySQL databases. It also shows you the MySQL code it generates at each step, which you can copy and paste into your own programs.
To interact with your MySQL databases from your own computer using R, there are several packages you should install, including DBI, RMySQL, pool, and dplyr. That last one provides five “verbs” (types of functions) for manipulating either dataframes or MySQL tables.
Here are some resources for learning how to put all this stuff together:
- Jenny Bryan’s Introduction to dplyr
- Hadley Wickham’s dplyr tutorial at useR 2014 – part 1
- Hadley Wickham’s dplyr tutorial at useR 2014 – part 2
- Data Transformation chapter of Hadley Wickham’s R for Data Science
- Hadley Wickham’s Introduction to dplyr package vignette
- Hadley Wickham’s Databases dplyr package vignette
- Shiny articles on Databases (five links in the Databases section near the bottom of the page.