mSQL stands for mini-SQL, a shareware database server (free for non-commercial use). It supports, as its name suggests, a subset of the normal SQL specification. Additionally, it has some extensions to SQL. Before we go any further, it is probably best to list what mSQL can't do so you can decide if it's worth your while to use it for your particular application.

Despite these shortcomings, mSQL is still a useful database server. For small datasets with a simple schema and low likelihood of simultaneous accesses, it is well-suited. There are several other reasons you might want to use mSQL:

These two additional packages are a part of the standard mSQL distribution.

Lite is a scripting language designed for use with mSQL. Its syntax is similar to Perl. Its variables are dynamically typed, arrays are dynamically sized, and variable names start with a $. Its chief advantage over other languages is its tight integration with mSQL.

W3-msql is a Web interface to Lite that is embedded in HTML and server-side parsed. Also included is W3-auth, an access control package for W3-msql. It supports users and groups and gives somewhat finer access control than the non-Web access control file. In some ways, W3-msql is very similar to PHP, though it lacks support for other databases and is simply not as full-featured. See the Web serving chapter for more information on PHP.

mSQL's author claims that it will manage up to 4GB of data, but suggests that other system limitations will make such a large database impractical under mSQL. It's not entirely clear how this number is arrived at, if it comes from a real-life example, or to how many rows it corresponds.

Linux 1.3 or higher is required. Earlier versions do not have full mmap support, which mSQL 2 requires. Additionally, you should have the kernel sources installed to ensure that you have all the necessary libraries and header files.

For an example of how to install mSQL, see the Web serving chapter.

An increasingly popular free database server, MySQL started off as an enhanced version of mSQL 1. It attempted to add a more complete set of the ANSI SQL specification, plus some enhancements. Today it implements nearly all of ANSI SQL and a bevy of additional features.

Like mSQL it implements a regex LIKE separate from LIKE to enhance performance. It is also fairly lightweight, though not light as mSQL.

Some things that MySQL supports that mSQL doesn't include are:

Some disadvantage of MySQL include:

In the past year or two, MySQL's popularity has exploded. It has replaced mSQL in many places. Virtually every common programming language has a module or wrapper that implements the C API. Support on Internet mailing lists and on the Web is excellent.

In general, if you are frustrated with mSQL's limitations or licensing, you should definitely look into MySQL. If your favorite language doesn't have an interface and you know a little C, it is probably worth your while to port mSQL modules to MySQL.

MySQL is free in almost every circumstance, the one exception being that if you wish to sell it, you have to purchase a license. Getting a legal copy of it for your use is thus simply a matter of FTPing the distribution. The source code is available, or if you choose, you can get a Linux binary distribution. Binaries also exist for Solaris and SunOS, as well as a growing number of other OSes.

MySQL's home page is http://www.tcx.se/; there are also numerous mirrors: in the U.S. (http://www.buoy.com/mysql), Australia (http://mysql.bluep.com/), and many others, all listed on the home page. Also on the home page are pointers to FTP for MySQL, the documentation home page, mailing list, and contributed software.

If you choose to compile MySQL yourself, you will need a multithreading library. If you have Red Hat 5.02 or later, the threading is built into the C library. Otherwise, you will need the LinuxThreads library. You will also need a C++ compiler, presumably g++ under and libg++. Note that libg++ is distributed separately from GCC/g++/gobj-c, just as GNU libc is. Lastly, you will need recent versions of GCC, at least 2.7.2; GNU make, at least 3.75; and libc 5.4.12 or glibc 2.0.2.

GNU autoconf is used, so the common sequence of configure, make, and make install works. Useful options for the MySQL configure are:

Other configure options can be listed with the --help option. You may also want to run an optional make check before installing.

We're not going to go into a lot of detail about how to deal with a troublesome compile, since you can always fall back to a binary distribution. One thing to note is that unpacking the binary distribution and installing from the compiled source distribution will place things in slightly different directories. The binary distribution creates a directory named mysql-<version> and subdirectories for libraries, header files, and program files. Installing from the source makes mysql subdirectories in /usr/local/lib and /usr/local/include. This is important to keep in mind if you compile MySQL-dependent programs or modules.

Beyond making sure that you have the right versions of the tools needed to compile, there is little to check. If you run into trouble, mysql mailing list is probably the best place to ask for help. Information about it can be found in the FAQ at the MySQL home page or any of its mirrors.

Once you have MySQL compiled, you can run the optional make check and then proceed to running the make install. This will install MySQL in subdirectories of your install base as follows: binaries in bin/, libraries in lib/mysql, header files in include/mysql, and the data area will be in var/.

