Lecture notes for Tuesday, 7/1

I may add some more details after the lecture, but I wanted to get up the instructions for installing and configuring MySQL earlier rather than later.

Installing MySQL

First, copy the archive from where I put it.

cp /l/www/classes/a202-ewennstr/software/mysql-5.6.19-linux-glibc2.5-x86_64.tar.gz .

Don't forget that period at the end! Next, we unzip the archive.

gunzip mysql-5.6.19-linux-glibc2.5-x86_64.tar.gz 

Unarchive it.

tar xvf mysql-5.6.19-linux-glibc2.5-x86_64.tar

Rename the installation directory to something shorter.

mv mysql-5.6.19-linux-glibc2.5-x86_64 mysql

Configure the installation for your directory.

cd mysql
./scripts/mysql_install_db --datadir=/u/username/mysql

You *must* be in the "mysql" directory for this to work, not in any subdirectories.

Starting up MySQL

The startup command is long and complicated, so to keep from having to type it out over and over again we'll create a little shell script to do it for us and name it "start".

pico -w start

Put this in the content of the start script:

/u/username/mysql/bin/mysqld_safe \
 --user=username \
 --pid-file=/u/username/mysql/mysqld.pid \
 --socket=/u/username/mysql/mysql.sock \
 --basedir=/u/username/mysql \
 --log-error=/u/username/mysqld-error.log \
 --datadir=/u/username/mysql \
 --port=mysqlportnumber &
 

When you're using Unix or Linux through a command prompt like we're doing now, or like this script will do when it runs, ending a line with a backslash (\) indicates that you're going to finish the command on the next line. It can make long Unix commands easier to read, and it means we don't have to worry as much about line breaks messing things up.

Some of these options might not strictly be necessary, but it's hard to be too careful. Remember to replace "username" with your username. For the last option, replace mysqlportnumber with your assigned MySQL port number, not your Apache port number.

In class, I accidentally left out the --datadir line and that caused all sorts of problems. There are many places to make mistakes here. Make sure that your username is spelled correctly everywhere and isn't left out.

I also omitted the ampersand at the end, which I'd done on purpose, but was probably a bad idea. At that point, I wasn't 100% clear on what the ampersand did. I've since looked it up, and now I can be a little bit more informative about it.

Ending a Unix Command with an Ampersand

When you end a Unix command with an ampersand, as we did in our script, it immediately sends that command to run in the background, which is what we want to do here. One slightly confusing issue is that when you run a script or a command that ends in an ampersand like this, you'll have to hit enter one extra time to get back to a shell prompt.

Exit pico and save the file.

We need to make the script executable.

chmod u+x start

Now we can run it to start the MySQL server!

./start

Check to see that it's running. There should be two processes with really long names. You can identify them because they have the word mysql in them.

ps -ef | grep username

Let's also create a script to stop the server.

pico -w stop

Put this in the content of the stop script:

/u/username/mysql/bin/mysqladmin \
 --socket=/u/username/mysql/mysql.sock \
 --port=mysqlportnumber \
 -u root -p \
 shutdown

Again, replace "username" with your username and replace mysqlportnumber with your assigned MySQL port number. The options "-u root -p" tag means that the stop script will ask for the root password before shutting things down.

Exit and save, then change the permissions.

chmod u+x stop

Now run the stop script.

./stop

It asks for a password, but we haven't set a root password yet, so just hit enter.

Did it work?

ps -ef | grep username

Let's fix that and actually set a password for root. Make sure you write this password down somewhere so that you don't forget it. It doesn't have to be a super-secure password.

./bin/mysqladmin --port=mysqlportnumber --socket=/u/username/mysql/mysql.sock -u root password 'password'

Remember to replace password with your own password. Start the server again so that we can log in as root and actually use MySQL.

./start

We'll use the command to connect to MySQL as root enough times that it's probably worthwhile to make a script to so that we don't have to type it all out every time.

So make a file called connect_as_root.

pico -w connect_as_root

Put the following contents in the script:

/u/username/mysql/bin/mysql --socket /u/username/mysql/mysql.sock \
 --port=mysqlportnumber -u root -p

And make it executable.

chmod u+x connect_as_root

Now try it out.

./connect_as_root

Creating Users and Databases and Granting Access

You now are using MySQL.

We could use the root account to do everything, but let's get some practice with how MySQL handles multiple users. Let's start by creating a user account for ourselves.

create user 'username'@'silo.soic.indiana.edu' identified by 'password';

Remember to replace password with your own password. Note that all MySQL commands end with a semicolon. Again, make sure you remember this password. I don't care if you make it the same as the other password.

While we're here, let's create users for your instructor and your AI's, so they can access your MySQL servers and see if you've done your database assignments properly. You don't need to set a password for these users.

create user 'ewennstr'@'silo.soic.indiana.edu';
create user 'kpittala'@'silo.soic.indiana.edu';
create user 'sudhgoll'@'silo.soic.indiana.edu';

We can see all the users for this installation of MySQL:

select * from mysql.user;

Now that we have users, let's create a database and give those users access to it. First we create the database.

create database myfirstdatabase;

Then we grant our users complete access to this database and everything that's part of it.

grant all on myfirstdatabase.* to 'username'@'silo.soic.indiana.edu';
grant all on myfirstdatabase.* to 'ewennstr'@'silo.soic.indiana.edu';
grant all on myfirstdatabase.* to 'sudhgoll'@'silo.soic.indiana.edu';
grant all on myfirstdatabase.* to 'kpittala'@'silo.soic.indiana.edu';

Now we can exit MySQL.

exit

Now that we've created a new user for ourselves, we can make another shell script to connect to MySQL as that user.

pico -w connect_as_username

Put in the following contents:

/u/username/mysql/bin/mysql --socket /u/username/mysql/mysql.sock \
 --host=silo.soic.indiana.edu \
 --port=mysqlportnumber -u username -p

And make it executable.

chmod u+x connect_as_username

Accessing the Database

Now we take a look at that database. Connect to MySQL using the shell script we just created.

./connect_as_username

We enter our password and MySQL starts up. Now to start working with our database, we use the use command.

use myfirstdatabase;

We can see what tables this database has with the show tables; command.

show tables;

Of course, we haven't created any tables, so let's fix that.

create table people ( userid varchar(8) primary key, firstname varchar(20), lastname varchar(2) );

There's a lot of new syntax in that one line, and if we have time, we'll talk about it. If we have more time, then we'll even put some data into that table. If not, we'll talk about that on Wednesday.