About the MySQL Service on Cubist
MySQL 5.0 is a cross-platform, open
source, SQL-compliant database server used at CSE for instruction.
This document offers a high-level description of the service, intended
for teaching teams and students of courses that use it.
This document has these sections:
Documents have bugs!
Please report any you find
in this document here.
Support staff manages MySQL accounts on Cubist. Typically accounts
will be created soon after the quarter begins for instructional staff
and known enrollees of each course known to be planning to use the
service. Student accounts and data will typically be deleted shortly
after the end of the quarter.
- Permissions
- MySQL allows a user to be granted the rights to create
databases that match a pattern, and to have full control over those
databases but no others. The Cubist MySQL accounts are created to
authorize users to create databases that match their MySQL
usernames, followed by an underscore, followed by anything else. For
example, user farnswrth might create a database named
farnswrth_test and another named farnswrth_production.
This approach makes it possible for students
to create their own databases-- as many as they need-- without stepping
on the toes of other students.
- Network Access
- MySQL can be accessed either via a Unix or TCP socket, but we
expect student MySQL accounts to be configured only for TCP socket
access. Mindful of security, the operating system on Cubist has been
configured to allow access to the MySQL network port only from CSE
hosts on Allen Center subnet, not including the wireless
subnet. Unfortunately, authorizing other hosts on an ad-hoc basis is
not a practical option, so if you wish to access the MySQL service on
Cubist from off-campus, we suggest that you use a solution such as
SSH port-forwarding.
- mysql
- MySQL comes with a eponymous command-line client. Typical
command line usage is
mysql -p -h <hostname> -u <username> <database name>
-p means to prompt for a password. You can provide the
password immediately after the switch-- for example,
-psecret-- but it's not recommended for security
reasons. -h is used to specify the host with the service,
so you would typically use -h cubist.cs.washington.edu or
just -h cubist. -u specifies the MySQL username,
and defaults to your Unix username. The final argument, if provided,
is the name of the database to connect to.
- Type man mysql for details.
- DBI and DBD::mysql (perl)
- DBI is the perl "database independant" layer, which allows you
to write database-enabled perl scripts that are intended to be as
portable between database servers as possible. DBD::mysql is the DBI
"driver" for MySQL. To help you get started with DBD::mysql, here
are the source code and output of a very simple CGI script that
uses DBD::mysql to open a connection to a MySQL database and dump the
contents of a simple table to an HTML table.
- NB: Cubist does not currently support the mod_perl extension
to Apache that embeds a perl compiler into the web server.
- PHP
- PHP is an open-source programming language that is targetted at
web applications. The syntax is similar to that of perl, but, like
the Microsoft-proprietary Active Server Pages, it allows you to
freely mix programming code and HTML in the same source files. The
way we run it at CSE is embedded into the web server. There are a
myriad of extensions to PHP that glue it to applications and
services, amongst which is, of course, a MySQL extension that is
supported on Cubist.
- A key difference between CGI scripts and PHP scripts at CSE is
the user context in which they run. CGI scripts run from your web
directory run in the context of your own account. In contrast, all
PHP scripts run in the context of the web server account. That
all-scripts-as-one-user mode is a less secure approach that provides
you little protection from malevolent or playful colleagues.
- Here is the output and
source code of a very simple
PHP script that opens a connection to a MySQL database and dumps the
contents of a simple table to an HTML table.
Why isn't my question answered here?
It hasn't been asked yet (or we don't know the
answer). You can ask it now by sending
email to the author.
webmaint at cs.washington.edu
$Id: index.html,v 1.5 2010/10/19 19:59:28 rose Exp $