Installing MySQL

1. These instructions are designed to be used with version MySQL 3.22. You can download this version from the course website, mysql-3.22.32-sun-solaris2.5.1-sparc.tar
2. You will need to use the GNU version of the tar program. The Solaris version of tar evidently has some bug in it that makes it not work with the mysql tar file. To use this, since it’s already installed on ISD machines, just type ‘gtar’ instead of ‘tar’.
3. Make sure you are explicitly logged into csci571 when you begin your MySQL installation. If you're on a Sun workstation in SAL, you are logged into that local machine. You must do an rlogin into csci571 so that the remote access permissions for the MySQL database root user (not Unix user) are assigned to csci571 (inside the USER database which MySQL uses to keep track of access rights/privileges).
4. Now use this gtar program to extract the mysql code from the tarball you’ve downloaded.

$ gtar xvf mysql-VERSION.tar

Now the untarred directory name is too long to handle [mysql-3.22.32-sun-solaris2.5.1-sparc] so for convenience you can rename it by using a move (mv) command:

$ mv mysql-3.22.32-sun-solaris2.5.1-sparc    mysql-3.22.32

but this is optional, the installation will work without this also (but you will type more).

5. To get your instance of MySQL to work, you will have to run it at a particular port number. This requires you to edit the safe_mysqld script in the bin subdirectory. The section you need to modify is around line 100. It needs to look like this:

#Default communication ports
#MYSQL_TCP_PORT=3306	#Change the port number for MySQL: you should use XXXXX+4 where XXXXX are the last 5 digits of the random number assigned to you when you joined this class.
if test -z "$MYSQL_UNIX_PORT"
then
MYSQL_UNIX_PORT="/tmp/mysql.sock"	#Change this to be somewhere in your unix account directory path (for example Use the result of the pwd command and attach mysql.sock, e.g. /auto/home-scf-22/csci571/MySQL/mysql-3.22.32/mysql.sock)
export MYSQL_UNIX_PORT
fi
#export MYSQL_TCP_PORT
Make sure you uncomment the lines above in red(remove the ‘#’), and change both the TCP port to a number and the UNIX port to the file as explained above.

6. Make changes to bin/mysqlaccess at line 20 as:

$MYSQL = "/YOUR_HOME_DIR_WHERE_MYSQL_IS_INSTALLED/bin/mysql"
Use the result of the pwd command for YOUR_HOME_DIR_WHERE_MYSQL_IS_INSTALLED, E.G.
/auto/home-scf-22/csci571/MySQL/mysql-3.22.32/bin/mysql

7. Now go to your mysql directory and run the configuration file as

$ ./configure --prefix=ABSOLUTE_PATH_TO_YOUR_MYSQL_DIRECTORY

If everything went well, MySQL will actually start running, and you will get a message such as this one:

csci571.usc.edu(171): ./configure –-prefix=/auto/home-scf-22/csci571/MySQL/mysql-3.22.32

Creating db table

Creating host table

Creating user table

Creating func table

Creating tables_priv table

Creating columns_priv table

The latest information about MySQL is available on the web at http://www.mysql.com

Support MySQL by buying support/licenses at http://www.tcx.se/license.htmy.

Starting the mysqld server.  You can test that it is up and running with the command:

./bin/mysqladmin version (Note: this command fails, but ignore it)

csci571.usc.edu(172): Starting mysqld daemon with databases from /auto/home-scf-22/csci571/MySQL/mysql-3.22.32/data

Note: If you do get an error, such as "mysqld daemon ended", move ahead anyway as in step 2 of "Running MySQL" (see below) you will again try to start the server.

8. Create the MySQL grant tables from the mySQL directory:

$ ./scripts/mysql_install_db

This creates the user table which mySQL uses to control access to the databases that you create.

Running MySQL (only from csci571/csci571)

1. If your server is already running, you can check the version by saying:

$ ./bin/mysqladmin -u root -S ~/mysql/mysql.sock -P PORT# -p version

You will be prompted for a password. Just type Enter with an "empty" password.

2. If your server is NOT running, or if your server is running then run ps -gx and kill the server. Then verify that you can start the server, by executing the following script:

$ ./bin/mysqld --basedir=ABSOLUTE_PATH_TO_YOUR_MYSQL_DIRECTORY --log -P port# -u root
--datadir=ABSOLUTE_PATH_TO_YOUR_MYSQL_DIRECTORY/data --socket=ABSOLUTE_PATH_TO_YOUR_MYSQL_DIRECTORY/mysql.sock &
For example:
./bin/mysqld --basedir=/auto/home-scf-22/csci571/MySQL/mysql-3.22.32
--log -P 9004 -u root
--datadir=/auto/home-scf-22/csci571/MySQL/mysql-3.22.32/data
--socket=/auto/home-scf-22/csci571/MySQL/mysql-3.22.32/mysql.sock &

3. To enter SQL statements interactively with your database, you enter the following: 

$ ./bin/mysql -u root -S ABSOLUTE_PATH_TO_YOUR_MYSQL_DIRECTORY/mysql.sock -p

or

$ ./bin/mysql -P YOUR-PORT# -u root --socket=/ABSOLUTE_PATH_TO_YOUR_MYSQL_DIRECTORY/mysql.sock

You will be prompted for a password. Just type Enter with an "empty" password.

Here is some sample output:

csci571.usc.edu(195): ./bin/mysql -u root -S /auto/home-scf-22/csci571/MySQL/mysql.sock -p

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 2 to server version: 3.22.32

Type 'help' for help.

mysql>

To look at the user database, enter the following SQL statements:

use mysql

select * from user;

You should see something like the following (with a lot more columns actually displayed):

+---------------+------+------------------+-------------+-------------+-------------
| Host          | User | Password         | Select_priv | Insert_priv | Update_priv
+---------------+------+------------------+-------------+-------------+------------+
| localhost     | root | 535c29643159d001 | Y           | Y           | Y          |
| csci571.usc.edu|root |                  | Y           | Y           | Y          |
| localhost     |      |                  | N           | N           | N          |
| csci571.usc.edu|     |                  | N           | N           | N          |
+---------------+------+------------------+-------------+-------------+------------+

4 rows in set (0.02 sec)

This means that for the user root accessing mySQL from csci571.usc.edu, there is no password setup.

4. Create a testing database db1 and a table tb1 and insert several records

mysql>create database db1;

mysql>use db1;

mysql>create table tb1 (
name VARCHAR(20),
age  INT
);

mysql>insert into tb1 values ('John', 20);
mysql>insert into tb1 values ('Mary', 25);
mysql>insert into tb1 values ('Tom', 24);

5. Here is an example Perl script to access a mysql database. Copy it into a file, e.g. test.pl, make sure your permissions are set right, and then execute it.

#!/auto/usc/perl/5.004/bin/perl 
use DBI;
# Perform a select on csci571/db1 DBI;
$dbh=DBI->connect("dbi:mysql:db1:csci571.usc.edu:9004", "root","") || die $DBI::err;
#you may need to replace csci571.usc.edu and the port #
$sth=$dbh->prepare('SELECT * from tb1;');
$sth->execute;
print "Name\tAge\n";
print "-------\t--------------\n";
while (@row=$sth->fetchrow_array) {
print $row[0]."\t".$row[1]."\n";
}
$sth->finish;
$dbh->disconnect;

You can run the perl script and see the result

6. Now you can drop the testing database db1

mysql>drop database db1;