Accessing MySql Database in R for Analytics

Step-1: Open / connect to your MySql by running following command.                                        

Step-2: Create test database and ensure test database created or not as shown in following figure.

Step-3: Now create a table student with student id and total marks as its fields as shown in following figure.

Step-4: Insert some data into student table as shown below.

Now student data is ready in MySQL. Now we will see how we can access student data in R.

Step-5: Before installing RMySQL package in R make sure you installed mysql-connector-c. To install mysql-connector-c run the following command in terminal.

sudo apt-get install libmysqlclient-dev    // on debian or ubuntu

yum install mysql-devel   // on RHEL, Cent OS, Fedora

Step-6: Now Open your RStudio and install MySQL package. To Install MySQL package click on Tools ->Install Packages...

Step-7: Now write a R Script as shown in following picture to read student data from MySQL. For more details please read the comments mentioned in below R code.

Fixing GPG NO_PUBKEY Error

              By default R version 2.14.1 available on my Ubuntu 12.04 LTS system. I decide to generate some interactive graphs in R but I was not able to generate them because 2.14.1 is very old version of R. So, I decided to upgrade my R from 2.14.1 to 3.2.2 version. So, added an entry into sources.list as shown in following picture.

List of R mirrors can be found at https://cran.r-project.org/mirrors.html.

After made an entry into sources.list and update the system by running following command.

sudo apt-get update

This update command some times throws an error like "GPG error NO_PUBKEY". We can fix this error using y-ppa-manager. To install y-ppa-manager run the following list of commands.

sudo add-apt-repository ppa:webupd8team/y-ppa-manager  // adding repository for y-ppa-manager

sudo apt-get update // updating repository

sudo apt-get install y-ppa-manager // Installing y-ppa-manager

After installing y-ppa-manager, start y-ppa-manager by typing the same in the terminal and it will display GUI as shown below.

Click on Advanced Option and then Select "try to import all missing GPG keys" and click on OK as shown in following figure. It will fix the error "GPG error NO_PUBKEY". Run sudo apt-get update again to see error is fixed or not.