Using Terminal to Connect to Ensembl public MySQL Servers

Question

[oars]

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.

[LLTommy]

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?

[Pierre Lindenbaum]

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 ?

[oars]

"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
Enter password:

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

[LLTommy]

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.

[oars]

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.

[LLTommy]

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

[GenoMax]

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.

[oars]

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

[LLTommy]

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.

[oars]

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.

[Emily]

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

[oars]

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

Answer 1

[Emily]

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