CSL MySQL Database Service

Overview

The CSL MySQL database service lets you use databases for personal, class or research projects without having to worry about administering them. The CSL keeps the server running, upgrades it and makes daily backups.

Requesting a User and Database

Because MySQL's authentication system doesn't integrate with the CSL authentication methods, you must first request a MySQL user that you will connect as when you use the database. This is done via the "Request a MySQL user" form on https://www-auth.cs.wisc.edu. You must supply both the login name you would like to use as well as the host the user will connect from ( % matches any host) - both are required as MySQL considers both the login name and the host it is connected from as a distinct user when it evaluates database permissions. Try to specify your host as narrowly as possible - if you will only be connecting from a specific workstation, use that as your host for better security. Consider requesting multiple users with different hosts if you will only connect from a few hostnames or IPs. Using % to match any host makes connecting to the database from anywhere possible, but isn't quite as secure.

After you have created a user, request a database by filling out the "Request a MySQL database" form on https://www-auth.cs.wisc.edu. Choose a name, the encoding and collation you would like, a reason for the database, and the MySQL user you would like to 'own' the database, possibly the user you just made. The MySQL user that is designated as the owner of the database will be given all permissions on the database, as well as the ability to grant permissions on that database to other existing MySQL users.

Using a Database

The server is running on mysql.cs.wisc.edu on default port 3306. Here's how to connect with a bunch of different programs:

mysql (Command Line Interface)

On Unix:

/usr/bin/mysql -h mysql.cs.wisc.edu -u <user> -p <database name>

On Windows:
Please email lab@cs.wisc.edu - we are still working on the client to Windows workstations.

Please see the man page for mysql for more information.

Perl

#!/s/std/bin/perl

use DBI;
use CSL::mysql;
$dbh = DBI->connect("dbi:mysql:database=<database name>;host=mysql.cs.wisc.edu", "<database user>", "<database user password>");

PHP

<?php
$dbh = mysql_connect("mysql.cs.wisc.edu", "<database user>", "<database user password>");
?>

Python

#!/s/python-2.5.2/bin/python
import MySQLdb
db=MySQLdb.connect (host='mysql.cs.wisc.edu', user='<database user>', passwd='<database user password>', db='<database>'")

For More information

• MySQL website: Official MySQL website
• Comprehensive Perl Archive Network: Information on the DBI and DBD::mysql Perl modules.
• PHP MySQL pages: Information on using PHP with your MySQL database.
• http://dev.mysql.com/usingmysql/python/: MySQL's using python page.

Collaboration and Sharing the Database

The MySQL user that is designated as your database's owner has all permissions on your database and also the ability to grant those options to other existing MySQL users. You can share the database with your own or others' MySQL users by using the GRANT command, specifying the user you would like to be able to connect to your database. Remember that MySQL treats a login name and the host is is connecting from as a distinct user - you will need to know both in order to use GRANT. The syntax for the GRANT command can be found at http://dev.mysql.com/doc/refman/5.1/en/grant.html.

Be careful who you grant permissions to, especially if you give someone else the grant option - they will be able to allow more existing MySQL users to view and alter your database! A good rule of thumb is to only give the bare minimum required permissions to a user and alter them later if they need additional access.

Database Quotas

There are no official disk space or bandwidth quotas on mysql.cs.wisc.edu. However, please remember that many people and projects use this server. Therefore, please try to keep your database size and I/O bandwidth usage low (about 5 GB of raw data is a practical limit). If you have any specific questions or if you need to run intensive queries on a very large database, please contact the lab and we will do our best to resolve the issue. If you are creating problems for other users on mysql.cs.wisc.edu, the lab will contact you and work with you to resolve the issue.

For More Information

The scope of MySQL is far too great for this document. As always, if you have questions, if you want to learn something, or if you just want to see official documentation, please consult the MySQL website.