Biostar Beta. Not for public use.
Using Terminal to Connect to Ensembl public MySQL Servers
1
Entering edit mode
20 months ago
oars • 150

For starters, I have mySQL downloaded. I'm trying to drop into an interactive mysql session by typing in the following:

$mysql --user=anonymous --host=ensembldb.ensembl.org  I've also tried the following: mysql -h useastdb.ensembl.org -u anonymous -P 5306  If I can get this to work correctly, my next step is: mysql> use homo_sapiens_core_90_38;  Then I can start looking at tables, etc. Maybe this is all wrong, perhaps a more logical approach is to get my terminal session already in mySQL mode...then connect? Any help is very appreciated. If this helps, I also have the Workbench interface downloaded. I understand that you can also open a scripting session via the workbench. ADD COMMENTlink 1 Entering edit mode I don't know too much about ensembl SQL server - I did not even know that this was possible - but that is why I was interested so I did google. If you look here and study the table, it tells you that "useastdb.ensembl.org" is associated with "MariaDB" . So, studying the table in the link, I would say you have to try different combinations of these ports and urls to see what could work for you. But anyway, what do you want to accomplish, if you are looking at these tables? Might a webservice API call not be 'good' enough (=less complicated) than the SQL thing? ADD REPLYlink 1 Entering edit mode works fine here: $ mysql --user=anonymous --host=ensembldb.ensembl.org -D homo_sapiens_core_90_38 -e 'show tables'
+---------------------------------------+
| Tables_in_homo_sapiens_core_90_38     |
+---------------------------------------+
| alt_allele                            |
| alt_allele_attrib                     |
| alt_allele_group                      |
| analysis                              |
| analysis_description                  |
| assembly                              |
| assembly_exception                    |
| associated_group                      |
| associated_xref                       |
| attrib_type                           |
| coord_system                          |
| data_file                             |
| density_feature                       |
| density_type                          |
| dependent_xref                        |
| ditag                                 |
| ditag_feature                         |
| dna                                   |
| dna_align_feature                     |
| dna_align_feature_attrib              |


are you working behind a proxy ?

0
Entering edit mode

"are you working behind a proxy ?"

This might be were I'm having an issue, I can only call up mysql when I type of following:

bin root# ./mysql -u root -p


then I'm greeted with the >mysql...otherwise, I cannot access mysql.

0
Entering edit mode

The fact that you might just be able to start mysql with root permissions on your laptop has nothing to do with the question if your internet connection goes through a proxy or not. If you have no idea about your proxy status, there are even websites that try to help like http://amibehindaproxy.com/ (no kidding). No idea if that is reliable.

Anyway, I ask the third and last time: What are you trying to do? Maybe there are better ways than SQL anyway yo answer your question?

Do you know SQL? Because, sorry - not trying to insult you - but if you don't know the difference between proxy and root permissions, I somehow doubt that you know SQL...? And if that is the case - it will be no fun even if you manage to connect to the database.

0
Entering edit mode

I'm simply trying to connect to the Ensembl mySQL server and perform some very basic queries. And yes, I'm very new to bioinformatics (as in 5 weeks) so everything is a struggle at the moment.

0
Entering edit mode

Glad you were able to connect ... and no worries, it's also a struggle later on! ;)

0
Entering edit mode

Did you make a note of the following on their web page?

Also note that direct MySQL queries on the database are not suited to retrieve sequences; to retrieve sequences please use the Perl API.

0
Entering edit mode

I hope I do not need to use Perl as I'm not familiar with that language.

0
Entering edit mode

Exactly, thus my question what he is actually looking for, but he did not really reply so far. And well, no oars, you do not need necessarily Perl to access a web service.

0
Entering edit mode

This:

export PATH=\$PATH:/usr/local/mysql/bin


Followed by this:

mysql --user=anonymous --host=ensembldb.ensembl.org


Worked!

Many thanks to everyone for their help.

0
Entering edit mode

Are you getting some kind of error message with those commands? What is the error message?

0
Entering edit mode

Many thanks to everyone. I was able to connect to the mySQL server and started examining the homo_sapiens_core DB.

2
Entering edit mode
18 months ago
EMBL-EBI

That looks fine to me. Now you can just do normal MySQL queries by following the schema here.