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 mariadb-server and php-mbstring, and from amazon-linux-extras, you need the package lamp-mariadb10.2-php7.2. If you're wondering what MariaDB is, it is a fork of MySQL server that is recommended by Amazon for use with Linux 2 AMI systems. For our purposes, MariaDB and MySQL are effectively identical.

To install these packages from their respective package repositories, run the following two commands:

$ sudo yum install -y mariadb-server php-mbstring
$ sudo amazon-linux-extras install -y lamp-mariadb10.2-php7.2

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 systemctl start mariadb
$ sudo systemctl enable mariadb

Run the following command to set the MySQL root user password:

$ sudo mysql_secure_installation

When it asks for the current password for root, press Enter. When it asks to switch to unix_socket authentication press Enter (which defaults to the answer Y or Yes). When asked to change your root password press Enter (which also defaults to Y). 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!

Finally press Enter to the remaining three questions (remove anonymous users, disallow root login remotely, remote test database).

Using MySQL from the Command Line

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

$ mysql -u <USERNAME> -p <DATABASENAME>
Welcome to the MariaDB monitor. ...

MariaDB [(DATABASENAME)]>

The -u argument specifies a specific user to use MySQL/MariaDB with, and the -p argument specifies that you would like to use a password to login to that user. If you add the -p argument, you will be prompted for the password associated with USERNAME. Failing to provide the -p is the same as attempting to log in with no password. DATABASENAME is optional; if this is your first time using MySQL from the command line, you should probably leave this blank.

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, extract it using tar -xzvf, and then remove the archive using rm:

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

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

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

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

$ rm phpMyAdmin-5.0.4-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 installation 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.