This page was last updated on Monday, 04-Aug-2014 15:21:58 CDT

Installation notes for local Blast2GO database - Aug 4, 2014

Home Page

Prerequisites

• MySQL
  $ sudo apt-get install mysql-server

Installation

1. $ mkdir /xxxx/blast2golocaldb
2. $ cd /xxxx/blast2golocaldb
3. $ wget -N http://www.blast2go.com/public-data/local_b2g_db.zip
4. $ unzip local_b2g_db.zip
   

   Archive:  local_b2g_db.zip
     inflating: local_b2g_db/b2gdb.sql  
     inflating: local_b2g_db/ImportIdMapping.class  
     inflating: local_b2g_db/mysql-connector-java-5.0.8-bin.jar  
     inflating: local_b2g_db/b2gdb.sql~

5. Download and unzip four files
   From http://archive.geneontology.org/latest-full, download the file go_YYYYMM-assocdb-data.gz where YYYYMM corresponds to the actual month and year
   These are large files, so run downloads simultaneously as detached jobs
   $ YYYYMM="201407"
6. $ wget -N http://archive.geneontology.org/latest-full/go_$YYYYMM-assocdb-data.gz &> dlog.1 &
7. $ wget -N ftp://ftp.ncbi.nlm.nih.gov/gene/DATA/gene_info.gz &> dlog.2 &
8. $ wget -N ftp://ftp.ncbi.nlm.nih.gov/gene/DATA/gene2accession.gz &> dlog.3 &
9. $ wget -N ftp://ftp.pir.georgetown.edu/databases/idmapping/idmapping.tb.gz &> dlog.4 &
10. I changed the default password 'blast4it' on line 4 of b2g_db.sql to a new random password before installing, this new password is designated "secretpassword" in these instructions
    $ nano local_b2g_db/b2gdb.sql
11. Execute the file b2g_db.sql to create a database and additional tables.
    $ mysql -h localhost -u root -p < local_b2g_db/b2gdb.sql
    

    Tables_in_b2gdb
    gene2accession
    gene_info
    gi2uniprot

12. Create a similar MySQL user that will be web-accessible
    $ mysql -h localhost -u root -p
13. mysql> GRANT ALL ON b2gdb.* TO 'blast2go'@'%' IDENTIFIED BY 'secretpassword';
14. mysql> FLUSH PRIVILEGES;
15. mysql> QUIT;
16. Import the latest mysql database dump (no need to uncompress if we do it this way)
    use "secretpassword" when prompted
    $ zcat go_$YYYYMM-assocdb-data.gz | mysql -s -h localhost -u blast2go -p b2gdb
17. We can use named pipes to load the gzip compressed files directly without uncompressing
    $ mkfifo tmp.fifo && gunzip -c gene2accession.gz > tmp.fifo & mysql --local-infile -h localhost -u blast2go -p b2gdb -e "LOAD DATA LOCAL INFILE 'tmp.fifo' INTO TABLE gene2accession FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';" && rm tmp.fifo
18. $ mkfifo tmp.fifo && gunzip -c gene_info.gz > tmp.fifo & mysql --local-infile -h localhost -u blast2go -p b2gdb -e "LOAD DATA LOCAL INFILE 'tmp.fifo' INTO TABLE gene_info FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';" && rm tmp.fifo
19. I didn't test using named pipe for this one
    $ cd local_b2g_db && zcat ../idmapping.tb.gz > idmapping.tb && java -cp .:mysql-connector-java-5.0.8-bin.jar: ImportIdMapping idmapping.tb localhost b2gdb blast2go secretpassword && rm idmapping.tb && cd -
20. Enable remote access to MySQL server
    $ sudo nano /etc/mysql/my.cnf
    ...
    #bind-address = 127.0.0.1
    ...
21. $ sudo service mysql restart
22. Firewall configuration. Enable MySQL port through iptables, example limiting to local addresses
    $ # port for mysql
    -A ETH0-INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -s XXX.YYY.ZZZ.0/23 -j ACCEPT
23. To test remote access
    $ mysql -u blast2go -h xxxx.vcru.wisc.edu -p
24. Start Blast2GO and import
    http://www.blast2go.com/start-blast2go?eprivacy=1
25. Tools
    General Settings
    DataAccess Settings
26. 
28. $
29. Cleanup
    $ rm local_b2g_db.zip dlog.?
30. $
31. $