MySQL Hints

Tue Mar 8 17:01:30 EST 2005

This material is mostly from Steve Elgersma of the CS support group, to whom many thanks.

MySQL is described at length at the MySQL web site; there are both reference materials and a good tutorial. The book MySQL by Paul DuBois is another good description.

MySQL support is provided through a CS machine called studentdb. This machine is only accessible from within the CS domain; you can't get at it from OIT systems. You should use one of the CS servers, either the Solaris systems at or the Linux systems at There are no logins on studentdb itself, everything is done via the client programs mysql and mysqladmin.

You should also look at Storacle, which is an interface to an Oracle database run by OIT for campus use. The examples there look quite similar to the MySQL examples shown in class. Indeed, if you do things carefully, most of your system would not depend on which of these systems you ultimately choose.

COS 333 project groups that want to use this MySQL service will have to get a project account on the machine. Send mail to csstaff@cs with a suitable project name (the usual net-id restrictions apply) and the names of the group members; someone on the CS staff will set up the account. You must also have an individual CS account of some sort. CS majors likely have an account already; if not, you can get one because you are taking COS 333, again by sending mail to csstaff@cs. Class accounts last for the semester; if you want your system to live longer, you will have to find another home for it over the summer.

MySQL accounts are usually set up with your login name as the password, so the first job will be to reset it:

    bolle:$ mysqladmin -h studentdb -u <username> -p password <new password>
where bolle is one of the Solaris servers, "studentdb" is the database machine, "<username>" is your user name, and "<new password>" is your new password. It will prompt you for your old password, and then silently resets it.

After you do this, all further mysql or mysqladmin commands will require:

    -h studentdb -u <username> -p
Note that anything using the "-p" by itself on the command line will prompt you for your password after you hit "Enter".

To connect to your database, you would type:

    bolle:$ mysql -h studentdb -u <username> -p <dbname>
where "<username>" is your username, and <dbname> is the database name (in this case, they're the same). After you enter your password at the prompt, you'll be in the mysql command-line utility.

To access the database via PHP, just create a file.php in your public_html directory that accesses the database:

	$machine = "";
	$user = "<username>";
	$passwd = "<password>";
	$db = "<dbname>";

	$connect = mysql_connect($machine,$user,$passwd);
	if(!$connect) {
		echo "Error: couldn't connect to db!<BR>\n";
		$error = mysql_error($connect);
		echo "<LI> $error\n";

	\\ query code here...
People considering Perl access to a database might investigate whether PHP would be a viable alternative.

You will surely have noticed that the database password is stored in the clear in the php file. This couod be accurately described as an enormous hole in security, and I don't know a good way around it, though one can invent kludges.

A word of warning: studentdb is not backed up. If you want to be sure that you don't lose things, you can use the command mysqldump to save your database in a file somewhere else, like on another machine, from which it can be later restored if necessary. You have been warned.