Today we learn how to work with our MySQL server. We will: a) create users b) create databases c) query databases to obtain answers, information d) embed queries in Perl, python, PHP scripts In lab today I will check your homework and offer individual help. You can try to follow the steps below on your own and ask for help when you need it. 1. We each have a MySQL server. Details: -- my port is 44089 (different from my Apache port, 44063) -- ports are listed here: http://www.cs.indiana.edu/classes/a202-dger/sum2009/0626.html 2. We can start the server with /nobackup/username/mysql-5.0.22/step008 Details: -- instead of username please use your own username -- here's my file: -bash-3.2$ cat step008 /nobackup/dgerman/mysql/bin/mysqld_safe \ --user=dgerman \ --pid-file=/nobackup/dgerman/mysql/mysqld.pid \ --log=/nobackup/dgerman/mysql/mysqld.log \ --socket=/nobackup/dgerman/mysql/mysql.sock \ --log-error=/nobackup/dgerman/mysqld-error.log \ --basedir=/nobackup/dgerman/mysql \ --datadir=/nobackup/dgerman/mysql \ --port=44089 & -bash-3.2$ 3. Let's start our MySQL servers. Details: -- check to see that it's not working; type: ps -ef | grep username where username is your username. For me this looks like this: -bash-3.2$ ps -ef | grep dgerman root 10945 4950 0 11:52 ? 00:00:00 sshd: dgerman [priv] dgerman 10947 10945 0 11:52 ? 00:00:00 sshd: dgerman@pts/7,pts/14 dgerman 10948 10947 0 11:52 pts/7 00:00:00 -bash dgerman 11436 10947 0 12:08 pts/14 00:00:00 -bash dgerman 11649 10948 0 12:14 pts/7 00:00:00 pico 0702.txt dgerman 11761 11436 0 12:19 pts/14 00:00:00 ps -ef dgerman 11762 11436 0 12:19 pts/14 00:00:00 grep dgerman dgerman 16942 19326 0 08:00 ? 00:00:00 /u/dgerman/apache/bin/httpd -k start dgerman 16943 19326 0 08:00 ? 00:00:00 /u/dgerman/apache/bin/httpd -k start dgerman 16944 19326 0 08:00 ? 00:00:00 /u/dgerman/apache/bin/httpd -k start dgerman 16945 19326 0 08:00 ? 00:00:00 /u/dgerman/apache/bin/httpd -k start dgerman 16946 19326 0 08:00 ? 00:00:00 /u/dgerman/apache/bin/httpd -k start dgerman 18311 19326 0 08:30 ? 00:00:00 /u/dgerman/apache/bin/httpd -k start dgerman 19326 1 0 Jun30 ? 00:00:00 /u/dgerman/apache/bin/httpd -k start -bash-3.2$ I don't have any MySQL running, just a few Apache server processes. -- start your MySQL server; type: -bash-3.2$ cat step008 -bash-3.2$ /nobackup/dgerman/mysql-5.0.22/step008 -bash-3.2$ Starting mysqld daemon with databases from /nobackup/dgerman/mysql -bash-3.2$ Type Enter if it appears that you won't ever get back the prompt. -- check to see it's running: -bash-3.2$ ps -ef | grep dgerman root 10945 4950 0 11:52 ? 00:00:00 sshd: dgerman [priv] dgerman 10947 10945 0 11:52 ? 00:00:00 sshd: dgerman@pts/7,pts/14 dgerman 10948 10947 0 11:52 pts/7 00:00:00 -bash dgerman 11436 10947 0 12:08 pts/14 00:00:00 -bash dgerman 11649 10948 0 12:14 pts/7 00:00:00 pico 0702.txt dgerman 11807 1 0 12:20 pts/14 00:00:00 /bin/sh /nobackup/dgerman/mysql/bin/mysqld_safe --user=dgerman --pid-file=/nobackup/dgerman/mysql/mysqld.pid --log=/nobackup/dgerman/mysql/mysqld.log --socket=/nobackup/dgerman/mysql/mysql.sock --log-error=/nobackup/dgerman/mysqld-error.log --basedir=/nobackup/dgerman/mysql --datadir=/nobackup/dgerman/mysql --port=44089 dgerman 11854 11807 1 12:20 pts/14 00:00:00 /nobackup/dgerman/mysql/libexec/mysqld --basedir=/nobackup/dgerman/mysql --datadir=/nobackup/dgerman/mysql --pid-file=/nobackup/dgerman/mysql/mysqld.pid --skip-locking --port=44089 --socket=/nobackup/dgerman/mysql/mysql.sock --log=/nobackup/dgerman/mysql/mysqld.log dgerman 11863 11436 0 12:20 pts/14 00:00:00 ps -ef dgerman 11864 11436 0 12:20 pts/14 00:00:00 grep dgerman dgerman 16942 19326 0 08:00 ? 00:00:00 /u/dgerman/apache/bin/httpd -k start dgerman 16943 19326 0 08:00 ? 00:00:00 /u/dgerman/apache/bin/httpd -k start dgerman 16944 19326 0 08:00 ? 00:00:00 /u/dgerman/apache/bin/httpd -k start dgerman 16945 19326 0 08:00 ? 00:00:00 /u/dgerman/apache/bin/httpd -k start dgerman 16946 19326 0 08:00 ? 00:00:00 /u/dgerman/apache/bin/httpd -k start dgerman 18311 19326 0 08:30 ? 00:00:00 /u/dgerman/apache/bin/httpd -k start dgerman 19326 1 0 Jun30 ? 00:00:00 /u/dgerman/apache/bin/httpd -k start -bash-3.2$ OK. My MySQL server is now running. Now the fun starts. 4. Make sure you can stop MySQL. Details: -- create mysql_stop in /nobackup/username/mysql-5.0.22 as follows: -bash-3.2$ cat mysql_stop /nobackup/dgerman/mysql/bin/mysqladmin \ --socket=/nobackup/dgerman/mysql/mysql.sock \ --port=44089 -u root -p shutdown Note the port number (yours, and different from the Apache web server port). -- make sure the file is executable: -bash-3.2$ ls -ld mysql_stop -rw-r--r-- 1 dgerman faculty 124 Jul 2 13:23 mysql_stop -bash-3.2$ chmod 700 mysql_stop -bash-3.2$ ls -ld mysql_stop -rwx------ 1 dgerman faculty 124 Jul 2 13:23 mysql_stop -- now verify that your MySQL server is running: -bash-3.2$ ps -ef | grep dgerman root 10945 4950 0 11:52 ? 00:00:00 sshd: dgerman [priv] dgerman 10947 10945 0 11:52 ? 00:00:00 sshd: dgerman@pts/7,pts/14,pts/5 dgerman 10948 10947 0 11:52 pts/7 00:00:00 -bash dgerman 11436 10947 0 12:08 pts/14 00:00:00 -bash dgerman 11649 10948 0 12:14 pts/7 00:00:00 pico 0702.txt dgerman 11807 1 0 12:20 pts/14 00:00:00 /bin/sh /nobackup/dgerman/mysql/bin/mysqld_safe --user=dgerman --pid-file=/nobackup/dgerman/mysql/mysqld.pid --log=/nobackup/dgerman/mysql/mysqld.log --socket=/nobackup/dgerman/mysql/mysql.sock --log-error=/nobackup/dgerman/mysqld-error.log --basedir=/nobackup/dgerman/mysql --datadir=/nobackup/dgerman/mysql --port=44089 dgerman 11854 11807 0 12:20 pts/14 00:00:00 /nobackup/dgerman/mysql/libexec/mysqld --basedir=/nobackup/dgerman/mysql --datadir=/nobackup/dgerman/mysql --pid-file=/nobackup/dgerman/mysql/mysqld.pid --skip-locking --port=44089 --socket=/nobackup/dgerman/mysql/mysql.sock --log=/nobackup/dgerman/mysql/mysqld.log dgerman 13071 10947 0 13:00 pts/5 00:00:00 -bash dgerman 14155 11436 0 13:23 pts/14 00:00:00 ps -ef dgerman 14156 11436 0 13:23 pts/14 00:00:00 grep dgerman dgerman 16942 19326 0 08:00 ? 00:00:00 /u/dgerman/apache/bin/httpd -k start dgerman 16943 19326 0 08:00 ? 00:00:00 /u/dgerman/apache/bin/httpd -k start dgerman 16944 19326 0 08:00 ? 00:00:00 /u/dgerman/apache/bin/httpd -k start dgerman 16945 19326 0 08:00 ? 00:00:00 /u/dgerman/apache/bin/httpd -k start dgerman 16946 19326 0 08:00 ? 00:00:00 /u/dgerman/apache/bin/httpd -k start dgerman 18311 19326 0 08:30 ? 00:00:00 /u/dgerman/apache/bin/httpd -k start dgerman 19326 1 0 Jun30 ? 00:00:00 /u/dgerman/apache/bin/httpd -k start -- call your mysql_stop file (notice you're prompted for password, just hit Enter): -bash-3.2$ ./mysql_stop Enter password: STOPPING server from pid file /nobackup/dgerman/mysql/mysqld.pid 090702 13:24:00 mysqld ended -- verify that your server is now down (no longer running): -bash-3.2$ ps -ef | grep dgerman root 10945 4950 0 11:52 ? 00:00:00 sshd: dgerman [priv] dgerman 10947 10945 0 11:52 ? 00:00:00 sshd: dgerman@pts/7,pts/14,pts/5 dgerman 10948 10947 0 11:52 pts/7 00:00:00 -bash dgerman 11436 10947 0 12:08 pts/14 00:00:00 -bash dgerman 11649 10948 0 12:14 pts/7 00:00:00 pico 0702.txt dgerman 13071 10947 0 13:00 pts/5 00:00:00 -bash dgerman 14303 11436 0 13:24 pts/14 00:00:00 ps -ef dgerman 14304 11436 0 13:24 pts/14 00:00:00 grep dgerman dgerman 16942 19326 0 08:00 ? 00:00:00 /u/dgerman/apache/bin/httpd -k start dgerman 16943 19326 0 08:00 ? 00:00:00 /u/dgerman/apache/bin/httpd -k start dgerman 16944 19326 0 08:00 ? 00:00:00 /u/dgerman/apache/bin/httpd -k start dgerman 16945 19326 0 08:00 ? 00:00:00 /u/dgerman/apache/bin/httpd -k start dgerman 16946 19326 0 08:00 ? 00:00:00 /u/dgerman/apache/bin/httpd -k start dgerman 18311 19326 0 08:30 ? 00:00:00 /u/dgerman/apache/bin/httpd -k start dgerman 19326 1 0 Jun30 ? 00:00:00 /u/dgerman/apache/bin/httpd -k start -bash-3.2$ 5. Get into MySQL to set things up. Details: -- start your server if your server is not running (ps -ef, step008 etc.) -- create a file in /nobackup/username/mysql-5.0.22 (pico connect_asroot) with the following content: mysql --socket=/nobackup/username/mysql/mysql.sock \ --port=44yyy -u root -p Change username to your username and use your own MySQL port instead of 44yyy (mine is 44089). It ends up like this (for me): mysql --socket=/nobackup/dgerman/mysql/mysql.sock \ --port=44089 -u root -p Make it executable, make sure the server is running (if not turn it on with step008) and connect as root (your password is empty so type Enter when prompted like you did when you stopped the server a bit earlier): -bash-3.2$ chmod u+x connect_asroot -bash-3.2$ ./connect_asroot 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$ It's good that you can go in and out. Now let's change the root password. Use pico step010 to create: /nobackup/[username]/mysql/bin/mysqladmin \ --port=[your port] \ --socket=/nobackup/[username]/mysql/mysql.sock \ -u root password '[password]' Change it to your MySQL port, and use your usernames. Also set a password. Here's what my file looks like: /nobackup/dgerman/mysql/bin/mysqladmin \ --port=44089 \ --socket=/nobackup/dgerman/mysql/mysql.sock \ -u root password 'sp00n' Make it executable, run it, then go it as root with this new password. -bash-3.2$ chmod u+x step010 -bash-3.2$ ./step010 -bash-3.2$ ./connect_asroot Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.0.22-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> exit Bye -bash-3.2$ Nice. At this point you can go in as root and do stuff. What stuff can you do: a) create users b) create databases c) give users permissions to access databases Let's start doing that. Up to this moment we have: a) a root account, we changed the password on it (step010 -- sp00n) b) step006, step007, step008 were used to install, initialize, start c) mysql_stop is used to stop the server d) connect_asroot is used to get into your MySQL server as root So now I get in in the only I set that up so far (as root, with password): -bash-3.2$ /nobackup/dgerman/mysql-5.0.22/connect_asroot Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.0.22-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> Type use mysql; then hit enter and you will get a new prompt. We now have to define our first user: create user '[username]'@'silo.cs.indiana.edu' IDENTIFIED BY '[password]'; We make two changes: the username and the password. create user 'lbird'@'silo.cs.indiana.edu' IDENTIFIED BY 'dribl'; Type edit at the mysql prompt and then you can edit as much as you need to. UW PICO(tm) 4.10 File: /tmp/sqlXRzVtX Modified create user 'lbird'@'silo.cs.indiana.edu' IDENTIFIED BY 'dribl'; [ Wrote 1 line ] -> ; Query OK, 0 rows affected (0.00 sec) mysql> The user was created. You can create any user, any number of them. You should probably create yourself (your username) instead of lbird (like I did). Now we need to define a database. In the .pdf we create a database called demoOne, here I create a database called: awards. I type create database awards; at the mysql> prompt then hit enter. Then I grant access to awards for lbird. mysql> create database awards; Query OK, 1 row affected (0.03 sec) mysql> grant all on awards.* to 'lbird'@'silo.cs.indiana.edu'; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye -bash-3.2$ We then get out as root and come as the user we created and start working. We describe a bit later what awards means and how it was designed. Let's create a connect_aslbird file that contains the command to connect as lbird: We start from: mysql --socket=/nobackup/dgerman/mysql/mysql.sock \ --port=13297 --host=silo.cs.indiana.edu -u dgerman -p We found this in text chapter 11.2 also it's just like connect_asroot with a --host switch. We need to change it: -bash-3.2$ pwd /nobackup/dgerman/mysql-5.0.22 -bash-3.2$ ls -ld conn* -rw-r--r-- 1 dgerman faculty 106 Feb 10 13:55 connect_aslbird -rwxr--r-- 1 dgerman faculty 82 Feb 6 11:47 connect_asroot -bash-3.2$ cat connect_aslbird mysql --socket=/nobackup/dgerman/mysql/mysql.sock \ --port=44089 --host=silo.cs.indiana.edu -u lbird -p -bash-3.2$ chmod u+x connect_aslbird -bash-3.2$ ./connect_aslbird Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.0.22-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use awards; Database changed mysql> show tables; Empty set (0.00 sec) mysql> Side note: http://www.libraries.iub.edu/scripts/countResources.php?resourceId=59555 is link to Books24x7 where you can find tens of thousands of IT books. Now we go back to creating a few tables and running one query in our first database. create table performers ( username char(8) primary key, lastName char(32), firstName char(32) ); mysql> show tables; +------------------+ | Tables_in_awards | +------------------+ | performers | +------------------+ 1 row in set (0.00 sec) mysql> select * from performers; Empty set (0.00 sec) mysql> edit UW PICO(tm) 4.10 File: /tmp/sqluND5CN Modified insert into performers (firstName, lastName, username) values ("Jordan", "Crawford", "jcrawfor"), ("D.J.", "White", "djwhite"), ("Deandre", "Thomas", "dthomas"), ("A.J.", "Ratliff", "ajratlif"), ("Eric", "Gordon", "egordon"), ("Mike", "White", "mwhite"), ("Armon", "Bassett", "abassett"), ("Jamarcus", "Ellis", "jmellis"), ("Lance", "Stemler", "lstemler"); [ Wrote 12 lines ] -> ; Query OK, 9 rows affected (0.00 sec) Records: 9 Duplicates: 0 Warnings: 0 mysql> select * from performers; +----------+----------+-----------+ | username | lastName | firstName | +----------+----------+-----------+ | jcrawfor | Crawford | Jordan | | djwhite | White | D.J. | | dthomas | Thomas | Deandre | | ajratlif | Ratliff | A.J. | | egordon | Gordon | Eric | | mwhite | White | Mike | | abassett | Bassett | Armon | | jmellis | Ellis | Jamarcus | | lstemler | Stemler | Lance | +----------+----------+-----------+ 9 rows in set (0.00 sec) mysql> drop table performers; Query OK, 0 rows affected (0.00 sec) mysql> show tables; Empty set (0.00 sec) mysql> exit Bye -bash-3.2$ ./mysql_stop Enter password: -bash-3.2$ So we're out after briefly describing the basics of creating tables and populating them with data. Next we need to discuss the design of relational databases: Basic set of notes describing the design of relational databases: http://www.cs.indiana.edu/classes/a348/spr2007/notes/Seven.html Similar kind of notes: http://www.cs.indiana.edu/classes/a348/spr2007/notes/Eight.html Introductory notes to be followed in today's class: http://www.cs.indiana.edu/classes/a348/spr2008/notes/weekThree/three.html We could discuss all of these in detail in class. Don't forget the text is here: http://www.cs.indiana.edu/~dgerman/eowp06.pdf ---- Let's see what we have done so far, what we know, and where we're heading: Web programming Web server Unix The server stays up all the time, listens to a port, takes requests, provides answers. Requests are in HTTP, so are the answers. Requests are about resources. Resources could be in htdocs (.html, .jpeg, movies, Excel spreadsheets) and in that case the resource is sent back as the response. Resources could be in cgi-bin and in that case they are programs and in that case the server runs them and sends back the output of the program. One possible behaviour: program reads data from a lot of sensors and reports back an aggregation of that. accuweather.com, e.g. amazon.com works slightly differently, instead of "sensors" it has access into a huge inventory, that changes like the weather. side comment: the server deals with requests to htdocs or cgi-bin differently. So if you set up a symbolic link in htdocs to a script in cgi-bin you will be able to see the source code if you access the symbolic link. Relational databases A relational database is a collection of tables. A table has a number of columns, named, storing values of a certain type. One row in the table is going to be called: record (tuple). Example: roster of players Player username lastName firstName egordon Gordon djwhite White ajratlif Ratliff mwhite White jellis lstemler abassett jcrawfor One or more of the columns in the table will serve a special role. In our example the username column will be the key. The key uniquely identifies a record. This table could be said to represent the entity "Player". There are two methods that we want to cover when designing databases: a) intuitive approach b) the more mathematical approach The problem: we have a contest with three judges and a few players. The judges are going to score the players and we want to record that. We want to be able to take any questions on the grading afterwards. lbird mjordan tkukoc egordon 8 3 6 jellis 7 - 8 djwhite - - - lstemler 9 - 2 mwhite - 5 4 jcrawfor 6 7 - Judge username lastName firstName lbird mjordan tkukoc kbryant Tables are meant to dynamically be added records. Tables have a fixed of number of columns, determined at design time. You can change them, but one seldom wants to allow that, because it's a sign of poor planning. So how can we change this: lbird mjordan tkukoc egordon 8 3 6 jellis 7 - 8 djwhite - - - lstemler 9 - 2 mwhite - 5 4 jcrawfor 6 7 - The purpose is to be able to store it in a table whose number of columns does not change. Scores playerUsername judgeUsername score egordon lbird 8 egordon mjordan 3 egordon tkukoc 6 jellis lbird 7 jellis mjordan - no need to add this jellis tkukoc 8 ... and so on playerUsername judgeUsername score egordon lbird 8 egordon mjordan 3 egordon tkukoc 6 jellis lbird 7 jellis tkukoc 8 So the problem seems to have the following database structure: Player Judge So the design steps are: 1. Determine the entities in your problem 2. Determine the relationships between the entities (that's how the entities are interacting) The relationship here is Scores so the picture becomes Player ------ Scores ------- Judge *username *playerUsername *username lastName *judgeUsername lastName firstName score firstName Library in town: Book ------ Transactions ------ Patrons *callNumber *callNumber *username isbn *username lastName title ?dateOut firstName author dateReturned address review What we are building is called an E-R (entity-relationship) diagram. The question for next time is: -- how do we determine in this intuitive approach the key in a relationship table? The Homework Three notes try to give you an answer to that: http://www.cs.indiana.edu/classes/a202/sum2009/HomeworkThree.html Here are related notes: http://www.cs.indiana.edu/classes/a348/spr2009/0217-class.txt How to include queries in web scripts: http://www.cs.indiana.edu/classes/a348/spr2009/0501.html