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.