Installing/Using MySQL on RedHat Linux

This workshop discusses how to install and use MySQL on RedHat Linux.

Introduction

What is a database?

According to www.WhatIs.com, a database is:

A collection of data that is organized so that its contents can easily be accessed, managed, and updated. The most prevalent type of database is the relational database, a tabular database in which data is defined so that it can be reorganized and accessed in a number of different ways. A distributed database is one that can be dispersed or replicated among different points in a network. An object-oriented database is one that is congruent with the data defined in object classes and subclasses.
Databases contain aggregations of data records or files, such as sales transactions, product catalogs and inventories, and customer profiles. Typically, a database manager provides users the capabilities of controlling read/write access, specifying report generation, and analyzing usage. Databases and database managers are prevalent in large mainframe systems, but are also present in smaller distributed workstation and mid-range systems such as the AS/400 and on personal computers. SQL is a standard language for making interactive queries from and updating a database such as IBM's DB2, Microsoft's Access, and database products from Oracle, Sybase, and Computer Associates.

Why use a database?

Simply put, databases enable a variety of client types to access data in a standard way. Instead of repeatedly developing unique, proprietary methods of accessing certain types of information, a single database system can provide a central repository, or "data haven," for all an organization's information. By centralizing the data, we reduce duplication of system management at the expense of creating a single point of failure (though database systems can be mirrored to prevent this sort of problem).

Why use MySQL?

For the purposes of this workshop we plan to use MySQL because it remains one of the most popular, best-supported free (GPL) database systems today. Also, it supports a subset of the ANSI/ISO SQL standard. According to www.WhatIs.com:

SQL (Structured Query Language) is a standard interactive and programming language for getting information from and updating a database. Although SQL is both an ANSI and an ISO standard, many database products support SQL with proprietary extensions to the standard language. Queries take the form of a command language that lets you select, insert, update, find out the location of data, and so forth. There is also a programming interface.

Installing MySQL

Once you set up a server, you can access it from any location you want. Therefore, the server and client come in separate parts. For the purposes of this workshop, however, we plan to run the server and client on the same computer.

Installing the Server

First, using a web browser, you should download the server RPM from the following location: http://www.mysql.com/Downloads/MySQL-3.22/MySQL-3.22.32-1.i386.rpm Once you download the server, change to the super user account and install the software as shown below.

[root@timpc tim]# rpm --install MySQL-3.22.32-1.i386.rpm 
Creating db table
Creating host table
Creating user table
Creating func table
Creating tables_priv table
Creating columns_priv table

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
This is done with:
/usr/bin/mysqladmin -u root password 'new-password
See the manual for more instructions.

Please report any problems with the /usr/bin/mysqlbug script!

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 mysqld daemon with databases from /var/lib/mysql
[root@timpc tim]#

Installing the Client

First, using a web browser, you should download the server RPM from the following location: http://www.mysql.com/Downloads/MySQL-3.22/MySQL-client-3.22.32-1.i386.rpm Install it ... Once you download the package, install it as shown below.

[root@timpc tim]# rpm --install MySQL-client-3.22.32-1.i386.rpm
[root@timpc tim]#

Securing the Server

Like other services you install on the server, MySQL opens up a network port to which anyone may attach. Not only that, but MySQL's authorization mechanism does not consider file (or other) restrictions the system maintains on a user's login account. This means that any user can access the database if they know the username and password of a valid database user. Therefore, every database account you create (even/especially if you call it "root") should have a secure password. As of this document's writing (7/14/2000), MySQL clients send their passwords in the clear across the wire. To keep you server secure:

When you create an account, give it a password.
Create a separate account for each function for each database and restrict rights appropriately.
Only allow database access from "localhost."
Do not enter the password on the command line (-p[password]) if you are on a multi-user system because other people could see it if they use the `w` or similar commands.
Remove any unused or seldom used databases (like the "test" database which is installed by default).

Learning MySQL

The rest of this workshop relies on the MySQL documentation's tutorial, with the following suggestions:

Section 9.2: As of Fri Jul 14 11:56:18 CDT 2000, note that to cancel a command you enter "\c" instead of "c" without the leading backslash.

Section 9.3: If you choose to do this section, you can create a sample database called "ecommerce" by doing the following:

[root@timpc tim]# mysql
mysql> create database ecommerce;
Query OK, 1 row affected (0.02 sec)

mysql> \u ecommerce;
Database changed
mysql>

Section 9.3.4: I recommend skipping this section and going directly to section 9.5. It appears the authors wrote section 9.3 and 9.4 for people with more SQL experience. If you decide to do this section, please note that temporary tables and the locking method shown rely on later versions of mysql server.
Section 9.5.3: When you hit tab in some editors, they actually substitute spaces for the tab character. This would give incorrect results when you tried to load the text file into a table. One way around this problem is to use a comma-delimited format for the text file. Make sure you include commas for the empty fields as well (especially at the end of lines that are missing the last date) and do not put spaces between the fields. Once you have the text file with comma separated values, the following command should import it:
```
mysql> LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet FIELDS TERMINATED BY ',';
Query OK, 8 rows affected (0.01 sec)
Records: 8  Deleted: 0  Skipped: 0  Warnings: 1
```
Note the 1 warning is intentional and matches the tutorials text. BTW, MySQL will not display the records with corresponding warnings. To determine what happened to the table, you need to display the values you inserted.

Wrapping It Up

This concludes the workshop. To see how well you understand MySQL, I recommend going back to the PHP workshop and making the final project use MySQL instead of the "dbm" functions. You should rely on the PHP MySQL function documentation to get help. Functions of special interest include:

Last Updated on Fri Jul 14 12:27:37 CDT 2000 by Tim Christian (tim@unt.edu)