RMySQL Package Installation
Installing a package on R seems like an easy task of single command, but not with the RMySQL package. The package requires certain Windows dynamically linked library(.dll) and compilation on Windows.
The below article outlines the steps required for the installation of the RMySQL package on Windows 7 64-bit, and would work on 32-bit version as well.
Make sure you have ‘R’ installed on the system 😉
For ease one can try RStudio.
- Install the recent release of RTools. (Perform this with the admin privileges, else one might face an error)
- Install MySQL and library files of MySQL from https://dev.mysql.com/downloads/mysql/. (Assuming we have done the same at location C:// )
- Create (if not present) or Edit the file ‘Renviron.site’ located at
C:\Program Files\R\R-3.1.2\etc\Renviron.site , and add the line:
The above path should be the one where mysql files have been created in Step-2. If in case one doesn’t know the home directory of R, check it by running R.home() in the R session.
(By default “Renviron.site” does not exist. Create the file in your preferred text editor with the above variable, check if the system hides the file extensions. Make sure you have enough admin rights to create the “Renviron.site” under the \etc folder of R.)
- Ensure that the file ‘Rcmd_environ’ located at
C:\Program Files\R\R-3.1.1\etc\Rcmd_environ file has the line:
If not, add the above line with other variables set under the ‘## from R.sh’ header. Do not make use of quotes at any of the place in it.
- Make sure that the ‘PATH’ variable under ‘System variables’ in the environment variables must have the paths to both R and mysql.
Add ‘ C:\mysql-5.6.21-winx64\ ‘ and ‘C:\Program Files\R\R-3.1.1‘, separated by semi-colon and without quotes. (Make sure the back slash at the end of the MySQL path is present in the PATH variable.)
- Add a separate variable in the ‘System variables’ defining the home for MySQL : (Make use of the path in Step-2)
- MYSQL_HOME = C:\mysql-5.6.21-winx64
- Copy the libmysql.lib file from /mysql/lib to /mysql/lib/opt to meet the dependencies. If ‘opt’ folder is not present then skip this step.
- Copy libmysql.dll present at … \mysql-5.6.21-winx64\lib\libmysql.dll to C:\Program Files\R\R-3.1.2\bin AND to … \mysql-5.6.21-winx64\bin\ OR to Windows/System32 directory.
- Make a cross check of the above set environment variables in RStudio, or R, by the following command:
This should show the path set for the environment variable in Step-6 as the output.
- Restart RStudio, close if there is any running session.
- Install the ‘RMySQL’ package by running the below command, and then load it.
You are good to go with the ‘RMySQL’ package!
Create a connection object and then make your desired query via R :
con <- dbConnect(MySQL(), host="127.0.0.1", port= 3306, user="username", password = "password", dbname="databasename")
dbGetQuery(conn=con, "select * from test_table;")
– Palash Goyal