Using RMySQL from Ubuntu

  • Install MySQL

sudo apt-get install mysql-server

  • Check if Server is Running

sudo netstat -tap | grep mysql

I use the MySQL command line to check it

To connect

mysql -h localhost -u root -p

To see databases

mysql>show databases;

To see tables

 

mysql> show tables from mysql;

To quit mysql

 

mysql> \q

Screenshot from 2015-07-23 17:48:51

  • Install and load RMySQL from within R

install.packages(“RMySQL”)

library(RMySQL)
  • I connect using this

mydb = dbConnect(MySQL(),
user=’root’,
password=’XXX’,
host=’localhost’,
port = 8018,
dbname=’mysql’)

  • I write sql queries using this
> dbGetQuery(mydb, "select * from  servers")
[1] Server_name Host        Db          Username    Password   
[6] Port        Socket      Wrapper     Owner      
<0 rows> (or 0-length row.names)
> dbGetQuery(mydb, "select * from  db")
 [1] Host                  Db                   
 [3] User                  Select_priv          
 [5] Insert_priv           Update_priv          
 [7] Delete_priv           Create_priv          
 [9] Drop_priv             Grant_priv           
[11] References_priv       Index_priv           
[13] Alter_priv            Create_tmp_table_priv
[15] Lock_tables_priv      Create_view_priv     
[17] Show_view_priv        Create_routine_priv  
[19] Alter_routine_priv    Execute_priv         
[21] Event_priv            Trigger_priv         
<0 rows> (or 0-length row.names)

Screenshot from 2015-07-23 18:16:09

Source-

https://help.ubuntu.com/12.04/serverguide/mysql.html

RMySQL 0.10.0

https://mkmanu.wordpress.com/2014/07/24/r-and-mysql-a-tutorial-for-beginners/

Author: Ajay Ohri

http://about.me/ajayohri

One thought on “Using RMySQL from Ubuntu”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s