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

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)