Introduction to MySQL

This article is an introduction to MySQL, a database used in conjunction with web applications.

Why Use a Database?

In Module 2, you made a web site that used flatfiles to keep track of usernames and uploaded file associations. However, flatfiles are insecure, lack complex logic, and are impractical for a large-scale web application. This is where databases come in. Databases are designed from the ground up to be fast, efficient, and powerful storage solutions for any amount of data.

In CSE330, we will be using MySQL, which is a popular, open-source relational database management system for web applications.

Database Structure

A MySQL server consists of databases, each of which contains tables. Tables, in turn, consist of one or more fields (like columns), and the data is stored in one or more entries (like rows). A field in a table can be designated as an index; that is, something that can be used to look up the information in the database. The set of tables, fields, and indices in your database is called your schema.

MySQL uses the Structured Query Language (SQL) for manipulating data.

Storage Engines

MySQL is a database server, but under the hood, it actually uses a storage engine to retrieve the data. Two storage engines that are popular in the MySQL community are InnoDB and MyISAM. MyISAM is slightly faster than InnoDB, and it specializes in database schemas that involve predominantly SELECT and INSERT queries (which you will learn about later). However, MyISAM lacks InnoDB's power in making relations between tables, and MyISAM also does not support InnoDB's database transactions. Click here for more information regarding the differences between MyISAM and InnoDB.

In CSE330, we will be using InnoDB for our primary MySQL storage engine because of its support for foreign key constraints. However, in your personal applications down the road, feel free to use either InnoDB or MyISAM.

Installing and Configuring MySQL

To use MySQL, you need the MySQL Server package. Because we will be interacting with MySQL primarily from PHP scripts, we will also need the associated PHP libraries.

• In aptitude (Debian), you need the packages mysql-server and mysql-client and php5-mysql and php5-mcrypt
• In yum (RHEL), you need the packages mysql-server and php-mysql and php-mcrypt

If you need a refresher on how to install software packages in Linux, re-read the Linux guide.

Remember to restart Apache after installing any packages relating to your web server!

In RHEL, you may need to run some additional commands to start the server and the client automatically on startup:

$ sudo /sbin/chkconfig --add mysqld
$ sudo /sbin/chkconfig mysqld on
$ sudo /sbin/service mysqld start
$

Now, you need to set the MySQL root password. To do this, run the command mysqladmin -u root password:

$ mysqladmin -u root password
New password: 
Confirm new password: 
$

When it asks, type the new password, press Enter, and then type the new password again. You cannot see what you are typing because a password is supposed to be secret. If the shell displayed the password to you while you were typing it, it would be forever saved in ~/.bash_history!

phpMyAdmin

It is often cumbersome to SSH into your server every time you want to interact with your database. A popular web-based window into your MySQL server is phpMyAdmin.

Installing phpMyAdmin

You have two choices here: you can either download phpMyAdmin directly from the phpMyAdmin web site, or if you are using Debian, you can install it from the aptitude repository.

Installing phpMyAdmin from Download

If you are using RHEL, this is your best option to install phpMyAdmin. If you are using Debian, it is easiest to install it directly from aptitude (see the next section).

Get the link to the latest *.tar.gz version of phpMyAdmin here: http://www.phpmyadmin.net/home_page/downloads.php

Inside your server via SSH, download the file to your home directory using wget, and then extract it using tar -xzvf:

$ cd
$ wget 'http://sourceforge.net/projects/phpmyadmin/files/phpMyAdmin/xxxx/phpMyAdmin-xxxx-english.tar.gz/download'
Connecting to downloads.sourceforge.net|216.34.181.59|:80... connected.
Saving to: “phpMyAdmin-3.5.2.2-english.tar.gz”

100%[==================>] 2,972,789    910K/s   in 3.2s
$ ls
phpMyAdmin-3.5.2.2-english.tar.gz
$ tar -xzvf phpMyAdmin-3.5.2.2-english.tar.gz
...
$ ls
phpMyAdmin-3.5.2.2-english  phpMyAdmin-3.5.2.2-english.tar.gz
$

Next, move the directory to your web server root, and set ownership of the directory to the Apache user.

• In RHEL, the Apache user is apache:apache (that is, the username apache in the group name apache)
• In Debian, the Apache user is www-data:www-data

As a refresher, the web server root is located at /var/www in Debian and /var/www/html in RHEL.

For example, in RHEL:

$ sudo chown -R apache:apache phpMyAdmin-3.5.2.2-english
$ mv phpMyAdmin-3.5.2.2-english /var/www/html/phpmyadmin
$

Now, create a phpMyAdmin configuration file, which needs to be saved as config.inc.php. The easiest way to do this is to copy the config.sample.inc.php file that is included with your download.

$ cd /var/www/htdocs/phpmyadmin
$ cp config.sample.inc.php config.inc.php
$

Now, open config.inc.php in your favorite text editor (if you forgot how, refer to the Linux guide), and edit the following line, putting in something random for "mySecretHere":

$cfg['blowfish_secret'] = 'mySecretHere';

Finally, restart Apache, and you're good to go. phpMyAdmin will be accessible via http://ec2-blah-blah.compute-1.amazonaws.com/phpmyadmin/

Installing phpMyAdmin from Aptitude

The package name in aptitude is phpmyadmin.

After you install the package, you also need to let Apache know that phpmyadmin was installed. To do this, run the following command to write the following configuration directive to a file in Apache's conf.d directory:

$ echo 'Include /etc/phpmyadmin/apache.conf' >> /etc/apache2/conf.d/phpadmin.conf
$ cat /etc/apache2/conf.d/phpadmin.conf
Include /etc/phpmyadmin/apache.conf
$

Don't forget to restart Apache after you've made these changes!

When everything's up and working, phpMyAdmin will be accessible via http://ec2-blah-blah.compute-1.amazonaws.com/phpmyadmin/

Using phpMyAdmin

Once phpMyAdmin is installed, you will see a login screen. Log in using a username and password you set up in the previous section. Once inside, you will see a GUI for interacting with your MySQL database.

Even with the GUI, it is often easier to run queries manually, especially when doing operations like creating a table. To run a query manually, click either the "SQL" tab or the little icon in the toolbar on the upper-left-hand side of the screen that looks like a database cluster (it's third from the left).