MySQL is an open source database that is very fast and comparatively easy to administer. If you need the most advanced database features, such as replication 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 (and it is in fact catching up even when it comes to those very advanced features). It is dual-licensed. If you are using the GPL version, your application must be licensed under the GPL as well; otherwise, you need to buy the commercial version.
25.1.1. MySQL Installation and Initial Configuration
It is very likely that your distribution contains an installable MySQL system, but if you'd rather have the latest and greatest, you can go to http://www.mysql.com/downloads and download the package yourself. At the time of this writing, the latest
stable version was 4.1.13. Version 5.0 was stabilizing quickly. If you plan to use MySQL for real-life applications, make sure your Linux kernel is at 2.4 or better.
A problem that can occur with MySQL versions compiled with gcc 2.96 is random crashes. This gcc version is not an official stable version, but at least one distributor (Red Hat) unfortunately shipped it as the default compiler in one version. So if you experience strange crashes in the database server and are using gcc 2.96, try using one of the precompiled binaries or install a more stable (and more recent) compiler version, such as 3.3.5.
If you want to build MySQL on your own, you need to download the source package, unpack it, and install with the following commands:
owl$ ./configure --prefix=/usr/local/mysql owl$ make owl# make install
Note that depending on your system, you might select a different installation path. You will also probably need to be root for the third step. You need to remember the installation path because you will need it for the PHP configuration later on.
For the next step, we recommend that you create a user and a group called mysql as described in "Creating Accounts" in Chapter 11. Change to this user with su - mysql and execute:
For security reasons, it might be a good idea to disable logins by the mysql user. You can simply do this as root by putting a star in the second (password) column in /etc/password or /etc/shadow, or both.
After this step, you need to perform just one more command before returning from root to your normal user account. The following command starts the MySQL server:
owl# /usr/local/mysql/bin/mysqld_safe &
You might also want to add either the option -- log or the option -- log-long-format in order to get a logfile about what is going on in the database server.
To check whether your server was started correctly, you can try (as a normal user again) the following command (you need to change the path, of course, if you have installed MySQL in a different location):
owl$ /usr/local/mysql/bin/mysqladmin version mysqladmin Ver 8.41 Distrib 4.1.13, for suse-linux on i686 Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license. Server version 4.1.13 Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 43 days 11 hours 39 min 17 sec Threads: 1 Questions: 142 Slow queries: 0 Opens: 160 Flush tables: 1 Open tables: 13 Queries per second avg: 0.000
This should work without entering a password. We would like to point out, though, that it is not a good idea to have a database without a password because that increases the odds that a possible intruder will get at your potentially valuable data in the database. You might perhaps want to leave the database without a password for testing, but you should make sure that you do not forget to set a password after you are done with your tests. Retest to see whether everything works with the password in place. If you have created a password for the root database user (or if your distribution has done so for you check your documentation in case of any problems), you must specify the -p option, which makes mysqladmin ask you for your password.
We should add here that most distributions include a startup script for the MySQL server that you can use instead of starting the server manually (especially if you have installed MySQL from your installation media). Often, this script is in /etc/init.d/mysql.
With the database server started and working, we can start to define database users and create new databases. We would like to point out that a usable tutorial is included with the MySQL sources. You can also find lots of documentation on http://www.mysql.com, so we just cover the very basics here to get you started.
25.1.2. Initial Tasks: Setting Up Users and SQL
There are three ways of communicating with the MySQL engine: you can use a console-based database client, you can write so-called SQL scripts and feed them to the database in order to execute many SQL commands at once, and you can use one of the many bindings to programming languages to access the MySQL database in the programming language of your choice (depending on the access library that you use, this may even mean that you do not have to enter SQL statements at all). SQL stands for Structured Query Language and is the database language used with relational databases; we cover its use later in this chapter. All three ways of executing SQL commands assume that you have the correct username/password combination.
An important thing you need to know about MySQL is that Linux user accounts are different from MySQL user accounts. In other words, MySQL has its own account management. Most people give their MySQL user accounts the same names as their Linux user accounts in order to avoid confusion, though.
By default, there is one MySQL account called root, which has no password (talk about "security by default"). This means that you can access the database server with the interactive command-line tool mysql as follows:
owl$ mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 13 to server version: 4.1.13. Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>
The -u option specifies the database user to use. If this does not work, maybe your MySQL installation has a password set for the root user. Try to find this password in the documentation and start the mysql program with:
owl$ mysql -u root -p
which will prompt you for the password.
Assuming that you have been able to log in to the database server, let's try to issue a 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 semicolonprobably 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 where the command keywords and the variable parameters are in a SQL script.
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 in to 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 as well shortly describe them here:
There are even more SQL commands and corresponding privileges (such as 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 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)
25.1.3. Creating and Populating a Database
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 a 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 columnsthat is, there are two data fields in each record. One is called id. This serves as a unique identifier for each record 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 that 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 all right 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.
At this point, you know already enough about MySQL to experiment yourself or start reading another book about databases and dream about building the next hugely successful e-commerce web site.
But before you enter your dream, take a moment and let us finish our MySQL discussion by letting you in on one more useful feature: 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
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 had more than a handful of tables.