Access MySQL on Mac 10.5 from R

Getting R to speak to mysql is a breeze but setting up the DB and getting the table in requires a bit of fiddling. Here goes the story.  Hope that helps and I have no typos in there.

1. Install  mysql server

Disk image for 10.4 works on Leopard
http://dev.mysql.com/downloads/mysql/5.0.html#macosx-dmg

Apply this fix for Leopard to add mysql server to your pref pane so you can start and stop it from there.
ftp://ftp.mysql.com/pub/mysql/download/gui-tools/MySQL.prefPane-leopardfix.zip
(thanks to this note)

2. Set up DB and user and password.

Add mysql path to your .bash /usr/local/mysql/bin

Then secure your root user (which has no password to begin with) with a password (replace newpwd)
[mysql]shell> mysql -u root
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');

# to check this logout and back in as root
mysql>exit
shell> mysql -u root -p

#Remove anonymous users from your DB (note that '' is not a doublequote, but two singles):
mysql> DROP USER ''@'localhost';

#Optionally if you want to access the DB remotely: (replace host_name)
mysql> SET PASSWORD FOR 'root'@'host_name' = PASSWORD('newpwd');
mysql> DROP USER ''@'host_name';

#If you don't know your hostname:
mysql> SELECT Host, User FROM mysql.user;

# add a regular user - optional, but I do it for security reasons.
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP on *.* to 'myusername'@'localhost' identified by 'mypwd';

#quit and login as that user
mysql> exit
shell> mysql -u jamiejones -p
[/mysql]

3. Read in your table.
[mysql]
mysql> CREATE database utah;
mysql> USE utah;
mysql> SHOW tables;       # empty!
# obviously the following needs to be adapted to your table's fields
mysql> CREATE TABLE example (id INT NOT NULL AUTO_INCREMENT, sex CHAR(1), age DECIMAL(3.2), birthdate DATE, primary key(id));
mysql> SHOW tables;       # there we go!

# insert values
# since you don't want to do the following a  650,000 times:
mysql> INSERT INTO example (sex,age,birthdate) values ('M',35,'1840-12-01');
# you can use a tab delimited file or other ways if that does not work for your data
mysql> LOAD DATA LOCAL INFILE "insert.txt" INTO TABLE example;
[/mysql]

4. For using R you need RMySQL and DBI packs and a config file. Roughly like this:

Create a file .my.cnf with 600 permissions in your homedir:

[code]
[client]
user = myusername
host = localhost
password = mypassword

[rs-dbi]
database = utah
[/code]

Then start R:

[r]library(RMySQL)
# Create mysql instance
m <- dbDriver("MySQL")

# Open a connection with credentials which returns a pointer to the DB.
con <- dbConnect(m)

# Use that pointer to submit your query,
# which returns another pointer to the results.
res <- dbSendQuery(con, "select * from example")

# get them all!
dd <- fetch(res, n=-1)[/r]

No TweetBacks yet. (Be the first to Tweet this post)

3 thoughts on “Access MySQL on Mac 10.5 from R

  1. Nice tutorial, with this mac pc can be used to create a database. To move the database to another pc is still the same or are there specific rules. thank you

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>