Databases

Courant offers users the ability to set up MySql databases on its central database server, warehouse.cims.nyu.edu.  The Database Manager should be used to set up your database.  Databases stored on this system will provide automatic backup along with automatic database restarts in the event of server downtime.  If you would prefer to set up a self-administered MySql database on a server such as linserv1.cims.nyu.edu, please refer to these instructions.

At this time we are not running a central PostgreSQL server but our users are welcome to run their own on a server such as linserv1.cims.nyu.edu. There are 2 approaches.

  1. The simplest and the one we recommend is to run new instance, which PostgreSQL calls a "cluster". This uses the system version of PostgreSQL that comes with Centos and does not require that you compile your own version. For instructions on how to do this go here.
  2. Compile your own PostgreSQL from scratch. For instructions on how to do this go here.

Usage/Purpose

The server warehouse.cims.nyu.edu (detailed below) is intended to host data for user web pages. It is not intended to be used for research, large data sets, and so on.  For those cases, please run your own instance of mysqld on a compute server and store your data in a /scratch partition or /data directory.

Warning: If allowing web-based content to a MySQL database, always be aware of the SQL Injection exploits that are possible.

Using the Database Manager

The database server requires a special account to connect.  Accessing the Database Manager will automatically create your database account.  You still need a special database password to connect to your databases, however.  To set your database password, click on the "Reset Database Password" button in the Database Manager.  The new password will then appear on the screen in green.  This button can also be used if you lose your database password.

After resetting your password, you can create a database by clicking on "Add New Database".  This will take you to a new screen where you can enter your database name.  The database name may only consist of letters, numbers, and underscore.  Click "Create" to complete the creation of the database.

The Database Manager will automatically prepend your CIMS login to the database name.  So if your CIMS login is "smith", and your database name is "data1", the full database name will be "smith_data1".

Accessing the Database

Once your databases are created, they can be accessed with the following command from any Unix machine on the CIMS network:

/usr/local/pkg/mysql/bin/mysql -hwarehouse -p

You will be prompted for your database password (not your CIMS password).

You can then change your password by entering:

set password = password("new-password");

Your database password should not be the same as your regular CIMS password.

For convenience, Courant offers phpMyAdmin to administer your databases.  You can login with your CIMS login and your Database password.

Migrating From a Self Managed Database

If you would like to migrate from a courses/linserv database to the new hosted system, please see the database migration guide.

Sample PHP Code

<?php

$db_type           = "mysql";
$db_server         = "warehouse.cims.nyu.edu";
$db_name           = "your_database_name";
$db_user           = "your_cims_login";
$db_password       = "your_database_password";

$db_link = mysql_connect($db_server, $db_user, $db_password);
if (!$db_link)
    die("Could not connect: " . mysql_error());
echo nl2br("Connected successfully\n");

$db_selected = mysql_select_db($db_name, $db_link);
if (!$db_selected)
    die("Can't use \"$db_name\" : " . mysql_error());
echo nl2br("Selected successfully\n");

mysql_close($db_link);

?>