Skip to content

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:
  1. shell> mysql -u root
  2. mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');
  3.  
  4. # to check this logout and back in as root
  5. mysql>exit
  6. shell> mysql -u root -p
  7.  
  8. #Remove anonymous users from your DB (note that '' is not a doublequote, but two singles):
  9. mysql> DROP USER ''@'localhost';
  10.  
  11. #Optionally if you want to access the DB remotely: (replace host_name)
  12. mysql> SET PASSWORD FOR 'root'@'host_name' = PASSWORD('newpwd');
  13. mysql> DROP USER ''@'host_name';
  14.  
  15. #If you don't know your hostname:
  16. mysql> SELECT Host, User FROM mysql.user;
  17.  
  18. # add a regular user - optional, but I do it for security reasons.
  19. mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP on *.* to 'myusername'@'localhost' identified BY 'mypwd';
  20.  
  21. #quit and login as that user
  22. mysql> exit
  23. shell> mysql -u jamiejones -p

3. Read in your table.

MySQL:
  1. mysql> CREATE DATABASE utah;
  2. mysql> USE utah;
  3. mysql> SHOW tables;       # empty!
  4. # obviously the following needs to be adapted to your table's fields
  5. mysql> CREATE TABLE example (id INT NOT NULL AUTO_INCREMENT, sex CHAR(1), age DECIMAL(3.2), birthdate DATE, PRIMARY KEY(id));
  6. mysql> SHOW tables;       # there we go!
  7.  
  8. # insert values
  9. # since you don't want to do the following a  650,000 times:
  10. mysql> INSERT INTO example (sex,age,birthdate) VALUES ('M',35,'1840-12-01');
  11. # you can use a tab delimited file or other ways if that does not work for your data
  12. 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:

CODE:
  1. [client]
  2. user = myusername
  3. host = localhost
  4. password = mypassword
  5.  
  6. [rs-dbi]
  7. database = utah

Then start R:

R:
  1. library(RMySQL)
  2. # Create mysql instance
  3. m <- dbDriver("MySQL")
  4.  
  5. # Open a connection with credentials which returns a pointer to the DB.
  6. con <- dbConnect(m)
  7.  
  8. # Use that pointer to submit your query,
  9. # which returns another pointer to the results.
  10. res <- dbSendQuery(con, "select * from example")
  11.  
  12. # get them all!
  13. dd <- fetch(res, n=-1)

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

Categories: R.

Tags: ,

Comment Feed

2 Responses

  1. examples of how to send data from R to mysql as mysql package is not available for windows xp



Some HTML is OK

or, reply to this post via trackback.

Continuing the Discussion