Installing/Recreating Ucsc Annotation Database Locally

Question

[Click downvote]

I want to download the rattus norvegicus annotation database so that my program can do lookups quickly and often without pestering the UCSC MySQL server.

Problem is, the download is just very many .sql files and .gz files: http://hgdownload.cse.ucsc.edu/goldenPath/rn4/database/

Eg.

  affyAllExonProbes.sql                      15-Mar-2009 14:44  1.5K  
  affyAllExonProbes.txt.gz                   15-Mar-2009 14:44   13M  
  affyExonTissues.sql                        15-Mar-2009 14:42  1.8K  
  affyExonTissues.txt.gz                     15-Mar-2009 14:42  254M  
  ...

There are no instructions on how to use this to recreate the database locally. Is it possible to do in one, or a few bash commands?

Ps. downloading them all in one go isn't a problem.

Answer 1

[Pierre Lindenbaum]

pipe the sql files in mysql

gunzip the *.txt.gz files

the loop over the txt files an invoke 'mysql local load data infile ' http://dev.mysql.com/doc/refman/5.1/en/load-data.html

mysql -u login -p -e "LOAD DATA LOCAL INFILE 'affyExonTissues.txt' INTO TABLE affyExonTissues" -D rr5

[Click downvote]

Thank you. Will accept when I get to try it out. Much appreciated.

[Pierre Lindenbaum]

you might have to create the mysql database before everything: mysql -u root -p -e 'create database rr5'

[Pierre Lindenbaum]

... and use LOCAL

Answer 2

[Maximilian Haeussler]

It might be easier to download the raw mysql data files, that's just one rsync command: Assuming that your mysql dataDir directory is /var/lib/mysql the command would go like this: rsync -avzP --delete --max-delete=20 rsync://hgdownload.cse.ucsc.edu/mysql/rn3/ /var/lib/mysql/rn3/

For details see http://genomewiki.ucsc.edu/index.php/Browser_Installation#Download_assembly_database_tables