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