In the past, we’ve covered a number of topics dealing with dynamic Web sites and the tools and services you can use to plan and set up such sites. We’ve looked at tools such as Apache, OpenSSL, and PHP and useful scripts in both Perl and PHP. I recently realized, however, that we haven’t touched on a key part of dealing with dynamic Web sites. While the server, add-ons, and scripting languages are important, another crucial aspect to a well-implemented, dynamic Web site is the database backend that stores the dynamic information.

Given the importance of this database component, we are going to take a look now at the MySQL database server. The information provided here, along with past Drill Downs, will help prepare you to implement an efficient, dynamic Web server or site.

Dynamic Web server series

For more information on setting up dynamic Web servers, download Vincent Danen’s complete series on the subject.

Introducing MySQL
MySQL is a relational database server that can contain small amounts of simple data or large amounts of complex data. It’s lightweight enough to be used for personal information yet robust enough to handle the volume of data and transactions that large applications require.

A number of lightweight databases excel in the area of Web application development, although MySQL is perhaps the most popular. PostgreSQL and mSQL are two other freely available, open source database servers you may be interested in.

MySQL is a popular database server because it is easy to use and administer. It’s lightweight and fast, and it supports direct interaction with various programming languages, such as C, Perl, PHP, Python, and Java (via the Java Database Connectivity API [JDBC]). MySQL also supports a key subset of Structured Query Language (SQL), so if you already have a background in SQL, you’ll be one step ahead. Because a lot of database servers use SQL (e.g., Oracle, Sybase, and Informix), it’s easy to migrate from one database server to another without needing to relearn the syntax to manipulate the data within them. However, MySQL lacks some of the advanced features found in the more expensive database products. As a result, you may need to work a little harder when writing your Web applications because you won’t be able to rely on the database server to handle certain tasks for you.

Building MySQL
We’re going to install MySQL on the Linux platform here, so your first step will be to download the MySQL source code and compile it. As of this writing, the latest version of MySQL is 3.23.39. You can download some precompiled binaries, but if there are no RPM packages for your distribution, I find using self-compiled packages to be better.

The file you want to download is the source code tarball, which is called mysql-3.23.39.tar.gz. Save this file to your /usr/local/src directory.

Now, change to that directory and unarchive the file as root using:
# cd /usr/local/src
# tar xvzf mysql-3.23.39.tar.gz
# cd mysql-3.23.39

The next step is to do the actual prebuild configuration using the configure script. This will define some system-wide build options for the MySQL server you are about to build. Here’s an example use of the configure script:
# ./configure –enable-assembler –with-mysqld-user=mysqld
  –with-unix-socket-path=/var/lib/mysql/mysql.sock –with-berkeley-db

This would be a good way to build MySQL, but it doesn’t take advantage of some of the compiler options available, and it doesn’t optimize the resulting binaries. You can improve the script by adding this:
# CFLAGS=”-O3″ CXX=gcc CXXFLAGS=”-O3 -felide-constructors -fno-exceptions -fno-rtti”
  ./configure –enable-assembler –with-mysqld-user=mysqld
  –with-berkeley-db –with-mysql-ldflags=-all-static –prefix=/usr/local/mysql

Of course, this is all one long line. The CFLAGS, CXX, and CXXFLAGS variable declarations are to pass parameters to your compiler. For this to work properly, you must have a recent version of the gcc compiler, 2.95.2 or higher. If you are using an older version of gcc, you may want to omit those declarations altogether.

The rest of the options are pretty simple as well. The first tells configure to use the assembler versions of some string functions if possible, which will increase speed. The second defines what user the MySQL daemon will run as; in this case, it will be the user mysqld. You will need to create the user before running the server; we’ll get to that momentarily. Next, we tell MySQL to support and use the Berkeley database, and then we pass some extra linking arguments for mysqld, the server program. Finally, we tell configure that we want to install MySQL into the /usr/local/mysql directory tree.

Once configure has run and created the necessary Makefile files, you are ready to begin building the software itself. To do this, simply issue as root:
# make

Now, sit back and have a cup of coffee. The speed of your computer will determine exactly how much coffee you will be able to drink before the build is done, but if it’s a slower system, be prepared to drink a few cups. Once the build is complete and you’re once again at the command line, execute as root the following to install it:
# make install

After everything has been installed, your MySQL server will be ready and waiting in your /usr/local/mysql directory. Now begins the task of configuring it and making it ready for use.

Configuring MySQL
To make MySQL useable, you must first create the user specified in the configure command—in this case, mysqld. You can do this with two simple commands as root:
# groupadd mysqld
# useradd -g mysqld mysqld

Once this is complete, you can initialize the database. The command here is nice and easy:
# cd /usr/local/mysql/bin
# ./mysql_install_db

Now you need to change some permissions so that your MySQL user can run the database server. Execute the following commands as root:
# chown -R root /usr/local/mysql
# chown -R mysqld /usr/local/mysql/var
# chgrp -R mysqld /usr/local/mysql

This will change the user ownership of all files and directories in the /usr/local/mysql hierarchy to root except for the var/ subdirectory, which will be owned by our mysqld user. This is necessary because the var/ subdirectory contains all of the database files. In addition, we changed the group ownership of all files and directories to the mysqld user so that it can properly run the server.

