I ran into this last weekend in a Mac OSX hackathon - it took me a solid 4 hours to put it all together, despite having a few reference materials (mentioned at the end). I did not find an easy walk, so I decided to publish it while it is fresh in my mind.
I'm not sure about compatibility with Windows, but hopefully these instructions will make it easier for you as well.
I tried to get R and MySQL to communicate in a local environment (maybe a server environment might need changes). I use XAMPP (although I did not use RMySQL to connect), but in the end I was able to use the PHP page to write the R file, execute this file and write R to the MySQL table. As far as I know, this only works for MacOSX ...
All the software used was in the form of dmg, so no binary settings are needed.
Download R and run some basic commands to make sure you have a job.
In R, you need to install RODBC (if you don't already have one). Enter this in console R.
install.packages("RODBC")
This installs RODBC, but with OS Mavericks some files are no longer included, so you get an error
ODBC headers sql.h and sqlext.h not found
and you need to get sql.h and sqlext.h files in the right place.
To make this the easiest way, make sure you have installed homebrew (simple instructions). Then use this code in the terminal to complete the installation.
After that, you again enter the console R
install.packages("RODBC")
Find MySQL for an appropriate ODBC installation . I am running Mac OSX 10.6, so I downloaded dmg and installed it. It took care of itself.
Now comes the hard part. Apparently, Mac OX took out the ODBC administrator after a recent OS version, so you need to download ODBC Manager ( http://www.odbcmanager.net/ ). It is also a dmg file, so just drag it to the utilities folder.
I had difficulty installing 5.3.6 dmg install (crash), so instead I installed 5.2.7.
Open ODBC Manager. You need to configure DSN, so click on the System DSN tab and click on add.
A pop-up window will appear asking you to select a driver. I had a "MySQL ODBC 5.2 Driver" based on my installation of MySQL ODBC. Click OK. If you do not see the driver, you need to confirm that ODBC MySQL is installed.
In the next pop-up window, make the data source name (DSN) what you want, but remember that this is the name you should use to call from R. In the underlying area of keywords (keywords will be in quotation marks and the value will be in round brackets), ADD
"database" (with the value of your database name)
"server" (for a local environment, DO NOT use localhost - use the local IP address 127.0.0.1 instead. *** This was the key for me)
"uid" (database user id)
"pwd" (database password)
"socket" (not sure if this was necessary, but after a few lessons it stayed in my configuration and everything works, so maybe you need it. You can find your socket location in my.cnf - do a spotlight search The location of the socket file is under the CLIENT)
Here my configuration looked like this:
DSN ("test" - it was the top)
database ("TVs")
socket ("/Applications/XAMPP/xamppfiles/var/mysql.sock")
uid ("root")
pwd ("")
server ("127.0.0.1")
In R, follow below - I believe that these last 3 steps should be done every time you start R and before you make a MySQL query.
Library (RODBC)
Make sure you enable MySQL and Apache from the XAMPP control panel.
Then do
odbcConnect ("test") - notice how I used my DSN in double quotes. Change as necessary.
This should help you. You can read other tutorials about creating MySQL queries in R.
I hacked it together out of a lot of great posts about stack overflows (thanks everyone!), Random other sites / messaging history and Joseph Adler's “R In A Nutshell” book, but let me know if I missed something or it is unclear.
Good luck