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]
examples of how to send data from R to mysql as mysql package is not available for windows xp
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