How to Connect R with MySQL or how to install RMySQL package?

Sharad picture Sharad · Apr 24, 2012 · Viewed 34.4k times · Source

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

hadley picture hadley · Mar 12, 2015

You can now skip all the complicated steps and just do install.packages("RMySQL")