Chapter 18. LAMP

18.1. MySQL

MySQL is an open-source database that is very fast and comparatively easy to administer. If you need the most advanced database features like reduplication or distributed databases, or if you plan to store gigabytes of data, the big iron databases, such as Oracle might be a better choice, but for most intents and purposes, MySQL is an excellent database choice.

On the Choice of Databases We should probably point out that MySQL is not the only choice you have when selecting a database as the back end for dynamic web sites. For example, Postgres, which is installed by default on Red Hat systems, is a very good open source database as well. So, if you want to deploy a "LAPP" system and use Postgres instead, you can very well do that. Most of the information in this chapter will still apply. However, MySQL can be considered the standard database for dynamic web sites on Linux systems. This is due to its ease of use, speed, and versatility. Also, if you are looking for additional documentation on deploying dynamic web sites on Linux (which we strongly recommend you to do), you are more likely to find this information (e.g., in the form of dedicated books on this topic) about MySQL than about Postgres.

[64]This is not a real SQL command, but rather a MySQL administration command.

mysql> show databases;
+-------------+
| Database    |
+-------------+
| mysql       |
| test        |
+-------------+
2 rows in set (0.11 sec)

This tells you that two databases are managed by this database server. One is called mysql and contains MySQL's internal configuration information including the usernames, and the other one is called test and can be used by you for your experiments. It's also no problem at all to create additional databases; we'll show you how in a minute. As you can see, all SQL commands need to be terminated with a semicolon — probably in order to make the C programmers happy.

Now you should give the root account a password (in case it does not have one already). This is done with two SQL commands:

mysql> SET PASSWORD FOR root=PASSWORD('new_topsecret_passwd');
mysql> FLUSH PRIVILEGES;

Notice again the semicolon at the end of these commands; if you forget to type them before pressing the Enter key, MySQL will just stare at you, waiting for you to enter more.

By the way, SQL commands are case-insensitive; we have written them in uppercase here because that makes it a bit easier to see in an SQL script where the command keywords and the variable parameters are.

Also note the use of the FLUSH PRIVILEGES command. This is important because only after this command has been executed will MySQL update its user database.

Now we want to create a new user called olof, which has the same access rights as root, except that it cannot create new users. Apart from that, olof may use and manipulate all MySQL databases on this database server:

mysql> GRANT ALL PRIVILEGES ON *.* TO olof@localhost IDENTIFIED BY 'olof_passwd';
mysql> FLUSH PRIVILEGES;

The user olof can log into the database only from the local machine. This is a good idea since it leaves one less security issue to think about. We recommend that you only allow access from the local machine unless you have a very good reason not to do it this way. Even in the LAMP combo, local access is enough, because the web server process is running on the local machine, and this is the process that connects to the database, not the user's web browser process.

But if you really require access to the database over the network, you could use these commands instead:

mysql> GRANT ALL PRIVILEGES ON *.* TO username@"%"  IDENTIFIED BY 'user_passwd';
mysql> FLUSH PRIVILEGES;

If you think that having all access rights except creating new users is a bit too much, let's create another user that may execute the SELECT, INSERT, UPDATE, DELETE, and DROP operations, but only on the database called test (and only when connected from the local machine):

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, DROP ON test.* TO gonzo@localhost 
       IDENTIFIED BY 'gonzo_passwd';
mysql> FLUSH PRIVILEGES;

If you haven't worked with SQL databases before, these operations will probably not make much sense to you. Since you are going to need to use them anyway when setting up your LAMP system, we might just as well shortly describe them here:

SELECT

This is the most commonly used SQL command. It queries the database for data with certain properties — e.g., you could ask for all customers in a certain town. SELECT never changes anything in the database.

INSERT

This SQL command inserts new records into a database table. You use this (either interactively or, more likely, as part of a program) to insert a customer record into the customer table in your database, for example.

UPDATE

This SQL command changes existing records in a database. You could use this to, for example, increase the retail prices of all articles in the database by 15% (talk about inflation!).

DELETE

This SQL command deletes entire records from the database. Be careful with this command, as there is no way of restoring the data short of restoring from a (hopefully available!) backup tape.

