About the MySQL Service on Abstract
is an open source,
is a drop-in
replacement for MySQL. The lab maintains a MariaDB 5.5 service on
abstract.cs.washington.edu for general use by the CSE community. This
document offers a high-level description of the service, intended for
users of the service.
Policies and Procedures
This is an
experimental service— policies and procedures will
change, backups are not guaranteed, and the service could conceivably
- MySQL Accounts
- MySQL accounts are available to any CSE user, but 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
- 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.
- 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
- NB: Abstract does not currently support the mod_perl extension
to Apache that embeds a perl compiler into the web server.
- 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
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.
- 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 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
here, and we offer a tutorial on using it here.
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' );
If, like us, you prefer phpMyAdmin, click on the "Change password" link on the front page and
fill in and submit the form that is presented.
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
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
corenot. I use that instead of
because MySQL uses a Unix domain socket to connect
localhost, and only TCP ports are forwardable.)
Don't like ssh? Another way to do it is
stunnel. Running Windows? You can
openssh or consult the
documentation for your SSH package for instructions on port
What happens to my databases when I separate from the
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
How do I take a database dump?
See the man page for
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- as long as this service is offered.
What's so great about MySQL anyway?
You tell me! It's fast, particularly for reads, and it
looks good on a resume. Lots of canned software supports it, and some
canned software supports only MySQL. For lightweight
applications, choose different database software such
Why did my working MySQL accounts stop working when the server was upgraded to Fedora 19?
The PHP 5.5 library used to connect to MySQL no longer
supports older-format password hashes that many users still have, and
you will therefore need to update your passwords. Please read
Old Hashes on the Abstract MySQL
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 support..
webmaint at cs.washington.edu
$Id: index.html,v 1.17 2013/11/19 23:30:40 rose Exp $