This is a summary of what we did in class on Tue Oct 13, 2009. 

First we want to verify that we have MySQL servers running. 

Please go into your MySQL server as root. Do it now!

Here's how I do it: 

-bash-3.2$ pwd
/nobackup/dgerman/mysql-5.0.22
-bash-3.2$ ls -ld connect_as_root 
-rwx------ 1 dgerman faculty 82 Oct  1 12:10 connect_as_root
-bash-3.2$ cat connect_as_root 
mysql --socket=/nobackup/dgerman/mysql/mysql.sock \
      --port=8974 -u root -p

-bash-3.2$ ./connect_as_root 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.22-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> exit
Bye
-bash-3.2$ 

I think it's a good idea to actually set a password for the root. 

Here's how I did it: 

-bash-3.2$ pwd
/nobackup/dgerman/mysql-5.0.22
-bash-3.2$ ls -ld step010
-rwx------ 1 dgerman faculty 134 Oct  1 16:40 step010
-bash-3.2$ cat step010
/nobackup/dgerman/mysql/bin/mysqladmin \
  --port=8974 \
  --socket=/nobackup/dgerman/mysql/mysql.sock \
  -u root password 'sp00n'


-bash-3.2$ ./step010
-bash-3.2$ 

The file helps me to remember the password later because I always forget it. 

So once you go into MySQL as root (with or without password) type edit at the prompt.

pico (if that's your default editor) is going to take over.

We start by creating a user:

create user '[username]'@'silo.cs.indiana.edu' IDENTIFIED BY '[password]';

I copy this in pico and change it to: 

create user 'lbird'@'silo.cs.indiana.edu' IDENTIFIED BY 'dribl';

Next, I create a database:

create database awards;

Finally I give the user complete, unrestricted access to the database: 

grant all on awards.* to 'lbird'@'silo.cs.indiana.edu';

We exit (as root) and need to come back as the new user (lbird). 

I need to create a file (connect_as_lbird) with this contents: 

-bash-3.2$ pwd
/nobackup/dgerman/mysql-5.0.22

-bash-3.2$ ls -ld connect_as_lbird 
-rwx------ 1 dgerman faculty 105 Oct 13 12:26 connect_as_lbird

-bash-3.2$ cat connect_as_lbird 
mysql --socket=/nobackup/dgerman/mysql/mysql.sock \
  --port=8974 --host=silo.cs.indiana.edu -u lbird -p

-bash-3.2$ ./connect_as_lbird 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.22-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> 

Let's go into awards, create a table, enter some data, select it, drop the table and exit. 

mysql> use awards
Database changed

mysql> create table accounts (username char(8), password char(20));
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+------------------+
| Tables_in_awards |
+------------------+
| accounts         | 
+------------------+
1 row in set (0.00 sec)

mysql> select * from accounts;
Empty set (0.01 sec)

mysql> insert into accounts values ('lbird', 'dribl');
Query OK, 1 row affected (0.00 sec)

mysql> select * from accounts;
+----------+----------+
| username | password |
+----------+----------+
| lbird    | dribl    | 
+----------+----------+
1 row in set (0.00 sec)

mysql> insert into accounts values ('mjordan', 'abc');
Query OK, 1 row affected (0.00 sec)

mysql> insert into accounts values ('tkukoc', 'whatever');
Query OK, 1 row affected (0.00 sec)

mysql> select * from accounts;
+----------+----------+
| username | password |
+----------+----------+
| lbird    | dribl    | 
| mjordan  | abc      | 
| tkukoc   | whatever | 
+----------+----------+
3 rows in set (0.00 sec)

mysql> drop table accounts;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
Empty set (0.00 sec)

mysql> exit
Bye
-bash-3.2$