Introduction / Background

I am interested in setting up my home computer to serve as a web server using open source web server, database and middleware/scriping software available for the Linux platform. Having installed Apache web server (assignment 3), this week I install the latest 4.x beta release of MySQL database server (version 4.0.3 MAX).

Software Overview

MySQL is a fast, reliable, open source relational database management system (RDMS). It is the most popular open source SQL RDMS and has been used in demanding production environments for several years. In fact, MySQL is the RDMS backend used (in conjunction with PHP) by the UNC-Chapel Hill Academic Affairs Library (AAL) to dynamically generate the Campus E-Journals and E-Indexes & Databases lists of the Libraries electronic resources. Health Sciences Library staff are actively working with AAL staff to develop the next version of this EJ/EID system and to enhance the existing MySQL database structure so that it can generate various views of these resources for different target audiences. MySQL has proven sufficiently robust for this purpose as well as quite easy to learn and use.

MySQL version 4.0 has recently been released in beta and is the "almost there" precursor to version 4.1 currently in development. Some key features of MySQL version 4.0 offers include:

• greater speeds in processes such as bulk inserts and full text index creation
• support for secure client-server traffic (SSL)
• support for UNION statements (as SQL standard) and mult-table deletes
• provides InnoDB table handler with full support for transactions, row-level locking and foreign key constraints

The coming 4.1 release will also include these high demand features: nested subqueries, stored procedures, and foreign key integrity rules.

MySQL is developed and provided by MySQL AB, a commercial company whose (income-producing) business is providing services around the MySQL database. MySQL software packages and supporting documentation may be found at MySQL.com.

The 4.0 version I installed comes in 2 different packages, both compiled form the same source code:
- the PRO package which is described as "Default package with all the features necessary for typical usage, including the InnoDB transactional table handler"

and

- the MAX version which has maximum features (SSL, RAID, BDB)

I elected to install the 4.0.3 MAX package for i686 Linux systems.

CHECK FOR EXISTING MYSQL INSTALLATION; RETRIEVE MYSQL BINARY PACKAGE

I used "which", "locate" and a newly discovered rpm command (rpm -q --queryformat "%{NAME}-%{VERSION}-%{RELEASE}:%{EPOCH}\n" mysql) to confirm that MySQL was not previously installed on my system. Previously, I visited mysql.com, navigated and read about the available MySQL versions and decided to install the MAX version for Linux/Intel since it is the "fully featured" version. I used wget to download the 4.0.3 MAX precompiled binary package to install. After failed attempts to download from UWisc and Netnumina mirror sites, I was able to successfully download the package from Oregon State to /home/barrie/tmp:

$wget http://mysql.oregonstate.edu/Downloads/MySQL-Max-4.0/mysql-max-4.0.3-beta-pc-linux-gnu-i686.tar.gz

Package download was completed before script was run.

PREPARATION, INSTALLATION AND CONFIGURATION
Prior to my real install, I did a trial run of the install elsewhere on my system and checked that the file would detar into its own directory and consulted the documentation files (README, INSTALL-BINARY, manual.html) enough to decide that I wanted to install MySQL in /usr/local and preview the installation and configuration steps. I then removed the trial install directories (rm -rdf) and started again with the tar.gz file.

