Difference between revisions of "Introduction to MySQL"
|Line 82:||Line 82:|
grant PRIVILEGES on DATABASE.TABLE to USERNAME@'localhost
grant PRIVILEGES on DATABASE.TABLE to USERNAME@'localhost';
Revision as of 22:59, 22 August 2012
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 MySQL User Management
- 6 phpMyAdmin
- 7 Managing Tables in MySQL
- 8 Manipulating Data with MySQL
- 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 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!
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.
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 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|22.214.171.124|:80... connected. Saving to: “phpMyAdmin-126.96.36.199-english.tar.gz” 100%[==================>] 2,972,789 910K/s in 3.2s $ ls phpMyAdmin-188.8.131.52-english.tar.gz $ tar -xzvf phpMyAdmin-184.108.40.206-english.tar.gz ... $ ls phpMyAdmin-220.127.116.11-english phpMyAdmin-18.104.22.168-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-22.214.171.124-english $ mv phpMyAdmin-126.96.36.199-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/
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).