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

Apply this fix for Leopard to add mysql server to your pref pane so you can start and stop it from there.
(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
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

3. Read in your table.
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;

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:

user = myusername
host = localhost
password = mypassword

database = utah

Then start R:

# 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>