How to Connect R with MySQL or how to install RMyS

Question

I am new in R and i am trying to connect R with MySQL. I have installed mysql-5.5.22-winx64 and R-2.12.0 for 64 bit. I have already set MYSQL_HOME environment path(C:\Program Files\MySQL\MySQL Server 5.5) and trying to follow these steps:

1. Install latest RTools from here
2. install MySQL or header and library files of mysql
3. create or edit file C:\Program Files\R\R-2.12.1\etc\Renviron.site and add line like MYSQL_HOME=C:/mysql (path to your mysql files)
4. copy libmysql.lib from mysql/lib to mysql/lib/opt to meet dependencies.
5. copy libmysql.dll to C:\Program Files\R\R-2.12.1\bin or to windows/system32 directory.

6. run install.packages('RMySQL',type='source') and wait while compilation will end. but still i am getting this error:-

   *> install.packages('RMySQL',type='source')
    --- Please select a CRAN mirror for use in this session ---
    trying URL 'http://ftp.iitm.ac.in/cran/src/contrib/RMySQL_0.9-3.tar.gz'
    Content type 'application/x-gzip' length 165363 bytes (161 Kb)
    opened URL
    downloaded 161 Kb
   
   * installing *source* package 'RMySQL' ...
   ERROR: configuration failed for package 'RMySQL'
   * removing 'C:/PROGRA~1/R/R-212~1.0/library/RMySQL'
   * restoring previous 'C:/PROGRA~1/R/R-212~1.0/library/RMySQL'
   
    The downloaded packages are in
     ‘C:\Users\sharad\AppData\Local\Temp\RtmpdQHwCb\downloaded_packages’
    Warning message:
         In install.packages("RMySQL", type = "source") :
   installation of package 'RMySQL' had non-zero exit status*
   

Please suggest me how can I solve this problem?

Finally I got the solution:- You can see the solution in detail on my blog.

I was working for last 2 days on Installation of R with RMySQL package, finally got the solution for that, here are the steps to install RMySQL package:-

1. DOWNLOAD SOFTWARE FROM THE FOLLOWING LINKS:

      * a. R2.13.2:  Download R from http://cran.stat.sfu.ca/index.html
   
       b. RTools 214:  Download RTools from http://cran.cict.fr
   
       c. RMySQL 0.8-0.tar.gz: Download RMySQL from 
        http://biostat.mc.vanderbilt.edu/wiki/main/RMySQL/RMySQL_0.8-0.tar.gz
   
       d. MySQL Server 5.0: download it from http://dev.mysql.com
   
       e. RSTUDIO (optional): download it from http://rstudio.org*
   

2. SET THE FOLLOWING ENVIRONMENT VARIABLES

       * a. MYSQL_HOME : <drive>/path to MySQL installation folder
            e.g. MYSQL_HOME= C:\Program Files\MySQL\MySQL Server 5.5\  
   
         b. R_HOME: <drive>/path to R installation
            e.g. R_HOME=C:\Program Files\R\R-2.13.2\
   
          c. PATH: Modify path to accommodate the above variables. *
   

   Be sure that the following paths areincluded in your Windows PATH variable: \Rtools\2.14\bin \Rtools\2.14\MinGW\bin \Rtools\2.14\MinGW64\bin

3. CREATE FOLDER AND COPY FILES

       * a. OPT: Create a folder OPT under
                C:\Program Files\MySQL\MySQL Server 5.5\lib and
            copy MYSQLLIB.LIB  the above path.
          Also copy libmysql.dll to
                 <drive>\<path>\R\R-2.14.0\bin\(64 bit) Or
                 <Drive>\<path>\R\R-2.14.0\bin\i386\ (32 bit) and
            to C:\Windows\System32.
   
         b. Renviron.site: create or edit a file 
                 <DRIVE>\<path>\R\R-2.14.0\etc\Renviron.site and 
           add a line: 
             MYSQL_HOME =”C:/Program Files/MySQL/MySQL Server 5.5/”
           NB: USE FORWARD SLASH AND DOUBLE QUOTES HERE
   
         c. libMySQL.dll: Copy this file to
               C:\Program Files\R\R-2.13.2\bin\i386 as well as
               C:\Program Files\R\R-2.13.2\bin*
   

   1. RUN COMMANDS

      a. Install.Packages: Run R GUI by clicking on the R icon on desktop or from Start menu. Type INSTALL.PACKAGES(“RMySQL”,type=”Sources”). This will download the required software from repositories.

      b. Command Prompt: Copy the downloaded zip file (in step 4.a.) and paste it under R installation folder. Go to start menu and open Command Prompt. Go to the R installation folder and type R CMD INSTALL RMySQL_0.8-0.tar.gz

      *COMMANDS:

      library(RMySQL) drv = dbDriver("MySQL") con = dbConnect(drv,host="localhost",dbname="test",user="root",pass="root") album = dbGetQuery(con,statement="select * from t_master") album*

Answer 1

Follow the guide here:

http://www.ahschulz.de/2013/07/23/installing-rmysql-under-windows/

I followed it, and it worked like a charm. :)