There are even more SQL commands and corresponding privileges (like DROP, which lets you delete entire tables or even entire databases), but these are used less often than the "big four" listed here.

Now we want to create a new database which we can then fill with tables and data later. This is done with the SQL command CREATE DATABASE:

mysql> create database test_database;
Query OK; 1 row affected (0.03 sec)

The output from MySQL already indicates that everything went fine, but in order to be really sure, we can ask anew which databases the server manages:

mysql> show databases;
+-----------------+
| Database        |
+-----------------+
| mysql           |
| test            |
| test_database   |
+-----------------+
6 rows in set (0.00 sec)

Now we want to define a table in our new database, but the first thing we need to do is tell the MySQL server that we actually want to use this database:

mysql> use test_database
Database changed

As you can see, we didn't use a semicolon at the end here, since this is again not an SQL command, but rather a control statement for the MySQL console client. It wouldn't hurt to add a semicolon here, too.

You define a table, which is ultimately where your data will be stored, by means of the SQL command CREATE TABLE. Here is an example:

mysql> CREATE TABLE comment_table(
    ->   id INT NOT NULL auto_increment,
    ->   comment TEXT,
    ->   PRIMARY KEY(id));
Query OK, 0 rows affected (0.10 sec)

Here we defined a table called comment_table with two columns — i.e., there are two data fields in each record. One is called id. This one serves as a unique identifier for each record that cannot occur twice in different records and is therefore marked as the primary key, which is just a fancy term in database-speak for "unique identifier." The other column is a variable of type TEXT which can store up to 65,535 characters.

Now we can check which tables we have within our database test_database:

mysql> show tables;
+-------------------------+
| Tables_in_test_database |
+-------------------------+
| comment_table           |
+-------------------------+
1 row in set (0.00 sec)

Now we know that everything is alright and can start to add data records to our table. This is done with the SQL command INSERT:

mysql> INSERT INTO comment_table VALUES ('0','comment');
Query OK, 1 row affected (0.06 sec)

Finally, we can check which data our table contains:

mysql> SELECT * FROM comment_table;
+----+---------+
| id | comment |
+----+---------+
|  1 | comment |
+----+---------+
1 row in set (0.01 sec)

Here we ask for all (*) columns in the table comment_table. But you might have noticed something odd here: we have asked MySQL to insert a 0 in the first column, but instead there is a 1 now. That's because we have defined this column to be of the type INT NOT NULL auto_increment, which means that the column value cannot be NULL and that MySQL will automatically choose the next available value. This is nice because we can insert new records into the table without having to ensure that we pick unique values for the first column:

mysql>  INSERT INTO comment_table VALUES ('0','comment1');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM comment_table;
+----+----------+
| id | comment  |
+----+----------+
|  1 | comment  |
|  2 | comment1 |
+----+----------+
2 rows in set (0.00 sec)

As you can see, we have specified 0 as the value for the first column again, but MySQL has automatically selected the next available valid value.

[65]Or the first successful e-commerce web site, as some people would like to rectify.

But instead of dreaming, you can also read on here and learn about SQL scripts.

You do not necessarily need to type in all commands at MySQL's own command-line prompt, you can also execute batch files with SQL commands by piping them to the mysql program. For example, if you save the following SQL code as create_db.sql:

DROP DATABASE IF EXISTS test_database;
CREATE DATABASE test_database;
USE test_database;
CREATE TABLE comment_table( id INT NOT NULL auto_increment,\
 comment TEXT,PRIMARY KEY(id));

INSERT INTO comment_table VALUES ('0','comment');
INSERT INTO comment_table VALUES ('0','comment1');

you can execute this script from the ordinary Linux command line with:

mysql -u root -p < create_db.sql

The line:

DROP DATABASE IF EXISTS test_database;

is of course pretty dangerous; you should use it only if you don't have important data in your database.

To tell the truth, it is not absolutely necessary (albeit strongly recommended) to create a new database for each project. In theory, you could lump all your data into the test database that is preinstalled with MySQL as long as you make sure the table names are all different. In practice, this would be a maintenance nightmare if you have more than a handful of tables.