Creating the configuration file
At this point, you are almost ready to start the database. First, however, you’ll need to create a MySQL configuration file. The easiest way to do this is to customize an example configuration file from the source directory tree. As root, copy the file to the appropriate location by executing the following:
# cp /usr/local/src/mysql-3.23.39/support-files/my-medium.cnf /etc/my.cnf

This copies the configuration file needed by MySQL (/etc/my.cnf), optimized for medium systems, from the support-files/ subdirectory in the source tree. You can select from a few configuration files for various site sizes: my-huge.cnf for extremely large sites, my-large.cnf for large sites, or my-small.cnf for small sites.

Once you’ve copied the appropriate file, open /etc/my.cnf in your favorite text editor. Most of the defaults should be fine, but you may want to change a few options. At the very least, you should scan the configuration file quickly to familiarize yourself with the options.

One of the versatile aspects of MySQL is that the configuration file can be used in different ways. The configuration file in /etc/my.cnf is the global MySQL configuration file. You could instead copy it to /usr/local/mysql/var/my.cnf for a server-specific configuration—for instance, if you were running multiple MySQL servers and wanted one to listen to port 3306 and the other to listen to an alternate port. You can also copy it to ~/.my.cnf for user-specific options (such as defining your password so that the MySQL client always sends it without your needing to type it—convenient, but not very secure).

The first part of the configuration file deals with the MySQL client options. Here you can define the port to connect to, the MySQL UNIX domain socket to connect to, and optionally, your password.

The second part deals with configuration options for the server itself. You can define the TCP/IP port to listen to and the UNIX domain socket to listen to. You can also set some options such as the size of your key buffer, your table cache, sort buffer, and so forth. The rest of the file basically deals with various optimizations for your MySQL database and should be fairly safe to leave alone.

After you edit the configuration file, you can start the server. Execute the following as root:
# cd /usr/local/mysql
# /usr/local/mysql/bin/safe_mysqld &

Now your server is running. If you execute ps ax, you will see a number of mysqld threads running to accept connections to the database. The next step is to set access permissions. The first user you want to set access for is the MySQL superuser, which is also called root. As the root user, execute this on the command line:
# cd /usr/local/mysql/bin
# ./mysqladmin -u root password ‘secret’
# ./mysqladmin -u root -h password ‘secret’

This sets the password for the root user to secret. The second mysqladmin command does the same but sets the accessible host to, which would be the hostname of the system you have installed MySQL on. This means that MySQL will accept login requests only from the user root on the machine Of course, if you need to have the root user connect to the MySQL server from a remote machine, you would use the last command and change the hostname to the name of the remote machine.

Now you can log in to your new MySQL server. You don’t need to be the root user to do this because when you connect to the database, you will be asked for the specified user’s password. We’ll log in here as root, but you can log in as any user on the system :
# /usr/local/mysql/bin/mysql -u root -p

This connects you to the running MySQL database as the root user and tells MySQL that you need a password to connect. The server will prompt you for the password, and if all goes well, you will get a MySQL command prompt. If you omit the -p option, MySQL assumes that you have the right to connect to the server without a password. If a password is defined for that user, the login will fail.

The mysqladmin program
Mysqladmin is the administrative program for MySQL. This program offers a number of options that will allow you to manipulate your server. Let’s take a look at some of the more useful commands.
# mysqladmin shutdown -p

This command tells the server to shut down. Each mysqladmin command you use must include the -p option at the end to tell MySQL to prompt for a password. The only way the command will work without the -p option is if you have not defined a password for the root user, which is a very bad idea.
# mysqladmin version -p

This command retrieves the version of the currently running MySQL server. It will also give you the server uptime and some status information.
# mysqladmin create dbase -p
# mysqladmin drop dbase -p

These two commands manipulate databases. The first will create the database dbase, and the second will remove that database and all of its tables.
# mysqladmin extended-status -p

This command will give you quite a bit of information on the running server, including the uptime, the number of bytes sent and received, and details on threads and other operations.

Access controls in MySQL
Finally, let’s look at defining access controls in MySQL. All user information is stored in a database within the server. So to manipulate this, you need to use SQL syntax and be logged in to the server as the root user (or any other user with appropriate permissions).

Let’s assume that you have a user called David. You want to create a personal database for him called david and give him access to it. To create the david database and add David to the database system, you would use these commands:
mysql> CREATE database david;
mysql> GRANT ALL on david.* TO david@localhost IDENTIFIED BY ‘pass’;

The first command creates the database david. The second command gives all access privileges to david@localhost on all tables within the david database. It also tells MySQL to allow David to connect only with the password pass.

The SQL command GRANT has a few parameters. ALL specifies all privileges, but you can use the parameters shown in Table A to allow only specific types of access.

Table A
ALTER Alters the structure of the table
CREATE Creates new tables
DELETE Deletes rows from tables
DROP Drops entire tables
INSERT Inserts data into tables
SELECT Reads data from tables

There are other GRANT privileges, but these are the most common. You can also specify other hostnames by using david@remote_domain, so David is allowed to connect only if he’s connecting from the specified remote domain. Notice in the example above that we specified localhost, which means David will be able to connect only from the local machine.

As you can see, MySQL is not a very difficult system to administer. Perhaps the biggest challenge to using MySQL, or any other similar database product, is learning SQL syntax. The server itself, however, is easy to use, both for users and administrators.

Now you will be able to plug in the missing piece of the dynamic Web-site puzzle with confidence. The database backend is one of the most important components, but it doesn’t need to be the most difficult.