MySQL is a very fast, powerful, and easy to use database which provides an excellent backend for Perl and PHP scripts on Bingwww. Accounts for using this popular open source database are available to departments, offices, and organizations that have a web account on this server. Most departments and offices on campus have a web site which runs on Bingwww.

The purpose of this document is not to explain how to write MySQL commands - for documentation on MySQL commands, go to mysql.org. Instead, it is intended as a guide to getting started with MySQL on the University's department web server (Bingwww).

Requesting a MySQL Account

To use MySQL on Bingwww, you must first request a MySQL account. Each department/office on campus that has a web site on Bingwww can request one MySQL account. Requests should be sent to John Hagan. Separate MySQL accounts will not be given for every database project developed by a particular department/office. If a department/office is developing multiple projects, it can use the same database; however, the developers should make sure to use a unique table prefix for each project so that they don't corrupt any other tables which might be present.

Accessing MySQL

Once your MySQL account is established, you will need to follow a few steps to access your database. Under most circumstances, access to your MySQL account is restricted to the localhost (that is Bingwww). This means that you won't be able to connect to the database from any other machine except by means of a web browser. Your Perl and PHP scripts will be able to find MySQL just fine if you specify your host as 'localhost'. To create your tables, however, you may need to login to your web account.

Using MySQL Interactively
After logging in to your web account on Bingwww, you can run MySQL interactively. From the system prompt type

/usr/local/mysql/bin/mysql -u mysql_userid -p

(where mysql_userid is the MySQL login ID you were given)

You'll be prompted for your MySQL password. If you are familiar with Unix, you can add /usr/local/mysql/bin to your PATH, then instead of the aforementioned command, just type mysql -u mysql_userid -p . That is, you won't have to type the whole path to mysql anymore.

After typing this command, you'll be greeted with the MySQL prompt from which you can access your database.

mysql> use mydb

(where mydb is the name of the database you were given)

Once you've logged in to your web account and connected to the MySQL server, you can start creating your tables. When you are finished using MySQL, type quit from the MySQL prompt. Type logout when you are done using your web account.

Using MySQL in Batch Mode
As an alternative to running MySQL interactively, MySQL can read input from a file in batch mode. This is useful for commands that you want to execute more than once because you won't have to type them in again each time. It is also easier sometimes to get all your commands written out in a file where you can review them before submitting them to MySQL instead of typing them in on the fly. You will still need to log in to your web account first and then submit the commands in your file to MySQL. Assuming you have created just such a file, to execute the MySQL statements in it you would issue a command such as this from the system prompt

/usr/local/mysql/bin/mysql -u mysql_userid -p mydb < create_mytables.sql

(where create_mytables.sql is a text file you've created with all the MySQL commands needed to create the tables you want.)

You'll be prompted for your MySQL password and then returned to the system prompt. If there are any problems with your file, you'll receive an error message. Remember to type logout when you are done using your web account.

Once you have created your tables, you can write your scripts in Perl or PHP using MySQL as the backend.

Security Considerations

Because there are many accounts on Bingwww and it is a system which is accessed by many people, connection information to your database must be secure. It is not enough to protect your MySQL login and password from web surfers, that information should also be protected from anyone else who has an account on the system. Typically, connection parameters are placed in a file which is readable only to you and which is separate from your main application. If you are using PHP, you might have a statement such as this to include your connections parameters:

<?php require_once('./connect.php'); ?>

If you are using Perl, your statemtent might look something like this:

require './connect.pl';

Then make the file readable only to you:

chmod 700 connect.php

or

chmod 700 connect.pl