Introduction to MySQL
This article is an introduction to MySQL, a database used in conjunction with web applications.
- 1 Why Use a Database?
- 2 Database Structure
- 3 Installing and Configuring MySQL
- 4 Using MySQL from the Command Line
- 5 Managing Databases
- 6 MySQL User Management
- 7 phpMyAdmin
- 8 MySQL Schema and State
- 9 Using MySQL in PHP
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.
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.
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 apt (Debian), you need the packages mysql-server and mysql-client and php5-mysqlnd and php5-mcrypt
- In yum (RHEL), you need the packages mysql-server and php-mysqlnd and php-mcrypt
Important: We will be using the MySQLnd driver in this class. Make sure that you install the php(5)-mysqlnd rather than the php(5)-mysql package.
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!
For RHEL Users: Additional Configurations
Apt in Debian takes care of some basic MySQL configurations automatically during installation, but unfortunately Yum in RHEL does not.
In RHEL, 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 $
In RHEL, 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.
Debian Quirk: If you will be importing data into your database on this MySQL session, you should also pass the
--local-infile flag as a runtime argument to the
$ mysql -u USERNAME -p DATABASENAME --local-infile
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:
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.
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
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.
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 apt 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 apt (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|184.108.40.206|:80... connected. Saving to: “phpMyAdmin-220.127.116.11-english.tar.gz” 100%[==================>] 2,972,789 910K/s in 3.2s $ ls phpMyAdmin-18.104.22.168-english.tar.gz $ tar -xzvf phpMyAdmin-22.214.171.124-english.tar.gz ... $ ls phpMyAdmin-126.96.36.199-english phpMyAdmin-188.8.131.52-english.tar.gz $
Next, move the directory to your web server root, and set ownership of the directory to the Apache user (example commands below).
- 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-184.108.40.206-english $ mv phpMyAdmin-220.127.116.11-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/html/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 Apt
The package name in apt is phpmyadmin. Install the package, then run:
$ sudo ln -s /etc/phpmyadmin/apache.conf /etc/apache2/conf.d/pma.conf $ sudo apache2ctl restart
You can access phpMyAdmin via http://ec2-blah-blah.compute-1.amazonaws.com/phpmyadmin/
That was easy!
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.)
To do this manually, run the following command to write the following configuration directive to a file in Apache's conf.d directory:
$ sudo sh -c 'echo "Include /etc/phpmyadmin/apache.conf" > /etc/apache2/conf.d/phpmyadmin.conf' $ cat /etc/apache2/conf.d/phpmyadmin.conf # make sure that the file was created Include /etc/phpmyadmin/apache.conf $
Don't forget to restart Apache after you've made these changes!
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.