Version of 7 October 2007

This page will explain how you can access or download and use MySQL, the widely used open access relational database system that is available for about fifty platforms/systems.

Here is a link to a local copy of Chapter 3 of the large MySQL Reference Manual. It is a 28 page tutorial (PDF).

MySQL on the CCIS CGI Server

The College has instructions for the various resources hosted on the our "CGI Server". The section for MySQL can be found here.

Here's a run through of my doing a few things with MySQL on the CGI Server, looking at a table I had created in my futrelle database. The table is one that could represent metadata for a jpeg image. The copy below of my interaction starts after an ssh connection from home. I then ran mysql (my password's not shown), showed my database (the CGI server only allows you one, named after your login name. Then I "used" that database and showed the only table I had created in it earlier, one named jpegs. Finally, I show the structure of the table. I could continue, adding data, querying, and so forth - things you'll be doing in the course. There were a few glitches in my run of mysql, such as forgetting the line terminating semicolon on one command, by I edited them out. You should do the same; don't turn in commands or results that are unintentioned, just the ones needed.

Note that for large amounts of data, for re-use of commands, and debugging, it's useful to run MySQL in batch mode. A page on this can be found here.

>mysql -h cgi -u futrelle -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 223811 to server version: 4.0.12-max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+----------+
| Database |
+----------+
| futrelle |
+----------+
1 row in set (0.03 sec)

mysql> use futrelle
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+--------------------+
| Tables_in_futrelle |
+--------------------+
| jpegs              |
+--------------------+
1 row in set (0.00 sec)

mysql> show columns from jpegs;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| flename    | varchar(100) | YES  |     | NULL    |       |
| bytelength | int(11)      | YES  |     | NULL    |       |
| height     | int(11)      | YES  |     | NULL    |       |
| width      | int(11)      | YES  |     | NULL    |       |
| comments   | varchar(255) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> 

MySQL on Mac OS X (10.4.10)

The example above runs identically on the Mac. Same commands, same results. Only difference is that had created five databases on my Mac, with one, btest, having the same jpegs table as above.

GUI tools for MySQL

There are two solid GUI-based tools for MySQL that you can download and install (they're Java apps, but that's transparent). They are MySQL Administrator and MySQL Query Browser.

Here's the Administrator startup on my Mac:

Once started, you'll see something like this:

and so forth .....

The Query Browser looks like this. I've shown the help mode, rather than the query mode.

There's also an MySQL Workbench, though it's still in beta. I'll also demo a workbench built into FileMaker and also OmniGraffle, which is good at drawing diagrams for databases, but is not connected to them. Below is someone else's screenshot of the MySQL Workbench.

Here's an omnibus view (a 1445 by 840 px png) of MySQL on Windows XP, running on a Windows virtual machine under VMware on my MacBook Pro (Intel).

Information resources for MySQL

A reference manual for MySQL is available on the MySQL site. But it is nearly 2000 (!) pages long, a 13MB PDF. Also available as web pages online. Its large size doesn't make it useless, by any means. For example Chapter 3 of the refman is a tutorial, only 28 pages in the PDF. Personally, I have found books such as Learning MySQL (O'Reilly, 2007) and MySQL Crash Course (Sams, 2006) useful. I'll get these on Reserve soon. A lot of these resources go over database concepts and SQL itself, which is well-covered in our course textbook.

Getting your own copy of MySQL

The main MySQL site has downloads for many platforms. I had no problems with my Mac or Windows installs.

Return to CSU430 Fall 2007 homepage. or RPF's Teaching Gateway or homepage