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)

PLAIN TEXT

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

3. Read in your table.

PLAIN TEXT

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;