Unpack the tarball where you want the mysql directory to be created. It will create a directory named mysql-<version info>-<platform info>; you can then create a link using ln or just rename it to mysql. The MySQL binaries will now be in /usr/local/mysql/bin, libraries in /usr/local/mysql/lib, header files in /usr/local/mysql/include, and /usr/local/mysql/data will be the home of the data files and subdirectories.

The next step in our installation is to set up the grants database. Unless you've done some fiddling after the installation, you should just be able to run it and let it do its thing. What it does is set up tables for access control for users. These users do not need to be system users, that is, they do not need /etc/passwd entries.

The name of the grants database is simply mysql. It has three tables: db, host, and user. The db table lists access for the combination of user, database, and host from which the user is connecting. host controls access for the pairing of host and database. Finally, user combines host, user, and password.

To initialize the mysql database, you need to run mysql_install_db. If you used a binary distribution, run the following command in the directory created when you unpacked the archive type:

scripts/mysql_install_db

If you compiled from source, run the above command in the top level of the source tree. This will set up root as a "superuser" when connecting locally, lock out remote users, and force other local users to be granted access on a per-database level in the db table. It will also set up db to allow any access to the test database, or any database starting with test_ to any user. It will start the MySQL daemon, mysqld, for the first time as well.

Since the grants database is probably the most complex aspect of using MySQL, we'll give a few examples here. There are a few things to remember when manipulating the access tables:

To start manipulating the mysql database, as root, enter:

mysql mysql

If you installed from a binary distribution, you will likely have to specify the path to the mysql binary.

The following set of entries will lock out all local users that don't otherwise have an entry for a particular database in db:

INSERT INTO user VALUES
('localhost','','','N','N','N','N','N','N','N','N','N','N'),
INSERT INTO user VALUES
('myhost','','','N','N','N','N','N','N','N','N','N','N'),

INSERT INTO host VALUES ('localhost','%','Y','Y','Y','Y','Y','Y'),
INSERT INTO host VALUES ('myhost','%','Y','Y','Y','Y','Y','Y'),

In the above and following examples, myhost stands for the name of the computer where mysqld is running.

Now, to grant the user cary full access to the database notebooks, we would add this row to the db table:

INSERT INTO db VALUES
('%','notebooks','cary','Y','Y','Y','Y','Y','Y'),

If we wanted to give all other users access to the data in notebooks, but not let them modify the table:

INSERT INTO db VALUES
('%','notebooks','','Y','N','N','N','N','N'),

To give the user mark full access with the password osprey from any host:

INSERT INTO user VALUES ('%','mark',password('osprey'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'),

To test your access control, you can use mysqlaccess. The syntax is very straightforward: mysqlaccess [host] [user] [database]. There are a number of options that control the amount of and format of the output as well; the --help option lists them.

In general, it is easiest to give a user no access at all in the user table; simply set up their username and password. Then, do all the access control in the db table. Of course, superusers have to be given full privileges in user. Also, if you want to give a user shutdown or reload privileges, you can only do this in the user table as well.

Now, you may be a little worried about slinging your MySQL usernames and passwords over your LAN. You need not be worried, or at least not as worried as you would normally be since MySQL encrypts its network traffic to at least make it difficult to sniff out users and passwords. (Of course, no decryptable encryption scheme is 100% foolproof. Cracking encryption is a matter of patience, cleverness, and lots of CPU power.)

You're now ready to start populating your database! Just a couple of last notes. When storing data in binary large objects (BLOBs) or variable-length text (VARCHARs), MySQL doesn't allocate space in the table on disk until there is data there. This can substantially reduce the amount of disk space used by your data compared to servers, where space for the entire VARCHAR or BLOB (or TEXT in mSQL) is allocated when the row is created, regardless of how much is actually stored in it.

An Apache authorization module, which is useful if you have a large number of users and lookups in the password flat file are becoming slow.

An X-based interface to the MySQL client, xmysql, exists and is available from the MySQL home page. It requires the Xforms library for compilation. Xforms can be found at http://bragg.phys.uwm.edu/xforms. The interface allows the user to choose tables and columns by pointing and clicking, doing a good part of the query building on its own. Of course, it's likely that the user will have to enter some information in the where clause.

This is an admin for MySQL. It supports the adding/dropping of databases and tables, full access and manipulation of the grants database, browsing and killing of threads, stopping and starting the server, and repair and optimization of tables.