MySQL Database
| Home | Project | People | News | For Team | Calendar | Library |
MySQL
A MySQL database is hosted on Edhar (edhar.genomecenter.ucdavis.edu) and is administrated by Morgan Langille.
If you would like an account please email Morgan with your desired user name.
Databases
- IMG
- Houses all of the genomes and genes information from the JGI IMG database.
- It also contians protein family data / information as well.
Web Interface (phpMyAdmin)
You can connect and browse the MySQL database with your web browser by logging into phpMyAdmin. Note that this website is using a self-signed SSL certificate for extra security. Upon your first visit, your web browser will most likely ask you to confirm that you want to connect to the website using this certificate.
Connecting via Perl using DBIx::Class
DBIx::Class is a Perl module that allows easier interaction with databases such as MySQL. The basic idea is that you query the database using objects instead of writing SQL statements (here is an introduction)
DBIx::Class is installed on Edhar and works very well with the IMG database. To use the module you need to include it at the beginning of your perl script:
use lib "/var/opt/iseem/perl_modules"; use IMG::Schema;
/home/mlangill/iseem/perl_modules/IMG/test.pl is an example showing the use of this module to access data in the IMG database.
Tunneling
In order to connect to the MySQL server on edhar from genbeo (or any other machine) you need to create an ssh tunnel.
Basically,
ssh -f -N -L 3307:localhost:3306
-f -N tell it just to login into the remote computer in the background. If you don't use these then you would have to open up a separate terminal.
-L is the option for port forwarding. Following it is the port number that you want to forward the remote port to. You can make this anything you like. The only reason I don't use 3306 (default mysql port) is because it is already in use by the local installation of mysql on genbeo. The middle part is usually localhost. The last part is the port on the remote machine you want to forward.
Once you issue the above command, you can then login to mysql using:
mysql -P 3307 -h 127.0.0.1 -p
For some reason you have to specify 127.0.0.1 as the host because localhost will not work.
