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 yum (RHEL), you need the packages mysql56-server and php70-mysqlnd and php70-mcrypt and php70-mbstring

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!

Run these 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
$

Run the following command to set the MySQL root user 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!

Using MySQL from the Command Line

To interface with MySQL from the command line, use the mysql command. The syntax is:

$ mysql -u USERNAME -p DATABASENAME
Welcome to the MySQL monitor. ...

mysql>

You will be prompted for the password associated with USERNAME.

Managing Databases

To create a database on your MySQL server, simply run the query:

create database DATABASENAME;

You can delete a database using the query:

drop database DATABASENAME;

When you're at the MySQL prompt, use the following query to select a database to which to run select, insert, update, and delete queries:

use DATABASENAME;

MySQL User Management

The first time you log into MySQL, you will need to use the root user, with the password that you specified earlier. However, it is unwise to use the root user for everyday operations with your MySQL database.

Creating a New User

To create a new user named USERNAME with the password PASSWORD, run the following query inside MySQL:

create user 'USERNAME'@'localhost' identified by 'PASSWORD';

This will create USERNAME, but that user will not have privileges to access any databases.

Granting Privileges

MySQL provides the grant command to grant privileges to a user. The syntax for assigning USERNAME some PRIVILEGES on a TABLE in a DATABASE is:

grant PRIVILEGES on DATABASE.TABLE to USERNAME@'localhost';

If you want to grant global privileges to a certain administrative user, run the query like this:

grant all on *.* to USERNAME@'localhost' with grant option;

The with grant option enables this user to create other users and assign them permissions, like we are doing here.

If you want to grant privileges to a certain user so that they can only manipulate data in a certain database (a wise idea for when you create a user to use in your PHP web application), run the query like this:

grant select,insert,update,delete on DATABASE.* to USERNAME@'localhost';

Whenever you change privileges, you also need to run the query flush privileges;:

flush privileges;

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

To add phpMyAdmin to apache's root server, you need to make your user apart of the apache group, and change the owner of the root directory to apache.

To add your user to the apache group:

$ sudo usermod -a -G apache YOUR_USERNAME

Note you may have to exit your server and ssh back in for this to take effect

Change the owner of the www folders to your user, and the group apache

$ sudo chown -R YOUR_USERNAME:apache /var/www

Now that you can access the /var/www/html directory download phpMyAdmin to apache's root directory using wget, and then extract it using tar -xzvf:

$ cd /var/www/html
$ wget 'https://www.phpmyadmin.net/downloads/phpMyAdmin-latest-all-languages.tar.gz'

$ ls
phpMyAdmin-latest-all-languages.tar.gz

$ mkdir phpmyadmin && tar -xvzf phpMyAdmin-latest-all-languages.tar.gz -C phpmyadmin --strip-components 1

$ ls
phpmyadmin  phpMyAdmin-latest-all-languages.tar.gz

$ rm phpMyAdmin-latest-all-languages.tar.gz

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/html/phpmyadmin
$ sudo 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/

Troubleshooting

Older versions of the phpmyadmin package in apt required that you perform one additional step: let Apache know that phpMyAdmin was installed. The new version does this automatically so long as you check the "apache" box in the first step of the instalation process.

To manually hook up phpMyAdmin with Apache, run the following command to symlink phpmyadmin.conf into Apache's conf.d directory:

$ sudo ln -s /etc/phpmyadmin/apache.conf /etc/apache2/conf.d/phpmyadmin.conf
$ sudo apache2ctl restart  # restart Apache

Using phpMyAdmin

Once phpMyAdmin is installed, you will see a login screen. Log in using a non-root 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).

MySQL Schema and State

Read the MySQL Schema and State guide for more information.

Using MySQL in PHP

Read the PHP and MySQL guide for more information.