Last time, we started looking at free databases for powering your own Web sites

JANUARY 2001 – NT INTERNET GOODIES

Last time, we started looking at free databases for powering your own Web sites. I got through mSQL, and this time we’ll look at MySQL (pronounced “my-ess-queue-ell”). Also, we’ll take a look at free security features for your own Web site. An ounce of prevention is worth a pound of cure!

MySQL

The developers at TcX DataKonsult AB (a Swedish company, located at http://www.tcx.se/) saw a need for a faster version of mSQL. While mSQL is a powerful and easy-to-use solution for many applications, the folks at TcX had major reservations about using it in a true production environment. As they developed their own database, they specifically addressed the following problems:

· Making the database server multithreaded. Multiple calls to the database server (from different clients) execute simultaneously. In mSQL, the database server must completely finish one SQL transaction before it can execute the next SQL transaction.

· Improving the supported SQL syntax. mSQL doesn’t support any functions (COUNT, MAX, MIN, STD, and so on). Also, mSQL has limited “join” support whereas MySQL supports LEFT OUTER JOIN statements.

· Better SQL statement optimizer, and more optimized code in general. TcX believes they have hand-crafted and tuned every critical part of SQL statement analysis and execution, with (generally) an improvement on the scale of an order of magnitude over mSQL.

· High-level of support for existing mSQL applications. TcX wanted to make converts of existing mSQL customers, so to a large degree learning to use mSQL means you’ve also learned to use MySQL.

Now for the bad news: MySQL is not freeware! (For that matter, neither is mSQL if you’re using it commercially!) The download page for the Win32 port specifically states that you can use MySQL for 30 days. After that, you need to either pay for it (around $200US, with the ODBC driver about $25US more) or remove it from your machine. The good news is that 30 days is sufficient time to get familiar with the database and to evaluate for your own needs. Plus, O’Reilly has a book out on both MySQL and mSQL (pretty handy, if you want to learn about both databases). You can find the book at most major bookstores, as well as the usual online companies.

Installing the Software

Once you’ve downloaded the software (4.5MB), open the zip file (mine was MySQL-shareware-win32-1.1.zip). If you use WinZIP, you can simply double-click the ZIP file, then double-click setup.exe. Otherwise, you need to unzip the file to a temporary directory and run setup.exe. Unfortunately, the setup program automatically copies all files directly to “C:\MySQL”--a poor choice for me since my C drive is almost completely full! The only way to work around this problem is to use the setup program to copy the files to C:\MySQL, and then move the C:\MySQL folder to the location where you want it. Since the setup program doesn’t set any environment variables or copy any system-level DLLs, doing this works as long as you remember to set the appropriate directory options when starting the MySQL daemon.

The Tools

MySQL uses many of the same types of tools as mSQL, only with a “my” instead of an “m” in the tool name. As with mSQL, all tools are located in the “bin” folder:

mysqld - Database server

mysql - Command-line program for issuing SQL commands.

mysqladmin - Creates databases, shutdown the server, print version info, and so on.

mysqldump - Extract data from a database and/or table.

mysqlimport - Import data into an existing database.

mysqlshow - Shows database and/or table schema information.

mysqlmanager - A GUI version of mysql (with some functions of mysqladmin). Nice, but still has some warts.

Starting the Database Server

For NT, you have lots of choices. You can run the server in default mode, as a foreground process, or as an NT service. Depending on where you placed the executables when you installed the software (as well as your machine configuration), you may need to specify some options to get correct operation. The most commonly used options are:

--basedir=path - Use this option when you copied the software to a different location (such as “D:\MySQL” instead of “C:\MySQL”.

--port=port - MySQL uses 3306 as the default TCP/IP port. If you have other software that uses 3306, or if you want to run multiple MySQL database servers on the same machine, you’ll need to use this option.

Use the --help option to get information on other options, such as starting MySQL as a service or stopping a running MySQL instance.

Since (for testing) I used the default installation path (C:\MySQL), I first tried to start the server without any options:

bin\mysqld

After a short delay, the prompt returned. I assumed that the server was running as a daemon, and tried to connect using mysql. No dice. I started the server again with:

bin\mysqld --standalone

This time, I was able to connect to the server using mysql. However, I wanted to know what the problem was, so I started the server again with debugging turned on:

bin\mysqld --debug=’d:t:i,\mysqld.trace’

I got some debugging information out to the screen (although I never got any information in the “\mysqld.trace” file) and I was able to connect to the server. Finally, I started the as a service. I first installed the service by using:

bin\mysqld --install

Then, I went to Service Control Manager (“Services” under Control Panel), high-lighted “MySql”, and clicked “Start”. Once again, the server started without problems. Note that if you want to pass options to the service version of MySQL, you must explicitly pass them using the “Startup Parameters” field in Service Control Manager (which is a little awkward!).

Verifying the Database Server

Once installed, it’s time to verify correct operation. You should end up with output similar to the following:

C:\mysql>bin\mysqlshow

+-----------+

| Databases |

+-----------+

| mysql |

| test |

+-----------+

C:\mysql>bin\mysqlshow -u root mysql

Database: mysql

+--------+

| Tables |

+--------+

| db |

| host |

| user |

+--------+

C:\mysql>bin\mysqladmin version status proc

bin\mysqladmin Ver 6.8 Distrib 3.21.29a-gamma, for Win95 on i586

TCX Datakonsult AB, by Monty

Server version 3.21.29a-gamma-debug

Protocol version 10

Connection localhost via TCP/IP

TCP port 3306

Uptime: 5 min 7 sec

Running threads: 1 Questions: 6 Opened_tables: 3 Reloads: 1 Open tables: 0 Memory in use: 1056K Max memory used: 1108K

Uptime: 307 Running threads: 1 Questions: 6 Opened_tables: 3 Reloads: 1 Open tables: 0 Memory in use: 1056K Max memory used: 1108K

+----+------+-----------+----+-----------+------+

+----+------+-----------+----+-----------+------+

+----+------+-----------+----+-----------+------+

C:\mysql>bin\mysql test

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 4 to server version: 3.21.29a-gamma-debug

Type 'help' for help.

mysql> quit

Bye

Performance Benchmarks

The Win32 package ships with a complete “bench” folder, containing Perl scripts to thoroughly test the database server (and many other servers, as well). However, I didn’t have a particular Perl package called “DBI”, so I went to the Web at http://www.tcx.se/crash-me.html. This page contains a detailed comparison of MySQL with a dozen other databases (including Oracle and Sybase). Surprisingly enough, MySQL performs as well as or better than many commercial databases both in capabilities and performance.

Limitations

The biggest limitation that I can see for the typical user is the lack of stored procedures on MySQL. Stored procedures allow you to develop an entire library of additional functions directly within the database server. This allows you to abstract the interface between your client application and the actual data your application uses. Plus, it can substantially cut down both on the parsing/compilation time for executing SQL statements, as well as reducing network traffic between the client and the database server.

You may recall from the mSQL article that mSQL supports a proprietary programming language. Keep in mind that this programming language wasn’t a substitute for true stored procedures--rather, it simply allowed scripted applications (as well as Web applications) to execute these scripts from the client-side.

On the plus side, it’s possible to extend the functionality of MySQL by writing user-defined functions within a shared library (using C or C++). However, because of time and platform portability constraints, this feature probably won’t be useful for the casual MySQL user.

Programming Language Integration

MySQL supports all the following languages:

C/C++ - You can use either the native API or a MySQL-provided ODBC driver. (By using the ODBC driver, note that you have access to MySQL from any ODBC-enabled language, including Visual Basic and Delphi).

Perl - MySQL uses the existing DBI database interface with a custom DBD database driver available from http://language.perl.com/info/cool_modules.html. Note that you’ll see a reference to the “Perl interface for MiniSQL source or readme”. This interface actually services both mSQL and MySQL.

MySQL also has support for Java, PHP, Tcl, and much more. You can get detailed information from http://www.mysql.com/links.html.

Web Integration

There’s a lot of information on this topic--more than I can reasonably cover here. MySQL is a successful database server used by many organizations. The best way to see if MySQL can do what you want is by browsing over some of the demos listed under the links page (see above). Since I’m using MySQL myself to manage my own Web site, I’ll get back to you on how well the database works for me and what configuration issues I run into!