About the MySQL Service on Abstract

Policies and Procedures

MySQL Accounts

MySQL accounts are available to any Abstract user, but, unlike your Abstract account, you must explicitly request one. Your MySQL username will be the same as your CSE username, but the initial password (which you can and should change) will differ. Request an account by sending an email to support.

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 lab will authorize users to create databases that match their MySQL usernames, followed by an underscore, followed by anything else (including nothing else). For example, user farnswrth might create a database named farnswrth_test and another named farnswrth_production. This approach makes it possible for users to create their own databases— as many as they need— without stepping on the toes of colleagues.

Network Access

MySQL can be accessed either via a Unix socket— useful only on the same machine as that hosting the server— or a TCP socket. Mindful of security concerns, the operating system on Abstract has been configured to allow access to the MySQL network port only from CSE hosts on Allen Center subnets, but 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 Abstract from off-campus, we suggest that you use a solution such as SSH port forwarding.

Client Software

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 abstract.cs.washington.edu or just -h abstract. -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: Abstract 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 on abstract 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 Abstract.

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 (hosted on cubist.cs) that opens a connection to a MySQL database and dumps the contents of a simple table to an HTML table.

JDBC

We also offer the JDBC driver for MySQL, installed in $JAVA_HOME/jre/lib/ext/. Here is the source of a very simple simple Java program (hosted on cubist.cs) that opens a connection to a MySQL database and dumps the contents of a simple table to standard output.

phpMyAdmin

phpMyAdmin is an application, coded in PHP, that provides point-and-click support for many of the common administrative operations you might perform on a MySQL database. We provide it on Abstract here, and we offer a tutorial on using it here.

Frequently-asked Questions

You were just... kidding about not taking backups, right?

Right. We take an application-level backup every night in the wee hours, and we keep those dumps about three nights deep. Plus the filesystem on which they reside is subject to routine file system backups. But we still don't guarantee it.

I can't possibly remember the initial password you sent me, which looks like line noise. How can I change it?

If you are a user of the mysql command line client, you can change your password like this:

   SET PASSWORD = PASSWORD( 'your-new-password' );

I forgot my MySQL password. What now?

Contact support at cs.washington.edu.

SSH port forwarding? Is that an on-the-QT shipping service for fortified wines?

SSH port forwarding is a scheme for making a TCP port on a remote server available on a local client. MySQL uses TCP port 3306, so one way to use the mysql command line client on an off-campus machine to attach to the MySQL service on abstract would be to forward port 3306 there to 3306. Here's an example openssh command line:

  ssh -L 3306:localhost:3306 abstract.cs.washington.edu

And here is an example mysql command line:

  mysql -h corenot -p

(In this example, the hostname of the client machine is corenot. I use that instead of localhost because MySQL uses a Unix domain socket to connect to localhost, and only TCP ports are forwardable.)

Don't like ssh? Another way to do it is with stunnel. Running Windows? You can use Cygwin with openssh or consult the documentation for your SSH package for instructions on port forwarding.

What happens to my databases when I separate from the department?

We will silently backup and then drop the databases of users who separate as soon after that happens as possible. We will discard the backups on an unpredictable schedule. In practice, the interval before either step can take quite a while, but don't count on it. To preserve your data, take a database dump before you separate.

How do I take a database dump?

See the man page for mysqldump.

I need the features of MySQL version Y, and you only offer version X. Can you upgrade for me?

Eventually! We offer whatever version of MySQL comes with our operating system distribution, which is based upon Fedora. When they upgrade, so will we.

What's so great about MySQL anyway?

You tell me! It's fast, particularly for reads, and it looks good on a resume. By default, though, it doesn't support advanced features like transactions or foreign keys. For advanced features, please read MySQL documentation for information about the "InnoDB" storage engine. Or, for lightweight applications, choose different database software such as sqlite.

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 maintainer.

References

• MySQL Documentation (external link)
• PHP Documentation (external link)
• phpMyAdmin documentation (CSE authentication required)

$Id: index.html,v 1.11 2008/04/25 17:53:36 rose Exp $