Exercise: Accessing MySQL and viewing table content

Goals: Learn how to access a MySQL database and look at table structure and content. From your Hebrides.wi.mit.edu account, access a MySQL database.

See /education/bioinfo2005/db4bio/ for course page

See http://www.mysql.com/documentation for MySQL documentation

1. From the terminal on your laptop, connect to Hebrides using the secure shell (ssh) command:

ssh –l username hebrides.wi.mit.edu

and you’ll be prompted for your password.

2. Type pwd (“print working directory”) to find your home directory on Hebrides. In the future you may be creating and accessing new directories. If you ever get lost, typing cd (“change directory”) will bring you back to your home directory. Another very common Unix command is ls (“list”) to list the files in the current directory.

3. To access My SQL on Hebrides, use the command:

mysql –u username –p – D database

with the following options:

-u username (replace with your MySQL username –[not necessarily your Hebrides username])

-p password (you will be prompted for your password)

-D database (This takes you directly to the database we’ll be using)

4. Important notes: if you’re logged in to MySQL, you should get the MySQL command prompt:

mysql>

so if you don’t see this, you’re not logged into the database. Keep in mind that every MySQL command must end with a semicolon. This also means that you can split up a long command into several lines. If you don’t get the MySQL command prompt back after you’ve entered a command, like

mysql > show databases

there’s a good chance you forgot the semicolon. Add it on any subsequent line and your command will be processed.

5. Find out what databases are on the system:

mysql > show databases;

6. To access one of these databases:

mysql > use database;

where “database” is replaced with the name of the one you’d like to access. In this case, enter the database “db4bio”.

7. To get a list of tables in this database:

mysql > show tables;

8. To get a description of data fields and types in a table:

mysql > describe table;

where “table” is replaced with the one you’d like to access.

9. To look at some of the data in this table, use a general “SELECT” command. Since these tables are large, you’ll want to limit the number of rows (tuples) in your query to 5 or so:

mysql > SELECT * from ________ LIMIT 5;

10. [Optional] Create a file containing the query and use Unix redirection to save the output of the query into another file.

a. Exit from MySQL: quit

b. Create a directory (“dbclass”) and switch into it: mkdir dbclass; cd dbclass;

c. Create a text file called query1.sql

To do this with pico, a simple text editor, type pico query1.sql

Type the query from step 9 in the editor and save it with the ^x (Control-x) command.

d. Back in the shell, to your MySQL login command, add the SQL file (query1.sql) as input and the file query1.out as output all on one line.

mysql –u username –p –D db4bio < query1.sql > query1.out

e. View the outputfile: more query1.out