New York University Faculty of Arts and Science College of Arts and Science Graduate School of Arts and Science

Databases (Old System)

Note: The Database Hosting Service is the preferred method of setting up and maintaining a database as it offers significant benefits (automatic backup, automatic restart after downtime).  Migrating to the new system is described here.

Creating a MySQL Database

Warning: Allowing other users read access to a MySQL database may compromise your data integrity, always keep your data out of the public_html directory and never provide a link to it from your public_html directory.

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

To create a MySQL database and access it from the CIMS network, follow the instructions below (mostly borrowed from Michael Schidlowsky):

  1. Login to access.cims.nyu.edu

    ssh access.cims.nyu.edu
  2. Decide on a data directory for you MySQL database. This should NOT be in your public_html directory. I recommend creating a directory in your home folder just for this purpose, something like ~/db/mysql
  3. Connect to one of the courses machines (courses1, courses2, or courses3). This is where you should run the mysql process (mysql will not run on the access machines). Since the courses machine are not publicly accessible, you will have to ssh to them from the access machines. From the UNIX prompt, just execute:
    ssh courses1.cs.nyu.edu
  4. Now issue the following command to create your database:
    /usr/local/pkg/mysql/bin/mysql_install_db \
    --datadir=/PATH/TO/DATA_DIR

    An example might be:
    /usr/local/pkg/mysql/bin/mysql_install_db \
    --datadir=/home/YOUR_USERNAME/db/mysql
  5. Pick a port number between 10000 and 49152 for your MySQL server and make sure you remember it. For this example we'll use 25000. Start the MySQL database using this command (don't forget to use the ampersand so that the process runs in the background), also make sure that someone isn't already using this port
    /usr/local/pkg/mysql/bin/mysqld_safe --port=25000 \
    --datadir=/home/YOUR_USERNAME/db/mysql \
    --socket=/var/tmp/mysql_YOUR_USERNAME.sock &

    You can check if your process is running by issuing this command:

    ps -ef | grep YOUR_USERNAME

    You can check if other MySQL processes are using this port by issuing this command:

    ps -ef | grep port
  6. Come up with a root password for the MySQL database and use the following command to set it (we'll use the password foobar) for both the localhost and coureses machines you're using:
    /usr/local/pkg/mysql/bin/mysqladmin --proto=tcp \
    --port=25000 -u root -h localhost password 'foobar'
  7. /usr/local/pkg/mysql/bin/mysqladmin --proto=tcp \
    --port=25000 -u root -h courses1 password 'foobar'
  8. You can now access your MySQL database from this computer. Run the following command to start up the MySQL command line interface:
    /usr/local/pkg/mysql/bin/mysql --proto=tcp \
    --host=courses1.cs.nyu.edu --port=25000 -u root -p
  9. Now that you've got a working database you probably want to allow the rest of the CIMS computers to access it. In order to do that you will need to grant permisison to those machines on a per-machine per-user (database user, not CIMS user) basis. As an example, we'll create a root account that can be accessed from any CIMS machine with the password 'foobar'. You may want to see the MySQL documentation for more information. Starting at the mysql prompt run:
    GRANT ALL PRIVILEGES ON *.* TO root@'%.cims.nyu.edu'
    IDENTIFIED BY "foobar";
  10. GRANT ALL PRIVILEGES ON *.* TO root@'%.cs.nyu.edu'
    IDENTIFIED BY "foobar";
    FLUSH PRIVILEGES;
  11. You can now access your MySQL database from any CIMS computer using the database hostname, port number, and the username combination you have setup (in this example courses1, 2500 and root). Run the following command from any CIMS computer to start up the MySQL command line interface:
    /usr/local/pkg/mysql/bin/mysql --proto=tcp \
    --host=courses1 --port=25000 -u root -p