Using MySQL on i6.cims.nyu.edu

Using MySQL on i6.cims.nyu.edu

Note: We will cover all of this in class. In addition, please see your instructor or our TA with any questions!


Resources:

CIMS resources for i6.cims.nyu.edu http://cims.nyu.edu/webapps/content/systems/userservices/webhosting

CIMS resources for databases http://cims.nyu.edu/webapps/content/systems/userservices/databases


STEP 1 – CREATE A NEW DATABASE AND RETRIEVE YOUR TEMPORARY MYSQL PASSWORD

1. Go to https://cims.nyu.edu/webapps/databases in your browser and log in using your netid and your i6 password.

2. SELECT ADD NEW DATABASE.

The database will be created for you with your netid as follows:

  • netid: de123
  • requested name: db1
  • … so your database will be named de123_db1

3. Select CREATE NEW PASSWORD - and you will see your temporary password in a small box. Be sure to copy it.


STEP 2 USING MYSQL THE FIRST TIME AND RESETTING YOUR MYSQL PASSWORD

1. use TERMINAL on the Mac or PUTTY on Windows to log into i6.cims.nyu.edu using your netid and i6 password:

ssh i6.cims.nyu.edu -l <netid>

<enter your i6 password when prompted>

2. Once there … and at the prompt … type the following

mysql -hwarehouse -p<temporary password assigned above for mysql>

3. Now you are in MySQL . Change your MySQL passwordto something that is not “special” (as it will be exposed for now):

set password = password(“new-password”);

4. Type exit to leave MySQL


STEP 3: USING MYSQL ON i6.cims.nyu.edu

1. Logon to i6.cims.nyu.edu

2. If you wish: create a directory for your scripts and go to that directory.

3. Open MYSQL as follows:
mysql -hwarehouse -p<password> <database name>

  • Notes: -h stands for host; -p stands for password; and you have included the name of your database in the command..
  • If you do not include the name of the database above, type USE <database name>; as your first command in MySQL.

ADDITIONAL INFORMATION: HOW TO RUN A .SQL SCRIPT ON i6.cims.nyu.edu:

You may place your MySQL scripts in your home directory or create a separate directory:

  • edit scripts in any text editor; for example, use pico< or vi to edit the script if you wish to edit the scripts in Unix; or edit your scrips in BBEdit or TextWrangler on your local machine and then SFTP or upload them to the webserver
  • use “;” at the end of every script line or command (i.e. the statement terminator is a semi-colon as it is in Java and JavaScript )
  • save your script file with a “.sql” suffix

To run a MySQL script on i6.cims.nyu.edu:

  • Create a script which is a text file with the suffix “.sql”
  • At the Unix prompt, type

mysql -hwarehouse -p<password> <database name> < scriptname.sql
For example:
mysql -hwarehouse -pdbw1 de123_db123 <book_script.sql


How to use PHPMYADMIN:

  1. Go to https://cims.nyu.edu/phpMyAdmin/
  2. Your login and password are your MySQL login and password i.e. your netid to login and your MySQL password
  3. Click on Databases to select and open your current database

Here is an exercise to try on i6.cims.nyu.edu:

NOTE: Keywords are typically written in CAPITAL LETTERS for easier reading although MySQL is not case-sensitive with respect to commands. However, MySQL is case-sensitive with respect to table and field names so they are typically written in lower case.)

$ mysql -hwarehouse -p<password>
mysql> USE <databasename>;
mysql> CREATE TABLE book (
-> title varchar(32),
-> author varchar(64)
-> );
[Result should be: Query OK. 0 rows affected.]
mysql> INSERT INTO book VALUES ("Pride and Prejudice", "Jane Austen");
[Result should be: Query OK. 1 row affected]
mysql> SELECT * FROM book;
[Result should show the name and author and 1 row in set]
mysql> exit
[Result should show Bye -> and return you to the Unix command line prompt.]