I copied the tar.gz package to /usr/local and unzipped and detarred it there (as root since I'm writing to /usr/local).

$sudo tar xzvf mysql-max-4.0.3-beta-pc-linux-gnu-i686.tar.gz

It extracted into its own directory: /usr/local/mysql-max-4.0.3-beta-pc-linux-gnu-i686

I cded into the mysql directory above and reviewed the README and INSTALL-BINARY files as well as the more indepth (MySQL) manual.html and followed the steps (albeit out of order) the steps outlined in INSTALL-BINARY and in manual.html for binaries. I executed su - to become root since most of these tasks require root access and permissions.

     shell> groupadd mysql
     shell> useradd -g mysql mysql
     shell> cd /usr/local
     shell> gunzip < /path/to/mysql-VERSION-OS.tar.gz | tar xvf -
     shell> ln -s full-path-to-mysql-VERSION-OS mysql
     shell> cd mysql
     shell> scripts/mysql_install_db
     shell> chown -R root  .
     shell> chown -R mysql data
     shell> chgrp -R mysql .
     shell> bin/safe_mysqld --user=mysql &
     or
     shell> bin/mysqld_safe --user=mysql & (for version 4.x)

Since I've already extracted and detarred the binary package and cded into the /usr/local/mysql-max-4.0.3-beta-pc-linux-gnu-i686 directory, I stepped back to the beginning of the sequence above and create the mysql group and mysql user in that group. The mysql user of the mysql group will own mysql datafiles and be the user that runs the mysql daemon.

(root) shell> groupadd mysql
(root) shell> useradd -g mysql mysql

Already extracted binary into its own directory so I skip those steps above and move on to create the symbolic link (called mysql) in /usr/local to the install directory.

(root) shell>ln -s /usr/local/mysql-max-4.0.3-beta-pc-linux-gnu-i686 mysql

Performed a long list (ls -l) to see the symbolic link, then cded to the link (cd mysql) to see it work.

I cded to and reviewed contents of the /bin and /script subdirectories highlighted by the MySQL manual: /bin . I then ran the script to initialize the mysql admin database that contains tables that store mysql server access permissions. This ran without problems.

(root) shell> scripts/mysql_install_db

The next step is to change the ownership and group assignments to the mysql directory and data subdirectory (from /usr/local/mysql)

(root) shell> chown -R root . (root) shell> chown -R mysql data (root) shell> chgrp -R mysql .

Once ownership is set to allow mysql daemon to access files it needs, I start MySQL to see if it runs:

(root) shell> bin/mysqld_safe --user=mysql &
(root) shell> ps -ef | grep mysql

MySQL is running!

Next, set the MySQL root password (not the same as system root account/password) for command line and network access:

(root) shell> ./bin/mysqladmin -u root password [new-password]
(root) shell> ./bin/mysqladmin -u root -h magnolia.brighton.org password [new-password]

The password setting command for network access failed so I added this password for root@magnolia.brighton.org by manually editing the User table in the mysql database (admin). I ran mysql as user root via command below and entered root's password I'd created above. I then used mysql commands to get into the mysql database and update the User table to add a password for the network user root@magnolia.brighton.org. This worked fine.

(root) shell> ./bin/mysql -u root -p

mysql>show databases; (see mysql and test in the list of available databases)
mysql>use mysql; (open the mysql admin dbase)
mysql>select * from User;

In looking in this table, root@localhost has the password assigned (encrypted) that I entered in 1st password assignment step above. However, root@hostname (magnolia.brighton.org) has no password indicating the failure or the second password assignment step above. I updated the User table to add a password for the root@hostname account.

mysql>UPDATE user SET Password=PASSWORD('[new-password]') where User='root' and Host='magnolia.brighton.org';

Exit MySQL:

mysql>exit;

Test shutdown of MySQL daemon and verify it works:

(root) shell> ./bin/mysqladmin -u root -p shutdown
(root) shell> ps -ef | grep mysql

Daemon starts and stops successfully via manual command; now to automate this by copying the mysql.server startup/shutdown script from /support-files to /etc/rc.d/init.d and symlink this script in /rc3.d so that MySQL gets started (S file) and stopped (K file) as the system boots/shuts down respectively.

(root) shell> cd support-files
(root) shell> cp mysql.server /etc/init.d/
(root) shell> cd /etc/rc3.d
(root) shell> ln -s ../init.d/mysql.server ./S98mysql
(root) shell> ln -s ../init.d/mysql.server ./K98mysql

Test manually:

(root) shell> /etc/rc.d/rc3.d/S98mysql.server start
(root) shell> ps -ef | grep myslq ; mysql is running.
(root) shell> /etc/rc.d/rc3.d/S98mysql.server stop
(root) shell> ps -ef | grep mysql ; mysql is no longer running.

Exited root; edited my .bash_profile to add /usr/local/mysql/bin to my $PATH so I can can (hopefully) run mysql from my account.
TEST this. logout and log back in as myself to refresh $PATH. Echo $PATH shows that path to mysql is there. Run mysql as me and see what I can see in terms of mysql databases (I should not be able to see mysql admin db; just test):

(barrie) bash shell> mysql
mysql prompt comes up (its running!) so I test what databases i can see:
mysql> show databases;

I only see the "test" database listed; I do not have mysql root's permissions to see "mysql" database.

Exit mysql and run mysql as mysql root to see difference.

(barrie) bash shell> mysql -u root -p

Show databases command this time shows both test and mysql databases in the list.

mysql> show databases;

Instructions for Use

To use MySQL, the user must have an account in the user table of the mysql (admin) database. By default, the mysql root account can modify that database and its tables to include user account information as needed. The mysql root user account can also create, modify and drop databases and their constituent tables. Initial priviledges for other user accounts only allow these accounts to create or use databases named test or test_[something]. Other user accounts can also start and run Mysql server (mysqld). Mysql user names (root or otherwise) are not the same as Linux/Unix user names.

The mysql.com site has helpful, searchable online documentation for using MySQL. Commands for accomplishing some key operations in MySQL include (note the semicolon at the end of all mysql commands):

Create a database:
mysql> CREATE database [database-name]

Create a table in the database:
mysql> use [database-name];
mysql> CREATE table [table-name] (columnname1 datatype(length), columnname2 datatype(length), columnname3 datatype(length), etc));

Insert data into a database table:
mysql> INSERT INTO [tablename]([columnname1, columnname2, ...]) VALUES (['value1', value2, ...]);

Dump database data and structure to a text file for backup or to create duplicate copy of database:

shell>mysqldump [database-name] > [filename]

I was able to run mysql from my user account and get the server to run (see above). Additionally, I was able to use the mysql root user account to create a database, create a table in the database and add data to it, SELECT/query the contents of the table for display, and finally to "dump" the database structure and contents to a text file that can be used to recreate the database if needbe.

With my initial mysql permissions, I was able to see and to complete most of these steps (only) on the "test" database. I was not able to successfully run the mysqldump command; permissions for this admin function must be reserved for mysql root by default.

As the mysql root user, I was able to add an account for myself (barrie) into the mysql database user table along with a password and give my account select, insert, update, delete, create, and drop privileges. After doing this I was required to login with password to run the mysql client (I hadn't been before I added the account). However, once logged in, I could create and drop databases and tables as well as see and use all databases including the mysql admin database.

Next steps in building my web database server is to install and configure PHP to work with MySQL and allow construction of web pages that can extract and display data from the database.